Equivalent SQL and COBOL data types

When you declare host variables in your COBOL 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 COBOL programs
COBOL host variable data type SQLTYPE of host variable1 SQLLEN of host variable SQL data type
COMP-1 480 4 REAL or FLOAT(n) 1≤n≤21
COMP-2 480 8 DOUBLE PRECISION, or FLOAT(n) 22≤n≤53
S9(i)V9(d) COMP-3 or S9(i)V9(d) PACKED-DECIMAL 484 i+d in byte 1, d in byte 2 DECIMAL(i+d,d) or NUMERIC(i+d,d)
S9(i)V9(d) DISPLAY SIGN LEADING SEPARATE 504 i+d in byte 1, d in byte 2 No exact equivalent. Use DECIMAL(i+d,d) or NUMERIC(i+d,d)
S9(i)V9(d) NATIONAL SIGN LEADING SEPARATE 504 i+d in byte 1, d in byte 2 No exact equivalent. Use DECIMAL(i+d,d) or NUMERIC(i+d,d)
S9(4) COMP-4, S9(4) COMP-5, S9(4) COMP, or S9(4) BINARY 500 2 SMALLINT
S9(9) COMP-4, S9(9) COMP-5, S9(9) COMP, or S9(9) BINARY 496 4 INTEGER
S9(18) COMP-4, S9(18) COMP-5, S9(18) COMP, or S9(18) BINARY 492 8 BIGINT
Fixed-length character data 452 n CHAR(n)
Varying-length character data 1≤n≤255 448 n VARCHAR(n)
Varying-length character data m>255 456 m VARCHAR(m)
Fixed-length graphic data 468 m GRAPHIC(m)
Varying-length graphic data 1≤m≤127 464 m VARGRAPHIC(m)
Varying-length graphic data m>127 472 m VARGRAPHIC(m)
SQL TYPE is BINARY(n), 1≤n≤255 912 n BINARY(n)
SQL TYPE is VARBINARY(n), 1≤n≤32704 908 n VARBINARY(n)
SQL TYPE IS RESULT-SET-LOCATOR 972 4 Result set locator2
SQL TYPE IS TABLE LIKE table-name AS LOCATOR 976 4 Table locator2
SQL TYPE IS BLOB-LOCATOR 960 4 BLOB locator2
SQL TYPE IS CLOB-LOCATOR 964 4 CLOB locator2
SQL TYPE IS DBCLOB-LOCATOR 968 4 DBCLOB locator2
USAGE IS SQL TYPE IS BLOB(i) 1≤i≤2147483647 404 i BLOB(i)
USAGE IS SQL TYPE IS CLOB(i) 1≤i≤2147483647 408 i CLOB(i)
USAGE IS SQL TYPE IS DBCLOB(m) 1≤m≤10737418233 412 i DBCLOB(m)3
SQL TYPE IS XML AS BLOB(i) 404 0 XML
SQL TYPE IS XML AS CLOB(i) 408 0 XML
SQL TYPE IS XML AS DBCLOB(i) 412 0 XML
SQL TYPE IS BLOB-FILE 916/917 267 BLOB file reference2
SQL TYPE IS CLOB-FILE 920/921 267 CLOB file reference2
SQL TYPE IS DBCLOB-FILE 924/925 267 DBCLOB file reference2
SQL TYPE IS XML AS BLOB-FILE 916/917 267 XML BLOB file reference2
SQL TYPE IS XML AS CLOB-FILE 920/921 267 XML CLOB file reference2
SQL TYPE IS XML AS DBCLOB-FILE 924/925 267 XML DBCLOB file reference2
SQL TYPE IS ROWID 904 40 ROWID
Notes:
  1. If a host variable includes an indicator variable, the SQLTYPE value is the base SQLTYPE value plus 1.
  2. Do not use this data type as a column type.
  3. m is the number of double-byte characters.

The following table shows equivalent COBOL host variables for each SQL data type. Use this table to determine the COBOL 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 fixed-length character string variable of length n

This table shows direct conversions between SQL data types and COBOL 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. COBOL host variable equivalents that you can use when retrieving data of a particular SQL data type
SQL data type COBOL host variable equivalent Notes
SMALLINT
S9(4) COMP-4,
S9(4) COMP-5,
S9(4) COMP,
or S9(4) BINARY
 
