Overview of generating declarations

For application programmers, the Declarations Generator function of the

BMC AMI SQL Explorer for Db2

product simplifies the process of creating a table declaration.

The Declarations Generator automatically generates a DECLARE TABLE statement, a host variable structure, and an indicator structure for nullable columns for a COBOL, C, or PL/I program. The product inserts the generated statements into a partitioned data set (PDS) member or a sequential data set that you can include in any number of applications. This reduces application development time and enables you to avoid the errors that often occur with manual coding. It also ensures that your host variable definitions match the Db2 column types, so you know that your table declarations are correct even before you begin precompiling an application.

Other generator products generate an array for indicator variables. Such an array can make it difficult to remember the column order and the array position for the indicator variable to be checked. As a further complication, the array includes all columns, rather than just the columns containing null values. All of these factors might lead to costly mistakes.

As an alternative solution, the Declarations Generator appends an alternative indicator structure to the bottom of your generated output. This structure uses the column name with a suffix of IND for the null indicator name. The product includes only nullable columns in the indicator structure. This simplifies the process of coding null indicators, because you do not need to remember the column order.

The Declarations Generator accommodates existing programs by including an array in the output, so that such programs do not have to be changed for the new indicator variables, even though the programs may have other coding changes that require a recompile. This means that the same Declarations Generator file can be used in both existing and new programs, even as newer programs begin to take advantage of the new indicator names.

Output from Declarations Generator operation shows a sample of the array and the additional indicator structure output produced by the Declarations Generator.

Some content is unavailable due to permissions.

      ******************************************************************
      * DCLGEN TABLE(RDAGXR.POLICY_INFO_BUS)                           *
      * LIBRARY(RDAGXR.SPUFI.IN(DEMO))                          *
      * ACTION(REPLACE)                                         *
      * LANGUAGE(COBOL)                                         *
      * HOST-AFFIX(HV-AFFX-)                                    *
      * HOST-STRUCT(HOST-STRUCT)                                *
      *        APOST *
      *        LABEL(YES) *
      *        COLSUFFIX(YES) *
      * IND-STRUCT(IND-STRUCT) *
      * IND-AFFIX(IX-AFFX-) *
      * INDVAR(YES) *
      * PREFIX/SUFFIX(P) *
      * ALLOWCMNT(NO) *
      * ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS   *
      ******************************************************************
            EXEC SQL DECLARE RDAGXR.POLICY_INFO_BUS TABLE
            ( POLICY_ID              CHAR(4),
              COVERAGE                       INTEGER,
              BUS_STAR DATE NOT NULL,
              BUS_END DATE NOT NULL
            ) END-EXEC.
      ******************************************************************
      * COBOL DECLARATION FOR TABLE RDAGXR.POLICY_INFO_BUS *
      ******************************************************************
       01 HOST-STRUCT.
           10 HV-AFFX-POLICY-ID PIC X(4).
           10 HV-AFFX-COVERAGE     PIC S9(9) USAGE COMP-5.
           10 HV-AFFX-BUS-START PIC X(10).
           10 HV-AFFX-BUS-END      PIC X(10).
      ******************************************************************
      * INDICATOR VARIABLE STRUCTURE                                   *
      ******************************************************************
       01 IPOLICY-INFO-BUS.
           10 INDSTRUC           PIC S9(4) USAGE COMP-5 OCCURS 4 TIMES.
      ******************************************************************
      * THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 4       *
      ******************************************************************
       01 IND-STRUCT.
           10 IX-AFFX-HV-AFFX-COVERAGE-IND PIC S9(4) USAGE COMP.
           10 IX-AFFX-HV-AFFX-POLICY-ID-IND PIC S9(4) USAGE COMP.

For more information, view the Quick Course SQL Explorer for DB2—Using DCLGEN.

Overview of generating declarations

On this page