DB2 DCLGEN: Generate Table and Host Variable Declarations

What DCLGEN does—and what it does not do

DCLGEN means declarations generator. It is a DSN subcommand, not a database utility such as LOAD or REORG. DCLGEN reads the catalog description of one table or view and produces source text. It can run from a foreground or background DSN session, through DB2I, or through the ADMIN_COMMAND_DSN administrative stored procedure when a controlled automation process is appropriate.

The generated DECLARE TABLE is descriptive. It tells the DB2 application preparation process what columns the programmer expects, but it does not create the table and does not replace catalog validation at bind time. The generated host structure gives the application fields with compatible language types and lengths. This saves repetitive typing and, more importantly, reduces errors such as treating a VARCHAR as a simple fixed string or giving a decimal host variable the wrong scale.

• DCLGEN reads metadata; it does not read application rows.
• It generates declarations; it does not compile, link-edit, bind, or run a program.
• Its output is a snapshot; it does not update itself when the source object changes.
• It generates all columns of the named object, not a hand-picked SELECT list supplied with the command.

Table and view input

The TABLE operand accepts a table name or view name that exists in the DB2 catalog. Prefer a qualified name such as APP.CUSTOMER_ACCOUNT so the result does not depend on the authorization ID used to run DCLGEN. If TABLE is unqualified and OWNER is omitted, DB2 uses the SQL authorization ID as the qualifier. A qualifier inside TABLE takes precedence over OWNER.

A base table is the obvious input when the program works with the complete business object. A view can be a better contract when the application should see a stable subset, renamed columns, or columns assembled from another design. However, DCLGEN describes the view's result columns; it does not explain whether every view column is updatable. The SQL statements and view definition still determine what the program can change.

Remote catalog objects can be identified with the documented ATlocation option. Most teams generate against the development subsystem whose DDL is being promoted. Generating against an unrelated production level can hide pending changes or introduce fields that are unavailable in test, so record the subsystem, location, and object version with the generated member.

Core DCLGEN options

The output data set must already exist and be accessible. Use ACTION(ADD) when accidental replacement must be prevented and ACTION(REPLACE) in a deliberate regeneration step. STRUCTURE and NAMES are valuable when multiple DCLGEN members appear in one program: predictable prefixes avoid collisions between common column names such as ID, STATUS, or CREATE_TS.

1
2
3
4
5
6
7
8
9
10
11
12
13
DSN SYSTEM(DB2T)
DCLGEN TABLE(APP.CUSTOMER_ACCOUNT) +
  LIBRARY('TEAM.APP.DCLGEN(CUSTACCT)') +
  ACTION(REPLACE) +
  LANGUAGE(IBMCOB) +
  STRUCTURE(DCL-CUSTOMER-ACCOUNT) +
  NAMES(CA-) +
  COLSUFFIX(YES) +
  INDVAR(YES) +
  RMARGIN(STD)
END
 
/* Verify subsystem, data set, naming rules, and authorization locally. */

Understanding the generated declarations

Normal output has two useful interfaces. First, EXEC SQL DECLARE identifies the table and lists its columns in SQL terms. Second, a host-language structure maps those columns into fields the program can use. In COBOL, fixed character columns become PICTURE X items, decimal columns become packed decimal pictures, and varying-length columns become groups containing a length field and a data field.

Exact output depends on the installed DB2 maintenance level and selected options. Current DB2 12 and DB2 13 maintenance added useful mappings, including COMP-5 generation for integer data and improved DECFLOAT support for PL/I and C. Treat the generated member from your maintained subsystem—not an old training example—as the source of truth for your environment.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
       EXEC SQL DECLARE APP.CUSTOMER_ACCOUNT TABLE
           ( CUSTOMER_ID       INTEGER NOT NULL,
             CUSTOMER_NAME     VARCHAR(80) NOT NULL,
             CREDIT_LIMIT      DECIMAL(11,2),
             PROFILE_XML       XML
           ) END-EXEC.
 
       01  DCL-CUSTOMER-ACCOUNT.
           05 CA-CUSTOMER-ID       PIC S9(9) COMP-5.
           05 CA-CUSTOMER-NAME.
              49 CA-CUSTOMER-NAME-LEN  PIC S9(4) COMP-5.
              49 CA-CUSTOMER-NAME-TEXT PIC X(80).
           05 CA-CREDIT-LIMIT      PIC S9(9)V99 COMP-3.
           05 CA-PROFILE-XML
              USAGE IS SQL TYPE IS XML AS CLOB(1M).
 
      * Conceptual shape only; generate from your own maintained subsystem.

SQLTYPE and SQL TYPE IS declarations

