Dynamic Compound Statements In DB2 For i

January 15, 2014 Michael Sansoterra

Dynamic compound statements are sure to be one of the most oft used new SQL features in IBM i 7.1. In general, this feature allows developers to execute a complex set of SQL statements that include variable use, error handling, and conditional logic on the fly without creating a permanent SQL routine such as a function or stored procedure.

Dynamic compound statements makes DB2 similar to other database platforms like SQL Server where SQL scripts can be written and executed on the fly. This feature comes in DB2 for i group PTF level 26, so make sure your database PTFs are up to date.

What Is A Compound Statement?

The IBM SQL Reference manual defines a compound statement as follows: A compound statement groups other statements together in an SQL procedure. A compound statement allows the declaration of SQL variables, cursors, and condition handlers.

If you’ve written a non-trivial SQL routine (trigger, function, procedure) in DB2, you’ve probably already used a compound statement. A compound statement is bounded by the BEGIN/END keywords, and contains one or more SQL statements. Further, each compound statement can contain its own locally scoped variable and cursor declarations and condition handlers (used for error/warning trapping).

Here is a simple dynamic compound statement:

BEGIN
    DECLARE @CUSNUM INT NOT NULL DEFAULT 938485;
    DECLARE @ORDER  INT;
    DECLARE @BALDUE DEC(19,4);
    DECLARE @ERROR  VARCHAR(70);

    SELECT BALDUE
      INTO @BALDUE
      FROM QIWS.QCUSTCDT
     WHERE BALDUE>500
       AND CUSNUM=@CUSNUM;

    IF @BALDUE IS NOT NULL THEN
    	  SET @ERROR='Balance overdue '||CAST(@BALDUE AS VARCHAR(24));
             SIGNAL SQLSTATE '38U01'
       	 SET MESSAGE_TEXT=@ERROR;
    ELSE
        SELECT MAX(ORDER_ID) INTO @ORDER
          FROM QIWS.ORDERS
         WHERE CUSTOMER_ID=@CUSNUM;

        CALL QIWS.PROCESS_ORDER (@ORDER);
    END IF;
END;

Notice this looks just like code for a SQL routine except it doesn’t have a CREATE with routine name and related options specified. A dynamic compound statement can have variables that can be set and then acted upon conditionally using control statements like IF, WHILE, FOR, etc. How many times have you wanted to perform a SELECT INTO in interactive SQL only for it to tell you that host variables aren’t allowed?!

The BEGIN and END keywords are required for DB2 for i to recognize the statement as a dynamic compound statement. As noted earlier, dynamic compound statements give DB2 for i a capability that is very similar to the SQL scripting available in other database platforms.

In the example above, it would be very easy to convert this dynamic compound statement (assuming it was debugged and validated to work correctly) into a stored procedure. Likewise, if a stored procedure needed troubleshooting, it’s now easy to pull out a large section of code that can be processed interactively as a dynamic compound statement. This can be a huge help when debugging code.

Database Schema Change Operations

Prior to dynamic compound statements, one challenge was finding a way to conditionally invoke database structure changes. Consider the following statements that add a new column and a check constraint to various tables:

ALTER TABLE QIWS.QCUSTCDT
ADD EXT_NUMBER VARCHAR(24);

ALTER TABLE QIWS.ORDERS
ADD CONSTRAINT QIWS.CHK_ORDER_DATE 
    CHECK (ORDER_DATE >= '2013-01-01');

Note that these statements can only successfully run once; thereafter they’ll throw an error if executed.

Assume these statements should be deployed to three partitions (development, test, and production) as part of a major software upgrade. In the past, using the Run SQL Statement (RUNSQLSTM) command with the severity parameter set to 40 could be used to run these statements and ignore any errors (in case the column or check constraint already existed).

However, in the case of the check constraint, the statement could fail if it already exists but it can also fail if an invalid data condition exists. In this case, the error should only be ignored in the first case but not the latter. This situation was not easy to handle using RUNSQLSTM and usually required each statement to be put in a separate source member so that each statement’s error handling could be controlled differently. Other alternatives are to use a stored procedure or an embedded SQL program to do these tasks, but who wants to maintain a program for simple database maintenance chores?

