Creating a tuning profile

A tuning profile is a set of JDBC connection information that makes a Db2® for z/OS® tuning target subsystem available to SQL Tuning Services. It includes a valid Db2 for z/OS user ID and password, security information, and location information for the tuning target subsystem (host, location, and so on).

Before you begin

Using a tuning profile is optional in some situations. For example, with SQL Tuning Services APAR PH60806 applied, a tuning profile is not required to use the following tuning features:

Access Path Advisor Capture Query Environment Index Advisor Query Rewrite Advisor	SQL Annotator SQL Capture (requires APAR PH65492) SQL Formatter Statistics Advisor Visual Explain

However, you still might want to use a tuning profile in the following situations:

You don’t have a user interface to streamline the tuning workflow. By creating a tuning profile, you don’t need to specify Db2 connection information every time you invoke a tuning API.
To simplify privilege management and SQL Tuning Services administration. A DBA can create a single tuning profile with an ID that has tuning privileges and share this tuning profile with multiple users. All users can use this common user ID for tuning purposes.
If your Db2 environment is enabled with RACF PassTickets. By creating a tuning profile with the credential type of PassTicket, you don't need to manage passwords or specify connection information for every tuning activity.

About this task

The following instructions describe how to create a tuning profile by using the SQL Tuning Services Connection Profile Management API through the Swagger interface. However, if you are using Db2 Administration Foundation or Db2 Developer Extension, you can use these products' user interfaces to create tuning profiles more easily.

Any SQL Tuning Services user can create and manage their own tuning profile. An SQL Tuning Services administrator can manage all tuning profiles.

When you create a tuning profile, the user ID and password for the Db2 for z/OS tuning target subsystem is stored in the repository database. Because the password persists on the repository database, whenever that password changes, the tuning profile must be modified with the new password.

Procedure

Complete steps 1-3 of Invoking SQL Tuning Services APIs to open the Swagger interface and authorize your ID.
Expand /tuningservice/v1/connections and click Try it out.

Specify the payload to create your tuning profile. For examples based on supported security mechanisms, see Example POST requests for creating a tuning profile.

For example, the following payload is for SSL with a key ring:


{
  "collection_cred": {
    "user": "{sqlts_dba_id}",
    "password": "{passw0rd}"
  },
  "name": "{profile_name}",
  "host": "{Db2_host}",
  "location": "{location}",
  "port": "{port_number}",
  "sslConnection": "true",
  "sslTrustStoreLocation": "{safkeyring://racf-id/ring-id}",
  "sslTrustStorePassword": "password",
  "additionalProperties": {
    "sslTrustStoreType": "JCERACFKS"
  }
}'

Note: SQL Tuning Services uses the default collection ID of NULLID for Db2 Connect packages. To use a different collection ID, include the currentPackagePath property in the Connection Profile Management API, as shown in the following example:

{
"collection_cred":{
  "user": "sysadm",
  "password": "passw0rd"
},
"host": "SVLTEST.svl.ibm.com",
"location": "DB01",
"port": "9555",
"sslConnection": "false",
"additionalProperties": {
"currentPackagePath": "NULLID_V12R1M504, IBMTMS"
},
"name":"DB01"
}