Setting object use options

Follow these steps to set the object use options:

1. On the Primary Menu panel, an object list panel, or a utility panel, on the Command line, type OPTIONS (OPT).
2. Press Enter.

   The Options panel is displayed.

3. 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 syntax                  

4. In 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

5. Specify whether to display synonyms, packages, and plans in a mixed list.

6. Specify whether to include objects in the CREATE object statements that the HDDL and HTDDL (if you have applied SPE2005 ) commands produce.

   Note

   If you exclude tables from the HDDL, Catalog Manager also excludes materialized query tables (MQTs).

   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:

   1. ACTWRK_MGMTCLASS

   2. ACTWRK_STORCLASS

   3. ACTWRK_DATACLASS

   4. ACTWRK_UNIT

   For details, see Creating a user POF or Updating a user POF.

7. (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.

8. (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.
9. 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.

10. 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.

11. (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 . Y                  

12. In 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.

13. (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.

14. (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.

15. (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

16. (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

17. Press END to return to the Object options panel.

18. (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.

19. 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

1. From the Primary Menu panel, an object list panel, or a utility panel, on the Command line, type OPTIONS (OPT).
2. Press Enter.
   The Options panel is displayed.
3. In the Object selections field, type Y and then press Enter.
   The Object Options panel is displayed.

4. 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                                                                         
                                                                                   

5. 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.

6. 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.

7. Press END to return to the Object Options panel.
8. Press END to return to the Options panel.