IBM Support

CLI/ODBC PATCH1 and PATCH2 configuration keywords values

Question & Answer


Question

DB2 UDB for Linux, UNIX and Windows V8.2 CLI/ODBC PATCH1 and PATCH2 configuration keywords values

Answer

V8.2 CLI/ODBC Configuration Keyword PATCH1: Values

The default behavior of the DB2 CLI/ODBC driver can be modified by specifying values for the PATCH1 CLI/ODBC configuration keyword in the db2cli.ini initialization file or through the SQLDriverConnect() or SQLBrowseConnect() CLI functions.

To determine the value to set PATCH1 to, add together all values you want to be set. For example, to set values 1, 4, and 8 (described below), set PATCH1 to 13. The table below describes each keyword value and its effect on the DB2 CLI/ODBC driver:

Note:


Values marked as "Deprecated" are no longer supported for DB2 Version 8 and should not be used. Values marked as "Service use only" should only be used under the direction of DB2 Service and Support.

4 Changes input timestamp data to date data if the time and fraction part of the timestamp are zero. For example, {ts 'YYYY-MM-DD 00:00:00'} is changed to {d 'YYYY-MM-DD'}. This value is typically needed for older versions of Microsoft Access.
8 Changes input timestamp data to time data if the date part of the timestamp is either 1899-12-30 or 1900-01-01. For example, {ts '1899-12-30 HH:MM:SS'} is changed to {t 'HH:MM:SS'}. This value is typically needed for older versions of Microsoft Access.
64 Null-terminates output GRAPHIC strings. This value is typically needed by Microsoft Access in a double-byte (DBCS) environment.
128 Disables the default performance optimization behavior for the MSysConf table associated with some Microsoft applications.

Microsoft applications, such as Microsoft Access, use a configuration table called MSysConf. Once these applications successfully connect to a database, they will typically issue the following query: "SELECT Config, nValue FROM MSysConf". Because the MSysConf table does not exist in a DB2 database by default, this query fails with the error "SQL0204N "MSysConf" is an undefined name.". Microsoft applications can handle this error and continue processing, however, issuing the query across the network to the DB2 server incurs overhead.

To enhance performance, DB2 CLI assumes that this query will always fail, so when it detects that an application is trying to execute this query, it automatically returns an error with an SQLSTATE of S0002 (Table not found). The query, therefore, is never sent to the server. If, however, the user has created the MSysConf configuration table in the database and wants the application to access it, this PATCH1 value can be set to disable the performance optimization and allow the query to be executed.

256 Service use only
512 Service use only
1024 Returns SQL_SUCCESS_WITH_INFO instead of SQL_NO_DATA_FOUND from the SQLExecute() and SQLExecDirect() functions if the executed UPDATE or DELETE statement affected no rows. This value may be needed by some Microsoft Visual Basic applications.
4096 Prevents a COMMIT from being issued after closing a cursor in autocommit mode.
8192 Returns an extra result set after invoking a stored procedure. This extra result set has one row and consists of the output values of the stored procedure. This PATCH1 value may be needed by some Powerbuilder applications that require an extra result set.
32768 Forces the driver to make Microsoft Query applications work with DB2 MVS synonyms.
65536 Deprecated
131072 Deprecated
262144 Deprecated
524288 Deprecated
1048576 Service use only
2097152 Service use only

V8.2 CLI/ODBC Configuration Keyword PATCH2: Values

The default behavior of the DB2 CLI/ODBC driver can be modified by specifying values for the PATCH2 CLI/ODBC configuration keyword in the db2cli.ini initialization file or through the SQLDriverConnect() or SQLBrowseConnect() CLI functions.

To specify the value to set PATCH2 to, list the desired values separated by commas. For example, to apply the values 6, 7, and 11 (described below), set PATCH2 to "6,7,11". The table below describes each keyword value and its effect on the DB2 CLI/ODBC driver:

Note:


Values marked as "Deprecated" are no longer supported for DB2 Version 8 and should not be used. Values marked as "Service use only" should only be used under the direction of DB2 Service and Support.

