DB2 - New England DB2 Users Group

Information Management for System z
DB2 for zLinux
presented to the
New England DB2 User’s Group
…a view from the lab
… from a performance guy
… who works with lots of customers
Berni Schiefer
Distinguished Engineer
Information Management Performance & Benchmarking
[email protected]
© 2014 IBM Corporation
Agenda
 The History
 DB2 Today
 DB2 Linux Unix Windows for System z
 Best Practices
2
© 2014 IBM Corporation
DB2: Proven to Tens of Thousands of Clients
3
© 2014 IBM Corporation
History of DB2 for zLinux
 In 2000, DB2 7.1 was one of the first software programs
certified for use on Linux for System Z
 The IBM DB2 and Linux for System Z development teams work together on
the DB2 on Linux for System Z stack
 Quality, reliability, and performance proven by hundreds of satisfied DB2 on
Linux for System Z customers
 In 2013 IBM delivered DB2 10.5 for Linux on System Z
 In Q1 2014 DB2 10.5 FP3 was delivered
More than
2000
4
running DB2
2003
2006
2009
2012
2015
© 2014 IBM Corporation
Choose from 2 Linux for System Z Distributions
 IBM is committed to open standards Linux
 DB2 is optimized to run on SuSE and Red Hat Linux distributions
 Favorite levels
 SLES 11 SP3
 RHEL 6.5
5
In 2014 we expect
both SLES12 and
RHEL7
© 2014 IBM Corporation
DB2 LUW : One Database Platform for All Business Applications
Advanced Application Functionality
Administration
NoSQL
Spatial
Analytics
HADOOP
Extensibility
MongoDB
API
Industry Standard
API’s
Development
Performance
Management
Oracle
Compatibility
JSON/Graph
Store
Data
Warehouse
Management
Workload
Management
Continual Data
Ingest/Access
Data Studio
Configuration
Management
Advanced
Recovery
Tools
Host
Connectivity
Advanced Data
Functionality
Temporal
Data
In Memory BLU
Acceleration
Pure XML
Fine grained
Security
Autonomic
Management
Intelligent
Compression
Enterprise Data Platform for all Business Applications
Massive scale /
24x7 OLTP Platform
Multi-tenant Enterprise
Platform
Active / Active 24x7
Massive Data Scale Warehouse
Deployment Options
Customer
Infrastructure
Optimised
Deployment
Available
on zLinux
DB2 for LUW : Three Overarching Tenants
Cost Effectiveness
Simplicity
Resiliency
Achieve leading
performance with fewer
resources.
Do more with less.
Simple to operate
Rich set of programming
interfaces and tools to
support agile development.
Keep data available and
secure despite component
failures, planned events,
disasters, intrusions.
“Before we made a final decision we benchmarked some of the key database
management systems. That includes Oracle, SQL Server and DB2. We ended up
choosing DB2 for several reasons. One was reliability, second was performance
and perhaps the most important factor was ease of use”
– Bashir Khan, Director of Data Management and Business Intelligence
Example Technologies & Proof Points
Cost Effectiveness
Simplicity
Resiliency
Achieve leading
performance with fewer
resources.
Do more with less.
Simple to operate
Rich set of programming
interfaces and tools to
support agile development.
Keep data available and
secure despite component
failures, planned events,
disasters, intrusions.
 Exceptional scalability
across scale-up and
scale-out architectures
 Developer-friendly and
ISV-friendly APIs
including noSQL & JSON
 Comprehensive Disaster
Recovery with DB2
HADR
 Industry Leading Data
Compression
 Rich application
semantics including time
travel query
 Online Operations,
Reorg Avoidance and
other
 Automatic Configuration
and Tuning
 Comprehensive Data
Security
 Comprehensive
Flash/SSD Exploitation
 Integrated Workload
Management
 Automatic Maintenance
