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

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

2. Agenda • Summary of DSM Features and integrations • Db2 Query Workload Tuner – Features Highlight • Selectivity Override • Demo

3. Summary of Features and Integrations

4. 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

5. 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

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

7. Db2 Query Workload Tuner - Features Highlight

8. DB2 Query Workload Tuner Capture • Capture query and workload from different sources for problematic queries for preemptive or reactive tuning Expert Advice on query and workload performance for Db2 for z/OS Capture Tune 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

9. 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

10. 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

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

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

13. Analyze Access Plans • Visualize access plan • See flow of query processing • See index and scan operations • See recommendation on RUNSTATS • See recommendation on Index

14. 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

15. 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

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

17. 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

18. Test Candidate Index User can add/edit/remove/virtually drop existing index for what-if analysis • Test Before Deployment • User can run what if analysis by: • Adding index • Removing recommended index • Virtually drop existing index

19. Indexing advice to improve database design 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 • 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 Choose analysis scope – statement cache, packages or existing jobs . % performance gain on statements

20. 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

21. Host variable Collection & Selectivity Override IBMSolution 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 ?

22. 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. 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. Developer functions

23. Db2 Query Workload Tuner - Selectivity Override

24. 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

25. 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

26. 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.

27. 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

28. 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”

29. 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

30. 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 .

31. 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% • On an already well-tuned query!! • 26% faster!!

32. Db2 Query Workload Tuner - Demo

33. 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

34. 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

35. 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)  

36. 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%

37. Backup Slides

38. Db2 Query Workload Tuner – Installation

39. 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 QWTz LAK

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

41. 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

42. Downloading ShopZ order • In the Shopz > Download page • Choose Download to your workstation using HTTPS

43. Installing QWTz v5.2.3 Install Steps 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=swg27049447

44. 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

45. Features of the Data Server Manager z/OS Based Tools - At A Glance • Db2 Performance Solution Pack v1.5 / Db2 SQL Performance Pack v1.1 • 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 • OMEGAMON XE for Db2 PE • Key Performance Indicators (KPIs) displayed in Data Server Manager on the Subsystems Dashboard • Data Server Manager Base • 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 • 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. NO CHARGE

46. IBM 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 DB2z Tools Configuration Alert Tuning Job Manager Run SQL Admin DB2 Stabase Integrated DB2 Repository LUW / zOS DB2 for LUW atabase DB2 Satabase DB2 for LUW se DB2 z/OS Subsystem DB2 for LUW ….. Optional: Required only for historical trend analysis, change tracking, tuning and query/storage optimization Monitored 100s of subsystems or Databases

47. 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

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

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

50. 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 Filter options, e.g. Average getpages > 250000 Sorting options, e.g. Sort by Getpagesdesc