The following sections are included:
Each DB2 CLI function that existed in version 2 but was deprecated in version 5 is still listed in the function reference, with an indication of its state at the beginning of the function description.
Although DB2 CLI version 5 continues to support all of the deprecated functions, we recommend that you begin using the new functions in your DB2 CLI programs so that they conform to the latest standards.
In some cases the features and arguments of the deprecated function and the replacement function are very similar (SQLColAttributes() and SQLColAttribute()). In these cases the description of the deprecated function has been removed.
In other cases the description of the deprecated function has been left to assist in understanding the conversion to the new function.
The following table lists each of the deprecated function, the type of
description given, and the replacement function(s).
Table 183. Deprecated Functions and their Replacements
| Deprecated Function | Description | Replacement Function |
|---|---|---|
| SQLAllocConnect() | Removed | SQLAllocHandle() |
| SQLAllocEnv() | Removed | SQLAllocHandle() |
| SQLAllocStmt() | Removed | SQLAllocHandle() |
| SQLColAttributes() | Removed | SQLColAttribute() |
| SQLError() | Unchanged | SQLGetDiagField() and SQLGetDiagRec() |
| SQLExtendedFetch() | Unchanged | SQLFetchScroll() |
| SQLFreeConnect() | Unchanged | SQLFreeHandle() |
| SQLFreeEnv() | Unchanged | SQLFreeHandle() |
| SQLGetConnectOption() | Removed | SQLGetConnectAttr() |
| SQLGetStmtOption() | Removed | SQLGetStmtAttr() |
| SQLParamOptions() | Unchanged | SQLSetStmtAttr() |
| SQLSetConnectOption() | Removed | SQLSetConnectAttr() |
| SQLSetParam() | Removed | SQLBindParameter() |
| SQLSetStmtOption() | Removed | SQLSetStmtAttr() |
| SQLTransact() | Unchanged | SQLEndTran() |
DB2 Universal Database version 5 introduced two system catalog views used to store information about all stored procedures on the server. Before version 5, DB2 CLI used the pseudo catalog table for stored procedure registration. By default, DB2 CLI will use the new system catalog views. If the application expects to use the pseudo catalog table then the CLI/ODBC configuration keyword PATCH1 should be set to 262144.
In order for SQLProcedureColumns() and SQLProcedures() to return information about stored procedures (from the pseudo catalog table) when the application is connected to a version 2 DB2 common server, the pseudo catalog table for stored procedure registration must have already been created and populated. This is the table named PROCEDURES in the DB2CLI schema. For further information on this pseudo catalog table, refer to and Appendix H, Pseudo Catalog Table for Stored Procedure Registration. It is imperative that the exact rules in Appendix H, Pseudo Catalog Table for Stored Procedure Registration are followed when populating this table, or the SQLProcedureColumns() and SQLProcedures() calls will result in an error (SQLSTATE 42601).
The SQLSetConnectAttr() function can only be used to set a subset of statement attributes. This is being phased out, and new DB2 CLI should not rely on this feature. It was offered using SQLSetConnectOption() in versions of DB2 CLI prior to version 5, but it was then removed and the function was deprecated.
To support DB2 CLI applications written before version 5, SQLSetConnectAttr() currently behaves as SQLSetConnectOption() did using the option values defined in version 2 (the '_ATTR_' has been added for version 5):
Previous versions of DB2 did not have the global statement cache. They did, however, provide dynamic statement caching at the server. In DB2 CLI terms this means that for a given statement handle, once a statement has been prepared, it does not need to be prepared again (even after commits or rollbacks), so long as the statement handle is not freed. Applications that repeatedly execute the same SQL statement across multiple transactions, can save a significant amount of processing time and network traffic by:
This is not needed in DB2 Universal Database Version 5 and later because of the global dynamic statement cache. Preparing the first statement would create the package in the global cache. Each subsequent prepare request would find the first access plan in the cache and use it right away.
If an application is connected to a server that does not support dynamic statement caching across transaction boundaries, DB2 CLI will prepare each statement internally as needed. This means the method described above can be used for all applications, regardless of the RDBMS.
The following table lists the changes to the columns returned by SQLColumns() from version 2.1.1 to version 5.
For the current values see Columns Returned by SQLColumns().
To have DB2 CLI behave as it did for version 2 (same column names and
order), set the DB2 CLI/ODBC configuration keyword PATCH2. See How to Set CLI/ODBC Configuration Keywords for more information on how to set
this keyword.
Table 184. Changes to Columns Returned By SQLColumns
| Version 2 Column | Version 5 Column | Change |
|---|---|---|
| 14 DATETIME_CODE | Removed for version 5 | This information is no longer returned. It was always NULL for version 2. |
| No version 2 equivalent | 14 SQL_DATA_TYPE | This information was not returned in version 2. |
| No version 2 equivalent | 15 SQL_DATETIME_SUB | This information was not returned in version 2. |
| 15 CHAR_OCTET_LENGTH | 16 CHAR_OCTET_LENGTH | The column number has changed, but the name and description remain the same. |
| 16 ORDINAL_POSITION | 17 ORDINAL_POSITION | The column number has changed, but the name and description remain the same. |
| 17 IS_NULLABLE | 18 IS_NULLABLE | The column number has changed, but the name and description remain the same. |
The following table lists the changes to the columns returned by SQLProcedureColumns() from version 2.1.1 to version 5.
For the current values see Columns Returned By SQLProcedureColumns.
To have DB2 CLI behave at it did for version 2 (same column names and
order), set the DB2 CLI/ODBC configuration keyword PATCH2. See How to Set CLI/ODBC Configuration Keywords for more information on how to set
this keyword.
Table 185. Changes to Columns Returned By SQLProcedureColumns
| Version 2 Column | Version 5 Column | Change |
|---|---|---|
| 14 ORDINAL_POSITION | 18 ORDINAL_POSITION | The column number has changed, but the name and description remain the same. |
Besides adding a number of new InfoTypes
to SQLGetInfo(), the following version 2 values have been renamed
for version 5:
Table 186. Changes to values used by SQLGetInfo()
| Version 2 InfoType | Version 5 InfoType |
|---|---|
| SQL_ACTIVE_CONNECTIONS | SQL_MAX_DRIVER_CONNECTIONS |
| SQL_ACTIVE_STATEMENTS | SQL_MAX_CONCURRENT_ACTIVITIES |
| SQL_MAX_OWNER_NAME_LEN | SQL_MAX_SCHEMA_NAME_LEN |
| SQL_MAX_QUALIFIER_NAME_LEN | SQL_MAX_CATALOG_NAME_LEN |
| SQL_ODBC_SQL_OPT_IEF | SQL_INTEGRITY |
| SQL_SCHEMA_TERM | SQL_OWNER_TERM |
| SQL_OWNER_USAGE | SQL_SCHEMA_USAGE |
| SQL_QUALIFIER_LOCATION | SQL_CATALOG_LOCATION |
| SQL_QUALIFIER_NAME_SEPARATOR | SQL_CATALOG_NAME_SEPARATOR |
| SQL_QUALIFIER_TERM | SQL_CATALOG_TERM |
| SQL_QUALIFIER_USAGE | SQL_CATALOG_USAGE |
In DB2 CLI Version 5, 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 the application generates queries where the answer set is very small, and the overhead of separate requests and replies is not spread across multiple blocks of query data. 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.
DB2 CLI Version 2 applications that expect the PREPARE to be executed as soon as it requested may not operate as expected. (An application may, for instance, rely on the row and cost estimates that are normally returned in the SQLERRD(3) and SQLERRD(4) of the SQLCA of a prepare statement; with deferred prepare, these values may become zeros.) To ensure these programs work as they did with Version 2, the DB2 CLI/ODBC configuration keyword DEFERREDPREPARE can be set to disable deferred prepare. See DEFERREDPREPARE for more information.
The statement attribute SQL_ATTR_DEFERRED_PREPARE can also be used to force DB2 CLI to prepare the statement as soon as it is issued. See the attribute in SQLSetStmtAttr - Set Options Related to a Statement for more information.
In version 2, a DB2 CLI application could use the function SQLSetColAttributes() to reduce network traffic by describing the result descriptor information for every column in the result set. With deferred prepare this is no longer of any benefit, and the SQLSetColAttributes() function has been deprecated. If your application does call this function it will ignore all arguments, and will always return SQL_SUCCESS.