DB2 10.5
DB2 10.5
Analytics at the Speed of Thought
Future Proof Versatility
DB2 10
Enhanced SQL & noSQL function
3x Query Performance Boost
50% Compression Boost
Temporal Query
DB2 9.7
noSQL Graph Store
Advanced Compression HADR Multiple Standby



TCO & Performance
 360O Monitoring
In memory
SQL based
Reorg Avoidance
Ease of Development
 XML extended to
DPF, MDC, RP
 “Currently Commited”
isolation
 Numerous SQL
enhancements



Reliability /Availability

HADR Advances
Active Standby
 Trap Resilience
 General Online Schema
Evolution

3x Query Performance
New Index Exploitation
Adaptive Compression
Multi-temp Storage
Real-time Warehousing
 Even

Temporal Query
98% SQL Compattibiltiy
Graph Store
RCAC
Reliability / Availability

pureScale Integration &
Enhancements
 WLM Enhancements
 Reorg Avoidance
 HADR Mutliple Standby
more performance !
Ease of Development
 Enhanced SQL Compatibility
 More noSQL Integration (MongoDB API)
Ease of Development





 Memory-optimized BLU Acceleration
 Workload Consolidation with pureScale
TCO & Performance





TCO & Performance

Reliability / Availability
 Rolling
Updates
 HADR with pureScale
 pureScale Active / active
DR
Enhancements
add/drop Member
 Other availability enhancements
 Online
Index on Expression
 What ?
– Allow indexes to be defined with an expression, eg.
CREATE INDEX i1 ON emp (UPPER(lastname), salary+bonus)
 Value Proposition
– Efficient execution of SQL statements with such expressions, eg.
SELECT * FROM emp WHERE UPPER(lastname) = ?
SELECT * FROM emp WHERE salary+bonus = ?
– Avoid the drawbacks of work-around (index on generated column)
• Space consumption of extra column
• Potential need to modify applications to reference the new column
Excluding NULL Keys from Indexes
 What ?
– Allow indexes to be defined so that NULL keys are excluded, eg:
CREATE [UNIQUE] INDEX i1(c1, c2) EXCLUDE NULL KEYS
 Value Proposition
– Support applications whose semantics require unique enforcement, but only where
keys are not NULL
– Storage savings ! (Avoid indexing NULLs if they are infrequently queried)
 Notes
– A “NULL key” is one where all key components are NULL
C1
C2
NULL
1
NULL
Excluded from
ENK index
Insert
Unique Constraint
C1
C2
1
NULL
NULL
NULL
Regular Index
ENK Index
Fail
Fail
Success
Extended Row Size
 What ?
– Allow tables to be defined with a row size which exceeds page size, eg:
CREATE TABLESPACE TS1 PAGESIZE 4K
CREATE TABLE T(c1 VARCHAR(4000), VARCHAR(4000)) IN TS1
 Value Proposition
– Support applications that require long row definitions
– Avoid a lengthy table redefinition to change pagesize
 Notes
– New maximum row length: 1,048,319 bytes
– Excess row data stored in a LOB
– Performance penalty when need to go ‘off page’ for a portion of row
• Usually OK – in most scenarios instances of long rows are rare
– If you expect long rows to be common, try to use a larger page size
REORG Enhancements
 Online inplace reorg support on a table using adaptive
compression
 Fastpath option for online inplace reorg to clean up
overflow records only
$ db reorg table T1 inplace cleanup overflows
 Reorg with RECLAIM EXTENTS can cleanup partially
empty extents for Insert Time Clustered tables
DB2 Supports Different SQL Dialects
Aggregated SQL Compatibility by Release
 Steady increase in compatibility over time
– More and more complex applications
 DB2 10.5 estimated to provide >98% statement compatibility
Data is based on DCW (Database Conversion Workbench) DB2 reports
Background : What is JSON ?
 Simple format for data
