DB2 Server for VM: System Administration

Using Double-Byte Character Set (DBCS)

The double-byte character set (DBCS) option supports the use of DBCS characters in identifiers, constants and data. Identifiers can be either:

• Host identifiers (such as host variables), or
• SQL identifiers (such as dbspaces, tables or columns).

Constants and data containing DBCS characters can be either:

• Graphic data, or
• Character data with a mixed subtype (that is, character data containing DBCS characters).

Setting the DBCS option also ensures that:

• A shift-out character is paired with a shift-in character on output,
• When mixed character data is truncated, truncation does not occur between the two bytes of a DBCS character.

If your installation uses a double-byte character set, you should consider setting the DBCS option to YES. The DBCS option can be set for the application server, the application requester, or both. For the application server, the DBCS option is set in the SYSTEM.SYSOPTIONS catalog table. For the application requester, the DBCS option is set by the DBCS parameter of the SQLINIT EXEC.

For information on enabling the DBCS option, see Setting the DBCS Option for the Application Server, and Setting DBCS Option for Application Requestors.

When the DBCS option is set to YES, the shift-out (X'0E') and shift-in (X'0F') delimiters are recognized in both identifiers in SQL statements and mixed data character string constants. The recognition of the delimiters provides the following benefits:

• On the application server
  • SQL identifiers can contain DBCS characters.
• On the application requester
  • Host identifiers can contain DBCS characters.
  • The DBS utility processing ensures the pairings of shift-out and shift-in characters.
  • ISQL allows the input, print, and display of DBCS characters and mixed data.

However, setting the DBCS option induces overhead for checking the proper pairing of shift-out and shift-in characters.

Identifiers Containing DBCS Characters

Identifiers can be either host identifiers or SQL identifiers.

To use host identifiers that contain DBCS characters, the VSE application requester must have the DBCS option set to YES. (For more information on the SQLINIT EXEC, see the DB2 Server for VSE & VM Database Administration manual.)

To use ordinary SQL identifiers that contain DBCS characters, the application server must have the DBCS option in the SYSTEM.SYSOPTIONS catalog table set to YES, and must also support DBCS characters and mixed data. The application server supports DBCS characters and mixed data when a mixed CHARNAME is specified as an initialization parameter. A mixed CHARNAME has a non-zero value for the CCSIDMIXED row in the SYSTEM.SYSOPTIONS catalog table. For more information, see Choosing the Application Server Default CHARNAME and CCSID.

If the DBCS option is set to YES for the application server, you can use DBCS characters in ordinary SQL identifiers. The identifier can be DBCS characters, or can contain a DBCS substring.

Identifiers are recorded in the catalog tables. When the database manager stores identifiers that contain DBCS characters, it also stores the shift-out and shift-in delimiters. The delimiters are stored because all columns of the catalog tables that contain identifiers have a data type of either CHAR or VARCHAR.

The number of bytes required to represent a string of DBCS characters is equal to:

    2 x the number of DBCS characters + 2

For more information on how identifiers are used in application programs, see the DB2 Server for VSE & VM Application Programming manual.

Constants and Data Containing DBCS Characters

Constants and data containing DBCS characters can be either graphic data or character data with a mixed subtype.

To use graphic and mixed constants or data, the application server and the application requester must support mixed data. This support is provided by specifying a mixed CHARNAME on the SQLSTART EXEC for the application server, and on the SQLINIT EXEC for the application requester. The application server supports graphic and mixed data when the default CHARNAME is a mixed CHARNAME. A mixed CHARNAME has a non-zero value for the CCSIDMIXED row in the SYSTEM.SYSOPTIONS catalog table. For more information, see Choosing the Application Server Default CHARNAME and CCSID. The application requester supports graphic and mixed data when a mixed CHARNAME is specified for the SQLINIT EXEC. (For more information, see the DB2 Server for VSE & VM Database Administration manual.) Using a mixed CHARNAME provides the following benefits:

• On the application server
  • Character string constants can contain mixed data consisting of DBCS and SBCS characters.
  • When character string constants containing DBCS characters are used in SQL statements, the data is correctly interpreted as having a mixed subtype and a mixed CCSID.
• On the application requester
  • Character string constants can contain mixed data consisting of DBCS, SBCS, and on some platforms, MBCS characters. (The MBCS characters will be stored as SBCS or DBCS characters by the database manager.)

If the DBCS value for the application server is set to YES in the SYSTEM.SYSOPTIONS catalog table, the SQLINIT DBCS option for the application requester does not have to be YES for mixed data to be recognized. Mixed data is still recognized and tagged with the appropriate mixed CCSID if the SQLINIT CHARNAME is a mixed CHARNAME, and the character string constant is a valid mixed string.

The implied data type of all string character constants is VARCHAR. When the DBCS option is set to YES:

• Constants with only SBCS characters have a subtype of SBCS.
• Constants with a combination of SBCS and DBCS characters have a subtype of mixed. For example:

    'abc<XXYYZZ>'
  

• Constants containing only DBCS are also of the character data type with a subtype of mixed. They are not considered to be graphic constants. For example:

    '<XXYYZZ>'
  

When the DBCS option for the application server is set to NO, all character constants have a subtype of SBCS.