Application Development Guide

Supported SQL Data Types in COBOL

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

Table 33 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 33. SQL Data Types Mapped to COBOL Declarations

SQL Column Type1	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
REAL2 (480 or 481)	01 name USAGE IS COMP-1.	Single-precision floating point
DOUBLE3 (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 variable4 (964 or 965)	01 MY-CLOB-LOCATOR USAGE IS SQL TYPE IS CLOB-LOCATOR.	Identifies CLOB entities residing on the server
CLOB file reference variable4 (920 or 921)	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 variable4 (960 or 961)	01 MY-BLOB-LOCATOR USAGE IS SQL TYPE IS BLOB-LOCATOR.	Identifies BLOB entities residing on the server
BLOB file reference variable4 (916 or 917)	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 variable4 (968 or 969)	01 MY-DBCLOB-LOCATOR USAGE IS SQL TYPE IS DBCLOB-LOCATOR.	Identifies DBCLOB entities residing on the server
DBCLOB file reference variable4 (924 or 925)	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 in COBOL

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.