DCLGEN (DECLARATIONS GENERATOR) (DSN)

The declarations generator (DCLGEN) produces an SQL DECLARE TABLE statement and a COBOL, PL/I, or C data declaration for a table or a view named in the catalog.

Environment

The declarations generator is executed by the DSN subcommand DCLGEN. That subcommand can be issued from a DSN session, running in either foreground or background mode, or it can be issued through DB2I.

Data sharing scope: Group

Authorization

To execute this command, you must use a privilege set of the process that includes one of the following privileges or authorities:

Ownership of the table or view
SELECT privilege on the table or view
DBADM authority on the database containing the table
SYSADM authority
SYSCTRL authority (catalog tables only)

Syntax

>>-DCLGEN--TABLE(-+-table-name-+-)--+-------------------+------->
                  '-view-name--'    '-OWNER(owner-name)-'   

>--+-------------------+---------------------------------------->
   '-AT(location-name)-'   

>--LIBRARY(library name-+---------------+-+-----------+-)------->
                        '-(member-name)-' '-/password-'     

>--+-----------------------+--+------------------------+-------->
   |         .-ADD-----.   |  '-LANGUAGE(-+-PLI----+-)-'   
   '-ACTION(-+-REPLACE-+-)-'              +-C------+       
                                          +-IBMCOB-+       
                                          '-CPP----'       

>--+---------------+--+---------------------------+------------->
   '-NAMES(prefix)-'  '-STRUCTURE(structure-name)-'   

>--+-------+--+------------------+--+---------------------+----->
   +-APOST-+  |        .-NO--.   |  '-DBCSSYMBOL(-+-G-+-)-'   
   '-QUOTE-'  '-LABEL(-+-YES-+-)-'                '-N-'       

>--+----------------------+--+----------------------+----------->
   |            .-YES-.   |  |            .-NO--.   |   
   '-DBCSDELIM(-+-NO--+-)-'  '-COLSUFFIX(-+-YES-+-)-'   

>--+-------------------+--+---------------------+--------------->
   |         .-NO--.   |  |          .-STD--.   |   
   '-INDVAR(-+-YES-+-)-'  '-RMARGIN(-+-WIDE-+-)-'   

>--+-------------------+---------------------------------------><
   |         .-NO--.   |   
   '-DCLBIT(-+-YES-+-)-'

Option descriptions

TABLE

Specifies the table or view for which a declaration is generated. table-name or view-name is the qualified or unqualified name of the table or view.

The name must follow these rules:

