Checklist for configuring DB2 for z/OS

Follow this checklist when you are configuring a subsystem for the first time for use with InfoSphere® Optim™ Query Workload Tuner, Version 4.1.1.

About this task

If you configured your subsystem for use with InfoSphere Optim Query Workload Tuner, Version 4.1, you do not have to follow this checklist. Instead, you can migrate the configuration.
Important: InfoSphere Optim Query Workload Tuner does not support configurations for more than one version on a single subsystem. For example, the product does not support configuring a single subsystem for use with both version 4.1 and version 4.1.1.

Procedure

  1. ☐ Install the Data Studio client and the license activation kit.
  2. Optional: If your subsystem was configured for IBM® Optimization Service Center, and before configuring for version 4.1.1 you want to drop both the data objects from those earlier versions and the data in those objects, drop databases DB2OSC and DSNOSCDB.
    Important: This step is not required. Follow it only if you are sure that you want to delete the data that is in these databases.
  3. ☐ Ensure that you have the authorities or privileges that are required for configuring DB2® for z/OS® subsystems for tuning with InfoSphere Optim Query Workload Tuner.
    Task Authorities or privileges required
    Bind packages One of the following authorities and privileges:
    • SYSADM or DBADM authority
    • BINDADD privilege if required packages do not exist, and CREATEIN privilege on the schema
    • ALTERIN privilege on the schema if the required packages exist
    • BIND privilege on the required packages if they exist
    Free packages One of the following authorities and privileges:
    • Ownership of the packages
    • BINDAGENT privilege that is granted by the owner of the packages
    • SYSCTRL authority
    • SYSADM authority
    • PACKADM authority for the collection or for all collections
    Browse subsystem parameters Both of the following authorities and privileges:
    • EXECUTE privilege on the SYSPROC.DSNWZP stored procedure.
    • At least SQLADM system privilege
    Manage users One of the following privileges or authorities:
    • The privilege WITH GRANT OPTION on required packages
    • Ownership of the packages
    • SYSADM authority
    Create EXPLAIN tables If you want to create the EXPLAIN tables in a new database, one or more of the following privileges or authorities for creating the database:
    • CREATEDBA privilege
    • CREATEDBC privilege
    • SYSADM or SYSCTRL authority
    For creating the table space, one or more of the following privileges or authorities:
    • CREATETS privilege for the database
    • DBADM, DBCTRL, or DBMAINT authority for the database
    • SYSADM or SYSCTRL authority
    For creating the tables, one or more of the following privileges or authorities:
    • CREATETAB privilege for the database
    • DBADM, DBCTRL, or DBMAINT authority for the database
    • SYSADM or SYSCTRL authority
    Create aliases for existing EXPLAIN tables One of the following privileges or authorities:
    • The CREATEALIAS privilege
    • SYSADM or SYSCTRL authority
    • DBADM or DBCTRL authority on the database that contains the tables, if the aliases are for tables and the value of field DBADM CREATE AUTH on installation panel DSNTIPP is YES
  4. ☐ Ensure that the subsystem is configured to connect to other systems with TCP/IP.
  5. ☐ Start the Data Studio client and switch to the IBM Query Tuning perspective.
    1. In the top-right corner, click the Open Perspective icon (Open Perspective icon).
    2. Select Other.
    3. Select IBM Query Tuning and click OK.
  6. ☐ In the Data Source Explorer in the Data Studio client, create a connection to the DB2 for z/OS subsystem that you are configuring.
  7. ☐ In the Data Source Explorer, right-click the connection to the subsystem and select Connect.
  8. ☐ Configure the subsystem and activate the full set of tuning features by following either of these steps:
    • Run JCL to create EXPLAIN tables, create Query Tuner tables, and activate the full set of tuning features.
    • If you want to use the workflow assistant to configure the subsystem and activate the full set of tuning features, follow these steps.
      1. Optional: Create the following two RACF® groups and link your users to them.
        • DB2OSCA
        • DB2OE
        If you do not follow this step, individual users in the Data Studio client must unselect an option in the Data Studio client before attempting to tune SQL that runs on the subsystem. After starting the Data Studio client, each user must select Window > Preferences. Then, expand Data Management and select Query Tuner. The preference to deselect is labeled Allow internal processes to use the RACF IDs DB2OE and DB2OSCA.
      2. Ensure that the authorization ID that you are using to connect to the subsystem has one of the following authorities or privileges that are required for activating the full set of tuning features.
        • DB2 Version 9.1 for z/OS:
          • CREATEIN privilege on the schema DB2OE
          • SYSADM or SYSCTRL authority
        • DB2 10 for z/OS and later:
          • CREATEIN privilege on the schema DB2OE
          • SYSADM, SYSCTRL, or DBADM authority
      3. Bind or bind-copy IBM Data Server Driver for JDBC and SQLJ packages with necessary bind options to enable the selectivity override feature on DB2 for z/OS V11 new-function mode.
      4. In the Data Source Explorer in the Data Studio client, click the plus symbol to expand the connection to the subsystem. Right-click the icon for the subsystem and select Analyze and Tune > Activate Full Set of Tuning Features

        If the full set of tuning features for InfoSphere Optim Query Workload Tuner is not yet activated on the subsystem by this point, the Data Studio client activates it, if the license activation kit is installed in the directory where the Data Studio client is installed on your workstation.

      5. Right-click the icon for the subsystem and select Analyze and Tune > Configure for Tuning > Guided Configuration. The configuration wizard opens.

        The configuration wizard allows you to configure the subsystem for query tuning and query-workload tuning.

      6. Follow the instructions in the wizard.
  9. ☐ Verify that the subsystem is configured for query or query-workload tuning. In the Data Source Explorer, right-click the subsystem that you configured, and then click Analyze and Tune > Configure for Tuning > Advanced Configuration and Privilege Management.

    You can check the status of the Query Tuner packages, Query Tuner tables, EXPLAIN tables, and the query or query-workload tuning features in this window. Click the Detailed Report button to generate an HTML report that shows the status of these objects and features.

  10. ☐ If you want multiple people to use the same set of EXPLAIN tables when they are tuning, use one of these methods for sharing these tables.
  11. ☐ Set up the following stored procedures and grant the EXECUTE privilege on them to authorization IDs that will be used for tuning with InfoSphere Optim Query Workload Tuner.
    1. ☐ If you want reduce traffic on your network by causing some tuning tasks to run on the connected subsystem independent of the Data Studio client, follow these steps to enable the SYSPROC.OPT_EXECUTE_TASK stored procedure.
    2. ☐ If one or more users do not have the privilege to run the EXPLAIN STMTCACHE ALL statement dynamically, follow these steps to enable the SYSPROC.OPT_RUNSQL stored procedure.
    3. ☐ Install the SYSPROC.DSNAEXP stored procedure, if users do not have the privilege to explain SQL statements or the SQLADM authority (for DB2 10 for z/OS and later).
    4. ☐ Install the SYSPROC.DSNAHVPM stored procedure.
    5. ☐ Install the following two stored procedures to enable users to review zParms and to turn on tracing for the dynamic statement cache.
      • SYSPROC.DSNWZP
      • SYSPROC.DSNACCMD (for DB2 Version 9.1 for z/OS) or SYSPROC.ADMIN_COMMAND_DB2 (DB2 10 for z/OS and later)
    6. ☐ Install the SYSPROC.DSNUTILU stored procedure, so that users can run RUNSTATS jobs that are recommended by the Statistics Advisor and Workload Statistics Advisor.
    7. ☐ Install the SYSPROC.ADMIN_INFO_SQL stored procedure, so that users can collect information that IBM support requires for resolving PMRs about performance problems on DB2 10 for z/OS.
    8. ☐ Install the SYSPROC.SET_PLAN_HINT stored procedure, so that users can validate and deploy subsystem-level hints on DB2 10 for z/OS or later. For DB2 11 for z/OS new-function mode or later, this stored procedure also enables users to override the selectivity of predicates.
    9. ☐ Install the SYSPROC.ADMIN_COMMAND_DSN stored procedure, so that users can validate and deploy optimization hints. For DB2 11 for z/OS new-function mode or later, this stored procedure also enables users to override the selectivity of predicates.
  12. ☐ If you want users to be able to use the administrative task scheduler to schedule tuning tasks, such as capturing or explaining SQL statements, set up the administrative task scheduler.
  13. ☐ If users will need to capture SQL statements from the dynamic statement cache on all subsystems of a data sharing group, configure a data sharing group.
  14. Ensure that your users have the authorities or privileges required for tuning SQL.

Feedback