DB2 Version 9.7 for Linux, UNIX, and Windows

Call level interface (CLI) functionality has been enhanced

Version 9.7 includes new enhancements that extend the CLI functionality that can improve the performance and reliability of applications that use CLI.

CLI applications can retrieve an accurate row count prior to fetching

You can now use the CLI statement attribute SQL_ATTR_ROWCOUNT_PREFETCH to enable a CLI application to retrieve a full row count before fetching.

Restriction: This feature is not supported if the cursor contains LOBs or XML data.

Before the introduction of this feature, calling the SQLRowCount function on a non-scrollable SELECT-only cursor set the contents of RowCountPtr to -1 because the number of rows was not available until all of the data had been fetched.

This support is also available in Version 9.5 Fix Pack 3 and later fix packs.

CLI dynamic packages can be bound on demand

You can use the new SQLCreatePkg API to bind arbitrary packages to a database. You can control some BIND options by using this API.

This support is also available in Version 9.5 Fix Pack 3 and later fix packs.

CLI ping capabilities have been enhanced

CLI applications can now override the default packet size that is used to ping a database and specify the number of times to ping the database before yielding a final result.

Before the introduction of this feature, the packet size was fixed, and you could ping a database one time only. These limitations made it more difficult for you to understand network complexities and judge system performance. With the new enhancements, you have finer control over the ping operation and can view accurate, more meaningful results.

This enhancement introduces two new CLI connection attributes:

SQL_ATTR_PING_REQUEST_PACKET_SIZE: Specifies the size of the ping packet that a CLI application uses when it pings a database
SQL_ATTR_PING_NTIMES: Specifies the number of times that a CLI application pings a database before a final result is returned

The application must call the SQLSetConnectAttr function to set these attributes on a connection handle before pinging a database. If you specify a value greater than 1 for the SQL_ATTR_PING_NTIMES attribute, CLI returns the average time that it took to ping the database over all iterations.

To get the current values for the new attributes, call the SQLGetConnectAttr function and pass SQL_ATTR_PING_NTIMES as the attribute argument.

This support is also available in Version 9.5 Fix Pack 3 and later fix packs.

Anyorder file type modifier can help improve the performance of CLI applications that use the LOAD API

You can help improve the performance of CLI applications that use the LOAD API by using the new SQL_ATTR_LOAD_MODIFIED_BY statement attribute to specify the anyorder file type modifier. Use the statement attribute to specify multiple file type modifiers that are separated by spaces.

For example, the following call specifies the anyorder file type modifier for the CLI LOAD:

char *filemod="anyorder";
SQLSetStmtAttr (hstmt, SQL_ATTR_LOAD_MODIFIED_BY,
                (SQLPOINTER) filemod, SQL_NTS);

Header information in CLI traces can be suppressed

You can suppress header information that is typically displayed in a CLI trace by setting the new SQL_ATTR_TRACENOHEADER environment attribute to 1. When you specify 1 for this attribute, no header information is written to the CLI trace log file. The default value for this attribute is 0.

If you accept the default or specify 0, information that is similar to the following example is displayed in the CLI trace file for every thread that is started:

[ Process: 1856, Thread: -1229691200 ]
[ Date & Time: 07/03/2008 14:43:53.074965 ]
[ Product: QDB2/LINUX DB2 v9.1.0.4 ]
[ Level Identifier: 01050107 ]
[ CLI Driver Version: 09.01.0000 ]
[ Informational Tokens: "DB2 v9.1.0.4","s080122","MI00228","Fixpack4" ]
[ Install Path: /opt/IBM/db2/V9.1.0.4 ]
[ db2cli.ini Location: /xxx/ramdisk2/db/cli/db2cli.ini ]

CLI applications can enable and disable the statement concentrator

You can control whether dynamic statements that contain literal values use the statement cache by setting the new StmtConcentrator CLI/ODBC configuration keyword or the new statement attribute SQL_ATTR_STMT_CONCENTRATOR.

By default, CLI applications use the behavior that is specified on the server.

CLI applications accessing DB2 for z/OS can roll back a transaction during streaming

CLI applications that access DB2® for z/OS® can now roll back a transaction even while in SQL_NEED_DATA state by setting the new SQL_ATTR_FORCE_ROLLBACK connection attribute by using the SQLSetConnectAttr API. This behavior is supported when the StreamPutData CLI/ODBC configuration keyword is set to 1.

