IBM Books

What's New

Configuration Keywords

The following changes and additions have been made to the CLI/ODBC configuration keywords.

For specific details on setting CLI/ODBC configuration keywords, see the Quick Beginnings manual for your platform.

CLISCHEMA

Keyword Description:
The DB2 ODBC catalog view to use.

db2cli.ini Keyword Syntax:
CLISCHEMA=ODBC catalog view

Default Setting:
None - No ODBC catalog view is used

DB2 CLI/ODBC Settings Tab:
This keyword cannot be set using the CLI/ODBC Settings notebook. To set these keywords use either the UPDATE CLI CONFIGURATION command in the Command Line Processor, or modify the db2cli.ini file directly.

Equivalent Connection Attribute:
SQL_ATTR_CLISCHEMA

Usage Notes:
 

The DB2 ODBC catalog is designed to improve the performance of schema calls for lists of tables in ODBC applications that connect to host DBMSs through DB2 Connect.

The DB2 ODBC catalog, created and maintained on the host DBMS, contains rows representing objects defined in the real DB2 catalog, but these rows include only the columns necessary to support ODBC operations. The tables in the DB2 ODBC catalog are pre-joined and specifically indexed to support fast catalog access for ODBC applications.

System administrators can create multiple DB2 ODBC catalog views, each containing only the rows that are needed by a particular user group. Each end user can then select the DB2 ODBC catalog view they wish to use (by setting this keyword).

Use of the CLISCHEMA setting is completely transparent to the ODBC application; you can use this option with any ODBC application.

While this keyword has some similar effects as the SYSSCHEMA keyword, CLISCHEMA should be used instead (where applicable).

CLISCHEMA improves data access efficiency: The user-defined tables used with SYSSCHEMA were mirror images of the DB2 catalog tables, and the ODBC driver still had to join rows from multiple tables to produce the information required by the ODBC user. Using CLISCHEMA also results in less contention on the catalog tables.

CURRENTREFRESHAGE

Keyword Description:
Set the value of the CURRENT REFRESH AGE special register.

db2cli.ini Keyword Syntax:
CURRENTREFRESHAGE=ANY | a numeric constant

Default Setting:
0 - summary tables defined with REFRESH DEFERRED will not be used to optimize the processing of a query

DB2 CLI/ODBC Settings Tab:
This keyword cannot be set using the CLI/ODBC Settings notebook. To set these keywords use either the UPDATE CLI CONFIGURATION command in the Command Line Processor, or modify the db2cli.ini file directly.

Usage Notes:
 

For information on Summary Tables and the SET CURRENT REFRESH AGE statement, see the SQL Reference.

This keyword can be set to one of the following values:

0
Indicates that summary tables defined with REFRESH DEFERRED will not be used to optimize the processing of a query (default).

99999999999999
Indicates that any summary tables defined with REFRESH DEFERRED or REFRESH IMMEDIATE may be used to optimize the processing of a query. This value represents 9999 years, 99 months, 99 days, 99 hours, 99 minutes, and 99 seconds.

ANY
This is a shorthand for 99999999999999.

CURRENTSCHEMA

Keyword Description:
Issue a SET CURRENT SCHEMA statement upon a successful connect.

db2cli.ini Keyword Syntax:
CURRENTSCHEMA=schema

Default Setting:
No statement is issued.

DB2 CLI/ODBC Settings Tab:
This keyword cannot be set using the CLI/ODBC Settings notebook. To set these keywords use either the UPDATE CLI CONFIGURATION command in the Command Line Processor, or modify the db2cli.ini file directly.

Usage Notes:
 

Upon a successful connect, if this keyword is set then a SET CURRENT SCHEMA statement is sent to the server. This allows the application to name SQL objects without having to qualify them with a schema name.

See the SET SCHEMA statement in the SQL Reference for more information.

IGNOREWARNLIST

Keyword Description:
Ignore specified sqlstates.

db2cli.ini Keyword Syntax:
IGNOREWARNLIST="'sqlstate1', 'sqlstate2', ..."

Default Setting:
Warnings are returned as normal

DB2 CLI/ODBC Settings Tab:
This keyword cannot be set using the CLI/ODBC Settings notebook. To set these keywords use either the UPDATE CLI CONFIGURATION command in the Command Line Processor, or modify the db2cli.ini file directly.

Usage Notes:
 

On rare occasions an application may not correctly handle some warning messages, but does not want to ignore all warning messages. This keyword can be used to indicate which warnings are not to be passed on to the application. The IGNOREWARNINGS keyword should be used if all database manager warnings are to be ignored.

If an sqlstate is included in both IGNOREWARNLIST and WARNINGLIST, it will be ignored altogether.

Each sqlstate must be in uppercase, delimited with single quotes and separated by commas. The entire string must also be enclosed in double quotes. For example: 

IGNOREWARNLIST="'01000', '01004','01504'"

OPTIMIZESQLCOLUMNS

Keyword Description:
Optimize SQLColumns() call with explicit Schema and Table Name.

db2cli.ini Keyword Syntax:
OPTIMIZESQLCOLUMNS=1 | 0

Default Setting:
1 - Optimization on

DB2 CLI/ODBC Settings Tab:
This keyword cannot be set using the CLI/ODBC Settings notebook. To set these keywords use either the UPDATE CLI CONFIGURATION command in the Command Line Processor, or modify the db2cli.ini file directly.

Equivalent Connection Attribute:
SQL_ATTR_OPTIMIZESQLCOLUMNS

Usage Notes:
 

If OPTIMIZESQLCOLUMNS is on (set to 1), then all calls to SQLColumns() will be optimized if an explicit (no wildcard specified) Schema Name, explicit Table Name, and % (ALL columns) for Column Name are specified. The DB2 CLI/ODBC Driver will optimize this call so that the system tables will not be scanned.

If the call is optimized then the COLUMN_DEF information (which contains the default string for the columns) is not returned. If the application needs the COLUMN_DEF information then OPTIMIZESQLCOLUMNS should be set to 0.

PATCH1 and PATCH2 Values

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. This is 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.

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 a double byte environment.

131072
This forces the driver to return a time stamp column as a CHAR(26) column instead. Needed by Microsoft applications when the time stamp column is part of a unique index.

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 Version 3.x system. This results in better performance.

1048576
This forces the driver to treat a zero length string through SQLPutData() as SQL_NULL_DATA.

2097152
This forces the driver to report that SQLParamOptions() is not supported.

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
This flag prevents an autocommit from occurring when the cursor is closed at the server when the last block of data is returned. The commit will occur when the cursor is closed by the application.

10
This setting should only be used in an EUC (Extended Unix Code) environment. It ensures that the CLI driver provides data for character variables (CHAR, VARCHAR, etc...) in the proper format for the JDBC driver. The data in these character types will not be usable in JDBC without this setting.

11
Pretend to support SQL_CATALOG_LOCATION, SQL_CATALOG_NAME_SEPARATOR, and SQL_MAX_CATALOG_NAME_LEN. Workaround for MS InterDev.

12
Remove extraneous double quotes (") from calls to schema functions. Workaround for MS InterDev.

13
Do not append keywords from the db2cli.ini file to the output string of SQLDriverConnect().

14
Do not return an error and ignore the schema argument for SQLProcedures() and SQLProcedureColumns().

15
Ignore locale specific decimal separator and force a period for conversions between Character and:

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]