Transcript IBM Smart Analytics Optimizer Version 2

IBM DB2 Data Analytics Accelerator Technology
Exploration - User Group Road Show - Detroit
Columbus Cincinnati - April 08 09 10
© 2014 IBM Corporation
1
Traditional Data Warehousing, & Business
Analytics Market Segmentation
Trans.
Number
Trans.
User Community
Traditional
Distributed
Market
of Users
Volume
Few
Small
Trans.
Latency Availability Type
Less
Less
Important Important Complex
C Level
Mgt
Analysts
(e.g. Mktg, Research)
Company
Management
Customer Service & Support
(e.g. call centers, sales personnel)
Customers
(e.g. external, Web)
Many
Very Large Critical
Critical
Simple
© 2014 IBM Corporation
Typical Current State of Affairs for Analytics:
Execution by Department
Research & Dev.
Marketing
Finance
Executive
Sales
Operations
Finance
Management
IT
3
Customer Care
© 2014 IBM Corporation
Multiply that Across each Department
Development
Development
Sales
Quality Assurance
Finance
Quality Assurance
Production
Production
Disaster Recovery
Development
Marketing
Disaster Recovery
Development
Quality Assurance
R&D
Quality Assurance
Production
Disaster Recovery
Production
Disaster Recovery
© 2014 IBM Corporation
What this Looks Like in the Bigger Picture
Scoring
Rules
Operational
Systems
Analyze
Optimized Business Processes
Analytical
Foresight
Sales Effectiveness
Customer Support
Fraud Management
Claims Processing
x/p server
Data Mining
Segmentation
Bulk
Prediction
Statistical Analysis
Underwriting
Marketing
Continuous feed
Data Mover
Staging
Area
Analytics
Server
OLTP
x/p/z server
x/p server
ODS
(RDBMS)
Staging
Area
Transformation Server
Batch
Process
MultiDimensional
Analysis
x/p server
Hourly/daily
Batch
Process
x/p/z server
Enterprise Data
Warehouse
(RDBMS)
x/p/z server
Departmantal
Data Marts
Report
Online Queries & Reporting
BA Tooling
MIS System
Budgeting
Campaign management
Financial Analysis
Selling Platforms
Customer Profit Analysis
CRM
Cleanse
Transform
Warehouse
© 2014 IBM Corporation
World’s Best Companies Run DB2 for z/OS and System z
Undisputed leader in total system availability, scalability, security and reliability
Availability
Zero downtime - maintenance and upgrades without service disruption.
Hyperswap for storage availability and GDPS for unmatched disaster recovery
Security
Highest protection of business critical data and applications on Evaluation
Assurance Level 5 (Common Criteria Security Certification)
Scalability
Unmatched Near-linear scalability through hardware-based System z
Coupling Facility for DB2 data sharing
Performance
CPU reductions out-of-the-box
Integration
Management integration, multi-platform integration and stack integration
Efficiency
Reduced infrastructure complexity through consolidation, automation and
virtualization. Savings on software and environmental costs, reduce labor, energy
and developmental costs
© 2014 IBM Corporation
OLTP vs. Analytics – Examples
OLTP - “Transactional”
Withdrawal from a bank
account using an ATM
Transactional Analytics:
(Operational BA)
Approve request to increase
credit line based on credit
history and customer profile
Deep Analytics
Regular reporting to central
bank – sum of transactions by
account
Buying a book at Amazon.com Propose additional books
Which books were best-sellers
based on similar purchases by in Europe over the last 2
other customers
months?
Check-In for a flight at the
airport
Offer an upgrade based on
frequent flyer history of all
passengers and available
seats
Marketing campaign to sell
more tickets in off-peak times
Hand-over manufactured
printers to an oversea-carrier
Optimize shipping by selecting Trend of printers sold in
cheapest and most reliable
emerging countries versus
carrier on demand
established markets.
© 2014 IBM Corporation
Creating the Hybrid Data Server – PureData System for Analytics
(Netezza) and System z
Data Mart Data Mart Data Mart
Data Mart Consolidation
Best in OLTP and
Transactional Analytics
Industry recognized leader in
mission critical transaction
systems
Best in Deep Analytics
Transaction Processing
Systems (OLTP)
Proven appliance leader in high
speed analytic systems
Best in Consolidation
Transactional Analytics
DB2 z/OS:
Deep Analytics
Recognized leader in
transactional workloads with
security, availability
and recoverability
PureData System for
Analytics
Unprecedented mixed workload
flexibility and virtualization
providing the most options for
cost effective consolidation
Recognized leader in costeffective high speed deep
analytics
Together:
Destroying the myth that transactional and
decision support workloads have to be on
separate platforms
8
© 2014 IBM Corporation
IBM DB2 Analytics Accelerator for z/OS
Blending zEnterprise and Netezza technologies
A high performance analytics
accelerator appliance for IBM
zEnterprise, delivering dramatically
faster complex business analysis
transparently to all users.
9
Fast
Cost Saving
Appliance
Complex queries run up
to 2000x faster while
retaining single record
lookup speed
Eliminate costly query
tuning while offloading
complex query processing
No applications to
change, just plug it in,
load the data, and gain
the value
© 2014 IBM Corporation
IBM DB2 Analytics Accelerator for z/OS
What is it? What is the value?
What is the value?
What is it?
The IBM DB2 Analytics Accelerator is a
dedicated workload optimized, appliance add-on
to a DB2 for z/OS environment that services
long-running, complex queries.
z Enterprise z196, z114, EC12, BC12
OLTP and Transactional Analytics
IDAA
Deep Analytics
 Breakthrough technology enabling new