Before this enhancement, CLI applications running on DB2 for z/OS had to drop and reestablish the database connection to come out of the SQL_NEED_DATA state.

CLI applications can retrieve data in an interleaved fashion for LOB objects in the same row

When querying data servers that support the Dynamic Data Format, CLI applications can now call for previously accessed LOB columns, and maintain the data offset position from the previous SQLGetData() call. You control this behavior by specifying the new AllowInterleavedGetData CLI/ODBC configuration keyword or the new SQL_ATTR_ALLOW_INTERLEAVED_GETDATA statement attribute.

Before this enhancement, CLI applications could call SQLGetData() for previously accessed LOB, if you specified the AllowGetDataLOBReaccess CLI/ODBC configuration keyword. However, there was no way to maintain the data position and offset information.

CLI applications support named parameter markers

CLI applications can now process SQL statements that contain named parameter markers represented by a colon (:) followed by a name. For example, the following syntaxes are now supported:

CALL addEmp(?,?,?,?);
CALL addEmp(:empNo, :empName, :empDeptNo, :empAddr);

Before this enhancement, you could not pass procedure arguments in an order that differed from the order in which you defined the parameters when creating the procedure.

CLI provides no support to bind by name. CLI processes anything that matches a valid parameter marker, and treats it as if it is a normal parameter marker, which is represented by a question mark (?).

To enable CLI support for named parameter processing, set the new EnableNamedParameterSupport CLI/ODBC configuration keyword to TRUE. By default, named parameter processing is disabled in the IBM® Data Server Driver for ODBC and CLI for all servers.

CLI applications support default parameter values

When you use the CALL statement to call a procedure, you no longer have to specify values for all the parameters. Unspecified parameters take the default values that you defined for the procedure.

For example, the following statement creates a procedure that has default parameter values:

CREATE PROCEDURE addEmp (
IN empNo       INTEGER       DEFAULT 100, 
IN empName     VARCHAR(20)   DEFAULT 'nothing',
IN empDeptNo   INTEGER       DEFAULT 2, 
IN empAddr     VARCHAR(100)  DEFAULT 'San Jose, CA'
) ...

When you call this procedure in a CLI application, you can omit specifying a value for any parameter that has a default value. The value for the missing parameter is supplied by the server. Therefore, the following example no longer results in an error:

CALL addEmp (empName => 'John', 
empDeptNo => 1, 
empAddr => 'Bangalore')

CLI applications support compiled compound SQL statements

You can now use compound SQL statements that include DECLARE, BEGIN, and END blocks in CLI applications. The statements are sent to the server as a single compound statement block. For example, the following statement is sent to the server as a single statement block:

BEGIN  
  INSERT INTO T0 VALUES (V0);
  INSERT INTO T1 VALUES (V1);
END

Compound SQL statements are not supported if you use CLI array input chaining.

The behavior of cursor stability scans in CLI applications can be controlled

You can now use the ConcurrentAccessResolution CLI/ODBC configuration keyword to specify a prepare attribute that overrides the behavior specified for cursor stability (CS) scans. You can choose to use currently committed semantics, wait for the outcome of the transaction, or skip locked data. This setting overrides the default behavior for currently committed semantic that is defined by the cur_commit configuration parameter.

CLI applications support additional data type conversions and variable-length TIMESTAMP data type

CLI applications now support conversions between the following data types:

Table 1. Support for additional data type conversions in CLI
SQL data type	C data type
SQL_BIGINT SQL_DECIMAL SQL_DECFLOAT SQL_DOUBLE SQL_FLOAT SQL_INTEGER SQL_NUMERIC SQL_REAL SQL_SMALLINT	SQL_C_DBCHAR
SQL_TYPE_DATE	SQL_C_TYPE_TIMESTAMP SQL_C_CHAR
SQL_TYPE_TIME	SQL_C_TYPE_TIMESTAMP
SQL_TYPE_TIMESTAMP	SQL_C_CHAR

In addition, CLI performs the conversions that are required to support a variable-length timestamp of the form TIMESTAMP(p), where the precision of the timestamp value, p, is between 0 and 12. CLI generates truncation warnings and errors as necessary during the conversion.

