IBM Db2 Query Workload Tuner for z/OS V5.x releases: new features

Content

To download the latest modification of IBM Db2 Query Workload Tuner for z/OS, go to Shopz

For information about how to install the latest modification, go to Installing Db2 Query Workload Tuner for z/OS

 

What's New in Version 5.2.3.1

Major features for this release include:

Tuning

• IBM Db2 Query Workload Tuner for z/OS now supports tuning of statement in reference to the Declared Global Temporary Table for Db2 for z/OS.
• The Statistics advisor now allows Db2 for z/OS users to customize the utility ID for SYSPROC.DSNUTILU stored procedure to execute the RUNSTATS recommendations.
• The enhanced Index Advisor now provides valuable recommendations on Index on Expression (IoE).
• The enhancement to capture Static statements from Package and plan source now captures the EXPLAIN table metrics with statement by default.
• The EXPLAIN options used in Single Query Tune now includes the Default Schema of the captured statement as the CURRENT PATH.

Security

• IBM Db2 Query Workload Tuner for z/OS now supports authentication and authorization with Tivoli Directory Server on z/OS with RACF back-end data. For more details, see Managing user authentication and user authorization with LDAP.
• IBM Db2 Query Workload Tuner for z/OS now supports repository authentication with repository UDF authorization to the Db2 repository database. For more details see, Enabling repository authentication with repository UDF authorization.
• The enhanced configurable idle connection timeout value now supports one-time password connection. For additional security, the administrators can now set the timeout value for one-time password connection and enforce users to re-enter the one-time password when the specified timeout value expires. For more details see, Setting the timeout value for onetime password connection.

What's New in Version 5.2.3

Major features for this release include:

• Tuning

New tuning features introduced in IBM Db2 Query Workload Tuner for z/OS (QWTz) V5.2.3 include new advisors and enhanced analysis tools, for improved tuning of statements and workloads.

Enhanced tuning support now available for IBM Query Monitor for Db2 for z/OS (QM)

• Users of QM can now call QWTz tuning functions for tuning both single static and dynamic SQL statements, as well as workloads. In addition, when using the QWTz integration, QM users now have the option of specifying the SQLID for the EXPLAIN table, in the event that it is not the same as the database user name.
• The enhanced Selectivity Override function (designed to helps users improve query access plans for dynamic queries with parameter markers) now supports tuning of static statements on Db2 for z/OS Version 11NFM or above. With this function, DSM sends a selectivity override hint to the optimizer, to help improve the access plan with run-time host variable values information. This can greatly improve static SQL with skewed host variable predicates.
• The QWTz operation credential now supports tuning services.
• The following QM APARs are prerequisite, in order to get all the latest functions: PI89058 and PI89061

Query Advisor and Access Path Advisor now available as tuning options

• Generate query revision recommendations: launches either the Db2 Query Advisor or Db2 Workload Query Advisor to find structures in a query that are likely to cause the optimizer to choose a sub-optimal access path. The advisors use a combination of rules and best practices to calculate their recommendations, which you can use to rewrite the query to resolve problems.

• Generate access path recommendations: launches the Db2 Access Path Advisor to examine the access plan selected by the optimizer and to identify common access path issues. The warnings that this advisor provides can help you to understand where to look for trouble in an access plan graph or in the Access Plan Explorer.

Enhancement for queries and workload capture

• Global option for setting the SQLID and default Schema
• Enable IFCID 316, 317, 318 traces for performance metrics in dynamic statement cache
• Filtering by table or index object is now supported for Packages data sources, when the underlying package is bound with EXPLAIN information
• Access plan comparison is now supported for Packages data sources.
• The tuning wizard is now capable of generating JCL scripts or commands from the SQL Editor, for collecting EXPLAIN plans for Packages that did not bind with EXPLAIN YES.

Improved index advisor

QWTz has improved the usability of its Index Advisor tool, by providing users with more information from which to evaluate index recommendations:

• The advisor now displays existing index information
• The advisor now supports access plan comparison, and the ability to review a revised access plan for Test Candidate Index analysis
• The advisor now supports index impact analysis for Test Candidate Index recommendations. Test Candidate Index analysis allows you to test your own custom hypotheses for index recommendations, on single queries or workloads. By integrating index impact analysis support on Test Candidate Index recommendations, you can test the impact of the Test Candidate Index analysis at the packages/plan level, Dynamic Statement Cache level, or against a custom defined workload.

What's New in Version 5.2.2

Major features for this release include:

Tuning

• New tuning features introduced in IBM Db2 Query Workload Tuner for z/OS(QWTz) V5.2.2 include many usability enhancements to make the tuning of individual and workload queries much simpler. New filters have been enabled to make capturing data for tuning much quicker and easier. An enhanced Compare Access Plan feature allows you to show differences between the original and new access plans after what-if index analysis. There is also an improved Index Advisor tool in this release.

  Improved display of tuning results

  The Tuning Jobs page now includes the following enhancements for better usability:

  • A more common method for column and row filtering
  • Display tuning results by clicking a link within the JOB NAME column
  • Check box controls for selecting entire rows
  • A quick search tool for locating jobs by job name and connection name
  • Close, Close others and Close all by right-click on the tabs
  
  Enhanced data capture for the QWTz Tuning Wizard

  Users can now manage filters they have created for capturing data for tuning, using the QWTz Tuning Wizard. By clicking the Manage Filters link, users can edit, copy, or delete existing filters, or create new ones. This option is available for the following data sources:

  • Db2 dynamic statement cache
  • Db2 packages and plans
  • User-defined repository
  
  Compare access plans quickly and easily when optimizing tuning jobs

  When optimizing an index for tuning, users may try to create multiple indexes virtually to compare the optimized access plan against the original one.

  QWTz now provides users with a Compare Access Plan button that, when selected from the what-if analysis result page, generates a single query access plan comparison report, identifying to the user the differences between their original query access plan and the new access plan created with virtual indexes.

  
  
  Improved index advisor

  QWTz has improved the usability of its Index Advisor tool, by providing users with more information from which to evaluate index recommendations:

  • Users can now check statements directly affected by index recommendations, as well as corresponding statement information, such as the package name and collection name.
  • Users can check the table information related to the index recommendations to quickly identify which tables and index recommendations need the most consideration.
  • Users can now review how existing indexes are currently used, and compare them with new index recommendations.

Security

• Making sure that your database is fully protected is an important part of every DBA's job. With the new release of IBM Db2 Query Workload Tuner for z/OS(QWTz) V5.2.2 we have built up the LDAP security features introduced in the last release and now support Kerberos authentication and allow QWTz to support multi-factor authentication. See Securing IBM Data Server Manager.

  Customize encryption mechanism

  QWTz now provides a customized encryption option, allowing QWTz administrators to better protect their QWTz user's passwords.

  Current QWTz user passwords are encrypted by the same key across all QWTz installations. QWTz can now generate a new key per installation. With the encryption key now being installation-specific, the key from one QWTz installation cannot be used to decrypt information from another QWTz installation. A new utility allows a QWTz administration to convert to this installation specific key for better protection of their encrypted content.

  
  
  Separate connection profile credential for Admin/Tuning/Alerting

  QWTz administrators can now create connection profiles that control a user's access to specific QWTz functions. To facilitate this change, the Add Database Connection window has been augmented to include two new credential options:

  • Enable operation— provides the credential for the current QWTz user to perform operational access to QWTz (perform administration functions, tune databases and run queries against the connected database). The user can choose to save this credential to the repository database, or let it be cleared after the user logs out of QWTz. For convenience, users can also update this credential through the User icon, next to the database selector, without having to go through the Connection dialog box. This operational credential will only apply to the current user and will never be shared with other users sharing the same connection profile.
  • Enable data collection—provides users of the connection profile with data collection privileges for the connected database, allowing them to create alerts on the connected server.
  
  When creating the connection profile, QWTz administrators can choose one or both options by clicking the appropriate check boxes at the bottom of the Database Connection tab page.
  
  
  QWTz Authentication using a repository database to handle Kerberos Authentication

  Users can now delegate QWTz user authentication duties to a repository database. In this mode, any repository database user that has been assigned the QWTz role can log in to QWTz. For example this allows QWTz users to be authenticated through Kerberos.

   

  
  2FA authentication in JDBC connection - Db2 for z/OS database

  QWTz now provides one-time password authentication (one kind of two-factor authentication) when performing administration or tuning tasks on Db2 for z/OS databases. With one-time password authentication enabled, the underlying connection will be shared, eliminating the need to create a new connection during the operation life-cycle.

   

  
  IBM LDAP Tivoli Directory Server support

  QWTz now supports the use of IBM TDS for LDAP authentication.

