Call level interface (CLI) driver enhancements

The CLI driver contains a number of major enhancements for the Db2 Version 11.1 base product, its mod packs, and its fix packs. Each fix pack for a release is cumulative; that is, a particular fix pack contains the enhancements and new features that were included in previous fix packs.

V11.1 enhancements

The Db2 V11.1 release includes the following enhancements and new capabilities for the CLI driver:
Table 1. CLI enhancements and new capabilities in V11.1
Enhancement Description
db2trc -tracecomm A new mechanism has been provided to capture network request in CLI trace through db2trc utility.
db2trc -tracetimestamp A new mechanism has been provided to capture timestamps in CLI trace through db2trc utility.
db2trc -stoptrc When tracing is turned on, the relevant data is captured in the trace file. When the trace file size reaches its limit, the trace file is overwritten with new data. With this, customers lose on some important data for some specific errors. Now a mechanism has been provided such that the db2trc & serviceability trace logging is stopped on the occurrence of some specific errors.

Mapping of Netezza connection string keywords to CLI connection string keywords

Mapping of Netezza environment, connection and statement attributes

Option to query metadata in uppercase in APIs like SQLTables

Db2 provides tools and features to help users migrate to Db2. These tools and features enable the users to seamlessly migrate their application and database into a Db2 environment. Netezza is one of the leading Data Warehouse solutions. As part of this, there are efforts to close the gaps between Netezza and Db2 ODBC drivers. The 3 features are done in this direction.
Passive Connect - connect without activating the database Db2 servers always implicitly activates the database if not already active before connecting to the database. Server also allows passive connection, but it always requires the database to be active already. Customers would like to establish passive connections to Db2 for LUW server sometimes for monitoring purpose. Now a explicit switch has been provided that could be enabled or disabled to establish a passive connection or normal (default at the server) connection to the Db2 database.

Microsoft Application Virtualization (App-V) Sequencer

The App-V client presents the user with a neat, locally-installed application experience for virtualized applications. This feature provides the sequencing, configuration and installation steps to create an App-V package for the IBM Data Server Driver using Microsoft App-V sequencer. For more information, see Microsoft Application Virtualization (App-V) Sequencer

Connection attribute to efficiently set global variables

The attribute SQL_ATTR_SESSION_GLOBAL_VAR is used to set one or more global variables separated by semicolon.

The global variables will be chained to the next SQL request going to the server to avoid extra network trip.

V11.1 MP1 FP1 enhancements

The Db2 V11.1 Mod Pack 1 and Fix Pack 1 release includes the following enhancements and new capabilities for the CLI driver:
Table 2. CLI enhancements and new capabilities in V11.1 Mod Pack 1 and Fix Pack 1
Enhancement Description

In-memory default keystore database for SSL connection

The default keystore database is now created in memory instead of in a file. The CLI driver will create a keystore database only when the application requests an SSL connection for the first time, and when the application has not passed any value for SSLClientKeystoredb. If the SSL keystore database path (SSLClientKeystoredb) does not exist, the CLI driver will not create the key database and the connection to the database will fail.

New function level support for Db2 for z/OS® continuous delivery

A new infotype SQL_DBMS_FUNCTIONLVL is provided in the SQLGetInfo() API. Applications are now able to get the functional/build level of the server.

Register ODBC DSN with description

With the description addition to the registry, ODBC applications like MS Access can now list the description from the registry along with the DSN.

Dynamic enabling/disabling of serviceability log

Users can modify the StopServiceabilityTracing keyword value in db2dsdriver.cfg file dynamically so that serviceability logging can be stopped or started. A new keyword, CfgRefreshInterval, specifies the interval in seconds to wait before it attempts to read the db2dsdriver.cfg file.

Blocking support for scrollable cursors for lobs and non-lobs

To reduce the cost of fetching through scrollable cursors, CLI provides a new statement attribute SQL_ATTR_PREFETCH_NROWS.

GUI support in Microsoft ODBC Data Source Administrator for DSN creation

When a new System, User DSN or FileDSN is created for the IBM Data Server Driver using the Microsoft ODBC Administrator, you will be prompted for mandatory fields. With the enhancements that are made to the Microsoft ODBC Administrator, you can add Host, Port, or Database in TCP/IP, and FileDSN or "Enable TCP/IP SSL security option" in the security tab.

