Example connection configurations

SQL Tuning Services supports a variety of mechanisms for securely connecting to the repository database and to Db2® target databases.

This topic provides example configuration settings to help you set up an SQL Tuning Services environment. Copy and modify the appropriate example based on your environment

Example tmsservice.config settings for specifying the repository database

Use the following examples to configure a connection to the repository database by specifying the appropriate values in the Repository Database Configuration section of the tmsservice.config file. Alternatively, you can create a connection to the repository database by using the set_repo API.

Examples are provided for the following types of security mechanisms:

Security mechanism 3, non-SSL
Security mechanism 3, SSL with key ring
PassTickets

Security mechanism 3, non-SSL

#######################################################################
#--------- Repository Database Configuration ---------
#- If you are using PassTickets for the repository credential,
#- specify the input parameters here.
#-
#- If you are using a username and password for repository credential
#- and you already have the password, specify the input parameters here.
#-
#- If you are using a username and password for repository credential
#- and you do not already have the password, skip this section and use
#- the /tuningservice/v1/set_repo API to set up the repository database
#- after the SQL Tuning Services server has been started.
#######################################################################
# Host name of the repository database
host={db2_R}

# Port of the repository database
port={db2_R_port}

# Location name of the repository database
locationName={location}

# User ID used to connect to the repository database
user={sqlts_repodb_id}

# Optional: Set to passticket when users connect to the repository
# database with passticket
credential_type=

# Conditional: Application ID is the applname of the subsystem. This 
# parameter is required only when the specified credential_type is 
# passticket.
appl_id=

# To set up a AT-TLS connection to the repository database, set
# sslConnection to true and choose to specify the following variable
# values.
sslConnection=false
sslTrustStoreLocation=
sslCertLocation=
sslTrustStoreType=
sslTrustStorePassword=

Security mechanism 3, SSL with key ring

#######################################################################
#--------- Repository Database Configuration ---------
#- If you are using PassTickets for the repository credential,
#- specify the input parameters here.
#-
#- If you are using a username and password for repository credential
#- and you already have the password, specify the input parameters here.
#-
#- If you are using a username and password for repository credential
#- and you do not already have the password, skip this section and use
#- the /tuningservice/v1/set_repo API to set up the repository database
#- after the SQL Tuning Services server has been started.
#######################################################################
# Host name of the repository database
host={db2_R}

# Port of the repository database
port={db2_R_port}

# Location name of the repository database
locationName={location}

# User ID used to connect to the repository database
user={sqlts_repodb_id}

# Optional: Set to passticket when users connect to the repository
# database with passticket
credential_type=

# Conditional: Application ID is the applname of the subsystem. This 
# parameter is required only when the specified credential_type is 
# passticket.
appl_id=

# To set up a AT-TLS connection to the repository database, set
# sslConnection to true and choose to specify the following variable
# values.
sslConnection=true
sslTrustStoreLocation={safkeyring://racf-id/ring-id}
sslCertLocation=
sslTrustStoreType=JCERACFKS
sslTrustStorePassword=password

PassTickets

#######################################################################
#--------- Repository Database Configuration ---------
#- If you are using PassTickets for the repository credential,
#- specify the input parameters here.
#-
#- If you are using a username and password for repository credential
#- and you already have the password, specify the input parameters here.
#-
#- If you are using a username and password for repository credential
#- and you do not already have the password, skip this section and use
#- the /tuningservice/v1/set_repo API to set up the repository database
#- after the SQL Tuning Services server has been started.
#######################################################################
# Host name of the repository database
host={db2_R}

# Port of the repository database
port={db2_R_port}

# Location name of the repository database
locationName={location}

# User ID used to connect to the repository database
user={sqlts_repodb_id}

# Optional: Set to passticket when users connect to the repository
# database with passticket
credential_type=passticket

# Conditional: Application ID is the applname of the subsystem. This 
# parameter is required only when the specified credential_type is 
# passticket.
appl_id={appl_id}

# To set up a AT-TLS connection to the repository database, set
# sslConnection to true and choose to specify the following variable
# values.
sslConnection=false
sslTrustStoreLocation=
sslCertLocation=
sslTrustStoreType=
sslTrustStorePassword=

Example POST requests for specifying the repository database

Use the following examples to configure a connection to the repository database by using the set_repo. API. Alternatively, you can create a connection to the repository database by using the tmsservice.config file.

The following examples use the Bearer token for the SQL Tuning Services ID that was created in Invoking SQL Tuning Services APIs.

Examples are provided for the following types of security mechanisms:

Security mechanism 3, non-SSL
Security mechanism 3, SSL with key ring

Security mechanism 3, non-SSL

curl -X POST 'https://service_ip:httpsport/tuningservice/v1/set_repo' \
--header 'Authorization: Bearer Bearer_token_for_temporary_username' \
--header 'Content-Type: application/json' \
--data-raw '{
  "credential": {
    "user": "{sqlts_repodb_id}",
    "password": "{password}"
  },
  "host": "{Db2_host_R}",
  "location": "{location}",
  "port": "{port_number}",
  "sslConnection": "false"
}'

