DCLGEN Tool

Host Compatibility Option provides a tool to generate DDL for existing SQL tables or copybooks for tables in COBOL or C syntax.

You can run the DCLGEN tool from the HCO for DB2 LUW user interface, or using the mfhco dclgen command.

Use Case Scenarios

Scenario 1

This example creates the file ITEMS.CPY from table DEMO.ITEMS in database DB2DEMO and prefixes all COBOL field names with the table name.

The DECLARE TABLE part of copybook file would look something like this:

************************************************************
*  MFHCODCL OPTIONS:                                          
*  DATABASE : DB2DEMO  
*  SCHEMA : DEMO      
*  PREFIX : TABLE NAME
************************************************************
  EXEC SQL DECLARE DEMO.ITEMS TABLE
  ( ORD_NO    INTEGER   NOT NULL
  ,ITEM_NO    SMALLINT  NOT NULL 
  ,PROD_ID    CHAR(4) 
  ,QTY_ORDERED   SMALLINT  NOT NULL WITH DEFAULT    
  ,QTY_SHIPPED   SMALLINT  NOT NULL WITH DEFAULT   
  ,AMT_EACH    DECIMAL(7, 2) NOT NULL WITH DEFAULT   
  ,DATE_ENTERED   TIMESTAMP  NOT NULL WITH DEFAULT 
  ,DATE_SHIPPED   DATE 
  ,ITEM_NOTES   VARCHAR(560)
  ) END-EXEC.

The COBOL DECLARATION part of copybook file would look something like this:

************************************************************
* COBOL DECLARATION FOR TABLE DEMO.ITEMS     
************************************************************
 01 DCLITEMS. 
  10 ITEMS-ORD-NO           PIC S9(09) COMP. 
  10 ITEMS-ITEM-NO          PIC S9(04) COMP. 
  10 ITEMS-PROD-ID          PIC X(4). 
  10 ITEMS-QTY-ORDERED      PIC S9(04) COMP. 
  10 ITEMS-QTY-SHIPPED      PIC S9(04) COMP. 
  10 ITEMS-AMT-EACH         PIC S9(05)v9(02) COMP-3. 
  10 ITEMS-DATE-ENTERED     PIC X(26). 
  10 ITEMS-DATE-SHIPPED     PIC X(10). 
  10 ITEMS-ITEM-NOTES.
   49 ITEMS-ITEM-NOTES-LEN  PIC S9(04) COMP. 
   49 ITEMS-ITEM-NOTES-TEXT PIC X(560).
************************************************************
* COBOL INDICATOR VARIABLES FOR TABLE      
************************************************************
 01 DCLITEMS-NULL. 
  10 ITEMS-PROD-ID-NULL      PIC S9(04) COMP. 
  10 ITEMS-DATE-SHIPPED-NULL PIC S9(04) COMP. 
  10 ITEMS-ITEM-NOTES-NULL   PIC S9(04)COMP.
************************************************************
* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 9  
************************************************************

Scenario 2

In this example, change the prefix to a user value of "HV" instead of using the table name as prefix and generate indicator values with a suffix of IND.

The COBOL DECLARATION part of copybook file would look something like this:

************************************************************
* COBOL DECLARATION FOR TABLE DEMO.ITEMS      
************************************************************
 01 DCLITEMS. 
  10 HV-ORD-NO           PIC S9(09) COMP. 
  10 HV-ITEM-NO          PIC S9(04) COMP. 
  10 HV-PROD-ID          PIC X(4). 
  10 HV-QTY-ORDERED      PIC S9(04) COMP. 
  10 HV-QTY-SHIPPED      PIC S9(04) COMP. 
  10 HV-AMT-EACH         PIC S9(05)v9(02) COMP-3. 
  10 HV-DATE-ENTERED     PIC X(26). 
  10 HV-DATE-SHIPPED     PIC X(10). 
  10 HV-ITEM-NOTES. 
   49 HV-ITEM-NOTES-LEN  PIC S9(04) COMP. 
   49 HV-ITEM-NOTES-TEXT PIC X(560). 
