Programming hints and tips for CLI applications

These hints and tips can help you tune and improve the logic of your CLI applications.

The KEEPDYNAMIC behavior refers to the server's ability to keep a dynamic statement in a prepared state, even after a commit is performed. This behavior eliminates the need for the client to prepare the statement again, the next time the statement is executed. Some CLI/ODBC applications on the client might improve their performance by taking advantage of the KEEPDYNAMIC behavior on servers that are Db2® for z/OS® and OS/390® Version 7 and later.

For Db2 for z/OS V12, you do not need to specify the KEEPDYNAMIC keyword to avail the preserve statement cache behavior. Db2 for z/OS V12 preserves the dynamic statement cache both on the commit and rollback if all the following conditions are met:
  1. The application is using the packages that are bound with KEEPDYNAMIC YES.
  2. The Db2 for z/OS special register CURRENT APPLICATION COMPATIBILITY is set to V12R1.
  3. The dynamic sections exist.
If the special register CURRENT APPLICATION COMPATIBILITY is set to V11R1 or below, The Db2 server preserves the dynamic statement cache only on commit.
Complete the listed steps to enable KEEPDYNAMIC behavior:
  1. Enable the dynamic statement cache on the Db2 for z/OS and OS/390 server (see the Db2 for z/OS and OS/390 server documentation).
  2. Bind the db2clipk.bnd file on your Db2 client with the KEEPDYNAMIC and COLLECTION options. The example shows how to bind db2clipk.bnd, creating a collection named KEEPDYNC:
    • db2 connect to database_name user userid using password
    • db2 bind db2clipk.bnd SQLERROR CONTINUE BLOCKING ALL KEEPDYNAMIC YES COLLECTION KEEPDYNC GRANT PUBLIC
    • db2 connect reset
  3. Inform the client that the KEEPDYNAMIC bind option is enabled for your collection by performing either of the listed examples:
    • Set the CLI/ODBC configuration keywords in the db2cli.ini file: KeepDynamic = 1, CurrentPackageSet = collection name created in Step 2. For example: 
      [dbname] 
KeepDynamic=1
CurrentPackageSet=KEEPDYNC
    • Set the SQL_ATTR_KEEPDYNAMIC and SQL_ATTR_CURRENT_PACKAGE_SET connection attributes in the CLI/ODBC application. For example: 
      SQLSetConnectAttr(hDbc, 
                  SQL_ATTR_KEEP_DYNAMIC, 
                  (SQLPOINTER) 1, 
                   SQL_IS_UINTEGER ); 

SQLSetConnectAttr(hDbc, 
                  SQL_ATTR_CURRENT_PACKAGE_SET, 
                   (SQLPOINTER) "KEEPDYNC", 
                  SQL_NTS);
See the Db2 for z/OS and OS/390 server documentation for further information about KEEPDYNAMIC behavior and configuration.

Common connection attributes

The listed connection attributes can be set by CLI applications:
  • SQL_ATTR_AUTOCOMMIT - Generally this attribute should be set to SQL_AUTOCOMMIT_OFF, because each commit request can generate extra network flow. Only leave SQL_AUTOCOMMIT_ON on if specifically needed.
    Note: The default is SQL_AUTOCOMMIT_ON.
  • SQL_ATTR_TXN_ISOLATION - This connection attribute determines the isolation level at which the connection or statement will operate. The isolation level determines the level of concurrency possible, and the level of locking required to execute the statement. Applications must choose an isolation level that maximizes concurrency, yet ensures data consistency.

Common statement attributes

The listed statement attributes might be set by CLI applications:
  • SQL_ATTR_MAX_ROWS - Setting this attribute limits the number of rows returned to the application from query operations. This can be used to avoid overwhelming an application with a very large result set generated inadvertently, which is especially useful for applications on clients with limited memory resources.

    Setting SQL_ATTR_MAX_ROWS while connected to Db2 for z/OS Version 7 and later will add OPTIMIZE FOR n ROWS and FETCH FIRST n ROWS ONLY clauses to the statement. For versions of Db2 for z/OS before Version 7 and any DBMS that does not support the FETCH FIRST n ROWS ONLY clause, the full result set is still generated at the server using the OPTIMIZE FOR n ROWS clause, however CLI will count the rows on the client and only fetch up to SQL_ATTR_MAX_ROWS rows.

  • SQL_ATTR_CURSOR_HOLD - This statement attribute determines if the cursor for this statement will be declared by CLI using the WITH HOLD clause.

    Resources associated with statement handles can be better utilized by the server if the statements that do not require cursor-hold behavior have this attribute set to SQL_CURSOR_HOLD_OFF. The efficiency gains obtained by the appropriate use of this attribute are considerable on OS/390 and Db2 for z/OS.

    Note: Many ODBC applications expect a default behavior where the cursor position is maintained after a commit.
  • SQL_ATTR_TXN_ISOLATION - CLI allows the isolation level to be set at the statement level, however, it is recommended that the isolation level be set at the connection level. The isolation level determines the level of concurrency possible, and the level of locking required to execute the statement.

    Resources associated with statement handles can be better utilized by CLI if statements are set to the required isolation level, rather than leaving all statements at the default isolation level. This should only be attempted with a thorough understanding of the locking and isolation levels of the connected DBMS.

    Applications should use the minimum isolation level possible to maximize concurrency.

