Appendix C.4 Foreign DB: DB2

Tables C-8 and C-9 show data type correspondence (foreign DB: DB2).

Table C-8 Data type correspondence (foreign DB: DB2) (1/2)

Data type of DB2 (foreign DB)Data type of HiRDB
SMALLINTINTEGERDECIMAL(m,n)
1 [Figure] m [Figure] 29, 0 [Figure] n [Figure] m
SMALLFLT or REAL1FLOAT or DOUBLE PRECISION1CHAR(n)
1 [Figure] n [Figure] 255
VARCHAR(n)
1 [Figure] n [Figure] 255
MCHAR(n)
1 [Figure] n [Figure] 255
MVARCHAR(n)
1 [Figure] n [Figure] 255
SMALLINTRM2M3, 11MM

INTEGERM2RM4, 11MM

DECIMAL(p,s)
p: Precision
s: Scaling
1 [Figure] p[Figure] 29M5M6R11, 12MM

30 [Figure] p[Figure] 31

M7, 11MM

REAL or FLOAT(n)1
1 [Figure] n[Figure] 21
MMM11RM

DOUBLE or FLOAT(n)1
22 [Figure] n[Figure] 53
MMM11RM

CHAR(n)
1 [Figure] n[Figure] 255

R8, 13

R10, 13

CHAR(n)
256 [Figure] n

VARCHAR(n)
1 [Figure] n[Figure] 255

R8, 13

R10, 13
VARCHAR(n)
256 [Figure] n[Figure] 32,000

VARCHAR(n)
32,001 [Figure] n

GRAPHIC(n)
1 [Figure] n[Figure] 127

GRAPHIC(n)
128 [Figure] n

VARGRAPHIC(n)
1 [Figure] n[Figure] 127

VARGRAPHIC(n)
128 [Figure] n[Figure] 16,000

VARGRAPHIC(n)
16,001 [Figure] n

DATE

TIME

TIMESTAMP

D9, 13

BLOB

CLOB

DBLOB

ROWID

Legend:
R: Recommended data type
M: Mappable data type
D: For execution of the DESCRIBE statement, this is a mappable data type.
Blank: Not mappable data type
Note 1
If a character string type of 255 bytes is defined for columns with greater than or equal to 256 bytes on the foreign server, only up to 255 bytes of data can be acquired.
Note 2
A DB2 error occurs if the number of bytes in a character string containing double-byte characters, after code conversion, is greater than the maximum number of bytes that can be accommodated on DB2.
1 The precision of values that can be represented may be reduced. The scope and precision of values on HiRDB are hardware-dependent.
2 The range is limited to between -32,768 and 32,767.
3 This should be declared as DECIMAL(5). The range is limited to between -32,768 and 32,767.
4 This should be declared as DECIMAL(10). The range is limited to between -2,147,483,648 and 2,147,483,647.
5 This can be used if 1 [Figure] p[Figure] 4, s = 0.
6 This can be used if 1 [Figure] p[Figure] 9, s = 0.
7 If p - s[Figure] 29, this can be mapped to DECIMAL(p-s,0). Any digits below the decimal point are truncated.
8 If the data does not include double-byte characters, the data type is the recommended data type.
9 This should be mapped to CHAR(n) (where n is 19 or 21 to 26). The format of the date or datetime character string is 'YYYY-MM-DD hh:mm:ss' if n = 19, and 'YYYY-MM-DD hh:mm:ss.xxxxxx' if n = 21 to 26 (where x is 1 digit long if n = 21, and 6 digits long if n = 26). Comparison and arithmetic operations performed on a given column can cause an error on the foreign DB or produce unexpected results.
10 If the data includes double-byte characters, the data type is the recommended data type.
11 An error may occur if the number of digits in the integer part is less than the data type of HiRDB.
If the number of digits in the fractional part is less than the data type of HiRDB, data is acquired by truncating the excess fractional part to match the number of digits in the fractional part of the data type of HiRDB.
If the number of digits in the integer or fractional part is larger than the data type of HiRDB, data is acquired by zero-filling any extended portion to match the number of digits in the data type of HiRDB.
12 If p and s are omitted, different foreign servers have different default precision and scaling. Therefore, the precision (p) and scaling (s) on the foreign server should be made equal to the precision (m) and the scaling (n) in the foreign table definition, accordingly
When specifying these operands, make DECIMAL(m,n) equal to DECIMAL(p,s).
13 If there is no specification, make the definition length of the data type of the foreign server equal to that of HiRDB.
If the definition length (length = n bytes, n[Figure] 255) of the data type of HiRDB is less than the actual length of the data type of the foreign server, only the first n bytes are acquired.
If the definition length of the data type of HiRDB is greater, the data is inserted left-justified and any excess length is blank-filled (double-byte spaces for national character strings, single-byte spaces for character strings or mixed character strings), provided that HiRDB is of a fixed-length data type.
If HiRDB is of a variable-length data type, the data is acquired "as is."

