db2 real-time analytics meeting wayne, pa 2015 - idaa & db2 tools update
TRANSCRIPT
![Page 1: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/1.jpg)
DB2 for z/OS Real-Time Analytics
IDAA and IBM DB2 Tools Update
1
Baha MajidIBM Senior Certified IT [email protected] 2015
![Page 2: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/2.jpg)
2
Disclaimer– Information regarding potential future products is
intended to outline our general product directionand it should not be relied on in making apurchasing decision. The information mentionedregarding potential future products is not acommitment, promise, or legal obligation to deliverany material, code or functionality. Informationabout potential future products may not beincorporated into any contract. The development,release, and timing of any future features orfunctionality described for our products remains atour sole discretion.
![Page 3: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/3.jpg)
AGENDA
IBM Strategy
DB2 Tools & Solution PacksPerformance Solution PackUtilities Solution PackFast Recover Solution PackAdministration Solution Pack
IBM DB2 Analytics Accelerator (IDAA)
IDAA Loader
QMF for z/OS
Closing
![Page 4: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/4.jpg)
DB2 Tools and Utilities Tools Strategy
Address customer needs, today and into the future Support core function & new technology Reduce CPU, ET & resource consumption Maximize availability Remove constraints & limitations Simplify data management Augment support with Tools
DB2 Utilities Solution Pack (e.g. DB2 Sort, Utility Enhancement Tool)
4
![Page 5: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/5.jpg)
5
Understanding Analytics
The goal of analytics is todeliver greater insight tothe business to maximizebusiness performance
Analytics are only as goodas the underlying datafoundation
Query Acceleration
Information ManagementFoundation
Info
rmat
ion
Man
agem
ent T
ools
Replication
Business Analytic Landscape
Reporting
Ad-H
ocA
nalysis
Statistical
Analysis
Predictive
Analytics &
Forecasting
![Page 6: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/6.jpg)
6
DB2 for z/OS Tools PortfolioDB2 Utilities Solution Pack
for z/OS
DB2 High Performance UnloadDB2 SortDB2 Automation ToolDB2 Utilities Enhancement Tool
DB2 Fast Copy Solution Packfor z/OS
DB2 Recovery ExpertDB2 Cloning Tool
DB2 Performance Solution Packfor z/OS
OM XE for DB2 Performance ExpertDB2 Query MonitorDB2 SQL Performance AnalyzerOptim Query Workload Tuner
DB2 Administration SolutionPack for z/OS
DB2 Administration ToolDB2 Object Comparison ToolDB2 Table EditorOptim Configuration Manager
DB2 Administration Toolkit SAP EditionData Studio
DatabaseAdministration
DB2 Utilities SuiteDB2 Automation Toolkit SAP Edition
UtilitiesManagement
DB2 Log Analysis ToolDB2 Change Accumulation ToolDB2 Object Restore ToolApplication Recovery Tool for IMS and DB2 Databases
Backup andRecovery
Optim Query Capture and Replay for DB2 on z/OSDB2 Path CheckerDB2 Bind ManagerInfoSphere Data Architect
Testing andMigrationManagement
Business Analyticsand DataWarehousing
Cognos Business Intelligence for z/OSCognos Business Intelligence for Linux on System zSPSS for Linux on System zDB2 Query Management Facility (QMF)DB2 Analytics Accelerator
PerformanceManagement
OMEGAMON XE for DB2 Performance MonitorDB2 Buffer Pool AnalyzerDB2 Performance Toolkit SAP EditionOptim pureQuery RuntimeTransaction Analysis Workbench
InformationGovernance
InfoSphere GuardiumInfoSphere Optim Data GrowthInfoSphere Optim Data PrivacyInfoSphere Optim Test Data ManagementInfoSphere Guardium S-Tap for DB2
IBM Tools Base for z/OS
InformationIntegration
InfoSphere Information Server Enterprise EditionInfoSphere Information Server for Data IntegrationInfoSphere Information Server for Data QualityInfoSphere Information Server for BusinessInformation ExchangeInfoSphere Data Replication for DB2 for z/OSInfoSphere Classic Federation ServerInfoSphere Classic Data Event Publisher for z/OSInfoSphere Classic Replication Server for z/OS
![Page 7: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/7.jpg)
IBM DB2 Solution Packs
![Page 8: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/8.jpg)
8
Why Solution Packs?
Simplified offerings in key solution areas: Database Administration Utilities Management Performance Management Fast Copy
Value: Complete solution for all needs rather than having to purchase
multiple products (one trip through Procurement) Simplified install and maintenance Build intelligence into when and how actions are performed Optimize performance and resource utilization associated with
DBA activities to reduce TCO
Functional integration / Improved productivity
![Page 9: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/9.jpg)
9
IBM DB2 Tools Solution Packs
DB2AdministrationSolution Pack
DB2Utilities
Solution Pack
DB2Fast Copy
Solution Pack
DB2PerformanceSolution Pack
o DB2 AdministrationTool
o DB2 ObjectComparison Tool
o DB2 Table Editoro Optim Configuration
Manager
o Tivoli OMEGAMON XEfor DB2 PerformanceExpert
o DB2 Query Monitoro DB2 SQL Performance
Analyzero Optim Query
Workload Tuner
o DB2 Sorto DB2 Utilities
Enhancement Toolo DB2 Automation Toolo DB2 High Performance
Unload
o DB2 Recovery Experto DB2 Cloning Tool
http://www-01.ibm.com/software/data/db2/zos/tools/
![Page 10: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/10.jpg)
10
DB2 Performance Solution
Integrate business priorities directly Monitor KPIs to better reflect end user experience Monitor and report on transaction response-time service
objectives Allocate resources according to business priorities
Improve application performance Get query recommendations, optimize statistics, create
appropriate indexes
Optimize results for entire workloads, not just single queries
Prevent performance problems before they occur Use performance data for pre-emptive analysis
and capacity planning Identify query hot spots, tune queries, and validate results
Ensure SLAs and user satisfaction are achieved Pinpoint and isolate problems to correct instantly
1. IDENTIFY1. IDENTIFY
2. DIAGNOSE2. DIAGNOSE
3. SOLVE3. SOLVE
4. PREVENT4. PREVENT
![Page 11: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/11.jpg)
11
DB2 Performance Solution Pack Tivoli OMEGAMON XE for DB2 Performance
Expert for z/OS Full range system and application monitoring Detailed reporting and performance warehouse with
integrated real-time monitor and Extended Insightfeature
DB2 Query Monitor for z/OS Dedicated SQL monitoring
Current and historical views of query detail throughoutDB2 subsystems with ability to set alerts for earlywarning
Optim Query Workload Tuner for DB2 forz/OS Eclipse-based SQL performance analysis
Enables visualization of query hotspots and includesexpert advisors to provide tuning recommendations
DB2 SQL Performance Analyzer for z/OS z/OS-based SQL performance analysis Cost estimate and explain function with “what if” analysis
Identify, diagnosesolve & prevent
DB2 PerformanceSolution
More companiesare facing growth ofdata volume, butnot staff. Need:
•Smarter approachw/ less resources
•Proactively optimizeperformance
•Keep up more w/users, growing&changing requests
“Improvingthe bottom-line”
![Page 12: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/12.jpg)
12
Where is my DB2 application spending its time? OMEGAMON PE’s Extended Insight - an advanced way to monitor the
database workload (SQL) of your applications and solutions Get total response times and response time breakdown (appl,
driver, network, data server) per defined workload/cluster (e.g. persystem, application, user)
Compare workload from various servers / applications Select a time period for analysis Get top SQL statements per workload Identify top clients contributing
in the workload
OperatingSystem
DB2
Network
JCC driver
WebSphere orJava App Server
Application
User
SQL 1 SQL 2 COMMIT
User experience
transaction
App pre- and post-processing
![Page 13: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/13.jpg)
Enhanced 3270 Interface
13
Easier navigation and fewer screeninteractions to accomplish tasks, instead of aseries of green screens to gather and displayinformation,.
Transactions can be linked across multiplesysplexes; no need to move between multiplescreens and monitors.
Centralized management environment (TivoliOMEGAMON Manager) from which variousactivities can be launched
Color highlighting to flag problems for quickerresolution.
TEPS like consolidated data across entireOMEGAMON family
z/OS-widesysplex view
CustomizeViews
e-3270 is a “green screen” GUI that provides an enterprise view ofinformation supported across the entire OMEGAMON family.
“To us, these new IBM 3270 screens accomplish functionally the same thing as their GUI equivalents. The 3270 “graphical “green screen”approach offers the same information that a GUI screen offers — only displayed slightly differently. What IBM has done with its “GUI on agreen screen” is it has allowed some mainframe managers to stay within their 3270 comfort zone while getting the same display benefitsthat GUI mainframe managers get using graphically-oriented products “ – Clabby Analytics
Enterprise management summary can be created that showswhat is happening across all active Sysplexes
![Page 14: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/14.jpg)
14
Cross Product Linking
Embedded Data and Cross Product Linking with OMEGAMON XEfor CICS
CICS embedded data in a sub panel in DB2 thread workspaces and CICS RCT Summary workspace Zoom items to navigate directly to CICS workspaces OMEGAMON XE for CICS must be installed and configured to the same ITM
environment CICS agent is running and reporting to the same ITM environment
![Page 15: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/15.jpg)
15
As a DBA, when I'm analyzing threads, I'm interested to navigate from the E3270UI ThreadDetail screens to the QM SQL Activity to further analyze SQL performance problems,additional information about the object associated with the thread and statement.
Zoom on "Active SQL Count" (providedby Query Monitor imbedded in theOMEGAMON DB2 screen) seamlessly tothe Query Monitor screen below.
Cross Product Linking
Embedded Data and Cross Product Linking with DB2 Query Monitor
![Page 16: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/16.jpg)
16
SQL Text in the Enhanced 3270Helps you to simplify problem identification and resolution
![Page 17: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/17.jpg)
17
Thread Accelerator Activity in the Enhanced 3270
ThreadAcceleratorActivitySummary
ThreadAcceleratorActivity Detail
![Page 18: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/18.jpg)
18
Batch Reporting - Accelerator DataBatch RECTRACE support (“eligibility” & metrics)
![Page 19: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/19.jpg)
19
PE Client – IDAA Support
Dynamicacceleratedstatementview
Statementacceleratedflag
![Page 20: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/20.jpg)
20
Batch RECTRACE (IFCID 2) - Accelerator Data
![Page 21: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/21.jpg)
21
OMEGAMON for DB2What can you save using the Analytics Accelerator?
![Page 22: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/22.jpg)
What’s New in DB2 Query Monitor V3.2?
Support new features of DB2 11 Use of zIIP processors to lower cost of monitoring Collector code path optimization New OPTKEY – PTEXT
Strip literals and multiple blanks from the summary text Additional delay statistics on Object panel
Lock, Latch, Page Latch, Synchronization I/O delays Sort and detect which object is having a delay problem
New operational commands Easier to automate the changing of the monitoring profile
Compare workloads in the activity browser Baseline to another workload Detect and analyze different kinds of changes
22
![Page 23: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/23.jpg)
What’s New in DB2 Query Monitor V3.2?
Share user configurations CAE Browser interface Filters, archive connections, staging table connection, baselines
Export activity browser data PDF or CSV LOAD utility support for offload New batch reports
Based on QMF Customizable
Averages based on execution counts AVGX elapse, CPU, GETPAGES, Delays For better compatibility with the OQWT
23
![Page 24: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/24.jpg)
24
Display information about Accelerator-eligiblequeries
The above example shows that you do ‘have Accelerator eligible workload’ on this DB2 and if you had anIDAA Accelerator you would save ~84% CPU overhead and ~91% in Elapse time for that workload.
User can drill down to plan, program, SQL or Object to view each perspective of eligible savings when usingAccelerator. The Object perspective will show which Objects would need to be moved to Accelerator.
![Page 25: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/25.jpg)
25
Use of QM to identify Accelerated queries
![Page 26: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/26.jpg)
26
Identify, Compare and Segregate Accelerated Querieswith DB2 Query Monitor for z/OS
Ability to visualize DB2 AnalyticsAccelerator queries viamultiple user interfaces (GUI/ISPF)
Provides measurable performancecomparisons to show ROIjustification for acceleratedqueries
Provides filtering to let you seeboth accelerated andnon-accelerated queries to aidin tuning efforts
Query Segregation screen in ISPF
Activity by SQL Text screen in web client
![Page 27: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/27.jpg)
27
Query Monitor – Export Data
![Page 28: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/28.jpg)
28
DB2 Tools Customizer (TCz) – QM customization
Standardizes QM with other tools install process
Builds all necessary jobs to install/configure QM
![Page 29: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/29.jpg)
29
Optim Query Workload Tuner’s “IDAA Advisor”Workload Analytics Acceleration Advisor (WAAA)
Benefit and Value
Operates on a workload basis• DB2 QM and OMEGAMON for DB2 are fantastic input sources
Identifies SQL statements that are eligible for acceleration Provides recommendations on candidate tables to accelerate Quantifies CPU savings if those tables are accelerated Explains why SQL statements aren’t eligible and how they could be
rewritten to become eligible Can recommend which tables should be removed from the
Accelerator
![Page 30: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/30.jpg)
30
Query Selection, Access Plan and Workload Analysis,Tuning with QWT
Enhance your ability to select the bestqueries to be accelerated
Visualize access plans ofaccelerated queries
Perform workload analysis torecommend what objects should beadded/deleted from query tableselection which will result inCPU and Elapsed Time savings
“ACCELERATED” node shownin Access Plan Graph (withaccess plan details below)
![Page 31: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/31.jpg)
31
DB2 Utilities Solution Pack
During DB2 utilities sort processing, reduce CPU usageand elapsed time by up to 50% (using zIIP engines)
Eliminate ALL CPU and elapsed time by avoidingunnecessary utility processing? Set it and forget it, adjust to fit changing needs
Use the fastest and most flexible unload in the market tomodernize your data movement
Set company-wide DB2 utility standards Enforced and auditable
Standardize utility control cards ONE change to embedded DB2 Utility JCL can find
and update every occurrence in every application withthe most current version of DB2 for z/OS
1. MANAGE1. MANAGE
2. OPTIMIZE2. OPTIMIZE
3. AUTOMATE3. AUTOMATE
4. CONTROL4. CONTROL
![Page 32: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/32.jpg)
32
DB2 Utilities Solution Pack
DB2 Sort for z/OS Optimizes overall system efficiency by optimizing
resources
Balances utilization of central storage and disk space tooptimize appropriately
Allocates storage based on system resources tooptimize memory usage
Additional zIIP offload to lower CPU consumption
DB2 High Performance Unload for z/OS Runs outside of DB2, so does not consume competing
resources
Supports efficient migration from non-IBM tools
Can invoke DB2 Sort to reduce CPU usage and elapsedtimeduring utility sort processing
Increased support for image copies using partitions,including one output file per partition when using a globalfull image copy
Optimize, controlmanage & automate
DB2 UtilitiesSolution Pack
How do we get thebest control overDB2 utilityprocessing?
How do we saveCPU & Elapsed timewhile improvingavailability?
How do we do smartautomation for DB2utilities execution?
Optimize, controlmanage & automate
DB2 UtilitiesSolution
How do we get thebest control overDB2 utilityprocessing?
How do we saveCPU & Elapsed timewhile improvingavailability?
How can we smartlyautomate our DB2utilities execution?
“More data,reduce costs”
![Page 33: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/33.jpg)
33
DB2 Utilities Solution Pack
DB2 Utilities Enhancement Tool for z/OS Extend capabilities of IBM DB2 utilities
Utility Syntax Monitor
Establish and maintain company-wide syntaxpolicies
Provides auditing
Ability to cancel and block threads on utility jobs
Utility Message Monitor to control the return code
Set it and forget it utility maintenance
DB2 Automation Tool for z/OS Easy to use profiles can set conditions to determine
when or if utility executes
Improves availability by reducing utility batch window
REORG avoidance saves system resources, especiallyCPU
Optimize, controlmanage & automate
DB2 UtilitiesSolution Pack
How do we get thebest control overDB2 utilityprocessing?
How do we saveCPU & Elapsed timewhile improvingavailability?
How do we do smartautomation for DB2utilities execution?
Optimize, controlmanage & automate
DB2 UtilitiesSolution
How do we get thebest control overDB2 utilityprocessing?
How do we saveCPU & Elapsed timewhile improvingavailability?
How can we smartlyautomate our DB2utilities execution?
“More data,reduce costs”
![Page 34: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/34.jpg)
The Growing DB2 z/OS Skills
Expert DB2 and IMS z/OS skills are dwindling Experienced DBAs and SysProgs continue to retire New DBAs and SysProgs take years to become “experienced” Industry wide – modern employees spend less time in a single role
Becoming less likely to find as many 25+ year experienced DBAsand SysProg
Yet, the need for expert DBA / SysProg skills is growing Demands for 24x7 high performance operation continue to increase Allowed outage windows are shrinking and are less frequent
Maintenance done in those windows is more important than ever Increasing system complexity makes planning, maintaining, and
troubleshooting more difficult and time consumingIMS and DB2 DBAs / SysProgs must become more efficient, more quickly
34
![Page 35: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/35.jpg)
The Solution
Autonomics Automate collection of data
Automate analysis of this data
Automate decisions based off this analysis
Automate execution of decisions
Advanced Graphical Interfaces Consolidate and simplify information from various sources
Simplify the presentation of complex information (visuals)
Shorten the learning curve (integrated assistance and doc)
Integration of Tools IBM Tools start working together and leverage each others functions
“Sum is greater than the total of its parts”
35
![Page 36: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/36.jpg)
Autonomics Objectives
Simple to enable & administer Central oversight, default profiles, new UI in addition to 3270
Provide staged enablement through passive then active management Individual tables through to entire DB2 environments
Open and transparent DB2 tables, stored procedures, Administrative Scheduler, integration
with batch schedulers Extensible with easy integration of other tools and products
OQWT, performance tools, non-utility actions such as WTOs, SMStexts
Preserve investment in Automation Tool, profiles Automation Tool profiles & logic for the core of REORG, COPY,
RUNSTATS automation, UET profiles bring in existing batch utilities
36
![Page 37: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/37.jpg)
DB2 Autonomics Director
Framework for a comprehensive DB2 autonomics environment RTS_SNAPSHOT stored procedure – creates a new snapshot of the
current DB2 real time statistics for a data base or a set of objects DB2 Active Autonomics Director stored procedure – evaluates a list
of possible actions Autonomics
Passive autonomics – automatically gather real time statistics andinformation about the results of running DB2 utilities Used to ID potential problem situations and recommend actions
Active autonomics – utilities are run automatically Priority Number of tasks Time left in the maintenance window
37
![Page 38: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/38.jpg)
IBM Management Console for IMS and DB2 for z/OS
38
DB2 Subsystem
Maintenance Windows
Progressive drill downthrough variety of DB2object dashboards
Autonomics Director for DB2for z/OS (in the no-chargeTools Base) enables:– Charting of DB2 object
statistics through RTSsnapshots with
– Autonomics control to defineprofiles and maintenancewindows
– Integrated support for theDB2 Admin Task Scheduler
![Page 39: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/39.jpg)
39© 2015 IBM Corporation
Management Console - Holistic Dashboards of DB2 ObjectsExtended with the DB2 Utilities Solution Pack
…from DB2 Catalog
…from Automation Tool Evaluations
…captured by UET
…recorded byRTS_SNAPSHOT
![Page 40: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/40.jpg)
40
Management Console – Big Picture
![Page 41: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/41.jpg)
What’s New in DB2 Sort V2.1?
Use of DB2 Sort 2.1 with DB2 utilities, as comparedwith running DB2 utilities alone, may see: *
Reduction of Sort CPU usage Up to 84.8% reduction on machines with zIIP engines (49% without zIIP)
Reduction of Utility CPU usage Up to 61% reduction on machines with zIIP engines (40% without zIIP)
Reduction of Utility Elapsed Time Approximately 45% with zIIP engines (46 without zIIP)
41
* The information contained on this slide is distributed AS IS. Performance data and results presented were determined invarious controlled laboratory environments, using specific, limited test configurations, and are for reference purposes only.Tests were run against the most current versions of DB2 Sort and DB2 Utilities Suite generally available as of October 24 th,2014. Results reported for machines with zIIP engines reflect a situation where all DB2 Sort program zIIP eligibleinstructions are successfully dispatched to execute on available zIIP processor(s). The results that may be obtained inother operating and production environments may vary significantly. Users of the product should verify the applicableresults they might achieve for their specific environment.
![Page 42: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/42.jpg)
42
Customer Challenges & Pain Points
– Reducing CPU usage– Customer wants to reduce CPU usage overall and increase zIIP– Chargeback CPU usage to users
– Reducing batch window– Customer cannot fit objects within batch window for maintenance
– “SORT CAPACITY EXCEEDED” errors during utility processing– Restarting utility jobs requires manual re-sizing as well as using more systemresources such as CPU
– Customer have large volume of data– Customers with large volumes of data, large table spaces that are being sorted,performing large utility sort processing operations involving REORG, LOAD,RUNSTATS, CHECK, REBUILD INDEX or ANY combination of these utilities
– Reducing Cost of 4 hour window– Customer runs utilities inside of the 4 hour window– Additional costs incurred outside of 4 hour window, not sure how much and whatcauses the additional costs
![Page 43: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/43.jpg)
43
DB2 Utilities Enhancement Tool – UtilityExtensions
Extend DB2 Utilities functionality LOAD
CONSTANT/VALUEIF
PRESORT
DISCARDTO – flat file
IFDISCARDS – used to pre-validate Validate SYSREC records against the CHECK constraint and data types
Examine errors fail the load or pause the load
REORG Automatic sizing and creating of mapping table
Support for DB2 11 MAPPINGTABLE and MAPPINGDATABASE
CHECK Discard rows to flat file
Automatically size and creation of discard table
![Page 44: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/44.jpg)
44
DB2 Fast Copy Solution Pack Ensure that unplanned outages do not affect
availability and profits Protect critical DB2 for z/OS objects and data with
an recovery insurance policy Extensive validity checking guarantees a
complete and accurate backup Take advantage of intelligent recovery
Analyze all resources and provideoptimized cost-based recovery in real time
Minimize risk of changes in application introducingcostly errors Health check functionality validates the
ability to recover to selected points in time Eliminate nightly and costly image copies by using
fast replication techniques
1. PROTECT1. PROTECT
2. OPTIMIZE2. OPTIMIZE
3. MODERNIZE3. MODERNIZE
4. CONTROL4. CONTROL
![Page 45: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/45.jpg)
45
DB2 Fast Copy Solution Pack DB2 Recovery Expert for z/OS
Expert backup and recovery capabilities tosimplify and standardize enterprise-widerecovery strategy
Extensive validity checking for backup andrecovery insurance with recommendation forbest recovery plan
Can leverage fast replication technology forbackup and recovery to maximize applicationavailability
DB2 Cloning Tool for z/OS Low cost DB2 subsystem and object (dataset)
level cloning/data refresh Can use fast replication technology to reduce
production online downtime to seconds whencloning
Insure, modernizeoptimize & protect
DB2 Fast CopySolution
CPU Usage almosteliminated, replacingcostly image copieswith System LevelBackup:
One SLB = multipleimage copies
13 TB processed In.5 seconds of CPU!
“Protect DataEliminate CPU”
![Page 46: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/46.jpg)
46
DB2 Cloning Tool – recent enhancements
• Enables the cloning of an entire DB2 for z/OS subsystem by data sets instead of volume copies
• Reduces manual intervention by automatically handling additional target processes, such as rebuildingindexes after data masking or log apply processes
• Offers more choices for consistent data sources by adding the capability to clone from image copies or fromsystem-level backups that are stored on tape
• Increases DB2 for z/OS migration confidence through better support for DB2 for z/OS, V11 object types andprocesses
• Implements parallelism to improve throughput when cloning multiple data set objects by processing eachextent of a large object in a separate task
• Offers increased flexibility to accommodate additional utility operations such as CHECK, REBUILD, REORG,QUIESCE, and RUNSTATS in order to streamline the cloning process
![Page 47: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/47.jpg)
DB2 Recovery Expert DB2 V11 Support!
DB2 V11 Added Drop Column Ability to drop columns from tables via ALTER Statement
New entry in SYSIBM.SYSPENDINGDDL
Creates recovery concerns to point in time before ALTER
Recovery Expert Support: Provides automated recovery around DROP COLUMN
Tracks changes in Schema Level Repository
Detects if recovery to PIT before alter
– Generates DDL with COLUMN added back in
– Performs recovery using DSN1COPY plus REDO SQL
47
![Page 48: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/48.jpg)
DB2 Recovery Expert DB2 V11 Support
DB2 V11 Extended RBA/LRSN
RBA/LRSN expanding from 6 bytes to 10 bytes
Recovery Expert Support:
Enhanced to support automatic detection of expanded RBA/LRSN
Detects if BSDS has been converted. Automatically detects format
Automatic support for recoveries before or after conversion
DB2 V11 New Object Type and DDL Attributes
GLOBAL VARIABLES
Various DDL Object Attribute Changes
Recovery Expert Support:
Tracks new objects and attributes in Schema Level Repository
Log Based Dropped Object Recovery Support
DDL will be generated correctly with new attributes
48
![Page 49: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/49.jpg)
49
DB2 Recovery Expert – Recent Enhancements
• Provides users the capability to undo a transaction or transactionsbased on selection criteria. Recoveries will be done in a transactionallyconsistent manner where all operations of a transaction are undone.
• Allows a recovery to an alternate location. The user will be able torecover to either another object on the same subsystem or to an objecton a different subsystem. Using this feature, they will be able tosimulate a production problem or test out a recovery solution beforeexecuting it in their production environment.
• Delivers an effective way to prioritize the recovery of applications.Through the use of application profile groupings, the user can defineand utilize priorities in operations such as creating image copies,analyzing quiet times, and performing application and disasterrecovery.
• Enhanced system recovery by allowing users to create a CombinedSystem Level Backup (CSLB) that includes one or more systems fromone or more Database Management Systems (DBMSs)
![Page 50: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/50.jpg)
50
DB2 Administration Solution Pack
Manage the growth of data and the database withoutinterruption or risk
Provide solution that deals with the daily tasks as wellas the unexpected interruptions Find problem quickly and solve it even faster
Address shrinking IT budgets and reduced personnel Enable less experienced staff to correctly perform
complex administration tasks Reduce errors through automation to allow
skilled users to focus on other tasks Reduce the negative impact changes can have on
business Minimize impact of changes that affect database,
application and availability Standardize and track changes throughout
application lifecycle
1. NAVIGATE1. NAVIGATE
2. MANAGE2. MANAGE
3. TRACK3. TRACK
4. CHANGE4. CHANGE
![Page 51: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/51.jpg)
51
DB2 Administration Solution Pack
DB2 Administration Tool DB2 Catalog navigation Migration Change management
DB2 Object Comparison Tool Compare DB2 structures Generate APPLY jobs
DB2 Table Editor Edit DB2 data values
Infosphere Optim Configuration Manager Explore Track change Take action
Navigate, managechange & track
DB2 AdministrationSolution
More companies arecoming to IBM astheir ONE DB2 Toolsvendor:
•Investment IBMmakes in DB2 toolsis > than total fromall 3rd party vendors
•Ongoing productdevelopment
•Proven DB2 support
“LimitedResources”
![Page 52: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/52.jpg)
What’s New in DB2 Administration Tool V11?
Ability to manage the Analytics Accelerator Display / Manage the accelerators
Start / Stop / Display related accelerated tables DET command to display all of the detail information
Display accelerated tables Enable / Disable Load
Run Accelerator functions in batch Adding a virtual Accelerator for testing purposes
Support for DB2 Autonomic functions Manage RUNSTATS profiles Display alerts Manage time windows
52
![Page 53: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/53.jpg)
53
What’s New in DB2 Administration Tool V11?
Testing the Accelerator connection
![Page 54: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/54.jpg)
54
What’s New in DB2 Administration Tool V11?
Detail information about the Accelerator
![Page 55: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/55.jpg)
What’s New in DB2 Object Comparison Tool V11?
Exclude specification – can specify objects to be excluded from GEN (DDL extraction) Compare
Ignore changes specification Provide the ability to specify specific changes which can be ignored
when doing a compare May specify a time range
Improved means of generating a base version before a change or after achange
Added --#RESTART identifiers in the work statement list member Added CM Batch facility Improved masking (global changes)
Can now mask using the fully qualified object name (3 part name) Added more granularity
55
![Page 56: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/56.jpg)
What’s New in DB2 Object Comparison Tool V11?
Masks, Ignores, Exclude Specifications, Ignore Change Specifications Can be stored in DB2 tables
DB2 Object Comparison Tool report output can be saved and reviewedfrom the tool
Change Propagation
How do you distribute and track changes to each of your environments?
Submit multiple jobs? Logon to each target? What if object names are different? Which target has the change applied? How many environments do you support? Do you synchronize the environments? What is your time window?
56
![Page 57: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/57.jpg)
What’s New in Change Management?
Multi-Target Change Propagation
Register a multi-target change once on a central system
Easily distribute the change to individual targets or a group of targets
Automatically via DRDA*
Register to remote system’s change management database
Status updates flow back to the central system
Optionally include masking at each target to adjust for namingdifferences
Each target’s status and the overall status is reflected on the centralsystem
One view of where the change has been deployed and applied
57
![Page 58: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/58.jpg)
58
InfoSphere OPTIM Configuration Manager(OCM)
Client Value
Server Value
• IBM has the only solutionin the marketplacedesigned to centrallymanage data clients andtrack the configurationchanges to the applicationsdata clients or serverswhile maintaining systemavailability and databaseintegrity.
• Improved operationalefficiency by enforcingdatabase properties toconform to establishedDBA policies
Centralizes management of database configurations bothservers and clients on z/OS and LUW
GUI InterfaceDB2 z/OS 10 & 11
![Page 59: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/59.jpg)
59
Optim Configuration Manager: Key Features
Understand YourEnvironment
Explore client and databaseserver properties
Create a baseline inventoryof clients for upgrades
Solve Problems Faster
Prevent problems by schedulingconfiguration compare jobs thatissue Alerts for differences
Keep configurations in synch Debug problems faster by reviewing
recent changes made to client orserver configurations
Centrally Manage Entire DB2z/OS Environment
Deploy Location Aliases orApplication Profiles with DB2 forz/OS v10
Define zParms with DB2 for z/OSv9 or DB2 for z/OS v10
Show changes made via z AdminTool and z Admin OC Tool
Control Clients (JCC, CLI, .NET)
Isolate/Throttle “rogue” Apps tolimit impact on the SYSPLEX
Enforce best practicesconfiguration – WLM, WLB, WASPool
Redirect client connections forhigh availability or staged roll-out
Note: With OCM client install
![Page 60: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/60.jpg)
60
InfoSphere Optim Configuration Manager
FUNCTIONS z/OS LUWExplore data servers, track changes and compareconfigurations
Explore clients, track changes and compare configurations Redirect database connection requests Isolate applications; (penalty box, proving ground orgeneral purpose)
Enforce client properties so that they conform to theestablished policies of z/OS Workload ManagementService Classes
Modify properties of deployed database clients anddrivers to achieve optimal workload balancing in thedatabase
(pureScale)
Identify storage optimization opportunities through reclaimedstorage, compression and least used objects
![Page 61: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/61.jpg)
IBM DB2 Analytics Accelerator (IDAA)
![Page 62: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/62.jpg)
62
SPEED• Dramatically improve query response – up to
2000X faster – to support time-sensitive decisions• Right-time. Low latency. Trusted. Accurate.
SIMPLICITY• Simplify infrastructure, reduce ETL and data movement
off-platform• Non-disruptive installation
SAVINGS• Minimize data proliferation• Lower the cost of storing and managing historical data• Free up compute resources
SECURITY• Safeguard valuable data under the control and security
of DB2 for z/OS• Protected. Secured. Governed.
A workload optimized, appliance add-on to DB2 for z/OS that enables theintegration of analytic insights into operational processes to drive businesscritical analytics & exceptional business value.
IBM DB2 Analytics Accelerator for z/OS
![Page 63: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/63.jpg)
63
DB2 11 The Foundation for Business Critical Analytics
DB2 for z/OS andIBM DB2 Analytics Accelerator
OLTP Transactions
Real time data ingestion DB2 NativeProcessing
High concurrency
Standard reports
OLAP
Complex queries
• CPU savings benefitDB2 query workloadsincluding short runningoperational analytics,and reporting
• DB2 AnalyticsAccelerator benefitsdata warehouse, OLAP,and complex queryworkloads
Operational analytics
![Page 64: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/64.jpg)
64
IDAA Advisor Scenario
Order by Avg CPU time
Top 10 queries
Capture queries into OQWT
Start by capturing “Top 10” queries from DB2 Query Monitor (QM)
Drill down from QM web interface
Transfer top 10 queries to OQWT as a workload for tuning
64
![Page 65: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/65.jpg)
6565
Invoke Workload Analytics Acceleration Advisor
Invoke AnalyticsAcceleration Advisor
![Page 66: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/66.jpg)
66
Review Accelerator Recommendations
Actual CPU savings from query offloading
Estimated savings from query offloading
Add Recommended Tables to Accelerator
List of tables recommended for offloading
Eligible Statements for offloading
66
![Page 67: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/67.jpg)
67
Review Recommendations – Ineligible SQL
Ineligible Statements for offloading
Display ineligible reasons (QI_DATA)
67
![Page 68: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/68.jpg)
68
Review Recommendations – Rewritable SQL
Display all queries that can be rewritten to become eligible
Non read-only queries can be rewritten byadding “FOR FETCH ONLY” clause
68
![Page 69: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/69.jpg)
69
Common Accelerator questions
![Page 70: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/70.jpg)
70
DB2 Analytics Accelerator V5.1 - Highlights
Version 5.1 of DB2 Analytics Accelerator opens up a new dimension of analyticalprocessing by introducing in-database analytics, in-database transformation andaccelerator-only tables:
In-database analytics capabilities enable acceleration of predictive analyticsapplications. This enables SPSS/Netezza Analytics data mining and in-databasemodeling to be processed within the Accelerator.
In-database transformation supports the consolidation of ETL/ELT processing in DB2for z/OS to enable a more cost-effective, simplified architecture.
Accelerator-only tables can benefit statistics and analytics tools that use temporarydata for reports. The high velocity of execution enables these tools to quickly gather allrequired data.
![Page 71: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/71.jpg)
71
Load and continuous operation improvements within the incremental updatebetter support enterprise clients.
Augmented security and improved performance with new PureData® technologyextend advanced security with encryption of data at rest and of data in motionwhile taking advantage of the legendary built-in security of System z®.
Enhanced systems management capabilities enable faster data synchronizationand data load (incremental update) as well as improved serviceability withautomatic call home capability.
Additional functional highlights new in Version 5.1(contd.)
![Page 72: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/72.jpg)
72
What‘s new in V4.1 PTF 6
Continuous Replication: Incremental update processing now continues for all enabled tables while
tables are enabled, disabled, loaded or reloaded for replication. Thisreduces replication latency, ensures near-realtime availability of replicateddata and keeps DB2 for z/OS tables availabe for business changes.
DB2 for z/OS 11 support for: Accelerator-only-tables for in-database transformation and multi-step
processing Caching of accelerated queries in the dynamic statement cache New set of statement level monitoring counter
Supports accelerating queries using the TIMESTAMP_ISO function
Trace facility enhancements The tracing facility has been enhanced to include the output from the
support tool ‚nzlogcollector‘ running on the Accelerator. Nzlogcollector collects information for a specific disk or another hardware
component
![Page 73: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/73.jpg)
Accelerator-only tables – Technical basics
AOTs are created and dropped using DB2 DDL statements (CREATE; DROP)• Accelerator must be started• QUERY ACCELERATION behavior may have any value during CREATE/DROP• Syntax:
CREATE TABLE MYTABLE (...) IN ACCELERATOR <ACCEL1>;
DROP TABLE MYTABLE;
Recommended to create a database in DB2 to be used for the AOTs• CREATE TABLE MYTABLE (...) IN ACCELERATOR <ACCEL1> IN DATABASEMYDB;
• Usual authorization necessary to create objects in database
SELECT and INSERT/UPDATE/DELETE operations using AOTs can only run on theAccelerator• QUERY ACCELERATION behavior must be set to ENABLE/ELIGIBLE/ALL• Accelerator-shadow tables, Accelerator-archived tables and other AOTs can be used
in the same statement
Dynamic and static SQL can be used with AOTs
![Page 74: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/74.jpg)
Multi-step reporting applications with DB2 forz/OSBEFORE Accelerator-only tables: Report processing on DB2, source datamight reside on the Accelerator already
Temporaryresults
22Temporaryresults
11Temporaryresults
Reports and Dashboards
nn
Multi-Step Report
nn
11
22
Reporting Application
Credit CardTransaction History
CustomerSummary Mart
Credit CardTransaction History
CustomerSummary Mart
![Page 75: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/75.jpg)
Multi-step reporting applications with DB2 forz/OSWith Accelerator-only tables: Temporary objects and processing on theAccelerator
Temporaryresults
22Temporaryresults
11Temporaryresults
Reports and Dashboards
nn
Multi-Step Report
nn
11
22
Reporting Application
Credit CardTransaction History
CustomerSummary Mart
Credit CardTransaction History
CustomerSummary Mart
Data for transactional and analytical processing
![Page 76: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/76.jpg)
In-database transformationUsing Accelerator-only tables and ELT logic in the Accelerator
Transaction ProcessingSystems (OLTP)
Analytics
Advantages:• Simpler to manage• Better performance and
reduced latency Data for transactional and analytical processing
CustomerTransactions
CustomerData
Customer TransactionSummary and HistoryAOTs
Customer SummaryMart AOTs
CustomerTransactions
CustomerData
ELT logic
![Page 77: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/77.jpg)
Data scientist work areaUsing Accelerator-only tables for ad-hoc analysis
Transaction ProcessingSystems (OLTP)
Data for transactional and analytical processing
CustomerTransactions
CustomerData
CustomerTransactions
CustomerData
Work databaseJohn
Work AreaAOTs
Work databaseBobWork Area
AOTs
Data Scientist John
Data Scientist Bob
![Page 78: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/78.jpg)
Integrate more data sources for analyticsLoad external data to the accelerator and save combined analytic resultsin accelerator-only tables
Transaction ProcessingSystems (OLTP)
AnalyticsData for transactional and analytical processing
CustomerTransactions
CustomerData
Combined resultin AOTs
CustomerTransactions
CustomerData
Relateddata from
othersourcesExternal
Files
Imagecopy
DB2 Analytics Accelerator Loader
![Page 79: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/79.jpg)
• Deeper insight into operational status through faster reportingSupport multistep reporting applications. Third party BI reporting suites, QMF, home grown applications, etc.
• Simplifying data-transformation processesDelivering in-database transformation within DB2 Analytics Accelerator
• Data mart consolidation through flexible data infrastructureHost data marts on z Systems, where the data originates
• Deeper insight into customers and marketsData scientist work area
• Simpler data integration with DB2 Analytics Accelerator Loader for z/OS (loadnon-DB2 for z/OS data)Assimilate more data sources for analytics to shorten development cycles and speed integration efforts
• In-database analytics to accelerate predictive analyticsImproves the quality of models, speeds calculations and delivers real-time, actionable business processes
What can I do with AOTs?
![Page 80: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/80.jpg)
80
I want to maximize the power of DB2 andSystem z for business analytics. How do Ibring Oracle data to the Accelerator forquery optimization?
How do I improve the loading of objectsinto DB2 ad/or Accelerator withoutimpacting my DB2 production data?
80
IBM DB2 Accelerator Loader
DB2 data– Loaded in into the accelerator and optionally into DB2
simultaneously
– Loaded without writing a program to invoke the storedprocedure
Non-DB2 data (IMS, VSAM, non-z/OS data, etc)– Load into the accelerator without first landing data into
DB2 tables
Load consistent data or historical data on relatedsets of tables
![Page 81: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/81.jpg)
81
Accelerator Loader Use Cases
External Data Load / Dual Load– Load Non-DB2 Data, or DB2 data from System z
• Can load data onto the accelerator and optionally into DB2simultaneously
• Can load non-DB2 data into the accelerator
Consistent Data Load– Load related tables or load historical data
• Loaded related sets of data into the accelerator• Historical data can be loaded into the accelerator to reset
data
![Page 82: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/82.jpg)
82
External Load / Dual Load Functionality
![Page 83: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/83.jpg)
83
Consistent Data Load Functionality
![Page 84: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/84.jpg)
IDAA tooling summary
Surround IBM DB2 Analytics Accelerator with DB2 Tools for z/OS to maximize youranalytics investments and their impact on your business
Accelerator performance monitoring, analysis and reporting OMEGAMON XE for DB2 Performance Expert on z/OS
Accelerated query identification, comparison and segregation DB2 Query Monitor for z/OS
Query candidate selection, access path visualization and analysis InfoSphere Optim Workload Query Tuner for DB2 for z/OS
Administer your IDAA appliances quickly and easily DB2 Administration Tool, DB2 Object Compare, IDAA Loader
![Page 85: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/85.jpg)
IBM QMF for z/OS V11
![Page 86: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/86.jpg)
86
Investment protection in QMF objects(queries, forms, procedures, batchprocesses)
Extend the reach of analytics to moreusers: TSO, Workstation, Web, andMobile
Innovation with dashboards, visualreports, extended graphics and chartingand more
Modernization of existing QMFenvironments
Standardization on a common BI/BAplatform
Broad reach to enterprise data: relational,OLAP, unstructured, Big Data, Excel, IMS
Data federation and caching
Drill thru to any available data (e.g. DB2 toOracle etc.)
Dynamarts – encapsulate data within areport, chart, etc.
Extended platform support: z/OS,Windows, Linux, WebSphere anywhere
High ROI with enterprise-wide deploymentat a fixed cost
Superior support of the IBM DB2 AnalyticsAccelerator
Technical benefits of QMF for z/OS
Data Sources:All Relational DBs,Hierarchical DBs,
Unstructured dataHadoopBigInsights
Workstation, Tablet, Web
Dashboards,visual reports,analytics,
dynamarts
Processes OLAP:SAP B/W,MS Analysis ServicesEssbase
Data Sources:Relational DBs:
DB2 LUW,DB2 iSeries and
DB2 VM/VSE
![Page 87: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/87.jpg)
87
The QMF for z/OS solution components
QMF HPO
• ‘HighPerformanceOption’ for QMFfor TSO/CICS isa multifacetedtool that helpsdatabaseadministratorsmanage QMFobjects andincreaseperformance inthe TSO andCICSenvironments
QMF forWorkstation
• Extends the keyfunctionality ofQMF forTSO/CICS ondistributedplatforms via arich desktopdevelopmentenvironment.Runs onWindows,Linux, and theMac
QMF forWebSphere
• Provides afeature setsimilar to that ofQMF forWorkstation,using a thin-client, browser-based solutionas well asmobile devicesupport. Runson Windows,Linux, Solaris,AIX, HP-UX,iSeries, z/Linux,and z/OS
QMFAnalytics for
TSO• Delivers
unprecedentedcharting andstatisticalanalysiscapabilitiesdirectly to themainframe
QMF for TSOThe ‘core’ component with query, reporting, forms, procsgraphics, with interactive (3270) or batch processingoptions.
![Page 88: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/88.jpg)
88
QMF for z/OS offers:fast, simple connection to a broad spectrum of data sources
IBM DB2for iSeries
IBM DB2for LUW
IBMPureData
Informix
SolidDB IMS
BigInsights
Oracle
SQLServer
TeradataMySQL
Postgres
zSQLSun JDBC-
ODBC bridgedata sources
such asExcel and
text files etc.
Cloudant
Hadoop
IBM DB2for z/OS
WebAccess–httpdata
QMF forz/OS
![Page 89: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/89.jpg)
89
Full support of the DB2 Analytics Accelerator
10GbOSA-Express3
10 GbE
Primary
Backup
zEnterprise
Data Warehouse applicationDB2 for z/OS enabled for IBMDB2 Analytics Accelerator
IBM DB2 AnalyticsAccelerator
NetezzaTechnology
QMF Users &QMF BA solutions
Network
DB2 forz/OS
Note ; All QMF for z/OScomponents include explicitsupport for DB2 AnalyticsAccelerator and prepareand execute queries thatmaximize the chance oftheir being offloaded to DB2Analytics Accelerator, giventhat the data is available onthe system.
![Page 90: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/90.jpg)
90
QMF for z/OS and IDAA Customer Scenario
QMF for z/OS is a DB2 application.Therefore it works seamlessly with the DB2Analytics Accelerator
QMF queries can run over 1000 times fasterwith the Accelerator.
Additionally, QMF can be configured to guidethe optimizer as to which queries can beselected for acceleration
Usage case: customers have QMF applicationsthat mix queries whose data can have a fewminutes of latency with queries where the resultset must reflect the latest transaction results.
QMF and IDAA have been deployed atseveral customers with very good results. Banco Do Brasil reports experienced execution
performance improvement from 10 hours to minutes
"It is our plan to replace Microquery in 2014, when all developers are going to use QMFWebSphere client.“ (Banco Do Brasil)
![Page 91: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/91.jpg)
91
QMF on the Web and enhanced mobile support
QMF for WebSphere
– Disseminate queries, reports and dashboards tousers across the enterprise for all browsers andplatforms
– Create, run and share queries on the Web– Run and share reports on the Web– Deploy dashboards to business users as stand-
alone solutions or embedded within web pagesand web applications
– Deploy to mobile devices (iPad, Android)
Ability to render content in
– Pure HTML & JavaScript*– Adobe Flash Player– PDF– Excel
![Page 92: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/92.jpg)
92
QMF for z/OS v11.2 Enhancements
![Page 93: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/93.jpg)
In Summary
Unparalleled investment in DB2 tools and utilities IBM Utilities Suite is essential for exploitation of major DB2
enhancements Support of core DB2 function from day 1 of GA Support & deliver new technology, new interfaces, advanced
autonomics Continued focus on:
Elimination of application impact from utilities
Elapsed time & CPU consumption reduction
Resource consumption reduction
Reduction in complexity & automation improvements
Solutions through DB2, Utilities & Tools
93
![Page 94: DB2 Real-Time Analytics Meeting Wayne, PA 2015 - IDAA & DB2 Tools Update](https://reader031.vdocuments.us/reader031/viewer/2022012917/58a5a9471a28ab1a628b54a9/html5/thumbnails/94.jpg)