Host variables in COBOL

In COBOL programs, you can specify numeric, character, graphic, binary, LOB, XML, and ROWID host variables. You can also specify result set and table locators and LOB and XML file reference variables.

Restrictions:
  • Only some of the valid COBOL declarations are valid host variable declarations. If the declaration for a variable is not valid, any SQL statement that references the variable might result in the message UNDECLARED HOST VARIABLE.
  • You can not use locators as column types.
    The following locator data types are COBOL data types and SQL data types:
    • Result set locator
    • Table locator
    • LOB locators
    • LOB file reference variables
  • One or more REDEFINES entries can follow any level 77 data description entry. However, you cannot use the names in these entries in SQL statements. Entries with the name FILLER are ignored.
Recommendations:
  • Be careful of overflow. For example, suppose that you retrieve an INTEGER column value into a PICTURE S9(4) host variable and the column value is larger than 32767 or smaller than -32768. You get an overflow warning or an error, depending on whether you specify an indicator variable.
  • Be careful of truncation. For example, if you retrieve an 80-character CHAR column value into a PICTURE X(70) host variable, the rightmost 10 characters of the retrieved string are truncated. Retrieving a double precision floating-point or decimal column value into a PIC S9(8) COMP host variable removes any fractional part of the value. Similarly, retrieving a column value with DECIMAL data type into a COBOL decimal variable with a lower precision might truncate the value.
  • If your varying-length string host variables receive values whose length is greater than 9999 bytes, compile the applications in which you use those host variables with the option TRUNC(BIN). TRUNC(BIN) lets the length field for the string receive a value of up to 32767 bytes.

Numeric host variables

You can specify the following forms of numeric host variables:

  • Floating-point numbers
  • Integers and small integers
  • Decimal numbers

The following diagram shows the syntax for declaring floating-point or real host variables.

>>-+-01----------+--variable-name--+---------------+------------>
   +-77----------+                 |        .-IS-. |   
   |         (1) |                 '-USAGE--+----+-'   
   '-level-1-----'                                     

                     (2)     
>--+-COMPUTATIONAL-1-----+-------------------------------------->
   +-COMP-1--------------+   
   |                 (3) |   
   +-COMPUTATIONAL-2-----+   
   '-COMP-2--------------'   

>--+---------------------------------+-- . --------------------><
   |        .-IS-.                   |        
   '-VALUE--+----+--numeric-constant-'        
Notes:
  1. level-1 indicates a COBOL level between 2 and 48.
  2. COMPUTATIONAL-1 and COMP-1 are equivalent.
  3. COMPUTATIONAL-2 and COMP-2 are equivalent.

The following diagram shows the syntax for declaring integer and small integer host variables.

                                                .-IS-.   
>>-+-01----------+--variable-name--+-PICTURE-+--+----+---------->
   +-77----------+                 '-PIC-----'           
   |         (1) |                                       
   '-level-1-----'                                       

                                               (2)              
>--+-S9(4)------+--+---------------+--+-BINARY--------------+--->
   +-S9999------+  |        .-IS-. |  +-COMPUTATIONAL-4-----+   
   +-S9(9)------+  '-USAGE--+----+-'  +-COMP-4--------------+   
   +-S999999999-+                     |                 (3) |   
   '-S9(18)-----'                     +-COMPUTATIONAL-5-----+   
                                      +-COMP-5--------------+   
                                      +-COMPUTATIONAL-------+   
                                      '-COMP----------------'   

                                            (4)   
>--+---------------------------------+-- . --------------------><
   |        .-IS-.                   |            
   '-VALUE--+----+--numeric-constant-'            
Notes:
  1. level-1 indicates a COBOL level between 2 and 48.
  2. The COBOL binary integer data types BINARY, COMPUTATIONAL, COMP, COMPUTATIONAL-4, and COMP-4 are equivalent.
  3. COMPUTATIONAL-5 (and COMP-5) are equivalent to the other COBOL binary integer data types if you compile the other data types with TRUNC(BIN).
  4. Any specification for scale is ignored.

The following diagram shows the syntax for declaring decimal host variables.

>>-+-01----------+--variable-name--+-PICTURE-+------------------>
   +-77----------+                 '-PIC-----'   
   |         (1) |                               
   '-level-1-----'                               

   .-IS-.                 (2)                      
>--+----+--picture-string------+---------------+---------------->
                               |        .-IS-. |   
                               '-USAGE--+----+-'   

                      (3)                                            
>--+-+-PACKED-DECIMAL-----+--------------------------------------+-->
   | +-COMPUTATIONAL-3----+                                      |   
   | '-COMP-3-------------'                                      |   
   |                     .-IS-.                    .-CHARACTER-. |   
   '-+-DISPLAY--+--SIGN--+----+--LEADING SEPARATE--+-----------+-'   
     '-NATIONAL-'                                                    