What's New in Version 5.2.1

Major features for this release include:

Tuning

• Better user experiences when capturing queries and workloads
  
  
  • IBM DB2 Query Workload Tuner for z/OS(QWTz) now provides a tuning wizard to facilitate the workflow of SQL and workload capturing. This new tool clearly defines the steps of capturing:
    1. Select a data source
    2. Define the scope
    3. Choose options
    Users can also jump to each step quickly by clicking an icon at the top of the wizard.

• Capture SQL statements from multiple files

• • Users can now capture SQL statements from multiple files when running the tuning wizard against a DB2 on z/OS or DB2 for LUW database. By choosing Optimize > Start Tuning and selecting the Files tab from the tuning wizard, users can browse to local or network files to for tuning. Options are also available for setting a statement terminator, an encoding format (e.g. UTF8) and a default schema.
  
  
  
  
   
  •  
  •  
• Enhanced comparison of workload access plans for displaying all non-matching statements
  • IBM DB2 Query Workload Tuner for z/OS(QWTz) can now display all non-matching statements in the workload access plan comparison report. With this enhancement, users can easily identify statements that are not comparable between two workload tuning jobs. Users can use this information to determine if changes were applied to SQL statements during application development.

• Capture dynamic statements from a data sharing group

• • When tuning queries sourced from a DB2 on z/OS database, users can now capture dynamic statements from the members of a data sharing group. Using the new tuning wizard, users can enable the Capture from data sharing option and select data sharing members from a list.
    This feature will run the EXPLAIN CACHE ALL command against all the selected members and consolidate all statements in each member's dynamic statement cache as the captured result

• Export to JCL

• • DB2 for z/OS customers can now save tuning recommendations locally as Job Control Language (JCL) files. These files can then be run from a DB2 for z/OS command line console as needed.  An  Export to JCL button now appears on the Tuning Results page for any of the following job types:
    • Indexes Advisor
    • Workload Indexes Advisor 
    • Test candidate index
    • Workload Test candidate index
    • Statistics Advisor 
    • Workload Statistics Advisor
    • Selectivity Override Analysis
•  

Misc enhancements

• Get things done faster

• • IBM DB2 Query Workload Tuner for z/OS(QWTz) version 5.2.1 continues to improve the user experience with its Up and Running design and intuitive graphic user interface (GUI).
  
  
  
  
   
  •  
  •  
• Task oriented welcome page for high efficiency
• • Step-by-step quick tours of common tasks help to get users up and running with the console in a matter of minutes.
  • Functional areas covered by quick tours include:
  • Set up and configuration
  • Monitoring databases
  • Administering and configuring databases
  • Executing and optimizing SQL statements
  • Quick links to take users to key console pages where they can perform common tasks
  • Accessible online help on each page, to provide answers when users need them
• LDAP authentication

• • LDAP Authentication now extends to support Microsoft Active Directory. Administrators can also configure to use multiple LDAP entries. QWTz supports both anonymous and simple authentication configuration on the LDAP server.

• Customized session time out

• • User can customize session time out now. Administrators are allowed to set the idle time that occurs before a session time out requires users to log in again.

• Define workloads

• • Users can now use the Administer > Workloads page to define workloads by different connection attributes, and to review existing workload definitions. They can then jump to the Monitor > Databases > Overview page to analyze the performance of their monitored database, all workloads, or individual workloads.

