Application development: Call Level Interface (CLI)

Trusted_Connection CLI/ODBC configuration keyword

|

Keyword description:

|

Allow a connection to be made with the current authenticated user. |

|

Syntax:

|

Trusted_Connection=Yes | |

Note:

|

This keyword has no effect if |set in the db2cli.ini file. It should instead |be provided in the connection string to SQLDriverConnect().

|

|

Default setting:

|

DB2 CLI uses the user ID and password information provided in the connection |string to SQLDriverConnect(), not the current authenticated user. |

|

Usage notes:

|

|

DB2 CLI applications that connect to a database typically connect using |the function SQLDriverConnect(). One of the input arguments for this function |is the DriverCompletion value, which determines when a window will |be opened. The following are the values of DriverCompletion :

|

|
• SQL_DRIVER_PROMPT: A dialog is always initiated.
|
• SQL_DRIVER_COMPLETE: A dialog is only initiated if there is insufficient |information in the connection string.
|
• SQL_DRIVER_COMPLETE_REQUIRED: A dialog is only initiated if there is |insufficient information in the connection string. Only mandatory information |is requested. The user is prompted for required information only.
|
• SQL_DRIVER_NOPROMPT: The user is not prompted for any information. A |connection is attempted with the information contained in the connection string. |If there is not enough information, SQL_ERROR is returned.

| |

Note:

|

More details on DriverCompletion can be found |in the documentation for SQLDriverConnect().

|

Some applications, |for example, those in a Kerberos environment, might require that a user be |able to connect to a DB2 UDB server without providing a user ID or password. |If the application uses the SQL_DRIVER_NO_PROMPT option on the SQLDriverConnect() call, |the connection is attempted without the user authentication. This keyword |is then not required.

|

In the case where a third party application is |involved and the prompt level used by the application is something other than |SQL_DRIVER_NO_PROMPT, DB2 CLI opens a window to request the missing information. |Setting Trusted_Connection to Yes, by providing it to the input connection |string for SQLDriverConnect() ("Trusted_Connection=Yes"), causes DB2 CLI to |ignore any user ID or password string (including blank strings) from the connection |string and ignore the prompt level of the connection function. DB2 CLI uses |the current authenticated user to attempt the connection to the database. |If the connection attempt fails, the user is prompted for the user ID and |password.

|

This keyword is used only in the connection string for SQLDriverConnect(); |setting it in the db2cli.ini file has no effect.

|

|

Diagnostic table update for SQLDescribeParam function (CLI)

The SQLDescribeParam() function returns the description of a parameter marker associated with a prepared SQL statement.

The diagnostics table has been updated with SQLSTATE HYC00.

Asynchronous execution of Call Level Interface

The DB2 Call Level Interface (DB2 CLI) can run a subset of functions asynchronously. The DB2 CLI driver returns control to the application after calling the function but before that function has finished executing. The function returns SQL_STILL_EXECUTING each time it is called until it is finished running, at which point it returns a different value (for example, SQL_SUCCESS).

Asynchronous execution is beneficial only on single-threaded operating systems. Applications that run on multithreaded operating systems should execute functions on separate threads. Asynchronous execution is possible for those functions that normally send a request to the server and then wait for a response. Rather than waiting, a function executing asynchronously returns control to the application. The application can then perform other tasks, or return control to the operating system, and use an interrupt to repeatedly poll the function until a return code other than SQL_STILL_EXECUTING is returned.

Support for asynchronous execution of CLI is included in DB2 Universal Database (UDB), starting with Version 8.2 FixPak 1 (equivalent to Version 8.1 FixPak 8). For documentation of this feature, refer to the DB2 UDB Version 7 Information Center at http://publib.boulder.ibm.com/infocenter/db2v7luw/index.jsp. All of the information in the Version 7 documentation applies to Version 8.2 FixPak 1 (equivalent to Version 8.1 FixPak 8) and later. The DB2 Version 8 Information Center does not contain any documentation of this feature.

SQL_ATTR_PING_DB connection attribute

SQLBindParameter function (CLI)

In the documentation for the SQLBindParameter function, the description in the Input parameter section is incorrect. The correct description is as follows:

Input parameter

A 0 ColumnSize means that DB2 CLI will use the maximum length for the SQL type provided as the size of the column or stored procedure parameter. DB2 CLI will perform any necessary conversions using this size.

SQLMoreResults function (CLI)

