Creating a materialized query table (MQT)

Use the following procedure to create a materialized query table.

  1. On the BMC AMI Change Manager for Db2 Main Menu, select WORKID, and press Enter.
  2. On the WORKID Action Menu, type the name of the WORKID or type a wildcard pattern to display a list of work IDs. Then, select Specify Db2 definitions and press Enter.
  3. On the Object Specification panel, specify the name of a view (or use wildcard characters) and press Enter.
  4. On the Mixed List panel, type E in the Act column to edit a view (VW) object, and press Enter.
  5. On the View Detail panel, type S to select Create Materialized Query Table based on this view, and press END.
  6. On the Create MQT panel, specify the information about the materialized query table (MQT).
    1. Type the owner of the table.
    2. Type the name of the table.
    3. Type the name of an existing database and table space.
    4. Specify the attributes for the table.
    5. Specify whether the parent table space already exists in the Db2 catalog.
    6. To modify the columns in the table, type S to select Table Column List.

    7. To specify a comment for the table, type S to select Table Comment.

      Tip

      Some object names might be too long to be displayed on a panel. To enter a long object name, position the cursor on the object name and press the ZOOM (F4) key.

    8. Type S to select MQT Parameters.

  7. On the Create MQT Parameters panel, specify the parameters for the MQT.


    Parameter

    Action

    Maintained By

    Type SYSTEM or USER.

    Query Optimize

    Type one of the following:

    • ENABLE to specify that the table can be used to optimize queries.
    • DISABLE to specify that the table cannot be used to optimize queries.

    MQT has Explicit Column List

    Type Y or N

  8. To edit the text for the MQT , type S to select MQT Text
  9. To specify columns for the MQT, type S to select MQT Column List
  10. To edit the schema path, type S to select Schema Path
  11. To view the old text for the MQT , type S to select Old MQT Text

  12. Press END.Now that the MQT has been created you can analyze a work ID and execute a worklist.

    Important

    The products do not guarantee the correct ordering of a worklist if the MQTs contains a large number of interdependencies.


 

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