IBM DB2 mainframe data mapping

Mapping to DB2 mainframe consolidated data types

The following table identifies how SQL Anywhere and UltraLite remote data types are mapped to DB2 mainframe consolidated data types. For example, a column of type BIT on the remote database should be type SMALLINT on the consolidated database.

SQL Anywhere or UltraLite data type

IBM DB2 mainframe data type

Notes

BIGINT

DECIMAL(20)

BINARY(n<MRL)

VARCHAR(n) FOR BIT DATA

When creating a DB2 table, you need to pay attention to the DB2 page size. DB2 has a maximum row length based on the page size: the MRL is 4005 when the page size is 4K, 8101 when 8K, 16293 when 16K and 32677 when 32K. The length of all columns in a table can't exceed the above limitation. If a table has a BLOB or CLOB column, you count row length using the LOB locator, not BLOB or CLOB data directly. For details, see the DB2 documentation.

BINARY(n>=MRL)

BLOB(n)

When creating a DB2 table, you need to pay attention to the DB2 page size. DB2 has a maximum row length based on the page size: the MRL is 4005 when the page size is 4K, 8101 when 8K, 16293 when 16K and 32677 when 32K. The length of all columns in a table can't exceed the above limitation. If a table has a BLOB or CLOB column, you count row length using the LOB locator, not BLOB or CLOB data directly. For details, see the DB2 documentation.

BIT

SMALLINT

CHAR(n<MRL)

VARCHAR(n)

MRL is DB2 maximum row length.

DB2 VARCHAR can only hold up to 32672 bytes depending on page size.

DB2 has a maximum row length (MRL) based on the page size: the MRL is 4005 when the page size is 4K, 8101 when 8K, 16293 when 16K and 32677 when 32K. For details, see your DB2 documentation.

SQL Anywhere CHAR is identical to SQL Anywhere VARCHAR.

CHAR(n>=MRL)

CLOB(n)

DB2 has a maximum row length (MRL) based on the page size: the MRL is 4005 when the page size is 4K, 8101 when 8K, 16293 when 16K and 32677 when 32K. For details, see your DB2 documentation.

SQL Anywhere CHAR is identical to SQL Anywhere VARCHAR.

DB2 CLOB values can be longer than SQL Anywhere or UltraLite values. You must make sure that downloaded values are not too big.

DATE

DATE

For SQL Anywhere and UltraLite, the time value must in the format 00:00:00.

DATETIME

TIMESTAMP

DECIMAL(p<32,s)

DECIMAL(p,s)

The precision of SQL Anywhere DECIMAL is between 1 and 127. The maximum precision of DB2 DECIMAL is 31. Any data of SQL Anywhere DECIMAL precision greater than 31 cannot be synchronized to DB2.

DECIMAL(p>=32,s)

Any data of SQL Anywhere DECIMAL precision greater than 31 cannot be synchronized to DB2.

DOUBLE

DOUBLE

DOUBLE can cause problems if the consolidated and remote databases do not allow the exact same (imprecise) values. We do not test all possible values, so care must be taken. To avoid problems, do not use these types as part of a primary key.

To successfully synchronize, SQL Anywhere/UltraLite DOUBLE values must be within the DB2 mainframe DOUBLE value range.

FLOAT(1-24)

REAL

FLOAT can cause problems if the consolidated and remote databases do not allow the exact same (imprecise) values. We do not test all possible values, so care must be taken. To avoid problems, do not use these types as part of a primary key.

To successfully synchronize, DB2 mainframe REAL values must be within the SQL Anywhere/UltraLite REAL value range.

FLOAT(25-53)

DOUBLE

FLOAT can cause problems if the consolidated and remote databases do not allow the exact same (imprecise) values. We do not test all possible values, so care must be taken. To avoid problems, do not use these types as part of a primary key.

To successfully synchronize, SQL Anywhere/UltraLite DOUBLE values must be within the DB2 mainframe DOUBLE value range.

IMAGE

BLOB(n)

INTEGER

INTEGER

LONG BINARY

BLOB(n)

LONG NVARCHAR

CLOB(n)

There is no corresponding data type in DB2. If the DB2 character set is Unicode, SQL Anywhere long NVARCHAR can synchronize to DB2 CLOB. UltraLite does not have LONG NVARCHAR.

LONG VARBIT

BLOB(n)

LONG VARCHAR

CLOB(n)

MONEY

DECIMAL(19,4)

NCHAR(c)

VARCHAR(n) or CLOB(n)

SQL Anywhere NCHAR is identical to SQL Anywhere NVARCHAR.

There is no corresponding data type in DB2. If the DB2 character set is Unicode, NCHAR can synchronize to DB2 VARCHAR or CLOB. The size of SQL Anywhere NCHAR is characters and the size of DB2 VARCHAR is bytes. If you map to VARCHAR, the total bytes of NCHAR cannot be bigger than MRL. Otherwise, NCHAR should map to CLOB. It is difficult to calculate the many bytes in NCHAR(c), but it is approximately c=n/4. In general, if c is less than MRL/4, map to VARCHAR(n), but if c is greater than or equal to MRL/4, map to CLOB(n).

