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.
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:
|
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.
SQL data type | COBOL host variable equivalent | Notes |
---|---|---|
SMALLINT |
|
|
INTEGER |
|
|
DECIMAL(p,s) or NUMERIC(p,s) |
|
p is precision; s is scale. 0≤s≤p≤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) |
|
1≤n≤21 |
DOUBLE PRECISION, DOUBLE or FLOAT (n) |
|
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,
|
1≤n≤255 |
VARCHAR(n) | Varying-length character string. For example,
|
The inner variables must have a level of 49. |
GRAPHIC(n) | Fixed-length graphic string. For example,
|
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,
|
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,
|
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,
|
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,
|
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,
|
n must be at least 19. |
TIMESTAMP(p) p > 0 | Fixed-length character string of length n.
For example, ). |
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,
|
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,
|
The inner variables must have a level of 49. n must be at least 26+p. |
Result set locator |
|
Use this data type only for receiving result sets. Do not use this data type as a column type. |
Table 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 |
|
Use this data type only to manipulate data in BLOB columns. Do not use this data type as a column type. |
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 |
|
Use this data type only to manipulate data in DBCLOB columns. Do not use this data type as a column type. |
BLOB(i) |
|
1≤n≤2147483647 |
CLOB(i) |
|
1≤n≤2147483647 |
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 |
|
Use this data type only to manipulate data in BLOB columns. Do not use this data type as a column type. |
CLOB file reference |
|
Use this data type only to manipulate data in CLOB columns. Do not use this data type as a column type. |
DBCLOB file reference |
|
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 |
|