Equivalent SQL and assembler data types

When you declare host variables in your assembler programs, the precompiler uses equivalent SQL data types. When you retrieve data of a particular SQL data type into a host variable, ensure that the host variable is of an equivalent data type.

The following table describes the SQL data type and the base SQLTYPE and SQLLEN values that the precompiler uses for host variables in SQL statements.

Table 1. SQL data types, SQLLEN values, and SQLTYPE values that the precompiler uses for host variables in assembler programs
Assembler host variable data type SQLTYPE of host variable1 SQLLEN of host variable SQL data type 
DS HL2
 500 2 
SMALLINT
 
DS FL4
 496 4 
INTEGER
 
DS P'value'
DS PLn'value' or
DS PLn
1<=n<=16
 484 p in byte 1, s in byte 2 
DECIMAL(p,s)
short decimal FLOAT: 
SDFP DC ED
SDFP DC EDL4
SDFP DC EDL4'11.11'
 996 4 
DECFLOAT
long decimal FLOAT: 
LDFP DC DD
LDFP DC DDL8
LDFP DC DDL8'22.22'
 996 8 
DECFLOAT
extended decimal FLOAT: 
EDFP DC LD
EDFP DC LDL16
EDFP DC LDL16'33.33'
 996 16 
DECFLOAT
 
DS EL4
DS EHL4
DS EBL4
 480 4 
REAL or FLOAT (n)
1<=n<=21
 
DS DL8
DS DHL8
DS DBL8
 480 8 
DOUBLE PRECISION,
or FLOAT (n)
22<=n<=53
 
DS FDL8
DS FD
 492 8 
BIGINT
 
SQL TYPE IS BINARY(n)
1<=n<=255
 912 n 
BINARY(n)
 
SQL TYPE IS VARBINARY(n)  or 
SQL TYPE IS BINARY(n) VARYING
1<=n<=32704
 908 n 
VARBINARY(n)
 
DS CLn
1<=n<=255
 452 n 
CHAR(n)
 
DS HL2,CLn
1<=n<=255
 448 n 
VARCHAR(n)
 
DS HL2,CLn
n>255
 456 n 
VARCHAR(n)
 
DS GLm
2<=m<=254
2		 468 n 
GRAPHIC(n)
3		 
DS HL2,GLm
2<=m<=254
2		 464 n 
VARGRAPHIC(n)
3		 
DS HL2,GLm
m>254
2		 472 n 
VARGRAPHIC(n)
3		 
SQL TYPE IS RESULT_SET_LOCATOR
972 4 
Result set locator4,5
 
SQL TYPE IS
TABLE LIKE 
table-name
AS LOCATOR
 976 4 
Table locator4
 
SQL TYPE IS
BLOB_LOCATOR
 960 4 
BLOB locator4
 
SQL TYPE IS
CLOB_LOCATOR
 964 4 
CLOB locator4
 
SQL TYPE IS
DBCLOB_LOCATOR
 968 4 
DBCLOB locator4
 
SQL TYPE IS
BLOB(n)
1≤n≤2147483647
 404 n 
BLOB(n)
 
SQL TYPE IS
CLOB(n)
1≤n≤2147483647
 408 n 
CLOB(n)
 
SQL TYPE IS
DBCLOB(n)
1≤n≤1073741823
 412 n 
DBCLOB(n)
3		 
SQL TYPE IS XML AS BLOB(n)
 404 0 
XML
 
SQL TYPE IS XML AS CLOB(n)
 408 0 
XML
 
SQL TYPE IS XML AS DBCLOB(n)
 412 0 
XML
 
SQL TYPE IS BLOB_FILE
 916/917 267 
BLOB file reference 4
 
SQL TYPE IS CLOB_FILE
 920/921 267 
CLOB file reference 4
 
SQL TYPE IS DBCLOB_FILE
 924/925 267 
DBCLOB file reference 4
 
SQL TYPE IS XML AS BLOB_FILE
 916/917 267 
XML BLOB file reference 4
 
SQL TYPE IS XML AS CLOB_FILE
 920/921 267 
XML CLOB file reference 4
 
SQL TYPE IS XML AS DBCLOB_FILE
 924/925 267 
XML DBCLOB file reference 4
 
SQL TYPE IS ROWID
 904 40 
ROWIDnote 5
Notes:
  1. If a host variable includes an indicator variable, the SQLTYPE value is the base SQLTYPE value plus 1.
  2. m is the number of bytes.
  3. n is the number of double-byte characters.
  4. This data type cannot be used as a column type.
  5. To be compatible with previous releases, result set locator host variables may be declared as fullword integers (FL4), but the method shown is the preferred syntax.

The following table shows equivalent assembler host variables for each SQL data type. Use this table to determine the assembler data type for host variables that you define to receive output from the database. For example, if you retrieve TIMESTAMP data, you can define variable DS CLn.

This table shows direct conversions between SQL data types and assembler data types. However, a number of SQL data types are compatible. When you do assignments or comparisons of data that have compatible data types, DB2® converts those compatible data types.

Table 2. Assembler host variable equivalents that you can use when retrieving data of a particular SQL data type
SQL data type Assembler host variable equivalent Notes
SMALLINT DS HL2  
INTEGER DS F  
BIGINT DS FD OR DS FDL8 DS FDL8 requires High Level Assembler (HLASM), Release 4 or later.
DECIMAL(p,s) or NUMERIC(p,s) DS P'value' DS PLn'value' DS PLn

