How to execute unix commands within nzsql

netezzascriptingteradataunix

I'm converting a script from Teradata to Netezza, and I'm stuck at the portion where BTEQ uses the functionality of the OS to check if a file contains any data. If it does, it is deleted. I want to perform the same function in nzsql.

Can anyone please help?

Best Answer

The documentation for nzsql states that to shell-out you just have to use blackslash pling, followed by the command:

\! command

If you need to know how to test for file size in a shell script, you can use something similar to:

if [[ -f $file ]] && [[ -s $file ]]; then 
  echo "File exists and is greater than 0 bytes" ; 
  rm -f $file
fi

-f checks that it exists, -s checks if it's greater than 0 bytes.

exporting it to the environment:

NZ_PASSWD_ENCRYPTED=29TY20T98=
export NZ_PASSWORD=`DecryptFunc $NZ_PASSWD_ENCRYPTED`
NzResult=$(nzsql -db ${NZ_DATABASE}  -u ${NZ_USER} -h $NZ_HOST \
              -qc "select 'hi there folks;'")
unset NZ_PASSWORD

Anyone who can read the environment variables set for your shell can still retrieve the password, but it won't show in the process list visible to everyone on the system

putting it in a script:

put the query in a sql file, add something like this at the top of it:

\set STOP_ON_ERROR TRUE
\echo *** connecting to MY_DATABASE ***
\connect MY_DATABASE MY_USER MY_PASSWORD
select 'hi there folks;'

Then use nzsql to execute this file. You may need to specify a login user to nzsql as well, but any user should do (even one that can only has no other rights besides log in)
Anyone who can read the sql file will be able to see the password, but I don't think it will show up anywhere else

using nzpassword

There is a tool to store passwords encrypted in a hidden ~/.nzpassword file, for later use, with:

nzpassword add -u user -pw password -host hostname

see: http://www-01.ibm.com/support/knowledgecenter/SSULQD_7.1.0/com.ibm.nz.adm.doc/c_sysadm_encrypted_passwords.html

How to split a large table in Teradata

You can use the QUALIFY statement to restrict the number of rows against a large table.

SELECT CUSTOMERID
     , FULLNAME
  FROM MYDB.LARGE_TABLE AS MYTBL
QUALIFY ROW_NUMBER() OVER (ORDER BY CUSTOMERID) 
        BETWEEN 1 and 30000000;

edit - corrected syntax of ROW_NUMBER()

Best Answer

Related Solutions

Netezza Scripting – Securely Call nzsql or nzload Without Disclosing Password

exporting it to the environment:

putting it in a script:

using nzpassword

How to split a large table in Teradata

Related Question