The terms SQLTYPE and SQL TYPE IS are easy to confuse. SQLTYPE is also the name of a code field in an SQLDA, where a numeric value describes a dynamic SQL column and whether it can be null. DCLGEN source output normally concerns host declarations instead. For SQL-oriented values that COBOL, PL/I, or C cannot express directly, the generated declaration can use an SQL TYPE IS clause.

IBM documents XML as a clear DCLGEN example: it generates SQL TYPE IS XML AS CLOB(1M). The one-megabyte size is a default for that host variable and can be changed when the program's actual XML requirement is smaller or larger. Similar SQL TYPE syntax is used by application programmers for LOB locators, file reference variables, ROWID, binary values, and other specialized interfaces. Do not replace these declarations with an ordinary character field merely because the compiler does not recognize the SQL words; the DB2 precompiler or integrated coprocessor must process them first.

Practical SQLTYPE rules

• Separate SQLDA SQLTYPE codes from source-language SQL TYPE IS clauses.
• Keep the DB2 preparation step before the normal host-language compile.
• Review generated defaults for large XML or LOB-style fields before production use.
• Recheck IBM documentation and local maintenance when a newer data type produces unexpected or blank declarations.

Null indicators and INDVAR

A nullable SQL column needs two pieces of information in a host program: its value and whether the value is null. COBOL has no ordinary data value equal to SQL NULL, so DB2 uses a small integer indicator variable. A negative indicator means null on input or reports null on output. Zero normally means a non-null value. A positive output indicator can communicate conditions such as truncation, depending on the operation and data type.

INDVAR(YES) asks DCLGEN to create an indicator array corresponding to the host-variable structure. Generation alone does not attach indicators magically to every SQL reference. The programmer still writes the correct indicator after the host variable, verifies its position, and initializes it before input. Named indicator fields are often clearer than positional array elements, so some teams generate the array and then expose approved names in a maintained wrapper.

1
2
3
4
5
6
7
8
9
10
11
12
       EXEC SQL
            SELECT CREDIT_LIMIT
              INTO :CA-CREDIT-LIMIT
                   :CA-IND-CREDIT-LIMIT
              FROM APP.CUSTOMER_ACCOUNT
             WHERE CUSTOMER_ID = :CA-CUSTOMER-ID
       END-EXEC.
 
       IF CA-IND-CREDIT-LIMIT < 0
           MOVE ZERO TO CA-CREDIT-LIMIT
           MOVE 'Y'  TO WS-CREDIT-LIMIT-IS-NULL
       END-IF

Custom tables and application-specific DCLGEN members

DCLGEN works just as well for a table designed by your organization as for an IBM sample table. A custom DCLGEN table should mean a normal, cataloged application table or view with a deliberate interface—not a private metadata table that DCLGEN reads instead of the DB2 catalog. Use a schema-qualified name, stable column naming, and a structure prefix tied to the application.

If a program uses only five columns from a fifty-column table, generating all fifty may create an unnecessarily broad coupling. One approach is to create an approved application view containing the intended interface and run DCLGEN for that view. Another is to maintain a smaller hand-written host structure, but then the team accepts responsibility for every type and length. The view approach keeps generation tied to catalog metadata while making the contract explicit.

Do not use a custom table as a substitute for dynamic metadata discovery. Programs that must describe unknown result sets at run time use DESCRIBE and an SQLDA; DCLGEN is for a known compile-time object interface.

Templates and naming standards

The standard DCLGEN syntax has no general-purpose TEMPLATE operand. Native formatting and naming controls include STRUCTURE, NAMES, COLSUFFIX, LABEL, INDVAR, DBCS options, and RMARGIN. When a shop says “DCLGEN template,” it usually means a maintained JCL skeleton, REXX wrapper, pipeline step, or source member convention that supplies those documented options consistently.

A useful template records environment placeholders, table qualifier, destination library, language, structure prefix, indicator policy, and ADD-versus-REPLACE behavior. Keep the generated block separate from hand-written additions. If a site needs aliases, validation fields, or comments not produced by DCLGEN, put them in a wrapper copybook or run a reviewed, deterministic post-processing step. Hand edits buried inside generated output disappear at the next regeneration.

• Use one naming pattern across tables so SQL statements remain readable.
• Make the generation command reproducible instead of relying on remembered DB2I fields.
• Mark generated regions clearly and keep local business fields outside them.
• Test template changes against CHAR, VARCHAR, decimal, nullable, XML, and long names.

Including DCLGEN output in source

Store the generated declarations in a library available to the DB2 precompiler or integrated coprocessor. The program uses the same member name from the DCLGEN LIBRARY operand in an SQL INCLUDE statement. INCLUDE is expanded during DB2 application preparation, so it must appear where the resulting host-language and SQL declarations are valid—normally the COBOL DATA DIVISION's WORKING-STORAGE or LINKAGE section.

