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
- 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:
where:user-prefix.library-name.language.(member-name)
- 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.
- 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.
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.
COBOL and the underscore character: DCLGEN translates any underscore characters in the table's column names into hyphens (-) for use in the generated structure.
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.
Language | Data declaration |
---|---|
C | short int Itable-name[n]; |
COBOL | 01 Itable-name PIC S9(4) USAGE COMP OCCURS n TIMES |
PL/I | DCL Itable-name(n) BIN FIXED (15); |
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 * ******************************************************************