SQL Tunniing update

SELECT

    SUM(CASE 

            WHEN UPPER(DBMS_LOB.SUBSTR(t.sql_text, 1000, 1)) LIKE 'INSERT%' 

            THEN s.executions_delta ELSE 0 

        END) AS total_inserts,

    SUM(CASE 

            WHEN UPPER(DBMS_LOB.SUBSTR(t.sql_text, 1000, 1)) LIKE 'UPDATE%' 

            THEN s.executions_delta ELSE 0 

        END) AS total_updates,

    SUM(CASE 

            WHEN UPPER(DBMS_LOB.SUBSTR(t.sql_text, 1000, 1)) LIKE 'DELETE%' 

            THEN s.executions_delta ELSE 0 

        END) AS total_deletes,

    SUM(CASE 

            WHEN UPPER(DBMS_LOB.SUBSTR(t.sql_text, 1000, 1)) LIKE 'MERGE%' 

            THEN s.executions_delta ELSE 0 

        END) AS total_merges,

    SUM(s.executions_delta) AS total_dml

FROM

    dba_hist_sqlstat s

JOIN

    dba_hist_snapshot sn

    ON s.snap_id = sn.snap_id

    AND s.dbid = sn.dbid

    AND s.instance_number = sn.instance_number

JOIN

    dba_hist_sqltext t

    ON s.sql_id = t.sql_id

    AND s.dbid = t.dbid

WHERE

    sn.begin_interval_time >= TO_TIMESTAMP('2026-03-19 14:00:00', 'YYYY-MM-DD HH24:MI:SS')

    AND sn.end_interval_time <= TO_TIMESTAMP('2026-03-19 16:00:00', 'YYYY-MM-DD HH24:MI:SS')

    AND (

        UPPER(DBMS_LOB.SUBSTR(t.sql_text, 1000, 1)) LIKE 'INSERT%'

        OR UPPER(DBMS_LOB.SUBSTR(t.sql_text, 1000, 1)) LIKE 'UPDATE%'

        OR UPPER(DBMS_LOB.SUBSTR(t.sql_text, 1000, 1)) LIKE 'DELETE%'

        OR UPPER(DBMS_LOB.SUBSTR(t.sql_text, 1000, 1)) LIKE 'MERGE%'

    );

Check Environment details on IBM Netezza

Check Environment details on IBM Netezza 

HPF version: 

cat /nzlocal/scripts/version.txt    

MODEL 

cat /nzlocal/scripts/model                   

FDT version :      
                                               
head -2 /opt/Netezza/FW/PSeries/version.txt                          
head -2 /opt/nz/fdt/version.txt    
                               
RedHat version:                                                      
lsb_release -i -r    
                                             
As nz user:                                                          
NPS version:
nzrev                          
                        
Current System Stats: nzstats  
Hardware stataus  
nzhw -issues
nzds -issues
                             
As root user:                                                        
uname -n        

model of the system                                                      
dmidecode -t 1  

Callhome                                                
cat /nz/data/config/callHome.txt   

Netezza Database refresh from prod to Dev --- Incremental Refresh nzbackup nzrestore

Netezza Database refresh from prod to Dev --- Incremental Refresh nzbackup nzrestore

1. Once We have done with full backup and restore, Then we may start incremental backup and restore .

Incremental backup 
crontab Entry:-
1 * * * * /export/home/nz/scripts/refresh/incr_backup_refresh.sh PMACDW      > /dev/null 2>&1

