Wednesday, 10 December 2014

How to create Unix Script to Drop & Truncate Netezza table- or execute any SQL on Netezza

#  Unix Script to Drop & Truncate a Netezza tables
#!/bin/sh
# enter your database name and table name in below

dbname=exampledb
tblname=exampletbl

# use below line to drop a table 
nzsql $dbanme -c "drop table $tblname"
# use below line to truncate a table 
nzsql $dbanme -c "truncate table $tblname"

-----------------------------------------------------

#  Unix Script to Truncate list of Netezza tables
#!/bin/sh
# enter your database name below

dbname=exampledb
table_list=`nzsql $dbane-t -c " select TABLENAME from _V_TABLE where OBJTYPE = 'TABLE' and TABLENAME like 'yourtablelistprefix_%' "`

for table_name in $table_list; do
  echo "Dropping the table $table_name from database $dbname"
  nzsql $dbname -c "truncate table $table_name"

done;