Use Data Server Manager to tune your DB2 on z/OS Queries for Free

­­

IBM® Data Server Manager is an integrated database management tools platform for DB2® for z/OS databases. It was designed to be simple to set up, easy to use, and enterprise ready with the ability to manage hundreds of databases.

Data Server Manager Base Edition offers database development, administration and basic performance capabilities to all DB2 for Linux, UNIX and Windows and DB2® for z/OS® clients at no charge.  

For DB2® for z/OS®, Data Server Manager Base also offers advanced capabilities with centralized configuration management available in the DB2 Administration Solution Pack for z/OS. DB2 Query Workload Tuner for z/OS takes advantage of Data Server Manager Architecture either as an individual product or component of the DB2 Performance Solution Pack for z/OS.

 

Query optimization with Data Server Manager

Data Server Manager 2.1 is now available at no additional cost to help you optimize queries to do the following tasks:

  • Capture queries from the dynamic statement cache, catalog, file, input text and user defined repository. 
  • View formatted queries.
  • View access plan graphs.
  • Capture information about the data server that queries run against, a feature that corresponds to Query Environment Capture in Data Studio.
  • Run a query statistics advisor to analyze the statistics that are available for the data that a query accesses, check for inaccurate, outdated, or conflicting statistics, and look for additional statistics that you might capture to improve how the data server processes the query.

To help you get started with query optimization, you will first need to go through the checklist for setting up no charge Query Tuning.

  1. Download DSM
  2. Install DSM server
  3. Create DB2® for z/OS® repository database
  4. Add your DB2® for z/OS® connections
  5. Configure for Tuning

Download Data Server Manager

You can download DSM using Passport Advantage from the following link:  http://www.ibm.com/developerworks/downloads/im/dsm/index.html

Installing DSM

Data Server Manager runs in a browser, but you will need to set up the DSM server. It can run on your laptop, on the data server being monitored or on a separate machine.  View the installation document for full details on how to  

 

Creating the Repository Database

The repository is used to store historical data, advanced performance analytics, alerts based on deviations from historical states, and robust tuning and optimization capabilities.

2016-09-saghi-figure1.png

 

To create a DB2® for z/OS® repository database, you can use the JCL template or the create.sql script provided in the Data Server Manager Installation image.

Note: Ensure there is sufficient space on the dataset where you create the repository database. Use the three-character prefix, BBF, for table space names (for example, BBFxxxxx, with a maximum of eight characters). BBF is the three-character prefix assigned for Data Server Manager.

Use the JCL template, BBFDDL01, in the ibm-datasrvrmgr/samples/DB2Z directory.

 

Add your DB2® for z/OS® connections

There is a multi-step process for adding your database connections to Data Server Manager.  You can follow the documentation in the Knowledge Center to perform this procedure.

 

Configure for Tuning

Configuring a DB2® for z/OS® subsystem for query tuning requires that you bind packages that the Statistics Advisor and other features require, and grant privileges on those packages.   You may also need to create a set of EXPLAIN tables and grant privileges on them if your subsystem does not already have a set.

You can configure DB2® for z/OS® subsystems for use with the no-charge tuning features by submitting a JCL job. You can use an AOCDDL sample JCL job to enable advanced query tuning functions on a DB2® for z/OS® subsystem. The JCL template is located in the ibm-datasrvrmgr/samples/DB2Z/QueryTuning directory.

Modify the corresponding AOCDDL JCL job file to conform to your environment:

V10

V10CM

V11

V11CM

V12

 

Tuning SQL Statements

Once the subsystem is set up to support tuning SQL statements, you can select a SQL statement to tune.

With Data Server Manager, you can format SQL statements so that they are easier to read, generate visual representations of access plans, and get recommendations for collecting statistics.

Access plan graphs show where statements are using the most time and where tables are being scanned.

Let's walk through some of the optimization capabilities of Data Server Manager and the steps to tune a query.   

Selecting a SQL statement

Data Server Manager makes it easy to get queries for tuning.  You can now select Optimize > Start Tuning to create a new tuning job, and Optimize> Tuning Jobs to view existing tuning jobs.

2016-09-saghi-figure2.png 

 

You can then easily grab queries from convenient sources. You can choose from DB2 sources including:

  • Catalog plan or package
  • Statement cache
  • File
  • Input text
  • User defined Repository

 

2016-09-saghi-figure3.png

 

