EXEC SQL

The EXEC SQL online utility control statement declares cursors or executes dynamic SQL statements. You can use this utility as part of the Db2 cross-loader function of the LOAD utility.

The cross-loader function enables you to use a single LOAD job to transfer data from one location to another location or from one table to another table at the same location. You can use either a local server or any DRDA-compliant remote server as a data input source for populating your tables. Your input can even come from other sources besides Db2 for z/OS®; you can use IBM® Information Integrator Federation feature for access to data from sources as diverse as Oracle and Sybase, as well as the entire Db2 family of database servers.

Important: Start of changeIf you bind DSNUTIL with DBPROTOCOL(DRDACBF), you cannot use EXEC SQL to issue remote dynamic SQL statements that modify data; you can use EXEC SQL only to declare a cursor, which can then be used by LOAD to make updates.End of change

Output

The EXEC SQL control statement produces a result table when you specify a cursor.

Authorization required

The EXEC SQL statement itself requires no privileges to execute. The authorization rules that are defined for the dynamic preparation of the SQL statement specified by EXECUTE IMMEDIATE apply.

Execution phases of EXEC SQL

The EXEC SQL control statement executes entirely in the EXEC phase. You can restart the EXEC phase if necessary.

Concurrency and compatibility of EXEC SQL

You can use the EXEC SQL control statement with any utility that allows concurrent SQL access on a table space. Other databases are not affected.

Start of change

Limitations on the number of EXEC SQL statements

Although Db2 does not limit the number of EXEC SQL statements that you can include in a single input stream (SYSIN) , running too many statements might result in failures or long-running jobs, depending on available resources. For example, running thousands of EXEC SQL statements can take unacceptable amount of time or fail.

End of change

EXEC SQL syntax

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.

Read syntax diagramSkip visual syntax diagram EXEC SQL DECLAREcursor-nameCURSORFORselect-statementnon-select dynamic SQL statement ENDEXEC

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.
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
  • RENAME
  • REVOKE
  • SET CURRENT DEGREE
  • 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.

Termination or restart of EXEC SQL

You can terminate an EXEC SQL utility job by using the TERM UTILITY command if you submitted the job or have SYSOPR, SYSCTRL, or SYSADM authority.

You can restart an EXEC SQL utility job, but it starts from the beginning again. If you are restarting this utility as part of a larger job in which EXEC SQL completed successfully, but a later utility failed, do not change the EXEC SQL utility control statement, if possible. If you must change the EXEC SQL utility control statement, use caution; any changes can cause the restart processing to fail.

Example 1: Creating a table

The following control statement specifies that Db2 is to create table MYEMP with the same rows and columns as sample table EMP.

Begin general-use programming interface information.
EXEC SQL
  CREATE TABLE MYEMP LIKE DSN8D10.EMP CCSID EBCDIC
ENDEXEC
End general-use programming interface information.

This type of statement can be used to create a mapping table.

Example 2: Inserting rows into a table

The following control statement specifies that Db2 is to insert all rows from sample table EMP into table MYEMP.

Begin general-use programming interface information.
EXEC SQL
  INSERT INTO MYEMP SELECT * FROM DSN8D10.EMP
ENDEXEC
End general-use programming interface information.

Example 3: Declaring a cursor

The following control statement declares C1 as the cursor for a query that is to return all rows from table DSN8810.EMP.

Begin general-use programming interface information.
EXEC SQL
  DECLARE C1 CURSOR FOR SELECT * FROM DSN8D10.EMP
ENDEXEC
End general-use programming interface information.

You can use a declared cursor with the Db2 cross-loader function to load data from a local server or from any DRDA-compliant remote server as part of the Db2 cross-loader function.