1 Deprecated
3 Service use only
4 Deprecated
5 Deprecated
6 Forces the driver to return a message indicating that scrollable cursors are not supported. This setting is needed by some applications (such as Visual Basic) that make use of LOBs or that do not need or want scrollable cursors to be used, even though they have been explicitly requested by the application.
7 Maps all GRAPHIC column data types to the CHAR column data type. The precision of a GRAPHIC column will also be doubled; for example, GRAPHIC(20) will be reported as CHAR(40).
8 Ignores catalog search arguments in schema calls.
11 SQLGetInfo() reports that catalog names are supported for Visual Basic stored procedures.
12 Deprecated
13 Prevents keywords in the db2cli.ini initialization file from being appended to the output connection string.
14 Deprecated
15 Causes a period separator to be used instead of the default locale's decimal separator in character output.
16 Deprecated
17 Deprecated
18 Attempts to replace literals with parameter markers for inefficient applications that use literals repeatedly. It is only applicable to INSERT SQL statements with the VALUES clause using only literals. Coding your application properly to use parameter markers is the best solution.
19 Removes parentheses from the ON clause of an outer join, where the outer join is an ODBC escape sequence and the server is DB2 for MVS Version 5. DB2 for MVS Version 5 does not currently support the ODBC syntax where parentheses are permitted in the ON clause of an outer join clause. Setting this PATCH2 value allows the outer join escape sequence to be used against DB2 for MVS Version 5. This value should only be set when the server is DB2 for MVS Version 5.
20 Forces the driver to rewrite the BETWEEN predicate when the server is DB2 for MVS. DB2 for MVS does not currently support the BETWEEN predicate with parameter markers as both operands. Setting this PATCH2 value causes (expression ? BETWEEN ?) to be rewritten as (expression >= ? and expression <= ?).
21 Deprecated
22 Causes SQLGetInfo() to report SQL_OUTER_JOINS=NO and SQL_OJ_CAPABILITIES=0. This prevents the application from using outer joins where they are not supported, thus ensuring that the outer join queries do not fail.
23 Deprecated
24 Reports TIME data as SQL_CHAR data. This patch value is used as a workaround for Microsoft Access applications.
25 Removes trailing zeros in the CHAR representation of DECIMAL columns; used as a workaround for Microsoft Access applications.
28 Deprecated
29 Removes leading zeroes in the string representation of DECIMAL values x, where 1 > x > -1; used as a workaround for ADO applications with some MDAC versions.
30 Disables stored procedure caching optimization.
31 Deprecated
32 Deprecated
33 Returns the ISO version of timestamp data when converted to CHAR, rather than the ODBC version.
34 Deprecated
38Turns statement caching off
42 Prevents the FOR UPDATE clause from being used with keyset cursors. By default, most applications expect keyset cursors to be updatable, however, if this is not required, then this PATCH2 value makes the cursor read-only (but still scrollable and sensitive to changes made by others).
50 Frees LOB locators when SQLFetch() is executed, rather than when a COMMIT is issued. This PATCH2 value frees the locators that are used internally when applications fetch LOB data without binding the LOB columns with SQLBindCol() (or equivalent descriptor APIs). Locators that are explicitly returned to the application must still be freed by the application. This PATCH2 value can be used to avoid scenarios where an application receives SQLCODE = -429 (no more locators).
56Allows client support for Early Close Cursors for those servers that do not support it as in the case of DB2 UDB for OS/390 version 7 or earlier.
57Allows calling a stored procedure that returns a NULL output parameter value without providing an output indicator pointer. This is normally applicable to older versions of Borland Delphi products.
58DateTime values inserted into the database that cause truncation errors can be downgraded to a truncation warning using this PATCH2 value.
61When data is given to the client from an SQL_CHAR there may be right padded spaces. This patch value strips off right padded single byte spaces, but not double byte spaces. This behavior partially mimics the Neon Shadow Driver behavior


For DB2 V9
Patch1 Values: http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.apdv.cli.doc/doc/r0008804.htm

Patch2 Values:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.apdv.cli.doc/doc/r0008805.htm

For DB2 V9.5
Patch1 Values:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0008804.html

Patch2 Values:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0008805.html

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Programming Interface - CLI","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.5;9.1;8","Edition":"Edition Independent","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21179697