exchange
–
–
–
–
{
"firstName": "John",
"lastName" : "Smith",
"age"
: 25,
"address" :
{
"streetAddress": "21 2nd Street",
"city"
: "New York",
"state"
: "NY",
"postalCode" : "10021"
},
"phoneNumber":
[
{
"type" : "home",
"number": "212 555-1234"
},
{
"type" : "fax",
"number": "646 555-4567"
}
]
Self-describing, schema-less
Very simple (eg. tag:value format)
Human readable
Based on JavaScript, initially targeted for
web applications,… but,…
 Use is rapidly spreading
– The data interchange format for the Web
• JavaScript is very popular in mobile and
systems-of-engagement applications
– Analytics
• Eg. An organization stores a large quantity
of web statistics stored as JSON
documents, and wants to perform analytics
}
Typical JSON Open Source Datastore
Attributes
 Optimized for high speed data ingest
 Relaxed/absent ACID properties
– Logging is often turned off or done asynchronously to improve performance
– “Fire and forget” inserts
• Applications include checking logic to verify update occurred
– No concept of commit or rollback; each JSON update is independent
• Applications implement compensation logic to update multiple documents with
ACID properties
 Data is sharded for scalability
– Shards are replicated asynchronously for availability
– Queries to replica nodes can return back-level data sometimes
 JSON documents are stored in “collections”
– No “join” across collections, requires “in application” joins
 Limited options for security, temporal, geo-spatial,...
DB2 JSON Datastore : Concept & Motivation
•
What : Built-in JSON support in DB2 !
•Including support for popular noSQL JSON
APIs
• Why
: Preserve mature DBMS features;
Leverage existing skills and tools
•Multi-statement Transactions
•ACID
•Extreme scale, performance and high
availability
•Comprehensive Security
•Management/Operations
•…
The Best of Both Worlds Agility with a Trusted Foundation
DB2 JSON Datastore : Features
Applications
Java
PHP
NodeJS
 Binary formatted JSON stored in
the database (in LOBs)
BSON Wire Protocol
 Btree indexing to JSON elements AIM Developed MongoDB Wire Protocol
NoSQL JSON Wire Listener
for fast query processing
 Java API and command line
Java Apps
 Optional “Fire-forget” inserts
JSON
JSON API
 Supports transactions
Command Shell
 Smart query re-write
JDBC Driver
 DB2 ecosystem of tools
SQL via DRDA
inlined
 Extend support to more
LOBs
applications and developers
DB2
item
www.ibm.com/developerworks.com
and search for “DB2 JSON”
Key
Shopping Cart
1
<binary JSON>
2
<binary JSON>
3
<binary JSON>
“item”:”camera”
IBM System zEC12 with Linux and DB2
Yet Another High-performance DB2 Platform
DB2 is an ideal data server on the new System zEC12. Customers know there are
compelling availability and server virtualization/consolidation opportunities on System.
With the IBM System zEC12, System z becomes an even more attractive platform for
deploying DB2..
19
Data Center Virtualization
with System z, z/VM & Linux
DB2 10
High Availability
with System z, z/VM & Linux
DB2 10 with HADR
Business Intelligence
with System z & Linux
Modernization
with System z & Linux
Cognos & DB2 10
DB2 10
© 2014 IBM Corporation
zEC12 Continues the Mainframe Heritage
5.2
GHz
6000
5.5
GHz
4.4
GHz
5000
MHz
4000
3000
1.7
GHz
2000
1000
300M
Hz
420
MHz
1997
G4
1998
G5
550
MHz
770
MHz
1.2
GHz
0
20
1999
G6
2000
z900
2003
z990
2005
z9 EC
2008
z10 EC
2010
z196
2012
zEC12
© 2014 IBM Corporation
The Evolution of Mainframe Generations
zEC12
21
© 2014 IBM Corporation
DB2 Database BI Workload on z10/z196/zEC12
 DB2 POPS workload
– Ten selected complex database warehouse queries
• Measured high hit / warm state
 Configuration