• Query and script building

• • Users can now re-use a single database connection to run different statements and scripts in multiple transactions, using the SQL Editor.
    Users can manage scripts with simple in-cell clicking. 
  
  
  
  
   
  •  
  •  
• Enhanced IBM DB2 Automation Tool for z/OS console

• • The DB2 Automation Tool for z/OS has been updated with the following features:
    • Improved support for DB2 Version 12 with new or modified fields included in the RECOVER and MODIFY RECOVERY utilities.
    • New profile management support for DB2 subsystems that are members of a data sharing group.
    • Improved layout and simplified user experience for creating and managing profiles, as well as better search and filtering capabilities.
    • Updated Action Registry and Symptom Registry screens (formerly called Action Priorities and Symptom Severities) to reflect changes and enhancements to the various autonomics options.

What's New in Version 5.2

Major features for this release include:

Tuning

• • NOTE: This feature requires integration with DB2 Query Monitor, in a DB2 for z/OS environment with the APAR PI72238 and PI71272 applied.

    The selectivity override feature utilizes host variable and parameter marker information captured from the DB2 Query Monitor for access plan analysis. Users can deploy a selectivity profile generated by this function to create better access plans for these queries. 

    To learn more about the domain knowledge of overriding predicate selectivity settings at the statement level, refer to:http://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/perf/src/tpc/db2z_createselecthint.html. 

    • BLU Advisor - Provides recommendations for converting row organized tables into column organized tables on DB2 Version 11.1 partitioned database environments.
    • Selectivity Override - Helps users improve query access plans for dynamic queries with parameter markers, on DB2 for z/OS Version 11NFM or above. 

Misc enhancements

• • The SQL editor now offers users several enhancements over previous versions:

    
    
    • LDAP Authentication
    
    
    
    
    
     
    •  
    •  
    • User can now configure to use LDAP authentication. Credentials validation and user privilege assignment can be delegated to an Open LDAP or Apache Directory server.

      Administrators can use LDAP for user management in the following ways:

      • Manage user credentials and user privileges: In this mode, both user credentials and user privileges (Administrator or User) are managed from the LDAP side. Any new user creation or privilege assignment must be done from the LDAP side as well.
      • Manage user credentials only: In this mode, only user credentials are managed in LDAP side, with user privilege management still available through IBM DB2 Query Workload Tuner for z/OS.
    
    
    • Enhanced SQL editor
      • Select multiple saved SQL statements, or templates, from your catalog for insertion into an open query, or to replace the contents of the SQL editor window. Check boxes in the Saved Scripts window now allow users to make multiple selections for insert and replace operations.
      • Display multiple result sets from one query in the results window at one time.
      • Save a script to an existing script to update the content.

What's New in Version 5.1.1

Tuning

• • • Support DB2 on z/OS V12 new explain table format.
    • Support to capture stabilized statements from dynamic statement cache.
    • Support to view the runtime access plan of dynamic statements and bindtime access plan of package statements.
    • Support to view stabilized access plan for the stabilized dynamic statements. 
    • Support to download query environment information such as DDL, statistics, etc.
    • Support to capture workload environment and download it. 
    • Streamline the workload tuning and single query tuning scenario.
    •  

Misc enhancements

• • • Redesign license management and display corresponding licenses information on the connections.
    • Enhance and optimize the setup process.
    • Add the global connection selector to optimize the workflow.
•  

What's New in Version 5.1.0.1