INTEGER
S9(9) COMP-4,
S9(9) COMP-5,
S9(9) COMP,
or S9(9) BINARY
 
DECIMAL(p,s) or NUMERIC(p,s)
S9(p-s)V9(s) COMP-3 or
S9(p-s)V9(s)
 PACKED-DECIMAL
 DISPLAY SIGN
 LEADING SEPARATE
 NATIONAL SIGN
 LEADING SEPARATE
p is precision; s is scale. 0≤sp≤31. If s=0, use S9(p)V or S9(p). If s=p, use SV9(s). If the COBOL compiler does not support 31–digit decimal numbers, no exact equivalent exists. Use COMP-2.
REAL or FLOAT (n)
COMP-1
1≤n≤21
DOUBLE PRECISION, DOUBLE or FLOAT (n)
COMP-2
22≤n≤53
BIGINT S9(18) COMP-4, S9(18) COMP-5, S9(18) COMP, or S9(18) BINARY  
CHAR(n) Fixed-length character string. For example,
01 VAR-NAME PIC X(n).
1≤n≤255
VARCHAR(n) Varying-length character string. For example,
01 VAR-NAME.
   49 VAR-LEN PIC S9(4)
 USAGE BINARY.
   49 VAR-TEXT PIC X(n).
The inner variables must have a level of 49.
GRAPHIC(n) Fixed-length graphic string. For example,
01 VAR-NAME PIC G(n)
   USAGE IS DISPLAY-1.
n refers to the number of double-byte characters, not to the number of bytes. 1≤n≤127
VARGRAPHIC(n) Varying-length graphic string. For example,
01 VAR-NAME.
   49 VAR-LEN PIC S9(4)
 USAGE BINARY.
   49 VAR-TEXT PIC G(n) 
      USAGE IS DISPLAY-1.
n refers to the number of double-byte characters, not to the number of bytes.

The inner variables must have a level of 49.

BINARY(n) SQL TYPE IS BINARY(n) 1≤n≤255
VARBINARY(n) SQL TYPE IS VARBINARY(n) 1≤n≤32704
DATE Fixed-length character string of length n. For example,
01 VAR-NAME PIC X(n).
If you are using a date exit routine, i is determined by that routine. Otherwise, i must be at least 10.
TIME Fixed-length character string of length n. For example,
01 VAR-NAME PIC X(n).
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 Fixed-length character string of length n. For example,
01 VAR-NAME PIC X(n).
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.
TIMESTAMP(0) Fixed-length character string of length n. For example,
01 VAR-NAME PIC X(n).
n must be at least 19.
TIMESTAMP(p) p > 0 Fixed-length character string of length n. For example,
01 VAR-NAME PIC X(n
).
n 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.
TIMESTAMP(0) WITH TIME ZONE Varying-length character string. For example,
01 VAR-NAME.
49 VAR-LEN PIC S9(4) USAGE
BINARY. 49 VAR-TEXT PIC
X(n).
The inner variables must have a level of 49. n must be at least 25.
TIMESTAMP(p) WITH TIME ZONE Varying-length character string. For example,
01 VAR-NAME.
49 VAR-LEN PIC S9(4) USAGE
BINARY. 49 VAR-TEXT PIC
X(n).
The inner variables must have a level of 49. n must be at least 26+p.
Result set locator
SQL TYPE IS
RESULT-SET-LOCATOR
Use this data type only for receiving 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
USAGE IS 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
USAGE IS 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
USAGE IS 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(i)
USAGE IS SQL TYPE IS
BLOB(i)
1≤n≤2147483647
CLOB(i)
USAGE IS SQL TYPE IS
CLOB(i)
1≤n≤2147483647
DBCLOB(i)
USAGE IS SQL TYPE IS
DBCLOB(i)
i is the number of double-byte characters. 1≤n≤1073741823
XML SQL TYPE IS XML AS BLOB(i) 1≤n≤2147483647
XML SQL TYPE IS XML AS CLOB(i) 1≤n≤2147483647
XML SQL TYPE IS XML AS DBCLOB(i) i is the number of double-byte characters. 1≤n≤1073741823
BLOB file reference
USAGE IS 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
USAGE IS 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
USAGE IS 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