DB2 Update: "Using Dynamic SQL for Maximum Flexibility"

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:

You can replace the DELETE statement in this listing with any of the following supported statements:

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.

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:

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:

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.

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.

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:

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.)

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:

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:

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:

Craig S. Mullins Database Performance Management
Return to Home Page
October 2004
	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 (5^th 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: DB2 Application Programming and SQL Guide DB2 SQL Reference 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.

Using Dynamic SQL for Maximum Flexibility By Craig S. Mullins

EXECUTE IMMEDIATE

Using Dynamic SQL for Maximum Flexibility
By Craig S. Mullins