Data Server Manager Overview Query Workload Tuner for

Data Server Manager Overview - Query Workload Tuner for Db 2 z/OS Jason Sizto, IBM, jsizto@us. ibm. com Date: January 2019 IBM z Analytics

IBM z Analytics Agenda • • Summary of DSM Features and integrations Db 2 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 Db 2 for z/OS (Tool purchase required*) • Charged functions – Query Workload functions – Configuration Manager

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

Typical DSM Deployment on Windows / Linux / AIX Data Tools Runtim e Client DSM Web UI z/OS LPAR JCC Driver z/OS Managed subsystems Windows / Linux / AIX DB 2 z/OS Subsystem DB 2 z/OS … Subsystem DB 2 z/OS Subsystem DSM server DB 2 z/OS Subsystem DB 2 z/OS running on Subsystem Manage multiple DB 2 s … Subsystem Windows / Linux (JCL: AOCDDL 01) z/OS Repository Database DB 2 LUW Repository can either on LUW or on z/OS Optional Repository Database (JCL: BBFDDL 01)

Db 2 Query Workload Tuner - Features Highlight IBM z Analytics

IBM z Analytics DB 2 Query Workload Tuner Capture Expert Advice on query and workload performance for Db 2 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 recommendati on on RUNSTATS – See recommendati on 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 recommended index – Virtually drop existing index User can add/edit/remove/virtually drop existing index for what-if analysis

Workload Index Impact Analysis § 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 analysis scope – statement cache, packages or existing 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 DB 2 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 DB 2 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 DB 2 Query Workload Tuner v 5. 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 Db 2 for z/OS Version 11 NFM 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.

Db 2 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: 125 ms • 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 Host. Vars 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: 92 ms • Improvement of 26% • On an already welltuned query!! Ø 26% faster!!

Db 2 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 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

Db 2 Query Workload Tuner – Installation IBM z Analytics

IBM z Analytics Ordering QWTz v 5. 2. 3 from Shop. Z • • • PID: 5655 -AB 4, FMID: H 2 AQ 510 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=swg 27049447 Title Order number IBM DB 2 Query Workload Tuner for z/OS, V 5. 1, and V 5. 2 (5655 -AB 4) License Information GC 27 -6775 Program Directory GI 13 -4611 DB 2 Query Workload Tuner for AIX DVD LCD 4 -8591 DB 2 Query Workload Tuner for Linux DVD LCD 4 -8592 DB 2 Query Workload Tuner for Windows DVD DB 2 Query Workload Tuner Lic. Act. Kit QWTz LAK LCD 4 -8593 LCD 4 -8584

IBM z Analytics Ordering QWTz v 5. 2. 3 from Shop. Z cont. . • • Step 7 when ordering from Shop. Z in important! Choose “Preferred media” as “Internet”

IBM z Analytics Downloading Shop. Z 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 Shop. Z order • In the Shopz > Download page • Choose Download to your workstation using HTTPS

IBM z Analytics Installing QWTz v 5. 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=swg 27049447

IBM z Analytics What is DB 2 Query Workload Tuner (QWTz)? • A web-based tool that provides expert recommendations to help you improve the performance of queries and workloads for Db 2 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 DB 2 for z/OS or DB 2 for LUW – OQWTz product assembly includes a restricted-use DB 2 for LUW download – Can share the repository with Data Server Manager for LUW Enterprise

IBM z Analytics Features of the Data Server Manager z/OS Based Tools - At A Glance Data Server Manager Base • Connect to Db 2 for z/OS V 10/ V 11/V 12 • 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 NO CHARGE Db 2 Performance Solution Pack v 1. 5 / Db 2 SQL Performance Pack v 1. 1 IBM Db 2 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 Db 2 Query Monitor • Launching of DSM from Query Monitor Web UI for end to end performance analysis • Host variable collection OMEGAMON XE for Db 2 PE • Key Performance Indicators (KPIs) displayed in Data Server Manager on the Subsystems Dashboard Db 2 Admin Solution Pack v 3. 1 Db 2 Configuration Manager for z/)S v 4. 1. 3 • Track configuration changes • Configure z. Parm • Compare and clone configurations • Manage application profile • Manage alias • Manage and control clients Db 2 Utility Solution Pack V 4. 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 Db 2 to self manage utility runs Graphical trend analysis of historical RTS Capture of utility history, recording utility output, time, duration, etc.

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

Download and activate Priced function in Data Server Manager • QWT v 5. 2 standalone – Download QWT package from shop. Z, using PID: 5655 -AB 4 – Download the QWT activation kit as well – Steps to activate: http: //www-01. ibm. com/support/docview. wss? uid=swg 27049136 • QWT in Performance Solution Pack v 1. 5 – Download QWT package and activation kit from shop. Z using PID: 5655 -E 74 – Same activation steps • CMz 4. 1. 2 in Admin Solution Pack v 3. 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=swg 27046889

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 consumption 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 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 DB 2 Query Workload Tuner v 5. 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.