>--+---------------------------------+-- . --------------------><
   |        .-IS-.                   |        
   '-VALUE--+----+--numeric-constant-'        
Notes:
  1. level-1 indicates a COBOL level between 2 and 48.
  2. The picture-string that is associated with SIGN LEADING SEPARATE must have the form S9(i)V9(d) (or S9...9V9...9, with i and d instances of 9 or S9...9V with i instances of 9).
  3. PACKED-DECIMAL, COMPUTATIONAL-3, and COMP-3 are equivalent. The picture-string that is that is associated with these types must have the form S9(i)V9(d) (or S9...9V9...9, with i and d instances of 9) or S9(i)V.

In COBOL, you declare the SMALLINT and INTEGER data types as a number of decimal digits. DB2® uses the full size of the integers (in a way that is similar to processing with the TRUNC(BIN) compiler option) and can place larger values in the host variable than would be allowed in the specified number of digits in the COBOL declaration. If you compile with TRUNC(OPT) or TRUNC(STD), ensure that the size of numbers in your application is within the declared number of digits.

For small integers that can exceed 9999, use S9(4) COMP-5 or compile with TRUNC(BIN). For large integers that can exceed 999 999 999, use S9(10) COMP-3 to obtain the decimal data type. If you use COBOL for integers that exceed the COBOL PICTURE, specify the column as decimal to ensure that the data types match and perform well.

If you are using a COBOL compiler that does not support decimal numbers of more than 18 digits, use one of the following data types to hold values of greater than 18 digits:
  • A decimal variable with a precision less than or equal to 18, if the actual data values fit. If you retrieve a decimal value into a decimal variable with a scale that is less than the source column in the database, the fractional part of the value might be truncated.
  • An integer or a floating-point variable, which converts the value. If you use an integer variable, you lose the fractional part of the number. If the decimal number might exceed the maximum value for an integer or if you want to preserve a fractional value, use a floating-point variable. Floating-point numbers are approximations of real numbers. Therefore, when you assign a decimal number to a floating-point variable, the result might be different from the original number.
  • A character-string host variable. Use the CHAR function to retrieve a decimal value into it.
Restriction: The SQL data type DECFLOAT has no equivalent in COBOL.

Character host variables

You can specify the following forms of character host variables:

  • Fixed-length strings
  • Varying-length strings
  • CLOBs

The following diagrams show the syntax for forms other than CLOBs.

The following diagram shows the syntax for declaring fixed-length character host variables.

>>-+-01----------+--variable-name--+-PICTURE-+------------------>
   +-77----------+                 '-PIC-----'   
   |         (1) |                               
   '-level-1-----'                               

   .-IS-.                 (2)                                   
>--+----+--picture-string------+----------------------------+--->
                               '-+---------------+--DISPLAY-'   
                                 |        .-IS-. |              
                                 '-USAGE--+----+-'              

>--+-----------------------------------+-- . ------------------><
   |        .-IS-.                     |        
   '-VALUE--+----+--character-constant-'        
Notes:
  1. level-1 indicates a COBOL level between 2 and 48.
  2. The picture-string that is associated with these forms must be X(m) (or XX…X, with m instances of X), where m is up to COBOL's limitation. However, the maximum length of the CHAR data type (fixed-length character string) in DB2 is 255 bytes.

The following diagrams show the syntax for declaring varying-length character host variables.

>>-+-01----------+--variable-name-- . -------------------------><
   |         (1) |                       
   '-level-1-----'                       
Notes:
  1. level-1 indicates a COBOL level between 2 and 48.
      (1)        (2)               .-IS-.          (3)     
>>-49------var-1------+-PICTURE-+--+----+--+-S9(4)-----+-------->
                      '-PIC-----'          '-S9999-----'   

>--+---------------+--+-BINARY----------+----------------------->
   |        .-IS-. |  +-COMPUTATIONAL-4-+   
   '-USAGE--+----+-'  +-COMP-4----------+   
                      +-COMPUTATIONAL-5-+   
                      +-COMP-5----------+   
                      +-COMPUTATIONAL---+   
                      '-COMP------------'   

>--+---------------------------------+-- . --------------------><
   |        .-IS-.                   |        
   '-VALUE--+----+--numeric-constant-'        
Notes:
  1. You cannot use an intervening REDEFINE at level 49.
  2. You cannot directly reference var-1 as a host variable.
  3. DB2 uses the full length of the S9(4) BINARY variable even though COBOL with TRUNC(STD) recognizes values up to only 9999. This behavior can cause data truncation errors when COBOL statements execute and might effectively limit the maximum length of variable-length character strings to 9999. Consider using the TRUNC(BIN) compiler option or USAGE COMP-5 to avoid data truncation.
      (1)        (2)               .-IS-.                 (3)   