1
2
3
4
5
6
7
8
9
10
       DATA DIVISION.
       WORKING-STORAGE SECTION.
 
       EXEC SQL
            INCLUDE CUSTACCT
       END-EXEC.
 
       EXEC SQL
            INCLUDE SQLCA
       END-EXEC.

SQL INCLUDE is not the same as a COBOL COPY processed independently before DB2 sees the SQL. Use the preparation method standardized by your build. Verify concatenation order when two libraries contain the same member name; otherwise a successful compile can silently use an obsolete declaration.

Source control and safe regeneration

A generated member is still a versioned application interface. Store it in source control when your build and audit practices permit, along with the exact DCLGEN command, source object, subsystem level, and any deterministic post-processing. This lets a code review show that CUSTOMER_NAME changed from VARCHAR(40) to VARCHAR(80), a new nullable column appeared, or a decimal scale changed.

1. Promote or apply the approved DDL in the development catalog.
2. Run the recorded DCLGEN command against the intended qualified table or view.
3. Compare generated output with the committed member; never replace it blindly.
4. Review reordered, renamed, added, removed, type-changed, and nullability-changed columns.
5. Update SQL and indicator references that depend on the changed fields.
6. Precompile or coprocess, compile, link-edit, bind, and run focused application tests.
7. Promote DDL, source, generated member, and package changes in a coordinated release.

Avoid regenerating all DCLGEN members automatically during every build. That makes the compile depend on whichever catalog happens to be reachable and can introduce unreviewed interface changes. A safer pipeline has an explicit generation stage that produces a diff and fails when output changes without an approved schema update.

Troubleshooting DCLGEN

Object not found or wrong object selected

Check the subsystem, location, qualifier, and authorization ID. A missing qualifier can resolve to the runner's ID instead of the application schema. Fully qualify TABLE and verify that the object exists in that catalog.

Authorization failure

The DCLGEN process needs an appropriate privilege set, such as ownership or SELECT on the table or view, DBADM on its database, or suitable system authority. Do not solve a development generation failure by granting broad production authority; use the least-privileged generation identity approved by the site.

Output member errors

LIBRARY must identify an existing accessible sequential or partitioned data set. ADD fails when the member already exists; REPLACE is required for intentional replacement. Check quoting because an unquoted library name can be expanded using the user prefix and language conventions.

Invalid names or wrapped source

Column names with special characters, embedded blanks, or excessive length can be invalid in a host language even though they are legal SQL identifiers. Use NAMES, STRUCTURE, and COLSUFFIX thoughtfully. COBOL underscores are translated to hyphens. RMARGIN(STD) wraps after column 72, while WIDE wraps after column 80; match the source format accepted by the downstream toolchain.

Compile errors around SQL TYPE IS

Confirm that the DB2 precompiler or integrated coprocessor is processing the source before the ordinary compiler treats SQL TYPE text as COBOL, PL/I, or C. Also verify that the selected host-language level and DB2 maintenance support the source data type.

Program and table disagree after ALTER

Regenerate and compare. Then rebuild and rebind the affected application. DCLGEN itself does not discover which programs use the member, and REBIND alone does not resize an old COBOL field. Maintain dependency information so schema reviews identify every include consumer.

Explain It Like I'm Five

Imagine a DB2 table is a tray with spaces for a name, a number, and a date. Your COBOL program needs its own tray with spaces of exactly the right shapes. DCLGEN looks at the DB2 tray and draws a matching tray for COBOL. If a space is allowed to be empty, DCLGEN can also draw a little yes-or-no flag called an indicator. The drawing does not build or change the real tray. When someone changes the real tray, you make a new drawing, compare it with the old one, and test that your program still puts everything in the right place.

Exercises

1. Write a DCLGEN command for APP.ORDER_HEADER, COBOL output member ORDHDR, structure DCL-ORDER-HEADER, prefixed column names, and generated indicators.
2. Explain why DCLGEN for a view can create a narrower application contract than DCLGEN for its base table.
3. Draw the generated COBOL shape you expect for INTEGER, VARCHAR(30), DECIMAL(9,2), and one nullable DATE column.
4. Given a nullable CREDIT_LIMIT, write a SELECT INTO with a host variable and indicator, then describe the meaning of a negative indicator.
5. Design a source-control checklist for regenerating a member after VARCHAR(40) becomes VARCHAR(100).
6. Diagnose this failure: DCLGEN ACTION(ADD) reports that the destination member exists. State the safe next step and why an automatic overwrite might be dangerous.
7. Explain the difference between SQLDA SQLTYPE metadata and a COBOL SQL TYPE IS XML declaration.
8. Create a site template specification using only documented DCLGEN operands, then list any business-specific fields that belong outside the generated block.