Transcript IBM Smart Analytics Optimizer Version 2

IBM DB2 Analytics Accelerator
Speed analytics delivery
...powered by Netezza technology
Osama Alkatib, [email protected]
System z Information Integration IT Specialist
1
© 2012 IBM Corporation
Information Management
Smart Business Analytics on System z
DB2 Analytics Accelerator
Accelerating decisions to the speed of business
Blending System z and Netezza
Get more insight from your data
technologies to deliver unparalleled,
•
Fast, predictable response times
for “right-time” analysis
•
Accelerate analytic query
response times
•
Improve price/performance for
analytic workloads
•
Minimize the need to create data
marts for performance
•
Highly secure environment for
sensitive data analysis
•
Transparent to the application
mixed workload performance for
complex analytic business needs.
© 2012 IBM Corporation
Information Management
Smart Business Analytics on System z
OLTP vs. Analytics – Examples
3
OLTP - “Transactional”
Transactional analytics:
Operational BI
Deep analytics: Warehouse / BI
/ OLAP
Withdrawal from a bank account
using an ATM
Approve request to increase credit
line based on credit history and
customer profile
Regular reporting to central bank
– sum of transactions by account
Buying a book at Amazon.com
Propose additional books based
on similar purchases by other
customers
Which books were best-sellers in
Europe over the last 2 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
cheapest and most reliable carrier
on demand
Trend of printers sold in emerging
countries versus established
markets.
© 2012 IBM Corporation
Information Management
Smart Business Analytics on System z
Performance & Savings
DB2 Only
Query
Query 1
Query 2
Query 3
Query 4
Query 5
Query 6
Query 7
Query 8
Query 9
Total
Total
Rows
Rows
Reviewed Returned
2,813,571
853,320
2,813,571
585,780
8,260,214
274
2,813,571
601,197
3,422,765
508
4,290,648
165
361,521
58,236
3,425.29
724
4,130,107
137
DB2 with
IDAA
Times
Faster
Queries run faster
• Save CPU resources
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
1,908
1,644
760
816
58
530
780
1,320
13
• People time
• Business opportunities
Actual customer results, October 2011
DB2 Analytics Accelerator: “we had this up and running in days
with queries that ran over 1000 times faster”
DB2 Analytics Accelerator: “we expect ROI in less than 4 months”
Accelerating decisions to the speed of business
© 2012 IBM Corporation
Information Management
Smart Business Analytics on System z
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
IBM
DB2
Analytics
Accelerator
Superior
performance on
analytic queries
z/OS on
System z
Netezza
5
© 2012 IBM Corporation
Information Management
Smart Business Analytics on System z
Accelerator Data Load
Accelerator
DB2 for z/OS
Table A
Table B
Unload
CPU
USS Pipe
FPGA
Memory
Table C
Part 2
Unload
USS Pipe
Table D
Part 1
Part 2
.
.
.
.
.
.
.
.
.
Coordinator
IDAA Administrative Stored Procedures
IDAA
Studio
Part 1
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
• Trickle-feed update under discussion
© 2012 IBM Corporation
Information Management
Smart Business Analytics on System z
IBM DB2 Analytics Accelerator
Query off-load applicability
IDAA is based on Netezza which supports rich set of SQL and data types
• BI tools such as Cognos have been running on Netezza for years and will run on
IDAA as well
Due to very large number of query types and SQL functions, there are some restrictions.
Here are the key restrictions:
• No static SQL
• Not all DB2 functions, such as HEX, POSITION, SIN, ...
• No user defined functions
• No correlated table expressions or recursive correlated table expressions
• No correlated subquery in the SELECT list
• No UTF-16 and MIXED/DBCS EBCDIC
• No multiple encoding schemes in the same statement
• Not all DB2 special registers: CURRENT PATH, SERVER, SQLID, SCHEMA,
APPLICATION ENCODING SCHEME
• Not all DB2 data types such as LOBs, ROWID, XML.
Note: These restrictions could be lifted in a future release
© 2012 IBM Corporation
Information Management
Smart Business Analytics on System z
Query Execution Process Flow
Application
Interface
Optimizer
Heartbeat
DB2 for z/OS
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
IDAA
Queries executed without IDAA
Queries executed with IDAA
Heartbeat (IDAA availability and performance indicators)
8
© 2012 IBM Corporation
Information Management
Smart Business Analytics on System z
Workload-Optimized Query Execution
DB2 for z/OS and
DB2 Analytics Accelerator
 Dynamic decision for most
