The CLI/ODBC/JDBC driver can be configured through the Client Configuration Assistant or the ODBC Driver Manager (if it is installed on the system), or by manually editing the db2cli.ini file. For more details, see either the Installation and Configuration Supplement, or the CLI Guide and Reference.
The DB2 CLI/ODBC driver default behavior can be modified by specifying values for both the PATCH1 and PATCH2 keyword through either the db2cli.ini file or through the SQLDriverConnect() or SQLBrowseConnect() CLI API.
The PATCH1 keyword is specified by adding together all keywords that the user wants to set. For example, if patch 1, 2, and 8 were specified, then PATCH1 would have a value of 11. Following is a description of each keyword value and its effect on the driver:
1 - This makes the driver search for "count(exp)" and replace it with
"count(distinct exp)". This is needed because some versions of DB2
support the "count(exp)" syntax, and that syntax is generated by some
ODBC applications. Needed by Microsoft applications when the server
does not support the "count(exp)" syntax.
2 - Some ODBC applications are trapped when SQL_NULL_DATA is returned in
the SQLGetTypeInfo() function for either the LITERAL_PREFIX or LITERAL_SUFFIX
column. This forces the driver to return an empty string instead. Needed
by Impromptu 2.0.
4 - This forces the driver to treat the input time stamp data as date data
if the time and the fraction part of the time stamp are zero. Needed by
Microsoft Access.
8 - This forces the driver to treat the input time stamp data as time data
if the date part of the time stamp is 1899-12-30. Needed by Microsoft Access.
16 - Not used.
32 - This forces the driver to not return information about SQL_LONGVARCHAR,
SQL_LONGVARBINARY, and SQL_LONGVARGRAPHIC columns. To the application
it appears as though long fields are not supported. Needed by Lotus 123.
64 - This forces the driver to NULL terminate graphic output strings. Needed
by Microsoft Access in a double byte environment.
128 - This forces the driver to let the query "SELECT Config, nValue FROM MSysConf"
go to the server. Currently the driver returns an error with associated
SQLSTATE value of S0002 (table not found). Needed if the user has created
this configuration table in the database and wants the application
to access it.
256 - This forces the driver to return the primary key columns first in
the SQLStatistics() call. Currently, the driver returns the indexes sorted
by index name, which is standard ODBC behavior.
512 - This forces the driver to return FALSE in SQLGetFunctions() for both
SQL_API_SQLTABLEPRIVILEGES and SQL_API_SQLCOLUMNPRIVILEGES.
1024 - This forces the driver to return SQL_SUCCESS instead of SQL_NO_DATA_FOUND
in SQLExecute() or SQLExecDirect() if the executed UPDATE or DELETE
statement affects no rows. Needed by Visual Basic applications.
2048 - Not used.
4096 - This forces the driver to not issue a COMMIT after closing a cursor
when in autocommit mode.
8192 - This forces the driver to return an extra result set after invoking
a stored procedure. This result set is a one row result set consisting
of the output values of the stored procedure. Can be accessed by
Powerbuild applications.
32768 - This forces the driver to make Microsoft Query applications work
with DB2 MVS synonyms.
65536 - This forces the driver to manually insert a "G" in front of character
literals which are in fact graphic literals. This patch should always
be supplied when working in an double byte environment.
131072 - This forces the driver to describe a time stamp column as a CHAR(26)
column instead, when it is part of an unique index. Needed by
Microsoft applications.
262144 - This forces the driver to use the pseudo-catalog table
db2cli.procedures instead of the SYSCAT.PROCEDURES and
SYSCAT.PROCPARMS tables.
524288 - This forces the driver to use SYSTEM_TABLE_SCHEMA instead of TABLE_SCHEMA
when doing a system table query to a DB2/400 V3.x system. This
results in better performance.
1048576 - This forces the driver to treat a zero length string through
SQLPutData() as SQL_NULL_DATA.
The PATCH2 keyword differs from the PATCH1 keyword. In this case, multiple patches are specified using comma separators. For example, if patch 1, 4, and 5 were specified, then PATCH2 would have a value of "1,4,5". Following is a description of each keyword value and its effect on the driver:
1 - This forces the driver to convert the name of the stored procedure
in a CALL statement to uppercase.
2 - Not used.
3 - This forces the driver to convert all arguments to schema calls to uppercase.
4 - This forces the driver to return the Version 2.1.2 like result set for
schema calls (that is, SQLColumns(), SQLProcedureColumns(), and so on),
instead of the Version 5 like result set.
5 - This forces the driver to not optimize the processing of input VARCHAR
columns, where the pointer to the data and the pointer to the length
are consecutive in memory.
6 - This forces the driver to return a message that scrollable cursors are not
supported. This is needed by Visual Basic programs if the DB2 client
is Version 5 and the server is DB2 UDB Version 5.
7 - This forces the driver to map all GRAPHIC column data types to the CHAR
column data type. This is needed in a double byte environment.
8 - This forces the driver to ignore catalog search arguments in schema calls.
9 - Do not commit on Early Close of a cursor
10 - Not Used
11 - Report that catalog name is supported, (VB stored procedures)
12 - Remove double quotes from schema call arguments, (Visual Interdev)
13 - Do not append keywords from db2cli.ini to output connection string
14 - Ignore schema name on SQLProcedures() and SQLProcedureColumns()
15 - Always use period for decimal separator in character output
16 - Force return of describe information for each open
17 - Do not return column names on describe
18 - Attempt to replace literals with parameter markers
19 - Currently, DB2 MVS V4.1 does not support the ODBC syntax where parenthesis
are allowed in the ON clause in an Outer join clause.
Turning on this PATCH2 will cause IBM DB2 ODBC driver to strip the parenthesis
when the outer join clause is in an ODBC escape sequence. This PATCH2
should only be used when going against DB2 MVS 4.1.
20 - Currently, DB2 on MVS does not support BETWEEN predicate with parameter
markers as both operands (expression ? BETWEEN ?). Turning on this patch
will cause the IBM ODBC Driver to rewrite the predicate to
(expression >= ? and expression <= ?).
21 - Set all OUTPUT only parameters for stored procedures to SQL_NULL_DATA
22 - This PATCH2 causes the IBM ODBC driver to report OUTER join as not supported.
This is for application that generates SELECT DISTINCT col1 or ORDER BY col1
when using outer join statement where col1 has length greater than 254
characters and causes DB2 UDB to return an error (since DB2 UDB does
not support greater-than-254 byte column in this usage
23 - Do not optimize input for parameters bound with cbColDef=0
24 - Access workaround for mapping Time values as Characters
25 - Access workaround for decimal columns - removes trailing zeros in char representation
26 - Do not return sqlcode 464 to application - indicates result sets are returned
27 - Force SQLTables to use TABLETYPE keyword value, even if the application
specifies a valid value
28 - Describe real columns as double columns
29 - ADO workaround for decimal columns - removes leading zeroes
for values x, where 1 > x > -1 (Only needed for some MDAC versions)
30 - Disable the Stored Procedure caching optimization
31 - Report statistics for aliases on SQLStatistics call
32 - Override the sqlcode -727 reason code 4 processing
33 - Return the ISO version of the time stamp when converted to char
(as opposed to the ODBC version)
34 - Report CHAR FOR BIT DATA columns as CHAR
35 - Report an invalid TABLENAME when SQL_DESC_BASE_TABLE_NAME
is requested - ADO readonly optimization
36 - Reserved
37 - Reserved