What's new in SQL Tuning Services

This topic describes new and changed SQL Tuning Services functions and identifies the APARs that deliver these functions.

APAR Date Description
PH67607 December 2025
  • All workload tuning APIs can now be run without specifying a connection profile.
  • A new num_of_tokens parameter has been added to the tmsserver_override.properties configuration file, which enables the same user to connect to a tuning server from multiple clients without their tokens being invalidated.
  • Read-only files can be separated from read-write files through the use of the WebSphere® Liberty server wlp_output_dir parameter. This enhancement is useful for separating log files from configuration files.
  • The Prune API no longer returns output if no job or jobs match the criteria in the mask password criteria.
  • When using Db2® Administration Foundation to connect to SQL Tuning Services through a truststore that has been defined in Unified Management Server, you can override the UMS truststore parameters by setting the com.ibm.izp.security.jdbc.passTruststoreInfo parameter in the UMS ZWEYAML PARMLIB member to false.
  • Changes have been made to how SQL Tuning Services processes threads to eliminate a situation that could cause index-related tuning actions to fail when multiple index-related tuning actions were run concurrently.
  • Fixed a problem which a Query Rewrite Advisor job would become unresponsive when running at the same time with other advisors.
  • Fixed a problem in which parameters of the SQL Capture API were not being validated.
  • Fixed a problem in which Index Advisor jobs could fail when running concurrently with SQL Annotator for queries that contain special characters.
  • Removed duplicate index-on-expression recommendations that could be issued by Index Advisor.
  • Fixed a problem in which Explain records were not being retrieved successfully from a data sharing group.
PH66651 June 2025
  • The following APIs have been added to support tuning workloads without the use of a tuning profile and the ability to specify a single-use MFA token:
  • Updates to the installation script enable you to configure a default truststore for all Db2 subsystems connected to the SQL Tuning Services server. See Configuring a truststore for more information.
  • The SQL Capture from data set API (tuningservice/v1/sqlcapture/dataset) now supports PassTickets.
  • The following miscellaneous installation enhancements have been added:
    • If a certificate or key ring for the Liberty server is not specified during installation, a default self-signed certificate is used.
    • If the path for the wlp_user_dir folder contains white space, the white space is removed.
  • This APAR provides the following fixes:
    • In SQL Tuning Services integrations with Unified Managemetn Server in which the tuningApplicationId is configured in the IZPDB2PM PARMLIB, error message TMSSEC0005E: Unauthorized Access! Check role failed is no longer issued.
    • Index Advisor no longer recommends ALTER operations on column expressions. It now recommends only DROP or CREATE operations.
    • A change was made to the application.properties configuration file to support rolling over of log files. See Configuring logging for more information.
PH65492 April 2025
  • Support for storing private keys in ICSF.
  • Support for the use of one-time tokens for Index Impact Analyzer and Virtual Index Analyzer jobs.
  • Support for running SQL Capture jobs without specifying a tuning profile.
  • Compatibility with Java 21.
  • The status of a Capture Query Environment job now changes to Obsolete when the associated zip file is deleted.
PH63509 November 2024 Various enhancements to support IBM® Db2 Query Workload Tuner for z/OS® features and improvements to the SQL Tuning Services installation and configuration process.
PH60806 June 2024
  • A new single_query_tuning_actions API that provides the following capabilities:
    • Support for invoking a subset of the SQL Tuning Services APIs without creating or specifying a tuning profile
    • Support for invoking multiple tuning actions simultaneously on a single query
    • The use of multi-factor authentication (MFA) to tune SQL
  • Support for Java 17.
  • The addition of a 'host' parameter to the sqlcapture/dataset API to support capturing SQL from a data set in environments where the z/OSMF host is not the same as the Db2 host.
  • An enhancement that enables Index Advisor to tune a query that has a 'fetch first ? rows only' clause.
  • An improved error message that is issued when a tuning profile is created.
  • A fix for an issue in which Index Advisor and Workload Index Advisor incorrectly return report card=-1.
PH59959 March 2024
  • Support for using the following security mechanisms for tuning connection profiles:
    • 7 - User ID and encrypted password
    • 9 - Encrypted user ID and encrypted password
    • 18 - Certificate authentication
  • Support for sorting workloads by their creation time in descending order.
  • Support for sorting jobs by their start time in descending order.
  • An enhancement to the installation script in which, if you are using key ring for Liberty SSL, you are no longer prompted for a key ring password.
  • An enhancement to the installation script that adds the sslTrustStoreType and sslTrustStorePassword parameters to the tmsservice.config configuration file.
  • Miscellaneous fixes. See the APAR for details.
PH58058 January 2024
  • A new repoDBCredReset.sh script, which enables an administrator to reset the repository database connection logon credentials.
  • An enhancement to the GET/DELETE jobs API that adds more filters.
  • An enhancement to the GET connection profile API that adds more properties.
  • The removal of the dependency on db2jcc_license_cisuz.jar.
  • Miscellaneous fixes. See the APAR for details.
PH56970 October 2023
  • Support for the following new SQL tuning features:
    • Workload Environment Capture
    • Workload Analytics Acceleration Advisor
    • Workload Candidate Acceleration Analyzer
  • Support for TLS 1.3.
  • To support Db2 13 for z/OS, an enhancement to the SQL capture API that supports the new value of S in the VALID column of the SYSPACKAGE and SYSPACKCOPY catalog tables.
  • An enhancement to the SQL Capture and Index Impact Advisor APIs for customizing the versions of bound packages.
  • An enhancement to the Index Impact Analyzer API for adding a job description to specify the source job.
  • Miscellaneous fixes. See the APAR for details.
PH55158 July 2023
  • Support for the following new SQL tuning features:
    • Single query and Workload Virtual Index Analysis API
    • Single query and Workload Impact Analysis API
    • Workload Refine API
    • Workload Explain failure reason API
  • An enhancement to the installation process that enables you to specify the repository database after the server is installed and started.
  • Sample JCL to upgrade the repository database.
  • A new set_repo API
  • An enhancement to the List workloads API to include more information in a summary report.
  • A change to the CreateRepo API to support the creation of only a new repository database.
  • Miscellaneous fixes. See the APAR for details.
PH53536 May 2023
  • The new Capture SQL from z/OS data set API.
  • An enhancement that updates the value of Explain_Status for each statement in a workload.
  • Enhancements to simplify the configuration script.
  • An enhancement that reduces idle CPU consumption.
  • Enhancements to sample JCL to remove dependency on SYSPROC.DSNAHVPM and eliminate SYSADM authority.
  • Miscellaneous fixes. See the APAR for details.