opportunities
 Extreme performance for complex analytics
(aka Train of Thought Analysis)
 Improve price/performance for analytic
workloads
 Minimize the need to create data marts for
performance
DB2 for z/OS
 Transparent to DB2 applications and users
 Highly secure environment for sensitive data
analysis
Versions Supported
 IDAA V2 - DB2 for z/OS V9 & V10
 IDAA V3 - DB2 for z/OS V10 & V11
 IDAA V4 (GA 11/29) - DB2 for z/OS V10 & V11
10
© 2014 IBM Corporation
DB2 Analytics Accelerator V3 Features
 Lowering the
 Better decisions
cost of historical
through lower
data
latency of data
 Dramatic improvement
in scale and growth
opportunities
Lowering the
cost of analytic
computing
High Performance Storage Saver
• Significantly reduces the cost for storage resources
• Option to store data only once in the accelerator
Incremental Update
• Data changes are propagated to the accelerator as they happen
• Uses change data capture technology
• Extends the accelerator use to reporting on operational data
New optimization
• Tables or partitions refresh much faster and less resources intensive
• Optimized unloading data from DB2
High Capacity
• Capacity has been extended to 1.28 PB for a single Accelerator
New functions
• More queries eligible for acceleration
11
PureData System for Analytics N2001
© 2014 IBM Corporation
NEW
DB2 Analytics Accelerator V4 Features
More Query
Acceleration
Static SQL
Enhanced
Capabilities
Improved
Transparency
Incremental Update
• Greatly improved scalability
• Better performance
DB2 Version 11
Support added
Multi-row fetch from
local applications
EBCDIC & Unicode in
same DB2 system &
accelerator
Improved performance for large
result sets
HPSS
• Archive to multiple accelerators
• Better access control for
archived partitions
Extend WLM support to local
applications
Automatic workload balancing
over multiple accelerators
New RTS ‘last-changed-at’
timestamp
Automated NZKit installation
HPSS
• Built-in restore
• Protection for image copies of
archived partitions
Richer system scope monitoring
Profile controlled special
registers
Report prospective CPU cost &
Elapsed time savings
Improved continuous operations
for Incremental Update
Separation of duties for
accelerator system administration
operations
Enabling
new
use
cases
© 2014 IBM Corporation
Deep DB2 Integration within zEnterprise
Applications
DBA Tools, z/OS Console, ...
Application Interfaces
Operational Interfaces
(standard SQL dialects)
(e.g. DB2 Commands)
DB2 for z/OS
Data
Manager
Buffer
Manager
Superior availability
reliability, security,
Workload management
...
IRLM
Log
Manager
z/OS on
System z
IBM
DB2
Analytics
Accelerator
Superior
performance on
analytic queries
PureData System
for Analytics
13
© 2014 IBM Corporation
Large Insurance Company
Adding value by Accelerating the Delivery of Business Reporting
Customer Quote:
“we had this up and
running in days with
queries that ran over
1000 times faster”
Query
Total Rows
Reviewed
Query 1
Query 2
Query 3
Query 4
Query 5
Query 6
Query 7
Query 8
Query 9
591,941,065
591,941,065
813,343,052
283,105,125
591,941,089
813,343,052
591,941,065
813,343,052
813,343,052
Total
Qualifying
Rows
2,813,571
2,813,571
8,260,214
2,813,571
3,422,765
4,290,648
361,521
3,425,292
4,130,107
Total
Rows
Returned
853,320
585,780
274
601,197
508
165
58,236
724
137
DB2 Only
DB2 with
IDAA
Hours Sec(s)
2:39 9,540
2:16 8,220
1:16 4,560
1:08 4,080
0:57 4,080
0:53 3,180
0:51 3,120
0:44 2,640
0:42 2,520
Hours Sec(s)
0.0
5
0.0
5
0.0
6
0.0
5
0.0
70
0.0
6
0.0
4
0.0
2
0.1
193
Times
Faster
1,908
1,644
760
816
58
530
780
1,320
13
With Accelerated Time to Value

