SQL_ON_ERROR

Use the SQL_ON_ERROR script element to determine the reaction to SQL errors. By default, jobs abort when an SQL error occurs; you can use this script element to change that behavior. The setting applies to all subsequent SQL statements until the job ends, or until the next SQL_ON_ERROR statement.

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

Syntax

SQL_ON_ERROR ACTION=...

Parameters

Parameter	Description	Allowed Values	Default Value
ACTION=	Defines the reaction to SQL errors. The value must be enclosed in quotation marks.	ABEND — 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.	ABEND

Examples

The second INSERT statement results in an error because the table name contains a typo. The agent continues the job regardless 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 the job is canceled at the second INSERT statement, the data record for Mr. Spencer is not processed, 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');