Equivalent SQL and PL/I data types

When you declare host variables in your PL/I programs, the precompiler uses equivalent SQL data types. When you retrieve data of a particular SQL data type into a host variable, you need to 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 PL/I programs
PL/I host variable data type SQLTYPE of host variable 1 SQLLEN of host variable SQL data type
BIN FIXED(n) 1<=n<=15 500 2 SMALLINT
BIN FIXED(n) 16<=n<=31 496 4 INTEGER
FIXED BIN(63) 492 8 BIGINT
DEC FIXED(p,s) 0<=p<=31 and 0<=s<=p 2 484 p in byte 1, s in byte 2 DECIMAL(p,s)
Start of changeDEC FLOAT (p) where 1 <= p <= 7End of change Start of change996/997End of change Start of change4End of change Start of changeDECFLOAT(16) 6End of change
Start of changeDEC FLOAT (p) where 8 <= p <= 16End of change Start of change996/997End of change Start of change8End of change Start of changeDECFLOAT(16)End of change
Start of changeDEC FLOAT (p) where 17 <= pEnd of change Start of change996/997End of change Start of change16End of change Start of changeDECFLOAT(34)End of change
BIN FLOAT(p) 1<=p<=21 480 4 REAL or FLOAT(n) 1<=n<=21
BIN FLOAT(p) 22<=p<=53 480 8 DOUBLE PRECISION or FLOAT(n) 22<=n<=53
DEC FLOAT(m) 1<=m<=6 480 4 FLOAT (single precision)
DEC FLOAT(m) 7<=m<=16 480 8 FLOAT (double precision)
CHAR(n) 452 n CHAR(n)
CHAR(n) VARYING 1<=n<=255 448 n VARCHAR(n)
CHAR(n) VARYING n>255 456 n VARCHAR(n)
GRAPHIC(n) 468 n GRAPHIC(n)
Start of changeGRAPHIC VARYING(n)End of change Start of change464End of change Start of changenEnd of change Start of changeVARGRAPHIC(n)End of change
SQL TYPE IS BINARY(n), 1<=n<=255 912 n BINARY(n)
SQL TYPE IS VARBINARY(n), 1<=n<=32 704 908 n VARBINARY(n)
SQL TYPE IS RESULT_SET_LOCATOR 972 4 Result set locator 3
SQL TYPE IS TABLE LIKE table-name AS LOCATOR 976 4 Table locator 3
SQL TYPE IS BLOB_LOCATOR 960 4 BLOB locator 3
SQL TYPE IS CLOB_LOCATOR 964 4 CLOB locator 3
SQL TYPE IS DBCLOB_LOCATOR 968 4 DBCLOB locator 3
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 4 412 n DBCLOB(n) 4
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 3
SQL TYPE IS CLOB_FILE 920/921 267 CLOB file reference 3
SQL TYPE IS DBCLOB_FILE 924/925 267 DBCLOB file reference 3
SQL TYPE IS XML AS BLOB_FILE 916/917 267 XML BLOB file reference 3
SQL TYPE IS XML AS CLOB_FILE 920/921 267 XML CLOB file reference 3
SQL TYPE IS XML AS DBCLOB_FILE 924/925 267 XML DBCLOB file reference 3
SQL TYPE IS ROWID 904 40 ROWID
Start of changeWIDECHAR(n)End of change Start of change468End of change Start of changenEnd of change Start of changeGRAPHIC(n) 5End of change
Start of changeWIDECHAR VARYING(n)End of change Start of change464End of change Start of changenEnd of change Start of changeVARGRAPHIC(n) 5End of change

The following table shows equivalent PL/I host variables for each SQL data type. Use this table to determine the PL/I data type for host variables that you define to receive output from the database. For example, if you retrieve TIMESTAMP data, you can define a variable of type CHAR(n).

This table shows direct conversions between SQL data types and PL/I 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. PL/I host variable equivalents that you can use when retrieving data of a particular SQL data type
SQL data type PL/I host variable equivalent Remarks
SMALLINT BIN FIXED(n) 1<=n<=15
INTEGER BIN FIXED(n) 16<=n<=31
BIGINT FIXED BIN(63) 7
DECIMAL(p,s) or NUMERIC(p,s) If p<16:  DEC FIXED(p) or  DEC FIXED(p,s) p is precision; s is scale. 1<=p<=31 and 0<=s<=p

