Reconfiguring SQL Tuning Services

To change your SQL Tuning Services configuration after the initial installation and configuration, you need to modify one or more configuration files depending on the types of changes that you want to make.

Procedure

  1. Edit the following configuration files, which are located in the TMS_HOME/Config directory, and update the parameters as needed.
    Configuration properties are defined in three separate configuration files:
    application.properties
    This file contains the following system-level global settings:
    log4j2-related settings:
    logger.ibm.appenderRef.rollingFileInfo.ref=rollingFileInfo
    The logging options set name.
    property.LOG_FILE_PATH=logs
    The file path for logging files.
    property.APP_NAME=T.S
    The prefix for the names of logging files.
    property.CHARSET=utf-8
    The encoding set for logging files.
    property.ROLLING_INTERVAL=1
    How often a log rollover occurs based on the most specific time unit in the date pattern.
    property.MAX_LOG_SIZE=10MB
    The maximum size for logging files.
    property.MAX_HISTORY=30
    The maximum number of compressed logging files that are retained.
    property.MAX_AGE=15d
    The amount of time, in days, that logging files are retained.
    property.LOG_PATTERN=%date{HH:mm:ss.SSS}${APP_NAME}[%logger{64}]%-5level-%msg%n
    The naming convention that's used to convert the logging file name from console.log to the name defined by LOG PATTERN.
    Circuit breaker settings
    A circuit breaker pattern is a lightweight fault tolerance capability that you can use to protect SQL Tuning Services from saturated access and continuing failure (for example, a high volume of visits during business peak time). Circuit breakers can proactively isolate and then recover the protected APIs quickly based on statistical trends in service layer monitoring. Currently, the protection is available on the token generation and connection profile synchronous APIs.

    The following circuit breaker properties can be customized for your runtime environment and specific business requirements:

    failureRateThreshold
    The failure rate threshold as a percentage. When the failure rate is equal to or greater than the threshold, the circuit breaker transitions to open and starts short-circuiting calls. The default value is 50.
    slowCallRateThreshold
    The slow call rate threshold as a percentage. The circuit breaker considers a call to be slow when the call duration is greater than the slowCallDurationThreshold. When the percentage of slow calls is equal to or greater than the threshold, the circuit breaker transitions to open and starts short-circuiting calls. The default value is 100.
    slowCallDurationThreshold
    The duration threshold above which calls are considered to be slow and increase the rate of slow calls. The default value is 60000 ms.
    permittedNumberOfCallsInHalfOpenState
    The number of permitted calls when the circuit breaker is half open. The default value is 10.
    slidingWindowType
    The type of the sliding window that is used to record the outcome of calls when the circuit breaker is closed. The sliding window can either be COUNT-BASED or TIME-BASED:
    • If the sliding window is COUNT_BASED, the last slidingWindowSize calls are recorded and aggregated.
    • If the sliding window is TIME_BASED, the calls of the last slidingWindowSize seconds are recorded and aggregated.
    The default setting is COUNT_BASED.
    slidingWindowSize
    The size of the sliding window that is used to record the outcome of calls when the circuit breaker is closed. The default value is 100.
    minimumNumberOfCalls
    The minimum number of calls that are required (per sliding window period) before the circuit breaker can calculate the error rate or slow call rate. For example, if minimumNumberOfCalls is 10, then at least 10 calls must be recorded before the failure rate can be calculated. If only nine calls have been recorded, the circuit breaker will not transition to open even if all nine calls have failed. The default value is 100.
    waitDurationInOpenState
    The time that the circuit breaker should wait before transitioning from open to half-open. The default value is 60000 ms.
    automaticTransitionFromOpenToHalfOpenEnabled
    If this property is set to true, the circuit breaker will automatically transition from open to half-open, and no call is needed to trigger the transition. A thread is created to monitor all the instances of circuit breakers to transition them to HALF_OPEN when waitDurationInOpenState passes. Whereas, if this property is set to false, the transition to HALF_OPEN happens only if a call is made, even after waitDurationInOpenState is passed. The advantage of using this property is that no thread monitors the state of all circuit breakers. The default value is true.

    You can set the following bulkhead pattern properties:

    maxConcurrentCalls
    The maximum number of parallel executions allowed by the bulkhead. The default value is 25.
    maxWaitDuration
    The maximum amount of time that a thread should be blocked when it attempts to enter a saturated bulkhead. The default value is 0.
    tmsserver_override.properties
    This file contains the following SQL Tuning Services-related settings:
    appl_id=APPLDB1C
    The application ID that was set in RACF® when the specified credential type is passticket.
    token_absolute_timeout=28800000
    The absolute timeout time for a user, in milliseconds.
    author.udf.user=IBMTMS.CANVIEW
    The UDF name for a SQL Tuning Services user (for example, IBMTMS.CANVIEW).
    author.udf.admin=IBMTMS.CANADMINISTER
    The UDF name for a SQL Tuning Services administrator (for example, IBMTMS.CANADMINISTER).
    ve.cache.timeout=1200
    The amount of time, in seconds, that the cache for the Visual Explainer function is valid.
    ve.cache.limitation=10
    The number of Visual Explainer result sets that are kept in memory per user.
    Important: Keeping a large number of Visual Explainer result sets in memory can negatively affect system performance.
    cqe.file.retention.period=7
    The length of time, in days, that the result files of the Capture Query Environment function are retained.
    query.joblist.batchsize=50
    The number of rows that are returned per batch by the Job Management function when jobs are queried.
    repodb_override.properties
    This file contains the following repository database-related settings:
    port=8010
    The port number of the Db2® for z/OS® subsystem that's used as the repository database.
    user=TSADM1
    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=false
    To enable SSL connections to the repository database, set this parameter to true.
    credential_type=
    If you are using PassTickets as the authentication method, specify passticket.
    locationName=DB1
    The location name of the Db2 for z/OS subsystem that's used as the repository database. This value is case-sensitive.
    password=wtiv2_252b0ce7696f03b959dfa17264ac267b
    The password of the db2_authid_R ID that's used to access the Db2 for z/OS subsystem that's used as the repository database. Typically, this password needs to be changed only when it expires.

    For instructions for changing this password, see Changing the repository database user ID and password.

    host=9.30.137.24
    The IP address of the Db2 for z/OS subsystem that's used as the repository database.
    sslTrustStoreLocation=
    The location of the SSL truststore file.
    sslTrustStorePassword=
    The password of the SSL truststore file. Typically, this password needs to be changed only when it expires. This value is case-sensitive.
    You can enter this password in regular text or you can run the following command to encrypt your new password and then paste the result in this field:
    'java -classpath path_to_the_jar/com.ibm.aps.tools.util.crypt.jar 
-Ddshome=TMS_HOME/bin com.ibm.aps.tools.util.crypt.CryptUtils new_password
    sslKeyStoreLocation=
    The location on your z/OS system where the SSL keystore (keystore.jks) file is stored.
    sslKeyStorePassword=
    The password where you store the SSL keystore (keystore.jks) file on your z/OS system. Typically, this password needs to be changed only when it expires. This value is case-sensitive.
    You can enter this password in regular text or you can run the following command to encrypt your new password and then paste the result in this field:
    'java -classpath path_to_the_jar/com.ibm.aps.tools.util.crypt.jar 
-Ddshome=TMS_HOME/bin com.ibm.aps.tools.util.crypt.CryptUtils new_password
    sslCertLocation=
    The location of the AT-TLS certificate file that's used to establish https service through the WebSphere® Liberty server.
  2. When you're finished changing configuration parameters, use the tms_setup_userid ID to restart SQL Tuning Services by running the /tms-stop.sh script followed by the tms-start.sh script.
    These scripts are located in the install_dir_zos directory.