Security mechanism 3, SSL with key ring

curl -X POST 'https://service_ip:httpsport/tuningservice/v1/set_repo' \
--header 'Authorization: Bearer Bearer_token_for_temporary_username' \
--header 'Content-Type: application/json' \
--data-raw '{
  "credential": {
    "user": "{sqlts_repodb_id}",
    "password": "{password}"
  },
  "host": "{Db2_host_R}",
  "location": "{location}",
  "port": "{port_number}",
  "sslConnection": "true",
  "sslTrustStoreLocation": "{safkeyring://racf-id/ring-id}",
  "sslTrustStorePassword": "password",
  "additionalProperties": {
    "sslTrustStoreType": "JCERACFKS"
  }
}'

Example POST requests for creating a tuning profile

To create a tuning profile, copy the appropriate example based on the security mechanism in use in your environment, modify it, and insert it into a POST request for the connections API. Although any authorized SQL Tuning Services user can create a tuning profile, the following examples use the Bearer token for the SQL Tuning Services ID that was created in Invoking SQL Tuning Services APIs.

Tip: To configure an IPv6 connection, when you specify the host parameter, specify the IPv6 address and enclose it in brackets. For example:

"host": "[::ffff:9.30.84.95]",

Examples are provided for the following types of security mechanisms:

Security mechanism 3, non-SSL
Security mechanism 3, SSL with key ring
Security mechanism 7 or 9 with encryption algorithm
PassTickets

Security mechanism 3, non-SSL

curl -X 'POST' \ 
  'https://service_ip:httpsport/tuningservice/v1/connections' \ 
  -H 'Accept: application/json' \
  -H 'Authorization: Bearer Bearer_token_for_the_SQL_Tuning_Services_ID' \
  -H 'Content-Type: application/json' \
  -d '{
  "name": "{profile_name}",
  "host": "{Db2_host}",
  "location": "{location}",
  "port": "{port_number}",
  "sslConnection": "false",
  "collection_cred": {
    "user": "{sqlts_targetsys_id}",
    "password": "{password}",
    "security_mechanism": "3"
  }
}'

SSL with key ring

curl -X 'POST' \ 
  'https://service_ip:httpsport/tuningservice/v1/connections' \ 
  -H 'Accept: application/json' \
  -H 'Authorization: Bearer Bearer_token_for_the_SQL_Tuning_Services_ID' \
  -H 'Content-Type: application/json' \
  -d '{
  "collection_cred": {
    "user": "{sqlts_targetsys_id}",
    "password": "{password}"
  },
  "name": "{profile_name}",
  "host": "{Db2_host}",
  "location": "{location}",
  "port": "{port_number}",
  "sslConnection": "true",
  "sslTrustStoreLocation": "{safkeyring://racf-id/ring-id}",
  "sslTrustStorePassword": "password",
  "additionalProperties": {
    "sslTrustStoreType": "JCERACFKS"
  }
}'

Security mechanism 7 or 9 with encryption algorithm

curl -X 'POST' \ 
  'https://service_ip:httpsport/tuningservice/v1/connections' \ 
  -H 'Accept: application/json' \
  -H 'Authorization: Bearer Bearer_token_for_the_SQL_Tuning_Services_ID' \
  -H 'Content-Type: application/json' \
  -d '{
  "collection_cred": {
    "user": " {sqlts_targetsys_id} ",
    "password": "{password}",
    "security_mechanism": "9"
  },
  "host": " {Db2_host} ",
  "location": " {location} ",
  "name": " {profile_name} ",
  "port": " {port_number} ",
  "additionalProperties": {
    "encryptionAlgorithm": "{algorithm}"
  }
}'

PassTickets

