How do I define a copybook that describes data exported to a sequential file?

Scenario: I have data in a Db2® table and I've used the FM/Db2 Export Utility to copy the data to a sequential file. I need to define a copybook that describes the exported data.

You need to code the copybook in either COBOL, HLASM, or PL/I. The amount of space required in the export data set for each of the Db2® data types is summarized in the following example:

Suppose you want to export data from the table created using the following definition:

 CREATE TABLE SAMPLE.EXPORT_EX
    (COL1   INTEGER         NOT NULL
    ,COL2   SMALLINT        NOT NULL
    ,COL3   FLOAT(21)       NOT NULL
    ,COL4   FLOAT(53)       NOT NULL
    ,COL5   DECIMAL(1,0)    NOT NULL
    ,COL6   DECIMAL(15,7)   NOT NULL
    ,COL7   CHAR(10)        NOT NULL
    ,COL8   VARCHAR(10)     NOT NULL
    ,COL9   DATE            NOT NULL
    ,COL10  TIME            NOT NULL
    ,COL11  TIMESTAMP       NOT NULL
    ,COL12  ROWID  GENERATED ALWAYS NOT NULL
    )

A COBOL copybook that describes the exported data is as follows:

 01  EXPORT-EXAMPLE.
* INTEGER DATA TYPE
     10 COL1                 PIC S9(6) USAGE COMP.
* SMALLINT DATA TYPE
     10 COL2                 PIC S9(4) USAGE COMP.
* FLOATING POINT (4 BYTES) DATA TYPE
     10 COL3                 USAGE COMP-1.
* FLOATING POINT (8 BYTES) DATA TYPE
     10 COL4                 USAGE COMP-2.
* PACKED DECIMAL DATA TYPE
     10 COL5                 PIC S9(1)V USAGE COMP-3.
     10 FILLER               PIC X(15).
* PACKED DECIMAL DATA TYPE
     10 COL6                 PIC S9(8)V9(7) USAGE COMP-3.
     10 FILLER               PIC X(8).
* CHARACTER DATA TYPE
     10 COL7                 PIC X(10).
* VARCHAR DATA TYPE
     10 COL8.
        49 COL8-LEN          PIC S9(4) USAGE COMP.
        49 COL8-TEXT         PIC X(10).
* DATE DATA TYPE
     10 COL9                 PIC X(10).
* TIME DATA TYPE
     10 COL10                PIC X(8).
* TIMESTAMP DATA TYPE
     10 COL11                PIC X(26).
* ROWID DATA TYPE
     10 COL12.
        49 COL12-LEN         PIC S9(4) USAGE COMP.
        49 COL12-TEXT        PIC X(40).

In HLASM, the copybook looks like this:

EXAMPLE  DSECT
* Integer data type
COL1     DS    F
* Smallint data type
COL2     DS    H
* Floating point (4 bytes) data type
COL3     DS    E
* Floating point (8 bytes) data type
COL4     DS    D
* Packed decimal data type (1 byte)
COL5     DS    PL1
* Packed decimal data type (8 bytes)
COL6     DS    PL8
* Character data type
COL7     DS    CL10
* VARCHAR data type
COL8_LEN DS    H
COL8_TXT DS    CL10
* DATE data type
COL9     DS    CL10
* TIME data type
COL10    DS    CL8
* TIMESTAMP data type
COL11    DS    CL26
* ROWID data type
COL12_LEN DS   H
COL12_TXT DS   CL40
         END

In PL/I, the copybook looks like this:

 DCL 1  EXPORT_EXAMPLE UNALIGNED,
/* INTEGER DATA TYPE                                                 */
     2 COL1                  BIN(31,0),
/* SMALLINT DATA TYPE                                                */
     2 COL2                  BIN(15,0),
/* FLOATING POINT (4 BYTES) DATA TYPE                                */
     2 COL3                  FLOAT,
/* FLOATING POINT (8 BYTES) DATA TYPE                                */
     2 COL4                  FLOAT(8),
/* PACKED DECIMAL DATA TYPE                                          */
     2 COL5                  FIXED DEC(1,0),
     2 COL5_PAD              CHAR(15),
/* PACKED DECIMAL DATA TYPE                                          */
     2 COL6                  FIXED DEC(15,7),
     2 COL6_PAD              CHAR(8),
