DB2 packages: Concepts, examples, and common problems
Understanding DB2 system and user application packages
Table 2. CLI keywords
|Keyword||Description||Syntax||Equivalent statement attribute||Usage|
|Reopt||Enable query optimization (or reoptimization) of SQL statements that have special registers or parameter markers||Reopt= 2|3|4||SQL_ATTR_REOPT|
When this keyword is used, the access path for an SQL statement, which contains parameter markers or special registers, will be optimized using the values of these variables rather than the default estimates that are chosen by the compiler. The optimization takes place at query execution time when the values are available. The valid values of the keyword are:
By default, SQL_REOPT_NONE is used; the default "NULLID" package set is used to execute dynamic SQL statements.
If SQL_REOPT_ONCE is set, the "NULLIDR1" package set is used. This package set is bound with the REOPT ONCE option.
If SQL_REOPT_ALWAYS is set, the "NULLIDRA" package set is used. This package set is bound with the REOPT ALWAYS option.
The "NULLIDR1" and "NULLIDRA" package sets are not created by default; they can be created by issuing the following command:
Note: The "NULLIDR1" and "NULLIDRA" are reserved package set names. When used, REOPT ONCE and REOPT ALWAYS are implied respectively.
Due to the way the REOPT support is implemented using various package sets, SQL_ATTR_REOPT and SQL_ATTR_CURRENT_PACKAGE_SET are mutually exclusive. Therefore, if one is set, the other is not allowed. If both Reopt and CurrentPackageSet keywords are specified, CurrentPackageSet takes precedence.
|CurrentPackagePath||Issues ||CURRENTPACKAGEPATH = (package path)||SQL_ATTR_CURRENT_PACKAGE_PATH||This option will issue the command |
|CLIPkg||Number of large packages to be generated||CLIPKG = 3 | 4 | ... | 30||N/A|
If the value is NOT an integer between three and 30, the default will be used without error or warning.
This keyword is used to increase the number of sections for SQL statements in CLI/ODBC applications. If it is used,
you should explicitly bind the required CLI bind files with
This setting only applies to large packages (containing 364 sections). The number of small packages (containing 64 sections) is three and cannot be changed.
It is recommended that you only increase the number of sections enough to run your application, as the packages take up space in the database.
|StaticPackage||Specify the package to be used with the static profiling feature||STATICPACKAGE = (collection_id.package_name)||N/A|
Only Applicable when STATICMODE is set to CAPTURE.
This keyword is used to specify the package to be used when the application runs in Match Mode. You first need to use Capture Mode to create the Capture File.
Only the first seven characters of the indicated package name will be used. A one-byte suffix will be added to represent each isolation level, as follows:
|KeepDynamic||Specify that CLI packages were bound with KEEPDYNAMIC YES option against DB2/390||KEEPDYNAMIC = 0 | 1||N/A|
This keyword only has effect against DB2/390, where the CLI packages have been bound with the KEEPDYNAMIC YES option. This keyword should be used in conjunction with the CURRENTPACKAGESET keyword, but this is not a strict requirement.
Here's the DB2/390 documentation on how KEEPDYNAMIC works:
KEEPDYNAMIC determines whether DB2 keeps dynamic SQL statements after commit points.
KEEPDYNAMIC = NO (0) specifies that DB2 does not keep dynamic SQL statements after commit points.
KEEPDYNAMIC = YES (1) specifies that DB2 keeps dynamic SQL statements after commit points.
If you specify KEEPDYNAMIC = YES (1), the application does not need to prepare an SQL statement after every commit point. DB2 keeps the dynamic SQL statement until one of the following occurs:
If you specify KEEPDYNAMIC(YES), and the prepared statement cache is active, DB2 keeps a copy of the prepared statement in the cache. If the prepared statement cache is not active, DB2 keeps only the SQL statement string past a commit point. DB2 then implicitly prepares the SQL statement if the application executes an OPEN, EXECUTE, or DESCRIBE operation for that statement.
If you specify KEEPDYNAMIC(YES), you must not specify REOPT(VARS). KEEPDYNAMIC(YES) and REOPT(VARS) are mutually exclusive.
Performance hint: KEEPDYNAMIC(YES) results in improved performance if your DRDA client application uses a cursor defined WITH HOLD. DB2 automatically closes a held cursor when there are no more rows to retrieve, which eliminates an extra network message.
The CLI keyword value KEEPDYNAMIC=1 means the CLI packages were bound with KEEPDYNAMIC YES.
The CLI keyword value KEEPDYNAMIC=0 means the CLI packages were bound with KEEPDYNAMIC NO (default).
In V7, specifying KEEPDYNAMIC 1 results in an SQL0518 error on any execute following a COMMIT if the CLI packages were NOT bound with the KEEPDYNAMIC YES option. In V8, re-Prepare logic will be triggered and the statement should be successful. (Performance will suffer because you are forced to go to the server twice.)
To bind the packages with KEEPDYNAMIC YES try:
db2 bind @db2cli.lst blocking all grant public sqlerror continue generic "'KEEPDYNAMIC YES'"
To determine how packages were bound, check the KEEPDYNAMIC column of the SYSIBM.SYSPACKAGE table. For example:
db2 SELECT COLLID, NAME, KEEPDYNAMIC FROM SYSIBM.SYSPACKAGE
|CurrentPackageSet||Issue 'SET CURRENT PACKAGESET schema' after every connection||CURRENTPACKAGESET = (schema_name)||SQL_ATTR_CURRENT_PACKAGE_SET|
This option will issue the command
This statement sets the schema name (collection identifier) that will be used to select the package to use for subsequent SQL statements.
CLI/ODBC applications issue dynamic SQL statements. Using this option, you can control the privileges used to run these statements:
The SQL statements from the CLI/ODBC applications will now run under the specified schema and use the privileges defined there.
The following are reserved package set names:
Due to the way the REOPT support is implemented using various package sets, SQL_ATTR_REOPT and SQL_ATTR_CURRENT_PACKAGE_SET are mutually exclusive. Therefore, if one is set, the other is not allowed. If both Reopt and CurrentPackageSet keywords are specified, CurrentPackageSet takes precedence. Please see the REOPT keyword for more details.
|UseOldSPCall||Controls how cataloged procedures are invoked||USEOLDSTPCALL = 0 | 1||N/A|
Invokes procedures using the new CALL method where GRANT EXECUTE must be granted on the procedure.
Prior to V8.1, CLI treated CALL statements differently than other dynamic SQL statements, whereas other statements would follow a "prepare+describe / dynamic execute" model. CALL statements would follow a "describe / static execute" model. As of V8.1, CLI defaults to treating CALL statements like all other dynamic SQL statements. If dynamic CALL is unsupported (for example, because the server doesn't support dynamic CALL; or the stored procedure is uncatalogued; or USEOLDSTPCALL=1 has been set), CLI reverts back to the pre-V8.1 CALL model.
Similarly, prior to Version 8, the invoker of a procedure had to have EXECUTE privilege on any package invoked from the procedure. Now, the invoker must have EXECUTE privilege on the procedure, and only the definer of the procedure has to have EXECUTE privilege on any required packages.
This keyword controls which method is used to invoke the procedure. Setting USEOLDSTPCALL on causes the procedure to be invoked using the deprecated sqleproc() API when the precompiler fails to resolve a procedure on a CALL statement. Turning this keyword off invokes procedures where GRANT EXECUTE must be granted on the procedure.
|StaticMode||Specify whether the CLI/ODBC application will capture SQL or use a static SQL package for this DSN||STATICMODE = DISABLED | CAPTURE | MATCH||N/A|
This option allows you to specify how the SQL issued by the CLI/ODBC application for this DSN will be processed:
|AppendForFetchOnly||Control whether we append FOR FETCH ONLY clause to READ ONLY SQL statements||AppendForFetchOnly=0|1||SQL_ATTR_APPEND_FOR_FETCH_ONLY|
For legacy reasons, we have always appended the "FOR FETCH ONLY" clause to read only SELECT statements when connected to a z/OS or iSeries database. This attribute allows an application to control this behaviour at the connection level for situations where the application is binding the CLI packages using different bind BLOCKING options (BLOCKING UNAMBIG, for example ) and they wish to suppress the blocking in order to keep positioned on a given row.
The corresponding attribute has been added for SAP running against z/OS.
If no keyword/attribute is specified, you will continue to have different default behaviours for different servers. But if an application indicates that it wishes CLI to append the clause, do it, no matter what the server is ( as there may be a similar reason to enable the appending against Linux, UNIX, and Windows in this future, although this is unlikely). If an application indicates using this keyword/attribute that it does not wish to append, do not append, no matter what the server is, so keyword/connection attribute will supercede any default server support logic we have. It makes the code simpler, and the documentation does not need to reflect limitations for particular servers.
It is best to set it once and leave it for the duration of the application.
It is best to set it either after connection is allocated or immediately after it is established.
The application can query it after connection is established or after this attribute is set -- there is no point in querying it otherwise.
However, if approach suggested above is taken, it is set immediately after the connection is established -- the application should not bother querying it.
Please note that changing this connection attribute in the middle of the application may affect existing statements. These are special cases and you will likely never trigger them. However, it is best to follow the approach suggested above, and this will never be an issue.
Note: To print this page, use landscape printing option.