Data Server Manager Overview – Query Workload Tuner for Db2 z/OS

Data Server Manager Overview - Query Workload Tuner for Db2 z/OS Jason Sizto, IBM, [email protected] Date: January 2019 IBM z Analytics

IBM z Analytics Agenda Summary of DSM Features and integrations Db2 Query Workload Tuner – Features Highlight Selectivity Override Demo 2

Summary of Features and Integrations IBM z Analytics

IBM z Analytics DSM Features Non-charged functions – Administer Accelerator Management – – – – – Run SQL or formerly SQL Editor Basic Alert functions Job Manager Free Tuning functions Autonomics for Db2 for z/OS (Tool purchase required*) Charged functions – Query Workload functions – Configuration Manager

IBM z Analytics DSM Integration Db2 Query Monitor D2b Query workload Tuner integration – Single query, workload and Selectivity Override tuning Db2 Automation Tool for z/OS, Autonomics Director for Db2 for z/OS and Db2 Utilities Enhancement Tool for z/OS Integration – Creation of Automation Tool Object/ Exception/ Utility/ Job profiles OMEGAMON XE for Db2 PE integration – Display KPI in Management Console subsystem dashboard – Capture SQL in OPM repository via user-defined repository in QWT

IBM z Analytics Typical DSM Deployment on Windows / Linux / AIX Data Tools Runtim e Client DSM Web UI z/OS LPAR z/OS Managed subsystems JCC Driver Windows / Linux / AIX DSM server running on Windows / Linux (JCL: AOCDDL01) z/OS Repository Database DB2 LUW Repository can either on LUW or on z/OS Optional Repository Database Manage multiple DB2s DB2 z/OS DB2 z/OS Subsystem DB2 z/OS Subsystem DB2 z/OS Subsystem DB2 z/OS Subsystem DB2 z/OS Subsystem DB2 z/OS Subsystem DB2 z/OS Subsystem Subsystem Optional Repository Database (JCL: BBFDDL01)

Db2 Query Workload Tuner - Features Highlight IBM z Analytics

IBM z Analytics DB2 Query Workload Tuner Capture Expert Advice on query and workload performance for Db2 for z/OS Capture Tune Capture query and workload from different sources for problematic queries for preemptive or reactive tuning Tune Get expert performance recommendation on query, access plan, index, IDAA Deploy What-if What-if analysis to get better confidence on recommendations Deploy recommendations

DSM Tuning function Developer functions – – – – – View Access Plan Graph Format SQL and annotation Access path explorer Access Plan Advisor Statistics Advisor DBA functions – – – – – Statistics Advisor Index Advisor and Index What-if analysis and Index Impact Analysis IDAA Advisor and IDAA What-if analysis Selectivity Override Access Plan Comparison

Tune SQL or Workload Review Access Plan Graph and annotations Advisors streamline tuning process – – – – Single Query Statistics Advisor Workload Statistics Advisor Index Advisor IDAA Advisor Use what-if analysis to determine what impact indexes have on a workload Use what-if analysis to determine what statements are eligible for acceleration

Develop, Run SQL scripts, EXPLAIN and Tune Manage scripts Explain SQL Tune SQL Customize and filter result Save execution results

Gather Queries and Workloads Input Statement Text Local File Dynamic Statement Cache Catalog Plan or Package User Defined Repository

Analyze Access Plans Visualize access plan – See flow of query processing – See index and scan operations – See recommendatio n on RUNSTATS – See recommendatio n on Index

Query Tuning – Plan Comparison 3 ways to compare – Select 2 single query tuning jobs – Select one workload job with at least 2 explain snapshots – Select 2 workload tuning jobs Comparison results displayed in a new browser window

Execute Advisors Statistics – Get recommendations on the best statistics to capture to influence access path selection Index – Get recommendations on indexes changes that can reduce database scans Analytics Accelerator – Get recommendations on optimizing and managing accelerated analytic queries and applications

Improve statistics quality and collection Generate RUNSTATS control statements Results Accurate estimated costs Better query performance Less CPU consumption Improved maintenance window throughput “80 % of access path PMRs could be resolved by statistics advisor before calling IBM support.” – IBM Support

Index Advice to improve query efficiency Improve query efficiency – – – – Index foreign keys in queries that do not have indexes defined Identify index filtering and screening Support for index only access Index to avoid sorts Simplify use – – – Consolidate indexes and provide singe recommendation What-if analysis DDL and run immediately

Test Candidate Index Test Before Deployment User can run what if analysis by: – – – Adding index Removing recommend ed index Virtually drop existing index User can add/edit/remove/virtually drop existing index for what-if analysis