/* CHARACTER DATA TYPE                                               */
     2 COL7                  CHAR(10),
/* VARCHAR DATA TYPE                                                 */
     2 COL8,
        3 COL8_LEN           BIN(15,0),
        3 COL8_TEXT          CHAR(10),
/* DATE DATA TYPE                                                    */
     2 COL9                  CHAR(10),
/* TIME DATA TYPE                                                    */
     2 COL10                 CHAR(8),
/* TIMESTAMP DATA TYPE                                               */
     2 COL11                 CHAR(26),
/* ROWID DATA TYPE                                                   */
     2 COL12,
        3 COL12_LEN          BIN(15,0),
        3 COL12_TEXT         CHAR(40);

Note the following:

Db2® data type: Space in exported data set.
INTEGER: 4 bytes.
SMALLINT: 2 bytes.
REAL (4 bytes): 4 bytes.
FLOAT (8 bytes): 8 bytes.
PACKED DECIMAL: 16 bytes in total. The data is left-aligned. The unused bytes contain binary zeros. A DECIMAL(n,m) field occupies TRUNC(n/2) + 1 bytes within the 16-byte field.
CHAR(n): n bytes.
VARCHAR(n): n+2 bytes. The data is preceded by a 2-byte binary field, containing the number of characters in the VARCHAR field.
DATE: 10 bytes. See note 1.
TIME: 8 bytes. See note 1.
TIMESTAMP: 26 bytes.
ROWID: 42 bytes. Define a ROWID column as for a VARCHAR(40) column.

Note:

The length required for DATE and TIME fields may vary if a date or time exit is in use.

If the Db2® table has columns that allow nulls and was created using the following definition:

CREATE TABLE SAMPLE.EXPORT_EX_NULL
   (COL1   INTEGER
   ,COL2   SMALLINT
   ,COL3   FLOAT(21)
   ,COL4   FLOAT(53)
   ,COL5   DECIMAL(1,0)
   ,COL6   DECIMAL(15,7)
   ,COL7   CHAR(10)
   ,COL8   VARCHAR(10)
   ,COL9   DATE
   ,COL10  TIME
   ,COL11  TIMESTAMP
   ,COL12  ROWID  GENERATED ALWAYS NOT NULL
   )

A COBOL copybook that describes the exported data is as follows:

 01  EXPORT-EXAMPL2.
* INTEGER DATA TYPE WITH NULL INDICATOR
     10 COL1-NULL            PIC S9(4) USAGE COMP.
     10 COL1                 PIC S9(6) USAGE COMP.
* SMALLINT DATA TYPE WITH NULL INDICATOR
     10 COL2-NULL            PIC S9(4) USAGE COMP.
     10 COL2                 PIC S9(4) USAGE COMP.
* FLOATING POINT (4 BYTES) DATA TYPE WITH NULL INDICATOR
     10 COL3-NULL            PIC S9(4) USAGE COMP.
     10 COL3                 USAGE COMP-1.
* FLOATING POINT (8 BYTES) DATA TYPE WITH NULL INDICATOR
     10 COL4-NULL            PIC S9(4) USAGE COMP.
     10 COL4                 USAGE COMP-2.
* PACKED DECIMAL DATA TYPE WITH NULL INDICATOR
     10 COL5-NULL            PIC S9(4) USAGE COMP.
     10 COL5                 PIC S9(1)V USAGE COMP-3.
     10 FILLER               PIC X(15).
* PACKED DECIMAL DATA TYPE WITH NULL INDICATOR
     10 COL6-NULL            PIC S9(4) USAGE COMP.
     10 COL6                 PIC S9(8)V9(7) USAGE COMP-3.
     10 FILLER               PIC X(8).
* CHARACTER DATA TYPE WITH NULL INDICATOR
     10 COL7-NULL            PIC S9(4) USAGE COMP.
     10 COL7                 PIC X(10).
* VARCHAR DATA TYPE WITH NULL INDICATOR
     10 COL8-NULL            PIC S9(4) USAGE COMP.
     10 COL8.
        49 COL8-LEN          PIC S9(4) USAGE COMP.
        49 COL8-TEXT         PIC X(10).
* DATE DATA TYPE WITH NULL INDICATOR
     10 COL9-NULL            PIC S9(4) USAGE COMP.
     10 COL9                 PIC X(10).