Reusing statement handles

Each time a CLI application declares a statement handle, the CLI driver allocates and then initializes an underlying data structure for that handle. To increase performance, CLI applications can reuse statement handles with different statements, thereby avoiding the costs associated with statement handle allocation and initialization.
Note: Before reusing statement handles, memory buffers and other resources used by the previous statement might need to be released by calling the SQLFreeStmt() function. Also, statement attributes previously set on a statement handle (for example, SQL_ATTR_PARAMSET_SIZE) must be explicitly reset, otherwise they might be inherited by all future statements using the statement handle.

Binding and SQLGetData()

Generally it is more efficient to bind application variables or file references to result sets than to use SQLGetData(). When the data is in a LOB column, LOB functions are preferable to SQLGetData() (see Large object data optimization for more information). Use SQLGetData() when the data value is large variable-length data that:
  • must be received in pieces, or
  • might not need to be retrieved.

Limiting use of catalog functions

Catalog functions, such as SQLTables(), force the CLI driver to query the DBMS catalog tables for information. The queries issued are complex and the DBMS catalog tables can be very large. In general, try to limit the number of times the catalog functions are called, and limit the number of rows returned.

The number of catalog function calls can be reduced by calling the function once, and having the application store (cache) the data.

The number of rows returned can be limited by specifying a:
  • Schema name or pattern for all catalog functions
  • Table name or pattern for all catalog functions other than SQLTables()
  • Column name or pattern for catalog functions that return detailed column information.

Remember that although an application might be developed and tested against a data source with hundreds of tables, it might be run against a database with thousands of tables. Consider this likelihood when developing applications.

Close any open cursors (call SQLCloseCursor() or SQLFreeStmt() with SQL_CLOSE Option) for statement handles used for catalog queries to release any locks against the catalog tables. Outstanding locks on the catalog tables can prevent CREATE, DROP or ALTER statements from executing.

Column names of function generated result sets

The column names of the result sets generated by catalog and information functions might change as the ODBC and CLI standards evolve. The position of the columns, however, will not change.

Any application dependency might be based on the column position (iCol parameter used in SQLBindCol(), SQLGetData(), and SQLDescribeCol()) and not the name.

CLI-specific functions loaded from ODBC applications

The ODBC Driver Manager maintains its own set of statement handles which it maps to the CLI statement handles on each call. When a CLI function is called directly, it must be passed to the CLI driver statement handle, as the CLI driver does not have access to the ODBC mapping.

Call SQLGetInfo() with the SQL_DRIVER_HSTMT option to obtain the CLI statement handle (HSTMT). The CLI functions can then be called directly from the shared library or DLL, passing the HSTMT argument where required.

Global dynamic statement caching

Db2 servers at version 5 or later for UNIX or Windows have a global dynamic statement cache. This cache is used to store the most popular access plans for prepared dynamic SQL statements.

Before each statement is prepared, the server automatically searches this cache to see if an access plan has already been created for this exact SQL statement (by this application or any other application or client). If so, the server does not need to generate a new access plan, but will use the one in the cache instead. There is now no need for the application to cache connections at the client unless connecting to a server that does not have a global dynamic statement cache.

Data insertion and retrieval optimization

The methods that describe using arrays to bind parameters and retrieve data use compound SQL to optimize network flow. Use these methods as much as possible.

Large object data optimization

Use LOB data types and the supporting functions for long strings whenever possible. Unlike LONG VARCHAR, LONG VARBINARY, and LONG VARGRAPHIC types, LOB data values can use LOB locators and functions such as SQLGetPosition() and SQLGetSubString() to manipulate large data values at the server.

LOB values can also be fetched directly to a file, and LOB parameter values can be read directly from a file. This saves the resource utilization of the application transferring data through application buffers.

Case sensitivity of object identifiers

All database object identifiers, such as table names, view names and column names are stored in the catalog tables in uppercase unless the identifier is delimited. If an identifier is created using a delimited name, the exact case of the name is stored in the catalog tables.

When an identifier is referenced within an SQL statement, it is treated as case insensitive unless it is delimited.

For example, if the listed two tables are created, 
  CREATE TABLE MyTable   (id INTEGER)
  CREATE TABLE "YourTable" (id INTEGER)
