Archive for February, 2015

Lets create a Profile file to pass all the values to the script.

Profile File:- (deceleration)

PATH_LIB_ODBC=”$PATH:/e/infa91/Netezza/bin”
LIB_PATH=”$LIBPATH:/e/infa91/Netezza/lib64″
NZ_ODBC_INI_PATH=”/e/infa/ODBC/ODBC6.1″

HOME_PATH=”/e/infa91/Netezza/bin”

PROFILE_DIR=”/e/deployment/scripts/GBT/GBTSCR”
QUER_PATH=”/e/deployment/scripts/GBT/GBTSCR”
SRC_PATH=”/e/srcfiles/GBT”
LOG_DIR=”/e/logfiles/GBGT”
BAD_DIR=”/e/badfiles”
HOST=”Your Host”
USER_NM=”USER”
USR_PASS=”PASS”
STG_DB=”DB”

STG_SCHEMA=CMPLNTSST
WH_SCHEMA=CMPLNTSDW

Actual Script to Load File to Netezza.

#! /bin/ksh

TABLE_NM=$1                 Table to which the data to be loaded
DELIMIT=$2                     The delimiter of the file   
SRC_FILE=$3                   File name to be loaded

#  Pass the parameter at run time (Ex:- sh -x Srcript_name “My_Table_name” “|” “Src File”)

. /A/bimisc/deployment/scripts/G/GR/GB_NETEZZA.Profile

# Run the profile so that all the parameter values will be picked from it

if [[ $? -ne 0 ]]
then
{
echo “GBT_Param.txt param file execution failed \n”
exit;
}
else
{
echo “GBT_Param.txt executed successfully \n”
}
fi
#*************************
#umask 002

PATH=$PATH_LIB_ODBC                               
LIBPATH=$LIB_PATH
NZ_ODBC_INI_PATH=$NZ_ODBC_INI_PATH
export PATH
export LIBPATH
export NZ_ODBC_INI_PATH

#*************************

cd $HOME_PATH

nzload -host $HOST -u $USER_NM -pw $USR_PASS -db $STG_DB -schema $WH_SCHEMA -t $TABLE_NM -delim $DELIMIT -df $SRC_PATH/$SRC_FILE -lf $LOG_DIR\Error.txt

# All the parameters will be replaced with the value in Profile file

\echo $?

if [[ $? -ne 0 ]]
then
{  echo “The nzload script has successfully executed \n”
exit;
}
else
{ echo “The nzload script has faild please check /a/logfiles/GBGT Log Directory for more information on the error  \n”
}
fi

Netezza nzload options that we used in the above script are as folllows

Option Description
-df filename Specifies the data file to load. If you do not specify a path, the system uses the special token <stdin> to store the file path string. Corresponds to the DataObject external table option.
-lf filename Specifies the log file name. If the file exists, append to it.
-bf filename Specifies the bad or rejected rows file name (overwrite if the file exists).
-outputDir dir Specifies the output directory for the log and bad or rejected rows files. Corresponds to the LogDir external table option.
-u user Specifies the database user name [NZ_USER].
-pw password Specifies the password of the Netezza® user [NZ_PASSWORD].
-host name Specifies the host name or IP address [NZ_HOST]. Runs on the local host if not specified here. If you set this option to any name but localhost or any IP address but the reserved one (127.0.0.1), the system sets the remotesource option to ODBC.
-db database Specifies the database to load [NZ_DATABASE].
-schema schema For a Netezza system that supports multiple schemas in a database, this option specifies the schema in which to load the table. If you do not specify the -schema option, the system uses the value of the NZ_SCHEMA environment variable. If NZ_SCHEMA is not set, the system uses the default schema for the database.
-t table Specifies the table name. You can specify a fully qualified name for this value.
Advertisements