Workload Index Impact Analysis Indexing advice to improve database design Indexes are decided at design stage - Lot of effort is spent making SQL to use the provided indexes - But what if the SQL is "right" and it's the indexes that are "wrong“ - Cost resources to maintain Choose Choose analysis analysis scope –– statement scope statement cache, cache, packages packages or or existing existing jobs jobs - How do you simply test your hypotheses without impacting production? Removing obsolete indexes simplify use - Consolidate indexes and provide a single recommendation - Enables what-if analysis - Provides DDL to create indexes - Run immediately or save Test before deployment - Use virtual index capabilities built into the DB2 engine . % performance gain on statements

IDAA Analysis Workload Analytics Accelerator Advisor – – – – Identify candidate queries and tables to be routed to the accelerator Identify candidate tables to be routed to the accelerator Implement advisor-based tuning recommendations for mixed workloads of accelerated and un-accelerated queries Enable “what if” analysis Benefits – – – – Shorten the process of selecting tables to be accelerated Visualize access paths of accelerated queries Increase productivity by working with accelerated queries through a unified interface Increase overall system capacity

Host variable Collection & Selectivity Override IBM Solution Exclusive! Why did the DB2 Optimizer choose that path? Helps users improve query access plans for dynamic queries with parameter markers or static queries with host variables The selectivity override feature utilizes parameter marker or host variable value information Users can deploy a selectivity profile generated by this function to create better access plans. SELECT * FROM EMPLOYEES WHERE SALARY BETWEEN ? AND ?

IBM z Analytics What’s new in DB2 Query Workload Tuner v5.2.3 Enhancement with QM integration – – – – Allow user to enter SQLID as EXPLAIN table qualifier Support tuning of unqualified static SQL Support Operation credential for Tuning services Selectivity Override analysis support for static SQL The enhanced Selectivity Override feature allows users to improve query access plans for static queries with host variables, on Db2 for z/OS Version 11NFM or above. With this function, QWTs will provide selectivity override hint to optimizer to make a better access plan with runtime host variable values information. This can greatly improve static SQL with skewed host variable predicates. Developer functions Support for Query Advisor and Access Plan Advisor – The Query Advisor and Workload Query Advisor uses a set of rules and best-practices to find structures in a query that are likely to cause the optimizer to choose a suboptimal access path. You can rewrite the query to resolve the problems that are identified in the recommendations. – The Access Path Advisor examines the access plan that is chosen by the optimizer and identifies certain 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.

Db2 Query Workload Tuner - Selectivity Override IBM z Analytics

1 Create a Baseline Run a test application now to get a baseline. Average execution time for this application is: 125ms Note this query is well tune before selectivity override analysis Remember this number

2 Identify candidate query and Collect Activities Identify queries with parameter marker, high elapsed, CPU time etc. Collect values for the host variables / parameter markers Collect HostVars here

3 Scheduling and Sampling before collection Collect parameter marker values – Select the Scope – Collection Period – Sample Size QM will sample the selected query and collect parameter marker or host variable value info base on collection period and sampling size.

4 Tune Query with Selectivity Override in QWT In Query Monitor - Select the workload from Manage Workloads for the Staging table – Click Tune Launch Selectivity Override analysis in QWT

5 View Selectivity Override analysis result in QWT In DSM tuning job page, select the job, click View Results In View Workload Statements, query is Selectivity Override Candidate, select “Host Variables”

6 Launch Selectivity Override Access Plan analysis Review analysis In this dialog, you can see: – – parameter markers distribution Weight of each parameter marker value set Select the sets (all) for Selectivity Override analysis Click Selectivity Override

7 View results and deploy the selectivity profile A selectivity override analysis job is created - Click View Results when it is completed Run recommended scripts Flush the statement cache .

8 Compare against baseline Run the test application again run after Selectivity Override analysis Average execution time for this application is: 92ms Improvement of 26% 26% faster!!

Db2 Query Workload Tuner - Demo IBM z Analytics

Use case 1: Basic Ad-hoc single query tuning non-charged function Basic Ad-hoc tuning scenario on day-in day-out SQL performance issue Assume you have identified a outliner query – demo different ways to capture queries Or you get a ticket from customer concerning a slow running dynamic query – Use QWT to capture the query from Dynamic Statement Cache Filter by CPU, GETPAGES, etc. Hands-on steps to turn on IFCID tracing for collecting DSC metrics – Perform non-charged tuning functions Statistics Advisor – 80% of Access Path related problems is resolved by getting right statistics Formatted SQL Access Plan Graph – Show you hidden high values functions in APG Single Query Summary Report – Good way to share findings with peers

Use case 2: Best Practice for Workload tuning function Best practice on how to capture a workload. For example: Assume there is a new static package application and you are tasked to tune the application – Use Query Workload Tuner to tune a static package as a workload Best practice to tune related objects as a workload – Capture query from Package and Plan source – Perform workload tuning: Statistics Advisor Access Plan Advisor Query Advisor Index Advisor IDAA Advisor Workload Summary Report – Advanced Test candidate Index analysis function Perform what-if analysis on the recommendation from Index Advisor Plus what-if analysis on user-defined hypothetical index – Advanced Index Impact analysis in Use case #3