– 16 processors (IFLs)
– 128 GB of main memory
– DB2 10.1 for Linux on System z
– Exploiting intra-query parallelism
– Not I/O constrained
22
© 2014 IBM Corporation
15X
improvement
23
© 2014 IBM Corporation
DB2 Performance for Complex BI Queries
 30 percent more throughput comparing zEC12 versus z196
 Close to 2x comparing zEC12 versus z10
DB2 BI Queries
Overall run time (lower is better)
25
Time in sec
20
z10
z196
zEC12
15
10
5
0
Testcase
24
© 2014 IBM Corporation
So What Is DB2 for zLinux?
(and what is it not)
 DB2 LUW for zLinux is
–
–
–
–
“JUST” DB2 LUW
99.9%+ the same code
99.9%+ the same look/feel
99.9%+ the same skills
 DB2 LUW for zLinux has 98%+ Oracle Compatibility
– PL/SQL
– Datatypes
 DB2 LUW is not identical to DB2 for zOS, but
– Common client infrastructure
• Jcc/odbc/cli
• Perl/php/python/ruby/…
– Optim Data Studio tools (Data Studio, OPM, OPWT)
– Exceptionally similar DDL/DML/DCL
25
© 2014 IBM Corporation
DB2 for z/OS vs DB2 for LUW (cont.)
(not exhaustive, features may be missing, doesn’t over DB2 for zOS v11)
DB2 10 for z/OS and DB2 10.1 Linux, Unix & Windows
z
c
o
m
m
o
n
l
u
w
26
Multi-row INSERT, FETCH & multi-row cursor UPDATE, Dynamic Scrollable Cursors, GET DIAGNOSTICS, Enhanced
UNICODE SQL, join across encoding schemes, IS NOT DISTINCT FROM, VARBINARY, FETCH CONTINUE,
Enhanced MERGE, SELECT from MERGE, routine versioning, timestamps w/timezone
Inner and Outer Joins, Table Expressions, Subqueries, GROUP BY, Complex Correlation, Global
Temporary Tables, CASE, 100+ Built-in Functions including SQL/XML, Limited Fetch, Insensitive
Scroll Cursors, UNION Everywhere, MIN/MAX Single Index, Self Referencing Updates with
Subqueries, Sort Avoidance for ORDER BY, and Row Expressions, 2M Statement Length, GROUP
BY Expression, Sequences, Scalar Fullselect, Materialized Query Tables, Common Table
Expressions, Recursive SQL, CURRENT PACKAGE PATH, VOLATILE Tables, Star Join Sparse
Index, Qualified Column names, Multiple DISTINCT clauses, ON COMMIT DROP, Transparent
ROWID Column, Call from trigger, statement isolation, FOR READ ONLY KEEP UPDATE LOCKS,
SET CURRENT SCHEMA, Client special registers, long SQL object names, SELECT from INSERT,
UPDATE or DELETE, INSTEAD OF TRIGGER, Native SQL Procedure Language, BIGINT, file
reference variables, XML, FETCH FIRST & ORDER BY in subselect & fullselect, caseless
comparisons, INTERSECT, EXCEPT, not logged tables, OmniFind, spatial, range partitions, data
compression, session variables, DECIMAL FLOAT, optimistic locking, ROLE, TRUNCATE, index &
XML compression, created temps, inline LOB, administrative privileges, implicit cast, date/time
changes, currently committed, moving sum & average, index include columns, row and column access
control, time travel query
Updateable UNION in Views, GROUPING SETS, ROLLUP, CUBE, more Built-in Functions, SET CURRENT
ISOLATION, multi-site join, MERGE, MDC, XQuery, XML enhancements, additional data type (array, row, cursor),
global variables, even more vendor syntax, temp table compression, MODULEs
© 2014 IBM Corporation
So What Is the Same? (  )
 Editions
– Advanced Enterprise Server Edition (AESE)
– Enterprise Server Edition
 PVU