p is precision; s is scale. 1<=p<=31 and 0<=s<=p. 1<=n<=16. value is a literal value that includes a decimal point. You must use Ln, value, or both. Using only value is recommended.

Precision: If you use Ln, it is 2n-1; otherwise, it is the number of digits in value. Scale: If you use value, it is the number of digits to the right of the decimal point; otherwise, it is 0.

For efficient use of indexes: Use value. If p is even, do not use Ln and be sure the precision of value is p and the scale of value is s. If p is odd, you can use Ln (although it is not advised), but you must choose n so that 2n-1=p, and value so that the scale is s. Include a decimal point in value, even when the scale of value is 0.
REAL or FLOAT(n) DS EL4 DS EHL4 DS EBL41 1<=n<=21
DOUBLE PRECISION, DOUBLE, or FLOAT(n) DS DL8 DS DHL8 DS DBL81 22<=n<=53
DECFLOAT DC EDL4 DC DDL8 DC LDL16  
CHAR(n) DS CLn 1<=n<=255
VARCHAR(n) DS HL2,CLn  
GRAPHIC(n) DS GLm m is expressed in bytes. n is the number of double-byte characters. 1<=n<=127
VARGRAPHIC(n) DS HL2,GLx DS HL2'm',GLx'<value>' x and m are expressed in bytes. n is the number of double-byte characters. < and > represent shift-out and shift-in characters.
BINARY(n)
Format 1:
variable-name--DS--X--Ln
Format 2:
SQL TYPE IS BINARY(n)
 1<=n<=255
VARBINARY(n)
Format 1:
variable-name--DS--H--L2--,--X--Ln
Format 2:
SQL TYPE IS VARBINARY(n) or SQL TYPE IS BINARY(n) VARYING
 1<=n<=32704
DATE DS CLn If you are using a date exit routine, n is determined by that routine; otherwise, n must be at least 10.
TIME DS CLn If you are using a time exit routine, n is determined by that routine. Otherwise, n must be at least 6; to include seconds, n must be at least 8.
TIMESTAMP DS CLn n must be at least 19. To include microseconds, n must be 26; if n is less than 26, truncation occurs on the microseconds part.
Start of changeTIMESTAMP(0) End of change Start of changeDS CLnEnd of change Start of changen must be at least 19.End of change
Start of changeTIMESTAMP(p) p > 0End of change Start of changeDS CLnEnd of change Start of changen must be at least 19. To include fractional seconds, n must be 20+x where x is the number of fractional seconds to include; if x is less than p, truncation occurs on the fractional seconds part.End of change
Start of changeTIMESTAMP(0) WITH TIME ZONEEnd of change Start of changeDS HL2,CLnEnd of change Start of changen must be at least 25.End of change
Start of changeTIMESTAMP(p) WITH TIME ZONE p > 0End of change Start of changeDS HL2,CLnEnd of change Start of changen must be at least 26+p.End of change
Result set locator DS F Use this data type only to receive result sets. Do not use this data type as a column type.
Table locator SQL TYPE IS TABLE LIKE table-name AS LOCATOR Use this data type only in a user-defined function or stored procedure to receive rows of a transition table. Do not use this data type as a column type.
BLOB locator SQL TYPE IS BLOB_LOCATOR Use this data type only to manipulate data in BLOB columns. Do not use this data type as a column type.
CLOB locator SQL TYPE IS CLOB_LOCATOR Use this data type only to manipulate data in CLOB columns. Do not use this data type as a column type.
DBCLOB locator SQL TYPE IS DBCLOB_LOCATOR Use this data type only to manipulate data in DBCLOB columns. Do not use this data type as a column type.
BLOB(n) SQL TYPE IS BLOB(n) 1≤n≤2147483647
CLOB(n) SQL TYPE IS CLOB(n) 1≤n≤2147483647
DBCLOB(n) SQL TYPE IS DBCLOB(n) n is the number of double-byte characters. 1≤n≤1073741823
XML SQL TYPE IS XML AS BLOB(n) 1≤n≤2147483647
XML SQL TYPE IS XML AS CLOB(n) 1≤n≤2147483647
XML SQL TYPE IS XML AS DBCLOB(n) n is the number of double-byte characters. 1≤n≤1073741823
BLOB file reference SQL TYPE IS BLOB_FILE Use this data type only to manipulate data in BLOB columns. Do not use this data type as a column type.
CLOB file reference SQL TYPE IS CLOB_FILE Use this data type only to manipulate data in CLOB columns. Do not use this data type as a column type.
DBCLOB file reference SQL TYPE IS DBCLOB_FILE Use this data type only to manipulate data in DBCLOB columns. Do not use this data type as a column type.
XML BLOB file reference SQL TYPE IS XML AS BLOB_FILE Use this data type only to manipulate XML data as BLOB files. Do not use this data type as a column type.
XML CLOB file reference SQL TYPE IS XML AS CLOB_FILE Use this data type only to manipulate XML data as CLOB files. Do not use this data type as a column type.
XML DBCLOB file reference SQL TYPE IS XML AS DBCLOB_FILE Use this data type only to manipulate XML data as DBCLOB files. Do not use this data type as a column type.
ROWID SQL TYPE IS ROWID  
Notes:
  1. Although stored procedures and user-defined functions can use IEEE floating-point host variables, you cannot declare a user-defined function or stored procedure parameter as IEEE.