Using Dynamic SQL for Maximum Flexibility
By Craig S. Mullins
This article is adapted from the latest version of Craig’s book,
DB2 Developer’s Guide (5th edition).
Most application programmers are comfortable coding embedded SQL in their
programs to access DB2 data. But usually this SQL is written as static SQL.
Static SQL is hard-coded, and only the values of host variables in predicates
can change.
But there is another type of SQL
programming that is much more flexible than static SQL; it is known as dynamic
SQL. Dynamic SQL is characterized by its capability to change the columns,
tables, and predicates it references during the program's execution. This
flexibility requires different techniques for embedding dynamic SQL in
application programs.
You should understand what dynamic SQL
is and what it can do for you for many reasons. Dynamic SQL makes optimal use of
the distribution statistics accumulated by RUNSTATS. Because the values are
available when the optimizer determines the access path, it can arrive at a
better solution for accessing the data. Static SQL, on the other hand, cannot
use these statistics unless all predicate values are hard-coded or REOPT(VARS)
is specified.
Additionally, dynamic SQL is becoming
more popular as distributed queries are being executed from non-mainframe
platforms or at remote sites using distributed DB2 capabilities. Indeed, the
JDBC and ODBC call-level interfaces deploy dynamic SQL, not static.
Using dynamic SQL is the only way to
change SQL criteria such as complete predicates, columns in the SELECT list, and
table names during the execution of a program. As long as application systems
require these capabilities, dynamic SQL will be needed.
There are four classes of dynamic SQL:
EXECUTE IMMEDIATE, non-SELECT dynamic SQL, fixed-list SELECT, and varying-list
SELECT.
EXECUTE IMMEDIATE
EXECUTE IMMEDIATE implicitly prepares
and executes complete SQL statements coded in host variables. Only a subset of
SQL statements is available when you use the EXECUTE IMMEDIATE class of dynamic
SQL. The most important SQL statement that is missing is the SELECT statement.
Therefore, EXECUTE IMMEDIATE dynamic SQL cannot retrieve data from tables.
If you do not need to issue queries,
you can write the SQL portion of your program in two steps. First, move the
complete text for the statement to be executed into a host variable. Second,
issue the EXECUTE IMMEDIATE statement specifying the host variable as an
argument. The statement is prepared and executed automatically.
The following pseudo-code shows a
simple use of EXECUTE IMMEDIATE that DELETEs rows from a table; the SQL
statement is moved to a string variable and then executed:
WORKING-STORAGE SECTION.
.
.
.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
.
.
.
01 STRING-VARIABLE.
49 STRING-VAR-LEN PIC
S9(4) USAGE COMP.
49 STRING-VAR-TXT PIC X(100).
.
.
.
PROCEDURE DIVISION.
.
.
.
MOVE +45 TO STRING-VAR-LEN.
MOVE "DELETE FROM DSN8810.PROJ WHERE DEPTNO = 'A00'"
TO STRING-VARIABLE.
EXEC SQL
EXECUTE IMMEDIATE :STRING-VARIABLE
END-EXEC.
.
.
.
You can replace the DELETE statement in this
listing with any of the following supported statements:
ALTER
COMMENT ON COMMIT
CREATE
DELETE
DROP
EXPLAIN
GRANT
INSERT
LABEL ON
LOCK TABLE REVOKE
ROLLBACK
SET
UPDATE
Despite the simplicity of the EXECUTE IMMEDIATE
statement, it usually is not the best choice for application programs that issue
dynamic SQL for two reasons.
1.
EXECUTE IMMEDIATE does not support the SELECT
statement.
2. Performance
can suffer when you use EXECUTE IMMEDIATE
in a program that executes the same
SQL statement many
times.
After an EXECUTE IMMEDIATE is performed, the
executable form of the SQL statement is destroyed. Thus, each time an EXECUTE
IMMEDIATE statement is issued, it must be prepared again. This preparation is
automatic and can involve a significant amount of overhead. A better choice is
to code non-SELECT dynamic SQL using PREPARE and EXECUTE statements.
In general, you should consider using EXECUTE
IMMEDIATE for quick, one-time tasks. For example, the following types of
programs are potential candidates:
-
A DBA utility program that issues changeable
GRANT and REVOKE statements
-
A program that periodically generates DDL
based on input parameters
-
A parameter-driven modification program that
corrects common data errors
Non-SELECT Dynamic
SQL
The second type of dynamic SQL is known as
Non-SELECT dynamic SQL. This class of dynamic SQL uses PREPARE and EXECUTE to
issue SQL statements. As its name implies, non-SELECT dynamic SQL cannot issue
the SELECT statement. The following pseudo-code listing shows a simple use of
non-SELECT dynamic SQL that DELETEs rows from a table.
WORKING-STORAGE SECTION.
.
.
.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
.
.
.
01 STRING-VARIABLE.
49 STRING-VAR-LEN PIC S9(4) USAGE COMP.
49 STRING-VAR-TXT PIC X(100).
.
.
.
PROCEDURE DIVISION.
.
.
.
MOVE +45 TO STRING-VAR-LEN.
MOVE "DELETE FROM DSN88310.PROJ
WHERE DEPTNO = 'A00'"
TO STRING-VARIABLE.
EXEC SQL
PREPARE STMT1 FROM :STRING-VARIABLE;
END-EXEC.
EXEC SQL
EXECUTE STMT1;
END-EXEC.
.
.
.
You can replace the DELETE statement in this
listing with any of the following supported statements:
ALTER
COMMENT ON COMMIT
CREATE
DELETE
DROP
EXPLAIN
GRANT
INSERT
LABEL ON
LOCK TABLE REVOKE
ROLLBACK
SET
UPDATE
Non-SELECT dynamic SQL can use a powerful
feature of dynamic SQL called a parameter marker, which is a placeholder
for host variables in a dynamic SQL statement. This feature is demonstrated in
the following pseudo-code:
WORKING-STORAGE SECTION.
.
.
.
EXEC SQL INCLUDE SQLCA END-EXEC.
.
.
.
01 STRING-VARIABLE.
49 STRING-VAR-LEN PIC S9(4) USAGE COMP.
49 STRING-VAR-TXT PIC X(100).
.
.
.
PROCEDURE DIVISION.
.
.
.
MOVE +40 TO STRING-VAR-LEN.
MOVE "DELETE FROM DSN8810.PROJ WHERE DEPTNO = ?"
TO STRING-VARIABLE.
EXEC SQL
PREPARE STMT1 FROM :STRING-VARIABLE;
END-EXEC.
MOVE 'A00' TO TVAL.
EXEC SQL
EXECUTE STMT1 USING :TVAL;
END-EXEC.
The question mark is used as a parameter
marker, replacing the 'A00' in the predicate. When the statement is executed, a
value is moved to the host variable (:TVAL) and is coded as a parameter to the
CURSOR with the USING clause. When this example is executed, the host variable
value replaces the parameter marker.
Non-SELECT dynamic SQL can provide huge
performance benefits over EXECUTE IMMEDIATE. Consider a program that executes
SQL statements based on an input file. A loop in the program reads a key value
from the input file and issues a DELETE, INSERT, or UPDATE for the specified
key. The EXECUTE IMMEDIATE class would incur the overhead of a PREPARE for each
execution of each SQL statement inside the loop.
Using non-SELECT dynamic SQL, however, you can
separate PREPARE and EXECUTE, isolating PREPARE outside the loop. The key value
that provides the condition for the execution of the SQL statements can be
substituted using a host variable and a parameter marker. If thousands of SQL
statements must be executed, you can avoid having thousands of PREPAREs by using
this technique. This method greatly reduces overhead and runtime and increases
the efficient use of system resources.
A prepared statement can contain more than one
parameter marker. Use as many as necessary to ease development.
Fixed-List SELECT
Until now, we have been unable to retrieve rows
from DB2 tables using dynamic SQL. The next two classes of dynamic SQL provide
this capability. The first and simplest is fixed-list SELECT.
You can use a fixed-list SELECT statement to
explicitly prepare and execute SQL SELECT statements when the columns to be
retrieved by the application program are known and unchanging. You need to do so
to create the proper working-storage declaration for host variables in your
program. If you do not know in advance the columns that will be accessed, you
must use a varying-list SELECT statement.
The following pseudo-code listing shows a
fixed-list SELECT statement:
SQL to
execute:
SELECT PROJNO, PROJNAME, RESPEMP
FROM DSN8810.PROJ
WHERE PROJNO = ?
AND PRSTDATE = ?
Move the "SQL to execute" to STRING-VARIABLE
EXEC SQL DECLARE CSR2 CURSOR FOR FLSQL;
EXEC SQL PREPARE FLSQL FROM :STRING-VARIABLE;
EXEC SQL OPEN CSR2 USING :TVAL1, :TVAL2;
Loop until no more rows to FETCH
EXEC SQL
FETCH CSR2 INTO :PROJNO, :PROJNAME, :RESPEMP;
EXEC SQL CLOSE CSR2;
This example formulates a SELECT statement in the application
program and moves it to a host variable. Next, a cursor is declared and the
SELECT statement is prepared. The cursor then is opened and a loop to FETCH rows
is invoked. When the program is finished, the cursor is closed. This example is
simple because the SQL statement does not change. The benefit of dynamic SQL is
its capability to modify the SQL statement. For example, you could move the SQL
statement
SELECT PROJNO, PROJNAME, RESPEMP
FROM DSN8810.PROJ
WHERE RESPEMP = ?
AND PRENDATE = ?
to the STRING-VARIABLE without modifying the
OPEN or FETCH logic. Note that the second column of the predicate is different
from the SQL statement as presented in the listing (PRENDATE instead of PRSTDATE).
Because both are the same data type (DATE), however, you can use TVAL2 for both
if necessary. The host variables passed as parameters in the OPEN statement must
have the same data type and length as the columns in the WHERE clause. If the
data type and length of the columns in the WHERE clause change, the OPEN
statement must be recoded with new USING parameters.
If parameter markers are not used in the SELECT
statements, the markers could be eliminated and values could be substituted in
the SQL statement to be executed. No parameters would be passed in the OPEN
statement.
You can recode the OPEN statement also to pass
parameters using an SQLDA (SQL Descriptor Area). The SQLDA would contain value
descriptors and pointers to these values. You can recode the OPEN statement as
follows:
EXEC-SQL
OPEN CSR2 USING DESCRIPTOR :TVAL3;
END_EXEC.
DB2 uses the SQLDA to communicate information
about dynamic SQL to an application program. The SQLDA sends information such as
the type of the SQL statement being executed and the number and data type of
columns being returned by a SELECT statement. It can be used by fixed-list
SELECT and varying-list SELECT dynamic SQL. The following code illustrates the
fields of the SQLDA:
*******************************************************
*** SQLDA: SQL DESCRIPTOR AREA FOR LE COBOL ***
*******************************************************
01 SQLDA.
05 SQLDAID PIC X(8) VALUE 'SQLDA'.
05 SQLDABC COMP PIC S9(8) VALUE 13216.
05 SQLN COMP PIC S9(4) VALUE 750.
05 SQLD COMP PIC S9(4) VALUE 0.
05 SQLVAR OCCURS 1 TO 750 TIMES DEPENDING ON SQLN.
10 SQLTYPE COMP PIC S9(4).
88 SQLTYPE-BLOB VALUE 404 405.
88 SQLTYPE-CLOB VALUE 408 409.
88 SQLTYPE-DBCLOB VALUE 412 413.
88 SQLTYPE-FLOAT VALUE 480 481.
88 SQLTYPE-DECIMAL VALUE 484 485.
88 SQLTYPE-SMALLINT VALUE 500 501.
88 SQLTYPE-INTEGER VALUE 496 497.
88 SQLTYPE-DATE VALUE 384 385.
88 SQLTYPE-TIME VALUE 388 389.
88 SQLTYPE-TIMESTAMP VALUE 392 393.
88 SQLTYPE-CHAR VALUE 452 453.
88 SQLTYPE-VARCHAR VALUE 448 449.
88 SQLTYPE-LONG-VARCHAR VALUE 456 457.
88 SQLTYPE-VAR-ONUL-CHAR VALUE 460 461.
88 SQLTYPE-GRAPHIC VALUE 468 469.
88 SQLTYPE-VARGRAPH VALUE 464 465.
88 SQLTYPE-LONG-VARGRAPH VALUE 472 473.
88 SQLTYPE-ROWID VALUE 904 905.
88 SQLTYPE-BLOB-LOC VALUE 961 962.
88 SQLTYPE-CLOB-LOC VALUE 964 965.
88 SQLTYPE-DBCLOB-LOC VALUE 968 969.
10 SQLLEN COMP PIC S9(4).
10 SQLDATA POINTER.
10 SQLIND POINTER.
10 SQLNAME.
15 SQLNAMEL COMP PIC S9(4).
15 SQLNAMEC COMP PIC X(30).
A description of the contents of the SQLDA
fields is in the discussion of the next class of dynamic SQL, which relies
heavily on the SQLDA.
Quite a bit of flexibility is offered by
fixed-list SELECT dynamic SQL. Fixed-list dynamic SQL provides many of the same
benefits for the SELECT statement as non-SELECT dynamic SQL provides for other
SQL verbs. An SQL SELECT statement can be prepared once and then fetched from a
loop. The columns to be retrieved must be static, however. If you need the
additional flexibility of changing the columns to be accessed while executing,
use a varying-list SELECT.
For fixed-list SELECT dynamic SQL, you cannot code the SQLDA in a VS/COBOL
program.
You will need to use
LE
COBOL. (Of course, at this late date, VS/COBOL is for all intents and purposes a
dead language.)
Varying-List SELECT
The fourth and final class of dynamic SQL is
varying-list SELECT. This class of dynamic SQL can be used to explicitly prepare
and execute SQL SELECT statements when you do not know in advance which columns
will be retrieved by an application program.
Varying-list SELECT provides the most
flexibility for dynamic SELECT statements. You can change tables, columns, and
predicates "on-the-fly." Keep in mind though, because everything about the query
can change during one invocation of the program, the number and type of host
variables needed to store the retrieved rows cannot be known beforehand. The
lack of knowledge regarding what is being retrieved adds considerable complexity
to your application programs.
The SQLDA is the vehicle for communicating
information about dynamic SQL between DB2 and the application program. It
contains information about the type of SQL statement to be executed, the data
type of each column accessed, and the address of each host variable needed to
retrieve the columns. The SQLDA must be hard-coded into the LE COBOL program's
WORKING-STORAGE area, as shown here:
EXEC-SQL
INCLUDE SQLDA
END_EXEC.
The following table defines each item in the
SQLDA when it is used with varying-list SELECT.
SQLDA Data Element Definitions
Field Name Use in DESCRIBE or
PREPARE Statement
SQLDAID Descriptive only; usually set
to the literal "SQLDA" to
aid in program debugging
SQLDABC Length of the SQLDA
SQLN Number of occurrences of
SQLVAR available
SQLD Number of occurrences of
SQLVAR used
SQLTYPE Data type and indicator of
whether NULLs are allowed
for the column; for UDTs, SQLTYPE is set based on
the base data type
SQLLEN External length of the
column value; 0 for LOBs
SQLDATA Address of a host variable for
a specific column
SQLIND Address of NULL indicator
variable for the preceding
host variable
SQLNAME Name or label of the column
The steps needed to code varying-list SELECT
dynamic SQL to your application program vary according to the amount of
information known about the SQL beforehand. Let’s walk through another
pseudo-code listing showing the steps necessary when you know that the statement
to be executed is a SELECT statement:
SQL to
execute: SELECT PROJNO, PROJNAME, RESPEMP
FROM DSN8810.PROJ
WHERE PROJNO = 'A00'
AND PRSTDATE = '1988-10-10';
Move the "SQL to execute" to STRING-VARIABLE
EXEC
SQL DECLARE CSR3 CURSOR FOR VLSQL;
EXEC
SQL
PREPARE VLSQL INTO SQLDA FROM :STRING-VARIABLE;
EXEC SQL OPEN CSR3;
Load storage addresses into the SQLDA
Loop until no more rows to FETCH
EXEC SQL FETCH CSR3 USING
DESCRIPTOR SQLDA;
EXEC
SQL CLOSE CSR3;
The code differs from fixed-list SELECT in
three ways: The PREPARE statement uses the SQLDA, the FETCH statement uses the
SQLDA, and a step is added to store host variable addresses in the SQLDA.
When PREPARE is executed, DB2 returns
information about the columns being returned by the SELECT statement. This
information is in the SQLVAR group item of the SQLDA. Of particular interest is
the SQLTYPE field. For each column to be returned, this field indicates the data
type and whether NULLs are permitted. Note that in the SQLDA layout presented
previously, all possible values for SQLTYPE are coded as 88-level COBOL
structures. They can be used in the logic of your application program to test
for specific data types. The valid values for SQLTYPE are shown in the following
table.
Valid Values for SQLTYPE
NULL
NULL
Allowed Not Allowed Data Type
384 385 DATE
388 389 TIME
392 393 TIMESTAMP
400 401 null-terminated graphic string
404 405 BLOB
408 409 CLOB
412 413 DBCLOB
448 449 Small VARCHAR
452 453 Fixed CHAR
456 457 Long VARCHAR
460 461 VARCHAR optionally
null-terminated
464 465 Small VARGRAPHIC
468 469 Fixed GRAPHIC
472 473 Long VARGRAPHIC
480 481 FLOAT
484 485 DECIMAL
496 497 INTEGER
500 501 SMALLINT
904 905 ROWID
961 962 BLOB locator
964 965 CLOB locator
968 969 DBCLOB locator
972 973 result set locator
976 977 table locator
The first value listed is returned when NULLs
are not permitted; the second is returned when NULLs are permitted. These two
codes aid in the detection of the data type for each column. The application
program issuing the dynamic SQL must interrogate the SQLDA, analyzing each
occurrence of SQLVAR. This information is used to determine the address of a
storage area of the proper size to accommodate each column returned. The address
is stored in the SQLDATA field of the SQLDA. If the column can be NULL, the
address of the NULL indicator is stored in the SQLIND field of the SQLDA. When
this analysis is complete, data can be fetched using varying-list SELECT and the
SQLDA information.
Note that the group item, SQLVAR, occurs 750
times. This number is the limit for the number of columns that can be returned
by one SQL SELECT. You can modify the column limit number by changing the value
of the SQLN field to a smaller number but not to a larger one. Coding a smaller
number reduces the amount of storage required. If a greater number of columns is
returned by the dynamic SELECT, the SQLVAR fields are not populated.
You can also code dynamic SQL without knowing
anything about the statement to be executed. An example is a program that must
read SQL statements from a terminal and execute them regardless of statement
type. You can create this type of program by coding two SQLDAs: one full SQLDA
and one minimal SQLDA (containing only the first 16 bytes of the full SQLDA)
that PREPAREs the statement and determines whether it is a SELECT. If the
statement is not a SELECT, you can simply EXECUTE the non-SELECT statement. If
it is a SELECT, PREPARE it a second time with a full SQLDA and follow the steps
in the following pseudo-code listing:
EXEC
SQL INCLUDE SQLDA
EXEC SQL INCLUDE MINSQLDA
Read "SQL to execute" from external source
Move the "SQL to execute" to STRING-VARIABLE
EXEC
SQL DECLARE CSR3 CURSOR FOR VLSQL;
EXEC
SQL
PREPARE VLSQL INTO MINSQLDA FROM :STRING-VARIABLE;
IF
SQLD IN MINSQLDA = 0
EXECUTE IMMEDIATE (SQL statement was not a SELECT)
FINISHED.
EXEC
SQL
PREPARE VLSQL INTO SQLDA FROM :STRING-VARIABLE;
EXEC SQL OPEN CSR3;
Load storage addresses into the SQLDA
Loop until no more rows to FETCH
EXEC SQL FETCH CSR3 USING
DESCRIPTOR SQLDA;
EXEC
SQL CLOSE CSR3;
In this section, I've provided a quick
introduction to varying-list SELECT dynamic SQL. If you want to code parameter
markers or need further information on acquiring storage or pointer variables,
consult the appropriate compiler manuals and the following DB2 manuals:
Summary
Without proper knowledge of dynamic SQL you are
going into battle without a full set of ammunition. Seriously consider using
dynamic SQL under the following conditions:
-
When the nature of the application program
is truly changeable, not just a series of static SQL statements
-
When the columns to be retrieved can vary
from execution to execution
-
When the predicates can vary from execution
to execution
-
When benefit can be accrued from interacting
with other dynamic SQL applications¾for
example, using the QMF callable interface
From DB2 Update, October 2004.
© 2004 Craig S. Mullins, All rights reserved.
Home.
![](images/wspace.gif)
|