Return to Home Page

Issuing SQL Statements in DB2 Utilities

by Craig S. Mullins

As of Version 7, the EXEC SQL utility control statement can be used to declares cursors and execute dynamic SQL statements during a DB2 utility execution. The EXEC SQL control statement produces a result table when you specify a cursor. The EXEC SQL control statement executes entirely in the EXEC phase of the utility. The EXEC phase can be restarted if necessary.

The EXEC SQL statement requires no additional privileges to execute. However, EXEC SQL adheres to the same authorization rules as must be followed for executing dynamic SQL using EXECUTE IMMEDIATE.

SQL statements can be used only in conjunction with DB2 utilities that allow concurrent SQL access on a table space with the utility. No other databases are affected when issuing the EXEC SQL statement.

Using EXEC SQL

To use EXEC SQL as a utility control statement, simply code a permissible SQL statement after the EXEC SQL keyword. That SQL statement will be run during the utility execution as a separate thread. When the SQL statement is executed, the specified statement string is parsed and checked for errors. If the SQL statement is invalid, it is not executed and the error condition is reported. If the SQL statement is valid, but an error occurs during execution that error condition is reported. When an error occurs, the utility terminates.

There are two options when using EXEC SQL to supply an SQL statement to a utility. The first option is for non-SELECT dynamic SQL statements where the SQL is used as input to an EXECUTE IMMEDIATE statement. The following SQL statements can be specified in an EXEC SQL statement for processing by a DB2 utility:

¨       ALTER

¨       COMMENT ON

¨       COMMIT

¨       CREATE

¨       DELETE

¨       DROP

¨       EXPLAIN

¨       GRANT

¨       INSERT

¨       LABEL ON

¨       LOCK TABLE

¨       RENAME

¨       REVOKE

¨       ROLLBACK

¨       SET CURRENT DEGREE

¨       SET CURRENT LOCALE LC_CTYPE

¨       SET CURRENT OPTIMIZATION HINT

¨       SET CURRENT PATH

¨       SET CURRENT PRECISION

¨       SET CURRENT RULES

¨       SET CURRENT SQLID

¨       UPDATE

The second form of SQL permitted within an EXEC SQL utility control statement is a cursor-driven SELECT statement. To use this option simply declare a cursor that is not already declared and specify the SELECT statement to be used in conjunction with the cursor. For example:

EXEC SQL

DECLARE CSR1 CURSOR FOR
SELECT DEPTNO, DEPTNAME, LOCATION FROM DSN8710.DEPT

ENDEXEC

This statement declares a cursor named CSR1 that selects three columns from all of the rows in the DEPT sample table.

Why Issue SQL During a Utility?

Once a DBA learns of this new DB2 capability the next logical question usually is “Why would I want to do that?” Well, there are several good reasons to run SQL in conjunction with a utility.

One possible use is for general purpose SQL that needs to be run and would otherwise be issued using DSNTEP2, SPUFI, or QMF. For example, consider the (perhaps unlikely) scenario where you wish to give every employee a 10% raise. You could use the EXEC SQL utility control statement to perform this task as you run the utility by including the following statement:

EXEC SQL

UPDATE DSN8710.EMP

                SET SALARY = SALARY * 1.10

ENDEXEC

Perhaps a more likely scenario will be for DBAs to create the tables required for exception processing in CHECK DATA, or the mapping table and index for running a REORG using SHRLEVEL CHANGE. For example, when running CHECK DATA on the ACT sample table to you might include the following DDL in the utility job using EXEC SQL: 

EXEC SQL

CREATE TABLE EXCPT_ACT LIKE DSN8710.ACT

ENDEXEC

EXEC SQL

ALTER TABLE EXCPT_ACT

ADD EXCPT_RID CHAR(4)

ENDEXEC

EXEC SQL

ALTER TABLE EXCPT_ACT

ADD EXCPT_TS TIMESTAMP

ENDEXEC

This effectively creates the exception table and adds the additional columns to the table as needed.

Similarly, to create the mapping table for a REORG SHRLEVEL CHANGE, the following DDL can be included in the utility job using EXEC SQL:  

EXEC SQL

CREATE TABLESPACE XMAP0001

 IN DBNAME

  USING STOGROUP MAPSG

       PRIQTY 52

SECQTY 20

  ERASE NO

  LOCKSIZE PAGE

  BUFFERPOOL BP9

  SEGSIZE 8

  CLOSE YES

  COMPRESS NO

ENDEXEC

EXEC SQL

       CREATE TABLE MAP_TABLE_0001

         (TYPE        CHAR(1) NOT NULL,

          SOURCE_RID  CHAR(5) NOT NULL,

          TARGET_XRID CHAR(9) NOT NULL,

          LRSN        CHAR(6) NOT NULL)

       IN DBNAME.XMAP0001

ENDEXEC

EXEC SQL

CREATE UNIQUE INDEX XMAP0001
 ON MAP_TABLE_0001

 (SOURCE_RID ASC,
  TYPE,

  TARGET_XRID,

  LRSN)

ENDEXEC

This effectively creates the table space for the mapping table, the mapping table itself, and the unique index required for the mapping table. Please note that other than the table space needing to be segmented, the exact parameters specified in this example are not etched in stone and can be changed to suit your site’s needs. Additionally, if desired, following the REORG an additional step could be run to DROP the mapping table objects. This way the mapping table exists only when it is needed – during the online reorganization process – and it does not hang around consuming extra disk space when it is not required.

Summary

DB2’s ability to execute SQL statements during a utility job delivers a powerful new capability to the DBA. What used to take multiple steps or jobs, might now be able to be accomplished in a single utility step.

From DB2 Update (Xephon) March 2002.

© 2002 Craig S. Mullins, All rights reserved.
Home.