The new SQL_ATTR_REPORT_TIMESTAMP_TRUNC_AS_WARN CLI statement attribute is also available to control whether a datetime overflow results in an error (SQLSTATE 22008) or warning (SQLSTATE 01S07).

FP3: APIs to add and drop databases

CLI applications can now use the SQLCreateDb() and SQLDropDb() APIs to add and drop databases. You can use the equivalent SQLCreateDbW() and SQLDropDbW() APIs with UNICODE CLI applications for adding and dropping databases.

DB2 Database servers must connect to the database instance by using the ATTACH configuration keyword.

FP3: Enhanced connection support

The new ATTACH configuration keyword allows SQLDriverConnect() to attach to a server instance instead of a database. CLI applications can now use this configuration keyword when connecting to a DB2 Linux, Unix, and Windows database server.

FP3: Code page conversion can be disabled during bind-in and bind-out operations

The new connection level attribute SQL_ATTR_OVERRIDE_CHARACTER_CODEPAGE enables CLI applications to specify a database code page even when the code page is not available at the client end. When this new attribute is set to non-zero value, CLI will skip code page conversion during bind-in or bind-out of character data. CLI will fetch/insert the raw data without performing conversion from/to the server.

FP3: Network connection statistics support

Using the new SQL_ATTR_NETWORK_STATISTICS connection attribute, CLI applications can collect the following network statistics for a database connection:

Database processing time
Total round-trip elapsed time
Number of bytes that are sent to the database server
Number of bytes that are received from the database server
Number of DRDA® round trips

FP3: Enhancement to DB2 for z/OS Version 10 feature support

Starting in Version 9.7 Fix Pack 3a, CLI applications support DB2 for z/OS Version 10, which includes support for the following features:

Ability for CLI applications to use statement cache for dynamic statement containing literal values.
The statementConcentrator property for connection and statement attributes, which controls whether statement cache is used for dynamic statement containing literal values, now applies to connections to DB2 for z/OS Version 10.
Ability for CLI applications to use new TIMESTAMP_WITH_TIMEZONE data type.
DB2 for z/OS Version 10 supports the new TIMESTAMP_WITH_TIMEZONE data type. The new TIMESTAMP_WITH_TIMEZONE data type is only available in new function mode.
Ability for CLI applications to use the SQL_ATTR_EXTENDED_INDICATORS statement attribute.
The SQL_ATTR_EXTENDED_INDICATORS statement attribute that eliminates the need to indicate the position in the SQL statement can now be used by CLI applications for connections to DB2 for z/OS Version 10.
The DB2Explain CLI configuration keyword is available for use with the DB2 for z/OS Version 10 server.
DB2Explain CLI configuration keyword support which is available for DB2 for Linux, UNIX, and Windows, data server is now extended to DB2 for z/OS Version 10 servers.
Support for a currently committed semantic through SQL_ATTR_CONCURRENT_ACCESS_RESOLUTION attribute or ConcurrentAccessResolution CLI configuration keyword.
Currently committed semantic support which is available for DB2 Linux, UNIX, and Windows data server is now extended to DB2 for z/OS Version 10 servers. The z/OS server, however, currently only supports query against uncommitted INSERT and uncommitted DELETE.
Support for binary XML format.
The DB2 for z/OS Version 10 New Function Mode supports the binary XML format. CLI now provides a pass through mechanism for Binary XML data format.

FP4: New CLI connection attribute, attribute value and changes

Version 9.7 Fix Pack 4 and later fix packs include the following enhancements to the CLI:

The SQL_ATTR_NETWORK_STATISTICS connection attribute has a new value, SQL_NETWORK_STATISTICS_ON_SKIP_NOSERVER. In addition to enabling network statistics collection for a connection, this option omits network flows that are known to have no server time reported, for example COMMIT and ROLLBACK statements.
The SQLGetInfo function has a new InfoType value, SQL_DRIVER_BLDLEVEL, which returns information about the build level for the current version of CLI.
You can use the CLI asynchronous execution and load processing features together by specifying the SQL_ATTR_ASYNC_ENABLE and SQL_ATTR_USE_LOAD_API attributes at the same time.

FP4: New enhancement to automatic client reroute and archive option for the db2diag command

Version 9.7 Fix Pack 4 and later fix packs include the following enhancements to the CLI:

As an aid to maintaining a current list of available servers for automatic client reroute, if the db2dsdriver.cfg file has no alternate servers defined in the <acr> section, at the first successful connection to the server, the client creates a local cache file, srvrlst.xml, and updates it with the server's list of available alternate servers. This file is refreshed whenever a new connection is made and the server's list differs from the contents of the client srvrlst.xml file.
When you modify the db2dsdriver.cfg file, your CLI application can call the SQLReloadConfig function to validate the entries for all alternate servers within the <acr> section. For each server, an attempt is made to open a socket by using the specified host name and port. If all servers in the alternate server list for an active database connection are unreachable, an error message is returned in the DiagInfoString argument of the SQLReloadConfig function.
The db2diag command with -archive option is available for IBM Data Server Driver Package and IBM Data Server for ODBC and CLI. This command option enables you to archive the diagnostic log file on an instance-less client.

FP4: New features available for DB2 on Windows operating system

Version 9.7 Fix Pack 4 and later fix packs include the following enhancements to the CLI:

The DB2 interactive CLI command (db2cli) has the new parameter, install. On Windows operating system, you can use the -setup and -cleanup options of this command parameter to register or unregister the IBM Data Server Driver for ODBC and CLI.
The validate option of the db2cli command has been enhanced so that invalid keywords found in the db2cli.ini and db2dsdriver.cfg files are displayed as well as valid keywords.
On Windows operating systems, when you are using the IBM Data Server Driver for ODBC and CLI, the db2diag.log file is located in %UNZIPPED PATH%\IBM\DB2\. (In previous release, the location was %UNZIPPED PATH%\IBM\DB2\CLIDRIVER\.)

FP5: Support for IBM i DB2 servers has been enhanced

Starting in Version 9.7 Fix Pack 5, CLI applications support the following features in IBM i DB2 servers:

The SQL_XML data type is supported with DB2 for i V7R1. See XML data handling in CLIapplications .
The SQL_ATTR_EXTENDED_INDICATORS connection attribute is available for connections to IBM i DB2 servers. SeeSQL_ATTR_EXTENDED_INDICATORS.
Support to facilitate the migration from iAccess drivers to CLI. See SchemaList CLI/ODBC configuration keyword or SchemaFilter IBM Data Server Driver configuration keyword.

FP5: CLI optimizations to improve performance, troubleshooting, and high availability

In Version 9.7 Fix Pack 5 and later fix packs, CLI applications support the following features to improve application performance, troubleshooting, and high availability:

Support for implicit COMMIT after a complete result set is read from a cursor. See SQL_ATTR_COMMITONEOF.
Support to convert array input chaining into a column-wise array insert for applications that are connected to DB2 for z/OS servers. See SQL_ATTR_COLUMNWISE_MRI.
Support to return the number of rows in a table that are affected by each parameter set in applications that use array input to achieve bulk inserts, deletes, or updates. See SQL_ATTR_PARC_BATCH.
Support to fetch or insert data without code page conversions. See SQL_ATTR_OVERRIDE_CODEPAGE.
Use of a password phrase to access DB2 for z/OS servers. A password phrase is a character string that consists of mixed-case letters, numbers, and special characters including blanks. See PWD CLI/ODBC configuration keyword or Password IBM Data Server Driver configuration keyword.
Support to collect statistics for server time reported on COMMIT and ROLLBACK. See SQL_ATTR_NETWORK_STATISTICS.
Support for prefixing warning messages with a diagnostic string consisting of the database connection information when you call the SQLReloadConfig () function. SeeSQLReloadConfig function.
Support for returning a warning message on seamless failover. SeeSQL_ATTR_REPORT_SEAMLESSFAILOVER_WARNING.
Support of alternate groups for DB2 for Linux, UNIX, and Windows servers and DB2 for z/OS servers. See Alternate groups for connections to DB2 Database for Linux, UNIX, and Windows from non-Java clients.

FP5: db2cli command changes

In Version 9.7 Fix Pack 5 and later fix packs, the following changes apply to thedb2cli command:

The validate parameter now shows the copy name and type in the command output. See Validating IBM Data Server Driver Package (Windows) installation.
Support for adding data sources from the local database directory by using the db2cli command with the registerdsn -add parameter. See db2cli - DB2 interactive CLI command.
Support for adding or modifying data source, database, and parameter entries in the db2dsdriver.cfg configuration file by using the db2cli command with the new writecfg parameter. See db2cli - DB2 interactive CLI command.

