#File:netezza-cheat-sheet.sh v1.2.5
# 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
# Start the nzAdmin windows tool , start , run , type
"C:\Program Files\IBM Netezza Tools\Bin\NzAdmin.exe"
#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 haning 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
Posts