two tables will exist, MYTABLE and YourTable
Both of the statements are equivalent: 
  SELECT * FROM MyTable (id INTEGER)
  SELECT * FROM MYTABLE (id INTEGER)
The second statement in the example will fail with TABLE NOT FOUND because there is no table named YOURTABLE: 
  SELECT * FROM "YourTable" (id INTEGER)   // executes without error
  SELECT * FROM YourTable (id INTEGER)     // error, table not found

All CLI catalog function arguments treat the names of objects as case sensitive, that is, as if each name was delimited.

SQLDriverConnect() versus SQLConnect()

Using SQLDriverConnect() allows the application to rely on the dialog box provided by CLI to prompt the user for the connection information.

If an application uses its own dialog boxes to query the connect information, the user might be able to specify additional connect options in the connection string. The string can also be stored and used as a default on subsequent connections.

Turning off statement scanning

CLI by default, scans each SQL statement searching for vendor escape clause sequences.

If the application does not generate SQL statements that contain vendor escape clause sequences, then the SQL_ATTR_NOSCAN statement attribute must be set to SQL_NOSCAN_ON at the connection level so that CLI does not perform a scan for vendor escape clauses.

Holding cursors across rollbacks

Applications that must deal with complex transaction management issues might benefit from establishing multiple concurrent connections to the same database. Each connection in CLI has its own transaction scope, so any actions performed on one connection do not affect the transactions of other connections.

For example, all open cursors within a transaction get closed if a problem causes the transaction to be rolled back. An application can use multiple connections to the same database to separate statements with open cursors; because the cursors are in separate transactions, a rollback on one statement does not affect the cursors of the other statements.

However, using multiple connections might mean bringing some data across to the client on one connection, and then sending it back to the server on the other connection. For example:
  • Suppose in connection #1 you are accessing large object columns and have created LOB locators that map to portions of large object values.
  • If in connection #2, you want to use (for example to insert) the portion of the LOB values represented by the LOB locators, you must move the LOB values in connection #1 first to the application, and then pass them to the tables that you are working with in connection #2. This is because connection #2 does not know anything about the LOB locators in connection #1.
  • If you only had one connection, then you can just use the LOB locators directly. However, the LOB locators are lost as soon as you rolled back your transaction.
Note: When multiple connections to a single database are used by an application, the application must be careful to synchronize access to database objects or it might experience various lock contention issues, as database locks are not shared between transactions. Updates by one connection can easily force other connections into a lock-wait state until the first connection releases the lock (through a COMMIT or ROLLBACK).

Preparing compound SQL sub-statements

In order to maximize efficiency of the compound statement, sub-statements might be prepared before the BEGIN COMPOUND statement, and then executed within the compound statement.

This also simplifies error handling because prepare errors can be handled outside of the compound statement.

User-defined types and casting

If a parameter marker is used in a predicate of a query statement, and the parameter is a user defined type, the statement must use a CAST function to cast either the parameter marker or the UDT.

For example, suppose the listed type and table is defined: 
 CREATE DISTINCT TYPE CNUM AS INTEGER WITH COMPARISONS

 CREATE TABLE CUSTOMER (
         Cust_Num     CNUM NOT NULL,
         First_Name   CHAR(30) NOT NULL,
         Last_Name    CHAR(30) NOT NULL,
         Phone_Num    CHAR(20) WITH DEFAULT,
         PRIMARY KEY  (Cust_Num) )
Suppose also that the listed SQL statement was then issued: 
  SELECT first_name, last_name, phone_num from customer
  WHERE cust_num = ?
This statement fails because the parameter marker cannot be of type CNUM and thus the comparison fails due to incompatible types.
Casting the column to integer (its base SQL type), allows the comparison to work because a parameter can be provided for type integer: 
  SELECT  first_name, last_name, phone_num from customer
  where cast( cust_num as integer ) = ?
Alternatively the parameter marker can be cast to INTEGER and the server can then apply the INTEGER to CNUM conversion: 
  SELECT  first_name, last_name, phone_num FROM customer
  where cust_num = cast( ? as integer )

Deferred prepare to reduce network flow

In CLI, deferred prepare is on by default. The PREPARE request is not sent to the server until the corresponding execute request is issued. The two requests are then combined into one command/reply flow (instead of two) to minimize network flow and to improve performance. This is of greatest benefit when an application generates queries with very small answer sets, because the resource utilization for requests and replies flowing over the network represents a large percentage of the processing time. In an environment where a Db2 Connect or DDCS gateway is used, there is a greater opportunity for cost reduction because four request and reply combinations are reduced to two.
Note: Functions such as SQLDescribeParam(), SQLDescribeCol(), SQLNumParams(), and SQLNumResultCols() require that the statement has been prepared. If the statement has not already been prepared, these functions trigger an immediate PREPARE request to the server, and the benefit of deferred prepare does not occur.