NTEXT

CLOB(n)

SQL Anywhere NTEXT is identical to SQL Anywhere LONG NVARCHAR.

There is no corresponding data type in DB2. If the DB2 character set is Unicode, NCHAR can synchronize to DB2 VARCHAR or CLOB. The size of SQL Anywhere NCHAR is characters and the size of DB2 VARCHAR is bytes. If you map to VARCHAR, the total bytes of NCHAR cannot be bigger than MRL. Otherwise, NCHAR should map to CLOB. It is difficult to calculate the many bytes in NCHAR(c), but it is approximately c=n/4. In general, if c is less than MRL/4, map to VARCHAR(n), but if c is greater than or equal to MRL/4, map to CLOB(n).

NUMERIC(p<32,s)

NUMERIC(p,s)

NUMERIC(p>=32,s)

There is no corresponding data type in DB2.

NVARCHAR(c)

VARCHAR(n) or CLOB(n)

There is no corresponding data type in DB2. If the DB2 character set is Unicode, NCHAR can synchronize to DB2 VARCHAR or CLOB. The size of SQL Anywhere NCHAR is characters and the size of DB2 VARCHAR is bytes. If you map to VARCHAR, the total bytes of NCHAR cannot be bigger than MRL. Otherwise, NCHAR should map to CLOB. It is difficult to calculate the many bytes in NCHAR(c), but it is approximately c=n/4. In general, if c is less than MRL/4, map to VARCHAR(n), but if c is greater than or equal to MRL/4, map to CLOB(n).

REAL

REAL

REAL can cause problems if the consolidated and remote databases do not allow the exact same (imprecise) values. We do not test all possible values, so care must be taken. To avoid problems, do not use these types as part of a primary key.

To successfully synchronize, DB2 mainframe REAL values must be within the SQL Anywhere/UltraLite REAL value range.

SMALLDATETIME

TIMESTAMP

SMALLINT

SMALLINT

SMALLMONEY

DECIMAL(10,4)

TEXT

CLOB(n)

SQL Anywhere TEXT is identical to SQL Anywhere LONG VARCHAR.

TIME

TIMESTAMP or TIME

SQL Anywhere and UltraLite TIME values with fractional seconds require DB2 TIMESTAMP. SQL Anywhere and UltraLite time values with fractional seconds that are always zero can use DB2 TIME.

TIMESTAMP

TIMESTAMP

TINYINT

SMALLINT

UNIQUEIDENTIFIER

CHAR(36)

UNIQUEIDENTIFIERSTR

CHAR(36)

UNIQUEIDENTIFIERSTR is not recommended for DB2. Use UNIQUEIDENTIFIER instead.

UNSIGNED BIGINT

DECIMAL(20)

DB2 values must be non-negative.

UNSIGNED INTEGER

DECIMAL(11)

DB2 values must be non-negative.

UNSIGNED SMALLINT

DECIMAL(5)

DB2 values must be non-negative.

UNSIGNED TINYINT

SMALLINT

DB2 values must be non-negative.

VARBINARY(n<MRL)

VARCHAR(n) FOR BIT DATA

When creating a DB2 table, you need to pay attention to the DB2 page size. DB2 has a maximum row length based on the page size: the MRL is 4005 when the page size is 4K, 8101 when 8K, 16293 when 16K and 32677 when 32K. The length of all columns in a table can't exceed the above limitation. If a table has a BLOB or CLOB column, you count row length using the LOB locator, not BLOB or CLOB data directly. For details, see the DB2 documentation.

VARBINARY(n>=MRL)

BLOB(n)

When creating a DB2 table, you need to pay attention to the DB2 page size. DB2 has a maximum row length based on the page size: the MRL is 4005 when the page size is 4K, 8101 when 8K, 16293 when 16K and 32677 when 32K. The length of all columns in a table can't exceed the above limitation. If a table has a BLOB or CLOB column, you count row length using the LOB locator, not BLOB or CLOB data directly. For details, see the DB2 documentation.

VARBIT(n<MRL)

VARCHAR(n)

When creating a DB2 table, you need to pay attention to the DB2 page size. DB2 has a maximum row length based on the page size: the MRL is 4005 when the page size is 4K, 8101 when 8K, 16293 when 16K and 32677 when 32K. The length of all columns in a table can't exceed the above limitation. If a table has a BLOB or CLOB column, you count row length using the LOB locator, not BLOB or CLOB data directly. For details, see the DB2 documentation.

VARBIT(n>=MRL)

CLOB(n)

When creating a DB2 table, you need to pay attention to the DB2 page size. DB2 has a maximum row length based on the page size: the MRL is 4005 when the page size is 4K, 8101 when 8K, 16293 when 16K and 32677 when 32K. The length of all columns in a table can't exceed the above limitation. If a table has a BLOB or CLOB column, you count row length using the LOB locator, not BLOB or CLOB data directly. For details, see the DB2 documentation.

