IBM Support

Dynamic Compound statement

News


Abstract

The Dynamic compound statement has been added to Db2 for i.

Content


 

The new SQL statement starts with BEGIN, has a middle portion similar to an SQL procedure, and ends with an END.  It can be executed through any SQL dynamic interface such as the Run SQL Statements (RUNSQLSTM) command or IBM i Navigator's Run SQL Scripts.  It can also be executed dynamically with PREPARE/EXECUTE or EXECUTE IMMEDIATE. Variables, handlers, and all normal control statements can be included within this statement. Both ATOMIC and NOT ATOMIC are supported. 


Code example:

BEGIN
DECLARE V_ERROR BIGINT DEFAULT 0;
DECLARE V_HOW_MANY BIGINT;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
   SET V_ERROR = 1;
SELECT COUNT(*) INTO V_HOW_MANY FROM STAFF
  WHERE JOB = 'Clerk' AND SALARY < 15000;
IF (V_ERROR = 1 OR V_HOW_MANY = 0)
  THEN RETURN;
END IF;
UPDATE STAFF SET SALARY = SALARY * 1.1 
  WHERE JOB = 'Clerk';
END

Programming considerations
  1. If your compound statement is frequently invoked, an SQL Procedure would be the better choice.
    A Dynamic Compound statement is a great match for situations where you don't want to build, deploy, authorize and manage a permanent program, but you do want to utilize the extensive SQL logic and handling that's possible within a compound statement.
  2. When a Dynamic Compound is prepared and executed, the statements within are processed as static statements. Since a Dynamic Compound statement is a compiled program, the parser options at the time of execution are used with the same rules in place for compile programs.
  3. Result sets can be consumed, but cannot be returned to the caller or client.
  4. The subset of SQL statements that are not allowed in triggers and routines are also not allowed within a Dynamic Compound statement.  Additionally, the follow statements are not allowed:
    • SET SESSION AUTHORIZATION
    • SET RESULT SET
  5. With TR7, we do not have support for Parameter Markers on a Dynamic Compound statement.
    If you want to parameterize a Dynamic Compound statement, use Global Variables in place of the parameter markers.
  6. Within the Database Monitor, a Dynamic Compound statement will surface with QQC21 = 'BE'.
  7. See the SQL Reference for detailed information about this statement.

Figure 1.  Dynamic Compound implementation

image-20200115130427-1

[{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB08","label":"Cognitive Systems"}}]

Document Information

Modified date:
15 January 2020

UID

ibm11167262