Table C-9 Data type correspondence (Foreign DB: DB2) (2/2)

Data type of DB2
(foreign DB)
Data type of HiRDB
NCHAR(n)
1 [Figure] n [Figure] 127
NVARCHAR(n)
1 [Figure] n [Figure] 127
DATETIMETIMESTAMP(p)
p = 0, 2, 4, or 6
INTERVAL YEAR TO DAYINTERVAL HOUR TO SECONDBINARY(n)
1 [Figure] n [Figure] 255
SMALLINT

INTEGER

DECIMAL(p,s)
p: Precision
s: Scaling
1 [Figure] p[Figure] 29

30 [Figure] p[Figure] 31

REAL or FLOAT(n)1
1 [Figure] n[Figure] 21

DOUBLE or FLOAT(n)1
22 [Figure] n[Figure] 53

CHAR(n)
1 [Figure] n[Figure] 255

CHAR(n)
256 [Figure] n

VARCHAR(n)
1 [Figure] n[Figure] 255

VARCHAR(n)
256 [Figure] n[Figure] 32,000

VARCHAR(n)
32,001 [Figure] n

GRAPHIC(n)
1 [Figure] n[Figure] 127
R3, 5

GRAPHIC(n)
128 [Figure] n

VARGRAPHIC(n)
1 [Figure] n[Figure] 127

R3, 5

VARGRAPHIC(n)
128 [Figure] n[Figure] 16,000

VARGRAPHIC(n)
16,001 [Figure] n

DATE

R

TIME

R2

TIMESTAMP

R1, 4

BLOB

CLOB

DBLOB

ROWID

Legend:
R: Recommended data type
Blank: Not mappable data type
Note 1
If a character string type of 255 bytes is specified for a column greater than or equal to 256 bytes on a foreign server, only a maximum of 255 bytes of data can be acquired.
Note 2
If the number of bytes in a character string containing double-byte characters, after code conversion, is greater than the maximum number of bytes that can be handled by DB2, an error may occur on DB2.
1 When mapping to the TIMESTAMP type, specify the value 6 in the precision p.
2 The hour 24 becomes the hour 0.
3 Whereas a maximum of 127 characters (254 bytes) can defined in the GRAPHIC type, for GRAPHIC-type literals the maximum size that can be specified is 124 characters (248 bytes). When specifying 125 characters or greater (249 bytes or greater), use the concatenation operation.
4 The hour 24 becomes the hour 0 of the following day. The value '9999-12-31-24.00.00.000000' may result in an error.
5 If there is no specification, make the definition length of the data type of the foreign server equal to that of HiRDB.
If the definition length (length = n bytes, n[Figure] 255) of the data type of HiRDB is less than the actual length of the data type of the foreign server, only the first n bytes are acquired.
If the definition length of the data type of HiRDB is greater, the data is inserted left-justified and any excess length is blank-filled (double-byte spaces for national character strings, single-byte spaces for character strings or mixed character strings), provided that HiRDB is of a fixed-length data type.
If HiRDB is of a variable-length data type, the data is acquired "as is."