How to set up and test using IBM DB2

DB2 Unicode and Non-Unicode Databases

In DB2 a database is defined as either a Unicode database or a non-Unicode database. This is determined with the CODESET option in the CREATE DATABASE command. Once a database is created, the CODESET (and so Unicode/non-Unicode database) cannot be changed.

Example:

1. Create DATABASE ORDER199ANSI USING CODESET ISO8859-1 TERRITORY US
2. Create DATABASE ORDER199 USING CODESET UTF-8 TERRITORY US

1)  Is a non-Unicode database. You cannot store Unicode data in such database. Data is stored as ANSI or, if you choose an OEM charset, as OEM.

2)  Is a Unicode database.

• CODESET UTF-8 is the default.
• The DB2 documentation is unclear about other codesets defining a Unicode database. The page https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.nls.doc/doc/r0004565.html mentions code page 1200/16-bit Unicode but codepage 1200 is not used anywhere.

Unicode Data in a DB2 Unicode Database

In a DB2 Unicode database there are 2 approaches to store Unicode data:

• Use special Unicode types (graphic types)
• Use char/varchar types to store Unicode data.

Both methods exist parallel to each other. The graphic types are an older way to support Unicode. Using the char/varchar types is a more modern way to support Unicode.

• Most non-Unicode databases cannot store Unicode data. The graphic types are not supported in most non-Unicode databases.
• There are some country-specific code pages for China, Japan, etc. that do support Unicode characters in a non-Unicode database.
• (See the Group columns in: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.nls.doc/doc/r0004565.html )

Graphic Types

In a DB2 Unicode database the following special Unicode types are available:

                Graphic

                Vargraphic

                Long Vargraphic

These types store data as UCS-2. They are comparable to the SQL Server NCHAR, NVARCHAR and NVARCHAR(max) types, respectively.

These types are only available in DB2 Unicode databases. They cannot be created in non-Unicode databases.

Char/Varchar Types in DB2 Unicode Database (UTF-8)

In a DB2 Unicode database character data types (like char, varchar, long varchar) can store Unicode characters. Data is stored as UTF-8.

Char/varchar types in a DB2 Unicode database have, besides a length, a string unit specification.

Example:

CREATE TABLE"DB2ADMIN"."TestUnicodeTable"(

                "ID" INTEGER NOTNULL DEFAULT0,

                "Varchar10Octets"VARCHAR(10 OCTETS) NOTNULL DEFAULT''

                ,"Varchar10Units32"VARCHAR(10 CODEUNITS32) NOTNULL DEFAULT''

                , "VarGraphic10Units16"VARGRAPHIC(10 CODEUNITS16) NOTNULL DEFAULT''

Note:

OCTETS == Bytes

This string unit specification (OCTETS, CODEUNITS16, CODEUNITS32) can only be done in Unicode databases.  In non-Unicode database the string unit specification is not possible.

String Units

• VARCHAR type can have string units OCTETS (== bytes) (default) or CODEUNITS32
• VARGRAPHIC type can have string units CODEUNITS16 (default) or CODEUNITS32
• VARCHAR (10 OCTETS) can contain 10 single-byte UTF-8 characters. ('Müller7890') will not fit because as a UTF-8 string, it is 11 bytes.
• VARCHAR (10 CODEUNITS32) can contain 10 4-byte characters.  For example, 10 smiley characters of 4 bytes each.
• VARGRAPHIC (10 CODEUNITS16) can contain 10 2-byte characters; 10 2-byte Unicode chars, but only 5 4-byte chars (smileys).
• VARGRAPHIC (10 CODEUNITS32) can contain 10 4-byte characters. For example, 10 smiley characters of 4 bytes each.

DataFlex DB2 Driver (DB2_DRV) 20.0

Non-Unicode Database

• DB2_DRV supports non-Unicode database, i.e. databases with an ANSI or OEM codeset.

Unicode Database

• DB2_DRV can read/write Unicode data to the graphic type columns, including composite characters (smileys).
• DB2_DRV can also read/write Unicode to the char/varchar columns.

The driver will handle the various string unit sizes (Octets, codeunits32) when reading/writing data to char/varchar columns.

Char/varchar columns with string unit octets have length restrictions imposed by DB2, not by the db2_drv.

Example:

A varchar(10 octets) column can contain:

10 ASCII character ‘abcdefghij’

5 accented characters ‘üüüüü’

5 Unicode characters ‘фальл’

2 smileys ‘��’

                A varchar(10 codeunits32) column can contain:

10 ASCII character ‘abcdefghij’

10 accented characters ‘üüüüüüüüüü’

10 Unicode characters ‘фальлфальл’

10 smileys ‘�� �� �� ��’

The db2_drv will detect the string unit size and read/write data correct to such columns. Driver buffers will be allocated according to string unit size.

When creating new char/varchar columns, the driver will take the default for the string unit size for the database. When not explicitly set, the default string unit size will be OCTETS.

The default can be changed with DB2 string_units configuration parameter. https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.config.doc/doc/r0060936.html

Example:

db2 => update db cfg for ORDER199 using STRING_UNITS CODEUNITS32

will set the default code units to CODEUNITS32.

So with db2_drv 20.0 both methods to use Unicode data in a DB2 Unicode database are supported:

• Use special Unicode types (graphic types)
• Use char/varchar types to store Unicode data

Which method to use is the developer’s choice. Considerations:

• Graphic types fit better the way DataFlex works.
• Graphic types take more space - data is stored as UCS-2.
• Graphic types may be considered ‘old’ types and not recommended
• Char/varchar with string units in octets has length limitations
• Char/varchar with string units in codeunits32 requires larger buffers

Type Mappings

The types to be used can be configured with db2_drv type mapping settings. These type mapping settings can be configured in db2_drv.int configuration file or at runtime with a driver attribute. Note that mapping settings are only used when creating new columns in a table. Also note that mapping settings do not apply when adding columns in DataFlex Studio, since in Table Editor the DB2 types can be directly chosen.

In db2_drv 20.0 or later, the driver will detect if it is connected to a Unicode database or a non-Unicode database and default type mappings will be set accordingly.

When connected to a Unicode database the graphic types will be used for new columns.

This can be changed with the MAP_DF_ASCII_TO_SQLTYPE and MAP_DF_TEXT_TO_SQLTYPE settings .int, or the corresponding attributes at runtime.

; MAP_DFASCII_TO_SQLTYPE: The DB2 type when creating new DF_ASCII columns

;                       Allowed values:

;                           char

;                           varchar

;                           graphic

;                           vargraphic

;                       Default:

;                           When connected to a DB2 Unicode database    : vargraphic

;                           When connected to a DB2 Non-Unicode database: varchar

;                  

;MAP_DFASCII_TO_SQLTYPE vargraphic

; MAP_DFTEXT_TO_SQLTYPE: The DB2 type when creating new DF_TEXT columns

;                       Allowed values:

;                           varchar        

;                           long varchar    

;                           clob           

;                           vargraphic      

;                           long vargraphic

;                           DBCLOB         

;                       Default:

;                           When connected to a DB2 Unicode database    : long vargraphic

;                           When connected to a DB2 Non-Unicode database: long varchar

;MAP_DFTEXT_TO_SQLTYPE long vargraphic

See Also

Environment Setup Notes