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.
INCLUDE SQLCA
END-EXEC.
.
.
.
.
.
01 STRING-VARIABLE.
49 STRING-VAR-LEN PIC S9(4) USAGE COMP.
49 STRING-VAR-TXT PIC X(100).
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.
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.
statement.
2. Performance can suffer when you use EXECUTE IMMEDIATE
in a program that executes the same SQL statement many
times.
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
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.
INCLUDE SQLCA
END-EXEC.
.
.
.
.
.
01 STRING-VARIABLE.
49 STRING-VAR-LEN PIC S9(4) USAGE COMP.
49 STRING-VAR-TXT PIC X(100).
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.
MOVE "DELETE FROM DSN8810.PROJ WHERE DEPTNO = 'A00'"
TO STRING-VARIABLE.
EXEC SQL
PREPARE STMT1 FROM :STRING-VARIABLE;
END-EXEC.
PREPARE STMT1 FROM :STRING-VARIABLE;
END-EXEC.
EXEC SQL
EXECUTE STMT1;
END-EXEC.
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).
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.
MOVE "DELETE FROM DSN8810.PROJ WHERE DEPTNO = ?"
TO STRING-VARIABLE.
EXEC SQL
PREPARE STMT1 FROM :STRING-VARIABLE;
END-EXEC.
PREPARE STMT1 FROM :STRING-VARIABLE;
END-EXEC.
MOVE 'A00' TO TVAL.
EXEC SQL
EXECUTE STMT1 USING :TVAL;
END-EXEC.
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.
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 = ?
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;
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;
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 = ?
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.
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 ***
*******************************************************
*** 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).
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:
EXEC-SQL
INCLUDE SQLDA
END_EXEC.
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
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
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
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';
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;
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 NULLAllowed 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
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
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;
PREPARE VLSQL INTO MINSQLDA FROM :STRING-VARIABLE;
IF SQLD IN MINSQLDA = 0
EXECUTE IMMEDIATE (SQL statement was not a SELECT) FINISHED.
EXECUTE IMMEDIATE (SQL statement was not a SELECT) FINISHED.
EXEC SQL
PREPARE VLSQL INTO SQLDA FROM :STRING-VARIABLE;
EXEC SQL OPEN CSR3;
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:
No comments:
Post a Comment