You can also define a filter for the source to limit the number of statements to choose from, and control the order of the statements. For example, when the source type is a package, you might filter by schema, by package name, by the authorization ID of the binder or owner of the package, or by other criteria. You can sort by any of the available columns in ascending and descending order.

When the source type is the Statement Cache, you can take slices (snaps) of dynamic SQL according to filters:

  • AUTHID or CURSQLID (that did the initial PREPARE).
  • BIND options for the statement.
  • Any of the STAT columns, for example, statements that ran longer than one second

 

2016-09-saghi-figure4.png

 

2016-09-saghi-figure5.png

 

Format the problem query

In many cases, when you capture a query for tuning, the text of the query is not formatted well and is hard to read.  Data Server Manager can format the problem query, which provides you a good starting point for analysis.

As you can imagine, for long complicated SQL, simply formatting the query can save hours of DBA time. Now it is easy to find out which tables are accessed, how many are in the query, what local and join predicates exist for each table, and which columns from each table appear in the select list.

 

2016-09-saghi-figure6.png

 

Analyze the query access plan

Data Server Manager provides a visualization of the processing that your data server uses to run a query. This visualization is called the access plan graph. From the access plan graph, you can see what choices the optimizer has made regarding how the query will be processed and the rationale for those choices. The diagram consists of nodes that represent tables, indexes, operations, and returned data. The nodes are arranged and connected by links that indicate the flow of the process. The graph is read left to right, bottom to top. Each node is annotated by statistics, estimated costs, selectivity information and so forth that are used to determine the access plan flow.

Understanding the access plan is important for understanding and influencing performance, as well as for stabilizing performance.

For more details on understanding access paths, please refer to this developerWorks article written by Gene Fuh, Kendrick Ren and Kathryn Zeidenstein. 

 

Execute statistics advisor to improve statistics quality and collection

In DB2® for z/OS®, the command RUNSTATS TABLE ALL INDEX ALL collects all uniform statistics, many of which may not be necessary for improving query performance. At the same time, the utility does not collect certain key statistics, such as multi-column and distributed statistics. There are often data correlations among columns collecting statistics of individual columns may not be enough to provide the information required, so you need to collect column group statistics.

Data Server Manager makes it easier by providing RUNSTATS recommendation to collect the required statistics by identifying the following problematic statistics status.

  • Missing statistics: When statistics are missing, the optimizer assumes the default values to determine costs, which could be completely inaccurate.
  • Conflicting statistics: Inconsistent statistics may cause the optimizer to derive the wrong estimated costs and to make the wrong decision on access plan assessment. Inconsistent statistics may be caused when users collect tablespace and index statistics individually, but at different points in time. For example, you run a REORG of an index today and collect statistics, but the tablespace had statistics collected one month ago.
  • Obsolete statistics: Old statistics may no longer represent the current state of the table.

To see the problems that the advisor found with the statistics, open the RUNSTATS Script View Detail report and Conflict detail to learn more about any conflicting statistics that the advisor found.

2016-09-saghi-figure7.png

 

Improving statistics quality means that you are giving the DB2 optimizer accurate data on which to base decisions about optimizing the access plan and thus potentially improving performance and reducing CPU. However, there is another benefit: you can avoid collecting irrelevant statistics that drive up CPU overhead and create unnecessary workload inside limited maintenance windows. In other words, the statistics advisor can help improve statistics quality as well as improve the efficiency of statistics data collection.

 

Capturing the environment of an SQL statement 

When you are working with IBM Support to resolve a problem with tuning an SQL statement, or when you want to recreate the environment of an SQL statement on a different DB2® for z/OS® subsystem, you can capture the statement's environment in a set of files. You can then upload the files to your machine and FTP them to IBM Support or to the other subsystem.

This feature is available only when you are connected to a DB2® for z/OS® subsystem. It is not available for DB2 for Linux, UNIX, and Windows databases.

 

2016-09-saghi-figure8.png

 

Conclusion

Data Server Manager provides a powerful and free feature to assist its DB2® for z/OS® customers tune their queries, so you may want to spend a little time to explore the tools that are already available to you.

IBM is always looking for customer input to help drive its product development priorities. So if you download DSM, please go to the IBM Information Management RFE Community, where you can suggest and vote on features to be included in future versions of DSM.

 

Special thank you to Jason Sitzo, Jun Lui, and Marichu Scanlon for their assistance in writing this article.

 

Additional helpful links

 

Recent Stories
Selecting Appropriate Data Architecture Technologies

SQL Tuning for Developers – Part 2 Explain Output

Some of my favorite tunings tips and tricks for DB2 by Joe Geller