************************************************************
* COBOL INDICATOR VARIABLES FOR TABLE       
************************************************************
 01 DCLITEMS-IND. 
  10 HV-PROD-ID-IND      PIC S9(04) COMP. 
  10 HV-DATE-SHIPPED-IND PIC S9(04) COMP. 
  10 HV-ITEM-NOTES-IND   PIC S9(04) COMP. 
************************************************************
* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 9  
************************************************************

Scenario 3

In this example, change the prefix to a user value of "FIELD" and generate numbered fields instead of column names. Use configuration default instead of specifying CBL for language.

The COBOL DECLARATION part of copybook file would look something like this:

************************************************************
* COBOL DECLARATION FOR TABLE DEMO.ITEMS     
************************************************************
 01 DCLITEMS. 
  10 FIELD-001       PIC S9(09) COMP. 
  10 FIELD-002       PIC S9(04) COMP. 
  10 FIELD-003       PIC X(4). 
  10 FIELD-004       PIC S9(04) COMP. 
  10 FIELD-005       PIC S9(04) COMP. 
  10 FIELD-006       PIC S9(05)v9(02) COMP-3. 
  10 FIELD-007       PIC X(26). 
  10 FIELD-008       PIC X(10). 
  10 FIELD-009. 
   49 FIELD-009-LEN  PIC S9(04) COMP. 
   49 FIELD-009-TEXT PIC X(560). 
************************************************************
* COBOL INDICATOR VARIABLES FOR TABLE      
************************************************************
 01 DCLITEMS-NULL. 
  10 FIELD-003-NULL PIC S9(04) COMP. 
  10 FIELD-008-NULL PIC S9(04) COMP. 
  10 FIELD-009-NULL PIC S9(04) COMP. 
************************************************************
* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 9  
************************************************************

Scenario 4

In this example, create the file ITEMS.PLI from table DEMO.ITEMS in database DB2DEMO and prefix all field names with the table name. Language is PL/I.

The DECLARE TABLE part of copybook file would look something like this:

/*-------------------------------------------------------*/
/*  MFHCODCL OPTIONS:                                    */
/*   DATABASE : DB2DEMO                                  */
/*   SCHEMA : DEMO                                       */
/*   PREFIX : TABLE NAME                                 */
/*-------------------------------------------------------*/
 EXEC SQL DECLARE DEMO.ITEMS TABLE 
 ( ORD_NO    INTEGER   NOT NULL,
  ITEM_NO    SMALLINT  NOT NULL, 
  PROD_ID    CHAR(4),
  QTY_ORDERED   SMALLINT  NOT NULL WITH DEFAULT,
  QTY_SHIPPED   SMALLINT  NOT NULL WITH DEFAULT,
  AMT_EACH    DECIMAL(7, 2) NOT NULL WITH DEFAULT,
  DATE_ENTERED   TIMESTAMP  NOT NULL WITH DEFAULT,
  DATE_SHIPPED DATE,  ITEM_NOTES   VARCHAR(560)
 );

The PL/I DECLARATION part of copybook file would look something like this:

/*-------------------------------------------------------*/
/*  PL/I DECLARATION FOR TABLE DEMO.ITEMS                */
/*-------------------------------------------------------*/
DECLARE 
1 DCLITEMS, 
 5 ITEMS_ORD_NO     BIN FIXED(31), 
 5 ITEMS_ITEM_NO    BIN FIXED(15), 
 5 ITEMS_PROD_ID     CHAR(4), 
 5 ITEMS_QTY_ORDERED    BIN FIXED(15), 
 5 ITEMS_QTY_SHIPPED    BIN FIXED(15), 
 5 ITEMS_AMT_EACH     DEC FIXED(5,2), 
 5 ITEMS_DATE_ENTERED    CHAR(26), 
 5 ITEMS_DATE_SHIPPED    CHAR(10), 
 5 ITEMS_ITEM_NOTES    CHAR(560) VAR;