curl -X 'POST' \ 
  'https://service_ip:httpsport/tuningservice/v1/connections' \ 
  -H 'Accept: application/json' \
  -H 'Authorization: Bearer Bearer_token_for_the_SQL_Tuning_Services_ID' \
  -H 'Content-Type: application/json' \
  -d '{
  "collection_cred": {
    "credential_type": "passticket",
    "user": "{sqlts_targetsys_id}",
    "security_mechanism": "3",
    "appl_id": "{appl_id}"
  },
  "name": "{profile_name}",
  "host": "{Db2_host}",
  "location": "{location}",
  "port": "{port_number}"
}'

Example POST requests for creating EXPLAIN tables

To create the EXPLAIN tables for SQL Tuning Services on the target Db2 database, copy and modify the appropriate example based on whether you are using a tuning profile and the security mechanism in use in your environment.

The EXPLAIN tables that you create are qualified by a user ID for you or your group. Only the SQL Tuning Services administrator or the user who owns the database connection can invoke this API.

To invoke the EXPLAIN Tables Management API, the user must be authorized to call the ADMIN_EXPLAIN_MAINT store procedure. Make sure that the account you use has the proper privileges to create the EXPLAIN tables as documented in Required user IDs and permissions

The following examples use the Bearer token for the SQL Tuning Services ID that was created in Invoking SQL Tuning Services APIs.

Examples are provided for the following types of connections:

With the use of a tuning profile
Without a tuning profile, security mechanism 3, non-SSL
Without a tuning profile and with SSL with key ring
Without a tuning profile, security mechanism 7 or 9 with encryption algorithm
Without a tuning profile and with a PassTicket
Without a tuning profile and with multi-factor authentication (MFA)

Example for use with a tuning profile

curl -X 'POST' \ 
  'https://service_ip:httpsport/tuningservice/v1/explaintb' \ 
  -H 'Accept: application/json' \
  -H 'Authorization: Bearer Bearer_token_for_the_SQL_Tuning_Services_ID' \
  -H 'Content-Type: application/json' \
  -d '{
  "action": "CREATE",
  "bp16kblob": "BP16K0",
  "bp32kblob": "BP32K",
  "bp4kblob": "BP0",
  "bp8kblob": "BP8K0",
  "connection": "{profile_name}",
  "database_name": null,
  "ixbufferpool": "BP0",
  "managealias": "NO",
  "mode": "RUN",
  "schema_alias": null,
  "schema_name": "TMSDEV",
  "storagegroup": "SYSDEFLT",
  "storagegroup_idx": "SYSDEFLT",
  "tableset": "ALL",
  "ts16kbufferpool": "BP16K0",
  "ts32kbufferpool": "BP32K",
  "ts4kbufferpool": "BP0",
  "ts8kbufferpool": "BP8K0"
}'

Example for use without tuning profile, security mechanism 3, non-SSL

curl -X 'POST' \ 
  'https://service_ip:httpsport/tuningservice/v1/explaintb' \ 
  -H 'Accept: application/json' \
  -H 'Authorization: Bearer Bearer_token_for_the_SQL_Tuning_Services_ID' \
  -H 'Content-Type: application/json' \
  -d '{
  "action": "CREATE",
  "auth_id": null,
  "bp16kblob": "BP16K0",
  "bp32kblob": "BP32K",
  "bp4kblob": "BP0",
  "bp8kblob": "BP8K0",
  "connection_attributes": {
    "collection_cred": {
      "user": "{sqlts_targetsys_id}",
      "password": "{password}"
    },
    "host": "{Db2_host}",
    "location": "{location}",
    "port": "{port_number}",
    "sslConnection": "false"
  },
  "database_name": null,
  "ixbufferpool": "BP0",
  "managealias": "NO",
  "mode": "RUN",
  "schema_alias": null,
  "schema_name": "TMSDEV",
  "storagegroup": "SYSDEFLT",
  "storagegroup_idx": "SYSDEFLT",
  "tableset": "ALL",
  "ts16kbufferpool": "BP16K0",
  "ts32kbufferpool": "BP32K",
  "ts4kbufferpool": "BP0",
  "ts8kbufferpool": "BP8K0"
}'

Example for use without tuning profile with SSL with key ring

