Extended dynamic statements support the direct creation and maintenance of packages for DB2 Server for VSE & VM data. For DB2 Server for VSE, extended dynamic statements can only be used with assembler language. For DB2 Server for VM, extended dynamic statements can only be used with assembler language or in the optional DB2 Server RXSQL feature (described in the DB2 REXX SQL for VM/ESA Installation and Reference manual). Refer to the DB2 Server for VSE & VM SQL Reference manual for a detailed discussion of the restrictions with DRDA protocol.
| Note: | This topic is more advanced than previous sections and the techniques discussed here are not relevant to all application programs. |
Before reading this chapter, you should be familiar with how to use packages as described in Preprocessing the Program (DB2 Server for VM) or Preprocessing the Program (DB2 Server for VSE), and dynamically defined statements, as described in Chapter 7, Using Dynamic Statements. Extended dynamic statements provide a function similar to that provided by the DB2 Server for VSE & VM preprocessors, but may be particularly useful where:
Individual SQL statements can be added or deleted without affecting or repeating the preprocessing of other SQL statements in the package.
The following extended dynamic statements are supported. (They are described in detail in the DB2 Server for VSE & VM SQL Reference manual.)
Except for CREATE PACKAGE and DROP STATEMENT, the names of these statements are the same as the corresponding "normal" dynamic statements discussed in Chapter 7, Using Dynamic Statements, but their format and meaning are somewhat different. For example, the statement-id, package-id, and cursor-name fields are all specified by host variables.
Unlike dynamic statements which are related through a specific statement name, extended dynamic statements are related through the symbolic host variables used for the statement-id and package-id. This relationship is shown in Figure 72. Because the statement-id and package-id are host variables, actual values can be substituted when the program is executed. STMTID is returned by an extended PREPARE statement, and is used as input by the subsequent extended EXECUTE (or DECLARE CURSOR) statement.
Figure 72. Relationship between Extended Dynamic Statements Expressed Using Host Program Variables
The differences between dynamic and extended dynamic statements are illustrated in Figure 73. As shown in this figure, the normal dynamic statements are intended primarily for supporting an interactive environment. As such, the PREPARE and EXECUTE commands must be used within the same logical unit of work. In contrast, extended dynamic statements are generally used in a compile environment where the EXECUTE (or DECLARE CURSOR) may be in a logical unit of work that is different from the one where the SQL statement was prepared. This makes it possible to PREPARE statements at different times. In DB2 Server for VSE & VM terms, they can be prepared in one logical unit of work (stored in a package), and called out for execution from another logical unit of work (from the same or a different program). This is made possible by passing the program and statement identifiers between the preparation environment and the execution environment.
Figure 73. Comparing Dynamic to Extended Dynamic Statements
CREATE PACKAGE and DROP STATEMENT have no counterparts in the normal dynamic statement set.
CREATE PACKAGE creates an empty package and is normally followed by extended PREPARE statements to add statements to the package. If the CREATE PACKAGE has a MODIFY option, the package may even be changed in another logical unit of work. The change may take the form of additional extended PREPAREs (adding statements to those already there), or DROP STATEMENTs (deleting statements previously prepared). If a program is created with the NOMODIFY option, it cannot be changed without completely replacing it. You do this by using CREATE PACKAGE with the REPLACE option and specifying the same package-id. When you use DB2 Server for VM DRDA protocol, there is no support for the MODIFY option. The MODIFY option of the CREATE PACKAGE statement defaults to NOMODIFY.
The DROP PACKAGE statement is not listed as an extended dynamic statement, because it has general applicability for all packages, not just those that are built with extended dynamic statements. (See the DB2 Server for VSE & VM SQL Reference manual for more information on the DROP PACKAGE statement.) Like the extended dynamic statements, DROP PACKAGE permits the package name to be specified as a host program variable.
Consider the following example. A support group needs to develop a program that dynamically accepts SQL statements for execution and does not know what SQL statements will be processed. This is a typical application for normal dynamic SQL statements. But since there is also a requirement for repetitively executing the preprocessed statements at a later time (stored SQL application) without having to repeat the PREPARE, it is an application for extended dynamic statements.
A program that handles preparation of end user SQL statements can also execute these statements. This is essentially a query language program (but it supports more than just select-statements). The program may also support deleting statements from and adding them to existing packages. (See the beginning of this chapter for a list of extended dynamic statements for doing this, as well as statements to control execution.)
The program may use CREATE PACKAGE and extended PREPARE to build a package and prepare the end-user SQL statements. However, you must first preprocess the program itself, by running it through the assembler preprocessor and the assembler. See Figure 74 (the application program is referred to as a "Support Program").
The resulting support program can accept end-user SQL statements, and create packages in the database to hold them. For example, there can be a separate package to hold the SQL statements for each end-user. A more advanced support program may even accept end-user commands that are at a higher level than that supported by the system, and then translate them to SQL statements before preparing them.
The package P is built by the support program (by CREATE PACKAGE) for the particular SQL statements. If the support program allows both adding SQL statements to and dropping them from P, then the support program must utilize and be preprocessed with a DROP STATEMENT as well as the PREPARE. Of course, there are a few other ordinary SQL statements that may be appropriate for the support program: WHENEVER, COMMIT/ROLLBACK, and so on to make it complete.
So far, this example has not addressed execution of the end-user SQL statements. We have already listed the extended dynamic statements that support execution (extended EXECUTE, DECLARE CURSOR, and so on). The support program would ordinarily support end-user commands to retrieve data and update data (using either direct SQL statements or higher level commands that require conversion). This addition does not alter the concept shown in Figure 74, except to add additional extended dynamic statements to the support program.
The DESCRIBE statement can be used in the same way as shown under normal dynamic statements.
Note that only one "copy" of each extended dynamic statement need be provided in the support program, because each of these statements is parameterized with host variables that can be dynamically changed for each use. For example, one DECLARE CURSOR statement may service all cursor retrievals, even if they are concurrently open, because each can be given a different cursor name by the host variable value for the cursor name, and a different statement identifier by the host variable value for the statement-id. This is important in cases where the use of program storage is critical and there are a significant number of predefined transactions.
The previous example is structurally simple. It assumes that the support program remains in control as an interpreter through preparation, maintenance, and execution of the user's SQL statements.
For a typical language preprocessor program such as those provided with the DB2 Server for VSE & VM product, however, this is not the case. If you write a support program for a new language preprocessor, you would probably separate the two parts, each with SQL statements:
The SQL facilities required are similar to the previous example, except that no package maintenance functions are needed. The language preprocessor has the following characteristics:
This process is illustrated in Figure 75, Figure 76, Figure 77, and Figure 78. The support program is the preprocessor for language X. It preprocesses the end-user program, modifying the source (adding control structures and generating calls to pass to the support program Part 2 at execution-time). Once the modified end-user source has been compiled by the language X compiler, it is combined in one load module with the object code for the support program Part 2, which provides the DB2 Server for VSE & VM support for execution-time functions (DECLARE CURSOR, and so on).
Figure 75 shows the preprocessing and assembly steps for the two parts of the support program. For DB2 Server for VM, no packages are created, because there are no SQL statements in either part that need to be stored in a package.
Figure 76 shows how the two resulting object modules of the support program process end-user SQL statements.
Figure 75. Preprocessing and Assembling of a Two-Part Support Program
Figure 76. Preprocessing and Executing an End-User Program by a Two-Part Support Program
Part 1 scans the end-user's source for SQL statements, uses CREATE PACKAGE to build an empty package, P, uses extended PREPARE statements to add SQL statements to P, and uses a COMMIT statement to finalize P. It also adds calls and control structures, required by Part 2 of the support program, to the user's source program and comments out the original SQL statement.
Part 2 of the support program works with the package, P, executing the SQL statements scanned and prepared by Part 1, and using the control structures passed in the calls generated by Part 1. Part 2 must be link/loaded with any end-user module that is preprocessed by Part 1.
Figure 77 shows Part 1 of the support program in more detail, with pseudocode to illustrate a simple user program that includes a DECLARE...CURSOR FOR SELECT..., an OPEN of that cursor, and a FETCH for the same cursor. Control structures are shown in more detail, and some particular values for parameters are given. The value 26 returned from the PREPARE statement is only for purposes of illustration, representing a unique identifier returned by the system to identify the statement within the package P. A user ID may be necessary to identify the owner of the package, but it is omitted here for simplicity. Other statements, such as CLOSE (cursor) and COMMIT are not shown in order to simplify the illustration.
Figure 77. Pseudocode Example of Preprocessing the End-User Program P
Figure 78 shows the execution-time flow between the end-user's object program and the support program (Part 2) in more detail. The two calls shown correspond to the two calls generated in Figure 77. This example does not go far enough to illustrate that two calls of the same type (two opens, for example) would share the same set of logic and the same extended dynamic statement (OPEN) in the support program.
Figure 78. Pseudocode Example of Executing the End-User Program P
There are primarily three cases to consider when determining the proper grouping of extended dynamic statements in a logical unit of work:
In the first case, the only other extended dynamic statement permitted is the PREPARE statement, and it must reference only the program that is specified in the CREATE PACKAGE statement. If the LUW is terminated by a COMMIT statement, a DB2 Server for VSE & VM package is created. If no extended PREPARE statements were executed, the package is empty and the COMMIT statement returns an SQLCODE of -759 (SQLSTATE '42943'). If a ROLLBACK statement terminates the LUW, no package is created. In Figure 79, Example 1 is a valid illustration of this case.
| DB2 Server for VM |
|---|
|
If you are using DRDA protocol, MODIFY defaults to NOMODIFY when specified on the CREATE PACKAGE statement. No error is returned if MODIFY is specified. If a COMMIT statement is used for an empty package (that is, the package contains no statements) created with the NOMODIFY option, one of the following SQLCODEs is received:
|
Figure 79. Placement of Extended Dynamic Statements in Logical Units of Work
In the second case, the rules discussed above for case 1 apply, but Extended DESCRIBE, EXECUTE, DECLARE CURSOR, OPEN, FETCH, DROP STATEMENT, and CLOSE statements may also be used in the same LUW, referencing the statements just added to or already contained in the current package. However, you cannot reference a package other than the one created in the current LUW. In Figure 79, example 2 is a valid example of this case. Example 3 illustrates an invalid case 2 sequence. If the current LUW is committed before extended PREPAREs are used to add statements to it (it is empty), it still may be extended in a later LUW (since it is modifiable, it may make sense to leave it empty initially).
In case 3, where the current LUW contains no CREATE PACKAGE, extended dynamic statements may reference any package that has been created with a CREATE PACKAGE statement. However, after an extended dynamic statement that causes modification of the package is used (an extended PREPARE or DROP STATEMENT), subsequent extended dynamic statements in the same LUW may only refer to the modified package. Once the LUW is terminated, reference to any package that has been created by a CREATE PACKAGE may be resumed. (Note that this does not preclude additional restrictions: to modify a package, you must have created it with the MODIFY option, and to DESCRIBE a statement in a package, it must have been created with the DESCRIBE option.)
For example, if packages X1, X2, and X3 have been created with a CREATE PACKAGE, where X1 and X2 have the MODIFY and DESCRIBE options. Examples 1, 2, 4, and 5 in Figure 79 are valid, while Examples 3, 6, 7, and 8 are invalid.
If virtual storage consumption by the database manager is an important consideration, you must be aware of the trade-off in using modifiable packages. The amount of virtual storage required to represent statements prepared in the current LUW may be significantly more than that required for previously prepared statements. If you enter a COMMIT before executing the statement, the virtual storage requirement for the package will be considerably less, but additional work will be performed to store the updated package and to reload it for execution.
You should make this trade-off based on the nature of the preprocessing in your application.
When declaring extended dynamic cursors, you must consider virtual storage requirements. Cursor names are dynamically mapped to statement numbers when DECLARE CURSOR statements are executed. A small amount of virtual storage is required for each uniquely named cursor declared in an LUW. This storage is not released until the end of the LUW. The amount of storage held, therefore, can become quite large when many unique cursor names are declared.
It is a good practice to always do a COMMIT WORK or ROLLBACK WORK in your program that contains extended dynamic statements before you terminate the program. If you use extended dynamic statements in a CICS/VSE transaction, it is imperative that a COMMIT or ROLLBACK WORK be done before ending the transaction. (A CICS abnormal termination may occur, especially if the logical unit of work contains a CREATE PACKAGE statement.)
If virtual storage consumption by the database manager is an important consideration, you should be aware of the trade-off in using modifiable packages. The amount of virtual storage required to represent statements prepared in the current LUW may be significantly more than that required for previously prepared statements. If you enter a COMMIT WORK before executing the statement, the virtual storage requirement for the package will be considerably less, but additional work will be performed to store the updated package and to reload it for execution.
You should make this trade-off based on the nature of the preprocessing in your application.
When declaring extended dynamic cursors, you must consider virtual storage requirements. Cursor names are dynamically mapped to statement numbers when DECLARE CURSOR statements are executed. A small amount of virtual storage is required for each uniquely named cursor declared in an LUW. This storage is not released until the end of the LUW. The amount of storage held, therefore, can become quite large when many unique cursor names are declared.
Figure 80 shows how static and dynamic SQL statements
are mapped to the SQL statements that preprocess and execute them.
Figure 80. Mapping Extended Dynamic to Static and Dynamic Statements
|
Static and Dynamic SQL Statement |
SQL Statement Executed at Preprocessing Time | SQL Statement Executed at Run Time | ||
|---|---|---|---|---|
| CLOSE | N/A | Extended CLOSE | ||
| COMMIT | N/A | COMMIT | ||
| CONNECT | N/A | CONNECT | ||
| DECLARE CURSOR FOR statement | Basic Extended PREPARE of statement | Extended DECLARE CURSOR | ||
| DECLARE CURSOR FOR statement_name | See Figure 81 | Extended DECLARE CURSOR | ||
| DESCRIBE statement_name | N/A | Extended DESCRIBE | ||
| DROP PACKAGE | N/A | DROP PACKAGE | ||
| EXECUTE | N/A | Extended EXECUTE | ||
| EXECUTE IMMEDIATE string_constant | Basic Extended PREPARE of string_constant | Extended EXECUTE | ||
| EXECUTE IMMEDIATE host_variable | Empty Extended PREPARE |
Temporary Extended PREPARE Extended EXECUTE | ||
| FETCH | N/A | Extended FETCH | ||
| OPEN | N/A | Extended OPEN | ||
| PREPARE string_constant | Basic Extended PREPARE of string_constant1 | N/A | ||
| PREPARE host_variable | Empty Extended PREPARE1 | Temporary Extended PREPARE | ||
| PUT | N/A | Extended PUT | ||
| ROLLBACK | N/A | ROLLBACK | ||
| SELECT INTO | Single row Extended PREPARE | Extended EXECUTE | ||
| Other executable statements | Basic Extended PREPARE | Extended EXECUTE | ||
| Non-executable statements | N/A | N/A | ||
| ||||
Figure 81 shows the SQL statements that prepare statements executed
with a cursor.
Figure 81. Preprocessing Related PREPARE and DECLARE CURSOR Statements
| Example Statements |
Extended Dynamic SQL Statement Executed at Preprocessing Time | ||
|---|---|---|---|
|
PREPARE string_constant DECLARE CURSOR statement_name |
Basic Extended PREPARE N/A | ||
|
PREPARE host_variable DECLARE CURSOR statement_name |
Empty Extended PREPARE N/A | ||
|
DECLARE CURSOR statement_name PREPARE string_constant |
Empty Extended PREPARE Temporary Extended PREPARE1 | ||
|
DECLARE CURSOR statement_name PREPARE host_variable |
Empty Extended PREPARE N/A | ||
| |||
The following SQL facilities are not supported for statements that are prepared using extended dynamic SQL, unless the application program that performs the extended PREPARE statement supplies the support:
These restrictions do not apply to FORTRAN application programs, because the DB2 Server for VSE & VM preprocessors provide the necessary support.
Refer to the DB2 Server for VSE & VM SQL Reference manual for more information on restrictions that apply to extended dynamic statements.