Use case 3: Query Tuning and Workload Tuning with SQL Performance Monitor Taking advantage of Performance Monitoring tool to identify resource intensive statements. Single and Workload tuning scenario Single query tuning – capture the same query in User case #1 for tuning – Show how easy you can drill down and tune with QM UI – Show priced single query tuning function Workload tuning – Use Query Monitor to drill down and capture queries from a Database object to form a workload – Baseline workload Contains queries from dynamic and static applications, and Ad hoc statements – Advanced tuning function perform Index Impact Analysis base on Index What-if analysis result (in use case #2)

Use case 4: Advanced tuning with Selectivity Override function Tune Static SQL with host variables for extra improvements Look at a static query that is already running very well (sub-second performance) – This query already tuned and has index created on all predicates – It is running in the milliseconds How to tune with Selectivity Override and still get 15% improvement – – – – – Capture query and host variable values from Query Monitor Use Query Workload Tuner to get Selectivity Override profile Review Access Plan change base on QWT recommendation Deploy the recommendation with QWT See query improvement of 15%

Backup Slides

Db2 Query Workload Tuner – Installation IBM z Analytics

IBM z Analytics Ordering QWTz v5.2.3 from ShopZ PID: 5655-AB4, FMID: H2AQ510 Delivery Media Preferred media Internet CD/DVD Images and Other Material Download to your workstation using HTTPS Note: QWTz CANNOT be installed with SMP/E How to install QWTz: http://www-01.ibm.com/support/docview.wss?uid swg27049447 Title Order number IBM DB2 Query Workload Tuner for z/OS, V5.1, and V5.2 (5655-AB4) License Information GC27-6775 Program Directory GI13-4611 DB2 Query Workload Tuner for AIX DVD LCD4-8591 DB2 Query Workload Tuner for Linux DVD LCD4-8592 DB2 Query Workload Tuner for Windows DVD DB2 Query Workload Tuner Lic. Act. Kit QWTz LAK LCD4-8593 LCD4-8584

IBM z Analytics Ordering QWTz v5.2.3 from ShopZ cont. Step 7 when ordering from ShopZ in important! Choose “Preferred media” as “Internet”

IBM z Analytics Downloading ShopZ order After your order is placed, you will received an email with download info Click the link to access to packages prepared for download

IBM z Analytics Downloading ShopZ order In the Shopz Download page Choose Download to your workstation using HTTPS

IBM z Analytics Installing QWTz v5.2.3 Install Steps 1. 2. 3. 4. 5. Install DSM base Stop DSM Install QWTz LAK Start DSM Login to DSM and Activate QWTz license on target subsystems For details on how to install QWTz: http://www-01.ibm.com/support/docview.wss?uid swg2704 9447

IBM z Analytics What is DB2 Query Workload Tuner (QWTz)? A web-based tool that provides expert recommendations to help you improve the performance of queries and workloads for Db2 for z/OS. It can help you to reduce the need for specialized skills and lower total cost of ownership. Data Server Manager – based – Basically Data Server Manager, Base Edition QWT for z/OS License Runs on a distributed platform such as Windows, AIX, Linux or Linux on Z. Requires a database repository which can be in DB2 for z/OS or DB2 for LUW – OQWTz product assembly includes a restricted-use DB2 for LUW download – Can share the repository with Data Server Manager for LUW Enterprise Edition

IBM z Analytics Features of the Data Server Manager z/OS Based Tools - At A Glance Data Server Manager Base Db2 Performance Solution Pack v1.5 / Db2 SQL Performance Pack v1.1 Connect to Db2 for z/OS V10/ V11/V12 Database object navigation, viewing object detail, and linking to related objects. Database object dependency display. Data browsing and editing. Basic database object operations, such as creation of tables, indexes, constraints, and tablespaces; dropping of tables, indexes and constraints; altering tables. Show system privilege from the perspective of Group/User, Role, or SQL object. Choose: -"Group/User" to see the role and the relative object privilege for a user account; - "Role" to see the role a user account belongs to and its relative object privilege; - "SQL object" to see a specific object and users or roles that have the relative authority. Single query tuning Statistics Advisor Query Environment Capture Access Path Graph IDAA Support IBM Db2 Query Workload Tuner Launch of visual explain and tune query on the SQL editor Tuning wizard to capture SQL statements from multiple sources Tuning advisors provide recommendations for: Statistics Advisor Index Advisor IDAA Advisor Problem analysis of query or workload Access plan graph Query formatting and annotation Tuning Report Test Candidate Index Access Plan Comparison Index Impact Analysis Query and Workload Environment Capture Selectivity Override IBM Db2 Query Monitor Launching of DSM from Query Monitor Web UI for end to end performance analysis Host variable collection NO CHARGE OMEGAMON XE for Db2 PE Key Performance Indicators (KPIs) displayed in Data Server Manager on the Subsystems Dashboard Db2 Admin Solution Pack v3.1 Db2 Configuration Manager for z/)S v4.1.3 Track configuration changes Configure zParm Compare and clone configurations Manage application profile Manage alias Manage and control clients Db2 Utility Solution Pack V4.2 Customizable profiles for performing conditional object evaluations and generating actions mapped to resolving utilities (reorg, copy, runstats, etc) Ability to control prioritization of objects, evaluation conditions and generated resolving actions. Ability to define maintenance windows for enabling autonomics, allowing Db2 to self manage utility runs Graphical trend analysis of historical RTS Capture of utility history, recording utility output, time, duration, etc.

