Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Catalog Manager for Db2 13.1.

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.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*