IBM DB2 Analytics Accelerator (PureData System for
Analytics 1000-12)
Production ready - 1 person, 2 days


Table Acceleration Setup in 2 Hours
- DB2 “Add Accelerator”
- Choose a Table for “Acceleration”
- Load the Table (DB2 Loads Data to the Accelerator)
- Knowledge Transfer
- Query Comparisons


Initial Load Performance
400 GB Loaded in 29 Minutes
570 Million Rows (Actual: Loaded 800 GB to 1.3 TB
per hour)
Extreme Query Acceleration - 1908x faster
2 Hours 39 minutes to 5 Seconds
CPU Utilization Reduction
35% to ~0%
14
© 2014 IBM Corporation
Load times may vary based other workload running on z/OS
IBM DB2 Analytics Accelerator Product Components
PureData System for
Analytics
zEnterprise
Technology
CLIENT
Data Studio
Foundation
DB2 Analytics
Accelerator
Admin Plug-in
Users/
Applications
15
Network
OSAExpress3/4/5
10 GbE
Data Warehouse application
DB2 for z/OS enabled for IBM
DB2 Analytics Accelerator
Primary
10Gb
Backup
IBM DB2
Analytics
Acelerator
© 2014 IBM Corporation
Query routing analysis
 Values for CURRENT QUERY ACCELERATION
Light ODSquery
Light BI Query
Heavy BI
Query
User control and DB2 heuristic
OLTP-like
query
DB2 for z/OS and
Value
Description
IBM DB2 Analytics Accelerator
NONE
No query is routed to the accelerator
ENABLE
A query is routed to the accelerator if it satisfies the
acceleration criteria including the cost and heuristics
criteria. Otherwise it is executed in DB2.
DB2 Native
Processing
If there is an accelerator failure while running the query,
or the accelerator returns an error, DB2 will return a
negative SQL Code to the application
Optimized processing
for BI Workload
ENABLE WITH
FAILBACK
A query is routed to the accelerator if it satisfies the
acceleration criteria including the cost and heuristics
criteria. Otherwise it is executed in DB2.
Under certain conditions the query will run on DB2 after it
fails in the accelerator. In particular any negative SQL
code will cause failback to DB2 during PREPARE or first
OPEN. No failback is possible after a successful OPEN
of a query
 Single and unique system for
mixed query workloads
 Dynamic decision for most
efficient execution platform
ELIGIBLE
 New special register QUERY
ACCELERATION
A query is routed to the accelerator if it satisfies the
acceleration criteria irrespective of the cost and heuristics
criteria. Otherwise it is executed in DB2
ALL
A query is routed to the accelerator, if it cannot execute
the query fails and a negative return code is passed back
to the application
 New heuristic in DB2 optimizer
16
© 2014 IBM Corporation
Routing Criteria
A query can be routed to Accelerator if is one of these types:
 The entire query can be accelerated, i.e. the unit of acceleration is a whole