* TIME DATA TYPE WITH NULL INDICATOR
     10 COL10-NULL           PIC S9(4) USAGE COMP.
     10 COL10                PIC X(8).
* TIMESTAMP DATA TYPE WITH NULL INDICATOR
     10 COL11-NULL           PIC S9(4) USAGE COMP.
     10 COL11                PIC X(26).
* ROWID DATA TYPE
     10 COL12.
        49 COL12-LEN         PIC S9(4) USAGE COMP.
        49 COL12-TEXT        PIC X(40).

In HLASM, the copybook looks like this:

EXAMPLE2  DSECT
* Integer data type with null indicator
COL1_NI  DS    H
COL1     DS    F
* Smallint data type with null indicator
COL2_NI  DS    H
COL2     DS    H
* Floating point (4 bytes) data type with null indicator
COL3_NI  DS    H
COL3     DS    E
* Floating point (8 bytes) data type with null indicator
COL4_NI  DS    H
COL4     DS    D
* Packed decimal data type (1 byte) with null indicator
COL5_NI  DS    H
COL5     DS    PL1
* Packed decimal data type (8 bytes) with null indicator
COL6_NI  DS    H
COL6     DS    PL8
* Character data type with null indicator
COL7_NI  DS    H
COL7     DS    CL10
* VARCHAR data type with null indicator
COL8_NI  DS    H
COL8_LEN DS    H
COL8_TXT DS    CL10
* DATE data type with null indicator
COL9_NI  DS    H
COL9     DS    CL10
* TIME data type with null indicator
COL10_NI DS    H
COL10    DS    CL8
* TIMESTAMP data type with null indicator
COL11_NI DS    H
COL11    DS    CL26
* ROWID data type
COL12_LEN DS   H
COL12_TXT DS   CL40
         END

In PL/I, the copybook looks like this:

 DCL 1  EXPORT_EXAMPLE UNALIGNED,
/* INTEGER DATA TYPE WITH NULL INDICATOR                             */
     2 COL1_NULL             BIN(15,0),
     2 COL1                  BIN(31,0),
/* SMALLINT DATA TYPE WITH NULL INDICATOR                            */
     2 COL2_NULL             BIN(15,0),
     2 COL2                  BIN(15,0),
/* FLOATING POINT (4 BYTES) DATA TYPE WITH NULL INDICATOR            */
     2 COL3_NULL             BIN(15,0),
     2 COL3                  FLOAT,
/* FLOATING POINT (8 BYTES) DATA TYPE WITH NULL INDICATOR            */
     2 COL4_NULL             BIN(15,0),
     2 COL4                  FLOAT(8),
/* PACKED DECIMAL DATA TYPE WITH NULL INDICATOR                      */
     2 COL5_NULL             BIN(15,0),
     2 COL5                  FIXED DEC(1,0),
     2 COL5_PAD              CHAR(15),
/* PACKED DECIMAL DATA TYPE WITH NULL INDICATOR                      */
     2 COL6_NULL             BIN(15,0),
     2 COL6                  FIXED DEC(15,7),
     2 COL6_PAD              CHAR(8),
/* CHARACTER DATA TYPE WITH NULL INDICATOR                           */
     2 COL7_NULL             BIN(15,0),
     2 COL7                  CHAR(10),
/* VARCHAR DATA TYPE WITH NULL INDICATOR                             */
     2 COL8_NULL             BIN(15,0),
     2 COL8,
        3 COL8_LEN           BIN(15,0),
        3 COL8_TEXT          CHAR(10),
/* DATE DATA TYPE WITH NULL INDICATOR                                */
     2 COL9_NULL             BIN(15,0),
     2 COL9                  CHAR(10),
/* TIME DATA TYPE WITH NULL INDICATOR                                */
     2 COL10_NULL            BIN(15,0),
     2 COL10                 CHAR(8),
/* TIMESTAMP DATA TYPE WITH NULL INDICATOR                           */
     2 COL11_NULL            BIN(15,0),
     2 COL11                 CHAR(26),
/* ROWID DATA TYPE                                                   */
     2 COL12,
        3 COL12_LEN          BIN(15,0),
        3 COL12_TEXT         CHAR(40);

The only difference between the first and second set of definitions is the addition of a 2-byte binary field immediately prior to the column definition for those columns that allow Db2® null values.

Related tasks

Related references

Export Utility ("From") panel