SQL_ON_ERROR

AE JCL for SQL

SQL_SET_STATEMENT_TERMINATOR

---

SQL_ON_ROWCOUNT_ZERO

Determines the return code if an SQL statement does not supply a hit.

Syntax

SQL_ON_ROWCOUNT_ZERO RETCODE=...

Comments

This script element can be used to cancel a job with a defined return code if an SQL statement such as SELECT or UPDATE does not return any hits. The specification made with SQL_ON_ROWCOUNT_ZERO applies for all subsequent SQL statements until the job ends or until the next SQL_ON_ROWCOUNT_ZERO statement.

Specify the RETCODE '0'; the number of hits that are found in SQL statements are ignored and the job continues.

SQL_ON_ROWCOUNT_ZERO can only be used for SELECT, UPDATE, INSERT and DELETE. Other SQL statements such as CREATE TABLE never supply hits. Using lSQL_ON_ROWCOUNT_ZERO in a job always requires RETCODE being set to 0 before the particular SQL statements are used.

All SQL statements until the last COMMIT are undone if the job is canceled.

Examples

The job is canceled and returns code 1 if one of the three SQL statements returns 0 hits.

SQL_ON_ROWCOUNT_ZERO RETCODE=1;
select * from person;
insert into person values (1,'Smith');
update person set address = '403 E. Main Street'
where name = 'Spencer';

See also:

AE JCL for SQL