Application Development Guide

Supported SQL Data Types

Certain predefined COBOL data types correspond to column types. Only these COBOL data types can be declared as host variables.

Table 22 shows the COBOL equivalent of each column type. When the precompiler finds a host variable declaration, it determines the appropriate SQL type value. The database manager uses this value to convert the data exchanged between the application and itself.

Not every possible data description for host variables is recognized. COBOL data items must be consistent with the ones described in the following table. If you use other data items, an error can result.
Note: There is no host variable support for the DATALINK data type in any of the DB2 host languages.

Table 22. SQL Data Types Mapped to COBOL Declarations

SQL Column Type¹ COBOL Data Type SQL Column Type Description

SMALLINT
(500 or 501)

01 name PIC S9(4) COMP-5. 16-bit signed integer

INTEGER
(496 or 497)

01 name PIC S9(9) COMP-5. 32-bit signed integer

BIGINT
(492 or 493)

01 name PIC S9(18) COMP-5. 64-bit signed integer

DECIMAL(p,s)
(484 or 485)

01 name PIC S9(m)V9(n) COMP-3. Packed decimal

REAL²
(480 or 481)

01 name USAGE IS COMP-1. Single-precision floating point

DOUBLE³
(480 or 481)

01 name USAGE IS COMP-2. Double-precision floating point

CHAR(n)
(452 or 453)

01 name PIC X(n). Fixed-length character string

VARCHAR(n)
(448 or 449)

01 name.
49 length PIC S9(4) COMP-5.
49 name PIC X(n).

1<=n<=32 672

Variable-length character string

LONG VARCHAR
(456 or 457)

01 name.
49 length PIC S9(4) COMP-5.
49 data PIC X(n).

32 673<=n<=32 700

Long variable-length character string

CLOB(n)
(408 or 409)

01 MY-CLOB USAGE IS SQL TYPE IS CLOB(n).

1<=n<=2 147 483 647

Large object variable-length character string

CLOB locator variable⁴
(964 or 965)

01 MY-CLOB-LOCATOR USAGE IS SQL TYPE IS CLOB-LOCATOR. Identifies CLOB entities residing on the server

CLOB file reference variable⁴
(808 or 809)

01 MY-CLOB-FILE USAGE IS SQL TYPE IS CLOB-FILE. Descriptor for file containing CLOB data

BLOB(n)
(404 or 405)

01 MY-BLOB USAGE IS SQL TYPE IS BLOB(n).

1<=n<=2 147 483 647
Large object variable-length binary string

BLOB locator variable⁴
(960 or 961)

01 MY-BLOB-LOCATOR USAGE IS SQL TYPE IS BLOB-LOCATOR. Identifies BLOB entities residing on the server

BLOB file reference variable⁴
(804 or 805)

01 MY-CLOB-FILE USAGE IS SQL TYPE IS CLOB-FILE. Descriptor for file containing CLOB data

DATE
(384 or 385)

01 identifier PIC X(10). 10-byte character string

TIME
(388 or 389)

01 identifier PIC X(8). 8-byte character string

TIMESTAMP
(392 or 393)

01 identifier PIC X(26). 26-byte character string

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

GRAPHIC(n)
(468 or 469)

01 name PIC G(n) DISPLAY-1. Fixed-length double-byte character string

VARGRAPHIC(n)
(464 or 465)

01 name.
49 length PIC S9(4) COMP-5.
49 name PIC G(n) DISPLAY-1.

1<=n<=16 336

Variable length double-byte character string with 2-byte string length indicator

LONG VARGRAPHIC
(472 or 473)

01 name.
49 length PIC S9(4) COMP-5.
49 name PIC G(n) DISPLAY-1.

16 337<=n<=16 350

Variable length double-byte character string with 2-byte string length indicator

DBCLOB(n)
(412 or 413)

01 MY-DBCLOB USAGE IS SQL TYPE IS DBCLOB(n).

1<=n<=1 073 741 823
Large object variable length double-byte character string with 4-byte string length indicator

