In this article, Netezza nzsql exit codes are discussed. These exit codes are very much important when you are building ETL/ELT operation using shell scripts.
On UNIX/Linux systems such as the NPS, the Netezza nzsql exit codes provide information about the execution status of the SQL commands.
Read:
Netezza nzsql Exit Codes
When you are running queries interactively at the nzsql prompt or by using a script (nzsql -f), the command returns one of the following exit codes:
- 0: All queries were successful.
- 1: At least one query was successful.
- 2: All queries failed.
Netezza nzsql Exit Codes Example
For example, to check the Netezza nzsql exit codes, write nzsql commands in the file and check the exit code by displaying content of ‘$?’
[user1@netezza ~]$ nzsql -f test_file.sql nzsql:test_file.sql:1: ERROR: Relation ’Test’ does not exist [user1@netezza ~]$ echo $? 2
There are different exit codes when you are running the nzsql command with option -c. Below are the exit codes with nzsql option -c:
- 0 : Query succeeded
- -1 or 255 : SQL failed (issues with syntax, failed authentication, permissions)
- -2 or 254 : Connection failed
- -3 or 253 : User cancellation request
- -4 or 252 : Session was terminated
For Example,
[user1@netezza ~]$ nzsql -c “select * from test” ERROR: Relation ’test’ does not exist [user1@netezza ~]$ echo $? 255
ON_ERROR_STOP
nzsql command with option -f executes all sql statements in the files. If you want the nzsql command to stop and exit when it encounters an error with a query, include -v ON_ERROR_STOP=1 on the command line. Here more information on Netezza ON_ERROR_STOP
If you create an ON_ERROR_STOP block inside a query file, as in this example:
<initial SQL queries> \set ON_ERROR_STOP ON <queries inside block> \unset ON_ERROR_STOP <trailing SQL queries>
The success or failure of queries in the initial SQL command section or the trailing
SQL command section are ignored when there is an ON_ERROR_STOP block. The
success or failure of the commands inside the block determine the exit value