query
– Individual query blocks are no longer units of acceleration
– The whole query will either run in DB2 or in the accelerator
 The associated cursor is not defined as a scrollable or a rowset cursor
 The query is defined as read-only
 The query is dynamic (V4 will support static)
 The query is a SELECT statement.
 The private protocol is not in effect.
 Routing to Accelerator is considered more efficient for performance than to
execute the query in DB2 mainline
– The decision is based on some heuristic rules
17
© 2014 IBM Corporation
Heuristic Routing Criteria – not just based on
“elapsed time” …
 DB2 Optimizer uses a set of rules to determine whether a given query is better off being
executed in DB2 core engine or routed to the accelerator, such as:
– In general, typical OLTP access path patterns are not routed to the accelerator
e.g. Equal unique access, One fetch access
– If none of these: WHERE, GROUP BY, ORDER BY, aggregate functions is specified (i.e. all rows
are to be returned), the query is not routed
– Threshold specified by the DB2 Profile (1) mechanism:
• If all the tables referred in the query are “small”, the query is not routed
ACCEL_TABLE_THRESHOLD determines total table cardinality for a query
The default value is 1,000,000
• If a “large” result set is expected, the query is not routed
ACCEL_RESULTSIZE_THRESHOLD (number of rows) determines what is a “large” result set.
The default value is -1, which means that this check is ignored
• If estimated total cost for a query is treated as “short running”, the query is not routed
ACCEL_TOTALCOST_THRESHOLD determines estimated total cost for a query
The default value is 5,000 (value is in milliseconds = 5 seconds)
• Recommendation: Use default values. Change only after rigorous testing!
(1) http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2z10.doc.perf/src/tpc/db2z_profiles.htm
18
© 2014 IBM Corporation
Query Off-load Applicability
 Accelerator is based on Netezza which supports rich set of SQL and data types
– BI tools such as Cognos has run on Netezza for years and will run on Accelerator as well
 Due to very large number of query types and SQL functions not all of them could
be processed in V3. Key restrictions include:
– No static SQL (Lifted in V4)
– Not all DB2 functions,
No Mathematical functions such as SIN, COS, TAN.
No advanced string functions such as HEX, POSITION, LOCATE, LEFT, OVERLAY
No advanced OLAP functions such as RANK, ROLLUP, CUBE
– No User Defined Functions
– No correlated table expressions or recursive correlated table expressions
– No correlated subquery in the SELECT list
– Not UTF-16 and MIXED/DBCS EBCDIC
– No multiple encoding schemes in the same statement (fixed in V4)
– Not all DB2 special registers: CURRENT PATH, SERVER, SQLID, SCHEMA,
APPLICATION ENCODING SCHEME
– Not all DB2 data types: LOBs, ROWID, XML, DECFLOAT, BINARY
 None of these restrictions is a design problem, IBM plans to lift them in future
releases based on customer feedback and needs
19
© 2014 IBM Corporation
Query Execution Process Flow
Application
Interface
Optimizer
SPU
CPU
FPGA
Memory
SMP Host
Query execution run-time for
queries that cannot be or should
not be off-loaded to IDAA
IDAA DRDA Requestor
Application
SPU
CPU
FPGA
Memory
SPU
CPU
FPGA
Memory
SPU
CPU
FPGA
Memory
DB2 for z/OS
DB2 Analytics Accelerator
Heartbeat (DB2 Analytics Accelerator availability and performance indicators)
Queries executed without DB2 Analytics Accelerator
Queries executed with DB2 Analytics Accelerator
20
Queries executed with value of “ALL” may receive a SQL Error Code if the query cannot run on the accelerator
© 2014 IBM Corporation
Accelerator Administrative Stored Procedures


















