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 SQL Explorer for Db2 13.1.

SQL Explorer and DB2 authority requirements

SQL Explorer uses IBM DB2 security to ensure that any access or updates to DB2 are appropriately authorized.

SQL Explorer uses the following authority:

  • Execution of an SQL statement via the SQLX edit macro or the online ad-hoc Explain interface uses the DB2 authority of your TSO ID.

The Explain component uses the following authority:

  • INSTALL SYSADM authority in the following situations:
    • When running Explain jobs online while being connected to the Data Collector
    • When running Explain jobs in batch mode from an APF-authorized library
  • Authority of your TSO ID in the following situations:
    • When running Explain jobs online without being connected to the Data Collector
    • When running Explain jobs in batch mode from a library that is not APF-authorized
Warning

Note

DB2 writes only to plan tables for the ID under which you are set. The Explain component initiates a SET CURRENT SQLID statement to the specified plan table owner in order to write to the plan tables.

If you receive a -553 SQL code when the SET CURRENT SQLID statement is issued against the plan table owner name, Explain processing continues (using your AUTHID) and writes to the authID.PLAN_TABLE. If needed, the Common Explain component of SQL Explorer dynamically builds the required plan tables in a BMC database.


(PTFs BQU1326 and BQU1107 applied)

Dynamic Explain requests now use the ADMIN_EXPLAIN_MAINT IBM DB2 stored procedure. This stored procedure can create or update Explain tables to the correct format for the version of DB2 that you are running. It can also create the indexes recommended by IBM on the Explain tables.

Ensure that the ADMIN_EXPLAIN_MAINT stored procedure is installed correctly for successful Explains.

Installing the ADMIN_EXPLAIN_MAINT stored procedure

For DB2 Version 11 or later, use the following procedure to install the ADMIN_EXPLAIN_MAINT stored procedure:

  1. Create a JCL startup procedure for the IBM z/OS Workload Manager (WLM) environment, if you do not already have one.
  2. Set up the IBM stored procedure using the DSNTESR member from the DB2 DSNSAMP data set. Perform the following steps:
    1. Create the following global temporary tables:
      • SYSIBM.EXPLAIN_MAINT_SUMMARY
      • SYSIBM.EXPLAIN_MAINT_SQL
      • SYSIBM.EXPLAIN_MAINT_TB_NOT_UPGRADED
    2. Define the SYSPROC.ADMIN_EXPLAIN_MAINT stored procedure by specifying your WLM environment and the COLLID for the DSN% packages.
    3. Issue a GRANT ALL to PUBLIC authority to the global temporary tables.
    4. Issue a GRANT EXECUTE to PUBLIC authority to the stored procedure.
    5. Remove the comments from the bind statement and run the bind for the DSNADMEM package, specify the DSNDBRM DB2 library, and perform the bind.
  3. Activate the WLM environment.
  4. Start the stored procedure.

For more information, see the IBM DB2 for z/OS installation documentation.

For information about the authorities you require for Workload Advisor Explains, see Required authorities for Workload Advisor Explains and the Index Advisor feature v12_1_SPE3_1803


Related topics

Explaining-and-executing-an-individual-SQL-statement
Migrating-access-path-statistics
Required-authorities-for-Explains-and-the-What-If-Index-feature



 

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

BMC AMI SQL Explorer for Db2 12.1