skip to main content
OpenEdge Data Management: DataServer for ODBC
ODBC Data Source Data Types : DB2 UDB data type equivalencies
 

DB2 UDB data type equivalencies

The following table lists the DB2 UDB data types supported by the DataServer, their ODBC SQL equivalents, and their default OpenEdge equivalents. The data types in parentheses are alternative data types that you can specify in the schema holder for your DB2 UDB data source. You cannot change the list of default data types in a schema holder for a DB2 UDB data source.
Table 31. DB2 UDB data type equivalencies
DB2 UDB Data type
SQL-ODBC data type
OpenEdge default
integer
SQL_TYPE_INTEGER
INTEGER (DECIMAL) (INTEGER) (INT64)
bigint
SQL_BIGINT
INT64 (DECIMAL) (INTEGER) (LOGICAL)
smallint
SQL_TYPE_SMALLINT
INTEGER (DECIMAL) (INTEGER) (INT64)
decimal1
SQL_TYPE_DECIMAL
DECIMAL (INTEGER) (INT64)
real/float2
SQL_TYPE_REAL
DECIMAL (INTEGER) (INT64)
double precision/float
SQL_TYPE_FLOAT
DECIMAL (INTEGER) (INT64)
date
SQL_TYPE_TIMESTAMP
CHARACTER (DATE)
time
SQL_TYPE_TIMESTAMP
CHARACTER3, 4, 5
timestamp
SQL_TYPE_TIMESTAMP
DATETIME or DATETIME-TZ (CHARACTER) (DATE)
char(n)
SQL_TYPE_CHARACTER
CHARACTER 6
varchar(n)
SQL_TYPE_VARCHAR
CHARACTER
long varchar(n)
SQL_TYPE_LONGVARCHAR
CHARACTER
graphic
SQL_TYPE_BINARY
CHARACTER
vargraphic(n)
SQL_TYPE_VARBINARY
CHARACTER
long vargraphic
SQL_TYPE_LONGBINARY
CHARACTER
1 The DataServer truncates values in DB2 UDB decimal or numeric columns defined with a scale greater than 10. In the case of float columns, it reliably translates up to 10 places only.

2 Do not use the float or real data types in joins, in primary indexes, or with the equality operator.

3 When you change the default mapping of the DB2 UDB timestamp or time data types to the OpenEdge DATE data type, OpenEdge truncates the time portion of the date.

4 The DB2 UDB timestamp data type contains both date and time information. The DataServer maps this to the OpenEdge CHARACTER data type; however, you can change the CHARACTER data type to DATE in the schema holder. If you do, remember to change the format to match the new data type. For example, if you change the data type to DATE, specify a date format, such as 99/99/99.

5 The default format used for a TIME data type is "99:99:99". A TIME column mapped to a CHARACTER type in the ODBC schema holder can be received on update into that TIME column as a 6-byte, undelimited value, such as "101112" ( meaning 10 hours, 1 minutes and 12 seconds). Or, it can be received as a 5-8 byte, colon-delimited, value, for example, "1:2:3" or "10:11:12". The TIME value received must be in hours, minutes and then seconds. No white space is allowed in the string format. The colon (:) is the only supported delimiter character.

6 SQL_TYPE_CHARACTER is a fixed length data type in a foreign database whereas the OpenEdge CHARACTER data type is variable length. If you retrieve data from a fixed length character data type from a foreign database, the value will be padded with blanks for the length of the fixed length data. If it is desirable to have the OpenEdge database trim white space data from the right or trailing side of a fixed length character value and then null terminated for the OpenEdge data conversion, you should specify the PRGRS_MAPFIXEDCHAR -Dsrv switch in your connection parameters to the foreign schema.