Installing and configuring SQL Tuning Services

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

Before you begin

Make sure that you complete all of the prior steps in the installation roadmap.

Procedure

  1. Follow the instructions in the IBM® Database Services Expansion Pack Program Directory to install the SQL Tuning Services scoring service and other SMP/E-installable components on z/OS®.
    The SMP/E program installs SQL Tuning Services in the default /usr/lpp/IBM/db2tms/ directory, which is referred to as the install_dir_zos directory. 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   wlp

    The install_dir_zos/IBM directory contains two parts: the DSN5DTMS pax file and the DSN5INSH script for unpacking it when applying.

  2. Change the ownership of the files in the /usr/lpp/IBM/db2tms directory to the tms_setup_userid ID.
  3. Configure the required SSL certificate.
    • SQL Tuning Services uses the AT-TLS protocols to secure network communications. You can generate the required certificate on the z/OS system where you will install SQL Tuning Services. 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.
  4. 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 12 for z/OS for storing metadata tables. You must create an authentication UDF before you start the repository service:
    1. Verify that your db2_authid_R ID has the required privileges.
    2. Copy the TMSCRUDF file from the install_dir_zos/tmsservice/tmssamp directory into a PDS.
      Note: The encoding of TMSCRUDF is iso8859-1.
    3. Follow the instructions in the file to customize the sample job.
    4. Submit your copy of the customized TMSCRUDF job to create the required UDFs and to grant required privileges to all tms_userid. IDs.
    5. Verify that the job runs successfully (with a return code of 0).
  5. Bind the required packages in Db2 for z/OS, which is the database for tuning jobs.
    To tune SQL on the database, you must bind the packages that SQL Tuning Services needs in advance.
    1. Verify that your db2_authid_T has the required privileges.
    2. Copy the TMSBIND file from the install_dir_zos/tmsservice/tmssamp directory into a PDS.
      Note: The encoding of TMSBIND is iso8859-1.
    3. In binary mode, copy all the DBRMs in the install_dir_zos/tmsservice/tmsdbrm directory into a PDS data set with the attributes: FB, RCL80, BLKSIZE 6160.
    4. Follow the instructions in the file to customize the sample job.
    5. Submit your copy of the customized TMSBIND job to bind all of the packages.
    6. Verify that the job runs successfully (with a return code of 0).
  6. Edit the install_dir_zos/tmsinstall/tmsservice.config file and update the following installation options:
    liberty_path
    The WebSphere® Liberty server PATH directory. The default value is install_dir_zos/wlp.
    wlp_user_dir
    The user-defined WebSphere Liberty server 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.
    appl_id (optional)
    The application ID that was set in RACF® when the specified credential type is PassTicket.
    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.
    service_ip
    The IP address of the server on which SQL Tuning Servicesis 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, INFO, OFF, TRACE, WARN) to be recorded in the authentication and training service logs. The default is INFO (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 request results that can be cached for each user.
    Important: Specifying a large number will cause SQL Tuning Services to consume 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.
    queryJobListBatchSize (optional)
    The number of rows that are returned per batch by the Job Management function when jobs are queried.
    host
    The IP address of the Db2 for z/OS subsystem that's used as the repository database.
    port
    The port of the Db2 for z/OS subsystem that's used as the repository database.
    databaseName
    The location name of the Db2 for z/OS subsystem that's used as the repository database. This value is case-sensitive.
    credential_type (optional)
    Specify passticket if you use PassTickets as the authentication method.
    user
    The username of the db2_authid_R ID that's used to access the Db2 for z/OS subsystem that's used as the repository database.
    sslConnection
    Specify ‘true’ if you use an SSL connection to the repository database.
    sslTrustStoreLocation (optional)
    The location of the SSL truststore file.
    sslKeyStoreLocation (optional)
    The location of the SSL keystore file.
    sslCertLocation
    The location of the SSL certificate file that's used to establish a TTLS connection to the repository database.
    sslClientCertFileLocation
    Specify the location of the AT-TLS certificate file that's used to establish https service through the WebSphere Liberty server.
  7. Run the tmsservice.sh script in the install_dir_zos/tmsinstall directory to configure SQL Tuning Services:
    ./tmsservice.sh tmsservice.config
    The script checks for the required certs files and other variables in your environment. When prompted, provide the following information:
    password
    Specify the password of the db2_authid_R ID.
    sslTrustStorePassword (optional)
    Specify the password of the SSL truststore file. This value is case-sensitive.
    sslKeyStorePassword (optional)
    Specify the password of the SSL keystore file. This value is case-sensitive.
    TLSCertificatePassword
    Specify the password of the AT-TLS certificate file that's used to establish https service through the WebSphere Liberty server. This value is case-sensitive.

    When the tmsservice.sh script completes, the SQL Tuning Services services are started.

    To stop and restart the SQL Tuning Services services, use the tms-stop.sh script and tms-start.sh script, which are in the install_dir_zos directory.

    By default, the process of the SQL Tuning Services services uses server_name as the job name.

  8. Optional: Set access control on repodb_override.properties to 640 so that only authorized USS users can read or modify it.

Results

After all services are successfully deployed and started, the process of installation, configuration, and service deployment is complete. SQL Tuning Services is ready for use by authorized users.