If the name is a single-byte or mixed string and contains special characters other than underscores (_), it must be enclosed between apostrophes ('). If the language is COBOL, single-byte underscores in the name are translated into hyphens (-) by DCLGEN. Double-byte character set (DBCS) names need not be enclosed in apostrophes.
If the name contains single-byte apostrophes, each one must be doubled (''). (Some host languages do not permit apostrophes in variable names.)

A table or view name that contains a period and is not enclosed by apostrophes is a qualified table name. The characters to the left of the period constitute the table owner, and those to the right of the period constitute the table name. Any table name enclosed in apostrophes is an unqualified table name. To understand how DCLGEN determines the table name qualifier, see the description of the OWNER option, which follows.

OWNER( owner-name )

Specifies a qualifier for the table name. owner-name is the qualifier for the table name.

If you specify a qualified table name for the TABLE( table-name ) option, and you also specify OWNER( owner-name ), the qualifier portion of table-name supersedes owner-name as the table name qualifier. If you specify an unqualified table name for the TABLE( table-name ) option, and you do not specify OWNER( owner-name ), the SQL authorization ID is the table name qualifier.

DCLGEN supports the use of underscore (_) as a valid character in the owner-name keyword parameter.

The following table illustrates the decision process for determining the DCLGEN table name qualifier.

Table 1. Decision process for determining the DCLGEN table name qualifier
Table name	OWNER( owner-name ) specified	OWNER( owner-name ) not specified
TABLE( table-name ) qualified	table-name qualifier	table-name qualifier
TABLE( table-name ) unqualified	owner-name	SQL authorization ID

AT( location-name )

Identifies the location of the table or view name specified in TABLE ( table-name ). location-name , which can consist of 1 to 16 characters, uniquely identifies an instance of a table or view in a network.

If you specify AT, location-name is used as the prefix for the table name, and table-name or table-view must be a qualified name.

DCLGEN supports the use of underscore (_) as a valid character in the location-name keyword parameter.

LIBRARY( library-name ( member-name )/ password )

Specifies the data set into which the declarations go. This data set must already exist and be accessible to the declarations generator. It can be either sequential or partitioned. password is optional.

If the library name is not enclosed within apostrophes, DCLGEN constructs the following full data set name:

user-prefix.library-name.language.(member-name)

where:

user-prefix: The user prefix of the primary authorization ID of the transaction.
language: The value of the LANGUAGE option: PLI, C, or COBOL.
( member-name ): Optional; if not used, the output goes to a sequential data set.

ACTION

Indicates whether to add or replace the data set.

( ADD ): Adds the data set as a new member, if it does not already exist.
The default is ACTION ( ADD ).
(REPLACE): Replaces an existing member or data set with the new one. If the output is to a partitioned data set, and no member exists with the given name, one is added.

LANGUAGE

Specifies the language of the generated declaration.

Possible languages are:

(PLI), for PL/I
(C), for C/370™
(IBMCOB), for IBM® COBOL
(CPP), for C++

NAMES( prefix )

Allows field names to be formed in the declaration.

Avoid possible name conflicts between DCLGEN output and the source program. If a conflict occurs, use NAMES or STRUCTURE, or manually edit the generated declaration or source program.

prefix can contain double-byte characters.

The field names consist of prefix concatenated with a number from one to three digits in length. prefix can have up to 28 characters. If prefix is a single-byte or mixed string and the first character is not alphabetic, it must be enclosed in apostrophes. For example, if prefix is ABCDE, the field names will be ABCDE1, ABCDE2, and so on, up to a maximum of ABCDE999. Special characters can be used, but use caution to avoid possible name conflicts.

For COBOL and PL/I, if the prefix is a DBCS string, the field name will be the DBCS prefix concatenated with the DBCS representation of the number. For example, if prefix is <D1D2D3> (where "<" and ">" represent shift-out and shift-in characters, respectively, and D1D2D3 represent double-byte characters), generated field names will be <D1D2D3.1>, <D1D2D3.2>, and so on. The period (.) represents X'42'.

The column names in the table are taken as default names for the fields in the output.

STRUCTURE( structure-name )

Specifies the generated data structure.

structure-name can have up to 31 characters. If structure-name is a single-byte or mixed string and the first character is not alphabetic, it must be enclosed in apostrophes. You can use special characters, but use caution to avoid possible name conflicts.

structure-name can contain double-byte characters.

For SQL output, the name is the same as the table or view name. If the host language is C, the default structure name is the prefix DCL concatenated with the table name. If the host language is COBOL or PL/I and the table name is a single-byte or mixed string, the default structure name is also the prefix DCL concatenated with the table name. If the host language is COBOL or PL/I and the table name is a DBCS string, the default structure name is the prefix <.D.C.L> concatenated with the table or view name. "<" and ">" represent shift-out and shift-in characters, respectively. You must guard against possible conflicts with names in the source program. DCLGEN allows the specified structure name to be the same as the table or view name, but will issue a warning message.

APOST or QUOTE

Specifies the string delimiter character used in the host language. This option is effective only for COBOL programs.

APOST specifies the apostrophe (') as the host language string delimiter; the SQL delimiter is the quotation mark (").

QUOTE specifies the quotation mark (") as the host language delimiter; the SQL delimiter is the apostrophe (').

If neither APOST nor QUOTE is specified, the default is either APOST or QUOTE for COBOL, depending on what was specified on DB2® installation panel DSNTIPF.

The string delimiter delimits strings in host language statements. The SQL escape character delimits table and column names in the SQL DECLARE TABLE statement produced by DCLGEN. It is possible, by a choice made during DB2 installation, to make both delimiters the quotation mark or both the apostrophe.

LABEL

Indicates whether to include column labels in the output as comments. (Column labels can be assigned by the LABEL ON statement.)

( NO ): Omits the column labels.
( YES ): Includes the column labels.

DBCSSYMBOL

Specifies the symbol used to denote a graphic data type in a COBOL PICTURE clause.

(G): Graphic data is denoted using G.
(N): Graphic data is denoted using N.

DBCSDELIM

Specifies whether the DBCS table and column names in the generated DECLARE table statement will be delimited.

( YES ): DBCS table and column names will be delimited in the DCLGEN table declaration.
(NO): DBCS table and column names will not be delimited in the DCLGEN table declaration.

COLSUFFIX

Determines whether to form field names by attaching the column name to the prefix given by the NAMES option.

( NO ): The column name is not used as a suffix, and field names are controlled by the option NAMES.
(YES): If NAMES is specified, DCLGEN forms field names by adding column names as a suffix to the value of NAMES. For example, if the prefix given by NAMES is "NEW" and the column name is EMPNO, the field name is "NEWEMPNO".
If NAMES is not specified, DCLGEN issues a warning message and uses the column names as the field names.

INDVAR

Determines whether to create an indicator variable array for the host variable structure.

( NO ): DCLGEN does not create an indicator variable array.
(YES): DCLGEN creates an indicator array for the host variable structure. The array name is the table name with a prefix of "I" (or DBCS letter "<I>" if the table name is double-byte).

RMARGIN

Specifies the break point for statement tokens that must be wrapped onto one or more subsequent records of DCLGEN output.

( STD ): Statement tokens will be wrapped after column 72.
(WIDE): Statement tokens will be wrapped after column 80.

DCLBIT

Specifies whether to generate a DECLARE VARIABLE statement of SQL variables for columns that are defined as FOR BIT DATA. This statement is required in IBM COBOL applications that have host variables for FOR BIT DATA columns and are prepared by using the SQLCCSID option of the integrated DB2 coprocessor. The statement is also valid, but not currently required, for C/C++ and PL/I, and for COBOL that is not prepared by using the SQLCCSID option of the integrated DB2 coprocessor.

( NO ): Does not generate a DECLARE VARIABLE statement of SQL variables for columns that are defined as FOR BIT DATA.
( YES ): Generates a DECLARE VARIABLE statement of SQL variables for columns that are defined as FOR BIT DATA. If the table or view does not have any FOR BIT DATA columns, the statement is not generated.

Usage notes

Parsing of the DCLGEN command conforms to standard TSO parsing conventions.

The DECLARE statement: The DECLARE statement generated by DCLGEN will define all columns created with a data type of VARCHAR or LONG VARCHAR as VARCHAR. Columns created with a data type of VARGRAPHIC or LONG VARGRAPHIC will be defined as VARGRAPHIC.

Comments: The output for all host languages includes comments. The leading comment block echoes the DCLGEN subcommand that requested the declarations. The trailing comment block indicates the number of variables declared.

Using the output: To include the DCLGEN output in an application program, use the SQL INCLUDE statement. The same member name specified in the DCLGEN LIBRARY parameter is specified on the INCLUDE statement.

Prompts: Online TSO will prompt for missing or incorrectly specified options.

Editing the output: It is expected that the output of DCLGEN will not meet every need. You can freely edit the output before including it in a program. For example, you might want to change a variable name, or include SQL escape characters.

You can edit the output to add WITH DEFAULT to NOT NULL for columns that do not allow null values. If you edit the output, you must provide a default value.

If your column names contain embedded blanks, they will also be reflected in the host variable declarations, and you will have to remove, or translate, any blank characters to some other value.

For a column with an XML data type, DCLGEN generates the following output: SQL TYPE IS XML AS CLOB(1M). The default length value for the XML host variable is 1MB. You can manually update the DCLGEN output if you want a larger or smaller size.

C: DCLGEN support of the C language is unique in the following ways:

DCLGEN does not fold the STRUCTURE, NAMES, or TABLE values to uppercase.
For any DB2 column that has the data type CHAR( n ), where n > 1, DCLGEN generates the corresponding host variable as CHAR( n + 1) to avoid the DB2 warning. For n = 1, the corresponding host variable is CHAR.

Start of change COBOL and binary integers: DB2 uses the full size of binary integers. It can place larger values than allowed in the specified number of digits in a COBOL variable, which can result in truncated values. To avoid this problem, you can modify the DCLGEN output to declare COBOL variables that correspond to binary integer columns with USAGE COMP-5 instead of USAGE COMP or USAGE BINARY. End of change

COBOL and the underscore character: DCLGEN translates any underscore characters in the table's column names into hyphens (-) for use in the generated structure.

Start of change COBOL and DBCS: Although DB2 accepts values outside of the range from X'41' to X'FE', in COBOL data definition statements, both bytes of each double-byte character in data names must be within this range. Data names must also contain at least one DBCS character that does not have X'42' as its first byte. End of change

Data declarations for arrays of indicator variables: If DCLGEN creates an array of indicator variables, data declarations have the following form:

Language	Data declaration
C	short int I`table-name`[`n`];
COBOL	01 I`table-name` PIC S9(4) USAGE COMP OCCURS `n` TIMES
PL/I	DCL I`table-name`(`n`) BIN FIXED (15);

n is the number of columns in the table.

Examples

Example: Using DCLGEN to generate PL/I host variable declarations for columns in a DB2 table

The following subcommand generates PL/I declarations for table VEMPL and writes them to data set member prefix .SRCLIB.DATA(DSN8MPEM). The host structure and field names are generated from the table and column names.

DCLGEN TABLE(VEMPL) -
       LIBRARY('prefix.SRCLIB.DATA(DSN8MPEM)') -
       LANGUAGE(PLI) -
       APOST

The output looks like this:

/*********************************************************************/
/* DCLGEN TABLE(VEMPL) -                                             */
/*        LIBRARY('prefix.SRCLIB.DATA(DSN8MPEM)') -                  */
/*        LANGUAGE(PLI) -                                            */
/*        APOST                                                      */
/* ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS      */
/*********************************************************************/
EXEC SQL DECLARE VEMPL TABLE
          ( EMPNO                          CHAR(6) NOT NULL,
            FIRSTNME                       VARCHAR(12) NOT NULL,
            MIDINIT                        CHAR(1) NOT NULL,
            LASTNAME                       VARCHAR(15) NOT NULL,
            WORKDEPT                       CHAR(3) NOT NULL
          ) ;
/*********************************************************************/
/* PLI DECLARATION FOR TABLE VEMPL                                   */
/*********************************************************************/
DCL 1 DCLVEMPL,
     5 EMPNO    CHAR(6),
     5 FIRSTNME CHAR(12) VAR,
     5 MIDINIT  CHAR(1),
     5 LASTNAME CHAR(15) VAR,
     5 WORKDEPT CHAR(3);
/*********************************************************************/
/* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 5          */
/*********************************************************************/

Example: Using DCLGEN NAMES and STRUCTURE options to specify a field name prefix and structure name for the generated output

The following subcommand generates PL/I declarations for table VEMPL and writes them to data set member prefix .SRCLIB.DATA(DSN8MPEM). The generated PL/I declarations are in a structure named EMPRECORD, and all host variable names consist of the string FIELD, followed by a number.

DCLGEN TABLE(VEMPL) -
       LIBRARY('prefix.SRCLIB.DATA(DSN8MPEM)') -
       LANGUAGE(PLI) -
       NAMES(FIELD) -
       STRUCTURE(EMPRECORD) -
       APOST

The output looks like this:

/*********************************************************************/
/* DCLGEN TABLE(VEMPL) -                                             */
/*        LIBRARY('prefix.SRCLIB.DATA(DSN8MPEM)') -                  */
/*        LANGUAGE(PLI) -                                            */
/*        NAMES(FIELD) -                                             */
/*        STRUCTURE(EMPRECORD) -                                     */
/*        APOST                                                      */
/* ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS      */
/*********************************************************************/
EXEC SQL DECLARE VEMPL TABLE
          ( EMPNO                          CHAR(6) NOT NULL,
            FIRSTNME                       VARCHAR(12) NOT NULL,
            MIDINIT                        CHAR(1) NOT NULL,
            LASTNAME                       VARCHAR(15) NOT NULL,
            WORKDEPT                       CHAR(3) NOT NULL
          ) ;

/*********************************************************************/
/* PLI DECLARATION FOR TABLE VEMPL                                   */
/*********************************************************************/
DCL 1 EMPRECORD,
     5 FIELD1   CHAR(6),
     5 FIELD2   CHAR(12) VAR,
     5 FIELD3   CHAR(1),
     5 FIELD4   CHAR(15) VAR,
     5 FIELD5   CHAR(3);
/*********************************************************************/
/* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 5          */
/*********************************************************************/

Example: Generating DECLARE variable statements for columns that are defined with FOR BIT DATA

The following subcommand generates COBOL declarations for table MYTABLE, which contains FOR BIT DATA columns. The DCLBIT(YES) option is specified to cause DCLGEN to generate DECLARE VARIABLE statements for the FOR BIT DATA columns so that applications that include the generated declaration compile correctly when they are compiled with the SQLCCSID option of the DB2 coprocessor.

DCLGEN TABLE(MYTABLE)
       LIBRARY('prefix.SRCLIB.DATA(MYTABLE))
       LANGUAGE(COBOL)
       APOST
       DCLBIT(YES)

The output looks like this:

****************************************************************** 
* DCLGEN TABLE(MYTABLE)                                          * 
*        LIBRARY('prefix.SRCLIB.DATA(MYTABLE))                   * 
*        LANGUAGE(COBOL)                                         * 
*        APOST                                                   * 
*        DCLBIT(YES)                                             * 
* ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS   * 
****************************************************************** 
     EXEC SQL DECLARE MYTABLE TABLE                                
     ( COL1                           CHAR(10) NOT NULL,           
       COL2                           CHAR(10),                    
       COL3                           VARCHAR(12) NOT NULL,        
       COL4                           VARCHAR(12) NOT NULL         
     ) END-EXEC.                                                   
****************************************************************** 
* DECLARED VARIABLES FOR 'FOR BIT DATA' COLUMNS                  * 
****************************************************************** 
     EXEC SQL DECLARE                                              
      :COL2                                                        
     ,:COL4                                                        
     VARIABLE FOR BIT DATA END-EXEC.                               
****************************************************************** 
* COBOL DECLARATION FOR TABLE MYTABLE                            * 
****************************************************************** 
 01  DCLMYTABLE.                                                   
     10 COL1                 PIC X(10).                            
     10 COL2                 PIC X(10).                            
     10 COL3.                                                      
        49 COL3-LEN          PIC S9(4) USAGE COMP.                 
        49 COL3-TEXT         PIC X(12).                            
     10 COL4.                                                      
        49 COL4-LEN          PIC S9(4) USAGE COMP.                 
        49 COL4-TEXT         PIC X(12).                            
****************************************************************** 
* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 4       * 
******************************************************************