Setting object use options
- On the Primary Menu panel, an object list panel, or a utility panel, on the Command line, type OPTIONS (OPT).
- Press Enter.The Options panel is displayed.
In the Object selections field, type Y.The Object Options panel is displayed:
Object Options 1 to 20 of 20
Command ===> Scroll ===> PAGE
SET CURRENT SQLID to . . CREATEDBY CREATEDBY/CREATOR/NONE/<other>
Include in Mixed Lists
Synonym and Alias. . . . Y Package. . . . . . . Y Plan . . . . . . . . Y
Include in HDDL
Database. . . . . . . . Y Tablespace . . . . . Y Table. . . . . . . . Y
Materialized Queries. . Y Index. . . . . . . . Y View . . . . . . . . N
Synonym . . . . . . . . N Alias. . . . . . . . N Trigger. . . . . . . Y
Foreign key . . . . . . Y Plan . . . . . . . . Y
Package . . . . . . . . N N/Y/L/R - No, Yes, Latest, Rebind
HDDL Auths . . . . . . . Y Y/N
Include in HDDL commit counts
Tablespace. . . . . . . Y Table. . . . . . . . . Y
Index . . . . . . . . . Y View . . . . . . . . . Y
Commit frequency count . 0-999 How often to insert commits
-------------------------------------------------------------------------------
Addtl object options . . N Y/N Edit HSTATS and other object options
Optional syntax text . . N Y/N Edit Optional syntaxIn the SET CURRENT SQLID to field, specify the value for the SET CURRENT SQLID statement that the DDL, HDDL, HTDDL (if you have applied SPE2005
), and MDDL commands produce for views and materialized query tables.The following table lists valid values for the SET CURRENT SQLID to field:
Value
Description
CREATEDBY or CREATOR
Indicates the authorization ID in the Db2 catalog
NONE or blank
Indicates that the statement is not included
<other>
Represents a variable that indicates any value
- Specify whether to display synonyms, packages, and plans in a mixed list.
Specify whether to include objects in the CREATE object statements that the HDDL and HTDDL (if you have applied SPE2005 ) commands produce.
The HDDL command uses a temp data set to build the resulting DDL. If you want to execute the HDDL line command, you must edit the Product Options File (POF) or create a new POF, as the values required to allocate the data set cannot be specified through the Catalog Manager interface.
The temp DATASET is allocated based on values in the POF data set for the following variables:
- ACTWRK_DATACLASS =
- ACTWRK_MGMTCLASS =
- ACTWRK_PRIQTY = 10 (recommended default value)
- ACTWRK_SECQTY = 2 (recommended default value)
- ACTWRK_STORCLASS =
- ACTWRK_UNIT = SYSALLDA (recommended default value)
The device where the data set is saved is defined according to the first POF variable, in the following order, that contains a valid value:
- ACTWRK_MGMTCLASS
- ACTWRK_STORCLASS
- ACTWRK_DATACLASS
- ACTWRK_UNIT
For details, see Creating-a-user-POF or Updating-a-user-POF.
(SPE2005) In the Package field, enter one of the following values to control the BIND PACKAGE commands added to the HDDL and HTDDL output:
Value
Description
N
No BIND PACKAGE statements are generated.
Y
BIND PACKAGE statements are generated for all versions of a package related to a changed object.
L
BIND PACKAGE statements are generated for the latest version of a package related to a changed object.
R
REBIND PACKAGE statements are generated for all package versions and types related to a changed object.
- (BMC.DB2.SPE2307) In the HDDL Auths field, type Y or N to specify whether to include GRANT statements in the HDDL and HTDDL output.
- In the Include in the HDDL commit counts fields, specify whether to generate a COMMIT statement after the number of CREATE object statements specified in the Commit frequency count field for table spaces, tables, views, and indexes.
- For objects for which you specified Y in the Include in the HDDL commit counts field, in the Commit frequency count field, specify the number of CREATE object statements to execute before inserting a COMMIT statement. Valid values are 0 through 999.
(BMC.DB2.SPE2110) In the Addtl object options field, type Y, and then press Enter.
The Additional Object Options panel is displayed.Additional Object Options 1 to 10 of 10
Command ===> Scroll ===> PAGE
Alter Add Columns. . . . N Y/N Generate DDL using ALTER ADD columns
HSTATS OPTIONS:
HSTATS objects . . . . . AP AP-Access Path, ALL-All statistics
Histogram. . . . . . . N Y/N Include Histogram Stats
Timestamp. . . . . . . . C C-Current , O-Original
For HSTATS insert commits after every:
Tablespace . . . . . . . Y Table. . . . . . . . . Y
Index. . . . . . . . . . Y Every 100 statements . YIn the Alter Add Columns field, specify whether to generate ALTER ADD DDL statements following a CREATE DDL statement for a table with a LOB or XML column. Select one of the following values:
Value
Description
N
Catalog Manager generates CREATE DDL statements that include all columns and does not generate ALTER ADD statements.
Y
Catalog Manager generates CREATE DDL statements followed by ALTER ADD statements for tables with a LOB or XML column. A COMMIT is also generated between the CREATE TABLE statement and ALTER ADD statements associated with that table.
(BMC.DB2.SPE2110) In the HSTATS objects field, specify which statistics are generated in the output. Select one of the following values:
Value
Description
AP
Catalog Manager generates DML statements for statistics used for generating access paths during the bind process.
ALL
Catalog Manager generates DML statements for all statistics regardless of their usage.
- (BMC.DB2.SPE2304) In the Histogram field, type Y or N to specify whether to include histogram statistics in the HSTATS output when ALL statistics are specified in the HSTATS objects field.
(BMC.DB2.SPE2310) In the Timestamp field, specify the value that the HSTATS command uses for STATSTIME in the generated DML. Enter one of the following values:
Value
Description
C
(Default) Uses the date and time the HSTATS command is run
O
Uses the date and time from the source Db2 subsystem tables
(BMC.DB2.SPE2110) In the following fields, type Y or N to specify when to insert a commit in the HSTATS output:
Field
Description
Tablespace
Specifies whether to insert a commit after each table space update
Index
Specifies whether to insert a commit after each index update
Table
Specifies whether to insert a commit after each table update
Every 100 statements
Specifies whether to insert a commit after every 100 statements
- Press END to return to the Object options panel.
- (Optional) To include user-defined command statements in generated DDL, in the Optional syntax text field, type Y, and then press Enter.
The Prefix/Suffix Options panel is displayed. For more information, see To include user-defined command statements in generated DDL. - Press END to return to the Options panel.
To include user-defined command statements in generated DDL
Use the following procedure to include user-defined command statements before and after DDL generated by the following commands:
- ALTER
- CREATE
- DDL
- MDDL
- HDDL
- From the Primary Menu panel, an object list panel, or a utility panel, on the Command line, type OPTIONS (OPT).
- Press Enter.
The Options panel is displayed. - In the Object selections field, type Y and then press Enter.
The Object Options panel is displayed. In the Optional syntax text field, type Y, and then press Enter.
The Prefix/Suffix Options panel is displayed.Prefix/Suffix Options 1 to 11 of 11
Command ===> Scroll ===> PAGE
Include prefix syntax. . . N (Y/N)
Provide up to 3 lines of comments/commands to be included prior to syntax
Line 1 --SET CURRENT APPLICATION COMPATIBILITY = 'V12R1M500';
Line 2
Line 3
Include suffix syntax. . . N (Y/N)
Provide up to 3 lines of comments/commands to be included after syntax
Line 1
Line 2
Line 3
To include user-defined command statements before the generated DDL, complete the following fields in the prefix section:
Field
Description
Include prefix syntax
Controls whether the commands are included before the generated DDL. The following table lists the valid values:
Value
Description
Y
Enables the inclusion of up to three lines of user-defined command statements before the generated DDL
N
Disables the inclusion of the user-defined command statements
Line 1
Enter command syntax to include before each stream of generated DDL statements.
Line 2
(Optional)
Enter command syntax to include before each stream of generated DDL statements.
Line 3
(Optional)
Enter command syntax to include before each stream of generated DDL statements.
To include user-defined command statements after the generated DDL, complete the following fields in the suffix section:
Field
Description
Include suffix syntax
Controls whether the commands are included after the generated DDL. The following table lists the valid values:
Value
Description
Y
Enables the inclusion of up to three lines of user-defined command statements after the generated DDL
N
Disables the inclusion of the user-defined command statements
Line 1
Enter command syntax to include after each stream of generated DDL statements.
Line 2
(Optional)
Enter command syntax to include after each stream of generated DDL statements.
Line 3
(Optional)
Enter command syntax to include after each stream of generated DDL statements.
- Press END to return to the Object Options panel.
- Press END to return to the Options panel.