Bidirectional support with Db2 Connect

When data is exchanged between Db2 Connect and a database on the server, it is usually the receiver that performs conversion on the incoming data. The same convention would normally apply to bidirectional layout transformations, and is in addition to the usual code page conversion. Db2 Connect has the optional ability to perform bidirectional layout transformation on data it is about to send to the server database, in addition to data received from the server database.

In order for Db2 Connect to perform bidirectional layout transformation on outgoing data for a server database, the bidirectional CCSID of the server database must be overridden. This is accomplished through the use of the BIDI parameter in the PARMS field of the DCS database directory entry for the server database.

Note: If you want Db2 Connect to perform layout transformation on the data it is about to send to the Db2® host or System i database, even though you do not have to override its CCSID, you must still add the BIDI parameter to the PARMS field of the DCS database directory. In this case, the CCSID that you should provide is the default Db2 host or System i database CCSID.
The BIDI parameter is to be specified as the ninth parameter in the PARMS field, along with the bidirectional CCSID with which you want to override the default server database bidirectional CCSID: 
   ",,,,,,,,BIDI=xyz"
where xyz is the CCSID override.
Note: The registry variable DB2BIDI must be set to YES for the BIDI parameter to take effect.

The use of this feature is best described with an example.

Suppose you have a Hebrew Db2 client running CCSID 62213 (bidirectional string type 5), and you want to access a Db2 host or System i database running CCSID 00424 (bidirectional string type 4). However, you know that the data contained in the Db2 host orSystem i database is based on CCSID 08616 (bidirectional string type 6).

There are two problems here: The first is that the Db2 host or System i database does not know the difference in the bidirectional string types with CCSIDs 00424 and 08616. The second problem is that the Db2 host or System i database does not recognize the Db2 client CCSID (62213). It only supports CCSID 00862, which is based on the same code page as CCSID 62213.

You will need to ensure that data sent to the Db2 host or System i database is in bidirectional string type 6 format to begin with, and also let Db2 Connect know that it has to perform bidirectional transformation on data it receives from the Db2 host or System i database. You will need to use following catalog command for the Db2 host or System i database: 
   db2 catalog dcs database nydb1 as telaviv parms ",,,,,,,,BIDI=08616"

This command tells Db2 Connect to override the Db2 host or System i database CCSID of 00424 with 08616. This override includes the following processing:

  1. Db2 Connect connects to the Db2 host or System i database using CCSID 00862.
  2. Db2 Connect performs bidirectional layout transformation on the data it is about to send to the Db2 host or System i database. The transformation is from CCSID 62213 (bidirectional string type 5) to CCSID 62221 (bidirectional string type 6).
  3. Db2 Connect performs bidirectional layout transformation on data it receives from the Db2 host or System i database. This transformation is from CCSID 08616 (bidirectional string type 6) to CCSID 62213 (bidirectional string type 5).
Note: In some cases, use of a bidirectional CCSID may cause the SQL query itself to be modified in such a way that it is not recognized by the Db2 server. Specifically, you should avoid using IMPLICIT CONTEXTUAL and IMPLICIT RIGHT-TO-LEFT CCSIDs when a different string type can be used. CONTEXTUAL CCSIDs can produce unpredictable results if the SQL query contains quoted strings. Avoid using quoted strings in SQL statements; use host variables whenever possible.

If a specific bidirectional CCSID is causing problems that cannot be rectified by following these recommendations, set DB2BIDI to NO.