External table support by ODBC/CLI

External table operations are now supported by the CLI driver’s implementations of SQLExecute( ), SQLExecDirect(), and SQLFetch() APIs. The ODBC/CLI application is now able to run all types of queries that are related to external tables.

V11.1 MP2 FP2 enhancements

The Db2 V11.1 Mod Pack 2 and Fix Pack 2 releases include the following enhancements and new capabilities for the CLI driver:
Table 3. CLI enhancements and new capabilities in V11.1 MP2 and FP2:
Enhancement Description

Register ODBC DSN with DESCRIPTION through db2cli.ini

With the description addition to the registry, ODBC applications like MS Access can now list the description from the registry along with the DSN. The description information can now be specified in db2cli.ini as well.

A mechanism to chain special registers along with the next SQL

A new mechanism to chain special registers along with the next SQL statement and flow to the server instead of flowing immediately by that reducing the network trips. CLI provides the SQL_ATTR_SPECIAL_REGISTER attribute to specify the list of special registers.

Improvements to serviceability log

On the occurrence of a specific event (SQLCODE), information is logged in to the serviceability log file to provide better diagnostic information for a given connection and for the SQL being executed:

  • Code-page information related to the connection
  • Monitoring information like Application ID, Handle and External Name (EXTNAM) of server
  • SQL statement, Attributes, package information (If an error occurs while executing SQL)
  • Application binding information (If any)
  • Result-set and Parameter description (If any)
  • XA information (If it is an XA connection)
  • SSL information (if the connection is SSL)
  • Application type and phase of the SQL execution that developed the error

CLI trace optimization through db2trc command

To allow users to control the type of information that is logged in to the CLI trace, a new db2trc command line option is introduced viz -clitracelevel that can take the following values:

0 (default) : CaptureFull trace.

1 : Do not log bind-in data that is supplied by the application. Bind-in data is the data supplied by the application to client from API calls like SQLBindParameter()SQLBindCol()SQLExtendedBind() via rgbValue application data pointer(s).

2 : Do not log bind-out data that is returned to application. Bind-out data is the data returned by the client to the application from application data pointers set by using API calls SQLBindParamer(), SQLBindCol(), SQLExtendedBind() and so on, via rgbValue application data pointer(s).

3 : Do not log bind-in data supplied by and bind-out data returned to the application.

Allow users to log information into the CLI trace based on a search pattern

A new db2trc command line option -pattern is introduced. The new option can take multiple search patterns that are separated by the semicolon (;) character. The multiple search patterns must be enclosed within double quotation marks. Up to 10 search patterns with not more than a combined 255 characters can be specified with the patterns that are separated by semicolon character.

ODBC Administrator Enhancement

For IBM DB2 Instance based clients, in Microsoft ODBC Administrator window, a new check box element in GUI labeled "Enable SSL security" under the Security tab is added. The check box can be used to set the Security configuration parameter value as SSL.

Connection attribute to efficiently set global variables

The attribute SQL_ATTR_SESSION_GLOBAL_VAR is used to set one or more global variables separated by semicolon.

The global variables will be chained to the next SQL request going to the server to avoid extra network trip.

V11.1 MP3 FP3 enhancements

The Db2 V11.1 Mod Pack 3 and Fix Pack 3 releases include the following enhancements and new capabilities for the CLI driver:
Table 4. CLI enhancements and new capabilities in V11.1 MP3 and FP3:
Enhancement Description

Enable Bind functionality to automatically take instance less or instance-based path for the bind file

When just the bind file name without any path is specified for the bind file parameter, the file would be first searched in the current directory. If it is present in the current directory, then that file would be picked up and the package would be created. If the bind file is not present in the current directory, the file would be automatically picked up from the instance or install path and the package will be created.

FOR N ROWS support in dynamic Embedded SQL

“FOR N ROWS” syntax is provided to support the array insert with dynamic SQL in embedded programs. ‘N’ can be an integer or a host variable declared of type short or int. An application can specify the cardinality for the array insert operation that uses the “FOR N ROWS”.

“FOR N ROWS” syntax is also supported for static INSERT SQL.