Analyze SMF data with DB 2 Analytics Accelerator

Analyze SMF data with DB 2 Analytics Accelerator for z/OS (IDAA) Peter Bendel IBM Session Code: F 13 Wed Nov 16 th 15: 50| Platform: DB 2 for z/OS / IDAA

Agenda • IT operational analytics goals • What is SMF data • Why analyze SMF data • How to load SMF data into IDAA • • Why use IDAA for log analysis DB 2 Analytics Accelerator Loader overview Loading SMF data into IDAA (initial) Adding SMF data into IDAA (resume) • Other tools that help analyzing SMF data • TDSz and TCR • z. CMA Solution: TDSz, Cognos, SPSS and standard reports • Sample SMF reports • Discussion on other ideas for working with SMF data 2

IT operational analytics goals What is SMF data 3

SMF records • IBM System Management Facility (SMF) is the logging component of z/OS • SMF records are the standard logs generated by operating system and applications on z/OS • Many IBM and non-IBM products including the operating system write SMF records • Typically the SMF records are written to VSAM datasets and then periodically dumped (by SMF dump program (IFASMFDP)) to sequential files 4

SMF - important record types • Each record has a numbered type and optionally subtype • 70 – 79 Resource measurement facility (RMF) performance and usage instrumentation of resources such as processor, memory, disk, cache, workload, virtual storage, XCF and Coupling Facility, supplemented by type 30 subtypes 2 and 3(address space) • 80 RACF (Security) • 100, 101, 102 DB 2 (Database) • 110 CICS (Transaction monitor) • 115, 116 Websphere MQ (Message queueing) • 120 Websphere application server (J 2 EE application server) • The data model (fields included) of the record types changes across releases to introduce new fields or deprecate existing ones 5

IT operational analytics goals Why analyze SMF data 6

The Business of ‘IT’ is NO Different! Have real time visibility into stock levels Optimize staffing mix, reduce employee turnover Optimize customer calls prevent churn Increase cross sell Operations Human Resources Work on scenarios to align development resources Product Development Customer Service IT Sales Finance Sell the right product to the right customer at the right time Marketing Provide discovery and planning tools to reduce error-prone spreadsheet work Manage rolling expenses Act on customer insights in real time 7

This is nothing new: IBM z/OS SMF Reference from 2006(!) 8

IT analytics high-level goals • Preventive: Capacity management and planning • For example predict problems (out of space) before they happen • Service Level Reporting - like • Continuous availability / reliability • Response times • Performance analysis and tuning • Cost reduction – move workloads to time-windows with low utilization • Detecting security violations – auditing • Accounting and Chargeback – billing users • Or: Just satisfy your curiosity 9

Capacity management questions • System/Workload Characteristics, Performance and Trending • • • How is my environment performing? What’s driving the demand on my capacity? Is my Workload Manager (WLM) environment properly tuned? Am I achieving my performance goals? Are capacity constraints causing bottlenecks and what is being impacted? What anomalies occurred that impacted resource usage and/or performance? • System/Workload Optimization, Prediction and Forecasting • Do I have windows of available capacity that I can move workloads / applications to in order to alleviate bottlenecks during peak processing? • Can I better balance my resource usage across servers/LPARs/VMs and defer a capacity upgrade? • Do I have enough available capacity to add new workloads/applications to my current environment? • When will I need to upgrade capacity in the future to support the planned addition ofnew workloads/applications? 10

How to load SMF data into IDAA Why use IDAA for log analysis 11

IBM DB 2 Analytics Accelerator for z/OS V 5 Pure. Data System for Analytics (Netezza Technology) z Systems CLIENT Data Studio with DB 2 Analytics Accelerator Studio Plug-in OSA-Express 10 Gb. E Dedicated highly available network connection Users/ Applications Data Warehouse application DB 2 for z/OS enabled for IBM DB 2 Analytics Accelerator

What can IDAA do for SMF analytics • • Query acceleration – run reports on SMF records faster History – cheaper archive of older SMF records Transformation – run schema transformation (ETL) in IDAA Predictive analytics – run data mining from SPSS modeler in IDAA 13

How to load SMF data into IDAA DB 2 Analytics Accelerator Loader overview 14

IBM DB 2 Analytics Accelerator Loader for z/OS v 2. 1 z/OS based Server – Direct Load from Data Sources

Loading non-DB 2 Data Sources into Accelerator Product Comparison IBM DB 2 Analytics Accelerator Loader V 1 – The user must: • Extract data from source (IMS, VSAM, Oracle, SMF, etc. ) • Convert extracted data to DB 2 external load file format • Data. Stage or other tooling can be used • Create a DB 2 table that matches format of extracted data • Add newly created table to the Accelerator • Construct a DB 2 Load utility field specification that describes the input data • Run Accelerator Loader batch job to load data to accelerator Accelerator Loader V 2. 1– Automates entire process: 16 • • User builds a select statement from data source(s) (IMS, VSAM, SMF, Oracle, …) Automatically creates the DB 2 table Automatically adds table to Accelerator Automatically extracts specified source data Automatically converts data to necessary DB 2 format (in memory) Automatically loads data to Accelerator Single batch job! Automatically enables table for acceleration © 2015 IBM Corporation

