Posted by: Serge Rielau
Motivation
It is very common nowadays for SQL statements to be generated by the application itself for by some productivity middle ware which abstracts the database.
On the server however, within routines, triggers or anonymous blocks most SQL is fixed and will execute just the way the definer of the object has specified it.
There are, however exceptions to this rule:
- Executing DDL statements such as CREATE TABLE or DROP INDEX
While DB2 supports select DDL statements, generally DDL is not part of the SQL PL syntax
- Execution of highly variable SQL.
What I mean by that is that e.g. the WHERE clause, SELECT list or ORDER BY clause can be very different between invocations based on the provided parameters.
An example could be an ad-hoc report
- Execution of long running queries where the actual input to the query can greatly influence the chosen access path.
While bind options like REOPT(ALWAYS) can help here they impact the entire routine and may be too much of a good thing.
So there is a need to compose dynamic SQL inside of SQL PL and in this post I'll illuminate the usage and some of the pitfalls.
Thanks to Cuong for bringing up this great question
EXECUTE IMMEDIATE
The most basic way to execute dynamic SQL is the EXECUTE IMMEDIATE statement.
Basic EXECUTE IMMEDIATE
If you provide it with a string it will pass it to DB2's compiler and execute the statement on the spot.
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE emp(name VARCHAR(20), salary INTEGER)';
END;
/
SELECT * FROM emp;
No rows were retrieved.
In this case we have presented a constant string. But the string can be an expression as well including bind variables.
BEGIN
DECLARE tabname VARCHAR(128) DEFAULT 'EMP';
EXECUTE IMMEDIATE 'DROP TABLE ' || CURRENT SCHEMA || '.' || tabname;
END;
/
SELECT * FROM emp;
SQL0204N "SERGE.EMP" is an undefined name.
So far our examples were doing DDL, but you can also use EXECUTE IMMEDIATE for any other dynamic statement with the exception of queries.
One common scenario for dynamic statements is to force compilation at execution time of the SQL PL because some dependent object does not yet exist when the SQL PL is compiled.
In the following example we insert into a table that does not exist until we execute the block:
BEGIN
DECLARE stmttxt VARCHAR(128);
SET stmttxt = 'CREATE TABLE temperature(date DATE, tempC DECIMAL(3, 1))';
EXECUTE IMMEDIATE stmttxt;
EXECUTE IMMEDIATE 'INSERT INTO temperature VALUES(CURRENT DATE, 21.5)';
END;
/
SELECT * FROM temperature;
DATE TEMPC
---------- -----
2012-03-13 21.5
In the examples above the entire SQL statement is defined by the composed string.
Any variables we used to compose the string do not appear as variables in the SQL statement.
Instead their values are incorporated into the string.
Let's insert multiple rows into the temperature table above and see what happens
BEGIN
DECLARE i INTEGER DEFAULT 0;
WHILE i < 10 DO
EXECUTE IMMEDIATE 'INSERT INTO temperature VALUES(CURRENT DATE - ' || i || ' DAYS, 20 - ' || i || ')';
SET i = i + 1;
END WHILE;
END;
/
SELECT * FROM temperature ORDER BY date;
DATE TEMPC
---------- -----
2012-03-04 11.0
2012-03-05 12.0
2012-03-06 13.0
2012-03-07 14.0
2012-03-08 15.0
2012-03-09 16.0
2012-03-10 17.0
2012-03-11 18.0
2012-03-12 19.0
2012-03-13 21.5
2012-03-13 20.0
This worked, but was it efficient?
SELECT SUBSTR(STMT_TEXT, 1, 60)
FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, NULL))
WHERE STMT_TEXT LIKE 'INSERT%';
INSERT INTO temperature VALUES(CURRENT DATE - 0 DAYS, 20 - 0)
INSERT INTO temperature VALUES(CURRENT DATE - 7 DAYS, 20 - 7)
INSERT INTO temperature VALUES(CURRENT DATE - 9 DAYS, 20 - 9)
INSERT INTO temperature VALUES(CURRENT DATE, 21.5)
INSERT INTO temperature VALUES(CURRENT DATE - 8 DAYS, 20 - 8)
INSERT INTO temperature VALUES(CURRENT DATE - 6 DAYS, 20 - 6)
INSERT INTO temperature VALUES(CURRENT DATE - 1 DAYS, 20 - 1)
INSERT INTO temperature VALUES(CURRENT DATE - 4 DAYS, 20 - 4)
INSERT INTO temperature VALUES(CURRENT DATE - 3 DAYS, 20 - 3)
INSERT INTO temperature VALUES(CURRENT DATE - 2 DAYS, 20 - 2)
INSERT INTO temperature VALUES(CURRENT DATE - 5 DAYS, 20 - 5)
It appears each statement was compiled individually. This is wasteful.
Instead we should recycle the statement and use parameter markers just like we would do from JDBC and ODBC.
EXECUTE IMMEDIATE however does not support variables
PREPARE/EXECUTE
To exploit parameter markers we must separate the compilation (PREPARE) from the execution (EXECUTE).
The PREPARE statement will provide us with a handle which can then be used by EXECUTE repeatedly.
DELETE FROM temperature;
BEGIN
DECLARE i INTEGER DEFAULT 0;
DECLARE text VARCHAR(1000);
DECLARE stmt STATEMENT;
SET text = 'INSERT INTO temperature VALUES(?, ?)';
PREPARE stmt FROM text;
WHILE i < 10 DO
EXECUTE stmt USING CURRENT DATE - i DAYS, 20 - i;
SET i = i + 1;
END WHILE;
END;
/
SELECT SUBSTR(STMT_TEXT, 1, 40) AS STMT_TEXT
FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, NULL))
WHERE STMT_TEXT = 'INSERT INTO temperature VALUES(?, ?)';
INSERT INTO temperature VALUES(?, ?)
This time we only got one entry of the statement in the cache.
Note that the USING clause is optional.
If there are no parameters you can still use PREPARE/EXECUTE. You simply don't use the USING clause.
In addition to input parameters there are also statements that return results.
The first three to discuss here are:
- SET statements
These are statements of the form: SET ? = <expression>
- Procedure invocation
CALL proc(?, ?) where some or all parameters are OUT or INOUT.
- Anonymous blocks including statements like the ones in the previous two bullets:
BEGIN SET ? = <expression>; CALL proc(?, ?); END
Let's try each in turn:
VARIABLE sum INTEGER;
VARIABLE cnt INTEGER;
BEGIN
DECLARE sum, cnt INTEGER;
DECLARE text VARCHAR(128);
DECLARE stmt STATEMENT;
SET text = 'SET (?, ?) = (SELECT COUNT(1), SUM(tempC) FROM temperature WHERE tempC > ?)';
PREPARE stmt FROM text;
EXECUTE stmt INTO cnt, sum USING 15;
SET (:cnt, :sum) = (cnt, sum);
END;
/
PRINT cnt;
5
PRINT sum;
90
Procedure invocation is very similar using a CALL statement.
What it interesting here is that any INOUT parameters need to be specified twice: Once each in the INTO and the USING clause.
CREATE OR REPLACE PROCEDURE add(INOUT arg1 INTEGER, IN arg2 INTEGER, OUT sign INTEGER)
BEGIN
SET arg1 = arg1 + arg2;
SET sign = SIGN(arg2);
END;
/
VARIABLE arg1 INTEGER;
VARIABLE sign INTEGER;
BEGIN
DECLARE arg1 INTEGER DEFAULT 10;
DECLARE arg2 INTEGER DEFAULT -3;
DECLARE sign INTEGER;
DECLARE text VARCHAR(128) DEFAULT 'CALL add(?, ?, ?)';
DECLARE stmt STATEMENT;
PREPARE stmt FROM text;
EXECUTE stmt INTO arg1, sign USING arg1, arg2;
SET (:arg1, :sign) = (arg1, sign);
END;
/
PRINT arg1;
7
PRINT sign;
-1
If you prepare a SET statement with a function that contains OUT and INOUT parameters the same rules apply.
An anonymous block works just the same.
CREATE OR REPLACE FUNCTION add(INOUT arg1 INTEGER, IN arg2 INTEGER)
RETURNS INTEGER
BEGIN
SET arg1 = arg1 + arg2;
RETURN sign(arg2);END;
/
BEGIN
DECLARE arg1 INTEGER DEFAULT 10;
DECLARE arg2 INTEGER DEFAULT -3;
DECLARE sign INTEGER;
DECLARE text VARCHAR(200);
DECLARE stmt STATEMENT;
SET text = 'BEGIN SET ? = add(?, ?); END';
PREPARE stmt FROM text;
EXECUTE stmt INTO sign, arg1 USING arg1, arg2;
SET (:sign, :arg1) = (sign, arg1);
END;
/
PRINT arg1;
7
PRINT sign;
-1
Cursors
So how does one operate a dynamic cursor?
BEGIN
DECLARE text VARCHAR(128);
DECLARE cnt, sum INTEGER;
DECLARE stmt STATEMENT;
DECLARE cur CURSOR FOR stmt;
SET text = 'SELECT COUNT(1), SUM(tempC) FROM temperature WHERE tempC < ?';
PREPARE stmt FROM text;
OPEN cur USING 15;
FETCH cur INTO cnt, sum;
CLOSE cur;
SET (:cnt, :sum) = (cnt, sum);
END;
/
PRINT cnt;
4
PRINT sum;
50
As you can see, instead of declaring the cursor with a query we declare it with a statement handle.
The PREPARE remains unchanged.
Then, of course, instead of doing and EXECUTE we perform an OPEN, FETCH, CLOSE sequence as is usual for cursors.
Parameters are passed at the OPEN with the USING clause.
Dynamic SQL and string literals
The perhaps most common complication when building dynamic SQL inside of SQL PL is the usage of string literals.
To illustrate let's go all the way back to the first example of the employee table:
CREATE TABLE emp(name VARCHAR(20), salary INTEGER);
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO emp VALUES ('John', 23000)';
END;
/
SQL0104N An unexpected token "John" was found following "ERT INTO T VALUES ('".
Expected tokens may include: "CONCAT".
What went wrong here?
The single quote before "John" makes the parser believe the string starting with "INSERT" is done.
Now it's looking for a semicolon to finish the statement or perhaps a concat operator to add more pieced to the string.
But really what we want is to produce a string with a single quote inside.
What we need here is an escape sequence.
Single quotes get escaped by doubling them up.
So instead of one single quote we need two single quotes (not one double quote!).
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO emp VALUES (''John'', 23000)';
END;
/
SELECT * FROM emp;
NAME SALARY
-------------------- -----------
John 23000
So far so good.
Now let's spin this example a bit further and provide "Jack" via a separate variable:
BEGIN
DECLARE text VARCHAR(20) DEFAULT 'Jack';
EXECUTE IMMEDIATE 'INSERT INTO emp VALUES (' || text || ', 23000)';
END;
/
SQL0206N "JACK" is not valid in the context where it is used.
What's wrong this time?
VARIABLE text VARCHAR(40);
BEGIN
DECLARE text VARCHAR(20) DEFAULT 'Jack';
SET :text = 'INSERT INTO emp VALUES (' || text || ', 23000)';
END;
/
PRINT text;
'INSERT INTO emp VALUES (Jack, 23000)'
"Jack" is not a string at all!
There are no single quotes here.
Therefore "JACK" must be a variable, parameter, column, ...that's why we get the -206 error.
Again we need to ensure we produce the single quotes.
Typically this is done in the surrounding statement since the name may have been passed in or looked up.
BEGIN
DECLARE text VARCHAR(20) DEFAULT 'Jack';
EXECUTE IMMEDIATE 'INSERT INTO emp VALUES (''' || text || ''', 23000)';
END;
/
SELECT * FROM emp;
NAME SALARY
-------------------- -----------
John 23000
Jack 23000
You may need to squint to see this on a a tablet.
These are three single-quotes each!
After the round bracket the first two single quotes produce the quote needed for "Jack" to be a string literal.
The third single quotes concludes the "INSERT..." string.
The first single quote after the concat (||) operator starts the next string which immediately uses two more single quotes to conclude "Jack" as a string.
BEGIN
DECLARE text VARCHAR(20) DEFAULT 'Jack';
SET :text = 'INSERT INTO emp VALUES (''' || text || ''', 23000)';
END;
/
PRINT text;
'INSERT INTO emp VALUES ('Jack', 23000)'