>>-49------var-2------+-PICTURE-+--+----+--picture-string------->
                      '-PIC-----'                               

>--+----------------------------+------------------------------->
   '-+---------------+--DISPLAY-'   
     |        .-IS-. |              
     '-USAGE--+----+-'              

>--+-----------------------------------+-- . ------------------><
   |        .-IS-.                     |        
   '-VALUE--+----+--character-constant-'        
Notes:
  1. You cannot use an intervening REDEFINE at level 49.
  2. You cannot directly reference var-2 as a host variable.
  3. For fixed-length strings, the picture-string must be X(m) (or XX, with m instances of X), where mis up to COBOL's limitation. However, the maximum length of the VARCHAR data type in DB2 varies depending on the data page size.

Graphic character host variables

You can specify the following forms of graphic host variables:

  • Fixed-length strings
  • Varying-length strings
  • DBCLOBs

The following diagrams show the syntax for forms other than DBCLOBs.

The following diagram shows the syntax for declaring fixed-length graphic host variables.

>>-+-01----------+--variable-name--+-PICTURE-+------------------>
   +-77----------+                 '-PIC-----'   
   |         (1) |                               
   '-level-1-----'                               

   .-IS-.                 (2)   
>--+----+--picture-string--------------------------------------->

>--+---------------+--+-DISPLAY-1----+--+---------------------------------+-->
   |        .-IS-. |  |          (3) |  |        .-IS-.                   |   
   '-USAGE--+----+-'  '-NATIONAL-----'  '-VALUE--+----+--graphic-constant-'   

>-- . ---------------------------------------------------------><
Notes:
  1. level-1 indicates a COBOL level between 2 and 48.
  2. For fixed-length strings, the picture-string is G(m) or N(m) (or, m instances of GG...G or NN...N), where m is up to COBOL's limitation. However, the maximum length of the GRAPHIC data type (fixed-length graphic string) in DB2 is 127 double-bytes.
  3. Use USAGE NATIONAL only for Unicode UTF-16 data. In the picture-string for USAGE NATIONAL, you must use N in place of G. USAGE NATIONAL is supported only by the DB2 coprocessor.

The following diagrams show the syntax for declaring varying-length graphic host variables.

>>-+-01----------+--variable-name-- . -------------------------><
   |         (1) |                       
   '-level-1-----'                       
Notes:
  1. level-1 indicates a COBOL level between 2 and 48.
             (1)               .-IS-.          (2)     
>>-49--var-1------+-PICTURE-+--+----+--+-S9(4)-----+------------>
                  '-PIC-----'          '-S9999-----'   

>--+---------------+--+-BINARY----------+----------------------->
   |        .-IS-. |  +-COMPUTATIONAL-4-+   
   '-USAGE--+----+-'  +-COMP-4----------+   
                      +-COMPUTATIONAL-5-+   
                      +-COMP-5----------+   
                      +-COMPUTATIONAL---+   
                      '-COMP------------'   

>--+---------------------------------+-- . --------------------><
   |        .-IS-.                   |        
   '-VALUE--+----+--numeric-constant-'        
Notes:
  1. You cannot directly reference var-1 as a host variable.
  2. DB2 uses the full length of the S9(4) BINARY variable even though COBOL with TRUNC(STD) recognizes values up to only 9999. This behavior can cause data truncation errors when COBOL statements execute and might effectively limit the maximum length of variable-length character strings to 9999. Consider using the TRUNC(BIN) compiler option or USAGE COMP-5 to avoid data truncation.
             (1)               .-IS-.                 (2)   
>>-49--var-2------+-PICTURE-+--+----+--picture-string----------->
                  '-PIC-----'                               

>--+---------------+--+-DISPLAY-1----+-------------------------->
   |        .-IS-. |  |          (3) |   
   '-USAGE--+----+-'  '-NATIONAL-----'   

>--+---------------------------------+-- . --------------------><
   |        .-IS-.                   |        
   '-VALUE--+----+--graphic-constant-'        
Notes:
  1. You cannot directly reference var-2 as a host variable.
  2. For fixed-length strings, the picture-string is G(m) or N(m) (or, m instances of GG...G or NN...N), where m is up to COBOL's limitation. However, the maximum length of the VARGRAPHIC data type in DB2 varies depending on the data page size.
  3. Use USAGE NATIONAL only for Unicode UTF-16 data. In the picture-string for USAGE NATIONAL, you must use N in place of G. USAGE NATIONAL is supported only by the DB2 coprocessor.

Binary host variables

You can specify the following forms of binary host variables:

  • Fixed-length strings
  • Varying-length strings
  • BLOBs

The following diagram shows the syntax for declaring BINARY and VARBINARY host variables. 

                               .-IS-.     
                      .-USAGE--+----+-.   