• • Automated Tuning services – including Workload Access Path Compare, Workload Index Advisor, Workload Statistics Advisor, Visual Explain, Query Annotation/Format – offer automatic comparisons that you can schedule to simplify change management process.
  • Optimized Access Plan Graph - The toolbar has been redesigned, all the view-switch buttons have been replaced by icons and placed in the right corner. The integrated panel is also redesigned, hidden by default, and displayed on demand. The graphic view toolbar has also been refined with a brand new vertical layout.
  • Tune single SQL statements or workloads with DB2 Query Monitor CAE Web Client - After you examine the monitored SQL statements with the DB2 Query Monitor CAE Web Client and store them in generic staging tables，you can now capture a query workload and transfer it to DB2 Query Workload Tuner for z/OS with no size limitations, and with complete metadata. IBM DB2 Query Workload Tuner for z/OS can retrieve problematic statements programmatically for query workload tuning. You can also use DB2 Query Workload Tuner for z/OS to tune single SQL statements.  
  • Migrate workload from InfoSphere Optim Query Workload Tuner for DB2 for z/OS V4.1.x - User can export a workload in Optim Query Workload Tuner for DB2 for z/OS V4.1.x, and migrate it into DB2 Query Workload Tuner for z/OS as a tuning job.

What's New in Version 5.1

• IBM DB2 Query Workload Tuner for z/OS v5.1 is a new web-based tool that is integrated within IBM Data Server Manager. The tool offers new web user interface designs, and efficient user scenarios to optimize performance and provide better user experience. DB2 Query Workload Tuner for z/OS provides full query tuning support for DB2 for z/OS. DBAs and developers can easily perform single query tuning and workload tuning tasks. The major features include:
  • Launching of visual explain and tune query on the SQL editor helps DBAs and application developers quickly analyze and tune a query during development or troubleshoot a problem.
  • A new tuning wizard in the query tuning dashboard helps you capture SQL statements from multiple sources, tune single queries and conduct workload tuning tasks.
  • Several tuning advisors provide recommendations quickly, for example:
  
  
  • - Statistics advisor analyzes a problem query or workload and generates recommendations to collect necessary statistics, including missing statistics, conflict statistics and obsolete statistics. The RUNSTATs scripts are provided. A detailed report is provided to help you check all statistics problems.
    - Index advisor analyzes a problem query or workload and generates recommendations to optimize indexes, including new indexes, modification of existing indexes, and dropping of unused indexes. Index DDLs and related performance gain are provided.
    - IBM DB2 Analytics Accelerator (IDAA) advisor analyzes a workload and recommends tables to be offloaded to IDAA and tables to retain in DB2. A detailed report is provided to explain the recommendations for each SQL statement. You can also perform what-if analysis to evaluate the performance gain from implementing recommendations.
  • Several analyzing features help you better understand a problem query or workload, and conduct tuning tasks. These features include:
  
  
  • - An access plan graph that provides a graphical view of the access plan for a single query. The graph can help you understand how DB2 executes the SQL statements and deeply analyze potential performance bottlenecks.
    - Query formatting and annotation formats complicated queries to a readable style and associates the statistics as an annotation.
    - Tuning report summarizes information for a single query or a workload, including recommended actions, access plan summary, and table/index information. You can save the report to a file and share with others.
    - Test candidate indexes help you perform what-if analysis on indexes for both single queries and workloads and further evaluate index recommendations. You can also use this feature to test indexes without actually creating them.
    - Access plan comparison helps you to compare access plan changes between two tuning results for a single query or a workload. This feature is very useful to evaluate tuning recommendations. You can also lock down the access plan to quickly resolve an urgent performance issue.
    - Index impact analysis helps you evaluate how the recommended indexes impact the entire system, including the existing packages and dynamic SQL statements. You can also choose a specific workload to evaluate the impact on an application.
  • Several sources for capturing SQL statements from a local file, dynamic statement cache, plan/package, or user-defined repository. These sources are required by internal DB2 users (such as monitoring tools) and external DB2 users (such as file system.)
  • Several filters can help you target workload statements accurately. The filters include: Estimation filters, Runtime filters, Access plan filters and Object filters.
  • Other serviceability enhancements include:
  
  
  • - A configuration report to identify configuration problems in monitored databases or subsystems.
    - The ability to capture query environments such as DDLs, statistics, zParm and query tuning analysis information and send the information to IBM.
  
  Related links
  • IBM Data Server Manager V2.1.x releases: new features and enhancements
    IBM Data Server Manager V1.1.x releases: new features and enhancements
    IBM Db2 Configuration Manager for z/OS V4.1.x releases: new features