When you generate the SQL for
one or more objects by using the GEN function, you can specify a number of options to control
the SQL that is generated. These options are available on the Generate SQL from DB2 catalog
(ADB2GENB) panel.
The following screen shows an example of this panel when SQL is requested to recreate a
database:
Figure 1. Generate SQL from DB2 catalog
(ADB2GENB) panel for a database
ADB2GENB -------------- DD1A Generate SQL from DB2 catalog ------------- 11:34
Option ===>
Generate SQL statements for database DBFSSGEN DB2 System: DD1A
DB2 SQL ID: ADM001
SQL statement types to be generated from the DB2 catalog:
CREATE DATABASE . . . . Y (Y,N) GRANT access ON DATABASE . . Y (Y,N,A,R)
CREATE TABLESPACE . . . Y (Y,N) GRANT access ON TABLESPACE . Y (Y,N,A,R)
CREATE TABLE . . . . . . Y (Y,N) GRANT access ON TABLE . . . Y (Y,N,A,R)
CREATE VIEW . . . . . . Y (Y,N,D) GRANT access ON VIEW . . . . Y (Y,N,A,R)
CREATE INDEX . . . . . . Y (Y,N) ALTER TABLE ADD FOREIGN KEY. Y (Y,N,D)
CREATE SYNONYM . . . . . Y (Y,N) LABEL ON . . . . . . . . . . Y (Y,N)
CREATE ALIAS . . . . . . Y (Y,N) COMMENT ON . . . . . . . . . Y (Y,N)
CREATE TRIGGER . . . . . Y (Y,N,D,A,R)
REBIND PLAN/PACKAGE . . . . Y (Y,N,D)
CREATE MASK . . . . . . Y (Y,N) ALTER TABLE ACTIVATE CONTROL Y (Y,N)
CREATE PERMISSION . . . Y (Y,N)
CREATE STORAGE GROUP . . Y (Y,N) GRANT use OF STORAGE GROUP . Y (Y,N,A,R)
New names/values for generated SQL: (leave blank to use current values)
Object schema . . . . . > Run SQLID . . . . . . . .
Object grantor . . . . . >
Alloc TS size as . . . . DEFINED (DEFINED, USED, or ALLOC)
Database name . . . . .
Storage group for TS . . > Storage group for IX . . . >
Target DB2 version . . . (Current DB2 version: 1115)
Target Function Level. . 500 (Current DB2 FL: 501)
Use Masking . . . . . . NO (Yes/No)
Use Exclude Spec . . . . NO (Yes/No)
Target cat qualifier . . > (Default is SYSIBM)
Generate catalog stats . NO (Yes,No,Only)
Statistics tables . . ALL (All or Select. Default is All)
Include DB2 pending chgs NO (Yes,No,Alter,Only)
PBG NUMPARTS value . . . EXISTING (Defined, Existing)
PBG LOB objects . . . . COMPUTED (Computed, Implicit)
Generate index cleanup . (Yes,No,Only)
SQL output data set and execution mode:
Add to a WSL . . . . . . NO (Yes/No)
Data set name . . . . .
Data set disposition . OLD (OLD, SHR, or MOD)
Execution mode . . . . . BATCH (BATCH or TSO)
Commit statements per . (Db, tS, Tb, All, None. Default is All)
DB2 defaults handling . (Keep, or Remove. Default is Keep)
Prompt to run SQL . . . NO (Yes/No. For TSO mode and no WSL)
Include SQL comments . . NO (Yes/No. For BATCH mode and no WSL)
DB2 Command output data set:
Data set name . . . . .
Data set disposition . OLD (OLD, SHR, or MOD)
BP - Change batch job parameters
G - Change additional parameters
This panel contains the following fields:
The first set of fields specify whether SQL statements are to be generated for the
requested objects and any dependent objects, where applicable. The fields that are
displayed depend on the type of object for which you requested SQL:
CREATE DATABASE
Specifies whether CREATE statements are to be generated for all of the explicitly
requested databases.
When you also request to generate storage groups, statements
are generated for the default storage group.
CREATE TABLESPACE
Specifies whether CREATE statements are to be generated for all of the table
spaces that are identified during processing, which includes both explicitly and
implicitly requested table spaces. For example, if you specify the GEN command for a
database and specify Y in the CREATE
TABLESPACE field, a CREATE statement is generated for each table space
that resides in the database.
CREATE parent DATABASE
Specifies whether CREATE statements are to be generated for the database that
contains the table or table space.
This field is applicable to only tables and
table spaces. In the case of a table, if Y is specified,
CREATE parent TABLESPACE is implicitly set to Y,
regardless of the value specified for that field. This behavior ensures the
generated DDL does not fail due to a missing CREATE TABLESPACE statement.
CREATE parent TABLESPACE
Specifies whether CREATE statements are to be generated for the table space that
contains the table. This field is applicable to only tables.
CREATE TABLE
Specifies whether CREATE statements are to be generated for all of the tables that
are identified during processing, which includes both explicitly and implicitly
requested tables.
CREATE VIEW
Specifies whether CREATE statements are to be generated for all of the views that
are identified during processing, which includes both explicitly and implicitly
requested views.
Specify D to extract views without
requiring Db2 Admin Tool to check
whether all other objects that used in the view are also being generated. This
option significantly reduces the resource consumption when running on large
Db2 catalogs.
CREATE INDEX
Specifies whether CREATE statements are to be generated for all of the indexes
that are identified during processing, which includes both explicitly and implicitly
requested indexes.
CREATE SYNONYM
Specifies whether CREATE statements are to be generated for all of the synonyms
that are identified during processing, which includes both explicitly and implicitly
requested synonyms.
CREATE ALIAS
Specifies whether CREATE statements are to be generated for all of the aliases
that are identified during processing, which includes both explicitly and implicitly
requested aliases.
CREATE TRIGGER
Specifies whether CREATE statements are to be generated for all of the triggers
that are identified during processing, which includes both explicitly and implicitly
requested triggers.
CREATE MASK
Specifies whether CREATE statements are to be generated for all of the masks that
are identified during processing, which includes both explicit and implicit
masks.
CREATE PERMISSION
Specifies whether CREATE statements are to be generated for all of the permissions
that are identified during processing, which includes both explicit and implicit
permissions.
CREATE STORAGE GROUP
Specifies whether CREATE statements are to be generated for all of the storage
groups that are identified during processing, which includes both explicit and
implicit storage groups.
GRANT access ON DATABASE
Specifies whether a GRANT access ON DATABASE statement is to be
generated.
The following values are valid for this field and the other GRANT
fields:
Y
Generate GRANT statements for authorizations and roles.
N
Do not generate any GRANT statements.
A
Generate GRANT statements for authorizations.
R
Generate GRANT statements for roles.
GRANT access ON TABLESPACE
Specifies whether a GRANT access ON TABLESPACE statement is to
be generated. Valid values are listed in GRANT access ON DATABASE.
GRANT access ON TABLE
Specifies whether a GRANT access ON TABLE statement is to be
generated. Valid values are listed in GRANT access ON DATABASE.
GRANT access ON VIEW
Specifies whether a GRANT access ON VIEW statement is to be
generated. Valid values are listed in GRANT access ON DATABASE.
ALTER TABLE ADD FOREIGN KEY
Specifies whether an ALTER TABLE ADD FOREIGN KEY statement is to be generated.
Specify D to extract foreign keys for tables that are
dependent on the tables that are extracted.
LABEL ON
Specifies whether a LABEL ON statement is to be generated.
COMMENT ON
Specifies whether a COMMENT ON statement is to be generated.
REBIND PLAN/PACKAGE
Specifies whether REBIND commands for plans and packages are to be generated.
These REBIND commands are written to the data set that is specified in the
DB2 Command output file: Data set name field.
ALTER TABLE ACTIVATE CONTROL
Activates an enabled masked column. A column mask can be created as enabled or
disabled for column access control. An enabled column mask does not take effect
until the ALTER TABLE statement with the ACTIVATE COLUMN ACCESS CONTROL clause is
used to activate column access control for the table.
GRANT use OF STORAGE GROUP
Specifies whether a GRANT USE OF STOGROUP statement is to be generated. Valid
values are listed in GRANT access ON DATABASE.
The second set of fields specify the new names or values to be used in the generated
SQL:
Object schema
Specifies a schema to use for any new objects.
Run SQLID
Specifies the SQL ID to use when creating objects. The SQL ID that is specified
must have the privileges that are necessary to create objects, such as an
administrative type of SQL ID that has been defined. If you specify a value of
<NONE>, no SET CURRENT SQLID statements are generated in the DDL. If you leave
the field blank, a SET CURRENT SQLID statement is generated in the DDL before each
object that is created; where possible, the SQL ID that was originally used to
create the object is used.
If you specify an SQL ID of <NONE> and use
synonyms, the following behaviors apply:
If the creator of the synonym is the same as the creator of the table on
which the synonym is defined, an executable CREATE SYNONYM statement is
generated.
If the creator of the synonym is not the same as the creator of the table on
which the synonym is defined, the SQL ID that created the synonym is extracted
from the catalog and both the SET SQLID and CREATE SYNONYM statements are
created. However, these statements are commented out, and an informational
message is issued. Because these statements are included in comments, other
generated statements might fail. For example, a view that is defined using the
synonym might fail.
The
other Db2 Admin Tool functions
where you can specify a RUN SQLID value include the Rename Database, ALT, Migrate,
and Change Management functions.
Object grantor
Specifies the grantor of the object.
Alloc TS size as
Specifies how to generate the primary quantity. The following values are valid:
DEFINED
Uses the size that is defined in the catalog.
USED
Uses the size that is actually used.
ALLOC
Uses the allocated size.
If you specify USED or ALLOC, ensure that you have recently run the RUNSTATS
utility on the selected objects and the STOSPACE utility on the storage groups for
the objects that are generated. Running these utilities is necessary, because
Db2 Admin Tool depends on
information in the Db2
catalog to generate actual allocated space or actual used space. The actual data set
sizes for table spaces and index spaces are not retrieved.
Database name
Specifies a new database name for the objects (except when initiated by using a
primary command from a list of databases).
Storage group for TS
Specifies a new storage group for the table spaces.
Storage group for IX
Specifies a new storage group for the indexes.
Target DB2 version
Specifies the Db2 level
for the generated SQL statements, if different from the current Db2 level.
The Db2 level format is
vvrm, where vv=version,
r=release, and m=modification level. The
current Db2 level is the
default.
Sometimes SQL syntax support is removed from Db2. Specifying the correct
target Db2 version
ensures that the generated SQL is valid for the target Db2 subsystem. For example, for
CREATE INDEX, Db2 11
supports EXCLUDE NULL KEYS, but Db2 10 does not.
The
following values are examples of valid level values:
1010
Db2 10
compatibility mode (CM8)
1012
Db2 10
compatibility mode (CM9)
1013
Db2 10
enabling-new-function mode (ENFM)
1015
Db2 10
new-function mode (NFM)
1110
Db2 11 conversion
mode (CM)
1113
Db2 11
enabling-new-function mode (ENFM)
1115
Db2 11
new-function mode (NFM)
Example: Suppose that your current Db2 level is Db2 11 new-function mode, but
you want to generate SQL that runs on a Db2 10 new-function mode system.
Set 1015 as the target Db2 version.
The IN DD run parameter DB2REL uses the same format and values
as the Target DB2 version option. When Db2 Admin Tool generates a GEN batch
job, it gets the Db2
release level from an SQL CONNECT statement and uses that release level value in
the generated job. Use this generated job as the base for defining customized GEN
jobs.
Target Function Level
Specifies the
target function level. DDL is generated based on the syntax requirements for the
specified target function level. Valid values are any integer value in the range 501
- 999 or 100. You can specify any function level equal to or less than the current
Db2 version function
level.
Include DB2 pending chgs
Specifies how to include Db2 pending changes. Valid values are:
Yes
Include the Db2
pending changes in the generated CREATE statements for table spaces and
indexes. Yes is the default.
No
Generate SQL comments that contain ALTER statements for the Db2 pending changes. The
Db2 pending
changes are not included in the generated CREATE statements for table spaces
and indexes.
Alter
Generate ALTER statements for the Db2 pending changes.
Only
Generate only ALTER statements for the Db2 pending changes. No
other SQL statements, such as CREATE statements, are generated.
PBG NUMPARTS value
Specifies the value for the NUMPARTS clause when re-creating a
partition-by-growith (PBG) table space. Valid values are:
Defined
Use the NUMPARTS value that was specified when the table space was
created.
Existing
Use the existing NUMPARTS value. This existing value includes any added
partitions. This value can be different from the value that was defined when
the table space was created. Existing is the
default.
PBG LOB objects
Specifies whether the auxiliary objects for LOB columns in a partition-by-growth
(PBG) table space are to be re-created implicitly or explicitly. Valid values are:
Computed
The auxiliary objects are to be re-created explicitly if all of the required
auxiliary objects exist and were created explicitly.
Computed is the default.
Implicit
The auxiliary objects are to be re-created implicitly by Db2.
Generate index cleanup
Specifies index cleanup options. Valid values are:
Yes
Generate DML statements for the Db2 SYSINDEXCLEANUP catalog
table.
No
Do not generate DML statements for the Db2 SYSINDEXCLEANUP catalog
table. No is the default value.
Only
Generate DML statements only for the Db2 SYSINDEXCLEANUP catalog
table. No other DDL statements, such as CREATE statements, or DML statements,
such as statements for catalog statistics, are to be generated.
Use Masking
Specifies whether to enable masking. For instructions on how to specify masks, see
Specifying masks.
Use Exclude Spec
Specifies whether to use an existing exclude specification. An exclude
specification enables you to select objects to exclude from the generated
DDL.
Target cat qualifier
Specifies the qualifier to use in the INSERT, UPDATE, and DELETE statements for
updating catalog statistics and for index cleanup settings.
Generate catalog stats
Specifies whether to generate catalog statistics, which means that INSERT, UPDATE,
and DELETE statements that modify the catalog statistics are included in the DDL
file. Valid value are:
Y
Generate DDL and catalog statistics.
N
Generate DDL only. Do not generate catalog statistics.
O
Generate catalog statistics only. Do not generate DDL.
The statistic fields that are modified are those fields that are
associated with the objects that are generated. (The complete list of statistics
fields are those fields that are set by RUNSTATS and that can be modified and the
statistics columns for table functions in SYSROUTINES, which are not set by
RUNSTATS.)
Statistics tables
Specifies which statistics to generate. Valid values are:
All
Generate all statistics. All is the default.
Select
Generate selected statistics. If you specify Select,
the Catalog Statistics Tables
(ADBPGEN2) panel opens.
On this panel, you can select the catalog tables for which you want to
generate DML statements.
The third set of fields specify the output file and execution mode options:
Add to work stmt list
Specifies whether to save the output to a work statement list (WSL) data set.
Data set name
Specifies the name of the data set in which to place the generated SQL. The value
must be a valid SPUFI input data set name or SYSOUT=x. The default
is SYSOUT=*. If you leave this field blank, the command output is
created as comments in the output file.
Data set disposition
Specifies the disposition of the output data set.
Execution mode
Specifies the execution mode for generating the SQL. Valid values are:
BATCH
Runs the SQL generation as a batch job. Db2 Admin Tool generates the batch
job and displays the job in an ISPF edit session, where you can make any
changes before submitting the job for execution.
TSO
Runs the SQL generation online. Db2 Admin Tool generates the SQL
statements online and displays the results.
Commit statements per
Specifies how often an SQL COMMIT statement is added to the generated SQL. Valid
values are:
D
COMMIT statements are run for each database.
S
COMMIT statements are run for each table space.
T
COMMIT statements are run for each table.
A
COMMIT statements are run for all objects. A is the
default.
N
COMMIT statements are never run.
The
exception is if the generated SQL includes 500 or more statements that
update catalog statistics. In this case, even if you specify N, COMMIT
statements are added to avoid catalog locking problems. A COMMIT statement
is added at least every 500 statements that modify statistics.
DB2 defaults handling
Specifies whether Db2
default parameters are kept in the generated SQL. Valid values are:
K
Keeps the default parameters. K is the default.
R
Removes the default parameters.
Prompt to run SQL
Specifies whether, after the SQL edit session, Db2 Admin Tool is to display a prompt that
allows you to choose whether to run the SQL immediately. This option applies only
when you are using TSO mode without WSL. Valid values are:
Y
Display this prompt.
The maximum number of SQL statements that are allowed
is 8120. The maximum length of an SQL statement is 2,097,152 bytes (2
MB).
N
Do not display this prompt. N is the default.
The last set of fields specify the following options for the command output file:
Data set name
Specifies the name of the data set in which to place the generated REBIND commands
if REBIND PLAN/PACKAGE is set to Y or D.