curl -X 'POST' \ 
  'https://service_ip:httpsport/tuningservice/v1/explaintb' \ 
  -H 'Accept: application/json' \
  -H 'Authorization: Bearer Bearer_token_for_the_SQL_Tuning_Services_ID' \
  -H 'Content-Type: application/json' \
  -d '{
  "action": "CREATE",
  "auth_id": null,
  "bp16kblob": "BP16K0",
  "bp32kblob": "BP32K",
  "bp4kblob": "BP0",
  "bp8kblob": "BP8K0",
  "connection_attributes": {
    "collection_cred": {
      "user": "{sqlts_targetsys_id}",
      "password": "{password}"
    },
    "host": "{Db2_host}",
    "location": "{location}",
    "port": "{port_number}",
    "sslConnection": "true",
    "sslTrustStoreLocation": "{safkeyring://racf-id/ring-id}",
    "sslTrustStorePassword": "password",
    "additionalProperties": {
      "sslTrustStoreType": "JCERACFKS"
    }
  },
  "database_name": null,
  "ixbufferpool": "BP0",
  "managealias": "NO",
  "mode": "RUN",
  "schema_alias": null,
  "schema_name": "TMSDEV",
  "storagegroup": "SYSDEFLT",
  "storagegroup_idx": "SYSDEFLT",
  "tableset": "ALL",
  "ts16kbufferpool": "BP16K0",
  "ts32kbufferpool": "BP32K",
  "ts4kbufferpool": "BP0",
  "ts8kbufferpool": "BP8K0"
}'

Example for use without a tuning profile, security mechanism 7 or 9 with encryption algorithm


curl -X 'POST' \ 
  'https://service_ip:httpsport/tuningservice/v1/explaintb' \ 
  -H 'Accept: application/json' \
  -H 'Authorization: Bearer Bearer_token_for_the_SQL_Tuning_Services_ID' \
  -H 'Content-Type: application/json' \
  -d '{
  "action": "CREATE",
  "auth_id": null,
  "bp16kblob": "BP16K0",
  "bp32kblob": "BP32K",
  "bp4kblob": "BP0",
  "bp8kblob": "BP8K0",
  "connection_attributes": {
    "collection_cred": {
      "user": "{sqlts_targetsys_id}",
      "password": "{password}",
      "security_mechanism": "9"
    },
    "host": "{Db2_host}",
    "location": "{location}",
    "port": "{port_number}",
    "sslConnection": "false",
    "additionalProperties": {
      "encryptionAlgorithm": "{algorithm}"
    }
  },
  "database_name": null,
  "ixbufferpool": "BP0",
  "managealias": "NO",
  "mode": "RUN",
  "schema_alias": null,
  "schema_name": "TMSDEV",
  "storagegroup": "SYSDEFLT",
  "storagegroup_idx": "SYSDEFLT",
  "tableset": "ALL",
  "ts16kbufferpool": "BP16K0",
  "ts32kbufferpool": "BP32K",
  "ts4kbufferpool": "BP0",
  "ts8kbufferpool": "BP8K0"
}'

Example for use without a tuning profile and with a PassTicket

curl -X 'POST' \ 
  'https://service_ip:httpsport/tuningservice/v1/explaintb' \ 
  -H 'Accept: application/json' \
  -H 'Authorization: Bearer Bearer_token_for_the_SQL_Tuning_Services_ID' \
  -H 'Content-Type: application/json' \
  -d '{
  "action": "CREATE",
  "auth_id": null,
  "bp16kblob": "BP16K0",
  "bp32kblob": "BP32K",
  "bp4kblob": "BP0",
  "bp8kblob": "BP8K0",
  "connection_attributes": {
    "collection_cred": {
      "credential_type": "passticket",
      "security_mechanism": "3",
      "user": "{sqlts_targetsys_id}",
      "appl_id": "{appl_id}"
    },
    "host": "{Db2_host}",
    "location": "{location}",
    "port": "{port_number}"
  },
  "database_name": null,
  "ixbufferpool": "BP0",
  "managealias": "NO",
  "mode": "RUN",
  "schema_alias": null,
  "schema_name": "TMSDEV",
  "storagegroup": "SYSDEFLT",
  "storagegroup_idx": "SYSDEFLT",
  "tableset": "ALL",
  "ts16kbufferpool": "BP16K0",
  "ts32kbufferpool": "BP32K",
  "ts4kbufferpool": "BP0",
  "ts8kbufferpool": "BP8K0"
}'

Example for use without a tuning profile and with multi-factor authentication (MFA)

Use the IBM® Verify application to generate a QR code, and then scan the QR code to obtain an MFA token (in the following example, the token is MFAToken).

