Application Development Guide

Supported SQL Data Types

Certain predefined REXX data types correspond to DB2 column types. Table 24 shows how SQLEXEC and SQLDBS interpret REXX variables in order to convert their contents to DB2 data types.
Note: There is no host variable support for the DATALINK data type in any of the DB2 host languages.

Table 24. 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 quote ('), which has length n after removing the two quote 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 nranges 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⁴
(808 or 809)

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⁴
(804 or 805)

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

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

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⁴
(812 or 813)

DECLARE :var_name LANGUAGE TYPE DBCLOB FILE Descriptor for file containing DBCLOB data

Notes:

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
DOUBLE PRECISION

This is not a column type but a host variable type.

Using Cursors in REXX

When a cursor is declared in REXX, the cursor is associated with a query. The query is associated with a statement name assigned in the PREPARE statement. Any referenced host variables are represented by parameter markers. The following example shows a DECLARE statement associated with a dynamic SELECT statement.

     prep_string = "SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = ?"
     CALL SQLEXEC 'PREPARE S1 FROM :prep_string'; 
     CALL SQLEXEC 'DECLARE C1 CURSOR FOR S1'; 
     CALL SQLEXEC 'OPEN C1 USING :schema_name';

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]