In the documentation for the SQLMoreResults function, the SQL_ATTR_ROW_ARRAY_SIZE statement attribute is referred to incorrectly. The correct statement attribute is SQL_ATTR_PARAMSET_SIZE. The Usage section should read as follows:

This function is used to return multiple results set in a sequential manner upon the execution of:

• A parameterized query with an array of input parameter values specified with the SQL_ATTR_PARAMSET_SIZE statement attribute and SQLBindParameter()

Additional environment attributes

In addition to being CLI connection attributes, the following attributes are also supported as CLI environment attributes:

• SQL_ATTR_INFO_ACCTSTR
• SQL_ATTR_INFO_APPLNAME
• SQL_ATTR_INFO_USERID
• SQL_ATTR_INFO_WRKSTNNAME

For information on these attributes, refer to the CLI connection attributes documentation in the DB2 Information Center or in the CLI Guide and Reference Volume 2 .

Dynamic scrollable cursors requirement

To perform updates and deletions on rows in a dynamic scrollable cursor's result set, the UPDATE or DELETE statement must include all the columns of at least one unique key in the base table. This can be the primary key or any other unique key.

RetCatalogAsCurrServer CLI/ODBC configuration keyword

Keyword description:

Catalog functions return the CURRENT SERVER value instead of the null value for the catalog columns.

db2cli.ini keyword syntax:

RetCatalogAsCurrServer= 0 | 1

Default setting:

If the target DBMS returns null for the catalog columns, the CURRENT SERVER value will not be substituted.

Usage notes:

If the catalog functions for the target DBMS return a null value for the catalog columns, setting RetCatalogAsCurrServer to 1 causes the DBMS to return the CURRENT SERVER value instead.

• 0 = Catalog functions return the null value for the catalog columns (default).
• 1 = Catalog functions return the CURRENT SERVER value, instead of the null value, for the catalog columns.

For example, assume the catalog function SQLTables() returns a result set where the values in the TABLE_CAT column are null values. Setting RetCatalogAsCurrServer to 1 causes the DBMS to return the CURRENT SERVER value in the TABLE_CAT column.

Note:

This keyword is supported by DB2 UDB for Linux, UNIX^(R) and Windows^(R) Version 8.2 FixPak 3 (equivalent to Version 8.1 FixPak 10) and later.

ReceiveTimeout CLI/ODBC configuration keyword

Keyword description:

Specify the time in seconds to wait for a reply from the server on an established connection before terminating the attempt and generating a communication timeout error.

db2cli.ini keyword syntax:

ReceiveTimeout = 0 | 1 | 2 | ... | 32767

Default setting:

The client waits indefinitely for a reply from the server on an established connection.

Equivalent connection attribute:

SQL_ATTR_RECEIVE_TIMEOUT

Usage notes:

The default value of 0 indicates that the client waits indefinitely for a reply. The receive timeout has no effect during connection establishment; it is only supported for TCP/IP and is ignored for any other protocol.

SQL_ATTR_RECEIVE_TIMEOUT connection attribute

SQL_ATTR_RECEIVE_TIMEOUT (DB2 CLI v8)

A 32-bit integer value that is the number of seconds a client waits for a reply from a server on an established connection before terminating the attempt and generating a communication timeout error. The default value of 0 indicates the client waits indefinitely for a reply. The receive timeout has no effect during connection establishment; it is only supported for TCP/IP, and is ignored for any other protocol. Supported values are integers from 0 to 32767.

Reopt CLI/ODBC configuration keyword

Keyword description:

Enable query optimization or reoptimization of SQL statements that have special registers or parameter markers.

db2cli.ini keyword syntax:

Reopt = 2 | 3 | 4

Default setting:

No query optimization occurs at query execution time. The default estimates chosen by the compiler are used for special registers or parameter markers.

Equivalent statement and connection attribute:

SQL_ATTR_REOPT

Usage notes:

Optimization occurs by using the values available at query execution time for the special registers or parameter markers instead of the default estimates that are chosen by the compiler. The valid values of the keyword are:

• 2 = SQL_REOPT_NONE. This is the default. No query optimization occurs at query execution time. The default estimates chosen by the compiler are used for the special registers or parameter markers. The default "NULLID " package set is used to execute dynamic SQL statements.
• 3 = SQL_REOPT_ONCE. Query optimization occurs once at query execution time, when the query is executed for the first time. The "NULLIDR1" package set, which is bound with the REOPT ONCE bind option, is used.
• 4 = SQL_REOPT_ALWAYS. Query optimization or reoptimization occurs at query execution time every time the query is executed. The "NULLIDRA" package set, which is bound with the REOPT ALWAYS bind option, is used.