21
ACCEL_ADD_ACCELERATOR
ACCEL_TEST_CONNECTION
ACCEL_REMOVE_ACCELERATOR
ACCEL_UPDATE_CREDENTIALS
ACCEL_ADD_TABLES
ACCEL_ALTER_TABLES
ACCEL_REMOVE_TABLES
ACCEL_GET_TABLES_INFO
ACCEL_GET_TABLES_DETAILS
ACCEL_LOAD_TABLES
ACCEL_SET_TABLES_ACCELERATION
ACCEL_SET_TABLES_REPLICATION
ACCEL_CONTROL_ACCELERATOR
ACCEL_UPDATE_SOFTWARE
ACCEL_ARCHIVE_TABLES
ACCEL_GET_QUERIES
ACCEL_GET_QUERY_DETAILS
ACCEL_GET_QUERY_EXPLAIN
* For a list of the description of each stored procedure
refer to the IDAA Stored Procedure Reference Manual
© 2014 IBM Corporation
The Key to the Speed
select DISTRICT,
PRODUCTGRP,
sum(NRX)
from
MTHLY_RX_TERR_DATA
where MONTH = '20091201'
and
MARKET = 509123
and
SPECIALTY = 'GASTRO'
Slice of table
MTHLY_RX_TERR_DATA
(compressed)
22
FPGA Core
CPU Core
Uncompress
Project
Restrict,
Visibility
FROM
SELECT
WHERE
select DISTRICT,
PRODUCTGRP,
sum(NRX)
Complex ∑
Joins, Aggs, etc.
GROUP BY
sum(NRX)
where MONTH = '20091201'
and
MARKET = 509123
and
SPECIALTY = 'GASTRO'
© 2014 IBM Corporation
IBM DB2 Analytics Accelerator
Table Definition and Deployment
IBM Data Studio Client
IDAA Studio
DB2 for z/OS
IDAA Administrative
Stored Procedures
IDAA
PureData
Catalog
DB2 Catalog



23
The tables need to be defined and deployed to IDAA before data is loaded and
queries sent to it for processing
 Definition: identifying tables for which queries need to be accelerated
 Deployment: making tables known to DB2, i.e. storing table metadata in the DB2
and PureData catalog
IDAA Studio guides you through the process of defining and deploying tables, as wall
as invoking other administrative tasks
IDAA Stored Procedures implement and execute various administrative operations
such as table deployment, load and update, and serve as the primary administrative
interface to IDAA from the outside world including IDAA Studio
© 2014 IBM Corporation
Synchronization Options with IBM DB2 Analytics Accelerator
Synchronization options
Use cases, characteristics and requirements
Full Table Refresh
 Existing ETL process replaces entire table
The entire content of a database table
is refreshed for accelerator processing
 Multiple sources or complex transformations
 Smaller, un-partitioned tables
 Reporting based on consistent snapshot
Table Partition Refresh
For a partitioned database table,
selected partitions can be refreshed for
accelerator processing
 Optimization for partitioned warehouse tables, typically
appending changes “at the end”
 More efficient than full table refresh for larger tables
 Reporting based on consistent snapshot
 Changed partitions only through real-time statistics
(starting with IDAA V3 for DB2 V10)
Incremental Update (starting
w/IDAA V3 for DB2 V10)
Log-based capturing of changes and
propagation to IBM DB2 Analytics
Accelerator with low latency (typically
few minutes)
24
 Scattered updates after “bulk” load
 Reporting on continuously updated data (e.g., an ODS),
considering most recent changes
 More efficient for smaller updates than full table refresh
© 2014 IBM Corporation
Accelerator Data Load
Accelerator
DB2 for z/OS
Table A
Table B
Part 1
Unload
CPU
USS Pipe
FPGA
Memory
Part 2
Unload
USS Pipe
Table D
Part 1
Part 2
.
.
.
.
.
.
.
.
.
Coordinator
Accelerator Administrative
Stored Procedures
Accelerator
Studio
Table C
CPU
Memory
CPU
Part m
Unload
USS Pipe
FPGA
Memory
CPU
Part 3
FPGA
FPGA
Memory
• 1 TB / h – can vary, depending on CPU resources, table partitioning, …
• Update on table partition level, concurrent queries allowed during load
• Unload in DB2 internal format, single translation by accelerator
© 2014 IBM Corporation
Incremental Update – IDAA V3 for DB2 V10
 Changes in data warehouse tables
typically driven by replication or an other
update process
– Corrections after a bulk-ETL-load of
a data warehouse table
– Continuously changing data (e.g.
trickle-feed updates from a
transactional system to the
warehouse or ODS)
 Reporting and analysis based on most
recent data
 May be combined with a full/partition
table refresh
26
Continuous
Query
Processing
DB2 z/OS Query Optimizer
Accelerator
processing
DB2 native
processing
Application
Replication
 Incremental update