VARCHAR(n<MRL)

VARCHAR(n)

DB2 VARCHAR can only hold up to 32672 bytes, depending on page size.

DB2 has a maximum row length (MRL) based on the page size: the MRL is 4005 when the page size is 4K, 8101 when 8K, 16293 when 16K and 32677 when 32K. For details, see your DB2 documentation.

SQL Anywhere CHAR is identical to SQL Anywhere VARCHAR.

VARCHAR(n>=MRL)

CLOB(n)

DB2 has a maximum row length (MRL) based on the page size: the MRL is 4005 when the page size is 4K, 8101 when 8K, 16293 when 16K and 32677 when 32K. For details, see your DB2 documentation.

SQL Anywhere CHAR is identical to SQL Anywhere VARCHAR.

DB2 CLOB values can be longer than SQL Anywhere or UltraLite values. You must make sure that downloaded values are not too big.

XML

CLOB(n)

SQL Anywhere XML is identical to SQL Anywhere LONG VARCHAR.

Mapping to SQL Anywhere or UltraLite remote data types

The following table identifies how DB2 mainframe consolidated data types are mapped to SQL Anywhere and UltraLite remote data types. For example, a column of type INT on the consolidated database should be type INTEGER on the remote database.

IBM DB2 mainframe data type SQL Anywhere or UltraLite data type Notes

BLOB

LONG BINARY

CHAR(n)

VARCHAR(n)

There is no equivalent to DB2 CHAR in SQL Anywhere. You should not use CHAR in a consolidated database column that is synchronized. If you must synchronize DB2 CHAR columns, run MobiLink server with the -b option.

CHAR(n) FOR BIT DATA

BINARY(n)

There is no equivalent to DB2 CHAR in SQL Anywhere. You should not use CHAR in a consolidated database column that is synchronized. If you must synchronize DB2 CHAR columns, run MobiLink server with the -b option.

CLOB(n)

LONG VARCHAR

DATE

DATE

For SQL Anywhere and UltraLite, the time value must in the format 00:00:00.

DBCLOB(n)

LONG VARCHAR

The data type DBCLOB(n) is only used for double-byte characters. SQL Anywhere does not have a corresponding data type. When the DB2 character set is Unicode, DBCLOB(n) is equivalent to CLOB.

DECIMAL(p,s)

DECIMAL(p,s)

DOUBLE

DOUBLE

DOUBLE can cause problems if the consolidated and remote databases don't allow the exact same (imprecise) values. We do not test all possible values, so care must be taken. To avoid problems, do not use these types as part of a primary key.

To successfully synchronize, SQL Anywhere/UltraLite DOUBLE values must be within the DB2 DOUBLE value range.

FLOAT

DOUBLE

FLOAT can cause problems if the consolidated and remote databases don't allow the exact same (imprecise) values. We do not test all possible values, so care must be taken. To avoid problems, do not use these types as part of a primary key.

To successfully synchronize, SQL Anywhere/UltraLite DOUBLE values must be within the DB2 DOUBLE value range.

GRAPHIC(n)

VARCHAR(2n)

DB2 GRAPHIC does blank-padding. There is no equivalent to DB2 GRAPHIC in SQL Anywhere. You should not use GRAPHIC in a consolidated database column that is synchronized.

The data type GRAPHIC is only used for double-byte characters. SQL Anywhere does not have a corresponding data type. When the DB2 character set is Unicode, GRAPHIC is equivalent to CHAR.

INT

INTEGER

NUMERIC(p,s)

NUMERIC(p,s)

REAL

DOUBLE

REAL can cause problems if the consolidated and remote databases don't allow the exact same (imprecise) values. We do not test all possible values, so care must be taken. To avoid problems, do not use these types as part of a primary key.

On DB2 mainframe, REAL, DOUBLE and FLOAT have the same range, between -7.2E+75 and 7.2E+75. The largest negative value is about -5.4E-79, and the smallest positive value is about 5.4E-79. The range of SA/UL REAL is -3.402823e+38 to 3.402823e+38, and DOUBLE is 2.22507385850721e-308 to 1.79769313486231e+308. To successfully synchronize, DB2 mainframe REAL must be in SA/UL REAL range and SA/UL DOUBLE must be in DB2 mainframe DOUBLE range.

ROWID

There is no corresponding data type in SQL Anywhere or UltraLite. ROWID is maintained by DB2 server. This data type cannot be synchronized.

SMALLINT

SMALLINT

TIME

TIME

The fractional seconds values from SQL Anywhere TIME values are truncated on download. To avoid problems, do not use fractional seconds.

TIMESTAMP

TIMESTAMP

VARCHAR(n)

VARCHAR(n)

VARCHAR(n) FOR BIT DATA

VARBINARY(n)

VARGRAPHIC(n)

VARCHAR(2n)

The data type VARGRAPHIC is only used for double-byte characters. SQL Anywhere does not have a corresponding data type. When the DB2 character set is Unicode, VARGRAPHIC is equivalent to VARCHAR.