db2 9.7 overview
TRANSCRIPT
© 2009 IBM Corporation
ibm.com/db2/labchats
DB2 9.7: Technology Preview 6 May 2009
Tim VincentChief Architect, DB2 for Linux, UNIX, Windows
2
© 2009 IBM Corporation
DisclaimerTHE INFORMATION CONTAINED IN THIS PRESENTATION IS PROVIDED FOR
INFORMATIONAL PURPOSES ONLY. ALTHOUGH EFFORTS WERE MADE TO VERIFY THE COMPLETENESS AND
ACCURACY OF THE INFORMATION CONTAINED IN THIS PRESENTATION, IT IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED.
IN ADDITION, THIS INFORMATION IS BASED ON IBM’S CURRENT PRODUCT PLANS AND STRATEGY, WHICH ARE SUBJECT TO CHANGE BY IBM WITHOUT NOTICE.
IBM SHALL NOT BE RESPONSIBLE FOR ANY DAMAGES ARISING OUT OF THE USE OF, OR OTHERWISE RELATED TO, THIS PRESENTATION OR ANY OTHER DOCUMENTATION. NOTHING CONTAINED IN THIS PRESENTATION IS INTENDED TO, OR SHALL HAVE THE EFFECT OF CREATING ANY WARRANTY OR REPRESENTATION FROM IBM (OR ITS AFFILIATES OR ITS OR THEIR SUPPLIERS AND/OR LICENSORS); OR ALTERING THE TERMS AND CONDITIONS OF THE APPLICABLE LICENSE AGREEMENT GOVERNING THE USE OF IBM SOFTWARE.
Performance is based on measurements and projections using standard IBM benchmarks in a controlled environment.
The actual throughput or performance that any user will experience will vary depending upon many factors, including considerations such as the amount of multiprogramming in the user's job stream, the I/O configuration, the storage configuration, and the workload processed.
Therefore, no assurance can be given that an individual user will achieve resultssimilar to those stated here.
2
3
© 2009 IBM Corporation
Themes Resource Optimization
– Best performance with most efficient utilization of available resources
Ongoing Flexibility – Allow for continuous and flexible change management
Service Level Confidence– Expand your critical workloads confidently and cost effectively
XML Insight– Harness the business value of XML
Break Free with DB2– Use the database server that gives you the freedom to choose
Balanced Warehouse– Create table ready warehouse appliance with proven high performance
3
4
© 2009 IBM Corporation
Resource Optimization
• Compression
• Storage I/O optimization
• Ease of storage management
• HA and/or DR utilization
4
5
© 2009 IBM Corporation
– Multiple algorithms for automatic index compression
– Automatic compression for temporary tables
– Compression of large objects and XML
– Replication of Compressed Tables
Unique in the industry
Unique in the industry
Table
Order By Order By
Temp Table Temp
Compression Improvements
Log db2ReadLog API
Dictionary Compressed user data in logsUncompressed user data in logs
5
6
© 2009 IBM Corporation
Simple Index Compression Tests - Elapsed Time
49.24
83.99
53.89
49.12
68.3
44.07
0 10 20 30 40 50 60 70 80 90
Simple Select
Simple Insert
Simple Update
Seconds
Without Index Compression With Index Compression
Simple Index Compression Tests - CPU Analysis
34.5
34.8
16.2
20.8
23.6
33.9
16.7
17.5
1.6
2.0
2.6
2.5
37.1
36.4
49.1
46.3
48.2
45.0
11.7
11.4
33.3
30.9
25.9
18.5
0% 20% 40% 60% 80% 100%
Select: Base
Select: Ixcomp
Insert: Base
Insert: Ixcomp
Update: Base
Update: Ixcomp
Machine Utilization
user system idle iowait
Index Compression: Measurements Index Compression Space Savings
31%
55%
16%
24%
20%
57%
50%
0% 10% 20% 30% 40% 50% 60% 70%
TD-EDW
SPAR
DDMV
SAP-ssqj
SAP-sd
SAP-bw
TPC-DS
Type
of D
atab
ase
Percentage Compressed
Average 36%
22% Faster
23% Faster
Runs As fast
* Higher is better
* Lower is better
Index compression uses idle CPU cycles and idle cycles spent waiting for I/O to compress & decompress index data
When we are not CPU bound, we are able to achieve better performance in all selects, inserts and updates
6
7
© 2009 IBM Corporation
Space Savings for TPC-DS Queries with Temp Compression
78.3
50.2
0.0
20.0
40.0
60.0
80.0
100.0
Without Temp Comp Total Bytes Stored With Temp Comp Bytes Stored
Size
(Gig
abyt
es)
Temp Compression: MeasurementsElapsed Time for TPC-DS Queries with Temp
Compression
183.98175.56
120.00
130.00
140.00
150.00
160.00
170.00
180.00
190.00
200.00
Without Temp Comp Runtime With Temp Comp Runtime
Min
utes
TPC-DS CPU Analysis for Temp Compression
39.2646.50
22.1914.61
0.00
20.00
40.00
60.00
80.00
Baseline Index Compression
I/O Wait
User CPU
56% lessspace
5% Faster
Effective CPU
Usage
* Lower is better * Lower is better
•swg-db2kit
7
8
© 2009 IBM Corporation
New tablespace format to allow automated extent remapping
Allow extents that are not assigned to any object (eg. table, index) to be used by other tablespaces
ALTER TABLESPACE REDUCE … XXX | MAX
All new tablespaces will have this format
Storage in an MDC table is tracked through a ‘block map’– which extents have data and which don’t– When a block is emptied the storage remains with the table and is available for later
reuse by that table
New option on reorg table command to not reorg the table but reclaim these empty blocks/extents
REORG TABLE <mdc table> RECLAIM EXTENTS ON [table partition clause] ALLOW WRITE ACCESS | ALLOW READ ACCESS | ALLOW NOACCESS
Simple Space Reclamation
8
9
© 2009 IBM Corporation
Automatic Storage Migration Support ALTER DATABASE command for non-auto AS database
Allow existing tablespaces to grow into auto storage containers ALTER TABLESPACE <table_space_name>
MANAGED BY AUTOMATIC STORAGE Existing containers can no longer be altered.
Support redirected tablespace restore to AS tablespace RESTORE DB <dbname> REDIRECT SET TABLESPACE CONTAINERS FOR <tablespaceID> USING AUTOMATIC STORAGE
REBALANCE support after a new path is added to the database– Allows existing tablespaces to use new path
Ability to DROP a path from an automatic storage database. – Can be used to migrate to new containers
9
10
© 2009 IBM Corporation
Scan Sharing
User 1 Scans Data
User 2 Scans Data
Buffer Pool
Start scan 2 at current
position of scan 1
Reread only missing pages
10
Scan Sharing for DB2
© 2006 IBM Corporation11© 2009 IBM Corporation
Scan Sharing Performance Test TPCH Q1 : CPU Intensive, Slow Query On Lineitem Table Using A Table Scan TPCH Q6 : IO Intensive, Fast Query On Lineitem Table Using A Table Scan
Test Scenario : Queries executed in parallel in the following sequence
Results : 34% Improvement In End to End Timing
Q1
Q6
Q1
Q6
30Secs
60 Secs
90 Secs
User System Idle IO Wait0
10
20
30
40
50
60
70
% T
ime
Spen
t
Base ScanSharing
CPU Usage
Tim e
0
1
2
3
4
Mill
ion
sC
um
ilativ
e R
ea
ds
Sc a n Sha ring B a s e
Reads on a disk: 42% Reduction
11
© 2009 IBM Corporation12 © 2009 IBM Corporation
DB2 9.7 Delivers Even Faster OLTP with Statement Concentrator
DB2 9.7– Optionally replace literals with parameter markers
• Increases section sharing and reduces compilation– Reduces number of statements to be compiled
SELECT BALANCE WHERE ACCOUNT_ID = 12345
SELECT BALANCE WHERE ACCOUNT_ID = 11111
SELECT BALANCE WHERE ACCOUNT_ID = 54321
SELECT BALANCE WHERE ACCOUNT_ID = 12121
Compile
ExecuteSELECT BALANCE WHERE ACCOUNT_ID = ?
12
13
© 2009 IBM Corporation
Primary
Read/Write Clients Read-Only Clients
HADR with Reads on Standby
StandbyClients Clients
HADR Standby database is functional not only for high availability and disaster recovery purposes but also for running read-only workloads.
Can offload reporting, DSS/BI workloads to Standby
Run concurrent read-only workloads with minimal impact to Standby system’s high availability and disaster recovery role.
Increases capacity of the HADR system
DB Logs
HADR Reads on Standby
13
14
© 2009 IBM Corporation
Ongoing Flexibility
Schema Evolution Data Life Cycle Warehouse Growth Transportable Tablespaces
14
15
© 2009 IBM Corporation
Schema Evolution Relax the object dependency model
– Allow changes that affect dependent objects to succeed– Automatically revalidate dependent objects
• ALTER TABLE, ALTER COLUMN, DROP COLUMN, RENAME COLUMN • CREATE OR REPLACE ALIAS, FUNCTION, NICKNAME, PROCEDURE,
SEQUENCE, TRIGGER, VARIABLE, VIEW • DROP FUNCTION, NICKNAME, PROCEDURE, SEQUENCE, TABLE, TRIGGER,
TYPE, VARIABLE, VIEW, TABLE
Extend to support– RENAME COLUMN– Support CREATE OR REPLACE syntax for views, functions, triggers,
etc.– Allow additional data type changes via ALTER COLUMN
• Between any types SQL runtime can cast
15
16
© 2009 IBM Corporation
Online Table MoveADMIN_MOVE_TABLE Move data in an existing table to a new table object Source table remains online: both read (select) and write (IUD)
operations Final phase renames the tables: target table will have the original table
name
Use Cases Online table compression Online REORG or Redistribute Online conversion to LARGE tablespaces Move data/index/long data to new/different tablespaces Support for limited schema evolution:
– Add or remove columns, change column datatypes– Add/change MDC dimensions, range partitioning or partitioning key
16
17
© 2009 IBM Corporation
… cnc2c1 … cnc2c1
… cnc2c1
TARGETTABLE
SOURCETABLE
STAGINGTABLE
INSERT
DELETE
UPDATE
COPY
REPLAY
Data flowvaluekeytabnametabschema
Online table move control table SYSTOOLS.ADMIN_MOVE_TABLE
Keys ofchanged rowscaptured via
triggers
Rowsre-copied
from source table
(by key)
17
18
© 2009 IBM Corporation
Local (aka partitioned) indexes
DP1
Jan 07 Feb 07 … Dec 07 Jan 08
ATTACH
IP1
DP2
IP2
DP12
IP12
Range Partitioned Tables
Ability to create local (partitioned) index Unique index must be superset of partition
key
Example:CREATE INDEX pINX1 on SALES (sales_date, partID) PARTITIONED
Partitioned index is the default Partition level reorg Detach availability improvements
18
19
© 2009 IBM Corporation
Index size comparison: Leaf page count
18,409
13,476
0
4,000
8,000
12,000
16,000
20,000
global index on RP table local index on RP table
Ind
ex l
eaf
pag
es
Partition maintenance with ATTACH
– 20x speedup compared to 9.5 global index because of reduced index maintenance
– 3000x less log space used than with 9.5 global index
Eliminates asynchronous index maintenance on DETACH
Local indexes occupy fewer disk pages than 9.5 global indexes
– 25% space savings is typical– 12% query speedup over
global indexes for index queries – fewer page reads
Range Partitioning with Local Indexes
25% Space
Savings
* Lower is better
Local Indexes* Lower is better
Total Time and Log Space required to ATTACH 1.2 million rows
651.84
0.05 0.03
0.21
1.E-02
1.E-01
1.E+00
1.E+01
1.E+02
1.E+03
V9.5 GlobalIndexes
Cobra LocalIndexes built
during ATTACH
Cobra LocalIndexes built
before ATTACH
No Indexes -Baseline
Log
Spac
e re
quire
d (M
B)
0.00
20.00
40.00
60.00
80.00
100.00
120.00
140.00
160.00
180.00
Atta
ch/S
et In
tegr
ity ti
me
(sec
)
Log Space used,MBAttach/Set Integritytime (sec)
19
20
© 2009 IBM Corporation
Transportable Schema Efficient schema movement between databases Transport schema from a backup image Performance objective – 100 GB in under 20 minutes Restore will now do multiple operations
– Restore the syscatspace and specified table spaces from the backup image
– Roll them forward to a consistency point– Validate the schemas specified– Transfer ownership of the specified table spaces to the
target DB– Recreate the schema in the target DB
20
21
© 2009 IBM Corporation
tablespace4
schema4
tablespace6tablespace2 tablespace3 tablespace5tablespace1
schema3
schema5
schema1
schema2
works works works
doesn’t work
Transport Sets
21
22
© 2009 IBM Corporation
Service Level Confidence
• Resource Optimization• Ongoing Flexibility• Resilience and Reliability• Performance • Monitoring• Workload Management
22
23
© 2009 IBM Corporation
Where is my DB application spending its time?
OperatingSystem
DB2 LUW
Network
JCC driver
WebSphere orJava App Server
Application
User
SQL 1 SQL 2 COMMIT
User experience
transaction
App pre- and post-processingIBM Tivoli CompositeApplication Manager for WebSphere Application Server(ITCAM for WAS)
− Application and application server insight
IBM DB2 PerformanceExpert V3.2 withExtended Insight Feature
− Transaction context− Connection, driver,
network, and database insight
End to End Monitoring
23
24
© 2009 IBM Corporation
Moving away from System Monitor Begin to move away from system monitor and snapshot technology for
database monitoring – Moving towards SQL access direct to internal memory– Continuing the trend of WLM table functions in DB2 9.5
New, parallel monitoring infrastructure introduced which is independent of system monitor infrastructure – i.e. not connected to existing system monitor infrastructure such as monitor
switches
Aim is to replace most commonly used database snapshot mechanisms over time– Only a few will be explicitly deprecated in Cobra but alternatives will be
provided– Snapshot still needed in future for instance level information
24
25
© 2009 IBM Corporation
“Time Spent” Metrics (example)
Processing / Non-Wait TimeWait Times
Log Buffer Insert Wait
Log Write Wait
Network Receive Wait
Network Send Wait
FCM Receive Wait
FCM Send Wait
WLM Queue Wait
Agent Wait
Lock Wait
Direct I/O Write Wait
Direct I/O Read Wait
Bufferpool Write Wait
Bufferpool Read Wait
Default Time MetricsTotal Time
25
26
© 2009 IBM Corporation
“Component Time” Metrics (example)
26
27
© 2009 IBM Corporation
Workload Management Objectives
– Deprecation of Query Patroller and Governor– Strengthen overall offering– Improve “Time to Value” for DB2 Workload Manager
Service Class Enhancements– Buffer Pool I/O priority
• Bias victim selection in Buffer Pool by assigning priority to pages visited by activities executing in a service class
• Reduces likelihood of high priority pages being selected as victim by low priority work
– Linux WLM integration• Available on Linux kernel 2.6.26 or above• Identical to AIX WLM integration from the DB2 perspective
27
28
© 2009 IBM Corporation
Workload Management Enhanced Thresholds
– Rows Read – Processing Time (CPU)– Aggregate System Temp
Workload Enhancements– Allow Activity Thresholds to be assigned at the workload level
• Estimated SQL cost, SQL rows returned, activity total time, SQL temp space
• Rows read• Processing time
28
29
© 2009 IBM Corporation
WLM TiersPriority Aging Concept
29
30
© 2009 IBM Corporation
Separation of Duties Remove implicit DBADM from SYSADM
Remove ability to grant DBADM and SECADM from SYSADM
Allow SECADM to be granted to groups and roles
Allow SECADM to GRANT/REVOKE database and object auth
Setup up a DBADM that does not have the capability to grant and revoke privileges or access data
GRANT DBADM ON DATABASE WITHOUT ACCESSCTRL TO USER JOEGRANT DBADM ON DATABASE WITHOUT DATAACCESS TO USER JOE
Remove secondary grants implicitly done when DBADM granted– BINDADD, CONNECT, CREATETAB, IMPLICIT_SCHEMA, LOAD,…
Introduce new authorities– EXPLAIN, DATAACCESS, ACCESSCTRL, SQLADM, WLMADM authorities– SQLADM authority can perform event monitor commands, holds EXPLAIN privilege, and can
execute RUNSTATS
30
31
© 2009 IBM Corporation
XML Insight
• ODS and warehouse
• Shared nothing support
• Large scale systems
• Range partitioning
• MDC
• XDA compression
31
32
© 2009 IBM Corporation
Simple query: Elapsed time speedup from 4 to 8 partitions
0
0.5
1
1.5
2
2.5
count w ithindex
count, noindex
grouped agg update colo join noncolo join
Elap
sed
time
4P /
8P
rel xmlxmlrel 80% of rel
*
XML on DPF: Scalability
Each query run in 2 or 3 equivalent variants:– Completely relational (“rel”) – Completely XML (“xml”)– XML extraction/predicates with relational joins (“xmlrel”) (join
queries only)
XML SCALES AS WELL AS RELATIONAL
* Higher than red line is better
Complex query: Elapsed time speedup from 4 to 8 partitions
0
0.5
1
1.5
2
2.5
3
3.5
1 2 3 4 5 6 7 8 9 10
Query number
Elap
sed
time
4P /
8P
rel xml
xmlrel 80% of rel
32
33
© 2009 IBM Corporation
Break free with DB2 Ongoing focus on flexibility
Support other DBMS’s SQL, natively Easy for developers to query DB2 Fast performance
Support other DBMS’s procedural language, natively Easy for developers to program DB2 Fast performance for procedural logic
Easily import other DBMS’s schemas Easy for developers to set up DB2
Support other DBMS’s concurrency models Easy for developers to use DB2
Support flexible data typing Easy for developers to work with DB2
And more…33
34
© 2009 IBM Corporation
Babylonian Confusion (aka Lock-In)
PL/SQLNUMBER“DATE”VARCHAR2CONNECT BY, DBMS_OUTPUT
SQL Standard
Another DBMS
DB2SQL/PSM(aka SQL PL)recursion, ..
SQL ’92, …
INTERVAL, ..
“IBM is committed to open standards”
Where does this leave YOU?
“Forget about portable code, exploit the DBMS!” (usenet wisdom)
GRAPHICSELECT FROM INSERT
34
35
© 2009 IBM Corporation
What’s changed in DB2?
Native supportProcedural SQL
Current DBMS DB2 9.7Concurrency ControlScalar Functions
Native supportNative support
SQL Native supportData Types Native supportImplicit Casting Native support
JDBC Native supportAdministrative Scripts Native support
Writers no longer block readers!
INITCAP, TO_NUMBERTO_CLOB, TO_LOB, TO_TIMESTAMP, date/time functions, ADD_MONTHSEXTRACT, LAST_DAY, MONTHS_BETWEEN, NEXT_DAY, ROUND, TRUNC , ROWNUM, TO_DATE, TO_CHAR, LPAD and RPAD, INSTRMIN, MAX, GREATEST, LEAST, BITAND, BITOR, BITXOR,BITANDNOT, BITNOT
e.g. CONNECT BY, NEXTVAL, CURRVAL, DECODEROWNUM, DUAL, TRUNCATE TABLE, ROWID, etc)
DATETIMESTAMP(n)VARCHAR 2BOOLEANROWASSOCIATIVE ARRAYCURSOR%TYPE% equiv%ROWTYPE% equivNUMBER
Weak typing allows assignment or comparison between differing data types.
Strings, dates, numerics
35
36
© 2009 IBM Corporation
Concurrency Control in DB2 9.7 Reads the currently committed version of a row
– If uncommitted row-change found use currently committed version Log based
– No management overhead– No performance overhead– No wasted memory/storage (no undo tablespace)
User 1: update T1 set name = ‘Russo’ where country=‘Italy’ X
RID 1=Rossi->Russo
Table T1Log Buffer
User 2:select * from T1
BelgiumPeeters
IsraelLevi
GreecePappas
SpainGarcia
FranceBernard
ItalyRossi
CountryName
Russo
Memory Lookup
Log Files
Scanner
Locks
36
37
© 2009 IBM Corporation
DB2 Early Access Program
Test-drive the new features!– Get more details and sign up
for the DB2 Early Access Program:
www.ibm.com/db2/technology-sandbox/
"Our uptime on DB2 9.5 was already very, very close to 100 percent so it’s difficult to improve upon that. But the stability of the product is really outstanding. We see a lot of new features in DB2 9.7 that we think can help developer productivity and reduce the amount of code significantly." --- John Enevoldson, Pulsen
37
38
© 2009 IBM Corporation
> Questions
38
39
© 2009 IBM Corporation
Thank You!
ibm.com/db2/labchats
39
Than
k you
for a
ttend
ing!