– 100 PVU/core (IFL) for older generations
– 120 PVU/core for new systems
 Core Features
– (Adaptive )Compression
– HADR
• Including multiple standby and reads on secondary
– WLM (workload managemet)
– Database Partitioning Feature
• MPP data warehousing
–
–
–
–
–
27
Federation / Replication
LBAC/FGAC
LDAP /
Flashcopy Manager
….
© 2014 IBM Corporation
So What Is Different / Missing (  )
 Editions
– Advanced Workgroup Server Edition
– Workgroup Server Edition
– Express/Express-C Edition
 PVU
– PVU-less licenses
 Features Unavailable
– DB2 pureScale (needs GPFS, Mellanox IB/RoCE /w uDAPL from STG)
• HADR or DB2 data sharing for zOS are alternatives
–
–
–
–
DB2 BLU Acceleration
Encryption (3rd party tool (Vormetric) not available)….
Text search
Hypervisor Edition
• Can build your own image using the IWD toolkit
– Kerberos plug-in
– Various GUI tools that are normally client-side
• Replication Center , Data Studio etc.
28
© 2014 IBM Corporation
So What Is Unique?
 Not so much in the core DB2 code
– First of all, DB2 is more than 99% the same code across all platforms
• x64 Linux, pLinux, zLinux, AIX, Solaris, HP-UX, Windows
– Linux for System z is a complete Linux implementation
• Pro;
• Con:
Reusable skills, common function
Difficult to Differentiate
 Core platform values provide value transparently
