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.

Before you begin

If you are configuring DB2 10 for z/OS (New Function Mode), apply the APAR that is documented here: http://www.ibm.com/support/docview.wss?uid=swg1PM79161

About this task

If you configured your subsystem for use with InfoSphere Optim Query Workload Tuner, Version 3.2, 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 3.2 and version 4.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 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 MONITOR1 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 product license by following either of these steps:
    • Run JCL to create EXPLAIN tables, create Query Tuner tables, and activate the product license.
    • If you want to use the workflow assistant to configure the subsystem and activate the product license, follow these steps.
      1. Create the following two RACF groups and link your users to them.
        • DB2OSCA
        • DB2OE
      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 product license.
        • 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. 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 > Configure for Tuning > Guided Configuration. The configuration wizard opens.

        If the license for InfoSphere Optim Query Workload Tuner is not yet active on the subsystem by this point, the Data Studio client installs the license on the subsystem and activates the license, if the license activation kit is installed in the directory where the Data Studio client is installed on your workstation. The configuration wizard allows you to configure the subsystem for query tuning and query-workload tuning.

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

  10. ☐ If you want multiple people to use the same set of EXPLAIN tables when they are tuning, use one of 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)
        Note: For DB2 11 for z/OS, this stored procedure is a requirement for the Statistics Collection Advisor.
    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. For DB2 11 for z/OS, this stored procedure also is a requirement for the Statistics Collection 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.
    9. ☐ Install the SYSPROC.ADMIN_COMMAND_DSN stored procedure, so that users can validate and deploy optimization hints
  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.

What to do next

In the Data Source Explorer in the Data Studio client, expand the connection to the subsystem. Right-click the subsystem and select Analyze and Tune > Configure for Tuning > Advanced Configuration and Privilege Management.. In the window that opens, click the Features tab. A green circle around a white checkmark should be next to each feature. If instead red circles with white Xs are next to each feature, click the Detailed Report button to see a list of the problems with the configuration.
Attention: If you are connected to a subsystem running DB2 11 for Z/OS (Compatibility Mode), all of the icons in the last three lists in the Features section will be red circles with white Xs. The features might still be functional. Click the Detailed Report button to find out. A problem in this beta release causes the red icons to appear even if the features are functional.