SET keyword in the ON EXCEPTION statement of SPL

If you include an SQL statement that begins with the keyword SET in the ON EXCEPTION statement, you must enclose it in a BEGIN ... END statement block.

The following list shows some of the SQL statements that begin with the keyword SET:
  • SET AUTOFREE
  • SET CONNECTION
  • SET CONSTRAINTS
  • SET DATASKIP
  • SET DEBUG FILE
  • SET DEFERRED_PREPARE
  • SET DESCRIPTOR
  • SET ENCRYPTION
  • SET ENVIRONMENT
  • SET EXPLAIN
  • SET INDEXES
  • SET ISOLATION
  • SET LOCK MODE
  • SET LOG
  • SET OPTIMIZATION
  • SET PDQPRIORITY
  • SET PLOAD FILE
  • SET ROLE
  • SET STATEMENT CACHE
  • SET TABLE
  • SET TRANSACTION
  • SET TRIGGERS

The following examples show the incorrect and correct use of a SET LOCK MODE statement inside an ON EXCEPTION statement.

The following ON EXCEPTION statement returns an error because the SET LOCK MODE statement is not enclosed in a BEGIN ... END statement block: 
ON EXCEPTION IN (-107)
   SET LOCK MODE TO WAIT; -- error, value expected, not 'lock'
END EXCEPTION;
The following ON EXCEPTION statement executes successfully because the SET LOCK MODE statement is enclosed in a BEGIN ... END statement block: 
ON EXCEPTION IN (-107)
   BEGIN
   SET LOCK MODE TO WAIT; -- ok
   END
END EXCEPTION;