Issuing SQL Statements in DB2 Utilities

Another new Version 7 utility feature is the EXEC SQL utility control statement. This statement can be used to declare 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	RENAME
COMMENT ON	REVOKE
COMMIT	ROLLBACK
CREATE	SET CURRENT DEGREE
DELETE	SET CURRENT LOCALE LC_CTYPE
DROP	SET CURRENT OPTIMIZATION HINT
EXPLAIN	SET CURRENT PATH
GRANT	SET CURRENT PRECISION
INSERT	SET CURRENT RULES
LABEL ON	SET CURRENT SQLID
LOCK TABLE	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 DSN8810.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 someone 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 series of SQL statements 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 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.

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.