can be configured per
table
 Initial full table load is
required
Operational Analytics, Reports, OLAP, …
Incremental Update
Changes
DB2 for z/OS database
© 2014 IBM Corporation
Incremental Update Details
DB2 Analytics Accelerator (Host Node)
DB2 for z/OS
Accelerator Stored
Procedures
Accelerator SERVER
ACCEL_SET_TABLES_REPLICATION
ACCEL_CONTROL_ACCELERATOR
...
Controller
Accelerator
Database
Catalog
information
JCL
Accelerator
Studio
Automation code
insert
(creates data sources,
subscriptions, etc.)
delete
Apply Agent
on NPS host
updat
e
(Receives log events)
IFI Log
Reads
Capture
Agent
(private network)
27
© 2014 IBM Corporation
User Interface
Incremental update UI elements only visible if it has been enabled on the DB2 subsystem via
IBM DB2 Analytics Accelerator configuration console
 Start / stop replication process (per subsystem-accelerator pair)
 Enable / disable replication (per table)
 Trace collection
 Information on replication latency and events
28
28
© 2014 IBM Corporation
Save Over 95% of Host Disk Space for Historical Data
Historical Data
Year
Year -1
1Q
1Q
2Q
2Q
3Q
Year -2
Year -3
Year -4
Year -5
Year -7
1Q
1Q
1Q
1Q
1Q
2Q
2Q
2Q
2Q
2Q
3Q
3Q
3Q
3Q
3Q
3Q
4Q
4Q
4Q
4Q
4Q
4Q
Current Data
4Q
One Quarter = 3.57% of 7 years of data
One Month = 1.19% of 7 years of data
One month = 2.78% of 3 years of data
29
© 2014 IBM Corporation
High Performance Storage Saver
Reducing the cost of high speed storage
 Time-partitioned tables where:
– only the recent partitions are used in a transactional context (frequent
data changes, short running queries)
– the entire table is used for analytics (data intensive, complex queries).
 High Performance Storage Saver’s “Archive” Process:
–
–
–
–
Data is loaded into Accelerator if not already loaded
Automatically takes Image Copy of Each Partition to be Archived
Automatically Remove data from DB2 archived tablespace partitions
DBA starts archived partitions as read-only
DB2
Query from
Application
Part
#1
No longer present on DB2 Storage
Or
Part
#1
30
Active
Part
#2
Accelerator
Part
Part
#3
#4
Part
#5
Archive
Part
#6
Part
#7
© 2014 IBM Corporation
High Performance Storage Saver
Reducing the cost of high speed storage
Store historic data on the Accelerator only
Applications
Tables can be resident on:
1. DB2 Only
2. DB2 and Accelerator
3. Archive to Accelerator
SQL
DB2
Table A
DB2
DB2
Table A
Table A
Active
When data no longer
requires updating, reclaim
the DB2 storage
Managed by zPARMs
Accelerator
Accelerator
Table A
Table A
 Best for OLTP
 High Speed
Indexed queries
31
 Mixed Workload
Active &
Archive




Active Only
Archive Only
Active & Archive
Mixed Workload
Controlled by Special Registers:
 CURRENT QUERY ACCELERATION
 CURRENT GET_ACCEL_ARCHIVE
