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. |
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. |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |