DDS/SQL Data Type Mappings

Troubleshooting

Problem

This document lists the mapping of DDS data types to IBM DB2 for i SQL, and ODBC for the most common data types.

Resolving The Problem

The following table lists the DDS/SQL data type mappings for the common data types.

For a listing of IBM DB2 UDB for IBM i SQL data type mappings to ODBC, use the ODBC SQLGetTypeInfo API. For data type mappings between DB2 UDB for IBM i SQL data types and a data type for a specific programming language, see the Embedded SQL Programming guide in IBM Documentation: https://www.ibm.com/docs/en/i/7.5.0?topic=programming-pdf-file-embedded-sql

`DDS`	`SQL`	`ODBC`
`ZONED1 6S 0`	`NUMERIC`	`SQL_NUMERIC=2 (1.0)`
`PACKED1 6P 0`	`DECIMAL`	`SQL_DECIMAL=3 (1.0)`
`SMALLINT1 4B 0`	`SMALLINT`	`SQL_SMALLINT=5 (1.0)`
`INT1 9B 0`	`INTEGER`	`SQL_INTEGER=4 (1.0)`
`BIGINT1 18B 0`	`BIGINT`	`SQL_BIGINT=-5 (1.0)`
`SINGLE1 4F 0`	`REAL FLOAT(1-24)`	`SQL_REAL=7 (1.0) SQL_FLOAT=6 (3.0) (See Note 1.)`
`DOUBLE1 17F 0 FLTPCN(*DOUBLE)`	`DOUBLE FLOAT FLOAT(25-53)`	`SQL_DOUBLE=8 (1.0) SQL_FLOAT=6 (1.0, 3.0) (See Note 1.)`
`DATE1 L`	`DATE`	`SQL_TYPE_DATE=91 (3.0) SQL_C_DATE=9 (1.0)`
`TIME1 T`	`TIME`	`SQL_TYPE_TIME=92 (3.0) SQL_TIME=10 (1.0)`
`TIMESTAMP1 Z`	`TIMESTAMP1`	`SQL_TYPE_TIMESTAMP=93 (3.0) SQL_TIMESTAMP=11 (1.0)`
`CHAR1 8A`	`CHAR`	`SQL_CHAR`
`VARCHAR1 8A VARLEN`	`VARCHAR`	`SQL_VARCHAR=12 (1.0) SQL_LONGVARCHAR=-1 (1.0)`
`BINARY1 8H`	`CHAR() FOR BIT DATA`	`SQL_BINARY`
`VARBINARY 8H VARLEN`	`VARCHAR() FOR BIT DATA`	`SQL_VARBINARY=-3 (1.0) SQL_LONGVARBINARY=-4 (1.0)`
`UNICODE 10G CCSID(13488)`	`GRAPHIC() CCSID 13488`	`SQL_WCHAR=-8 (3.0)`
`UNICODE 10G CCSID(13488) VARLEN`	`VARGRAPHIC() CCSID 13488`	`SQL_WVARCHAR=-9 (3.0) SQL_WLONGVARCHAR=-10 (3.0)`
`UNDEFINED 4B 2 (See Note 2.)`	`N/A`	`N/A`
`N/A`	`BLOB, CLOB, DBCLOB (See Note 4.)`	`SQL_LONGVARBINARY SQL_LONGVARCHAR SQL_WLONGVARCHAR`
`N/A`	`DATALINK`	`N/A`
`OPEN1 10O CCSID(937) EITHER1 10E CCSID(937) ONLY1 10J CCSID(937)`	`CHAR (See Note 3.)`	`SQL_CHAR (See Note 3.)`
`GRAPHIC 30G CCSID(<DBCS CCSID>)`	`CHAR (See Note 4.)`	`SQL_CHAR (See Note 4.)`
`N/A`	`DECFLOAT`	`SQL_DOUBLE (See Note 5.) SQL_VARCHAR`

Notes:

1.	SQL_FLOAT: ODBC 2.x and earlier defined SQL_FLOAT as a double. ODBC 3.0 and later allow SQL_FLOAT to be described as variable: single or double.
2.	Binary fields with nonzero scale. The DDS specification allows a binary data type of length 9 or less to have a nonzero scale (decimal positions in DDS terms). R510 and earlier of IBM OS/400 describe this data type as an SQL SMALLINT or INT, but with a nonzero scale. The SQL specification and the ODBC specification state that SMALLINT and INT must have a scale of zero. Directly accessing tables with this data type through SQL can result in unpredictable results including data corruption. For more information, see `Binary Fields Reported as Integer Types`.
3.	Commonly used SQL implementations and the ODBC standard do not define an equivalent to the OPEN data type. IBM i Access Client Solutions ODBC drivers map this type to SQL_CHAR. Open fields can contain SBCS or DBCS characters. Although the byte count is fixed, the character count is variable, depending on the number of SBCS versus DBCS characters in the data. ODBC has no such concept, which often leads to truncation errors involving the truncation of trailing blanks. IBM i Access Client Solutions ODBC drivers provide a DBCSNoTruncErr setting that allows an application to specify that truncation errors involving OPEN fields are ignored. Most applications work with OPEN fields when they are careful to size the fields correctly. For new applications, consider making use of a Unicode field (graphic with CCSIC 13488).
4.	The ODBC specification does not define a non-Unicode DBCS data type. IBM i Access Client Solutions maps the DBCS data type to SQL_CHAR. ODBC 2.x has an assumption that byte count and character count are the same (for example, SBCS data with 1 byte per character). ODBC 3.x supports describing a SQL_CHAR with both a character count and byte count; however, some applications do not use this information correctly. Because of these limitations, Access Client Solutions ODBC driver provides a special GRAPHIC setting that controls how graphic types are described. This setting can be configured to allow most applications to use graphic fields. For new applications, consider making use of a Unicode field (graphic with CCSID 13488).
5.	The DECFLOAT type can be returned as either SQL_DOUBLE or SQL_VARCHAR, depending upon a data source setting in the ODBC driver.

[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000001gDAAAY","label":"Data Access-\u003EODBC"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]

Historical Number

25905151

Was this topic helpful?

Document Information

Modified date:
11 April 2025

UID

nas8N1019404

Tips