The "NULLIDR1" and "NULLIDRA" are reserved package set names, and when used, REOPT ONCE and REOPT ALWAYS are implied respectively. These package sets have to be explicitly created with the following commands:

db2 bind db2clipk.bnd collection NULLIDR1
db2 bind db2clipk.bnd collection NULLIDRA

If both the Reopt and CurrentPackageSet keywords are specified, CurrentPackageSet takes precedence.

SQL_ATTR_REOPT statement and connection attribute

SQL_ATTR_REOPT (DB2 CLI v8)

A 32-bit integer value that enables query optimization for SQL statements that contain special registers or parameter markers. Optimization occurs by using the values available at query execution time for special registers or parameter markers, instead of the default estimates that are chosen by the compiler. The valid values of the attribute are:

• 2 = SQL_REOPT_NONE. This is the default. No query optimization occurs at query execution time. The default estimates chosen by the compiler are used for the special registers or parameter markers. The default "NULLID " package set is used to execute dynamic SQL statements.
• 3 = SQL_REOPT_ONCE. Query optimization occurs once at query execution time, when the query is executed for the first time. The "NULLIDR1" package set, which is bound with the REOPT ONCE bind option, is used.
• 4 = SQL_REOPT_ALWAYS. Query optimization or reoptimization occurs at query execution time every time the query is executed. The "NULLIDRA" package set, which is bound with the REOPT ALWAYS bind option, is used.

The "NULLIDR1" and "NULLIDRA" are reserved package set names, and when used, REOPT ONCE and REOPT ALWAYS are implied respectively. These package sets have to be explicitly created with these commands:

db2 bind db2clipk.bnd collection NULLIDR1
db2 bind db2clipk.bnd collection NULLIDRA

SQL_ATTR_REOPT and SQL_ATTR_CURRENT_PACKAGE_SET are mutually exclusive, therefore, if one is set, the other is not allowed.

CurrentPackageSet CLI/ODBC configuration keyword

Keyword description:

Issues the SET CURRENT PACKAGESET statement after every connection.

db2cli.ini keyword syntax:

CurrentPackageSet = schema name

Default setting:

The clause is not appended.

Equivalent connection attribute:

SQL_ATTR_CURRENT_PACKAGE_SET

Usage notes:

This option issues the SET CURRENT PACKAGESET SQL statement with the CurrentPackageSet value after every connection to a database. By default this clause is not appended.

The SET CURRENT PACKAGESET SQL statement sets the schema name (collection identifier) that is used to select the package to use for subsequent SQL statements.

CLI/ODBC applications issue dynamic SQL statements. Using this option you can control the privileges used to run these statements:

• Choose a schema to use when running SQL statements from CLI/ODBC applications.
• Ensure the objects in the schema have the desired privileges and then rebind accordingly.
• Set the CurrentPackageSet option to this schema.

The SQL statements from the CLI/ODBC applications will now run under the specified schema and use the privileges defined there.

The following package set names are reserved: "NULLID ", "NULLIDR1", "NULLIDRA".

If both the Reopt and CurrentPackageSet keywords are specified, CurrentPackageSet takes precedence.

SQL_ATTR_CURRENT_PACKAGE_SET connection attribute

SQL_ATTR_CURRENT_PACKAGE_SET (DB2 CLI v5)

A null-terminated character string that indicates the schema name (collection identifier) that is used to select the package for subsequent SQL statements. Setting this attribute causes the SET CURRENT PACKAGESET SQL statement to be issued. If this attribute is set before a connection, the SET CURRENT PACKAGESET SQL statement will be issued at connection time.

CLI/ODBC applications issue dynamic SQL statements. Using this connection attribute, you can control the privileges used to run these statements:

• Choose a schema to use when running SQL statements from CLI/ODBC applications.
• Ensure the objects in the schema have the desired privileges and then rebind accordingly. This typically means binding the CLI packages (sqllib/bnd/db2cli.lst) using the COLLECTION <collid> option. Refer to the BIND command for further details.
• Set the CURRENTPACKAGESET option to this schema.

The SQL statements from the CLI/ODBC applications will now run under the specified schema and use the privileges defined there.

Setting the CLI/ODBC configuration keyword CURRENTPACKAGESET is an alternative method of specifying the schema name.

