MBDT and DBMS Data Types

The DSNTIAUL, DSNTEP2, and DSNUTILB batch utilities use ODBC, and therefore ODBC data types, to communicate with a DBMS. The following table shows the supported DBMS data types these utilities support. Footnotes provide additional information and/or restrictions.

ODBC Data Types HCOSS (DB2 for z/OS) SQL Server DB2 LUW Oracle PostgreSQL
SQL_CHAR CHAR CHAR CHAR CHAR CHAR
SQL_LONG_VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR2 VARCHAR
SQL_WCHAR GRAPHIC NCHAR GRAPHIC NCHAR CHAR
SQL_WVARCHAR VARGRAPHIC NVARCHAR VARGRAPHIC NVARCHAR2 VARCHAR
SQL_BIGINT BIGINT BIGINT BIGINT NUMBER(19,0) BIGINT
SQL_INTEGER INT INT INT NUMBER(10,0) INT
SQL_SMALLINT SMALLINT SMALLINT SMALLINT NUMBER(5) SMALLINT
SQL_TINYINT SMALLINT 1 TINYINT 1 SMALLINT 1 NUMBER(3) 1 SMALLINT 1
SQL_DOUBLE FLOAT FLOAT(53) FLOAT BINARY_DOUBLE DOUBLE PRECISION
SQL_REAL REAL REAL REAL BINARY_FLOAT REAL
SQL_DECIMAL(p,s) DECIMAL(p,s) DECIMAL(p,s) DECIMAL(p,s) NUMBER(p,s) DECIMAL(p,s) 2
SQL_NUMERIC(p,s) NUMERIC(p,s) NUMERIC(p,s) NUMERIC(p,s) NUMBER(p,s) NUMERIC(p,s) 2
SQL_BIT CHAR(1) 1 BIT 1 CHAR(1) 1 NUMBER(3) 1 BOOLEAN 1
SQL_GUID CHAR(36) 1 GUID 1 CHAR(36) 1 CHAR(36) 1 CHAR(36) 1
SQL_BINARY BINARY BINARY CHAR FOR BIT DATA RAW 1 BYTEA 1
SQL_VARBINARY VARBINARY VARBINARY VARCHAR FOR BIT DATA RAW 1 BYTEA 1
SQL_LONGVARBINARY BLOB 4 BLOB 4 BLOB 4 LONG RAW 4 BYTEA 4
SQL_WLONGVARCHAR CLOB 4 CLOB 4 CLOB 4 LONG 4 TEXT 4
SQL_DATE DATE DATE DATE DATE 3 DATE
SQL_TYPE_TIME TIME TIME TIME CHAR(15) 1 TIME
SQL_TIMESTAMP TIMESTAMP DATETIME2(6) TIMESTAMP DATE 3 TIMESTAMP

1 Supported in DSNTIAUL, DSNTEP2, and DSNUTILB (LOAD and UNLOAD) with cited equivalent data types

  • For PostgreSQL data type BYTEA, the default size is 255, and can be expanded (DSNTEP2 and DSNUTILB only) by resetting the ODBC connection VARCHAR(MAX) size, to a larger number
  • For Oracle data type RAW, the maximum width is 200
  • For SQL Server data type TINYINT, OpenESQL maps it to SMALLINT
  • For SQL Server data type BIT, OpenESQL maps it to TINY (one byte)
  • For SQL Server data type GUID, OpenESQL maps it to CHAR (36)
  • For PostgreSQL data type BOOLEAN, OpenESQL maps it to VARCHAR(5)

2 PostgreSQL data types DECIMAL and NUMERIC are supported in DSNTIAUL, DSNTEP2, and DSNUTILB (LOAD and UNLOAD) where the supported range is s <= p <= 38.

3 Oracle DATE data type has both date and time, and is supported in DSNTIAUL, DSNTEP2, and DSNUTILB (LOAD and UNLOAD)

4 Unsupported in DSNTIAUL, DSNTEP2, and DSNUTILB (LOAD and UNLOAD)

Parent topic: Reference