curl -X 'POST' \ 
  'https://service_ip:httpsport/tuningservice/v1/explaintb' \ 
  -H 'Accept: application/json' \
  -H 'Authorization: Bearer Bearer_token_for_the_SQL_Tuning_Services_ID' \
  -H 'Content-Type: application/json' \
  -d '{
  "action": "CREATE",
  "auth_id": null,
  "bp16kblob": "BP16K0",
  "bp32kblob": "BP32K",
  "bp4kblob": "BP0",
  "bp8kblob": "BP8K0",
  "connection_attributes": {
    "collection_cred": {
      "security_mechanism": "3",
      "user": "{sqlts_targetsys_id}",
      "password": "{password}:{MFAToken}"
    },
    "host": "{Db2_host}",
    "location": "{location}",
    "port": "{port_number}"
  },
  "database_name": null,
  "ixbufferpool": "BP0",
  "managealias": "NO",
  "mode": "RUN",
  "schema_alias": null,
  "schema_name": "TMSDEV",
  "storagegroup": "SYSDEFLT",
  "storagegroup_idx": "SYSDEFLT",
  "tableset": "ALL",
  "ts16kbufferpool": "BP16K0",
  "ts32kbufferpool": "BP32K",
  "ts4kbufferpool": "BP0",
  "ts8kbufferpool": "BP8K0"
}'

Example POST requests for running tuning actions

To run a tuning action, copy the appropriate example based on whether you use a tuning profile and the security mechanism in use in your environment, modify it, and insert it into a POST request for the action that you want to run. The examples in this section invoke the Visual Explainer API.

The following examples use the Bearer token for the SQL Tuning Services ID that was created in Invoking SQL Tuning Services APIs.

Note: The tuning features that are available depend on whether you are using SQL Tuning Services that is provided with IBM Database Services Expansion Pack or SQL Tuning Services that is provided with Db2 Query Workload Tuner for z/OS®. See Overview of SQL Tuning Services for more information.

Make sure that the account that you use has the proper privileges to invoke the Visual Explainer API as documented in Required user IDs and permissions.

When you submit a tuning job by invoking a SQL Tuning Services API, the job runs that use the database connection credential ID.

Examples are provided for the following types of connections:

With the use of a tuning profile
Without a tuning profile and with SSL with key ring
Without a tuning profile and with a PassTicket

The format of the collection credientials for all other security mechanism types is identical to the examples in Example POST requests for creating EXPLAIN tables. See that section for details.

Example to run Visual Explain with a tuning profile


curl -X 'POST' \ 
  'https://service_ip:httpsport/tuningservice/v1/ve' \ 
  -H 'Accept: application/json' \
  -H 'Authorization: Bearer Bearer_token_for_the_SQL_Tuning_Services_ID' \
  -H 'Content-Type: application/json' \
  -d '{
    "sql_text": "select * from sysibm.systables",
    "explain_schema": "{schema_explain}",
    "connection":"{tuning_profile_name}" 
}'

Example to run Visual Explain without a tuning profile and with SSL with key ring

curl -X 'POST' \ 
  'https://service_ip:httpsport/tuningservice/v1/ve' \ 
  -H 'Accept: application/json' \
  -H 'Authorization: Bearer Bearer_token_for_the_SQL_Tuning_Services_ID' \
  -H 'Content-Type: application/json' \
  -d '{
  "sql_text": "select * from sysibm.systables",
  "explain_schema": "{schema_explain}",
  "connection_attributes": {
    "collection_cred": {
      "password": "{password}",
      "user": "{sqlts_targetsys_id}"
    },
    "data_server_type": "DB2Z",
    "host": "{Db2_host}",
    "location": "{location}",
    "port": "{port_number}",
    "sslConnection": "true",
    "sslTrustStoreLocation": "{safkeyring://racf-id/ring-id}",
    "sslTrustStorePassword": "password",
    "additionalProperties": {
      "sslTrustStoreType": "JCERACFKS"
    }
  }
}'

Example to run Visual Explain without a tuning profile and with a PassTicket

curl -X 'POST' \ 
  'https://service_ip:httpsport/tuningservice/v1/ve' \ 
  -H 'Accept: application/json' \
  -H 'Authorization: Bearer Bearer_token_for_the_SQL_Tuning_Services_ID' \
  -H 'Content-Type: application/json' \
  -d '{
  "sql_text": "select * from sysibm.systables",
  "explain_schema": "{schema_explain}",
  "connection_attributes": {
    "collection_cred": {
      "credential_type": "passticket",
      "security_mechanism": "3",
      "user": "{sqlts_targetsys_id}",
      "appl_id": "{appl_id}"
    },
    "data_server_type": "DB2Z",
    "host": "{Db2_host}",
    "location": "{location}",
    "port": "{port_number}"
  }
}'