Configuring a DB2 for z/OS subsystem to tune SQL statements from IBM Data Studio

You can connect to a DB2® for z/OS® subsystem from IBM® Data Studio and configure the subsystem to tune single SQL statements by using a wizard.

Before you begin

About this task

You can configure your DB2 for z/OS subsystem from Data Studio. During the configuration, you can follow the Configure Subsystem for SQL Tuning wizard to bind the required packages, create EXPLAIN and Query Tuner tables, and grant privileges and access to users. If these objects exist on the subsystem in a different format or version, you see a message. Follow the instructions in the message to migrate them.

Procedure

To configure a DB2 for z/OS subsystem to tune SQL statements from Data Studio:

  1. Optional: Specify whether to preserve the contents of and privileges for the EXPLAIN tables during the migration process. By default, the contents and privileges are preserved during the migration process. From the main menu, click Window > Preferences. In the Preferences window, expand the Data Management branch, and click Query Tuner to change these settings.
  2. In the Data Source Explorer, right-click the database icon for the DB2 for z/OS subsystem, and click Query Tuner > Configure for Tuning > Guided Configuration. The Configure Subsystem for SQL Tuning wizard opens.
  3. If you see a message that the format of some EXPLAIN tables and Query Tuner tables is not correct, follow the instructions in the message to migrate the tables.
  4. Follow the instructions in the wizard to bind the required packages, create EXPLAIN tables and other Query Tuner tables to be used by the tuning advisor, and grant privileges and access to users.
  5. Verify that the subsystem is configured for query tuning:
    1. In the Data Source Explorer, right-click the database that you configured, and then click Query Tuner > Configure for Tuning > Advanced Configuration and Privilege Management. The Advanced Configuration and Privilege Management window opens.

      You can check the status of the Query Tuner packages, Query Tuner tables, EXPLAIN tables, and the query tuning features in this window.

    2. In the Query Tuner section, follow the instructions in the message to bind the packages if the Query Tuner packages are not enabled.
    3. In the Query Tuner section, follow the instructions in the message to migrate or create the tables if the Query Tuner tables are not enabled.
    4. In the EXPLAIN tables section, follow the instructions in the message to migrate or create the EXPLAIN tables if the EXPLAIN tables are not enabled.

Results

If you successfully configured the subsystem, you can access the Data Studio IDE and Data Studio stand-alone features. For a list of features, see Single-query tuning features by product.

Feedback