efficient execution platform
OLTP-like query
Light BI Query
Heavy BI Query
9
User control and DB2 heuristic
Light ODSquery
 Single and unique system for
mixed query workloads
DB2 Native
Processing
 New special register QUERY
ACCELERATION
– NONE
– ENABLE
– ENABLE WITH FAILBACK
 New heuristic in DB2
optimizer
 Combines the strengths of
both System z and Netezza
Optimized processing
for BI Workload
 Merging operational and
data warehouse into a
single optimized
environment
© 2012 IBM Corporation
Information Management
Smart Business Analytics on System z
IBM DB2 Analytics Accelerator V2 Product Components
Netezza
Technology
zEnterprise
CLIENT
Data Studio
Foundation
DB2 Analytics
Accelerator
Admin Plug-in
Network
Primary
BladeCenter
OSA-Express4
10 GbE
10Gb
Backup
Users/
Applications
10
Data Warehouse application
DB2 for z/OS enabled for IBM
DB2 Analytics Accelerator
IBM DB2
Analytics
Acelerator
© 2012 IBM Corporation
Information Management
Smart Business Analytics on System z
TM
IDAA powered by Netezza 1000 Appliance
Slice of User Data
Swap and Mirror partitions
High speed data streaming
High compression rate
Disk Enclosures
SMP Hosts
EXP3000 JBOD Enclosures
12 x 3.5” 1TB, 7200RPM, SAS (3Gb/s)
max 116MB/s (200-500MB/s compressed data)
e.g. TF12:
8 enclosures → 96 HDDs
32TB uncompressed user data (→ 128TB)
IDAA Server
SQL Compiler, Query Plan, Optimize,
Administration
2 front/end hosts, IBM 3650M3 or 3850X5
clustered active-passive
2 Nehalem-EP Quad-core 2.4GHz per host
Snippet BladesTM
(S-Blades, SPUs)
Processor &
streaming DB logic
High-performance database
engine streaming joins,
aggregations, sorts, etc.
e.g. TF12: 12 back/end SPUs
(more details on following charts)
© 2012 IBM Corporation
Information Management
Smart Business Analytics on System z
Netezza 1000 Appliance Scalability
1
10
.......
1000-3
1000-6
1000-12
1000-24
1000-36
1000-48
1000-72
1000-96
1000-120
Cabinets
1/4
1/2
1
2
3
4
6
8
10
Processing
Units
24
48
96
192
288
384
576
768
960
Capacity
(TB)
8
16
32
64
96
128
192
256
320
Effective
Capacity
(TB)*
32
64
128
256
384
512
768
1024
1280
Current IDAA Platforms
Future
Predictable, Linear Scalability throughout entire family
Capacity
= User Data space
Effective Capacity = User Data Space with compression
*: 4X compression assumed
© 2012 IBM Corporation
Information Management
Smart Business Analytics on System z
Connectivity Options
Multiple DB2 systems can connect to a single IDAA
A single DB2 system can connect to multiple IDAAs
Multiple DB2 systems can connect to multiple IDAAs
Full flexibility for DB2 systems:
Better utilization of IDAA resources
Scalability
High availability
13
•
•
•
•
•
•
residing in the same LPAR
residing in different LPARs
residing in different CECs
being independent (non-data sharing)
belonging to the same data sharing group
belonging to different data sharing groups
© 2012 IBM Corporation
Information Management
Smart Business Analytics on System z
Creating the Hybrid Data Server
Combining the best transaction system with the best analytics system
Best in OLTP and
Transactional Analytics
Data Mart Data Mart Data Mart
Industry recognized leader in
mission critical transaction
systems
Data Mart Consolidation
Best in Deep Analytics
Transaction Processing
Systems (OLTP)
Proven appliance leader in high
speed analytic systems
Best in Consolidation
Transactional Analytics
Deep Analytics
z/OS:
Netezza:
Recognized leader in
transactional workloads
with security, availability
and recoverability
Recognized leader in
cost-effective high
speed deep analytics
Unprecedented mixed workload
flexibility and virtualization
providing the most options for
cost effective consolidation
Together:
14
Destroying the myth that transactional and
decision support workloads have to be on
separate platforms
© 2012 IBM Corporation
Information Management
Smart Business Analytics on System z
Free Workload Evaluation/Sizing
Report for a first assessment:
 Acceleration potential for
 Queries
 Estimated time
 Customer
 CP cost
 Collecting information from dynamic
