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 the CQEConfig.xml file on each IBM Cognos report server computer configured for your IBM Cognos environment. Because this functionality is set up at the query level, the information associated with the connection attributes is automatically updated every time 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:

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

Copy the c10_location/configuration/CQEConfig.xml.sample file to c10_location/bin and rename it to CQEConfig.xml.
Note: If the CQEConfig.xml was used for other purposes, for example to disable session caching, it may already exist in the c10_location/bin directory. In this situation, use the existing CQEConfig.xml file to perform the remaining steps.
Open the c10_location/bin/CQEConfig.xml in an editor.
Ensure that your editor supports saving files in UTF-8 format.
Locate the <section name="QueryEngine"> element and add the DB2WFM entry with a value of 1, as shown below:
```
<section name="QueryEngine">
      <entry name=" DB2WFM" value="1"/>
       …
</section>
```
To disable this functionality, set the value to zero or remove the element.
Save the CQEConfig.xml file.
Repeat the steps for each report server computer configured for your IBM Cognos environment.
Restart the IBM Cognos service.