Installing and configuring SQL Tuning Services

This topic provides instructions for installing, configuring, and starting SQL Tuning Services.

1. Install SQL Tuning Services on your z/OS® system by following the instructions in the IBM Database Services Expansion Pack Program Directory.
   The SMP/E program installs some SQL Tuning Services components in a UNIX System Services directory and some components in partitioned data sets.

   • By default, the UNIX System Services components are installed in the /usr/lpp/IBM/db2tms/v2r1 directory, which is the parent folder of the SQL Tuning Services file system paths that are defined for DDNAME SDSN5TZF in the DSN5TDEF job. This directory is referred to as the install_dir_zos directory. For example, if the PATH of SDSN5TZF is defined to be /usr/lpp/IBM/db2tms/v2r1/IBM, the structure of install_dir_zos looks similar to the following example:

     
     _Dir      755   OMVSKERN         8192   tmsinstall
     _Dir      755   OMVSKERN         8192   tmsservice
     _Dir      755   OMVSKERN         8192   IBM

     The install_dir_zos/IBM directory contains two files: the DSN5DTMS pax file and the DSN5INSH script for unpacking the DSN5DTMS pax file.

     
   • Two partitioned data sets are created to contain the following components:

     hlq. SDSN5TSA

     This data set contains the sample JCL.

     hlq. SDSN5TDB

     This data set contains the DBRMs.

2. Configure the required SSL certificate.
   SQL Tuning Services uses the HTTPS protocol to secure network communications. You can use either a file-based keystore (PKCS12) or a key ring-based keystore (JCERACFKS).

   You can use an existing key ring if one is available on your system, or you can generate a file-based certificate on the z/OS system that you are installing SQL Tuning Services on. The certificate must be a SAN (Subject Alternative Name) or wildcard SSL certificate that allows the specification of multiple domains or hosts. Make sure that your SSL certificate contains the IP addresses of your SQL Tuning Services system. The certificate must be CA- or self-signed.

   If you decide to use a new CA-signed certificate, self-signed certificate, or an existing certificate pair (consisting of a certificate and a private key), complete the following steps to configure the certificate:

   1. Use an FTP program to copy the certificate in binary mode into the install_dir_zos/tmsservice/Config/security directory.
   2. Convert the certificate to the PKCS12 format.
3. Copy the install_dir_zos/tmsinstall/tmsservice.config file into a writable directory (new_dir/tmsservice.config) and edit the following installation options:

   liberty_path

   The system-level WebSphere® Liberty server PATH directory. If your environment does not use the system-level Liberty server, modify this option with the correct path.

   wlp_user_dir

   The user-defined WebSphere Liberty server directory. If the directory that you specify does not exist, the installation process creates it for you. If the directory that you specify does exist, the tms_setup_userid must have write access to it because the installation process copies files into this directory.

   server_name

   The new WebSphere Liberty server for SQL Tuning Services. When this server name is confirmed, $TMS_HOME will be wlp_user_dir/servers/server_name.

   Important: This name is used as BPX_JOBNAME when SQL Tuning Services is started.

   author_udf_admin

   The UDF name for the SQL Tuning Services administrator; for example, IBMTMS.CANADMINISTER.

   author_udf_user

   The UDF name for the SQL Tuning Services user; for example, IBMTMS.CANVIEW.

   host_name

   The hostname of the server on which SQL Tuning Services is installed.

   httpsport

   The security port of the server on which SQL Tuning Services is installed.

   log_level (optional)

   The level of details (ALL, DEBUG, ERROR, unrecoverable, information, OFF, TRACE, WARN) to be recorded in the authentication and training service logs. The default is information (for example, log_level=INFO).

   token_absolute_timeout (optional)

   The absolute timeout in milliseconds for a user.

   visual_explain_cache_timeout (optional)

   The amount of time (in seconds) that the cache for Visual Explainer is kept.

   visual_explain_resource_pool_size (optional)

   Specify a number that indicates the number of Visual Explain, Access Path Comparison, and Workload Access Path Comparison request results that can be cached for each user.

   Important: Specifying a large number causes SQL Tuning Services to use more memory and can negatively affect system performance. The recommended setting is a number less than 20.

   capture_query_environment_cache_timeout (optional)

   The amount of time (in days) that the result files of the Capture Query Environment function are kept in the server.

   query_job_list_batch_size (optional)

   The number of rows that are returned per batch by the Job Management function when jobs are queried.

4. Configure SQL Tuning Services by running the tmsservice.sh script in the install_dir_zos/tmsinstall directory:
   

   ./tmsservice.sh new_dir/tmsservice.config

   The script checks for the required certs files and other variables in your environment. When prompted, provide the following information:

   temporary username and password

   Specify a temporary username and password that are used before a repository database connection is set successfully in SQL Tuning Services. After the connection to the repository database is set successfully, this username and its corresponding password will be revoked immediately. You will need to log on to SQL Tuning Services with a tms_userid that is authorized to access the repository database.

   TLSCertificatePassword

   Specify the password of the TLS certificate file that is used to establish the https service through the WebSphere Liberty server. This value is case-sensitive.

   
5. Create the required UDFs in Db2 for z/OS, which is the repository database for SQL Tuning Services.
   The SQL Tuning Services repository service uses Db2 for z/OS for storing metadata tables. You must create an authentication UDF and grant access to users before you start the repository service:
   1. Follow the instructions in the DSN5RUDF file to customize this sample job.
   2. Submit your copy of the customized DSN5RUDF job to create the required UDFs and to grant required privileges to all tms_userid IDs.
   3. Verify that the job runs successfully (with a return code of 0).
6. Bind the required packages in Db2 for z/OS.
   To tune SQL on the database, you must bind the packages that SQL Tuning Services needs in advance.
   1. Follow the instructions in the DSN5NDRP and DSN5NDTG sample jobs to customize them for your environment.
   2. Submit your customized copy of the DSN5NDRP job to bind all the packages to the repository database. You need to submit this job only once because there is only one repository database.
   3. Submit your customized copy of the DSN5NDTG job to bind all packages to tuning target databases. You need to submit this job for each tuning target database.
   4. Optional: Submit your copy of the customized DSN5NBND job to bind all the packages to the same database where the repository database and target database reside together.
   5. Verify that the jobs run successfully.
7. Grant required privileges to the db2_authid_R and db2_authid_T IDs.
   1. Follow the instructions in the DSN5RTRP and DSN5RTTG sample jobs to customize them for your environment.
   2. Submit your customized copy of the DSN5RTRP job to grant required privileges to the db2_auth_R ID.
   3. Submit your customized copy of the DSN5RTTG job to grant required privileges to the db2_auth_T ID.
   4. Optional: If the repository database and target database are on the same system, submit your copy of the customized DSN5RGRT job to grant all the required privileges to the db2_authid_R and db2_authid_T IDs.
   5. Verify that the job runs successfully and ends with a return code of 0.
8. Optional: Set the access control on repodb_override.properties to 640 so that only authorized UNIX System Services users can read or modify it.
9. Customize the DSN5STRT sample JCL procedure to start the SQL Tuning Services server.