netezza cheat sheet

netezza cheat sheet

#File:netezza-cheat-sheet.sh  v1.3.0

# how-to loop trough tuple cells of an result set 

for grp in `nzsql -q -r -t -c "SELECT GROUPNAME FROM _V_GROUP;"`; do nzsql -c "\dpg $grp" ; done | less

# how-to get help

command -h

# how-to get commands' help

commmand -hc

# how-to create an user

nzpassword add -u username -pw password -host hostname

#how-to export sql result set into a file

nzsql -F ";" -d $db_name -c "SELECT * FROM $table WHERE 1=1 AND LOAD_TM > to_date ( '2013-04-16 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') ORDER BY LOAD_TM DESC ;" -o /tmp/data/tmp.txt

nzsql -F " " -d SYSTEM -c "SELECT HW_HWID FROM SYSTEM.._V_SPU ;"

# how-to restore a db without the data

nzrestore -db DbNameToRestore -sourcedb BackUpSetDatabaseName -increment 3 -schema-only -dir /backup2

#drop session on some criteria ( use grep for filtering )

nzsession | grep idle | awk '/idle/ {print "nzsession abort -force -id "$1}' > /tmp/drop_sessions.sh

# show the disk usage

nzds show -detail | perl -ne 'split /\s+/;print $_[9] ." " ; print $_  ' | sort -nr

#backup

#where are stored the log files of the backup scripts

/export/home/nz/BackupScripts/log

#how-to view the latest 4 differential backups

for file in `ls -t1 /export/home/nz/BackupScripts/log/RunBackUpDiff* | head -n 4` ; do cat $file ; done ;

#how-to view the latest 10 backup files starting with the newest on top

ls -alt --time-style=long-iso /export/home/nz/BackupScripts/log/* | head -n 10

#how-to create an repot of the db and table sizes

sh /nz/support/nz_db_size >> /export/home/nz/Logs/nz-dbs-tables-sizes-report.txt

# how-to kill idle sessions

nzsession | grep SLOW_READER_ACCOUNT | awk '/idle/ {print "nzsession abort -force -id "$1}' > drop_sessions.sh

# check the swap space usage

/nz/kit/bin/nzstats -type spu

# show sub command help

/nz/kit/bin/nzstats -hc show

# check the number of active users per database

/nz/kit/bin/nzstats show -type database

/nz/support-TwinFin-5.0-100107-1556/bin/nz_view_references -h

nzhw -detail -type SPU

#how-to find out what where has been backed up

for i in {1..4}; do find "/backup${i}" -maxdepth 3 -mindepth 3 ; done ;

#check the exact errors from the last 1000 backup logs

for file in `ls -lrt /nz/kit.7.0.P4/log/backupsvr/*.log | perl -ne 'split /\s+/;print $_[8]."\n"' | tail -1000` ; do grep -nH Error $file ; done ;

#how-to check from odbc.ini the name of the db by the connection string

export IniFile=/informatica/9.1.0/ODBC6.1/odbc.ini

export IniSection=nz_etl_test

sed -e 's/[[:space:]]*\=[[:space:]]*/=/g' \

      -e 's/;.*$//' \

      -e 's/[[:space:]]*$//' \

      -e 's/^[[:space:]]*//' \

      -e "s/^\(.*\)=\([^\"']*\)$/\1=\"\2\"/" \

      < $IniFile \

      | sed -n -e "/^\[$IniSection\]/,/^\s*\[/{/^[^;].*\=.*/p;}" | grep -i database

#how-to search for "broken" views which need to be recreated

/nz/support-TwinFin-5.0-100107-1556/bin/nz_view_references

/nz/support/bin/nz_view_references

#how-to show the number of active users per db

/nz/kit/bin/nzstats -type database | sort -n -k8

#how-to check current system sfw version and revision

nzrev; nz_stats

#how-to check the current version of the netezza support scripts

nz_help -version

# show the utilization of the components

nzsqa proctbl -all | grep -i util

#how-to show hardware issues

nzhw show -issues

#important paths

#where are the netezza cli binaries

/nz/support/bin/

/nz/kit/bin/

/nz/kit/bin/adm/

#important logs

/var/log/nz/heartbeat_admin.log

/nz/kit.7.0.P4/log/postgres/pg.log

#important conf files

/etc/ha.d/ha.cf

# check the clustering and resource group status on NPS

crm_mon -i5

# report db sizes on

nz_db_size -summary

#how-to check for locks on netezza tables

nzsql -F ";" -d $db_name -c "SELECT * FROM _T_PG_LOCKS;" -a | grep -i TERRA

# how-to check which tables do need grooming

perl /nz/kit.7.0.P4/bin/adm/tools/cbts_needing_groom -db TERRA_STG_PROD

# now-to check for hanging sessoins

export db_name=SYSTEM;nzsql -F ";" -d $db_name -c " select q.qs_planid, q.qs_sessionid, q.qs_clientid, s.dbname, s.username, q.qs_cliipaddr,

 q.qs_state, q.qs_tsubmit, q.qs_tstart, case when q.qs_tstart = 'epoch' then '0' else abstime 'now' - q.qs_tstart end,

initcap(q.qs_pritxt), case when qs_estcost >= 0 then ltrim(to_char(cast(qs_estcost as float)/1000,'99999999999999999.999'))

else ltrim(to_char(cast((18446744073709551616 + qs_estcost) as float)/1000, '99999999999999999.999')) end,

q.qs_estdisk, q.qs_estmem, q.qs_snippets, q.qs_cursnipt, q.qs_resrows, q.qs_resbytes , q.qs_sql from _v_qrystat q, _v_session s

where 1=1 and q.qs_sessionid = s.id and q.QS_STATE not in ( 2,3);" -a

# find out the heaviest queries today

export db_name=SYSTEM;nzsql -F ";" -d $db_name -c " SELECT * FROM _V_QRYHIST

where 1=1 AND QH_TSUBMIT > CURRENT_DATE ORDER BY QH_ESTCOST ASC" -a

export db_name=SYSTEM;

#how-to list the columns of a table

export sql=" SELECT ' , ' || NAME || '.' || ATTNAME  from _V_RELATION_COLUMN WHERE 1=1

AND NAME LIKE 'DIM_PAYER_ADDRESS'

ORDER BY NAME , ATTNUM

;";

nzsql -F ";" -d $db_name -c "$sql" -a

#how-to export a list of netezza tables into csv files :

export db_name=SET_HERE_YOUR_DB_NAME

export schema_owner=SET_HERE_YOUR_SCHEMA_OWNER

for table in `echo table_name1 table_name2 `; do (nzsql -F ";" -d $db_name -c "SELECT * FROM $db_name.$schema_owner.$table" -A -q -o /tmp/$db_name.$table.csv.tmp ) ; head --lines=-1 /tmp/$db_name.$table.csv.tmp > /tmp/$db_name.$table.csv; rm -fv /tmp/$db_name.$table.csv.tmp ; done ;

nz_responders

#

# Purpose:

# To provide a simple cheat sheet for netezza administration and command line utilities

# eof file: netezza-cheat-sheet.sh