Because compound statements can perform conditional logic and error-handling logic, they can handle this situation:

BEGIN
     -- Add row to error table if constraint test fails
	DECLARE CONTINUE HANDLER FOR SQLSTATE '23512'
	INSERT INTO QIWS.ERRORS 
     VALUES('COULD NOT ADD ORDERS.ORDER_DATE CHECK CONSTRAINT');

	IF NOT EXISTS(
	SELECT *
  	FROM QSYS2.SYSCOLUMNS
 	WHERE TABLE_NAME='QCUSTCDT'
   	AND TABLE_SCHEMA='QIWS'
   	AND COLUMN_NAME='EXT_NUMBER') THEN
ALTER TABLE QIWS.QCUSTCDT
ADD EXT_NUMBER VARCHAR(24);
	END IF;

	IF NOT EXISTS (
	SELECT *
  	FROM QSYS2.SYSCHKCST
 	WHERE CONSTRAINT_SCHEMA='QIWS'
   	AND CONSTRAINT_NAME='CHK_ORDER_DATE'
	) THEN
    	    ALTER TABLE QIWS.ORDERS
    	    ADD CONSTRAINT QIWS.CHK_ORDER_DATE 
             CHECK (ORDER_DATE >= '2013-01-01');
	END IF;
END;

The first IF condition will only attempt to add the new column if it doesn’t already exist, by checking for the column’s presence in the SYSCOLUMNS catalog view.

The second IF statement will attempt to add the new check constraint if it doesn’t already exist, by checking the SYSCHKCST (check constraints) catalog view. However, a continue handler is defined to catch the case when the new constraint can’t be created because of a data condition. In this case, the handler dumps a message in the ERRORS table. Other options are available, such as rolling back all changes (if all relevant tables are journaled), stopping the script by SIGNALing an error condition, etc.

Although these statements can be done in embedded SQL or with a stored procedure, it’s often a pain in the neck to run the procedure to see how something is going to work. Using dynamic compound statements allow developers to test multiple statements as they’re coded. This technique also allows developers to append more statements to the dynamic compound statement as new requirements are found. Because of the conditional checking, the script can be run over and over with already executed statements being skipped yet allowing new statements to run.

Finally, having dynamic compound statements that can run against multiple partitions or environments is useful for ensuring that all database structural changes are consistently applied.

Behind The Scenes

Technically each time a “dynamic” compound statement is submitted, DB2 for i will compile the statement “on the fly” into an embedded SQL C program placed in QTEMP. (This is similar to how SQL routines such as functions, triggers and procedures are created.) Thereafter it will execute the C program and then remove it when the execution has completed.

All of this work behind the scenes means:

Performance may not be the greatest because of the compile time, especially with a large number of statements. I was testing this feature on a P50 box and everything was great. However, I suspect the results may not be as impressive on a P05.
Occasionally there are issues where the code is not flagged with a specific problem, such as a syntax error, missing END IF, etc., yet DB2 cannot create the program object. When this happens, you often need to look at the compiled C code to find out what the underlying problem is because the DB2 user interface doesn’t show any useful information. When this happens, I usually take my “dynamic” compound statement and attach it to a dummy CREATE PROCEDURE statement with SET OPTION OUTPUT=*PRINT specified for the purpose of getting a C compiler listing.

Also note that the environment attributes of the dynamic compound statement–date format, naming convention, etc.–will be inherited from the host SQL session’s attributes. You cannot specify the USRPRF option (*USER/*OWNER) for a dynamic compound statement so make sure the user running the dynamic compound statement has the proper authority.

Dynamic compound statements are an asset for developers. Running statements dynamically will help developers reduce development and debugging time. They will also be very useful when deploying complex scripts that interrogate the database catalog views before deciding whether to execute a specific statement such as ALTER COLUMN.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.