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.
* DCLGEN TABLE(IOD2.DEPT) *
* LIBRARY(PSS.TEST.REG2.DCLGEN(DGPALLP1)) *
* LANGUAGE(COBOL) *
* QUOTE *
* INDVAR(YES) *
* QUALIFYTB(NO) *
* ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS *
******************************************************************
EXEC SQL DECLARE DEPT TABLE
( DEPTNO CHAR(3) NOT NULL,
DEPTNAME VARCHAR(36) NOT NULL,
MGRNO CHAR(6),
ADMRDEPT CHAR(3) NOT NULL,
LOCATION CHAR(16)
) END-EXEC.
******************************************************************
* COBOL DECLARATION FOR TABLE IOD2.DEPT *
******************************************************************
01 DCLDEPT.
10 DEPTNO PIC X(3).
10 DEPTNAME.
49 DEPTNAME-LEN PIC S9(4) USAGE COMP.
49 DEPTNAME-TEXT PIC X(36).
10 MGRNO PIC X(6).
10 ADMRDEPT PIC X(3).
10 LOCATION PIC X(16).
******************************************************************
* INDICATOR VARIABLE STRUCTURE *
******************************************************************
01 IDEPT.
10 INDSTRUC PIC S9(4) USAGE COMP OCCURS 5 TIMES.
******************************************************************
* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 5 *
******************************************************************
01 DEPT-IND.
10 MGRNO-IND PIC S9(4) USAGE COMP.
10 LOCATION-IND PIC S9(4) USAGE COMP.
| For more information, view the Quick Course SQL Explorer for DB2—Using DCLGEN. |
Related topic