Netezza SET ON_ERROR_STOP and Examples

When you are working on Netezza data warehouse (IBM Puredata Systems) appliance, you will be executing multiple SQL statement in single SQL file. In this article, we will discuss about the Netezza SET ON_ERROR_STOP variable to stop the execution in case one of the sql statement encountered error inside script. Without this variable, Netezza will continue with other statements ignoring the failed query.

Netezza SET ON_ERROR_STOP

Netezza 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 or set the ON_ERROR_STOP variable inside the sql query that you are going to run.

Read:

• Netezza nzsql exit codes for Unix/Linux Systems
• Netezza nzsql commands and is Usage
• IBM Netezza nzsql Windows System Exit Codes
• Netezza nzload commands and its usage

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>

\unset 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

Netezza SET ON_ERROR_STOP Example

Let’s check how ON_ERRO_STOP with an examples.

Below is the cat putput of my test.sql file:

\set ON_ERROR_STOP ON; 
select * from PATIENT_1; 
select * from PATIENT_2;

-- This table is does not exists in the database so Netezza will stop execution at this poin
select * from PATIENT_23; 
select * from PATIENT_1; 
\unset ON_ERROR_STOP

Lets execute the file and see how netezza behaves:

[nz@netezza ~]$ nzsql -db TRAINING -e -f test.sql 
select * from PATIENT_1; 
 ID | NAME 
----+------ 
 1 | ABC 
 3 | CDE 
 5 | EFG 
 2 | BCD 
 4 | DEF 
(5 rows)

select * from PATIENT_2; 
 ID | NAME 
----+------ 
 2 | BCD 
 6 | FGH 
 1 | ABC 
 7 | GHI 
(4 rows)

select * from PATIENT_23; 
nzsql:test.sql:6: ERROR: relation does not exist TRAINING.ADMIN.PATIENT_23 
 -- This table is not exists in the database so Netezza will stop execution 
[nz@netezza ~]$