statement cache, supported by step-by-step
instruction and REXX script (small effort for
customer)
 Uploading compressed file (up to some MB)
to IBM FTP server
 IBM / Center of Excellence
 Importing data into local database
 Quick analysis based on known DB2
Analytics Accelerator capabilities
1
Customer
Database
Documentation
and REXX procedure
2
Data package
(mainly unload
data sets)
Pre-process and
load
3
IBM lab
Database
Quick Workload
Test Tool
Report
Assessment
© 2012 IBM Corporation
Information Management
Smart Business Analytics on System z
IDAA - Fast Time to Value
Faster
Time to
Deliver
+
Productivity
Benefits
+
Reduce
MIPS & CPU
Consumption
+
Delay
Capacity
Upgrades
+
Reduce
ISV SW
Costs
=
Faster
Time To
Value
Faster Time to Deliver to Business
Delay Capacity Upgrades




 Offload eligible DB2 workload
 Reduce Storage Costs
– 1/3 less required Indexes of target tables
– Direct savings of eliminated copies
 Avoid/Reduce ISV SW Costs
 Avoid/Reduce Maintenance Costs
Eliminate/Simplify ETL
Eliminate Copies of Data
Centralize Data
Access current data using current structures
– Avoid DataMart/DataWarehouse Design
Delays
 Dramatically Reduce Development Costs
 Create New Business Opportunities
Productivity Benefits
 Reduced IT Operational Costs due to reduced
complexity
 Enable “Train of Thought” business processes
 Over time, once initial IDAA environment is
setup, DBA time freed up for additional projects
– No indexes = less DBA time
– Less database and storage administration
– Less overall application tuning
Improved Service Levels on Key
Application and Reporting Environments
 Fast, predictable response times for “right-time”
analysis
 Accelerated analytic query response times
 Improved price/performance for analytic
workloads
 Highly secure environment for sensitive data
analysis
 Transparent to the application
© 2012 IBM Corporation
Information Management
Smart Business Analytics on System z
© 2012 IBM Corporation
17
4/13/2015
Information Management
Smart Business Analytics on System z
Fast Time to Value
 IBM DB2 Analytics Accelerator (Netezza 1000-12)
 Production ready - 1 person, 2 days
 Table Acceleration Setup … 2 Hours
–
–
–
–
–
DB2 “Add Accelerator”
Choose a Table for “Acceleration”
Load the Table (DB2 copy to Netezza)
Knowledge Transfer
Query Comparisons
 Initial Load Performance …
400 GB “Loaded” in 29 Min
570 million rows (Loads of 800GB to 1.3TB/Hr)
 Actual Query Acceleration … 1908x faster
2 Hours 39 Minutes to 5 Seconds
 CPU Utilization Reduction
35% to ~0%
Actual customer results, October 2011
© 2012 IBM Corporation
Information Management
Smart Business Analytics on System z
What is the value?
 Quickly delivers analytics to operational applications
 High speed analytics where the data is generated
 Enables train-of-thought analysis with high speed complex queries
 Substantially reduces operational costs by removing the need for complex query
tuning
 Creates a highly secure environment for highly sensitive analysis (EAL5)
 Speeds batch reporting cycle to meet stricter SLAs
 Enables decision makers to perform business analysis they never dared in the
past
 Enables query acceleration across multiple applications and systems
 Capitalizes on DB2 skills and certification removing the need to learn or convert
to another SQL environment
© 2012 IBM Corporation