DB2 packages: Concepts, examples, and common problems

Understanding DB2 system and user application packages

Return to article

Table 2. CLI keywords
KeywordDescriptionSyntaxEquivalent statement attributeUsage
ReoptEnable query optimization (or reoptimization) of SQL statements that have special registers or parameter markersReopt= 2|3|4SQL_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:

  • 2 - None (SQL_REOPT_NONE)
  • 3 - Once (SQL_REOPT_ONCE)
  • 4 - Always (SQL_REOPT_ALWAYS)

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:

db2 bind db2clipk.bnd collection NULLIDR1

db2 bind db2clipk.bnd collection NULLIDRA

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.

CurrentPackagePathIssues SET CURRENT PACKAGE PATH =? after every connectionCURRENTPACKAGEPATH = (package path)SQL_ATTR_CURRENT_PACKAGE_PATHThis option will issue the command SET CURRENT PACKAGE PATH = (package path) after every connection to the database. By default, this special register is not modified. See the documentation for SET CURRENT PACKAGE PATH for more details. This statement calls sqleseti to send a special register setting to OS390 v8 to indicate the path to search for a given package/section. This does not have a lot of usefulness for CLI applications with the exception of ODBC -- static processing.
CLIPkgNumber of large packages to be generatedCLIPKG = 3 | 4 | ... | 30N/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 CLIPKG bind option. Also, the db2cli.ini file on the server (DB2 UDB V6.1 or later on UNIX or Intel platforms) must be updated with the same value of CLIPKG.

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.

StaticPackageSpecify the package to be used with the static profiling featureSTATICPACKAGE = (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:

  • -0 for Uncommitted Read (UR)
  • -1 for Cursor Stability (CS)
  • -2 for Read Stability (RS)
  • -3 for Repeatable Read (RR)
  • -4 for No Commit (NC)
KeepDynamicSpecify that CLI packages were bound with KEEPDYNAMIC YES option against DB2/390KEEPDYNAMIC = 0 | 1N/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:

  1. The application process ends
  2. A rollback operation occurs
  3. The application executes an explicit PREPARE statement with the same statement identifier.

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

CurrentPackageSetIssue 'SET CURRENT PACKAGESET schema' after every connectionCURRENTPACKAGESET = (schema_name)SQL_ATTR_CURRENT_PACKAGE_SET

This option will issue the command SET CURRENT PACKAGESET schema after every connection to a database. By default, this clause is not appended.

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:

  1. Choose a schema to use when running SQL statements from CLI/ODBC applications.
  2. Ensure the objects in the schema have the desired privileges and then rebind accordingly.
  3. Set the CURRENTPACKAGESET option to this schema.

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:

"NULLID"

"NULLIDR1"

"NULLIDRA"

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.

UseOldSPCallControls how cataloged procedures are invokedUSEOLDSTPCALL = 0 | 1N/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.

StaticModeSpecify whether the CLI/ODBC application will capture SQL or use a static SQL package for this DSNSTATICMODE = DISABLED | CAPTURE | MATCHN/A

This option allows you to specify how the SQL issued by the CLI/ODBC application for this DSN will be processed:

  • DISABLED = Static mode disabled. No special processing. The CLI/ODBC statements will be executed as dynamic SQL with no change. This is the default.
  • CAPTURE = Capture Mode. Execute the CLI/ODBC statements as dynamic SQL. If the SQL statements are successful, they will be captured into a file (known as the Capture File) to be bound by the DB2CAP command later.
  • MATCH = Match mode. Execute the CLI/ODBC statements as static SQL statements if a matching statement is found in the Capture Files specified in STATICPACKAGE. The Capture File must first be bound by the DB2CAP command.
AppendForFetchOnlyControl whether we append FOR FETCH ONLY clause to READ ONLY SQL statementsAppendForFetchOnly=0|1SQL_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.

Usage notes:

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.

Return to article