SQL_ON_ERROR

The SQL_ON_ERROR script element determines the reaction to SQL errors. By default, jobs abort if an SQL error occurs. You can use this script element to change this behavior. The specification that you make with SQL_ON_ERROR applies for all subsequent SQL statements until the job ends or until the next SQL_ON_ERROR statement is used.

Important! All SQL statements until the last COMMIT are canceled if the job aborts. This means that all SQL statements are undone if the job does not contain a COMMIT.

Syntax

SQL_ON_ERROR

ACTION=...

Parameters

SQL_ON_ERROR
Determines the reaction to SQL errors
ACTION=
Defines the reaction to SQL errors. The value must be put in quotation marks.
Allowed values:
- ABEND (default)
  The job aborts with return code 1 when the first SQL error occurs. Subsequent SQL statements are not processed.
- RESUME
  The job continues if SQL errors occur.

Examples

The second INSERT statement results in an error because the table name contains a typing error. Regardless of this error, the agent continues the job and writes the data record for Mr. Brown to the database.

SQL_ON_ERROR ACTION="RESUME";
insert into person values (1,'Smith');
insert into person values (2,'Brown');
insert into person values (3,'Spencer');

In the following example, the agent cancels the job because the table name is wrong. As a result of the COMMIT command, the data-record entry for Mr. Smith is kept. Because of the fact that the job is canceled with the second INSERT statement, the data record for Mr. Spencer is not considered although it is syntactically correct.

SQL_ON_ERROR ACTION="ABEND";
insert into person values (1,'Smith');
COMMIT;
insert into person values (2,'Brown');
insert into person values (3,'Spencer');