Skip to Content

 

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

Applying SQL model statements

From any list panel, you can apply an SQL model statement that contains host variables to the list objects by using the APPLY command.

For the host variables in the SQL model statement, Catalog Manager substitutes values in the Db2 catalog row indicated by the list entry and creates one statement for each object in the list. The host variables must be Db2 catalog column names.

By using the APPLY command, you can significantly reduce the time required to perform the same action against a group of objects. For example, using one model statement, you can add the RESTRICT ON DROP attribute to a group of tables.

Related topic

Generating-editing-and-executing-SQL

The following procedure combines an SQL model statement with a list of tables to generate SQL with host variables that adds the RESTRICT ON DROP attribute to a group of tables.

To apply SQL statements to lists

  1. Create an SQL model statement in the Catalog Manager SQL_Table.For this example, create a member called RESTRICT, which contains two host variables, :CREATOR and :NAME. The host variables represent the Db2 catalog columns that contain the data to be substituted for the variables.

    Success

    Tip

    If you omit the semicolon (;) at the end of the SQL model statement, Catalog Manager supplies it.

  2. Generate an object list. For information, see Generating-lists-in-Catalog-Manager.
  3. On the object list, in the Cmd column, type X beside objects that you want to exclude from processing and press Enter.

  4. On the Command line, type APPLY memberName ALL and press Enter.In this example, the SQL_Table memberName is RESTRICT.

    Catalog Manager displays the Confirm APPLY SQL MODEL panel, which contains the SQL model statement at the bottom of the panel .

    DEFF-R -------------------  Confirm APPLY SQL MODEL  -------------------------
    Command ===>

    Edit SQL Model  . . . . . N    (Y/N)      Current SQLID : RDACRJ2
    Replace with SQL Member .
    Save model in SQL table . N    (Y/N)
    Apply to list objects . . N    (Y/N)
    Name of saved SQL . . . . RDACRJ2.RESTRICT
    ------------------------------------  SQL  -----------------------------------
                                                                       More:     +
    ALTER TABLE :CREATOR . :NAME ADD RESTRICT ON DROP;
    ******************************** BOTTOM OF DATA ******************************
  5. In the Confirm APPLY SQL MODEL panel, you can edit and save the SQL, and apply the SQL to the objects in the list.
    1. In the Edit SQL Model field, type Y or N to edit the SQL and then press Enter.An ISPF edit panel is displayed, from which you can edit the SQL. Press END to return to the Confirm APPLY SQL MODEL panel.
    2. In the Replace with SQL Member field, type the name of another member to replace the existing member and then press Enter.
    3. In the Save model in SQL table field, type Y or N to save the SQL and then press Enter.
    4. In the Name of saved SQL field, specify the owner and name for the SQL and then press Enter.

    5. In the Apply to list objects field, type Y or N to apply the SQL model statement to list objects that were not excluded and then press Enter.Catalog Manager displays the Confirm SQL panel (the following figure). Values from the Db2 catalog have been substituted for the host variables.

      DEFF-R --------------------------  Confirm SQL  ------------ Row 1 to 15 of 235
      Command ===>                                                  Scroll ===> PAGE
                                                                                      
      Current SQLID. . . . . . . .  MVSSXS2                                          
      Edit Options . . . . . . . .  N         Y/N Modify SQL processing options      
      Edit SQL . . . . . . . . . .  N         Y/N Edit SQL before executing          
      Save in SQL table. . . . . .  N         A/Y/R/N A/Y-Append, R-Replace          
       Name of saved data. . . . .  20220222_094815                                  
      Save in PDS. . . . . . . . .  N         Y/N Save in PDS                        
       PDS(member) . . . . . . . .  ACT.V12.DATABASE(TEST)                           
                                                                                    
      Execute SQL. . . . . . . . .  N         Remote Db2 . NONE      Enter ? for list
      ------------------------------------  SQL  ------------------------------------
      ALTER TABLE  QZU    .   QZUT01_DCI08S01
                                   ADD RESTRICT ON DROP;
      ALTER TABLE  QZU    .   QZUT02_DCI08S01
                                   ADD RESTRICT ON DROP;
      ALTER TABLE  QZU    .   QZUT03_DCI08S01
                                   ADD RESTRICT ON DROP;
  6. On the Confirm SQL panel, you can edit and save the SQL and then execute it.

    1. (Optional) From the Command line, issue the SET sqlid command to change the value of the current SQLID.

      Warning

      Note

      The ID shown in the Current SQLID field must have the proper authority to perform the specified SQL ALTER statement. If you hold a primary- or secondary-authorization ID that has the proper authority, you can change the Current SQLID to that authorization ID and complete the CREATE. To change the Current SQLID, use the SET command.

    2. (Optional) In the Edit options field, type Y to modify the default values for the options on the Confirm SQL panel and then press Enter.The Options panel is displayed. In the Edit SQL and Confirm options field, type Y to display the options for the Confirm SQL panel. Press END to return to the Confirm SQL panel.
    3. (Optional) In the Edit SQL field, type Y to invoke an ISPF edit session to edit the SQL statement and then press Enter.Press END to save the SQL and return to the Confirm SQL panel.
    4. (Optional) In the Save SQL field, type Y to save the SQL in the Catalog Manager SQL_Table. In the Name of saved SQL field, type a name for the SQL and then press Enter.The saved SQL uses the ID displayed in the Current SQLID field as the object qualifier. If the SQL is not saved, the ID in the Current SQLID is used only to identify Db2 authority.

    5. (BMC.DB2.SPE2210)

       (Optional) In the Remote Db2 field, perform one of the following steps to execute the SQL displayed on a different Db2 subsystem:

      • Enter a Db2 subsystem ID.
      • To select a Db2 subsystem from a list, type ? and then press Enter.
    6. (Optional) In the Execute field, type Y to execute the SQL displayed on the Confirm SQL panel and then press Enter.The SQL Progress Indicator panel is displayed. The panel automatically refreshes to display the status of the SQL that is being executed.



 

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

BMC AMI Catalog Manager for Db2 12.1