© 2014 IBM Corporation
Key Elements of the Implementation Approach
• Older partitions are moved to IDAA and their data no longer exists in DB2
• DB2 is still solely responsible for the recovery and maintains all backups (copy images)
• The most recent partitions exist in both DB2 and IDAA
• They are synchronized by existing means
• Partition refresh or incremental update (replication-based propagation of changes)
• Having the most recent partition in both, DB2 and IDAA, provides IDAA-driven
performance acceleration for analytical queries that access most recent partitions only
• The data move process is encapsulated in a stored procedure
• The stored procedure can be invoked directly or via IDAA Studio
• The SQL statements do not change
• The fact that some partitions have been moved to IDAA is transparent
• By default, queries access only the data from the most recent partitions
• The queries can be executed in DB2 or IDAA based on the standard routing criteria
• If all the data need to be accessed, one of the following mechanisms is used:
• Setting a zparm which activates the 'all data' scope for the DB2 subsystem/data sharing group.
This way, none of the applications need to be changed (but this setting has global impact).
• Setting a special register “CURRENT GET_ACCEL_ARCHIVE”, which allows switching between
the 'all data' scope and the 'most recent data' scope at any time. This way the application can
use both scopes within the same execution at choose scope at SQL statement level.
32
© 2014 IBM Corporation
Work Load Management - Usage scenarios
• Workload Isolation:
Ensure that the workload of one DB2 subsystem doesn’t
monopolize the resources of a shared accelerator. A
development subsystem, attached to the same accelerator as a
production subsystem, should not be able to drain all accelerator
resources.
• Query Prioritization:
More important queries should be executed before and faster
than less important queries that are sent from the same DB2
subsystem against the accelerator.
33
© 2014 IBM Corporation
IBM DB2 Analytics Accelerator
Instrumentation
34
© 2013 IBM Corporation
IBM DB2 Analytics Accelerator Studio
35
© 2014 IBM Corporation
Moving Partitions with HPSS via IDAA Studio
36
© 2014 IBM Corporation
DB2 Command – DISPLAY ACCEL DETAIL
37
© 2014 IBM Corporation
Product
Feature
Description
Benefit
OM/PE
Reporting
Utilization of Accelerator (under/over)
ROI/Performance
Real time and short term
monitoring
Detailed perf reports (batch and online) for Accelerator and apps routed to the Accelerator –
takes advantage of Extended Insight
ROI/Performance
Assessment
Determine if workload can be accelerated
ROI
Cost comparison
Measure perf of query with & w/out Accelerator
ROI/Performance
Capture workload
Capture SQL workload (or queries) for designated time – can be used as input to OQWT for
tuning
ROI/Performance
Separate Queries
Isolate non-accelerated queries from workload
ROI/Performance
Best fit object
Determine best set of objects for query acceleration (both Static and dynamic SQL)
ROI/Performance
Best fit queries
Which queries are eligible, non-eligible or can be rewritten
ROI/Performance
What-if analysis
Expert recommendations with estimated cost savings (no actual execution)
ROI/Performance
Load non DB2 data
Ability to load non-DB2 data directly to Accelerator
Hybrid env
ROI for System z
P.i.T. load
Load to a specific point-in-time to fit business needs
ROI/reporting
Operational data
Ability to load to Accelerator w/out impact to applications
Avail/Perf/ROI
Accelerator
Admin/Mgt
Complete mgt of Accelerator and accelerated objects via familiar tool w/ ISPF
Usability
Refresh data?
Ability to use RUNSTATS to help determine if refresh of
Accelerator data is recommended
ROI/Performance
Chg Mgt
Automated reload of data to Accelerator after chg
Avail/Usability
Query Monitor
OQWT
Loader
Admin/OC
© 2014 IBM Corporation
Product Documentation Links
 Information Center for IBM DB2 Analytics Accelerator for z/OS V3.1
– http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.datatools.aqt.doc/welcome/topics/idaa_start.html
 Quick Start Guide
– http://publibfp.dhe.ibm.com/epubs/pdf/h1269820.pdf
 Installation Guide
– http://publibfp.dhe.ibm.com/epubs/pdf/h1269830.pdf
 Stored Procedure Reference
– http://publibfp.dhe.ibm.com/epubs/pdf/h1269840.pdf
 Analytics Accelerator Studio Users Guide
– http://publibfp.dhe.ibm.com/epubs/pdf/h1269850.pdf
 Getting Started Guide
– http://publibfp.dhe.ibm.com/epubs/pdf/h1269860.pdf
 Program Directory
– http://publibfp.dhe.ibm.com/epubs/pdf/i1950060.pdf
 Redbook – Optimizing DB2 Queries with IBM DB2 Analytics Accelerator for z/OS
– http://www.redbooks.ibm.com/redpieces/abstracts/sg248005.html?Open
 Redbook – Hybrid Analytics Solution using IBM DB2 Analytics Accelerator for z/OS V3.1
– http://www.redbooks.ibm.com/redpieces/abstracts/sg248151.html?Open
39
© 2014 IBM Corporation
40
40
© 2014 IBM Corporation
7/7/2015