– Inherent reliability creates unique opportunities
• E..g “virtual HA” (2 LPARs with HADR to protect against software failures
– Optimized virtualization permits high consolidation ratio
– Enormous scale-up potential reduces need for scale-out
 Exception:
– Code to have an up but idle DB2 “go to sleep” to help z/VM
conserve/consolidate resources
– db2set DB2_MIN_IDLE_RESOURCES
– Designed for zLinux, available on all Linux
29
© 2014 IBM Corporation
High Value Scenarios for DB2 for zLinux
 Consolidation
– Bring many small, typically lightly loaded databases together into a highly
consolidated and virtualized environment under z/VM
 Migrate unhappy Oracle on zLinux customers
– Oracle may be pushing them to Exadata
– Oracle may be charging outrageous prices
– Migration is typically relatively easy/trivial
• Especially if using a packaged app that already supports DB2
 Use DB2 as the persistent data store inside another SWG solution
– Websphere Portal Server
– Cognos 10 content server
– … many more
 A regular database for OLTP or query processing
30
© 2014 IBM Corporation
Best Practices (System config)
 Use a modern System z machine
– The System z processor has caught up a lot on a per core (IFL) basis
 If you want to use z/VM (most do) remember
– z/VM is not “free” (resource-wise) – especially wrt. memory
• The more IFLs the more memory you need to reserve for z/VM
– Ensure you have current maintenance for z/VM
• Every test we do “finds” interesting things.
– Give DB2 dedicated processors where possible
 If your database does disk I/O (most do) remember
– These days you care less about storage space
• You care about storage performance (random IOPS, sequential MB/sec)
– Avoid ECKD disk (it is not a good fit for DB2), use SCSI disks
• With SCSI you can disable file system caching and keep memory for DB2
– Follow standard zLinux best practices
31
© 2014 IBM Corporation
Best Practices (Linux)
 Ensure you are entitled to Linux support
– You can choose to get support from IBM or
from a Linux distribution company
• Redhat
• SUSE
 Use a current Linux version
– SLES 11 (ideally SP3) or RHEL6 (ideally RHEL6.5)
– Consult with the zLinux performance team on specifics
 Apply standard Linux tuning best practices for DB2
– Virtual memory tuning
• vm.Swappiness: 0
• vm.Dirty_ratio: 10
• vm.Dirty_background_ratio: 5
– General OS tuning
• http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.qb.server.doc/
doc/c0057140.html
32
© 2014 IBM Corporation
Best Practices (DB2 software/sizing)
 Use current DB2 LUW software
– I know the tendency on zOS is to use n-1 VERSIONS
– Linux is different
 DB2 10.5 has been well-received (across platforms)
– Most customers have deployed DB2 10.1 FP3
– DB2 10.5 FP3 is GA
• has cumulative maintenance from 9.7 and 10.1 included
 Don’t overthink the sizing. First order approximations
– DB2 for zOS = DB2 LUW
– DB2 LUW = Oracle
– DB2 LUW on Power7 = DB2 LUW on zLinux on a z196
 KISS principle
– Resist the urge to over-specify all possible options
• Just because they exist doesn’t mean you have to use them
33
© 2014 IBM Corporation
Best Practices (DB2 configuration)
 Use DB2 automatic storage
– A modest number of storage paths that map sensibly to the SAN storage
 Minimize the use of distinct page sizes
– 8K is a good default (no more than 2)
– Specify the smaller one at create database time for the catalog
 Minimize the number of bufferpools
– 1 for data/index, 1 for temp is usually sufficient
 Go ahead and use plenty of tablespaces
– Often helpful to split data/index/LOB (esp LOB) into separate tablespaces
– No need to put EACH table in its own tablespace (common with DB2 for zOS)
 Use the Autonomics
– Autoconfigure & STMM
• But give DB2 a budget via instance_memory or database_memory
 Compression
– Turn on adaptive compression at create table time for top ~50 large tables
• Also index compression.
34
© 2014 IBM Corporation
Best Practices (Tools)
 There is a strong tools portfolio
– Many are free
– Even more are included in Advanced Enterprise Server Edition (AESE)
 Optim Data Studio
– Database Administration
– Developer
 Optim Performance Manager (OPM)
– With Extended Insight for application server environments (e.g. WAS)
 Optim Query Workload Tuner (OQWT)
– Integration with OPM
– Next generation of “DB2 design advisor”
 Optim Capture/Replay
– Realistic Pre-production
35
© 2014 IBM Corporation
InfoSphere Optim Solutions for Managing Performance
Identify, diagnose, solve and prevent performance problems
• Alert of potential problems
• Visual quick scan of complex environment
• Drill-down into problem detail and related context
• Analyze captured data
Network
1. Identify
2. Diagnose
Users
• Optim Performance Manager
• OMEGAMON XE DB2 Performance
Expert
•Optim Configuration Manager
• Optim Performance
Manager Extended Edition
Application
Servers
4. Prevent
DBMS & OS
• Monitor and analyze historical data trends for planning
• Auto-manage workloads
• Optim Query Capture Replay
• All the other tools
36
• Optim Query Workload Tuner
•Optim Configuration Manager
3. Solve
• Receive expert advice for problem resolution
• Correct the problem (SQL, database)
© 2014 IBM Corporation
Customers using zLinux with DB2 LUW:
 Nationwide Insurance
 Peter Hahn (Germany)
 Russian Hydrometeorological Research
Institute – World Data Center (Russia)
 Wessels+Müller AG (Germany)
 White Cube – z114
 Total System Services (TSYS)
 Boston University
 Calculo S.A. (Spain)
 University of AZ College of Pharmacy –
new and cool…….
37
© 2014 IBM Corporation
Summary
 DB2 LUW is a mature product on zLinux
– Many years of development/testing/deployment
 DB2 LUW on zLinux is full function
– Nearly every capability is available on zLinux
– Yes, I know there are 2 visible exceptions
 DB2 LUW on zLinux is high performance
– Regular testing on latest System z hardware (OLTP and BI)
 DB2 10.5 is production ready and available on zLinux
– DB2 10.5 FP3 is available and production ready
 100’s of satisfied customers
– continued strong interest
38
© 2014 IBM Corporation
39
© 2014 IBM Corporation
40
© 2014 IBM Corporation

Download Report