######################Incremental Backup shell script #######################
#!/bin/sh
##Pass Database name with space
cd /export/home/nz
. ~/.bashrc
export PATH=/nz/kit/bin:/nz/kit/sbin:/nz/kit/bin/adm:/bin:/usr/bin:/usr/local/bin:.:/nz/support/bin
email_list="EDW_Support@gmail.com"
DBA="NZRed@gmail.com,EDW_Support@gmail.com"
echo "Please call DBA , If It's not acknowledged by DBA" > /tmp/massage.log
HOST=`hostname`
if [ $# > 0 ]; then
    i=$1
    DBNAME=$(nzsql -l|grep -w $i |awk '{ print $i}'|cut -d '|' -f1|cut -d " " -f2)
    if [ $i = "$DBNAME" ]; then
                DBNAME=$i
                DB=$DBNAME
                DB=`echo "${DB,,}"| cut -c 2-`
                BKTP=DIFF
                DB_TYPE=PROD
                LOG_DIR=/export/home/nz/scripts/log
                LOG=$LOG_DIR/$DBNAME_backup_`hostname`_`date +%y%m%d%H%M%S`.log
                BKP_LOG_DIR=/nz/kit/log/backupsvr/
                file1=/nz/$DB/triggers/$DB.done
                file1a=/nz/$DB/triggers/$DB.wip
                file1b=/nz/$DB/triggers/$DB.bkup_done
                file1c=/nz/$DB/triggers/$DB.bkup_aborted
                ########checking logfile status
                if [ -f "$file1" ]; then
                        echo " Initiate incremental backup - trigger file available" > $LOG
                        mv $file1 $file1a
                        echo "incremental  backup of Database $DBNAME" >>$LOG
                 #######Backup
                NOW=$(date +"%Y-%m-%d")
                nzbackup -u admin -db $DBNAME -connector netbackup -connectorArgs "DATASTORE_SERVER=edp-nbu-120-ap:DATASTORE_POLICY=phx_netezza"  -differential -streams 8 -v  >>$LOG
                        if [ $? -ne 0 ]; then
                                cat /tmp/massage.log | mailx -s "FAILED:Backup(Refresh) NZ  $DBNAME $BKTP Database Backup on  $HOST " -a $LOG $DBA
                                 mv $file1 $file1c
                        else
                                BKPSTS=$(nzsql  -qc  "SELECT  status  FROM  _v_backup_history     WHERE   optype='${BKTP}'    AND     dbname = '${DBNAME}'  AND     starttime = (SELECT MAX(starttime) FROM _v_backup_history where OPTYPE='${BKTP}' and DBNAME = '${DBNAME}');")
                                BKPLOG=$(nzsql  -qc  "SELECT  logfile  FROM  _v_backup_history     WHERE   optype='${BKTP}'    AND     dbname = '${DBNAME}'  AND     starttime = (SELECT MAX(starttime) FROM _v_backup_history where OPTYPE='${BKTP}' and DBNAME = '${DBNAME}');")
                                STATUS=`echo $BKPSTS|cut -d " " -f3`
                                LOGFILE=`echo $BKPLOG|cut -d " " -f3`
                                cd $BKP_LOG_DIR
                                BKP_DT=`echo $LOGFILE|cut -d "." -f3`
                                echo $BKP_DT
                                        if [ $BKP_DT = $NOW ] && [ $STATUS = "COMPLETED" ]; then
                                                cat $BKP_LOG_DIR/$LOGFILE | mailx -s "SUCCESS:$DB_TYPE NZ  $DBNAME $BKTP Database Backup on  $HOST " $email_list
                                                /bin/touch /dbbackup/refresh_trigger/bnr/.incr_bkups_$DBNAME.done
                                                mv $file1a $file1b
                                        else
                                        cat /tmp/massage.log | mailx -s "FAILED:Backup(Refresh)NZ $DBNAME $BKTP Database Backup on  $HOST " -a $BKP_LOG_DIR/$LOGFILE $DBA
                                        mv $file1a $file1c
                                        fi
                        fi
                        else
                        echo "Triiger file does not exist `date`" > $LOG
                        exit;
                        fi
    else
     echo "$i Database does not exist on $HOST"| mailx -s "Notification:$DB_TYPE  NZ $i  Database Backup failed on  $HOST " $DBA
    fi
else
echo "Please specify database name " >>$LOG
exit 1;

fi

################################################################################

Incremental Restore Script to Refresh 

################################################################################

Crontab Entry 

/export/home/nz/scripts/refresh/incr_restore_refresh.sh PMACDW 

#############################################################################

#!/bin/sh

##Pass Database name with space

cd /export/home/nz

. ~/.bashrc

export PATH=/nz/kit/bin:/nz/kit/sbin:/nz/kit/bin/adm:/bin:/usr/bin:/usr/local/bin:.:/nz/support/bin

email_list="EDW_Support@gmail.com"

DBA="NZRed@gmail.com,EDW_Support@gmail.com"

HOST=`hostname`

if [ $# > 0 ]; then

    i=$1

    DBNAME=$(nzsql -l|grep -w $i |awk '{ print $i}'|cut -d '|' -f1|cut -d " " -f2)

    if [ $i = "$DBNAME" ]; then

                DBNAME=$i

                DB=$DBNAME

                DB=`echo "${DB,,}"`

                BKTP="INCR:REST"

                DB_TYPE=DEV

                LOG_DIR=/export/home/nz/scripts/log

                LOG=$LOG_DIR/$DBNAME_restore_`hostname`_`date +%y%m%d%H%M%S`.log

                RST_LOG_DIR=/nz/kit/log/restoresvr/

               file1=/dbbackup/refresh_trigger/bnr/.incr_bkups_$DBNAME.done

               file1a=/dbbackup/refresh_trigger/bnr/.incr_restore_$DBNAME.wip

               file1b=/dbbackup/refresh_trigger/bnr/incr_restore_$DBNAME.done

               file1c=/export/home/nz/dba/scripts/bnr/.incr_restore_$DBNAME.abort

                ########checking logfile status

                if [ -f "$file1" ]; then

                echo " Initiate incremental restore - trigger file available" >> $LOG

                mv $file1 $file1a

                echo "Starting incremental restore of Database $DBNAME" >>$LOG

                 #######Restore

                NOW=$(date +"%Y-%m-%d")

                nzrestore -db $DBNAME -sourcedb $DBNAME  -connector netbackup -connectorArgs DATASTORE_SERVER=edp-nbu-120-ap  -streams 8 -v  -npshost 7836279-H1 -increment REST -lockdb T >>$LOG

                        if [ $? -ne 0 ]; then

                              echo "FAILED:$BKTP Refresh $DBNAME Database on $HOST "| mailx -s "FAILED:$BKTP Refresh NZ  $DBNAME $BKTP Database Restore on  $HOST " -a $LOG $DBA

                              mv -f $file1a $file1c

                        else

                                RSTSTS=$(nzsql  -qc  "SELECT  status  FROM  _v_Restore_history     WHERE   optype='${BKTP}'    AND     dbname = '${DBNAME}'  AND     starttime = (SELECT MAX(starttime) FROM _v_Restore_history where OPTYPE='${BKTP}' and DBNAME = '${DBNAME}');")

                                RSTLOG=$(nzsql  -qc  "SELECT  logfile  FROM  _v_Restore_history     WHERE   optype='${BKTP}'    AND     dbname = '${DBNAME}'  AND     starttime = (SELECT MAX(starttime) FROM _v_Restore_history where OPTYPE='${BKTP}' and DBNAME = '${DBNAME}');")

                                STATUS=`echo $RSTSTS|cut -d " " -f3`

                                LOGFILE=`echo $RSTLOG|cut -d " " -f3`

                                cd $RST_LOG_DIR

                                RST_DT=`echo $LOGFILE|cut -d "." -f3`

                                echo $RST_DT

                                        if [ $RST_DT = $NOW ] && [ $STATUS = "COMPLETED" ]; then

                                                cat $RST_LOG_DIR/$LOGFILE | mailx -s "SUCCESS:$BKTP Refresh NZ  $DBNAME Database  on  $HOST " $email_list

                                             #   /bin/touch /dbbackup/refresh_trigger/bnr/.full_bkups_$DBNAME.done

                                                mv -f $file1a $file1b

                                        else

                              echo "FAILED:$BKTP Refresh $DBNAME Database on $HOST "| mailx -s "FAILED:$BKTP NZ Refresh $DBNAME Database Restore on  $HOST " -a $RST_LOG_DIR/$LOGFILE $DBA

                                        mv -f $file1a $file1c

                                        fi

                        fi

                        else

                        echo "Triiger file does not exist `date`" > $LOG

                        exit;

                        fi

    else

     echo "$i Database does not exist on $HOST"| mailx -s "Notification:$DB_TYPE  NZ $i  Database Restore failed on  $HOST " $DBA

    fi

else

echo "Please specify database name " >>$LOG

exit 1;

fi

Netezza Database refresh from prod to Dev --- Full Refresh nzbackup nzrestore

1. Using backup scirpt it will take backup of database specified after trigger file found and it will create a trigger file on dev to start restore PMACDW  database.

crontab Entry try every 15 min and wait for trigger file

00,15,30,45 4-10 * * 6 /export/home/nz/scripts/refresh/full_backup_refresh.sh PGCIDWSEC   > /dev/null 2>&1
#############################Full Backup script#################################

#!/bin/sh
##Pass Database name with space
cd /export/home/nz
. ~/.bashrc
export PATH=/nz/kit/bin:/nz/kit/sbin:/nz/kit/bin/adm:/bin:/usr/bin:/usr/local/bin:.:/nz/support/bin
email_list="EDW_Support@gmail.com"
DBA="NZRed@gmail.com,EDW_Support@gmail.com"
echo "Please call DBA , If It's not acknowledged by DBA" > /tmp/massage.log
HOST=`hostname`
if [ $# > 0 ]; then
    i=$1
    DBNAME=$(nzsql -l|grep -w $i |awk '{ print $i}'|cut -d '|' -f1|cut -d " " -f2)
    if [ $i = "$DBNAME" ]; then
                DBNAME=$i
                DB=$DBNAME
                DB=`echo "${DB,,}"| cut -c 2-`
                BKTP=FULL
                DB_TYPE=PROD
                LOG_DIR=/export/home/nz/scripts/log
                LOG=$LOG_DIR/$DBNAME_backup_`hostname`_`date +%y%m%d%H%M%S`.log
                BKP_LOG_DIR=/nz/kit/log/backupsvr/
                file1=/nz/$DB/triggers/$DB.done
                file1a=/nz/$DB/triggers/$DB.wip
                file1b=/nz/$DB/triggers/$DB.bkup_done
                file1c=/nz/$DB/triggers/$DB.bkup_aborted
                ########checking logfile status
                if [ -f "$file1" ]; then
                        echo " Initiate incremental backup - trigger file available" > $LOG
                        mv $file1 $file1a
                        echo "Starting Full backup of Database $DBNAME" >>$LOG
                 #######Backup
                NOW=$(date +"%Y-%m-%d")
                nzbackup -u admin -db $DBNAME -connector netbackup -connectorArgs "DATASTORE_SERVER=edp-nbu-120-ap:DATASTORE_POLICY=phx_netezza"  -streams 8 -v  >>$LOG
                        if [ $? -ne 0 ]; then
                                cat /tmp/massage.log | mailx -s "FAILED:Backup(Refresh) NZ  $DBNAME $BKTP Database Backup on  $HOST " -a $LOG $DBA
                                                                mv $file1 $file1c
                        else
                                BKPSTS=$(nzsql  -qc  "SELECT  status  FROM  _v_backup_history     WHERE   optype='${BKTP}'    AND     dbname = '${DBNAME}'  AND     starttime = (SELECT MAX(starttime) FROM _v_backup_history where OPTYPE='${BKTP}' and DBNAME = '${DBNAME}');")
                                BKPLOG=$(nzsql  -qc  "SELECT  logfile  FROM  _v_backup_history     WHERE   optype='${BKTP}'    AND     dbname = '${DBNAME}'  AND     starttime = (SELECT MAX(starttime) FROM _v_backup_history where OPTYPE='${BKTP}' and DBNAME = '${DBNAME}');")
                                STATUS=`echo $BKPSTS|cut -d " " -f3`
                                LOGFILE=`echo $BKPLOG|cut -d " " -f3`
                                cd $BKP_LOG_DIR
                                BKP_DT=`echo $LOGFILE|cut -d "." -f3`
                                echo $BKP_DT
                                        if [ $BKP_DT = $NOW ] && [ $STATUS = "COMPLETED" ]; then
                                                cat $BKP_LOG_DIR/$LOGFILE | mailx -s "SUCCESS:$DB_TYPE NZ  $DBNAME $BKTP Database Backup on  $HOST " $email_list
                                                /bin/touch /dbbackup/refresh_trigger/bnr/.full_bkups_$DBNAME.done
                                                mv $file1a $file1b
                                        else
                                        cat /tmp/massage.log | mailx -s "FAILED:Backup(Refresh)NZ $DBNAME $BKTP Database Backup on  $HOST " -a $BKP_LOG_DIR/$LOGFILE $DBA
                                        mv $file1a $file1c
                                        fi
                        fi
                        else
                        echo "Triiger file does not exist `date`" > $LOG
                        exit;
                        fi
    else
     echo "$i Database does not exist on $HOST"| mailx -s "Notification:$DB_TYPE  NZ $i  Database Backup failed on  $HOST " $DBA
    fi
else
echo "Please specify database name " >>$LOG
exit 1;
fi

################################################################################

2. After we get the trigger file on dev it will hit Full restore on DEV for PMACDW  database.

crontab 

00,15,30,45 5-11 * * 6 /export/home/nz/scripts/refresh/full_restore_refresh.sh PMACDW >/dev/null 2>&1

###############################Full Restore script ################################

#!/bin/sh

##Pass Database name with space

cd /export/home/nz

. ~/.bashrc

export PATH=/nz/kit/bin:/nz/kit/sbin:/nz/kit/bin/adm:/bin:/usr/bin:/usr/local/bin:.:/nz/support/bin

email_list="EDW_Support@gmail.com"

DBA="NZRed@gmail.com,EDW_Support@gmail.com"

HOST=`hostname`

if [ $# > 0 ]; then

    i=$1

    DBNAME=$(nzsql -l|grep -w $i |awk '{ print $i}'|cut -d '|' -f1|cut -d " " -f2)

    if [ $i = "$DBNAME" ]; then

                DBNAME=$i

                DB=$DBNAME

                DB=`echo "${DB,,}"`

                BKTP=FULL

                DB_TYPE=DEV

                LOG_DIR=/export/home/nz/scripts/log

                LOG=$LOG_DIR/$DBNAME_restore_`hostname`_`date +%y%m%d%H%M%S`.log

                RST_LOG_DIR=/nz/kit/log/restoresvr/

               file1=/dbbackup/refresh_trigger/bnr/.full_bkups_$DBNAME.done

               file1a=/dbbackup/refresh_trigger/bnr/.full_restore_$DBNAME.wip

               file1b=/dbbackup/refresh_trigger/bnr/.full_restore_$DBNAME.done

               file1c=/export/home/nz/dba/scripts/bnr/.full_restore_$DBNAME.abort

                ########checking logfile status

                if [ -f "$file1" ]; then

                ##This is to capture user sessions during LQC full restoration

                nzsession | grep $DBNAME  > /export/home/nz/cap_session

                #cat /export/home/nz/cap_session | mail -s "List of EDW-NTZ-LQC on $DBNAME user session on `date` " $email_list

                #End of user tracing during full restore set2

                # Kill users connected to dbs to be dropped

                for killpid in `nzsession | grep $DBNAME | awk '{print $1}'`; do

                echo "PID killed"

                echo $killpid

                nzsession abort -id ${killpid} -force

                done

                # Drop  database $DBNAME before restoring new ones

                /nz/kit/bin/nzsql < /export/home/nz/dba/scripts/bnr/dropdbs_$DBNAME.sql

                 if [ $? -ne 0 ]; then

                echo "DATABASE $DBNAME DROP COMMAND FAILED CAUSES EXISTING REFRESH,It will try again after 15 min."|mailx -s "FAILED:$BKTP Refresh NZ $DBNAME $BKTP Database Restore on $HOST"  $DBA

                exit;

                else

                 echo "DATABASE DROPPED"

                 fi

                echo " Initiate FULL restore - trigger file available" >> $LOG

                mv $file1 $file1a

                echo "Starting FULL restore of Database $DBNAME" >>$LOG

                 #######Restore

                NOW=$(date +"%Y-%m-%d")

                nzrestore -db $DBNAME -sourcedb $DBNAME  -connector netbackup -connectorArgs DATASTORE_SERVER=edp-nbu-120-ap  -streams 8 -v  -npshost 7836279-H1 -lockdb T >>$LOG

                        if [ $? -ne 0 ]; then

                            echo " FAILED:$BKTP Refresh NZ DB  $DBNAME  on  $HOST"| mailx -s "FAILED:$BKTP Refresh NZ DB $DBNAME  on  $HOST " -a $LOG $DBA

                            mv -f $file1a $file1c

                        else

                                RSTSTS=$(nzsql  -qc  "SELECT  status  FROM  _v_Restore_history     WHERE   optype='${BKTP}'    AND     dbname = '${DBNAME}'  AND     starttime = (SELECT MAX(starttime) FROM _v_Restore_history where OPTYPE='${BKTP}' and DBNAME = '${DBNAME}');")

                                RSTLOG=$(nzsql  -qc  "SELECT  logfile  FROM  _v_Restore_history     WHERE   optype='${BKTP}'    AND     dbname = '${DBNAME}'  AND     starttime = (SELECT MAX(starttime) FROM _v_Restore_history where OPTYPE='${BKTP}' and DBNAME = '${DBNAME}');")

                                STATUS=`echo $RSTSTS|cut -d " " -f3`

                                LOGFILE=`echo $RSTLOG|cut -d " " -f3`

                                cd $RST_LOG_DIR

                                RST_DT=`echo $LOGFILE|cut -d "." -f3`

                                echo $RST_DT

                                   if [ $RST_DT = $NOW ] && [ $STATUS = "COMPLETED" ]; then

                                   cat $RST_LOG_DIR/$LOGFILE | mailx -s "SUCCESS:$BKTP Refresh NZ  $DBNAME Database  on  $HOST " $email_list

                                   #/bin/touch /dbbackup/refresh_trigger/bnr/.full_bkups_$DBNAME.done

                                   mv -f  $file1a $file1b

                                   else

                                   echo "FAILED:$BKTP NZ Refresh DB $DBNAME on  $HOST " | mailx -s "FAILED:$BKTP NZ Refresh DB $DBNAME on  $HOST " -a $RST_LOG_DIR/$LOGFILE $DBA

                                   mv -f $file1a $file1c

                                   fi

                        fi

                        else

                        echo "Triiger file does not exist `date`" > $LOG

                        exit;

                        fi

    else

     echo "$i Database does not exist on $HOST"| mailx -s "Notification:$DB_TYPE  NZ $i  Database Restore failed on  $HOST " $DBA

    fi

else

echo "Please specify database name " >>$LOG

exit 1;

fi

Shell Script Incremental Backup on netezza Puredata IBM nzrestore

#Shell Script Incremental Backup on netezza Puredata IBM

#1* * * * /export/home/nz/scripts/refresh/incr_backup_refresh.sh PMACDW

#!/bin/sh
##Pass Database name with space
email_list="NZRed@gmail.com,GCIT-PHXNOC@gmail.com"
DBA="NZyellow@gmail.com"
cd /export/home/nz
. ~/.bashrc
export PATH=/nz/kit/bin:/nz/kit/sbin:/nz/kit/bin/adm:/bin:/usr/bin:/usr/local/bin:.:/nz/support/bin
HOST=`hostname`
echo "Please call DBA on call, If It's not acknowledged by DBA" > /tmp/massage.log
if [ $# > 0 ]; then
    for i in "$@"
        do
    DBNAME=$(nzsql -l|grep -w $i |awk '{ print $i}'|cut -d '|' -f1)
    if [ $i = $DBNAME ]; then
                DBNAME=$i
                BKTP=DIFF
                DB_TYPE=PROD
                LOG_DIR=/export/home/nz/scripts/log
                LOG=$LOG_DIR/$i_backup_`hostname`_`date +%y%m%d%H%M%S`.log
                BKP_LOG_DIR=/nz/kit/log/backupsvr/
        echo "Starting Incremental  backup of Database $DBNAME on `date +%y%m%d%H%M%S`" >$LOG
        #######Backup
          NOW=$(date +"%Y-%m-%d")
          nzbackup -u admin -db $DBNAME -connector netbackup -connectorArgs "DATASTORE_SERVER=edp-nbu-120-ap:DATASTORE_POLICY=phx_netezza" -differential -streams 8 -v >>$LOG
                        if [ $? -ne 0 ]; then
                                cat /tmp/massage.log | mailx -s "FAILED:$DB_TYPE NZ  $DBNAME $BKTP Database Backup on  $HOST " -a $LOG $email_list
                        else
                                BKPSTS=$(nzsql  -qc  "SELECT  status  FROM  _v_backup_history     WHERE   optype='${BKTP}'    AND     dbname = '${DBNAME}'  AND     starttime = (SELECT MAX(starttime) FROM _v_backup_history where OPTYPE='${BKTP}' and DBNAME = '${DBNAME}');")
                                BKPLOG=$(nzsql  -qc  "SELECT  logfile  FROM  _v_backup_history     WHERE   optype='${BKTP}'    AND     dbname = '${DBNAME}'  AND     starttime = (SELECT MAX(starttime) FROM _v_backup_history where OPTYPE='${BKTP}' and DBNAME = '${DBNAME}');")
                                STATUS=`echo $BKPSTS|cut -d " " -f3`
                                LOGFILE=`echo $BKPLOG|cut -d " " -f3`
                                cd $BKP_LOG_DIR
                                BKP_DT=`echo $LOGFILE|cut -d "." -f3`
                                echo $BKP_DT
                                        if [ $BKP_DT = $NOW ] && [ $STATUS = "COMPLETED" ]; then
                                                #cat $BKP_LOG_DIR/$LOGFILE | mailx -s "SUCCESS:$DB_TYPE NZ  $DBNAME Incremental Database Backup on  $HOST " $email_list
                                                echo "Backup Success"
                                        else
                                        cat /tmp/massage.log | mailx -s "FAILED:$DB_TYPE NZ $DBNAME Incremental Database Backup on  $HOST " -a $BKP_LOG_DIR/$LOGFILE $email_list
                                        fi
                        fi
    else
     echo "$i Database does not exist on $HOST"| mailx -s "Notification:$DB_TYPE  NZ $i  Database Incremental Backup failed on  $HOST " $DBA
    fi
    done
else
echo "Please specify database name " >>$LOG
exit 1;
fi

Netezza Full Backup on Puredata nzbackup

#Shell script for Netezza Full Backup on  Puredata
##Full database Backup  crontab entry
30 15 * * 1 /scripts/NZfullbackup.sh PCTGDM > /export/home/nz/scripts/log/NZfullbackup.log  2>&1

####################################################################

#!/bin/sh

##Pass Database name with space

cd /export/home/nz

. ~/.bashrc

export PATH=/nz/kit/bin:/nz/kit/sbin:/nz/kit/bin/adm:/bin:/usr/bin:/usr/local/bin:.:/nz/support/bin

email_list="NZRed@gmail.com,GCIT-PHXNOC@gmail.com"

DBA="NZyellow@gmail.com"

echo "Please call DBA on call, If It's not acknowledged by DBA" > /tmp/massage.log

HOST=`hostname`

if [ $# > 0 ]; then

    for i in "$@"

        do

    DBNAME=$(nzsql -l|grep -w $i |awk '{ print $i}'|cut -d '|' -f1)

    if [ $i = $DBNAME ]; then

                DBNAME=$i

                BKTP=FULL

                DB_TYPE=PROD

                LOG_DIR=/export/home/nz/scripts/log

                LOG=$LOG_DIR/$i_backup_`hostname`_`date +%y%m%d%H%M%S`.log

                BKP_LOG_DIR=/nz/kit/log/backupsvr/

        echo "Starting Full backup of Database $DBNAME" >$LOG

        #######Backup

                NOW=$(date +"%Y-%m-%d")

                nzbackup -u admin -db $DBNAME -connector netbackup -connectorArgs "DATASTORE_SERVER=edp-nbu-120-ap:DATASTORE_POLICY=phx_netezza"  -streams 8 -v  >>$LOG

                        if [ $? -ne 0 ]; then

                                cat /tmp/massage.log | mailx -s "FAILED:$DB_TYPE NZ  $DBNAME $BKTP Database Backup on  $HOST " -a $LOG $email_list

                        else

                                BKPSTS=$(nzsql  -qc  "SELECT  status  FROM  _v_backup_history     WHERE   optype='${BKTP}'    AND     dbname = '${DBNAME}'  AND     starttime = (SELECT MAX(starttime) FROM _v_backup_history where OPTYPE='${BKTP}' and DBNAME = '${DBNAME}');")

                                BKPLOG=$(nzsql  -qc  "SELECT  logfile  FROM  _v_backup_history     WHERE   optype='${BKTP}'    AND     dbname = '${DBNAME}'  AND     starttime = (SELECT MAX(starttime) FROM _v_backup_history where OPTYPE='${BKTP}' and DBNAME = '${DBNAME}');")

                                STATUS=`echo $BKPSTS|cut -d " " -f3`

                                LOGFILE=`echo $BKPLOG|cut -d " " -f3`

                                cd $BKP_LOG_DIR

                                BKP_DT=`echo $LOGFILE|cut -d "." -f3`

                                echo $BKP_DT

                                        if [ $BKP_DT = $NOW ] && [ $STATUS = "COMPLETED" ]; then

                                                #cat $BKP_LOG_DIR/$LOGFILE | mailx -s "SUCCESS:$DB_TYPE NZ  $DBNAME $BKTP Database Backup on  $HOST " $email_list

                                                 echo "Backup Success"

                                        else

                                        cat /tmp/massage.log | mailx -s "FAILED:$DB_TYPE NZ $DBNAME FULL Database Backup on  $HOST " -a $BKP_LOG_DIR/$LOGFILE $email_list

                                        fi

                        fi

    else

     echo "$i Database does not exist on $HOST"| mailx -s "Notification:$DB_TYPE  NZ $i  Database Backup failed on  $HOST " $DBA

    fi

    done

else

echo "Please specify database name " >>$LOG

exit 1;

fi

Shell script to Netezza System status (NPS) IBM Netezza

#Shell script to Netezza System status (NPS) IBM Netezza
#Manish Jaiswal
#!/bin/sh
# =============================================================================
# Netezza  NPS Status
# =============================================================================
# Script configuration - Static values only.
# =============================================================================
cd /export/home/nz
. ~/.bashrc
export PATH=/nz/kit/bin:/nz/kit/sbin:/nz/kit/bin/adm:/bin:/usr/bin:/usr/local/bin:.:/nz/support/bin
NPS_STATE=`nzstate | awk '{print $4}' | tr -d "[.']" | tr "[a-z]" "[A-Z]"`
# ------------------------------------------------------------------------
# Make sure the system is online.
# ------------------------------------------------------------------------
if [ "${NPS_STATE}" = "ONLINE" ]
then
   echo "${NPS_STATE}"
        SYSTEMSTATE=0
else
   echo "NPS ${NPS_STATE} OFFLINE During Hostbackup!"
   echo  " NPS is Offline on  $HOST in Current state: [${NPS_STATE}]"|mailx -s "CRITICAL:NZHOSTBACKUP  on  $HOST " $email_list
   exit;
fi