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.




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.


Unicode Data in a DB2 Unicode Database

In a DB2 Unicode database there are 2 approaches 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.


Graphic Types

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



                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.



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''



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


DataFlex DB2 Driver (DB2_DRV) 20.0


Non-Unicode Database


Unicode Database


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.


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.


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:


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


Type Mappings


The types to be used can be configured with db2_drv type mapping settings. These type mapping settings can be configured in 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_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