Syntax and options of the EXEC SQL control statement

The EXEC SQL utility control statement, with its multiple options, defines the function that the utility job performs.

You can create a control statement with the ISPF/PDF edit function. After creating it, save it in a sequential or partitioned data set. When you create the JCL for running the job, use the SYSIN DD statement to specify the name of the data set that contains the utility control statement.

Utility control statements submitted in UNICODE, including EXEC SQL, are translated into EBCDIC before processing; however, character string constants are not translated. Character string constants are left in the character set in which the were specified. In some cases, you might need to use hexadecimal string constants in order to achieve the behavior that you want.

Syntax diagram

>>-EXEC--SQL--+-| declare-cursor-spec |----------+--ENDEXEC----><
              '-non-select dynamic SQL statement-'            

declare-cursor-spec:

>>-DECLARE--cursor-name--CURSOR--FOR--select-statement---------><

Option descriptions

cursor-name
Specifies the cursor name. The name must not identify a cursor that is already declared within the same input stream. When using the DB2® cross-loader function to load data from a remote server, you must identify the cursor with a three-part name. Cursor names that are specified with the EXEC SQL utility cannot be longer than eight characters.
select-statement
Specifies the result table for the cursor. This statement can be any valid SQL SELECT statement, including joins, unions, conversions, aggregations, special registers, and user-defined functions. The result table cannot include XML columns.
non-select dynamic SQL statement
Specifies a dynamic SQL statement that is to be used as input to EXECUTE IMMEDIATE. You can specify the following dynamic SQL statements in a utility statement:
  • ALTER
  • COMMENT ON
  • COMMIT
  • CREATE
  • DELETE
  • DROP
  • EXPLAIN
  • GRANT
  • INSERT
  • LABEL ON
  • LOCK TABLE
  • RENAME
  • REVOKE
  • ROLLBACK
  • SET CURRENT DECFLOAT ROUNDING MODE
  • SET CURRENT DEGREE
  • SET CURRENT LOCALE LC_CTYPE
  • SET CURRENT OPTIMIZATION HINT
  • SET PATH
  • SET CURRENT PRECISION
  • SET CURRENT RULES
  • SET CURRENT SQLID
  • UPDATE

Each SQL statement runs as a separate thread. When the utility executes the SQL statement, the specified statement string is parsed and checked for errors. If the SQL statement is invalid, EXEC SQL does not execute the statement and reports the error condition. If the SQL statement is valid, but an error occurs during execution, EXEC SQL reports that error condition. When an error occurs, the utility terminates.