Automation Engine Script Guide > AE JCL for SQL > SQL_ON_ERROR

SQL_ON_ERROR

Determines the reaction to SQL errors.

Syntax

SQL_ON_ERROR ACTION=...

Syntax

Description/Format

ACTION=

Reaction to SQL errors.

Allowed values: ABEND (default value) and RESUME

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.

The value must be put in inverted commas.

 RESUME is used if this parameter is not specified.

Comments

By default, jobs abort if an SQL error occurs. This script element can be used to handle this behavior.

The specification that is made with SQL_ON_ERROR applies for all subsequent SQL statements until the job ends or until the next SQL_ON_ERROR statement is used.

Note that 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.

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 databaseA database is an organized collection of data including relevant data structures..

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, 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');

 

See also: