Creating a database profile and EXPLAIN tables

Tuning profiles allow a SQL Tuning Services server to access the Db2 for z/OS database that you will tune a SQL statement against. A tuning profile includes a valid Db2 for z/OS user ID and password, security information, and location information for the target database such as host, location, and port.

Note: Use of tuning profiles is optional in some cases. For more information, see Tuning without a tuning profile. However, if you are using security mechanism 18 (SSL Certificates), a tuning profile is required. The benefit of using tuning profiles is that they can be reused and shared. For example, a database administrator can create a single tuning profile with specific credentials and make it available to multiple Developer for z users. However, if you specify a Db2 for z/OS password when you create a tuning profile, this password must be updated whenever the password is updated in RACF. To avoid this additional overhead, you can use multi-factor authentication instead of a password when you create a tuning profile.

Creating a database connection profile

  1. In the Remote Systems view, expand the Db2 for z/OS subsystem.
  2. Expand SQL Tuning Services Servers and the server where you want to create a database connection profile.
  3. Right-click Database Connection Profiles and click New > Database Connection Profile. The Create Database Connection Profile wizard opens.
  4. Select the existing Db2 for z/OS connection to associate with the new tuning profile. The configuration of the selected connection will be copied into the newly created profile.
  5. In the Profile name field, type a name for the new database connection profile.
  6. Click Next.
  7. On the Advanced Settings page, you can specify SSL security settings.
  8. Click Finish.

Supported Security Mechanisms

The following security mechanisms are supported for SQL Tuning services. Your ability to use a tuning profile depends on the security mechanism in use:
  • Security mechanism 18 (SSL Certificates): If you are using this mechanism to tune SQL, you must create and use a tuning profiles to tune SQL. For more information, see Creating a database connection profile.
  • Security mechanisms 7 and 9 (Encrypted User ID and/or Password): These mechanisms do not support to tuning profiles. If you are using either, see Tuning without a profile for more information.
  • Security mechanism 3 (User ID and Password): This mechanism supports both methods for tuning SQL.

Generating EXPLAIN Tables for a Profile

The tuning services use EXPLAIN tables to store information about SQL statements, access paths, and other elements that can affect SQL performance. The tuning connection profile needs its own set of EXPLAIN tables. To perform this action the user ID must be authorized to create EXPLAIN tables. Contact your Db2® for z/OS® administrator for assistance.

  1. In the Remote Systems view, expand Db2 for z/OS > SQL Tuning Services Servers, and navigate to a server where your database connection profile is defined.
  2. Right-click the profile and click Create EXPLAIN Tables. The Create EXPLAIN tables wizard opens.
  3. Enter values for the EXPLAIN table options and click Next to advance through the wizard pages.

    Use this wizard to specify options for an EXPLAIN table. All of the options in this wizard are optional. If you do not specify any values in this wizard, then the EXPLAIN tables are created with default values or with values inherited from the equivalent database connection options.

    For information about EXPLAIN tables, see the related information. EXPLAIN table options links to information about the fields in this wizard.

  4. To create the tables, click Finish.
You can also do these tasks with database connection profiles:
  • Update the profile to change the name or associate it with a different database connection.
  • Share and revoke access to a profile that you created.
  • Standardize all existing EXPLAIN tables to the format for the current Db2 version, and create any missing tables.
  • Drop all existing EXPLAIN tables.