DCLGEN Guidelines

DCLGEN Guidelines

Follow these guidelines when issuing DCLGEN statements at your shop.

Use the Appropriate DCLGEN Library

Most shops allocate DCLGEN libraries. They are commonly either a partitioned data set or in the format specified by your shop's change management tool.

Control Who Creates DCLGEN Members

The DBA usually is responsible for creating DCLGEN members for each table. This establishes a point of control for managing change.

Avoid Modifying DCLGEN Members

Avoid modifying the code produced by the DCLGEN command. When the DECLARE TABLE code or WORKING-STORAGE variables are manually changed after DCLGEN creates them, the risk of syntax errors and incompatibilities increases .

Consider Prefixing DCLGEN Host Variables

The DCLGEN command produces WORKING-STORAGE fields with the same names as the DB2 column names , except that underscores are converted to hyphens. It should be standard practice for shops to use DCLGEN with COLSUFFIX and NAMES to produce prefixed field names. When COLSUFFIX is not utilized, two tables having identical column names would have identical field names for each table.

Use Unqualified Table References

When you're using the DCLGEN command, set the current SQLID to the creator of the table to ensure that DCLGEN does not generate a qualified table name . Then, when specifying the DCLGEN options, provide an unqualified table name. This produces an unqualified DECLARE TABLE statement.

An alternative method can be used whereby a SYNONYM for every table is created for the DBA issuing the DCLGEN . The SYNONYM must be named the same as the table for which it has been created. The DBA should then specify the unqualified SYNONYM to DCLGEN . This produces an unqualified DECLARE TABLE statement.

Unfortunately, because DCLGEN does not provide the option of producing a qualified or unqualified DECLARE TABLE statement, DBAs must perform gyrations to unqualify their DECLARE TABLE statements.

Avoid Breaking DCLGEN Host Variables into Components

Although doing so is not generally recommended, you can modify the WORKING-STORAGE variables generated by DCLGEN to "break apart" columns into discrete components. Consider, for example, the following DCLGEN -created WORKING-STORAGE variables for the DSN8810.PROJACT table:

 01  DCLPROJACT.     10  PROJNO     PIC X(6).     10  ACTNO      PIC S9(4)      USAGE COMP.     10  ACSTAFF    PIC S999V99    USAGE COMP-3.     10  ACSTDATE   PIC X(10).     10  ACENDATE   PIC X(10). 

The two date columns, ACSTDATE and ACENDATE , are composed of the year, the month, and the day. By changing the structure to "break apart" these columns, you could reference each component separately, as in the following example:

 01  DCLPROJACT.     10  PROJNO                PIC X(6).     10  ACTNO                 PIC S9(4)   USAGE COMP.     10  ACSTAFF               PIC S999V99 USAGE COMP-3.     10  ACSTDATE.         15  ACSTDATE-YEAR.             20  ACSTDATE-CC   PIC X(2).             20  ACSTDATE-YY   PIC X(2).         15  ACSTDATE-FILLER1  PIC X.         15  ACSTDATE-MONTH    PIC X(2).         15  ACSTDATE-FILLER2  PIC X.         15  ACSTDATE-DAY      PIC X(2).     10  ACENDATE.         15  ACENDATE-YEAR     PIC X(4).         15  ACENDATE-FILLER1  PIC X.         15  ACENDATE-MONTH    PIC X(2).         15  ACENDATE-FILLER2  PIC X.         15  ACENDATE-DAY      PIC X(2). 

This approach is not favored because it is invasive to the generated DCLGEN code, which can result in errors, as mentioned previously. Instead, you should code structures that can be used to "break apart" these columns outside the DCLGEN , and then move the necessary columns to the structures outside the DCLGEN variables.

Avoid the Field Name PREFIX

Avoid the field name PREFIX option of DCLGEN . This option generates WORKING-STORAGE variables with a numeric suffix added to the PREFIX text. For example, if you ran DCLGEN for the DSN8510.PROJACT table and specified a PREFIX of COL , the following WORKING-STORAGE variable names would be generated:

 01  DCLPROJACT.     10  COL01      PIC X(6).     10  COL02      PIC S9(4)      USAGE COMP.     10  COL03      PIC S999V99    USAGE COMP-3.     10  COL04      PIC X(10).     10  COL05      PIC X(10). 

Note how each column begins with the supplied prefix and ends with a number that steadily increases by 1. The COL01 column is used for the PROJNO column, COL02 for ACTNO , and so on. This type of DCLGEN should be avoided because the generated column names are difficult to trace to the appropriate WORKING-STORAGE variables.