DB2 Version 9.7 for Linux, UNIX, and Windows

Supported SQL data types in REXX embedded SQL applications

Certain predefined REXX data types correspond to DB2® database column types. Only these REXX data types can be declared as host variables. The following table shows how SQLEXEC and SQLDBS interpret REXX variables in order to convert their contents to DB2 data types.

Table 1. SQL Column Types Mapped to REXX Declarations
SQL Column Type¹	REXX Data Type	SQL Column Type Description
SMALLINT (500 or 501)	A number without a decimal point ranging from -32 768 to 32 767	16-bit signed integer
INTEGER (496 or 497)	A number without a decimal point ranging from -2 147 483 648 to 2 147 483 647	32-bit signed integer
REAL² (480 or 481)	A number in scientific notation ranging from -3.40282346 x 10³⁸ to 3.40282346 x 10³⁸	Single-precision floating point
DOUBLE³ (480 or 481)	A number in scientific notation ranging from -1.79769313 x 10³⁰⁸ to 1.79769313 x 10³⁰⁸	Double-precision floating point
DECIMAL(p,s) (484 or 485)	A number with a decimal point	Packed decimal
CHAR(n) (452 or 453)	A string with a leading and trailing quotation mark ('), which has length n after removing the two quotation marks A string of length n with any non-numeric characters, other than leading and trailing blanks or the E in scientific notation	Fixed-length character string of length n where n is from 1 to 254
VARCHAR(n) (448 or 449)	Equivalent to CHAR(n)	Variable-length character string of length n, where n ranges from 1 to 4000
LONG VARCHAR⁵ (456 or 457)	Equivalent to CHAR(n)	Variable-length character string of length n, where n ranges from 1 to 32 700
CLOB(n) (408 or 409)	Equivalent to CHAR(n)	Large object variable-length character string of length n, where n ranges from 1 to 2 147 483 647
CLOB locator variable⁴ (964 or 965)	DECLARE :var_name LANGUAGE TYPE CLOB LOCATOR	Identifies CLOB entities residing on the server
CLOB file reference variable⁴ (920 or 921)	DECLARE :var_name LANGUAGE TYPE CLOB FILE	Descriptor for file containing CLOB data
BLOB(n) (404 or 405)	A string with a leading and trailing apostrophe, preceded by BIN, containing n characters after removing the preceding BIN and the two apostrophes.	Large object variable-length binary string of length n, where n ranges from 1 to 2 147 483 647
BLOB locator variable⁴ (960 or 961)	DECLARE :var_name LANGUAGE TYPE BLOB LOCATOR	Identifies BLOB entities on the server
BLOB file reference variable⁴ (916 or 917)	DECLARE :var_name LANGUAGE TYPE BLOB FILE	Descriptor for the file containing BLOB data
DATE (384 or 385)	Equivalent to CHAR(10)	10-byte character string
TIME (388 or 389)	Equivalent to CHAR(8)	8-byte character string
TIMESTAMP (392 or 393)	Equivalent to CHAR(26)	26-byte character string
XML (988 or 989)	SQL_TYP_XML	There is no XML support for REXX; applications are able to get the describe type back but will not be able to make use of it.

The following data types are only available in the DBCS environment.

Table 2. SQL Column Types Mapped to REXX Declarations
SQL Column Type¹	REXX Data Type	SQL Column Type Description
GRAPHIC(n) (468 or 469)	A string with a leading and trailing apostrophe preceded by a G or N, containing n DBCS characters after removing the preceding character and the two apostrophes	Fixed-length graphic string of length n, where n is from 1 to 127
VARGRAPHIC(n) (464 or 465)	Equivalent to GRAPHIC(n)	Variable-length graphic string of length n, where n ranges from 1 to 2000
LONG VARGRAPHIC⁵ (472 or 473)	Equivalent to GRAPHIC(n)	Long variable-length graphic string of length n, where n ranges from 1 to 16 350
DBCLOB(n) (412 or 413)	Equivalent to GRAPHIC(n)	Large object variable-length graphic string of length n, where n ranges from 1 to 1 073 741 823
DBCLOB locator variable⁴ (968 or 969)	DECLARE :var_name LANGUAGE TYPE DBCLOB LOCATOR	Identifies DBCLOB entities residing on the server
DBCLOB file reference variable⁴ (924 or 925)	DECLARE :var_name LANGUAGE TYPE DBCLOB FILE	Descriptor for file containing DBCLOB data
Note: The first number under Column Type indicates that an indicator variable is not provided, and the second number indicates that an indicator variable is provided. An indicator variable is needed to indicate NULL values, or to hold the length of a truncated string. FLOAT(n) where 0 < n < 25 is a synonym for REAL. The difference between REAL and DOUBLE in the SQLDA is the length value (4 or 8). The following SQL types are synonyms for DOUBLE: FLOAT FLOAT(n) where 24 < n < 54 is a synonym for DOUBLE. DOUBLE PRECISION This is not a column type but a host variable type. The LONG VARCHAR and LONG VARGRAPHIC data types are deprecated, not recommended, and might be removed in a future release. Use the CLOB or DBCLOB data type instead.