Topics for installing, migrating to, and configuring for IOQWT 3.1.1


Installing IBM InfoSphere Optim Query Workload Tuner, Version 3.1.1

Installing IBM InfoSphere Optim Query Workload Tuner, Version 3.1.1 installs two components on your workstation:

IBM Data Studio full client, Version 3.1.1
IBM Data Studio provides an integrated development environment for robust database application development and database management for database servers. It includes a limited set of features for tuning that are free of charge.
A License Activation Kit for InfoSphere Optim Query Workload Tuner, Version 3.1.1
This kit allows you to activate the full set of features for tuning query workloads and single SQL statements. When you use the IBM Data Studio full client to start tuning, the client will install and activate a license on the connected DB2 for Linux, UNIX, and Windows database or DB2 for z/OS subsystem. When you and other people subsequently connect to that database through the IBM Data Studio full client or administration client, you and they can use the full set of tuning features.

There are two different License Activation Kits:

Your product DVD or the compressed file that you downloaded from IBM Passport Advantage contains the kit that corresponds to the product that you purchased.

You can install both components or only one of them, depending on your requirements.

You can also choose whether to install these components through a wizard or silently with a response file.

Installing Data Studio and the License Activation Kit for IBM InfoSphere Optim Query Workload Tuner through a wizard

Before you begin

Ensure that your workstation meets the system requirements.

About this task

Procedure

  1. On the product DVD or in the extracted content of the compressed file downloaded from IBM Passport Advantage, double-click either of these files, depending on your operating system:
  2. In the launchpad, select Install Product and select the type of installation that you want to perform.
  3. On the Select Packages page of the wizard, select both packages.
  4. On the Install Packages page, follow either of these steps:
  5. Finish the remaining steps in the wizard.

What to do next

If you need to configure DB2 for z/OS subsystems for tuning, see Configuring a DB2 for z/OS subsystem for query and query-workload tuning with InfoSphere Optim Query Workload Tuner.

If your subsystems were already configured for tuning with IBM InfoSphere Optim Query Workload Tuner for DB2 for z/OS, Version 3.1 and a license for that version is active on the subsystem, you can start IBM Data Studio full client and start tuning.

Installing Data Studio and the License Activation Kit for IBM InfoSphere Optim Query Workload Tuner silently

Before you begin

Ensure that any version of IBM Installation Manager from 1.3.3 through 1.5 is installed on your workstation.

About this task

This task involves generating a response file on one workstations, and then performing the installation with the response file on other workstations.

Restrictions:

Procedure

  1. Generate a response file:
    1. At a command line, change to the eclipse subdirectory in the directory where IBM Installation Manager is installed.
      • On Windows, for example: cd C:\Program Files\IBM\Installation Manager\eclipse
      • On Linux, for example: cd /opt/IBM/Installation Manager/eclipse
    2. Run one of these commands to start IBM Installation Manager.
      Note:
      • The commands as shown will not install InfoSphere Optim Query Workload Tuner on your workstation, and will generate a response file only, in addition to a log file. If you want to install InfoSphere Optim Query Workload Tuner on your workstation, remove the -skipInstall option from the command that you run.
      • Ensure the file paths you enter exist; Installation Manager will not create directories for the response file and the log file.

      If the users on the workstations where you plan to install IBM InfoSphere Optim Query Workload Tuner have administrative rights:

      • For Windows: IBMIM.exe -record <response file and path name> -log <log file and path name> -skipInstall <full path name of a writable directory>
      • For Linux: IBMIM -record < full path name of the response file> -log <log file and path name> -skipInstall < full path name of a writable directory>

      If the users on the workstations where you plan to install IBM InfoSphere Optim Query Workload Tuner do not have administrative rights:

      • For Windows: userinst.exe -record <response file and path name> -log <log file and path name> -skipInstall <full path name of a writable directory>
      • For Linux: userinst -record < full path name of the response file> -log <log file and path name> -skipInstall <full path name of a writable directory>
    3. In IBM Installation Manager, select File > Preferences.
    4. On the Repositories page, add the location of the diskTag.inf files for both IBM Data Studio full client, Version 3.1.1 and the IBM InfoSphere Optim Query Workload Tuner for DB2 for z/OS License Activation Kit, Version 3.1.1. Then, click OK.

      The path to the diskTag.inf file for IBM Data Studio full client:

      • On Windows operating systems: <Install media root>\disk1\diskTag.inf
      • On Linux operating systems: <Install media root>/disk1/diskTag.inf

      The path to the diskTag.inf file for the license activation kit:

      • On Windows operating systems: <Install media root>\oqwt\diskTag.inf
      • On Linux operating systems: <Install media root>/oqwt/diskTag.inf
    5. In the main screen of IBM Installation Manager, click Install.
    6. On the Install Packages page, follow either of these steps. The results of these steps apply to the workstation on which you are generating the response file, if you did not use the -skipInstall option in step b; the results of these steps also apply on the workstations where you perform the silent installations.
      • If the InfoSphere Optim Query Tuner client, Version 3.1 or IBM Data Studio full client, Version 3.1 is installed and you want to upgrade to IBM Data Studio full client, Version 3.1.1, select the package group in which the product that you want to replace appears. The Installation Manager wizard will uninstall that product before installing IBM Data Studio full client, Version 3.1.1.
        Note:
        InfoSphere Optim Query Tuner client, Version 3.1 and IBM Data Studio full client, Version 3.1 could be in separate package groups or the same package group. If they are in separate package groups, choose one of the package groups. Installation Manager will not uninstall the client that is in the other package group.
      • If the InfoSphere Optim Query Tuner client, Version 3.1 or IBM Data Studio full client, Version 3.1 is not installed, create a new package group.
    7. Finish the remaining steps in the wizard.
  2. Transfer the response file to a workstation where you want to install IBM InfoSphere Optim Query Workload Tuner for DB2 for z/OS, Version 3.1.1. Ensure that the version of IBM Installtion Manager that you used to generate the response file is installed on the workstation where you want to run the silent installation. Ensure that the repositories that the response file specifies are accessible by IBM Installation Manager. For example, if the response file points to the diskTag.inf files that are on the product DVD, place the product DVD in the DVD drive of the workstation before starting the silent installation.
  3. 3. Perform the silent installation.
    1. At a command line, change to the eclipse subdirectory in the directory where IBM Installation Manager is installed.
      • On Windows, for example: cd C:\Program Files\IBM\Installation Manager\eclipse
      • On Linux, for example: cd /opt/IBM/Installation Manager/eclipse
    2. Run one of these commands.
      Note:
      The -log option creates a new log file, so the directory that you specify must be writable.

      If you generated the response file with any version of IBM Installation Manager from 1.3.3 to 1.4.2:

      • For Windows: IBMIMc.exe --launcher.ini silent-install.ini -input <response file path and name> -log <log file path and name>
      • For Linux: ./IBMIM --launcher.ini silent-install.ini -input <response file path and name> -log <log file path and name>

      If you generated the response file with IBM Installation Manager 1.4.3 or 1.5:

      • For Windows: imcl.exe -acceptLicense input <response file path and name> -log <log file path and name>
      • For Linux: ./imcl -acceptLicense input <response file path and name> -log <log file path and name>

What to do next

If you need to configure DB2 for z/OS subsystems for tuning, see Configuring a DB2 for z/OS subsystem for query and query-workload tuning with InfoSphere Optim Query Workload Tuner.

If your subsystems were already configured for tuning with IBM InfoSphere Optim Query Workload Tuner for DB2 for z/OS, Version 3.1 and a license for that version is active on the subsystem, you can start IBM Data Studio full client and start tuning.

Installing only the IBM Data Studio full client, Version 3.1.1 for IBM InfoSphere Optim Query Workload Tuner through a wizard

Before you begin

Ensure that your workstation meets the system requirements.

About this task

You might want to install only the IBM Data Studio full client, Version 3.1.1 in either of these situations:

Procedure

  1. On the product DVD or in the extracted content of the compressed file downloaded from IBM Passport Advantage, double-click either of these files, depending on your operating system:
  2. In the launchpad, select Install Product and select the type of installation that you want to perform.
  3. On the Select Packages page of the wizard, select the package for the IBM Data Studio full client, Version 3.1.1.
  4. On the Install Packages page, follow either of these steps:
  5. Finish the remaining steps in the wizard.

What to do next

If you need to configure DB2 for z/OS subsystems for tuning, see Configuring a DB2 for z/OS subsystem for query and query-workload tuning with InfoSphere Optim Query Workload Tuner.

If your subsystems were already configured for tuning with IBM InfoSphere Optim Query Workload Tuner for DB2 for z/OS, Version 3.1 and a license for that version is active on the subsystem, you can start IBM Data Studio full client and start tuning.

Installing only the IBM Data Studio full client, Version 3.1.1 for IBM InfoSphere Optim Query Workload Tuner silently

Before you begin

Ensure that any version of IBM Installation Manager from 1.3.3 through 1.5 is installed on your workstation.

About this task

You might want to install only the IBM Data Studio full client, Version 3.1.1 in either of these situations:

This task involves generating a response file on one workstations, and then performing the installation with the response file on other workstations.

Restrictions:

Procedure

  1. Generate a response file:
    1. At a command line, change to the eclipse subdirectory in the directory where IBM Installation Manager is installed.
      • On Windows, for example: cd C:\Program Files\IBM\Installation Manager\eclipse
      • On Linux, for example: cd /opt/IBM/Installation Manager/eclipse
    2. Run one of these commands to start IBM Installation Manager.
      Note:
      • The commands as shown will not install the IBM Data Studio full client, Version 3.1.1 on your workstation, and will generate a response file only, in addition to a log file. If you want to install the IBM Data Studio full client, Version 3.1.1 on your workstation, remove the -skipInstall option from the command that you run.
      • Ensure the file paths you enter exist; Installation Manager will not create directories for the response file and the log file.

      If the users on the workstations where you plan to install the IBM Data Studio full client, Version 3.1.1 have administrative rights:

      • For Windows: IBMIM.exe -record <response file and path name> -log <log file and path name> -skipInstall <full path name of a writable directory>
      • For Linux: IBMIM -record < full path name of the response file> -log <log file and path name> -skipInstall < full path name of a writable directory>

      If the users on the workstations where you plan to install the IBM Data Studio full client, Version 3.1.1 do not have administrative rights:

      • For Windows: userinst.exe -record <response file and path name> -log <log file and path name> -skipInstall <full path name of a writable directory>
      • For Linux: userinst -record < full path name of the response file> -log <log file and path name> -skipInstall <full path name of a writable directory>
    3. In IBM Installation Manager, select File > Preferences.
    4. On the Repositories page, add the location of the diskTag.inf file for the IBM Data Studio full client, Version 3.1.1. Then, click OK.

      The path to the diskTag.inf file for IBM Data Studio full client:

      • On Windows operating systems: <Install media root>\disk1\diskTag.inf
      • On Linux operating systems: <Install media root>/disk1/diskTag.inf
    5. In the main screen of IBM Installation Manager, click Install.
    6. On the Select Packages page of the wizard, select the package for the IBM Data Studio full client.
    7. On the Install Packages page, follow either of these steps. The results of these steps apply to the workstation on which you are generating the response file, if you did not use the -skipInstall option in step b; the results of these steps also apply on the workstations where you perform the silent installations.
      • If the InfoSphere Optim Query Tuner client, Version 3.1 or IBM Data Studio full client, Version 3.1 is installed and you want to upgrade to IBM Data Studio full client, Version 3.1.1, select the package group in which the product that you want to replace appears. The Installation Manager wizard will uninstall that product before installing IBM Data Studio full client, Version 3.1.1.
        Note:
        InfoSphere Optim Query Tuner client, Version 3.1 and IBM Data Studio full client, Version 3.1 could be in separate package groups or the same package group. If they are in separate package groups, choose one of the package groups. Installation Manager will not uninstall the client that is in the other package group.
      • If the InfoSphere Optim Query Tuner client, Version 3.1 or IBM Data Studio full client, Version 3.1 is not installed, create a new package group.
    8. Finish the remaining steps in the wizard.
  2. Transfer the response file to a workstation where you want to install the IBM Data Studio full client, Version 3.1.1. Ensure that the version of IBM Installtion Manager that you used to generate the response file is installed on the workstation where you want to run the silent installation. Ensure that the repositories that the response file specifies are accessible by IBM Installation Manager. For example, if the response file points to the diskTag.inf file that is on the product DVD, place the product DVD in the DVD drive of the workstation before starting the silent installation.
  3. 3. Perform the silent installation.
    1. At a command line, change to the eclipse subdirectory in the directory where IBM Installation Manager is installed.
      • On Windows, for example: cd C:\Program Files\IBM\Installation Manager\eclipse
      • On Linux, for example: cd /opt/IBM/Installation Manager/eclipse
    2. Run one of these commands.
      Note:
      The -log option creates a new log file, so the directory that you specify must be writable.

      If you generated the response file with any version of IBM Installation Manager from 1.3.3 to 1.4.2:

      • For Windows: IBMIMc.exe -launcher.ini silent-install.ini -input <response file path and name> -log <log file path and name>
      • For Linux: ./IBMIM -launcher.ini silent-install.ini -input <response file path and name> -log <log file path and name>

      If you generated the response file with IBM Installation Manager 1.4.3 or 1.5:

      • For Windows: imcl.exe -acceptLicense input <response file path and name> -log <log file path and name>
      • For Linux: ./imcl -acceptLicense input <response file path and name> -log <log file path and name>

What to do next

If you need to configure DB2 for z/OS subsystems for tuning, see Configuring a DB2 for z/OS subsystem for query and query-workload tuning with InfoSphere Optim Query Workload Tuner.

If your subsystems were already configured for tuning with IBM InfoSphere Optim Query Workload Tuner for DB2 for z/OS, Version 3.1 and a license for that version is active on the subsystem, you can start IBM Data Studio full client and start tuning.

Installing only the license activation kit for IBM InfoSphere Optim Query Workload Tuner, Version 3.1.1 through a wizard

About this task

You might want to install only the license activation kit if the IBM Data Studio full client, Version 3.1.1 is installed on your workstation and you plan to activate licenses for IBM InfoSphere Optim Query Workload Tuner for DB2 for z/OS, Version 3.1.1, so that everyone who connects to your subsystems through an IBM Data Studio client can access the full set of tuning features for DB2 for z/OS.

Procedure

  1. On the product DVD or in the extracted content of the compressed file downloaded from IBM Passport Advantage, double-click either of these files, depending on your operating system:
  2. In the launchpad, select Install Product and select the type of installation that you want to perform.
  3. On the Select Packages page of the wizard, select the package for the license activation kit.
  4. 4. On the Install Packages page, select the package group in which IBM Data Studio full client, Version 3.1.1 is installed.
  5. Finish the remaining steps in the wizard.

What to do next

If you need to configure DB2 for z/OS subsystems for tuning, see Configuring a DB2 for z/OS subsystem for query and query-workload tuning with InfoSphere Optim Query Workload Tuner.

Installing only the license activation kit for IBM InfoSphere Optim Query Workload Tuner, Version 3.1.1 silently

Before you begin

Ensure that any version of IBM Installation Manager from 1.3.3 through 1.5 is installed on your workstation.

About this task

You might want to install only the license activation kit if the IBM Data Studio full client, Version 3.1.1 is installed on your workstation and you plan to activate licenses for IBM InfoSphere Optim Query Workload Tuner for DB2 for z/OS, Version 3.1.1, so that everyone who connects to your subsystems through an IBM Data Studio client can access the full set of tuning features for DB2 for z/OS.

This task involves generating a response file on one workstations, and then performing the installation with the response file on other workstations.

Restrictions:

Procedure

  1. Generate a response file:
    1. At a command line, change to the eclipse subdirectory in the directory where IBM Installation Manager is installed.
      • On Windows, for example: cd C:\Program Files\IBM\Installation Manager\eclipse
      • On Linux, for example: cd /opt/IBM/Installation Manager/eclipse
    2. Run one of these commands to start IBM Installation Manager.
      Note:
      • The commands as shown will not install the license activation kit on your workstation, and will generate a response file only, in addition to a log file. If you want to install the license activation kit on your workstation, remove the -skipInstall option from the command that you run.
      • Ensure the file paths you enter exist; Installation Manager will not create directories for the response file and the log file.

      If the users on the workstations where you plan to install the license activation kit have administrative rights:

      • For Windows: IBMIM.exe -record <response file and path name> -log <log file and path name> -skipInstall <full path name of a writable directory>
      • For Linux: IBMIM -record < full path name of the response file> -log <log file and path name> -skipInstall < full path name of a writable directory>

      If the users on the workstations where you plan to install the license activation kit do not have administrative rights:

      • For Windows: userinst.exe -record <response file and path name> -log <log file and path name> -skipInstall <full path name of a writable directory>
      • For Linux: userinst -record < full path name of the response file> -log <log file and path name> -skipInstall <full path name of a writable directory>
    3. In IBM Installation Manager, select File > Preferences.
    4. On the Repositories page, add the location of the diskTag.inf file for the license activation kit. Then, click OK.

      The path to the diskTag.inf file for the license activation kit:

      • On Windows operating systems: <Install media root>\oqwt\diskTag.inf
      • On Linux operating systems: <Install media root>/oqwt/diskTag.inf
    5. In the main screen of IBM Installation Manager, click Install.
    6. On the Select Packages page of the wizard, select the package for the license activation kit.
    7. 4. On the Install Packages page, select the package group in which IBM Data Studio full client, Version 3.1.1 is installed.
    8. Finish the remaining steps in the wizard.
  2. Transfer the response file to a workstation where you want to install the license activation kit. Ensure that the version of IBM Installtion Manager that you used to generate the response file is installed on the workstation where you want to run the silent installation. Ensure that the repositories that the response file specifies are accessible by IBM Installation Manager. For example, if the response file points to the diskTag.inf file that is on the product DVD, place the product DVD in the DVD drive of the workstation before starting the silent installation.
  3. 3. Perform the silent installation.
    1. At a command line, change to the eclipse subdirectory in the directory where IBM Installation Manager is installed.
      • On Windows, for example: cd C:\Program Files\IBM\Installation Manager\eclipse
      • On Linux, for example: cd /opt/IBM/Installation Manager/eclipse
    2. Run one of these commands.
      Note:
      The -log option creates a new log file, so the directory that you specify must be writable.

      If you generated the response file with any version of IBM Installation Manager from 1.3.3 to 1.4.2:

      • For Windows: IBMIMc.exe -launcher.ini silent-install.ini -input <response file path and name> -log <log file path and name>
      • For Linux: ./IBMIM -launcher.ini silent-install.ini -input <response file path and name> -log <log file path and name>

      If you generated the response file with IBM Installation Manager 1.4.3 or 1.5:

      • For Windows: imcl.exe -acceptLicense input <response file path and name> -log <log file path and name>
      • For Linux: ./imcl -acceptLicense input <response file path and name> -log <log file path and name>

What to do next

If you need to configure DB2 for z/OS subsystems for tuning, see Configuring a DB2 for z/OS subsystem for query and query-workload tuning with InfoSphere Optim Query Workload Tuner.

Migrating from the InfoSphere Optim Query Tuner client to the IBM Data Studio full client, Version 3.1.1

The InfoSphere Optim Query Tuner client is not a part of IBM InfoSphere Optim Query Workload Tuner, Version 3.1.1. You must export your Query Tuner projects and install the IBM Data Studio full client into the package group where the InfoSphere Optim Query Tuner client is located.

About this task

The IBM Data Studio full client, Version 3.1.1 allows you to connect to DB2 databases and subsystems where licenses for IBM InfoSphere Optim Query Workload Tuner, Version 3.1 or 3.1.1 are active, and to use the full set of features for tuning queries and query workloads.

If you install the license activation kit for either IBM InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows, Version 3.1.1 or IBM InfoSphere Optim Query Workload Tuner for DB2 for z/OS, Version 3.1.1 together with the IBM Data Studio full client, Version 3.1.1, you can activate licenses for those products on DB2 databases and subsystems. Activating a license on a DB2 database or subsystem allows anybody with access and who is using IBM Data Studio full client or administration client, Version 3.1.1 to use the full set of features for tuning queries and query workloads.

Procedure

To migrate from the InfoSphere Optim Query Tuner client to the IBM Data Studio full client, Version 3.1.1:

  1. Optional: Export your Query Tuner projects from the InfoSphere Optim Query Tuner client.
    1. In the InfoSphere Optim Query Tuner client, select File > Export.
    2. In the Export window, expand Query Tuner and select Projects.
    3. Select the projects that you want to export, select the directory to which you want to export them, and click Finish.
  2. Follow either of these steps:
  3. Start the IBM Data Studio full client. When Data Studio asks you to select a workspace, select the workspace that you used with the InfoSphere Optim Query Tuner client.
  4. If you find any problems with your Query Tuner projects, delete the ones in error and import them from the directory that you exported them to in step 1.

Migrating from IBM(r) Optimization Service Center to InfoSphere Optim(tm) Query Workload Tuner for DB2 for z/OS(r)

Use this roadmap as an overview for the tasks to migrate from IBM® Optimization Service Center to InfoSphere Optim™ Query Workload Tuner for DB2 for z/OS®.

Before you begin

About this task

The migration from Optimization Service Center to InfoSphere Optim Query Workload Tuner for DB2 for z/OS includes installing InfoSphere Optim Query Workload Tuner for DB2 for z/OS and configuring your DB2® subsystems for query tuning and query-workload tuning.

Procedure

To migrate from Optimization Service Center to InfoSphere Optim Query Workload Tuner for DB2 for z/OS:

  1. If you want to reuse existing projects, export those projects by following these steps in Optimization Service Center:
    1. Click Project > Export.
    2. In the Export Project window, select the project to export.
    3. Select the directory to contain the exported project.
    4. Click Finish.
    The exported project is stored in a compressed file that you can import later.
  2. Follow these steps to export individual workloads.
    1. Open the Workload List view.
    2. Right-click the workload to export and select Export.
    3. Specify the amount of information for the exported workload to contain.
    4. In the File field, type the path and name for the file to contain the workload. The extension must be .zip. If you do not specify a path, Optimization Service Center saves the file in the directory of the current project.
  3. Uninstall Optimization Service Center.
    1. On your Windows desktop, click Start > Programs > IBM Optimization Service Center for DB2 for z/OS > Uninstall Optimization Service Center. The Optimization Service Center Uninstaller wizard opens.
    2. Click Next, and click Uninstall. The uninstallation process runs. When it completes, Optimization Service Center is no longer installed on the workstation.
    3. Click OK.
    The exported workload is stored in a compressed file that you can import later.
  4. Follow either of these steps:
  5. Configure the DB2 for z/OS subsystem for query tuning either from the client or by running a JCL job:

What to do next

To start tuning in the Query Tuner Workflow Assistant:

  1. Open the IBM Data Studio client.
  2. In the Data Source Explorer, open the connection to the subsystem.
  3. Expand the connection, so that the subsystem is visible.
  4. Right-click the subsystem and click Start Tuning.

Configuring DB2 databases and subsystems for use with IBM InfoSphere Optim Query Workload Tuner

You must enable certain facilities and objects on a DB2 database or subsystem before you can tune the SQL statements and query workloads that run on that database or subsystem.

Configuring DB2 for Linux, UNIX, and Windows databases for use with IBM InfoSphere Optim Query Workload Tuner

You must configure your DB2 for Linux, UNIX, and Windows database before you start tuning SQL statements.

The following objects are created on the database during the configuration process:

The license for InfoSphere Optim Query Workload Tuner
If you are using the IBM Data Studio full client, Version 3.1.1, and the IBM InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows License Activation Kit, Version 3.1.1 is installed on your workstation, you can activate the product license on the database. You can activate the license either by running a script or by configuring the database by using the IBM Data Studio full client.
EXPLAIN tables
You can create a set of EXPLAIN tables manually by running a script, or you can let the IBM Data Studio client create a set in the SYSTOOLS schema.
QT_WCC* tables in the SYSTOOLS schema
These tables store information about query workloads.
The DB2OE.CALLDB2ADVIS stored procedure
This stored procedure allows the Index Advisor and the Workload Index Advisor to recommend new indexes.
The OQT.WCC_EXPLAIN_SP stored procedure
This stored procedure gathers EXPLAIN information for the SQL statements that are in query workloads.

Configuring DB2 databases for query and query-workload tuning by using the IBM Data Studio client, Version 3.1.1

You can configure DB2 for Linux, UNIX, and Windows databases for use the IBM InfoSphere Optim Query Workload Tuner by using the IBM Data Studio client.

Before you begin

Recommendation:
In the DB2 for Linux, UNIX, and Windows database that you are configuring, create a system temporary table space with a page size of 8 KB or larger to avoid potential query tuning errors.

About this task

During configuration, when InfoSphere Optim Query Workload Tuner creates the EXPLAIN tables and the EXPLAIN_GET_MSGS user-defined function, it grants the EXECUTE privilege on the EXPLAIN_GET_MSGS user-defined function to PUBLIC.

Procedure

To configure a DB2 for Linux, UNIX, and Windows database for query tuning with InfoSphere Optim Query Workload Tuner:

  1. In the Data Source Explorer, right-click the connection to the database and select Connect.
  2. Expand the connection by clicking the plus symbol next to it. An icon that represents the database appears. Right-click the database and select Analyze and Tune > Configure for Tuning > Guided Configuration.

    The client detects whether a license for IBM InfoSphere Optim Query Workload Tuner, Version 3.1.1 is active on the database.

    If there is an active license on the database, the client attempts to configure the database for query tuning and query-workload tuning.

    If there is no active license on the database, your choice of action depends on which of the three following situations match your own situation:

    When the client attempts to configure the database, it first checks whether at least one EXPLAIN table exists under the current schema, which is the user ID. The client then follows this algorithm:

  3. Verify that the database is configured for query and query-workload tuning and that the required data objects are created or enabled.
    1. In the Data Source Explorer, right-click the database, and then click Analyze and Tune > Configure for Tuning > Advanced Configuration and Privilege Management. The Advanced Configuration and Privilege Management window opens.
    2. In the Configuration Status section, you can check the status of the tables, stored procedures, objects, and automatic statistics collection that are required to use the query and query-workload tuning features. If a data object is disabled, follow the instructions in the message that is displayed beside that object to create or enable it.

Results

Your DB2 for Linux, UNIX, and Windows database is configured for query and query-workload tuning with InfoSphere Optim Query Workload Tuner.

What to do next

To start tuning in the Query Tuner Workflow Assistant, right-click the connection in the Data Source Explorer, then click Analyze and Tune > Start Tuning.

Authorities and privileges for configuring DB2 for Linux, UNIX, and Windows databases for use with IBM InfoSphere Optim Query Workload Tuner

Ensure that you have the authorities and privileges that are required to configure a DB2 for Linux, UNIX, and Windows database.

You must have the following authorities and privileges to configure a DB2 for Linux, UNIX, and Windows database for query tuning:

Migrating EXPLAIN tables

If you upgraded the version of DB2 for Linux, UNIX, and Windows that is managing a database, and that database contains a set of EXPLAIN tables that you are using with IBM InfoSphere Optim Query Workload Tuner, you can run a script to migrate those tables so that they are compatible with the new version of DB2 for Linux, UNIX, and Windows.

To migrate the EXPLAIN tables, follow the instructions in the migrate_explaintables_db2luw.ddl migration script.

You can find this migration script in these locations in the installation directory for the IBM Data Studio client.

Running scripts to configure DB2 databases for use with IBM InfoSphere Optim Query Workload Tuner

If you do not want to use the IBM Data Studio client to configure a DB2 for Linux, UNIX, and Windows database, you can carry out the configuration by editing and running a number of scripts.

Before you begin

Ensure that you have the required authorities and privileges to configure a DB2 for Linux, UNIX, and Windows database and activate the license for InfoSphere Optim Query Tuner.

Procedure

  1. Run the file EXPLAIN.DDL on the database that you want to use for tuning SQL. This file is located in the MISC folder in your DB2 installation.
  2. If you are using the IBM Data Studio full client, Version 3.1.1, the IBM InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows, Version 3.1.1 License Activation Kit is installed on your workstation: Run the file License.bat (for Windows systems) or License.sh (for Linux systems), which is in the directory <IBM Data Studio installation directory>\QueryTunerServerConfig\all_features\LUW\License\, to activate the product license on the database.
    1. In a DB2 command window, connect to the database on which you want to activate the license.
    2. Change to the directory in which the License.bat (for Windows systems) or License.sh (for Linux systems) file is located.
    3. Run the file with the db2 -vtf command.
  3. In a text editor, edit the file luwsp.sql, which is in the directory <IBM Data Studio installation directory>\QueryTunerServerConfig\all_features\LUW\IA\, by replacing the $install variable with the absolute path to this file. You do not need to run the file after you edit it. In a later step, you will run a batch file that will run this file.
  4. In a text editor, edit the file wccexplainsp.sql, which is in the directory <IBM Data Studio installation directory>\QueryTunerServerConfig\all_features\LUW\WCC\, by replacing the $install variable with the absolute path to this file. You do not need to run the file after you edit it. In a later step, you will run a batch file that will run this file.
  5. Open an DB2 command window, connect to the database that you activated the license on, and change to the directory <IBM Data Studio installation directory>\QueryTunerServerConfig\all_features\LUW\.
  6. Run the file enablement_win.bat (on Windows systems) or enablement_LinuxUnix.sh (on Linux systems).
  7. Check these output files in the current directory for errors: luwsp.out, oqt_profile.out, wccexplainsp.out, and wccluwddl.out

What to do next

To start tuning in the Query Tuner Workflow Assistant, right-click the connection in the Data Source Explorer, then click Analyze and Tune > Start Tuning.

Recommendation:
In the DB2 for Linux, UNIX, and Windows database that you are configuring, create a system temporary table space with a page size of 8 KB or larger to avoid potential query tuning errors.

Configuring DB2 for z/OS subsystems for use with InfoSphere Optim Query Workload Tuner

After you install the IBM Data Studio full client on your workstation, you can configure your DB2 for z/OS subsystems for tuning SQL statements and workloads.

Configuring for tuning means to create EXPLAIN tables and other tables, table spaces, indexes, and aliases. It also means binding required packages. You can carry out this task either by running JCL on your subsystem, or by connecting to the subsystem with the IBM Data Studio full client or administration client and using a configuration wizard.

If you configured a subsystem for tuning with InfoSphere Optim Query Workload Tuner for DB2 for z/OS, Version 2.2.1, you must migrate the tables and other objects, so that you can use them with InfoSphere Optim Query Workload Tuner for DB2 for z/OS, Version 3.1.1.

If you want to upgrade your DB2 for z/OS subsystem after you configure for tuning, you must run a JCL job to migrate the EXPLAIN tables and other objects that InfoSphere Optim Query Workload Tuner uses.

When you carry out the task of configuring for tuning, you can also carry out one or more optional configuration tasks, such as setting up the administrative scheduler to schedule tasks related to tuning query workloads.

Running JCL to configure DB2 for z/OS subsystems for use with InfoSphere Optim Query Workload Tuner

You can configure your DB2 for z/OS subsystem for query and query-workload tuning by submitting a JCL job.

Before you begin

You must have the authorities and privileges that are required for configuring DB2 for z/OS subsystems for query tuning.

About this task

If your subsystem was configured for InfoSphere Optim Query Workload Tuner, Version 3.1, then you do not need to follow the steps in this procedure.

If your subsystem was configured for earlier versions of InfoSphere Optim Query Tuner or InfoSphere Optim Query Workload Tuner, and before configuring for version 3.1.1 you want to drop both the data objects from those earlier versions and the data in those objects, the following procedure lists a step for performing the drop.

Procedure

To configure a DB2 for z/OS subsystem for query tuning and query-workload tuning:

  1. Upload the required JCL job files and DBRMs to the subsystem from the client system.

    The JCL files and DBRMs are located inside the installation directory for the IBM Data Studio full client.

    Upload the JCL files in ASCII format and the DBRMs in binary format.

    Table 1. JCL and DBRMs for Version 9.1, Version 10 conversion mode from Version 9.1, and Version 10 of DB2 for z/OS
    Name of subfolder V9 V10CM9 V10
    List of files JCL:
    AOCDDL9

    DBRMS:
    AOC5OADM
    AOC5OAPC
    AOC5OEPK
    AOC5OEXK
    AOC5OFMJ
    AOC5OIA1
    AOC5OIA2
    AOC5OIA3
    AOC5OIA4
    AOC5OIA5
    AOC5OIA6
    AOC5OIA7
    AOC5OIA8
    AOC5OIA9
    AOC5OIAA
    AOC5OIAK
    AOC5OIAL
    AOC5ONPT
    AOC5OPKG
    AOC5OQA
    AOC5OQIA
    AOC5OSA
    AOC5OWCK
    AOC5OWQA
    AOC5OWSA
    AOC5OWSK
    JCL:
    AOCDDLX9

    DBRMS:
    AOC5OADM
    AOC5OAPC
    AOC5OEPK
    AOC5OEXK
    AOC5OFMM
    AOC5OIA1
    AOC5OIA2
    AOC5OIA3
    AOC5OIA4
    AOC5OIA5
    AOC5OIA6
    AOC5OIA7
    AOC5OIA8
    AOC5OIA9
    AOC5OIAA
    AOC5OIAK
    AOC5OIAL
    AOC5ONPT
    AOC5OPKG
    AOC5OQA
    AOC5OQIA
    AOC5OSA
    AOC5OWCK
    AOC5OWQA
    AOC5OWSA
    AOC5OWSK
    JCL:
    AOCDDL10

    DBRMS:
    AOC5OADM
    AOC5OAPC
    AOC5OEPK
    AOC5OEXM
    AOC5OFMM
    AOC5OIA1
    AOC5OIA2
    AOC5OIA3
    AOC5OIA4
    AOC5OIA5
    AOC5OIA6
    AOC5OIA7
    AOC5OIA8
    AOC5OIA9
    AOC5OIAA
    AOC5OIAK
    AOC5OIAL
    AOC5ONPT
    AOC5OPKG
    AOC5OQA
    AOC5OQIA
    AOC5OSA
    AOC5OWCM
    AOC5OWQA
    AOC5OWSA
    AOC5OWSM
  2. If your subsystem was configured for earlier versions of InfoSphere Optim Query Tuner or InfoSphere Optim Query Workload Tuner, and before configuring for version 3.1.1 you want to drop both the data objects from those earlier versions and the data in those objects, drop the following databases.
    CAUTION:
    This step is not required. Follow it only if you are sure that you want to delete the data that is in these databases. If the subsystem is configured for versions 2.2.1 or 2.2.1.1, you can migrate the data objects to version 3.1.1 by following the steps here: Migrating data objects that were created for InfoSphere Optim Query Workload Tuner for DB2 for z/OS, Version 2.2.1 or 2.2.1.1
    Option Description
    If your subsystem is configured for versions 2.2.1 or 2.2.1.1: Drop databases DB2OSC, DSNOSCDB, and AOCOEDB.
    If your subsystem is configured for versions that are earlier than 2.2.1: Drop databases DB2OSC, DSNOSCDB, and DB2OE.
  3. If your subsystem was configured for IBM Optimization Center, and before configuring for version 3.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.
    CAUTION:
    This step is not required. Follow it only if you are sure that you want to delete the data that is in these databases.
  4. Create the tables for Version 3.1.1. On the subsystem, modify the corresponding AOCDDL JCL job file to conform to your environment, then submit the job. The following table lists the AOCDDL JCL job files for each environment.
    DB2 subsystem JCL job to create the query tuning tables for Version 3.1.1
    DB2 Version 10 for z/OS new-function mode AOCDDL10
    DB2 Version 10 for z/OS conversion mode from Version 9 AOCDDLX9
    DB2 Version 10 for z/OS conversion mode from Version 8 AOCDDLX8
    DB2 Version 9 for z/OS AOCDDL9

Using the IBM Data Studio client to configure DB2 for z/OS subsystems for use with InfoSphere Optim Query Workload Tuner

You can connect to a DB2 for z/OS subsystem from the IBM Data Studio client and configure the subsystem for query and query-workload tuning by using a wizard.

Before you begin

About this task

If your subsystem was configured for InfoSphere Optim Query Workload Tuner, Version 3.1, then you do not need to follow the steps in this procedure.

The procedure below explains how to use the IBM Data Studio client to create the data objects that are required for query tuning and query-workload tuning with IBM InfoSphere Optim Query Workload Tuner, Version 3.1.1..

For subsystems configured for earlier versions of InfoSphere Optim Query Tuner or InfoSphere Optim Query Workload Tuner, this procedure also describes how you can perform either of these two actions:

Procedure

To configure a DB2 for z/OS subsystem for query tuning and query-workload tuning:

  1. If you want to drop data objects that were created for earlier versions of InfoSphere Optim Query Tuner or InfoSphere Optim Query Workload Tuner and you want to delete the data that is in those objects, drop the following databases.
    CAUTION:
    This step is not required. Follow it only if you are sure that you want to delete the data that is in these databases. If the subsystem is configured for versions 2.2.1 or 2.2.1.1, you can migrate the data objects to version 3.1.1 by following the steps here: Migrating data objects that were created for InfoSphere Optim Query Workload Tuner for DB2 for z/OS, Version 2.2.1 or 2.2.1.1
    Option Description
    If your subsystem is configured for versions 2.2.1 or 2.2.1.1: DB2OSC
    DSNOSCDB
    AOCOEDB
    If your subsystem is configured for versions that are earlier than 2.2.1: DB2OSC
    DSNOSCDB
    DB2OE
  2. If your subsystem was configured for IBM Optimization Center, and before configuring for version 3.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.
    CAUTION:
    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. If you want to migrate the data objects that were created for version 2.2.1 or 2.2.1.1, follow these steps:
    1. Upload the required JCL job files to the subsystem from the client system.

      The JCL files to upload are listed in step b below and in step 5. The JCL files are installed with the IBM Data Studio client in the \QueryTunerServerConfig\ZOS\migration subdirectory of the installation directory. The default installation directory is C:\Program Files\IBM\DS3.1.1.

    2. Modify and submit the corresponding AOCM2B JCL job to unload data from the existing Version 2.2.1 or Version 2.2.1 fix pack 1 query tuning tables. The following table lists the AOCM2B JCL job files for each environment.
      DB2 subsystem JCL job to unload data from query tuning tables
      DB2 for z/OS Version 8 new-function mode subsystem AOCM2B8N
      DB2 Version 9 for z/OS subsystem AOCM2B9
      DB2 Version 10 for z/OS conversion mode from DB2 for z/OS Version 8 subsystem AOCM2BX8
      DB2 Version 10 for z/OS conversion mode from DB2 Version 9 for z/OS subsystem AOCM2BX9
      DB2 Version 10 for z/OS subsystem AOCM2BXN
    3. Drop the DB2OSC, DSNOSCDB, and AOCOEDB databases.
  4. In the Data Source Explorer in the IBM Data Studio client, expand the connection to the subsystem. Right-click the icon for the subsystem 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 full 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 full client is installed on your workstation. The configuration wizard allows you to configure the subsystem for query tuning and query-workload tuning.

    If the licence for InfoSphere Optim Query Workload Tuner is not yet active on the subsystem by this point and the license activation kit is not installed in the directory where the Data Studio full client is installed on your workstation, the configuration wizard allows you to configure the subsystem only for the no-charge query-tuning features. Close the configuration wizard and the Data Studio full client, install the license activation kit on your workstation, then open the Data Studio full client and the configuration wizard again. The license should now be active on the subsystem and you should be able to configure the subsystem for query tuning and query-workload tuning.

  5. Follow the instructions in the wizard.
  6. If you are migrating the data objects that were created for version 2.2.1 or 2.2.1.1, modify and submit the corresponding AOCM2F JCL job to load data to the query tuning tables for Version 3.1.1. The following table lists the AOCM2F JCL job files for each environment.
    DB2 subsystem JCL job to load data to the query tuning tables
    DB2 for z/OS Version 8 new-function mode subsystem AOCM2F8N
    DB2 Version 9 for z/OS subsystem AOCM2F9
    DB2 Version 10 for z/OS conversion mode from DB2 for z/OS Version 8 subsystem AOCM2FX8
    DB2 Version 10 for z/OS conversion mode from DB2 Version 9 for z/OS subsystem AOCM2FX9
    DB2 Version 10 for z/OS subsystem AOCM2FXN
  7. 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.

Authorities and privileges for configuring DB2 for z/OS subsystems for query and query-workload tuning with InfoSphere Optim Query Workload Tuner

You must have the authorities and privileges that are required to configure a DB2 for z/OS subsystem for query and query-workload tuning.

If you have the SYSADM authority, you can configure a subsystem for query and query-workload tuning. Otherwise, see the following table for detailed authorities and privileges that are required for each individual tasks.

Task Authorities or privileges required
Connect to a DB2 for z/OS subsystem
  • To connect to the subsystem, you must have access authority on underlying DB2 subsystem.
  • To execute tuning functions, you must have EXECUTE privilege on the following packages:
    • AOC5OADM
    • AOC5OEPJ (DB2 for z/OS Version 8)
    • AOC5OEPK (DB2 for z/OS Versions 9 and 10)
    • AOC5ONPT
    • AOC5OPKG
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

Creating connections to DB2 for z/OS subsystems that you are using for query and query-workload tuning

You can use the pages in the New Connection wizard to create a connection profile, so that you can connect to a DB2 for z/OS subsystem. This topic explains how to open the New Connection wizard from the Data Source Explorer view and the Administration Explorer view.

Procedure

To create a connection to a DB2 for z/OS subsystem:

  1. Right-click in the Data Source Explorer, and select New from the pop-up menu.

    In the Administration Explorer, you can click New > New Connection to a Database from the Administration Explorer toolbar.

  2. On the first page of the wizard, select DB2 for z/OS as the database manager, if you are connecting directly to the subsystem. If you are connecting through a gateway, select DB2 for Linux, UNIX, and Windows as the database manager. Then, specify other connection details, as described.
    Connection identification
    Specify preferences for naming the new connection.
    Use default naming convention
    Specifies that a connection name is generated based on the name of the subsystem that you are connecting to. This connection name is displayed after you create the connection.
    Connection name
    Type a name for the connection. Available only if Use default naming convention is not checked.
    JDBC driver

    Specify a JDBC driver to use to connect to the subsystem. JDBC drivers that appear in the list are fully supported. If a driver that you want to use is not listed but it is supported by the database manager, select Other Driver Default and provide the details.

    Click ... next to the JDBC driver field to open a window so that you can modify the path to the JAR files that are being used for a particular JDBC driver. You can also use this window to view the names and typical locations for JDBC JAR files for each listed driver.

    Global driver properties are set in the Preferences window, on the Driver Definitions page. To get to that page, select Window > Preferences. Then, in the Preferences window, expand Data Management > Connectivity.

    The IBM Data Server Driver for JDBC and SQLJ is included with the workbench product, and by default the wizard uses the included version of the driver. It is recommended that you use this version, because it has been tested thoroughly. If you want to use a different version of this driver, you can modify the path to the required JAR files by clicking .... Other JDBC drivers for IBM data servers might also be included, depending on the workbench product that you are using.

    JDBC driver limitation: Some drivers require a pass code or license file. Although license information can be set or available in the current environment, the information might not be available in other environments, such as on a Web server. The driver does not work if the pass code or license is not available.

    Properties - General
    Database
    This field appears when DB2 for Linux, UNIX, and Windows is the selected database manager because you are connecting to the subsystem through a gateway. Specify the name of the subsystem.
    Location
    This field appears when DB2 for z/OS is the selected database manager. Type the DB2 location name that is defined during installation. You must enter the name in upper case. To determine the location, host, and port that should be used for DB2 for z/OS connections, a DB2 for z/OS system programmer or DBA can issue a <cmd prefix="">DIS DDF where <cmd prefix=""> is a preassigned character in your system for a particular DB2 subsystem.
    Host
    If you are connecting directly to the subsystem, specify the TCP/IP host name or TCP/IP address of the subsystem.

    If you are connecting through a gateway, specify the TCP/IP host name or TCP/IP address of the gateway.

    Port number
    If you are connecting directly to the subsystem, specify the TCP/IP connection port for the selected subsystem.

    If you are connecting through a gateway, specify the TCP/IP connection port for the gateway.

    Retrieve objects created by this user only
    This field appears when DB2 for z/OS is the selected database manager. Select to load objects that were created by the user who is specified in the User ID field.
    Default schema
    Type the name of the schema to use for unqualified database object references in SQL statements. If you want SQL statements to refer to database objects that are in other schemas, you must qualify the names of the objects with the names of their corresponding schemas.
    Connection URL
    Shows the generated JDBC URL for the JDBC driver that you are using. The URL identifies the database so that the driver can establish a connection. The URL format depends on the driver.
    Properties - Tracing
    The controls on this page enable JDBC tracing for this connection. JDBC tracing is useful for troubleshooting, but enabling JDBC tracing can cause performance problems. Therefore, you should only enable tracing if you are experiencing problems.
    Disable tracing
    Clear this check box to enable JDBC tracing for this connection.
    Directory
    Specify a directory to which the trace files are saved.
    File name
    Specify a base file name for the trace files. More than one trace file might be created for each connection. If more than one trace file is generated, this name is used and is appended with an underscore and numeric values. For example, if you specify trace, the generated files might be trace_1, trace_2, and so on.
    Append
    Specifies that trace files are not overwritten if the files already exist. If this option is selected, new trace information is appended to any existing trace files.
    Trace levels
    Defines what kind of information is traced. Select a check box next to each option to include information in the trace file.
    Properties - Optional
    Specify additional connection properties, for example: readOnly = true. The properties that you can specify are different for every JDBC driver. Refer to the JDBC driver documentation for more examples.
  3. Optional: On the Filter page, specify filtering options.

    For best performance, you should use filters when you are connecting to a large database.

    If you do not specify filtering options in the wizard, you can modify them later by modifying connection properties or by specifying data object filter options. (For information about these options, see "Data object filters" at http://publib.boulder.ibm.com/infocenter/dstudio/v3r1/topic/com.ibm.datatools.server.ui.doc/topics/cfilters.html.) To modify connection properties, right-click a connection and select Properties.

    Filtering is not enabled by default on the wizard page. To filter your connection, clear the Disable filter check box, then specify filtering options either by using an expression or by selecting specific objects to include or not include in the connection view.

  4. Complete all other wizard steps and click Finish.

Results

The connection is displayed in the Data Source Explorer or the Administration Explorer, depending on which view you started in.

Migrating data objects that were created for InfoSphere Optim Query Workload Tuner for DB2 for z/OS, Version 2.2.1 or 2.2.1.1

If your subsystem was previously configured for query tuning with Optim Query Tuner for DB2 for z/OS or Optim Query Workload Tuner for DB2 for z/OS Version 2.2.1 or Version 2.2.1 fix pack 1, you must migrate the data objects to enable query tuning with Version 3.1.1.

Before you begin

You must have the authorities and privileges for migrating the EXPLAIN and Query Tuner tables.

About this task

To support table space and index name format changes that were introduced by Version 3.1 and are continued into Version 3.1.1, you must perform the migration in the following order on the subsystem:

  1. Unload data from the existing Version 2.2.1 or Version 2.2.1 fix pack 1 query tuning tables.
  2. Drop the databases that contain the existing Version 2.2.1 or Version 2.2.1 fix pack 1 query tuning tables.
  3. Create the databases and query tuning tables for Version 3.1.1.
  4. Load data to the query tuning tables for Version 3.1.1.

Procedure

To migrate the data objects for query tuning on a subsystem:

  1. Upload the required JCL job files to the subsystem from the client system.

    The files are installed with the IBM Data Studio client. You can find the AOCM JCL files in the \QueryTunerServerConfig\ZOS\migration subdirectory of the installation directory. You can find the AOCDDL JCL files in the \QueryTunerServerConfig\ZOS\z/OS_version_number_and_mode subdirectory of the installation directory. The default installation directory is C:\Program Files\IBM\DS3.1.1.

  2. Modify and submit the corresponding AOCM2B JCL job to unload data from the existing Version 2.2.1 or Version 2.2.1 fix pack 1 query tuning tables. The following table lists the AOCM2B JCL job files for each environment.
    DB2 subsystem JCL job to unload data from query tuning tables
    DB2 for z/OS Version 8 new-function mode subsystem AOCM2B8N
    DB2 Version 9 for z/OS subsystem AOCM2B9
    DB2 Version 10 for z/OS conversion mode from DB2 for z/OS Version 8 subsystem AOCM2BX8
    DB2 Version 10 for z/OS conversion mode from DB2 Version 9 for z/OS subsystem AOCM2BX9
    DB2 Version 10 for z/OS subsystem AOCM2BXN
  3. Drop the DB2OSC, DSNOSCDB, and AOCOEDB databases.
  4. Modify the corresponding AOCDDL JCL job file to conform to your environment, then submit the job. The following table lists the AOCDDL JCL job files for each environment.
    DB2 subsystem JCL job to drop existing databases and create new Version 3.1.1 databases and query tuning tables
    DB2 Version 10 for z/OS new-function mode AOCDDL10
    DB2 Version 10 for z/OS conversion mode from Version 9 AOCDDLX9
    DB2 Version 10 for z/OS conversion mode from Version 8 AOCDDLX8
    DB2 Version 9 for z/OS AOCDDL9
    DB2 for z/OS Version 8 new-function mode AOCDDL8N
  5. Modify and submit the corresponding AOCM2F JCL job to load data to the query tuning tables for Version 3.1.1. The following table lists the AOCM2F JCL job files for each environment.
    DB2 subsystem JCL job to load data to the query tuning tables
    DB2 for z/OS Version 8 new-function mode subsystem AOCM2F8N
    DB2 Version 9 for z/OS subsystem AOCM2F9
    DB2 Version 10 for z/OS conversion mode from DB2 for z/OS Version 8 subsystem AOCM2FX8
    DB2 Version 10 for z/OS conversion mode from DB2 Version 9 for z/OS subsystem AOCM2FX9
    DB2 Version 10 for z/OS subsystem AOCM2FXN

Authorities and privileges for migrating the EXPLAIN tables and Query Tuner tables on DB2 for z/OS subsystems

You must have the authorities and privileges that are required to migrate the EXPLAIN tables and Query Tuner tables on DB2 for z/OS subsystems.

If you have the SYSADM authority, you can migrate the EXPLAIN and Query Tuner tables. Otherwise, see the following table for detailed authorities and privileges that are required for each individual tasks.

Task Authorities or privileges required
Alter EXPLAIN and Query Tuner tables One or more of the following privileges or authorities:
  • ALTER privilege on the tables
  • Ownership of the table
  • DBADM authority for the database
  • SYSADM or SYSCTRL authority
Create EXPLAIN and Query Tuner tables If you want to create the 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
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

Packages that are required to be bound on DB2 for z/OS subsystems that are used with InfoSphere Optim Query Workload Tuner

On each DB2 for z/OS that runs the SQL statements that you tune, you must bind certain packages to enable various functions of the IBM Data Studio client.

You can bind the packages by using the IBM Data Studio client or by running an AOCDDL sample JCL job. After the packages are bound from a particular workstation, you do not need to bind again to connect from the IBM Data Studio client on another workstation.

Basic packages

AOC5OADM
This package accesses catalog tables to check the existence of the objects, such as tables and databases that are used for query tuning. This package also checks the format of objects, such as tables and columns.
AOC5OEPJ
On a DB2 for z/OS Version 8 subsystem, this package accesses catalog information for any tables on which the EXPLAIN statement is issued. This package accesses information about columns, indexes, table spaces, and their statistics.
AOC5OEPK
On a DB2 for z/OS Version 9 or Version 10 subsystem, this package accesses catalog information for any tables on which the EXPLAIN statement is issued. This package accesses information about columns, indexes, table spaces, and their statistics.
AOC5ONPT
This package accesses QMF™ control tables in case the current user does not have SYSADM authority.
AOC5OPKG
This package accesses catalog tables to check the existence of the package, and retrieves the privileges that users have on the packages and tables.

Packages required for tuning SQL statements

AOC5OSA
For the Statistics Advisor, this package accesses statistics-profile tables that are used by the statistics advisor to manage the statistics profile.
AOC5OQA
For the Query Advisor, this package accesses catalog information for referential constraints between tables.
AOC5OQIA
For the Index Advisor, this package accesses the catalog tables to get the referential constraints, table definition, and statistics.
AOC5OFMJ
For the query annotation feature, this package accesses catalog tables on a DB2 for z/OS Version 8 or Version 9 subsystem. This package obtains the DDL statements for views and the materialized query tables.
AOC5OFMM
For the query annotation feature, this package accesses catalog tables on a DB2 for z/OS Version 10 subsystem. This package obtains the DDL statements for views and the materialized query tables.

Packages required for tuning query workloads

AOC5OCWP
For the SYSPROC.OPT_EXECUTE_TASK stored procedure, this package accesses the workload tables, catalog tables, and EXPLAIN tables under the DB2OSC schema on a DB2 for z/OS Version 8 compatibility mode subsystem.
AOC5OEXJ
For the SYSPROC.OPT_RUNSQL stored procedure, this package accesses workload tables and EXPLAIN tables under the DB2OSC schema on a DB2 for z/OS Version 8 new-function mode or Version 10 conversion mode from Version 8 subsystem.
AOC5OEXK
For the SYSPROC.OPT_RUNSQL stored procedure, this package accesses workload tables and EXPLAIN tables under the DB2OSC schema on a DB2 for z/OS Version 9 or Version 10 conversion mode from Version 9 subsystem.
AOC5OEXM
For the SYSPROC.OPT_RUNSQL stored procedure, this package accesses workload tables and EXPLAIN tables under the DB2OSC schema on a DB2 for z/OS Version 10 new-function mode subsystem.
AOC5OIAn where n={1-9, A-M}
For the Index Advisor, this package accesses the tables that are used by the Index Advisor to perform the workload-based index advisor process. This package also accesses the catalog tables to get referential constraints, table definitions, and statistics.
AOC5OSA
For the Statistics Advisor, this package accesses the statistics-profile tables that are used by the Statistics Advisor to manage the statistics profiles.
AOC5OWCJ
For the workload control center feature, this package accesses workload tables, catalog tables, and EXPLAIN tables under the DB2OSC schema on a DB2 for z/OS Version 8 new-function mode or Version 10 conversion mode from Version 8 subsystem.
AOC5OWCK
For the workload control center and workload monitoring features, this package accesses workload tables, catalog tables, profile tables, and EXPLAIN tables under the SYSIBM and DB2OSC schemas on a DB2 for z/OS Version 9 or Version 10 conversion mode from Version 9 subsystem.
AOC5OWCM
For the workload control center feature, this package accesses workload tables, catalog tables, and EXPLAIN tables under the DB2OSC schema on a DB2 for z/OS Version 10 new-function mode subsystem.
AOC5OWQA
For the Query Advisor, this package accesses tables that are used by the Query Advisor to manage the analysis session and corresponding operations.
AOC5OWSJ
For the SYSPROC.OPT_EXECUTE_TASK stored procedure, this package accesses workload tables, catalog tables, and EXPLAIN tables under the DB2OSC schema on a DB2 for z/OS Version 8 new-function mode or Version 10 conversion mode from Version 8 subsystem.
AOC5OWSK
For the SYSPROC.OPT_EXECUTE_TASK stored procedure, this package accesses workload tables, catalog tables, and EXPLAIN tables under the DB2OSC schema on a DB2 for z/OS Version 9 or Version 10 conversion mode from Version 9 subsystem.
AOC5OWSM
For the SYSPROC.OPT_EXECUTE_TASK stored procedure, this package accesses workload tables, catalog tables, and EXPLAIN tables under the DB2OSC schema on a DB2 for z/OS Version 10 new-function mode subsystem.
AOC5OCWA
For the Statistics Advisor, this package accesses tables that are used by the Statistics Advisor to manage the analysis session and corresponding operations on a DB2 for z/OS Version 8 compatibility mode subsystem.
AOC5OCWC
For the workload control center feature, this package accesses workload tables, catalog tables, and EXPLAIN tables under the DB2OSC schema on DB2 for z/OS Version 8 compatibility mode subsystem.
AOC5OWSA
For the Statistics Advisor, this package accesses tables that are used by the Statistics Advisor to manage the analysis session and corresponding operations on a DB2 for z/OS Version 8 new-function mode, Version 9, or Version 10 subsystem.

Package required for comparing access plans

AOC5OAPC
For the access plan comparison feature, this package accesses the plan comparison table to perform the advisor process. It also accesses the catalog tables to get information about user-specified DB2 packages to be compared.

Migrating data objects when upgrading DB2 for z/OS

If you upgrade your DB2 for z/OS subsystem to a higher version, and the subsystem is already configured for tuning with InfoSphere Optim Query Workload Tuner, Version 3.1.1, you must migrate the data objects in that configuration.

About this task

To migrate, modify and submit the corresponding AOCTIJ JCL job. The following table lists the AOCTIJ JCL job files for each environment.

Upgrading from Upgrading to JCL job for migrating data objects for query tuning
DB2 for z/OS Version 8 new-function mode DB2 Version 10 for z/OS conversion mode from DB2 for z/OS Version 8 AOCTIJC8
DB2 Version 10 for z/OS conversion mode from DB2 for z/OS Version 8 DB2 Version 10 for z/OS new-function mode AOCTIJ8X
DB2 Version 9 for z/OS DB2 Version 10 for z/OS conversion mode from DB2 Version 9 for z/OS AOCTIJC9
DB2 Version 10 for z/OS conversion mode from DB2 Version 9 for z/OS DB2 Version 10 for z/OS new-function mode AOCTIJ9X

Procedure

  1. Upload to your subsystem the JCL file that corresponds to the upgrade of your DB2 for z/OS subsystem. The JCL files are located in the directory <installation directory for the IBM Data Studio client>\QueryTunerServerConfig\all_features\ZOS\migration. Upload the file as an ASCII file.
  2. Follow the instructions in the Notes section of the comments to customize the JCL for your environment, and then submit the job. For example, here are the instructions from the file AOCTIJ9X.
    //*  Notes =
    //*    PRIOR TO RUNNING THIS JOB, customize it for your system:
    //*    (1) Add a valid job card
    //*    (2) Locate and change all occurrences of the following strings
    //*        as indicated:
    //*        (A) '!DSN!'      to the subsystem name of your DB2
    //*        (B) 'DSNTIA!!'   to the plan name for DSNTIAD on your DB2
    //*        (C) 'DSN!!0'     to the prefix of the target library for DB2
    //*        (D) '!AOCDBRM!' to the prefix of target library for QT
    //*                          DBRMs
    //*        (E) 'USER!!'     user id for individual explain tables
    //*        (F) '!GRANTEE!   to one or more authorization IDs that
    //*                          need to use stored procedures
    //*                           SYSPROC.OPT_RUNSQL and
    //*                           SYSPROC.OPT_EXECUTE_TASK
    //*        (G) '!USERID!'    to the USER ID which will have execute
    //*                           authority on packages
    //*        (H) 'PKGOWNER!'   to the owner of the QT packages
    //*        (I) '!SQLID!'     CURRENT SQLID which is set before execute
    //*                           DDLs
    //*        (J) '!WLMENV4!'   WLM environment name for SYSPROC.OPT_RUNSQL
    //*        (K) '!WLMENVJU!'  WLM environment name for
    //*                           SYSPROC.OPT_EXECUTE_TASK
    //*                           and want to capture SQL from cache.
    Attention: Step AOCSP is required only if you created the OPT_RUNSQL and OPT_EXECUTE_TASK stored procedures in the configuration that you are migrating. Creating these stored procedures when configuring a subsystem for tuning is optional.

Running JCL to activate the license for InfoSphere Optim Query Workload Tuner, Version 3.1.1

You can activate the license for InfoSphere Optim Query Workload Tuner by running a JCL job, if you do not want the license to be activated automatically by the IBM Data Studio full client.

About this task

A product license must be active on each DB2 for z/OS subsystem that you connect to through the client when you are tuning queries and query workloads.

When you connect to a DB2 for z/OS subsystem from the IBM Data Studio full client and the license activation kit is installed with the client, the client detects whether a license is not yet present on the subsystem. If a license is not present, the client installs and activates the license.

If you would rather activate the license manually on a subsystem, you can run a JCL job that is included in the installation directory for the client after you install the license activation kit.

Important:
If a license for version 3.1 of IBM InfoSphere Optim Query Workload Tuner for DB2 for z/OS is already active on a subsystem that you want to use for tuning, you do not need to follow these steps. The license for version 3.1 gives you access to the new features and improvements in version 3.1.1.

Procedure

  1. Upload the following file to the subsystem where you want to activate the license: qwt_license.jcl This file is located in the QueryTunerServerConfig\all_features\ZOS\License\ folder in the installation directory for the IBM Data Studio full client.
  2. Replace the variables $$DSN and $$USER with values for your system environment and submit the job.

What to do next

In the Data Source Explorer view in the IBM Data Studio full client, Version 3.1.1 or in the IBM Data Studio administration client, Version 3.1.1, you can connect to the subsystem and begin tuning queries and query workloads.

Optional tasks for configuring DB2 for z/OS subsystems for tuning

Several of the tasks that are involved in configuring subsystems for tuning are optional.

Enabling the SYSPROC.OPT_EXECUTE_TASK stored procedure on DB2 for z/OS subsystems

To gather EXPLAIN information for a query workload from the database server, you must enable the SYSPROC.OPT_EXECUTE_TASK stored procedure.

About this task

By enabling the SYSPROC.OPT_EXECUTE_TASK stored procedure, you can gather and consolidate EXPLAIN information for a query workload from the database server. The stored procedure must also be enabled to consolidate literal values in EXPLAIN information.

Procedure

To enable the Java stored procedure SYSPROC.OPT_EXECUTE_TASK:

  1. Ensure that the IBM SDK for Java 2 Technology Edition, Version 1.4 is installed in the UNIX System Service folder that is specified by the JAVA_HOME environment variable.
  2. Ensure that the IBM Data Server Driver for JDBC and SQLJ is installed, the environment variables are correctly set, and the properties are correctly configured. See the section "Installing the IBM Data Server Driver for JDBC and SQLJ as part of a DB2 installation" in DB2 for z/OS Application Programming Guide and Reference for Java for your version of DB2 for z/OS for your version of DB2 for z/OS.
  3. Ensure that a WLM environment is set up for Java routines, and a started-task job for Java stored procedures exists in the system procedure library. See the section "Setting up the WLM application environment for Java routines" in DB2 for z/OS Application Programming Guide and Reference for Java for your version of DB2 for z/OS for your version of DB2 for z/OS.
  4. Ensure that users have permission to write to the temporary z/OS UNIX directory. The SYSPROC.OPT_EXECUTE_TASK stored procedure writes log files to this directory.

Example

In the following sample, aocwccsp.jar is the Java stored procedure JAR file. This file is installed in two locations in the product. You can use either copy. They are identical to each other.

JCC_HOME is the home directory of the JDBC driver, and JAVA_HOME is the home directory of the Java. You must change the value of JCC_HOME and JAVA_HOME, depending on the location of the JDBC driver and runtime environment. You must also change the value of TZ, which represents the time zone, to your local time zone, for example:

ENVAR("CLASSPATH=/usr/lpp/db2910_base/classes/aocwccsp.jar",
"TZ=PST08",                                                 
"JCC_HOME=/usr/lpp/db2910_jdbc/",                           
"JAVA_HOME=/usr/lpp/java140/J1.4"),                         
MSGFILE(JSPDEBUG,,,,ENQ),                                   
XPLINK(ON)

Enabling the SYSPROC.OPT_RUNSQL stored procedure on DB2 for z/OS subsystems

By enabling the SYSPROC.OPT_RUNSQL stored procedure, you can capture the SQL statements from dynamic statement caches and store these statements in the DSN_STATEMENT_CACHE_TABLE table, if you do not have the privilege to run the EXPLAIN STMTCACHE ALL statement dynamically.

Procedure

To enable the C stored procedure SYSPROC.OPT_RUNSQL:

  1. Upload the tersed load module to the subsystem. You can find the load module in the \serverconfig\ZOS\z/OS_version_number_and_mode\Load Module subdirectory of the installation directory.

    Ensure that the following transfer rules are specified.

    ftp> quote site blk=6144 lrecl=1024 recfm=fb tracks unit=sysallda primary=90
    200 SITE command was accepted
    ftp> binary
    200 Representation type is Image
  2. Use the following sample job to unterse the load module to a partition data set.
    //***************************************************************
    //* Notes =
    //*  PRIOR TO RUNNING THIS JOB, 
    //*  locate and change the string "!!" to the following suffix
    //*  for the different versions of DB2 for z/OS:
    //*  For V8 and V10CM8, change to "J". 
    //*  For V9 and V10CM9, change to "K".
    //*  For V10NFM, change to "M".
    //***************************************************************
    //UNTERSE    JOB CLASS=A,MSGLEVEL=(1,1),                        
    //        NOTIFY=&SYSUID                                        
    //UNTERSE     EXEC PGM=TRSMAIN,PARM='UNPACK'                      
    //STEPLIB   DD DISP=SHR,DSN=IBMUSER.TERSE.LOADLIB               
    //SYSPRINT  DD SYSOUT=*,DCB=(LRECL=1024,BLKSIZE=6144,RECFM=FB)  
    //INFILE    DD DISP=SHR,DSN=USER.TERSED.AOCRNSQ!!                
    //OUTFILE   DD DSN=USER.UNTERSED,                                
    //  DISP=(SHR,CATLG,DELETE),                                    
    //  UNIT=3390,VOL=SER=DK8320,SPACE=(CYL,(5,5),RLSE)             
    /*   
  3. Copy the untersed load module to your user EXIT library.
  4. Ensure that the EXIT library is concatenated to your DB2 start task STEPLIB.

Setting up the administrative scheduler

You have the option of using the administrative scheduler to schedule tasks on query workloads, such as workload capture and monitoring.

Without the administrative scheduler, you can schedule tasks, but you must keep the client application running on your workstation and connected to the DB2 for z/OS subsystem for the operations to run when scheduled. If the connection to the subsystem is dropped while scheduled tasks are running, those tasks cannot complete.

Setting up the administrative scheduler on DB2 UDB for z/OS, Version 8

You can use the administrative task scheduler to execute administrative tasks according to a time- or event-based schedule.

About this task

Procedure

  1. Set up the administrative task scheduler.
    1. Customize the administrative task scheduler address space startup procedure (xxxxADMT). The name of the startup procedure needs to match the value of the ADMTPROC subsystem parameter. Before using the startup procedure, locate and review the settings for the following parameters:
      DB2SSID
      The name of this DB2® subsystem.
      DFLTUID
      The default ID that is used by the administrative task scheduler to execute its tasks. DFLTUID must be different than the ID that is used to start this address space.
      TRACE
      Indicates whether to activate tracing for the admin scheduler. The value can be ON or the default value of OFF.
      You can specify other parameters, such as ERRFREQ or MAXTHD, or STOPONDB2STOP in the startup procedure similarly to the three parameters DB2SSID, DFLTUID and TRACE. These additional administrative task scheduler parameters are described in the information about scheduling administrative tasks.
    2. Run job DSNTIJRA to define the administrative task scheduler started task module to RACF® program control and to define the administrative task scheduler as a trusted context in RACF. In DB2 data sharing environments, DSNTIJRA needs to be customized and run for each member of the group.
    3. Enable the administrative task scheduler routines.
    4. Create a job to make image copies of the administrative task scheduler table spaces. Establish a schedule for making image copies. In general, you should back up important databases on a regular basis. The database for the administrative task scheduler is no exception and should be copied on the same frequency as the DB2 catalog and directory. The following example shows how to copy the table spaces in this database to stacked data sets on tape with a retention period of 99 days:
      //* 
      //DSNTIC  EXEC PGM=DSNUTILB,PARM='DSN,IMAGCOPY',COND=(4,LT) 
      //SYSPRINT DD  SYSOUT=* 
      //SYSUDUMP DD  SYSOUT=* 
      //DSNTRACE DD  SYSOUT=* 
      //SYSLISTD DD *
        LISTDEF DSNLDEF           
      					INCLUDE TABLESPACES DATABASE DSNADMDB 
      //SYSIN    DD  *
          TEMPLATE DSNTPLT            
      					  DSN(prefix.IMAGCOPY.&DB..;&TS.;)
                  DISP (NEW,KEEP,DELETE)
                  UNIT TAPE
                  STACK YES
                  RETPD 99
             COPY LIST DSNLDEF
                  COPYDDN(DSNTPLT) 
      /*
  2. Enable the administrative task scheduler and administrative enablement routines The administrative task scheduler routines are DB2®-supplied routines that enable you to schedule work. The administrative enablement routines are DB2-supplied routines that enable you to execute administrative work immediately.

    The following routines are administrative task scheduler routines

    The following routines are administrative enablement routines
    1. If you did not create these procedures during installation or migration, customize and run job steps DSNTIAS, DSNTIAE, and DSNTIJG of your customized job DSNTIJSG to define, bind and grant access to the administrative task scheduler and administrative enablement stored procedures.

      Job step DSNTIJG grants EXECUTE authority on these stored procedures and their packages to PUBLIC, and grants ALL on related result set tables to PUBLIC. If you do not want this authorization granted to PUBLIC, edit the job to grant the authority only to specific users or groups.

      Restriction:
      This job should be executed by a user with all the specific privileges needed.
      These stored procedures run in a WLM-managed stored procedure address space.
    2. In the JCL for starting the WLM-established address space for running the stored procedures whose load module must reside in an APF-authorized library, ensure that all libraries in the STEPLIB DD concatenation are APF-authorized.

      In the JCL for starting the WLM-established address space for running the following stored procedures: ADMIN_COMMAND_DB2, ADMIN_COMMAND_UNIX, ADMIN_INFO_HOST, ADMIN_INFO_SSID, ADMIN_JOB_FETCH, ADMIN_JOB_CANCEL, ADMIN_JOB_QUERY, ADMIN_JOB_SUBMIT, ADMIN_TASK_ADD, ADMIN_TASK_LIST, ADMIN_TASK_REMOVE, ADMIN_TASK_STATUS, ADMIN_UTL_SCHEDULE and ADMIN_UTL_SORT, ensure that the library prefix.SDSNLOD2 is added to the STEPLIB DD concatenation following the library prefix.SDSNLOAD.

      The name of the WLM environment must match the WLM ENVIRONMENT parameter value in the CREATE PROCEDURE statement for each stored procedure.

    3. If the BPX.DAEMON facility class is active and the BPX.DAEMON.HFSCTL facility class is not defined, perform the following actions for the stored procedures that must be registered to RACF® program control. The stored procedures that require RACF program control are:
      • ADMIN_COMMAND_UNIX
      • ADMIN_JOB_CANCEL
      • ADMIN_JOB_FETCH
      • ADMIN_JOB_QUERY
      • ADMIN_JOB_SUBMIT
      1. Customize and run job step DSNADER in job DSNTIJRA to define the security environment for these stored procedures. You need to uncomment job step DSNADER before you run job DSNTIJRA. You do not need to run any other job steps in job DSNTIJRA.
      2. Create a WLM environment for these stored procedures that meets the following requirements:
        • The WLM-established stored procedure address space loads only controlled programs.
        • In the JCL for starting the WLM-established address space for running these stored procedures, ensure that all libraries in the STEPLIB DD concatenation are APF-authorized and that the library prefix.SDSNLOD2 is added to the STEPLIB DD concatenation following the library prefix.SDSNLOAD.
        • The name of this environment must match the WLM ENVIRONMENT parameter value in the CREATE PROCEDURE statement for these stored procedures in the job DSNTIJSG.
Setting up the administrative scheduler on DB2 Version 9.1 for z/OS

You can use the administrative task scheduler to execute administrative tasks according to a time-based or event-based schedule.

Before you begin

You should have run job DSNTIJRA and job DSNTIJIN before you set up the administrative task scheduler.

About this task

Restriction:
After migration to conversion mode, the administrative task scheduler is disabled until you run job DSNTIJSG.

Procedure

  1. Set up the administrative task scheduler.
    1. Customize the administrative task scheduler address space startup procedure (xxxxADMT) in job DSNTIJMV. The name of the startup procedure needs to match the value of the ADMTPROC subsystem parameter. Before using the startup procedure, locate and review the settings for the following parameters:
      DB2SSID
      The name of this DB2® subsystem.
      DFLTUID
      The default ID that is used by the administrative task scheduler to execute its tasks. DFLTUID must be different than the ID that is used to start this address space.
      TRACE
      Indicates whether to activate tracing for the administrative task scheduler. The value can be ON or the default value of OFF.

      You can specify other parameters, such as ERRFREQ, MAXHIST, MAXTHD, or STOPONDB2STOP in the startup procedure similarly to the three parameters DB2SSID, DFLTUID and TRACE.

      ERRFREQ
      Specifies how frequently message DSNA679I displays on the console, indicating that one of the redundant, active copies of the task list is not accessible. By default, this message displays on the console once per minute, for example, when DB2 is offline.
      MAXHIST
      Specifies the number of status entries per task that are stored. This parameter is a positive integer with a default value of 10. When the limit is reached, the oldest status entries are deleted.
      MAXTHD
      Specifies the maximum number of execution threads for an administrative task scheduler. The default value is 99.
      STOPONDB2STOP
      Specifies that the administrative task scheduler will terminate when DB2 is stopped.

      Also, locate and review the ADMTDD1 DD statement. The VSAM data set that is indicated must match the VSAM task list data set that is defined in job DSNTIJIN.

      The following example shows how these parameters can be updated.

      //DSNADMT  PROC LIB='DSN!!0.SDSNLOAD',
      //          DB2SSID=DSN,
      //          DFLTUID=DFLTUID,
      //          TRACE=OFF,
      //          MAXTHD=10 
      //*
      //STARTADM EXEC PGM=DSNADMT0,DYNAMNBR=100,REGION=0K,  
      //          PARM=('DB2SSID=&DB2SSID', 
      //          ' DFLTUID=&DFLTUID',   
      //          ' TRACE=&TRACE'  
      //          ' MAXTHD=&MAXTHD'  
      //          ' ERRFREQ=1440'  
      //          ' STOPONDB2STOP') 
      //STEPLIB  DD DISP=SHR,DSN=&LIB  
      //ADMTDD1  DD DISP=SHR,DSN=DSNC!!0.TASKLIST
    2. Ensure that the administrative task scheduler routines were enabled. They were enabled in steps DSNTIAS and DSNTIJG of job DSNTIJSG.
    3. Create a job to make image copies of the administrative task scheduler table spaces. Establish a schedule for making image copies. In general, you should back up important databases on a regular basis. The database for the administrative task scheduler is no exception and should be copied on the same frequency as the DB2 catalog and directory. The following example shows how to copy the table spaces in this database to stacked data sets on tape with a retention period of 99 days:
      //*
      //DSNTIC  EXEC PGM=DSNUTILB,PARM='DSN,IMAGCOPY',COND=(4,LT)
      //SYSPRINT DD  SYSOUT=*
      //SYSUDUMP DD  SYSOUT=*
      //DSNTRACE DD  SYSOUT=*
      //SYSLISTD DD  *
        LISTDEF DSNLDEF
                INCLUDE TABLESPACES DATABASE DSNADMDB
      //SYSIN    DD  *
      
        TEMPLATE DSNTPLT
                 DSN(prefix.IMAGCOPY.&DB..;&TS.;)
                 DISP (NEW,KEEP,DELETE)
                 UNIT TAPE
                 STACK YES
                 RETPD 99
             COPY LIST DSNLDEF
                 COPYDDN(DSNTPLT)
      /*
  2. Enable the administrative task scheduler and administrative enablement routines.

    The administrative task scheduler routines are DB2®-supplied routines that enable you to schedule work. The administrative enablement routines are DB2-supplied routines that enable you to execute administrative work immediately.

    Before starting this step, set up the DB2-supplied routines that it enables. For instructions, see the section "Enabling DB2-supplied routines" in DB2 for z/OS Installation Guide at http://publib.boulder.ibm.com/epubs/pdf/dsnigk1f.pdf.

    The following routines are administrative task scheduler routines:

    The following routines are administrative enablement routines:

    1. If you did not create these procedures during installation or migration, customize and run job steps DSNTIAS, DSNTIAE, and DSNTIJG of your customized job DSNTIJSG to define, bind, and grant access to the administrative task scheduler and administrative enablement stored procedures.

      Job step DSNTIJG grants EXECUTE authority on these stored procedures and their packages to PUBLIC, and grants ALL on related result set tables to PUBLIC. If you do not want this authorization granted to PUBLIC, edit the job to grant the authority only to specific users or groups.

      Tip:
      Do not grant EXECUTE authority to PUBLIC. If you do, anyone that can access DB2 can use these stored procedures.
      Restriction:
      This job should be executed by a user with all the specific privileges needed.

      These stored procedures run in a WLM-managed stored procedure address space.

    2. In the JCL for starting the WLM-established address space for running the stored procedures whose load module must reside in an APF-authorized library, ensure that all libraries in the STEPLIB DD concatenation are APF-authorized. The name of the WLM environment must match the WLM ENVIRONMENT parameter value in the CREATE PROCEDURE statement for each stored procedure.
    3. If the BPX.DAEMON facility class is active and the BPX.DAEMON.HFSCTL facility class is not defined, perform the following actions for the stored procedures that must be registered to RACF® program control.

      The stored procedures that require RACF program control are:

      • ADMIN_COMMAND_UNIX
      • ADMIN_JOB_CANCEL
      • ADMIN_JOB_FETCH
      • ADMIN_JOB_QUERY
      • ADMIN_JOB_SUBMIT
      1. Customize and run job step DSNADER in job DSNTIJRA to define the security environment for these stored procedures. You need to uncomment job step DSNADER before you run job DSNTIJRA. You do not need to run any other job steps in job DSNTIJRA.
      2. Ensure that you have a WLM environment for these stored procedures that meets the following requirements:
        • The WLM-established stored procedure address space loads only controlled programs.
        • In the JCL for starting the WLM-established address space for running these stored procedures, ensure that all libraries in the STEPLIB DD concatenation are APF-authorized.
        • The name of this environment must match the WLM ENVIRONMENT parameter value in the CREATE PROCEDURE statement for these stored procedures in the job DSNTIJSG.
    4. Authorize the appropriate users to use one or more of these stored procedures by issuing the GRANT EXECUTE statement.
      Important:
      The privileges to run DSN8EXP and DSNAEXP should be granted with consideration that DSN8EXP and DSNAEXP can EXPLAIN on any explainable SQL statement that is valid on the system, and that EXPLAIN output can reveal potentially sensitive information. For example, you should not grant access to PUBLIC to use DSN8EXP and DSNAEXP.
Setting up the administrative scheduler on DB2 Version 10 for z/OS

You can use the administrative task scheduler to execute administrative tasks according to a time-based or event-based schedule.

Before you begin

You should have run job DSNTIJRA and job DSNTIJIN before you set up the administrative task scheduler.

About this task

Restriction:
After migration to conversion mode (from Version 8 or Version 9.1), the administrative task scheduler is disabled until you run job DSNTIJRT.

Procedure

  1. Set up the administrative task scheduler.
    1. Customize the administrative task scheduler address space startup procedure (xxxxADMT) in job DSNTIJMV. The name of the startup procedure needs to match the value of the ADMTPROC subsystem parameter. Before using the startup procedure, locate and review the settings for the following parameters:
      DB2SSID
      The name of this DB2® subsystem.
      DFLTUID
      The default ID that is used by the administrative task scheduler to execute its tasks. DFLTUID must be different than the ID that is used to start this address space.
      TRACE
      Indicates whether to activate tracing for the administrative task scheduler. The value can be ON or the default value of OFF.

      You can specify other parameters, such as ERRFREQ, MAXHIST, MAXTHD, or STOPONDB2STOP in the startup procedure similarly to the three parameters DB2SSID, DFLTUID and TRACE.

      ERRFREQ
      Specifies how frequently message DSNA679I displays on the console, indicating that one of the redundant, active copies of the task list is not accessible. By default, this message displays on the console once per minute, for example, when DB2 is offline.
      MAXHIST
      Specifies the number of status entries per task that are stored. This parameter is a positive integer with a default value of 10. When the limit is reached, the oldest status entries are deleted.
      MAXTHD
      Specifies the maximum number of execution threads for an administrative task scheduler. The default value is 99.
      STOPONDB2STOP
      Specifies that the administrative task scheduler will terminate when DB2 is stopped.

      Also, locate and review the ADMTDD1 DD statement. The VSAM data set that is indicated must match the VSAM task list data set that is defined in job DSNTIJIN.

      The following example shows how these parameters can be updated.

      //DSNADMT  PROC LIB='DSN!!0.SDSNLOAD',
      //          DB2SSID=DSN,
      //          DFLTUID=DFLTUID,
      //          TRACE=OFF,
      //          MAXTHD=10 
      //*
      //STARTADM EXEC PGM=DSNADMT0,DYNAMNBR=100,REGION=0K,  
      //          PARM=('DB2SSID=&DB2SSID', 
      //          ' DFLTUID=&DFLTUID',   
      //          ' TRACE=&TRACE'  
      //          ' MAXTHD=&MAXTHD'  
      //          ' ERRFREQ=1440'  
      //          ' STOPONDB2STOP') 
      //STEPLIB  DD DISP=SHR,DSN=&LIB  
      //ADMTDD1  DD DISP=SHR,DSN=DSNC!!0.TASKLIST
    2. Ensure that the administrative task scheduler routines were enabled. They were enabled by job DSNTIJRT.
    3. Create a job to make image copies of the administrative task scheduler table spaces. Establish a schedule for making image copies. In general, you should back up important databases on a regular basis. The database for the administrative task scheduler is no exception and should be copied on the same frequency as the DB2 catalog and directory. The following example shows how to copy the table spaces in this database to stacked data sets on tape with a retention period of 99 days:
      //*
      //DSNTIC  EXEC PGM=DSNUTILB,PARM='DSN,IMAGCOPY',COND=(4,LT)
      //SYSPRINT DD  SYSOUT=*
      //SYSUDUMP DD  SYSOUT=*
      //DSNTRACE DD  SYSOUT=*
      //SYSLISTD DD  *
        LISTDEF DSNLDEF
                INCLUDE TABLESPACES DATABASE DSNADMDB
      //SYSIN    DD  *
      
        TEMPLATE DSNTPLT
                 DSN(prefix.IMAGCOPY.&DB..;&TS.;)
                 DISP (NEW,KEEP,DELETE)
                 UNIT TAPE
                 STACK YES
                 RETPD 99
             COPY LIST DSNLDEF
                 COPYDDN(DSNTPLT)
      /*
  2. Enable the administrative task scheduler and administrative enablement routines.

    The administrative task scheduler routines are DB2®-supplied routines that enable you to schedule work. The administrative enablement routines are DB2-supplied routines that enable you to execute administrative work immediately.

    Before you complete these steps, set up WLM application environments for the administrative task scheduler and administrative enablement routines, and either install the DB2-supplied routines during migration or install the DB2-supplied routines during installation. The steps for setting up WLM application environments and installing the DB2-supplied routines are in the IBM Information Management Software for z/OS Solutions Information Center and the DB2 for z/OS Installation and Migration Guide at http://publib.boulder.ibm.com/epubs/pdf/dsnigm05.pdf in the following locations:

    The following routines are administrative task scheduler routines:

    The following routines are administrative enablement routines:

    As part of the installation and migration process, job DSNTIJRT defines, binds, and grants access to these DB2-supplied routines.

    1. In the JCL for starting the WLM-established address space for running the stored procedures whose load module must reside in an APF-authorized library, ensure that all libraries in the STEPLIB DD concatenation are APF-authorized.
    2. If the BPX.DAEMON facility class is active and the BPX.DAEMON.HFSCTL facility class is not defined, perform the following actions for the stored procedures that must be registered to RACF® program control.

      The stored procedures that require RACF program control are:

      • ADMIN_COMMAND_UNIX
      • ADMIN_JOB_CANCEL
      • ADMIN_JOB_FETCH
      • ADMIN_JOB_QUERY
      • ADMIN_JOB_SUBMIT
      1. Customize and run job step DSNADER in job DSNTIJRA to define the security environment for these stored procedures. You need to uncomment job step DSNADER before you run job DSNTIJRA. You do not need to run any other job steps in job DSNTIJRA.
      2. Ensure that you have a WLM environment for these stored procedures that meets the following requirements:
        • The WLM-established stored procedure address space loads only controlled programs.
        • In the JCL for starting the WLM-established address space for running these stored procedures, ensure that all libraries in the STEPLIB DD concatenation are APF-authorized.
        • The name of this environment must match the WLMENV parameter value in the configuration control statement for each stored procedure in job DSNTIJRT.
      Recommendation:
      If you do not have a WLM environment defined to run these routines, use DB2 core WLM environment DSNWLM_GENERAL, except as follows:
      • ADMIN_COMMAND_DSN - Use DSNWLM_REXX
      • ADMIN_COMMAND_UNIX - Use DSNWLM_PGM_CONTROL
      • ADMIN_INFO_SYSLOG - Use DSNWLM_REXX
      • ADMIN_INFO_SYSPARM - Use DSNWLM_NUMTCB1
      • ADMIN_JOB_CANCEL - Use DSNWLM_PGM_CONTROL
      • ADMIN_JOB_FETCH - Use DSNWLM_PGM_CONTROL
      • ADMIN_JOB_QUERY - Use DSNWLM_PGM_CONTROL
      • ADMIN_JOB_SUBMIT - Use DSNWLM_PGM_CONTROL
      • ADMIN_UTL_MONITOR - Use DSNWLM_PGM_CONTROL

Configuring database groups for use with InfoSphere Optim Query Workload Tuner

To be able to capture a workload from the statement cache on all subsystems of a data sharing group, you must configure a database group in the client. This database group must contain the same members of the data sharing group in a Parallel Sysplex® in your DB2 for z/OS environment.

Before you begin

About this task

A data sharing group is a collection of one or more DB2 for z/OS subsystems that share DB2 data. A DB2 subsystem that belongs to a data sharing group is a member of that group. Each member can belong to one, and only one, data sharing group. All members of a data sharing group share the same DB2 catalog and directory, and all members must be in the same Parallel Sysplex. Data sharing improves the availability of DB2 data, extends the processing capacity of the system, provides more flexible ways to configure your environment, and increases transaction rates.

The SQL statements that are used by an application can run on multiple subsystems. If these subsystems are members of a data sharing group, you can configure a database group in the client to contain the same members. You can then define a workload to contain all SQL statements of the application from the statement cache, and capture the workload from the data sharing group.

For information about how data sharing work, see these references:

Procedure

To configure a database group in the client:

  1. To open the Database Groups view, click the Database Groups tab. If the tab is not visible, click Window > Show View > Database Groups.
  2. Configure a database group by creating a group and adding the same members of the data sharing group. Ensure that the connection profiles of a subsystem are correct when you add the subsystem to a group.