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

Related topics

Explaining-and-executing-an-individual-SQL-statement

Migrating-access-path-statistics

Required-authorities-for-Explains-and-the-What-If-Index-feature


Important

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 BMC Explain component of SQL Explorer dynamically builds the required plan tables in a BMC database.

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.

Specifying an Explain user ID for batch Explain

The Batch Explain process uses the Db2 Install SYSADM when the STEPLIB data sets are APF-authorized. Alternatively, you can specify a valid user ID for Batch Explain to use that has the appropriate Db2 authorization for the Explains, such as SYSADM.

To specify an Explain user ID for Batch Explain, follow these steps:

  1. Copy the members PSSDEFL and #PSSDEFL from the BMCSAMP data set to your own data set.

  2. Modify the EXPUSRID field in your copy of PSSDEFL with a valid user ID that has the required Db2 authorizations. We recommend SYSADM. 

    Important

     Make sure to include any previous values for the STOGROUP or database prefix that you might have set in a previous copy of this module. 

  3. Modify and submit the copy of the #PSSDEFL JCL member to assemble and link the PSSDEFLT module to the UBMCLINK data set.

Installing the ADMIN_EXPLAIN_MAINT stored procedure

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 by using the DSNTESR member from the Db2 DSNSAMP data set as follows:
    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.
    6. Run the bind for the DSNADMEM package.
    7. 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.

 

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