Generating table and view declarations by using DCLGEN from
DB2I
DCLGEN generates table and view declarations and the corresponding
variable declarations for C, COBOL, and PL/I programs so that you
do not need to code these statements yourself. The easiest way to
start DCLGEN is through DB2I.
Procedure
To generate table and view declarations by using DCLGEN
from DB2I:
From the DB2I Primary Option Menu panel, select the DCLGEN option.
The following DCLGEN panel is displayed:Figure 1. DCLGEN panel
DSNEDP01 DCLGEN SSID: DSN
===>
Enter table name for which declarations are required:
1 SOURCE TABLE NAME ===>
2 TABLE OWNER ..... ===>
3 AT LOCATION ..... ===> (Optional)
Enter destination data set: (Can be sequential or partitioned)
4 DATA SET NAME ... ===>
5 DATA SET PASSWORD ===> (If password protected)
Enter options as desired:
6 ACTION .......... ===> ADD (ADD new or REPLACE old declaration)
7 COLUMN LABEL .... ===> NO (Enter YES for column label)
8 STRUCTURE NAME .. ===> (Optional)
9 FIELD NAME PREFIX ===> (Optional)
10 DELIMIT DBCS .... ===> YES (Enter YES to delimit DBCS identifiers)
11 COLUMN SUFFIX ... ===> NO (Enter YES to append column name)
12 INDICATOR VARS .. ===> NO (Enter YES for indicator variables)
13 ADDITIONAL OPTIONS===> YES (Enter YES to change additional options)
PRESS: ENTER to process END to exit HELP for more information
Complete the following fields on the DCLGEN panel:
1 SOURCE TABLE NAME
Is the unqualified name of the table, view, or created temporary
table for which you want DCLGEN to produce SQL data declarations.
The table can be stored at your Db2 location
or at another Db2 location.
To specify a table name at another Db2 location,
enter the table qualifier in the TABLE OWNER field and the location
name in the AT LOCATION
field. DCLGEN generates a three-part table name from the SOURCE TABLE
NAME, TABLE OWNER, and AT LOCATION
fields. You can also use an alias for a table name.
To specify
a table name that contains special characters or blanks, enclose the
name in apostrophes. If the name contains apostrophes, you must double
each one(' '). For example, to specify a table named DON'S TABLE,
enter the following text:
'DON''S TABLE'
The
underscore is not handled as a special character in DCLGEN. For example,
the table name JUNE_PROFITS does not need to be enclosed in apostrophes.
Because COBOL field names cannot contain underscores, DCLGEN substitutes
hyphens (-) for single-byte underscores in COBOL field names that
are built from the table name.
You do not need to enclose DBCS
table names in apostrophes.
If you do not enclose the table
name in apostrophes, Db2 converts
lowercase characters to uppercase.
2 TABLE OWNER
Is the schema qualifier of the source table. If you
do not specify this value and the table is a local table, Db2 assumes that the table qualifier
is your TSO logon ID. If the table is at a remote location, you must
specify this value.
3 AT LOCATION
Is the location of a table or view at another Db2 subsystem. The value of the AT LOCATION field
becomes a prefix for the table name on the SQL DECLARE statement,
as follows: location_name, schema_name, table_name For
example, if the location name is PLAINS_GA, the schema name is CARTER,
and the table name is CROP_YIELD_89, the following table name is included
in the SQL DECLARE statement: PLAINS_GA.CARTER.CROP_YIELD_89
The
default is the local location name. This field applies to Db2 private protocol access only.
The location must be another Db2 for z/OS® subsystem.
4 DATA SET NAME
Is the name of the data set that you allocated to contain the
declarations that DCLGEN produces. You must supply a name; no default
exists.
The data set must already exist and be accessible to
DCLGEN. The data set can be either sequential or partitioned. If you
do not enclose the data set name in apostrophes, DCLGEN adds a standard
TSO prefix (user ID) and suffix (language). DCLGEN determines the
host language from the DB2I defaults panel.
For example, for
library name LIBNAME(MEMBNAME), the name becomes userid.libname.language(membname)
For library name LIBNAME, the name becomes userid.libname.language.
If
this data set is password protected, you must supply the password
in the DATA SET PASSWORD field.
5 DATA SET PASSWORD
Is the password for the data set that is specified in the DATA
SET NAME field, if the data set is password protected. The password
is not displayed on your terminal, and it is not recognized if you
issued it from a previous session.
6 ACTION
Specifies what DCLGEN is to do with the output when it is sent
to a partitioned data set. (The option is ignored if the data set
you specify in the DATA SET NAME field is sequential.) You can specify
one of the following values:
ADD
Indicates that an old version of the output does not exist and
creates a new member with the specified data set name. ADD is the
default.
REPLACE
Replaces an old version, if it already exists. If the member
does not exist, this option creates a new member.
7 COLUMN LABEL
Specifies whether DCLGEN is to include labels that are declared
on any columns of the table or view as comments in the data declarations.
(The SQL LABEL statement creates column labels to use as supplements
to column names.) You can specify one of the following values:
YES
Include column labels.
NO
Ignore column labels. NO is the default.
8 STRUCTURE NAME
Is the name of the generated data structure. The name can be up
to 31 characters. If the name is not a DBCS string, and the first
character is not alphabetic, enclose the name in apostrophes. If you
use special characters, be careful to avoid name conflicts.
If
you leave this field blank, DCLGEN generates a name that contains
the table or view name with a prefix of DCL. If the language is COBOL
or PL/I and the table or view name consists of a DBCS string, the
prefix consists of DBCS characters.
For C, lowercase characters
that you enter in this field are not converted to uppercase.
9 FIELD NAME PREFIX
Specifies a prefix that DCLGEN uses to form field names in the
output. For example, if you choose ABCDE, the field names generated
are ABCDE1, ABCDE2, and so on.
You can specify a field name prefix
of up to 28 bytes that can include special and double-byte characters.
If you specify a single-byte or mixed-string prefix and the first
character is not alphabetic, enclose the prefix in apostrophes. If
you use special characters, be careful to avoid name conflicts.
For
COBOL and PL/I, if the name is a DBCS string, DCLGEN generates DBCS
equivalents of the suffix numbers.
For C, lowercase characters
that you enter in this field do not converted to uppercase.
If
you leave this field blank, the field names are the same as the column
names in the table or view.
10 DELIMIT DBCS
Specifies whether DCLGEN is to delimit DBCS table names and column
names in the table declaration. You can specify one of the following
values:
YES
Specifies that DCLGEN is to enclose the DBCS table and column
names with SQL delimiters.
NO
Specifies that DCLGEN is not to delimit the DBCS table and column
names.
11 COLUMN SUFFIX
Specifies whether DCLGEN is to form field names by attaching the
column name as a suffix to the value that you specify in FIELD NAME
PREFIX. You can specify one of the following values:
YES
Specifies that DCLGEN is to use the column name as a suffix. For
example, if you specify YES, the field name prefix is NEW, and the
column name is EMPNO, the field name is NEWEMPNO.
If you specify
YES, you must also enter a value in FIELD NAME PREFIX. If you do not
enter a field name prefix, DCLGEN issues a warning message and uses
the column names as the field names.
NO
Specifies that DCLGEN is not to use the column name as a suffix.
The default is NO.
12 INDICATOR VARS
Specifies whether DCLGEN is to generate an array of indicator
variables for the host variable structure. You can specify one of
the following values:
YES
Specifies that DCLGEN is to generate an array of indicator variables
for the host variable structure.
If you specify YES, the array name
is the table name with a prefix of I (or DBCS letter <I> if
the table name consists solely of double-byte characters). The form
of the data declaration depends on the language, as shown in the following
table. n is the number of columns in the table.
Table 1. Declarations for indicator
variable arrays from DCLGEN
Language
Declaration form
C
short int Itable-name[n];
COBOL
01 Itable-name PIC S9(4) USAGE COMP-5 OCCURS n TIMES.
PL/I
DCL Itable-name(n)
BIN FIXED(15);
For example, suppose that you define the following table:
Specifies that DCLGEN is not to generate an array of indicator
variables. The default is NO.
13 ADDITIONAL OPTIONS
Indicates whether to display the panel for additional DCLGEN options,
including the break point for statement tokens and whether to generate
DECLARE VARIABLE statements for FOR BIT DATA columns. You can specify
YES or NO. The default is YES.
If you specified YES in the ADDITIONAL OPTIONS
field, the following ADDITIONAL DCLGEN OPTIONS panel is displayed:Figure 2. ADDITIONAL DCLGEN OPTIONS panel
DSNEDP02 ADDITIONAL DCLGEN OPTIONS SSID: DSN
===>
Enter options as desired:
1 RIGHT MARGIN .... ===> 72 (Enter 72 or 80)
2 FOR BIT DATA .... ===> NO (Enter YES to declare SQL variables for
FOR BIT DATA columns)
PRESS: ENTER to process END to exit HELP for more information
Otherwise, DCLGEN creates the declarations in the specified
data set.
If the ADDITIONAL DCLGEN OPTIONS panel is
displayed, complete the following fields on that panel:
1 RIGHT MARGIN
Specifies the break point for statement tokens that must be wrapped
to one or more subsequent records. You can specify column 72 or column
80.
The default is 72.
2 FOR BIT DATA
Specifies whether DCLGEN is to generate a DECLARE VARIABLE statement
for SQL variables for columns that are declared as FOR BIT DATA. This
statement is required in Db2 applications
that meet all of the following criteria:
are written in COBOL
have host variables for FOR BIT DATA columns
are prepared with the SQLCCSID option of the Db2 coprocessor.
You can specify YES or NO. The default is NO.
If the table
or view does not have FOR BIT DATA columns, DCLGEN does not generate
this statement.
DCLGEN creates the declarations
in the specified data set.