>>-01--variable-name--+---------------+------------------------->

                                              (1)           
>--SQL TYPE IS--+-BINARY---------+--(--length------)-- . ------><
                +-VARBINARY------+                          
                '-BINARY VARYING-'                          
Notes:
  1. For BINARY host variables, the length must be in the range from 1 to 255. For VARBINARY host variables, the length must be in the range from 1 to 32 704.

COBOL does not have variables that correspond to the SQL binary types BINARY and VARBINARY. To create host variables that can be used with these data types, use the SQL TYPE IS clause. The SQL precompiler replaces this declaration with a COBOL language structure in the output source member.

When you reference a BINARY or VARBINARY host variable in an SQL statement, you must use the variable that you specify in the SQL TYPE declaration. When you reference the host variable in a host language statement, you must use the variable that DB2 generates.

Examples of binary variable declarations: The following table shows examples of variables that DB2 generates when you declare binary host variables.
Table 1. Examples of BINARY and VARBINARY variable declarations for COBOL
Variable declaration that you include in your COBOL program Corresponding variable that DB2 generates in the output source member 
01 BIN-VAR USAGE IS SQL TYPE IS BINARY(10).
 
01 BIN-VAR PIC X(10).
 
01 VBIN-VAR USAGE IS SQL TYPE IS VARBINARY(10).
 
01 VBIN-VAR.
  49 VBIN-VAR-LEN PIC S9(4) USAGE BINARY.
  49 VBIN-VAR-TEXT PIC X(10).

Result set locators

The following diagram shows the syntax for declaring result set locators.

>>-01--variable-name--+---------------+--SQL TYPE IS--RESULT-SET-LOCATOR--VARYING--.-><
                      |        .-IS-. |                                                
                      '-USAGE--+----+-'                                                

Table Locators

The following diagram shows the syntax for declaring table locators. 

>>-+-01----------+--variable-name--+---------------+------------>
   |         (1) |                 |        .-IS-. |   
   '-level-1-----'                 '-USAGE--+----+-'   

>--SQL TYPE IS--TABLE LIKE--table-name--AS LOCATOR--.----------><
Notes:
  1. level-1 indicates a COBOL level between 2 and 48.

LOB variables and file reference variables

The following diagram shows the syntax for declaring BLOB, CLOB, and DBCLOB variables and file reference variables. 

>>-+-01------+--variable-name--+---------------+--SQL TYPE IS--->
   '-level-1-'                 |        .-IS-. |                
                               '-USAGE--+----+-'                

>--+-+-+-BINARY LARGE OBJECT-+----+--(--length--+---+--)-+--.--><
   | | '-BLOB----------------'    |             +-K-+    |      
   | +-+-CHARACTER LARGE OBJECT-+-+             +-M-+    |      
   | | +-CHAR LARGE OBJECT------+ |             '-G-'    |      
   | | '-CLOB-------------------' |                      |      
   | '-DBCLOB---------------------'                      |      
   +-+-BLOB-LOCATOR---+----------------------------------+      
   | +-CLOB-LOCATOR---+                                  |      
   | '-DBCLOB-LOCATOR-'                                  |      
   '-+-BLOB-FILE---+-------------------------------------'      
     +-CLOB-FILE---+                                            
     '-DBCLOB-FILE-'                                            

XML data host and file reference variables

The following diagram shows the syntax for declaring BLOB, CLOB, and DBCLOB host variables and file reference variables for XML data types.

>>-+-01----------+--variable-name--+---------------+------------>
   |         (1) |                 |        .-IS-. |   
   '-level-1-----'                 '-USAGE--+----+-'   

>--SQL TYPE IS XML AS------------------------------------------->

>--+-+-+-BINARY LARGE OBJECT-+----+--(--length--+---+--)-+--.--><
   | | '-BLOB----------------'    |             +-K-+    |      
   | +-+-CHARACTER LARGE OBJECT-+-+             +-M-+    |      
   | | +-CHAR LARGE OBJECT------+ |             '-G-'    |      
   | | '-CLOB-------------------' |                      |      
   | '-DBCLOB---------------------'                      |      
   '-+-BLOB-FILE---+-------------------------------------'      
     +-CLOB-FILE---+                                            
     '-DBCLOB-FILE-'                                            
Notes:
  1. level-1 indicates a COBOL level between 2 and 48.

ROWID host variables

The following diagram shows the syntax for declaring ROWID host variables.

>>-+-01----------+--variable-name--+---------------+------------>
   |         (1) |                 |        .-IS-. |   
   '-level-1-----'                 '-USAGE--+----+-'   

>--SQL TYPE IS--ROWID--.---------------------------------------><
Notes:
  1. level-1 indicates a COBOL level between 2 and 48.