Additional Data Types

In addition to the data types described in the chapter Data Types as being supported, DB2 supports the DECIMAL data type. This describes a packed-decimal item, with or without a decimal point. In COBOL, such items can be declared either as COMP-3 or as PACKED-DECIMAL.

All additional data types must be declared using SQL syntax of the form: 

>>--level_number--name-+------------+-SQL-+-----------+-->
                       |            |     |           |
                       +-USAGE-+----+     +-TYPE-+----+
                               |    |            |    |
                               +-IS-+            +-IS-+

 >--sql_type--+----------+--><
              |          |
              +-(-size-)-+

where:

level_number is within the range 1 to 48
sql_type is one of the new SQL data types BLOB, CLOB, DBCLOB, BLOB-FILE, CLOB-FILE, DBCLOB-FILE, BLOB-LOCATOR, CLOB-LOCATOR, DBCLOB-LOCATOR or TIMESTAMP. TIMESTAMPs are not new to DB2 V2 and are provided as a convenience by the DB2 ECM.
size may only be specified for BLOBs, CLOBs and DBCLOBs, and is mandatory. It may be qualified with K (Kilobytes), M (Megabytes) or G (Gigabytes).

VALUE clauses are not permitted on the new SQL data types.

Depending on the sql_type specified, the actual data created may be an elementary or group item. The names of elements in the group item are generated automatically.

The table below shows the structure of the data items created using SQL syntax by showing the equivalent native COBOL definition. Note that although the same data is created in each case, the items must be declared using the SQL syntax in order to be recognised as acceptable host variables by the DB2 ECM. (This is because the COBOL definitions are ambiguous: various of the new SQL types, and existing group items which are expanded to individual host variables, are indistinguishable). All previously existing data types continue to be declared using normal COBOL syntax. The only exception to this rule is TIMESTAMP, which may be declared using either form.

SQL syntax Equivalent COBOL syntax 
01  MY-BLOB SQL BLOB(125M).
 
01  MY-BLOB.
   49  MY-BLOB-LENGTH PIC S9(9) COMP-5.
   49  MY-BLOB-DATA   PIC X(131072000).
 
03  A SQL CLOB(3K).
 
03  A.
   49  A-LENGTH PIC S9(9) COMP-5.
   49  A-DATA   PIC X(3072).
 
03  HV SQL DBCLOB(125).
 
03  HV.
   49  HV-LENGTH PIC S9(9) COMP-5.
   49  HV-DATA   PIC G(125).
 
01  B SQL BLOB-LOCATOR.
 
01  B PIC S9(9) COMP-5.
 
01  C SQL CLOB-FILE.
 
01  C.
   49  C-NAME-LENGTH  PIC S9(9) COMP-5.
   49  C-DATA-LENGTH  PIC S9(9) COMP-5.
   49  C-FILE-OPTIONS PIC S9(9) COMP-5.
   49  C-NAME         PIC X(255).
 
01  TS SQL TIMESTAMP.
 
01  TS PIC X(29).
Parent topic: DB2 External Compiler Module
Related information
Data Types