If you plan to ftp a configuration profile from one machine to another, ensure that you ftp the file in binary.
The CLI/ODBC Settings Notebook is available on OS/2, Windows 95, and Windows NT. It is accessible from the Client Configuration Assistant, or from the ODBC Driver Manager (if it is installed on the system).
The notebook can only be used to modify the configuration settings for databases configured as ODBC data sources. If you want to modify the settings of a CLI data source using the notebook, you must register the database as an ODBC data source. The settings (stored in the db2cli.ini file) affect both CLI and ODBC applications accessing the database.
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.
Turn 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 ?). Turn 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
Tabs in the Client Configuration Assistant SmartGuide on OS/2 may be truncated to one character if certain Matrox video drivers are used. These video drivers (for example, the 2.23.082 Matrox Millenium drivers) are not supported, because they have been shown not to work properly with the DB2 GUI tools.
The custom export (the Export button in the CCA with the Customize option) and the new "export all" function (db2cfexp on the command line, or the Export button in the CCA with the All option), which allow you to export client connectivity information, do not export administrator node information. When this profile is then imported on a different client using either the new "import all" function (db2cfimp on the command line), or through the CCA (on OS/2 or Windows operating systems only) through the "import all" or import customize functions, the database and instance information are cataloged without the system information. This will not affect application connectivity to the database, but it will cause the DB2 Control Center running on the client to interpret DB2 Version 5.x or DB2 Version 6.1 databases as DB2 Version 2 databases instead. DB2 Version 5.2 or DB2 Version 6.1 Control Centers are not able to properly administer DB2 Version 2 instances or databases, because there is no administration server for that database level. Similarly, they cannot properly administer DB2 Version 5.x or DB2 Version 6.1 instances or databases, unless the administration server node information is cataloged.
Following are suggested workarounds:
A server profile can be generated using "export server profile" from the Control Center, or by issuing "db2genp" from the command line. The profile (generate.spf) can be found in the /sqllib/tmp directory on UNIX based systems, or in the \sqllib\<instance name>\tmp directory on OS/2 or Windows operating systems. Non-interactive import can be invoked by selecting "Import ALL" from the CCA (on Win32 or OS/2 clients), or by issuing "db2cfimp fname" from the command line.
Multiple administration nodes can be configured by importing multiple server profiles. Non-interactive import will import and set up node and database directories to connect to all the databases in that server profile. If that is not a desired outcome, the administrator can edit the profile(s) and remove all groups from the profile (except for groups headed by [File_Description] or [adminst>.. ]) prior to the non-interactive import.