IBM Analytics IBMz Data Server Manager (Web) Architecture Common Web Browser UI Supported platforms: - Windows - Linux - Linux on z - AIX IBM Data Server Manager (Server) Integrated Workflow and Smart Analytics Engine Services Layer Admin Run SQL Job Manager Integrated DB2 Repository LUW / zOS Optional: Required only for historical trend analysis, change tracking, tuning and query/storage optimization Alert Tuning DB2 Stabase DB2 Satabase DB2 z/OS Subsystem Configuration . DB2z Tools DB2 for LUW atabase DB2 for LUW se DB2 for LUW Monitored 100s of subsystems or Databases

Download and activate Priced function in Data Server Manager QWT v5.2 standalone – Download QWT package from shopZ, using PID: 5655-AB4 – Download the QWT activation kit as well – Steps to activate: http://www-01.ibm.com/support/docview.wss?uid swg27049136 QWT in Performance Solution Pack v1.5 – Download QWT package and activation kit from shopZ using PID: 5655-E74 – Same activation steps CMz 4.1.2 in Admin Solution Pack v3.1 – Download z/OS CMz package from Admin Solution Pack, using PID: 5697-DAQ – Download the CMz activation kit as well – Steps to activate: http://www-01.ibm.com/support/docview.wss?uid swg27046889

e.g. Capture type: Input Statement Text 1 2

Tuning – Tuning Job page. Tuning Jobs page View results You can specify unique job or workload name Workload tuning is successful

Capture from Dynamic Statement Cache Filter on Statement attributes Filter on RUNTIME metrics if IFCID trace is turned on Enable IFCID 316, 317, 318 trace Sorting options, e.g. Sort by Getpages desc Filter options, e.g. Average getpages 250000

e.g. Capture from Packages and Plans Filter on package attributes Filter on EXPLAIN metrics - if package is bound with EXPLAIN(YES) Filter options, if package bound with EXPLAIN (YES) Sorting options, e.g. Sort by TOTAL COST desc

Capture from User Defined Repository Map Monitor Repository definition with a view Capture Statements via a view Useful for capturing Static SQL with runtime metrics Specify view definition pointing to SQL metrics Click to learn more on how to create table or view definition

Refine statements for Tuning

Improve Statistics Quality and Collection Improved Statistics for optimizer to make better access plans Reduce CPU consumptio n Improve throughput Generates RUNSTATS control statements

Improve Statistics Quality – Review Detail Report Detailed Report for user to quickly evaluate RUNSTATS recommendations

Index Impact Analysis Run impact analysis on recommended index Run impact analysis on recommended index against an existing workload Run impact analysis on recommended index against the package source / dynamic statement cache source

IBM z Analytics What’s new in DB2 Query Workload Tuner v5.2.3 Enhancement for queries and workload capture – Global setting for setting SQLID and default Schema – Ease of use link to enable IFCID 316, 317, 318 traces for performance metrics in dynamic statement cache – Ease of use – Filtering by table / index object is supported on Packages and Plan source when underlying package is bound with EXPLAIN information – Ease of use – Support Access Plan comparison on different VERSION of plan in Packages and Plan source. – Ease of use – The tuning wizard is now capable of generating JCL scripts or commands in SQL Editor for collecting EXPLAIN plans for Packages and Plan that did not bound with EXPLAIN YES. Enhancement for Index Advisor – Display existing index information – Support Access Plan comparison and Review Revised Access Plan on What-if analysis – Index Impact analysis support on What-if recommendation. What-if analysis allows you to test custom hypothesis on index recommendation on single query or at workload level. By integrating Index Impact analysis support on What-if recommendations, you can test the impact of the What-if analysis at the Packages/Plan level, Dynamic Statement Cache level, or against a custom defined workload. You can have extra confidence before deployment.