DB 2 Analytics Accelerator Loader V 2. 1 Architecture Access to non z/OS data

Loading Data via Data Studio Interface High Level Description Create Virtual Table (if non-relational) • Virtual table represents data source (mapping process) • Virtual view can be created to further refine data • where clause, eliminate columns, SQL data conversions (cast) • If single relational data source – No need to create virtual table Generate select statement from relation source or virtual table/view that represents data desired in accelerator Test select on small result set to verify it returns desired data • SQL result set represents data that will be loaded • Test and adjust until happy with result set Choose the DB 2 subsystem and the Accelerator to load Build JCL to perform process • • Creates DB 2 table that matches SQL result set Adds table to accelerator Performs extract of source data and direct load to accelerator Enables acceleration Submit JCL and view results 18 © 2015 IBM Corporation

IBM DB 2 Analytics Accelerator Loader V 2. 1 Provides a complete solution to load DB 2 and non DB 2 data to the Analytics Accelerator Supplies a relational view of non-relational data sources • VSAM, IMS, Sequential, Adabas (Beta), SMF, etc. • Allows users to code familiar SQL against data sources for purpose of loading the Accelerator Consistent access to the data no matter the source Provides end-to-end support for data extraction, conversion, and loading to accelerator in one step • No data is landed in any intermediate files • Automates process of creating DB 2 and Accelerator tables Leverages z. IIP for • Data Extraction • Data Conversion • Data Loading to the Accelerator 19 © 2015 IBM Corporation

Accelerator Loader V 2. 1 Server Details Accelerator Loader Server • Runs as z/OS address space • Acts as a relational database server • Provides mapping facility for non-relational data sources • Manages Virtual Table/View metadata • Manages all access to supported data sources • All data access controlled by z/OS security (pass-through) Accelerator Loader V 2. 1 will require two started tasks • Data Server • Existing Accelerator Loader Started Task New Data Server must be running on same LPAR that is connected to Accelerator Remote access provided to relational data sources • Remote DB 2 (DRDA) • DB 2 LUW AESE • Provides access to Oracle (and other data sources) 20 © 2015 IBM Corporation

Analytics Accelerator Loader V 2. 1 In Memory Data Transformation SQL Result Set Batch DSNUTILB Accelerator Loader IBM DB 2 Analytics Accelerator Loader Server Source SQL Statement DRDA Sources (Oracle) 21 © 2015 IBM Corporation

How to load SMF data into IDAA initial load 22

23

24

25

26

27

28

29

DB 2 Analytics Accelerator Loader V 2. 1 Architecture Access to non z/OS data sources through DB 2 LUW Federation Server

32

33

34

35

Other tools that help analyzing SMF data TDSz and TCR 36

Quick TDSz Overview • Tivoli Decision Support for z/OS (TDSz) collects data from various sources and uses a central repository for easy access to historical enterprise-wide IT reporting. • The primary function of TDSz is to read, convert, combine, and aggregate systems management data and store it in a DB 2 database • Data aggregation over a long period provides historical view of data. TDSz typically stores data in hierarchies of hourly, daily and monthly tables • TDSz also provides reporting capabilities for display and analysis • Performance measurement, capacity management, accounting and service level agreement support are typical use cases 37

New Key Performance Metrics Components in TDSz 1. 8. 2 • New KPM Components are available for z/OS, DB 2, CICS, and IMS The new z/OS KPM component is made up of the following sub-components: 1. 2. 3. 4. 5. 6. 7. 8. Address Space Statistics (SMF type 30) LPAR Statistics (SMF type 70) Storage Statistics (SMF type 71) Workload Statistics (SMF type 72_3) Capture Ratio - Install 2&4 first. Channel Statistics (SMF type 73) Coupling Facility Statistics (SMF type 74_4) Hardware Capacity Statistics (SMF type 113_2) 9. Problem Determination (SMF type 99) 38

KPM Components – Exception Reporting • TDSz V 1. 8. 2 introduces built-in exception reporting into the KPM subcomponents: • Any figure that breaches a pre-defined threshold will be written to an exception table for easy reporting and investigation. • These are the default Exception Thresholds supplied with TDSz V 1. 8. 2: • These values can be changed in lookup table KPM_THRESHOLDS.

Solution Capabilities • TDSz • Day to day management • Service level monitoring • Historical trends • Highly customizable product to define only data you want collected and reports that can be created • Tivoli Common Reporting V 3. 1 (TCR) • Robust and flexible reporting providing greater insight • Built on the strength of Cognos 10 (Version 3 of TCR) • Can build dynamic and active reports to fit specific business requirements • Little to no knowledge of SQL needed • Cognos Workspace can be used to combine data/charts from multiple defined reports 40

How to load SMF data into IDAA z. CMA Solution: TDSz, Cognos, SPSS and standard reports 41

Capacity Management Analytics on System z (CMA) • Beyond TCR, there is the IBM z. CMA solution • Includes z. CMA, and SPSS for predictive modeling of future behavior • Built off of the same TDSz and Cognos base