The following package set names are reserved: "NULLID ", "NULLIDR1", "NULLIDRA".

SQL_ATTR_REOPT and SQL_ATTR_CURRENT_PACKAGE_SET are mutually exclusive, therefore, if one is set, the other is not allowed.

MapBigintCDefault CLI/ODBC configuration keyword

Keyword description:

Specify the default C type of BIGINT columns and parameter markers.

db2cli.ini keyword syntax:

MapBigintCDefault = 0 | 1 | 2

Default setting:

The default C type representation for BIGINT data is SQL_C_BIGINT.

Usage notes:

 

MapBigintCDefault controls the C type that is used when SQL_C_DEFAULT is specified for BIGINT columns and parameter markers. This keyword should be used primarily with Microsoft applications, such as Microsoft Access, which cannot handle 8-byte integers. Set MapBigintCDefault as follows:

• 0 - for the default SQL_C_BIGINT C type representation
• 1 - for an SQL_C_CHAR C type representation
• 2 - for an SQL_C_WCHAR C type representation

This keyword affects the behavior of CLI functions where SQL_C_DEFAULT might be specified as a C type, such as SQLBindParameter(), SQLBindCol(), and SQLGetData().

DescribeOutputLevel CLI/ODBC configuration keyword

Keyword description:

Set the level of output column describe information that is requested by the CLI driver during prepare or describe requests.

db2cli.ini keyword syntax:

DescribeOutputLevel = 0 | 1 | 2 | 3

Default setting:

Request the describe information listed in level 2 of Table 25.

Usage notes:

 

This keyword controls the amount of information the CLI driver requests on a prepare or describe request. By default, when the server receives a describe request, it returns the information contained in level 2 of Table 25 for the result set columns. An application, however, might not need all of this information or might need additional information.

Setting the DescribeOutputLevel keyword to a level that suits the needs of the client application might improve performance because the describe data transferred between the client and server is limited to the minimum amount that the application requires. If the DescribeOutputLevel setting is set too low, it might impact the functionality of the application (depending on the application's requirements). The CLI functions to retrieve the describe information might not fail in this case, but the information returned might be incomplete.

Supported settings for DescribeOutputLevel are:

• 0 - no describe information is returned to the client application
• 1 - describe information categorized in level 1 (see Table 25) is returned to the client application
• 2 - (default) describe information categorized in level 2 (see Table 25) is returned to the client application
• 3 - describe information categorized in level 3 (see Table 25) is returned to the client application

The following table lists the fields that form the describe information that the server returns when it receives a prepare or describe request. These fields are grouped into levels, and the DescribeOutputLevel CLI/ODBC configuration keyword controls which levels of describe information the CLI driver requests.

SQL_DESC_COUNT
SQL_COLUMN_COUNT
SQL_DESC_TYPE
SQL_DESC_CONCISE_TYPE
SQL_COLUMN_LENGTH
SQL_DESC_OCTET_LENGTH
SQL_DESC_LENGTH
SQL_DESC_PRECISION
SQL_COLUMN_PRECISION
SQL_DESC_SCALE
SQL_COLUMN_SCALE
SQL_DESC_DISPLAY_SIZE
SQL_DESC_NULLABLE
SQL_COLUMN_NULLABLE
SQL_DESC_UNSIGNED
SQL_DESC_SEARCHABLE
SQL_DESC_LITERAL_SUFFIX
SQL_DESC_LITERAL_PREFIX
SQL_DESC_CASE_SENSITIVE
SQL_DESC_FIXED_PREC_SCALE

all fields of level 1 and:
SQL_DESC_NAME
SQL_DESC_LABEL
SQL_COLUMN_NAME
SQL_DESC_UNNAMED
SQL_DESC_TYPE_NAME
SQL_DESC_DISTINCT_TYPE
SQL_DESC_REFERENCE_TYPE
SQL_DESC_STRUCTURED_TYPE
SQL_DESC_USER_TYPE
SQL_DESC_LOCAL_TYPE_NAME
SQL_DESC_USER_DEFINED_
         TYPE_CODE

all fields of levels 1
and 2 and:
SQL_DESC_BASE_COLUMN_NAME
SQL_DESC_UPDATABLE
SQL_DESC_AUTO_UNIQUE_VALUE
SQL_DESC_SCHEMA_NAME
SQL_DESC_CATALOG_NAME
SQL_DESC_TABLE_NAME
SQL_DESC_BASE_TABLE_NAME