News
Abstract
The Dynamic compound statement has been added to Db2 for i.
Content
You are in: IBM i Technology Updates > Db2 for i - Technology Updates > Db2 for i Functional Enhancements > Dynamic Compound statement
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;
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;
THEN RETURN;
END IF;
UPDATE STAFF SET SALARY = SALARY * 1.1
WHERE JOB = 'Clerk';
WHERE JOB = 'Clerk';
END
Programming considerations
- 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. - 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.
- Result sets can be consumed, but cannot be returned to the caller or client.
- 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
- 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. - Within the Database Monitor, a Dynamic Compound statement will surface with QQC21 = 'BE'.
- See the SQL Reference for detailed information about this statement.
Figure 1. Dynamic Compound implementation
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Was this topic helpful?
Document Information
Modified date:
15 January 2020
UID
ibm11167262