FP5: ODBC 3.8 support

In Version 9.7 Fix Pack 5 and later fix packs, the following changes or additions have been made to support ODBC 3.8:

The new SQL_OV_ODBC3_80 value for the SQL_ATTR_ODBC_VERSION environment attribute. See SQL_ATTR_ODBC_VERSION.
A new connection attribute for better management of connection pooling, ODBC 3.8. See SQL_ATTR_RESET_CONNECTION.
A new transaction state called suspended state for better application programming and transaction control. See SQLEndTran function

FP6: Prefetched cursor support for seamless automatic client reroute

In Version 9.7 Fix Pack 6 and later fix packs, if all the data, including the end of file (EOF) character, is returned in the first query block or in a subsequent fetch request, the CLI driver can perform seamless failover when you issue a COMMIT or ROLLBACK statement after the server becomes unreachable. For seamless failover to take place, the following conditions must be met:

You must enable both the enableAcr and enableSeamlessAcr parameters.
The cursor must have blocking enabled.
The cursor must be either read only or forward only.

See Operation of automatic client reroute for connections to DB2 Database for Linux, UNIX, and Windows from non-Java clients, Operation of automatic client reroute for connections from non-Java clients to DB2 for z/OS servers, and Operation of automatic client reroute for connections to Informix database server from non-Java clients.

FP6: New enhancements to the DB2 interactive CLI command (db2cli) (Windows)

Version 9.7 Fix Pack 6 and later fix packs include the following enhancements to the DB2 interactive CLI command (db2cli):

You can use the db2cli validate command to obtain a complete list of IBM data server client packages that are installed on a Windows operating system. When you issue the command on a Windows operating system, the db2cli command reports all duplicate and conflicting settings for the same property in the same DSN, database, or global section of the db2dsdriver.cfg file.
You can use the db2cli validate command to check duplicate or conflicting keyword entries in the same section of the db2dsdriver.cfg file.
On Windows operating systems, new options are added to the registerdsn parameter for the db2cli command:
- The db2cli registerdsn -remove command include the following new options:
  - -alldsn
  - -copyname copy_name
  - -allcopies
  - -force
  - -dsn
- The db2cli registerdsn -add command includes the new -dsn option.
- The db2cli registerdsn -list command includes the following new options:
  - -copyname copy_name
  - -allcopies
See db2cli - DB2 interactive CLI command .

FP6: Support for DB2 for i servers has been further enhanced

In Version 9.7 Fix Pack 6 and later fix packs, CLI applications support the following features in DB2 for i servers:

SQL_BINARY and SQL_VARBINARY data types
The following client information properties, in DB2 for i V6R1 and later:
- SQL_ATTR_INFO_ACCTSTR
- SQL_ATTR_INFO_APPLNAME
- SQL_ATTR_INFO_USERID
- SQL_ATTR_INFO_PROGRAMID
- SQL_ATTR_INFO_WRKSTNNAME
See Environment attributes (CLI) list and Connection attributes (CLI) list.

FP6: SQL_ATTR_NETWORK_STATISTICS attribute support has been further enhanced

In Version 9.7 Fix Pack 6 and later fix packs, you can obtain the server time for COMMIT or ROLLBACK SQL operations on DB2 for z/OS Version 10 and later. SeeConnection attributes (CLI) list.

FP6: New LDAP keywords for the db2dsdriver.cfg file

In Version 9.7 Fix Pack 6 and later fix packs, the following new keywords are added to the db2dsdriver.cfg for the LDAP support:

EnableLDAP
LDAPServerHost
LDAPServerport
ClientProvider
BaseDN
UserID
Password

See IBM Data Server Driver configuration keywords.

FP6: New SQL_C_CURSORHANDLE C data type

In Version 9.7 Fix Pack 6 and later fix packs, the new SQL_C_CURSORHANDLE C data type is available for use with the SQL_CURSORHANDLE SQL data type. See SQL symbolic and default data types for CLI applications.

FP6: QueryTimeout keyword support

In Version 9.7 Fix Pack 6 and later fix packs, the CLI driver now supports use of the QueryTimeout keyword in the db2dsdriver.cfg file. See QueryTimeout IBM Data Server Driver configuration keyword.