DBCLOB locator variable⁴
(968 or 969)

01 MY-DBCLOB-LOCATOR USAGE IS SQL TYPE IS DBCLOB-LOCATOR. Identifies DBCLOB entities residing on the server

DBCLOB file reference variable⁴
(812 or 813)

01 MY-DBCLOB-FILE USAGE IS SQL TYPE IS DBCLOB-FILE. Descriptor for file containing DBCLOB data

Notes:

The first number under SQL 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. These are the values that would appear in the SQLTYPE field of the SQLDA for these data types.
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.

The following is a sample SQL declare section with a host variable declared for each supported SQL data type.

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
     * 
       01 age        PIC S9(4) COMP-5. 
       01 divis      PIC S9(9) COMP-5. 
       01 salary     PIC S9(6)V9(3) COMP-3.
       01 bonus      USAGE IS COMP-1. 
       01 wage       USAGE IS COMP-2. 
       01 nm         PIC X(5). 
       01 varchar. 
          49 leng    PIC S9(4) COMP-5. 
          49 strg    PIC X(14). 
       01 longvchar. 
          49 len     PIC S9(4) COMP-5. 
          49 str     PIC X(6027). 
       01 MY-CLOB USAGE IS SQL TYPE IS CLOB(1M). 
       01 MY-CLOB-LOCATOR USAGE IS SQL TYPE IS CLOB-LOCATOR. 
       01 MY-CLOB-FILE USAGE IS SQL TYPE IS CLOB-FILE. 
       01 MY-BLOB USAGE IS SQL TYPE IS BLOB(1M). 
       01 MY-BLOB-LOCATOR USAGE IS SQL TYPE IS BLOB-LOCATOR. 
       01 MY-BLOB-FILE USAGE IS SQL TYPE IS BLOB-FILE. 
       01 MY-DBCLOB USAGE IS SQL TYPE IS DBCLOB(1M). 
       01 MY-DBCLOB-LOCATOR USAGE IS SQL TYPE IS DBCLOB-LOCATOR. 
       01 MY-DBCLOB-FILE USAGE IS SQL TYPE IS DBCLOB-FILE. 
       01 MY-PICTURE PIC G(16000) USAGE IS DISPLAY-1. 
       01 dt         PIC X(10). 
       01 tm         PIC X(8). 
       01 tmstmp     PIC X(26). 
       01 wage-ind   PIC S9(4) COMP-5. 
     * 
     EXEC SQL END DECLARE SECTION END-EXEC.

The following are additional rules for supported COBOL data types:

PIC S9 and COMP-3/COMP-5 are required where shown.
You can use level number 77 instead of 01 for all column types except VARCHAR, LONG VARCHAR, VARGRAPHIC, LONG VARGRAPHIC and all LOB variable types.
Use the following rules when declaring host variables for DECIMAL(p,s) column types. Refer to the following sample:
```
     01 identifier PIC S9(m)V9(n) COMP-3 
```
- Use V to denote the decimal point.
- Values for n and m must be greater than or equal to 1.
- The value for n + m cannot exceed 31.
- The value for s equals the value for n.
- The value for p equals the value for n + m.
- The repetition factors (n) and (m) are optional. The following examples are all valid:
```
     01 identifier PIC S9(3)V COMP-3 
     01 identifier PIC SV9(3) COMP-3 
     01 identifier PIC S9V COMP-3 
     01 identifier PIC SV9 COMP-3 
```
- PACKED-DECIMAL can be used instead of COMP-3.
Arrays are not supported by the COBOL precompiler:

FOR BIT DATA

Certain database columns can be declared FOR BIT DATA. These columns, which generally contain characters, are used to hold binary information. The CHAR(n), VARCHAR, LONG VARCHAR, and BLOB data types are the COBOL host variable types that can contain binary data. Use these data types when working with columns with the FOR BIT DATA attribute.

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

[ DB2 List of Books | Search the DB2 Books ]