Using DB2 CLI Connection Attributes for DB2

DB2® Call Level Interface (DB2 CLI) is a callable SQL interface to DB2 LUW, DB2 for z/OS® and DB2 for i. IBM® Cognos® Business Intelligence can change some of the DB2 CLI connection attributes to pass application context to DB2 in a format acceptable to the components of IBM Optim™ Integrated Data Management.

This information can later be retrieved from DB2 special registers using SQL statements.

To enable this functionality in IBM Cognos BI, you must modify a configuration file on each IBM Cognos report server computer that is configured in your IBM Cognos environment. Because this functionality is set up at the query level, the information that is associated with the connection attributes is automatically updated every time that the report runs.

The following list shows the DB2 CLI connection attributes that can be changed by IBM Cognos BI, and the type of information that these attributes can pass to DB2:

SQL_ATTR_INFO_USERID
Specifies the name of the user running a report.
SQL_ATTR_INFO_WRKSTNNAME
Specifies the address of the system on which the user's browser is installed.
SQL_ATTR_INFO_APPLNAME
Specifies the package name associated with the query. If the string is longer than 32 characters, it overflows to $SLOT2 in the accounting string.

SQL_ATTR_INFO_ACCTSTR

Specifies the prefix or string that associates the request with IBM Cognos BI. The values are:

Table 1. Using DB2 CLI connection attributes for DB2
Value	Description
COG	Associates the request with IBM Cognos products in IBM Optim Integrated Data Management.
ccc	Associates the request with an IBM Cognos solution. For version 8.4, this is set to BI.
vr	Specifies the version of IBM Cognos product, such as 8.4.
Additional accounting information	This information is divided into the following fields (slots): $SLOT2 - $packageName (overflow section for $SLOT1) $SLOT3 - $reportName $SLOT4 - $queryName $SLOT5 - $reportPath Each slot has a fixed length that accepts strings containing no more than 46 bytes, padded with blanks if necessary. Because report paths, model paths, and so on, are often long, the strings may be shortened to adjust to the space limitations. Note: In DB2, values passed to the API cannot contain single quote characters, which are converted to spaces. If the character set encoding is using multiple bytes per character, the character is converted to "?" in order to avoid overflow. This is important where Unicode is used and a character may require more than 2 bytes.

Procedure

If you connect to your database with the compatible query mode, do the following steps:
1. In the c10_location/configuration directory, make a copy of the CQEConfig.xml.sample file and rename it to CQEConfig.xml.
  Tip: If the CQEConfig.xml file was used for other purposes, for example to disable session caching, it might exist in the c10_location/configuration directory. In this situation, use the existing CQEConfig.xml file to perform the remaining steps.
2. Open the c10_location/configuration/CQEConfig.xml file in an editor.
  Ensure that your editor supports saving files in UTF-8 format.
3. Locate the <section name="QueryEngine"> element and add the DB2WFM entry with a value of 1, as shown in the following example:
```
<section name="QueryEngine">
      <entry name=" DB2WFM" value="1"/>
</section>
```
  To disable this functionality, set the value to zero.
If you connect to your database with the dynamic query mode, do the following steps:
1. In the c10_location/configuration directory, make a copy of the xqe.config.xml file and rename it to xqe.config.xml.backup.
2. Open the c10_location/configuration/xqe.config.xml file in an editor.
  Ensure that your editor supports saving files in UTF-8 format.
3. Locate the <setConnectionAttributes enabled="false"> element and change its value to "true", as shown in the following example:
```
<setConnectionAttributes enabled="true">
```
  To disable this functionality, set the value to "false".
Save the file.
Repeat the steps for each report server computer that is configured in your IBM Cognos environment.
Restart the IBM Cognos service.