/*-------------------------------------------------------*/
/*  PL/I INDICATOR VARIABLES FOR TABLE                   */
/*-------------------------------------------------------*/
DECLARE 1 DCLITEMS_NULL, 
 5 ITEMS_PROD_ID_NULL    BIN FIXED(15),
 5 ITEMS_DATE_SHIPPED_NULL   BIN FIXED(15), 
 5 ITEMS_ITEM_NOTES_NULL   BIN FIXED(15);
/*-------------------------------------------------------*/
/*  THE NO. OF COLUMNS DESCRIBED BY THIS DECLARATION IS 9*/
/*-------------------------------------------------------*/

Scenario 5

This example creates the file ITEMS.C from table DEMO.ITEMS in database DB2DEMO and prefixes all field names with the table name. Language is C.

The copybook file would look something like this:

/*-------------------------------------------------------*/
/*  MFHCODCL OPTIONS:                                    */
/*   DATABASE : DB2DEMO                                  */
/*   SCHEMA : DEMO                                       */
/*   PREFIX : TABLE NAME                                 */
/*  C HOST VARIABLES FOR TABLE DEMO.ITEMS                */
/*-------------------------------------------------------*/
EXEC SQL BEGIN DECLARE SECTION;
struct dclitems
{
 long   items_ord_no;
 short   items_item_no;
 char   items_prod_id[4];
 short   items_qty_ordered;
 short   items_qty_shipped;
 double  items_amt_each;
 char   items_date_entered[26];
 char   items_date_shipped[10];
 struct {
    short len
    char data[560];
   }  items_item_notes;
/*-------------------------------------------------------*/
/*   C INDICATOR VARIABLES FOR TABLE                     */
/*-------------------------------------------------------*/
 short   items_prod_id_null;
 short   items_date_shipped_null;
 short   items_item_notes_null;
 };
EXEC SQL END DECLARE SECTION;
/*-------------------------------------------------------*/
/*  THE NO. OF COLUMNS DESCRIBED BY THIS DECLARATION IS 9*/
/*-------------------------------------------------------*/

Scenario 6

In this scenario, generate the EMP_PHOTO table without a DECLARE TABLE section, generate all numeric variables as COMP-5 rather than COMP, and generate LOB variables as LOB SQL TYPEs rather than generating them as the default of LOB-LOCATOR.

The DECLARE TABLE part of copybook file would look something like this:

/*----------------------------------------------------------*/
/* MFHCODCL OPTIONS:                                        */
/*     DATABASE : SAMPLE                                    */
/*     SCHEMA   : TESTER                                    */
/*     PREFIX   : TABLE NAME                                */
/*----------------------------------------------------------*/
/* COBOL DECLARATION FOR TABLE                              */
/*----------------------------------------------------------*/
 01  DCLEMP-PHOTO.
     10 EMP-PHOTO-EMPNO                  PIC X(6).
     10 EMP-PHOTO-PHOTO-FORMAT.
        49 EMP-PHOTO-PHOTO-FORMAT-LEN    PIC S9(04)  COMP-5.
        49 EMP-PHOTO-PHOTO-FORMAT-TEXT   PIC X(10).
     10 EMP-PHOTO-PICTURE                SQL TYPE IS
                                         BLOB(100K).
/*----------------------------------------------------------*/
/* COBOL INDICATOR VARIABLES FOR TABLE                      */
/*----------------------------------------------------------*/
 01  DCLEMP-PHOTO-NULL.
     10 EMP-PHOTO-PICTURE-NULL           PIC S9(04)  COMP-5.
/*----------------------------------------------------------*/
/* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 3 */
/*----------------------------------------------------------*/