If p>15, the PL/I compiler must support 31-digit decimal variables.
Start of changeDECFLOAT(16)End of change Start of changeDEC FLOAT (p) End of change Start of change1 <= p <= 7End of change
Start of changeDECFLOAT(16)End of change Start of changeDEC FLOAT (p) End of change Start of change8 <= p <= 16End of change
Start of changeDECFLOAT(34)End of change Start of changeDEC FLOAT (p) End of change Start of change17 <= pEnd of change
REAL or FLOAT(n) BIN FLOAT(p) or DEC FLOAT(m) 1<=n<=21, 1<=p<=21, and 1<=m<=6
DOUBLE PRECISION, DOUBLE, or FLOAT(n) BIN FLOAT(p) or DEC FLOAT(m) 22<=n<=53, 22<=p<=53, and 7<=m<=16
CHAR(n) CHAR(n) 1<=n<=255
VARCHAR(n) CHAR(n) VAR  
Start of changeGRAPHIC(n)End of change Start of changeGRAPHIC(n) or WIDECHAR(n) 2End of change Start of changen refers to the number of double-byte characters, not to the number of bytes.End of change
Start of changeVARGRAPHIC(n)End of change Start of changeGRAPHIC(n) VARYING or WIDECHAR(n) VARYINGEnd of change Start of changen refers to the number of double-byte characters, not to the number of bytes.End of change
BINARY(n) SQL TYPE IS BINARY(n) 1<=n<=255
VARBINARY(n) SQL TYPE IS VARBINARY(n) 1<=n<=32 704
DATE CHAR(n) If you are using a date exit routine, that routine determines n; otherwise, n must be at least 10.
TIME CHAR(n) If you are using a time exit routine, that routine determines n. Otherwise, n must be at least 6; to include seconds, n must be at least 8.
TIMESTAMP CHAR(n) n must be at least 19. To include microseconds, n must be 26; if n is less than 26, the microseconds part is truncated.
Start of changeTIMESTAMP(0)End of change Start of changeCHAR(n)End of change Start of changen must be at least 19.End of change
Start of changeTIMESTAMP(p) p > 0End of change Start of changeCHAR(n)End 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 changeCHAR(n) VAREnd of change Start of changen must be at least 25.End of change
Start of changeTIMESTAMP(p) WITH TIME ZONEEnd of change Start of changeCHAR(n) VAREnd of change Start of changen must be at least 26+p.End of change
Result set locator SQL TYPE IS RESULT_SET_LOCATOR Use this data type only for receiving result sets. 3
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. 3
BLOB locator SQL TYPE IS BLOB_LOCATOR Use this data type only to manipulate data in BLOB columns. 3, 6, 7
CLOB locator SQL TYPE IS CLOB_LOCATOR Use this data type only to manipulate data in CLOB columns. 3, 6, 7
DBCLOB locator SQL TYPE IS DBCLOB_LOCATOR Use this data type only to manipulate data in DBCLOB columns. 3, 6, 7
BLOB(n) SQL TYPE IS BLOB(n) 1≤n≤2147483647 6, 7
CLOB(n) SQL TYPE IS CLOB(n) 1≤n≤2147483647 6, 7
DBCLOB(n) SQL TYPE IS DBCLOB(n) n is the number of double-byte characters. 1≤n≤1073741823 5, 7
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 6
BLOB file reference SQL TYPE IS BLOB_FILE Use this data type only to manipulate data in BLOB columns. 3, 6, 7
CLOB file reference SQL TYPE IS CLOB_FILE Use this data type only to manipulate data in CLOB columns. 3, 6, 7
DBCLOB file reference SQL TYPE IS DBCLOB_FILE Use this data type only to manipulate data in DBCLOB columns. 3, 6, 7
XML BLOB file reference SQL TYPE IS XML AS BLOB_FILE Use this data type only to manipulate XML data as BLOB files. 3
XML CLOB file reference SQL TYPE IS XML AS CLOB_FILE Use this data type only to manipulate XML data as CLOB files. 3
XML DBCLOB file reference SQL TYPE IS XML AS DBCLOB_FILE Use this data type only to manipulate XML data as DBCLOB files. 3
ROWID SQL TYPE IS ROWID  
Table notes:

The following notes apply as indicated to Table 1 and Table 2.

  1. If a host variable includes an indicator variable, the SQLTYPE value is the base SQLTYPE value plus 1.
  2. If p=0, DB2 interprets it as DECIMAL(31). For example, DB2 interprets a PL/I data type of DEC FIXED(0,0) to be DECIMAL(31,0), which equates to the SQL data type of DECIMAL(31,0).
  3. Do not use this data type as a column type.
  4. n is the number of double-byte characters.
  5. Start of changeCCSID 1200 is always assigned to WIDECHAR type host var.End of change
  6. Start of changeThe data type conversions can be used only if the DB2 coprocessor is used, and the PL/I compiler options FLOAT(DFP) and ARCH(7) are specified.End of change
  7. Specify the following compiler options when you compile your program: LIMITS(FIXEDBIN(63), FIXEDDEC(31)).