CMA v 2. 1. 1 overview Solution Kit Modules Solution Installer Systems Management & Optimization Software Cost Analysis Capacity Planning and Forecasting Problem Identificatio n Application Analytics User Defined Extensible Report Templates IBM Enterprise Platform Reporting & Visualization Cognos BI CPLEX Modeling & Optimization SPSS Data Collection, TDS for z/OS Scheduling & Automation DB 2 IBM DB 2 Analytics Accelerator SPSS Model 43

Capacity Management Analytics data flow 44

Sample SMF reports 45

TDSz Report Example Pre-built reports typically have both required and optional filters – Prompts can be filled in using ‘PF 4’ to find valid filter parameters Output will be either text table and/or graphical display of data Data tables saved into specified output files 46 © 2015 IBM Corporation

TCR Reports Example • Reports can be designed and saved to be run either on a schedule or ad-hoc as needs arise • Reports can be saved as PDF, HTML, or EXCEL files for distribution or for later analysis • Can easily and rapidly combine data from different areas into a single report of your choosing • • 47 For example, CPU demand by Service class from RMF 72 records combined with partition rolling four hour average from RMF 70 records DB 2 performance data with RMF data

CMA report example 48

Systems Management & Optimization Example: Linux on z Systems 49

Systems Management & Optimization IBM Capacity Management Analytics Question: Are you getting the most out of your z. IIP engines? • System z Integrated Information Processor (z. IIP) & System z Application Assist Processor (z. AAP) • Specialty processors have lower hardware acquisition costs and z. IIP’s & z. AAP’s don’t impact software pricing based on capacity Question: Are you getting the most out of your mainframe? • Prescriptive recommendation of LPAR Policy • Analyze whether utilization is over/under your guaranteed share • What-if analysis of different target demand workload to make best use of available capacity.

LPAR weight optimization Run result 51

MIPS Used by Applications in an LPAR by day Application Analytics 52

Discussion on other ideas for working with SMF data 53

IBM Capacity Management Analytics Perform simple ad hoc analysis to predict potential issues before they impact the business Question: Can you quickly identify where problem might happen? Week Aggregation 10 Minute Aggregation Problem Identification Start with a top-down, big picture view Then drill down to greater detail to identify potential capacity problem Detect potential problems before they happen and take action to prevent them SMF data Real Time Scoring 54

Capacity Planning and Forecasting – SPSS Time Series Modelling 55

Peter Bendel IBM peter_bendel@de. ibm. com Session F 13 Analyze SMF data with DB 2 Analytics Accelerator for z/OS Please fill out your session evaluation before leaving!

Acknowledgements • Part of this presentation uses screenshot of a DB 2 Analytics Accelerator loader demo prepared by the loader team • Part of this presentation uses slides from presentations given by Jaime Anaya – janaya@us. ibm. com on CMA • Part of this presentation uses material from presentations given by Bradley Snyder(Bradley. Snyder@us. ibm. com ) at Share conference on TDSz and TCR 57

CMA PIDs – CMA v 2. 1 on z/OS vs CMA v 2. 1. 1 on Distributed IBM Capacity Management Analytics on z/OS, V 2. 1 Announcement Letter URL : https: //ibm. biz/Bd. Xtfq Ø Shop. Z product • • Ø Product Identification Number / Program Number : 5698 -CMA Subscription and Support PID number : 5698 -AA 7 • Pre reqs: IBM DB 2 z/OS V 10 or V 11 (required w/ IDAA) Included products ü ü ü IIBM Tivoli® Decision Support for z/OS V 1. 8. 2 IBM Cognos® Business Intelligence for z/OS V 10. 2. 2 IBM SPSS® Modeler Gold with Scoring Adapter for z Systems™ V 17. 0 Modeler, Collaboration and Deployment Services, Scoring Adapter for DB 2 z/OS IBM ILOG® CPLEX® Optimization Studio V 12. 6. 2 IBM Capacity Management Analytics Solutions Kit V 2. 1 IBM Capacity Management Analytics V 2. 1. 1 Pre reqs: • IBM DB 2 z/OS V 10 or V 11 (required w/ IDAA) Product Program number : 5725 -M 72 • IBM Tivoli® Decision Support for z/OS V 1. 8. 2 IBM Capacity Management Analytics PVU License + SW Subscription & Support 12 Months : D 11 AYLL Announcement Letter URL : https: //ibm. biz/Bd. Xtfg Ø Passport Advantage product • • • Ø IBM Capacity Management Analytics PVU Annual SW Subscription & Support Renewal : E 0 IB 9 LL Included products ü ü ü IBM Cognos® Business Intelligence V 11. 0. 2 IBM SPSS® Modeler Gold V 17. 1 Modeler, Collaboration and Deployment Services IBM ILOG® CPLEX® Optimization Studio V 12. 6. 3 IBM Capacity Management Analytics Solutions Kit V 2. 1. 1 IBM Capacity Management Analytics Solution Installer V 2. 1. 1 58

Data Used in CMA Solution Reports, Forecasting and Optimization