Transcript Sheryl M. Larsen, Inc. DB2  SQL Consulting & Education

Platform: z/OS
Application Development
DB2 10 – What to
Implement First!
Sheryl M. Larsen
Sheryl M. Larsen, Inc.
September 20, 2012
NEODB2UG, Sturbridge, MA
1
Sheryl M. Larsen, Inc.
DB2 SQL Consulting & Education
Sheryl Larsen is an internationally
recognized researcher, consultant
and lecturer, specializing in DB2
and is known for her extensive
expertise in SQL. She co-authored
a book, DB2 Answers, OsborneMcGraw-Hill, 1999.
Sheryl has over 20 years
experience in DB2, has published
articles, white papers, webtv:
She was voted an inaugural IBM
Information Champion 2009 ,
IDUG Speaker Hall of Fame and, a
member of IBM’s DB2 Gold
Consultants program since 1994.
Currently, she is President of the
Midwest Database Users Group
(mwdug.org), and owns Sheryl M.
Larsen, Inc., a firm specializing in
Advanced DB2 Consulting and
Education.
Contact: [email protected]
WWW.IBM.DEVELOPERWORKS.COM
WWW.CA.COM
WWW.BMC.COM
WWW.SOFTBASE.COM
(630) 399-3330
© Sheryl M. Larsen, Inc. 2000-2012
WWW.SMLSQL.COM
2
DB2 10 – WOW!
In Memory
Query techniques
Result
Moving SUM
Moving AVG
SQL
SQL Paging for partial result
Automatic Stats Collection
Work
Files
Caching of dynamic
Stage 2
In Memory
SQL with literals
Caching DB2 Code
And control structures
Buffer pool enhancements
Greater TIMESTAMP precision
Individual cells restricted,
value returned with
access denied
Temporal Semantics
Dynamic Index
ANDing improvements
Catalog
Instance Based SQL Hints
Stage 1
Buffer Pool
Directory
Buffer Manager
Indexing support of DECFLOAT
Enhanced Parallelism
INCLUDE Indexing support
Data
Index
© Sheryl M. Larsen, Inc. 2000-2012
New technique for p-key access
3
DB2 10 for z/OS
DB2 10 Optimization
Enhancements
Data dependent paging
DB2 10 SQL Enhancements
Index INCLUDE Option
Moving SUM
Moving AVG
Temporal Data Requests
© Sheryl M. Larsen, Inc. 2000-2012
4
Declare BRWSUM1 Cursor
DELCLARE BRWSUM1 CURSOR FOR
SELECT COL5
FROM BRWSUM
COL1
WHERE ((COL1 = :col1-last
A
AND COL2 = :col2-last
A
AND COL3 = :col3-last
A
AND COL4 > :col4-last)
B
OR
(COL1 = :col1-last
B
AND COL2 = :col2-last
B
AND COL3 > :col3-last)
B
OR
(COL1 = :col1-last
B
AND COL2 > :col2-last)
B
OR
(COL1 > :col1-last))
C
ORDER BY COL1, COL2, COL3, COL4C
FETCH FIRST 5 ROWS ONLY;
5
© Sheryl M. Larsen, Inc. 2000-2012
COL2
COL3
COL4
COL5
1
TT
99
Data ….
1
UU
77
Data ….
4
SS
66
Data ….
2
RR
66
Data ….
3
RR
77
Data ….
3
RR
88
Data ….
3
SS
66
Data ….
4
SS
99
Data ….
4
UU
88
Data ….
1
SS
66
Data ….
1
SS
77
Data ….
Get First 5 Rows
SET
:col1-last = low values, :col2-last = 0, :col3-last = low values,:col4-last = 0,
:page0 in COM AREA
OPEN BRWSUM1;
FETCH BRWSUM1 FOR 5
ROWS;
Store :col1-4-last = :last-fetchedrow in COM AREA and :page1
Display screen
COL1
COL2
COL3
COL4
COL5
A
1
TT
99
Data ….
A
1
UU
77
Data ….
A
4
SS
66
Data ….
B
2
RR
66
Data ….
B
3
RR
77
Data ….
B
3
RR
88
Data ….
B
3
SS
66
Data ….
B
4
SS
99
Data ….
B
4
UU
88
Data ….
C
1
SS
66
Data ….
C
1
SS
77
Data ….
6
© Sheryl M. Larsen, Inc. 2000-2012
Get Next 5 Rows
Get from COM AREA
:col1-last = ‘B’, :col2-last = 3, :col3-last = ‘RR’,:col4-last =77
OPEN BRWSUM1;
COL1
COL2
COL3
COL4
COL5
A
1
TT
99
Data ….
A
1
UU
77
Data ….
A
4
SS
66
Data ….
B
2
RR
66
Data ….
B
3
RR
77
Data ….
B
3
RR
88
Data ….
B
3
SS
66
Data ….
B
4
SS
99
Data ….
B
4
UU
88
Data ….
C
1
SS
66
Data ….
C
1
SS
FETCH BRWSUM1 FOR 5 ROWS;
Store :col1-4-last = :last-fetched-row in COM AREA and :page2
Display screen
77
Data ….
(
(COL1 = ‘B’
AND COL2 = 3
AND COL3 = ‘RR’
AND COL4 > 77)
OR
(COL1 = ‘B’
AND COL2 = 3
AND COL3 > ‘RR’)
OR
(COL1 = ‘B’
AND COL2 > 3)
OR
(COL1 > ‘B’))
First
Screen
7
© Sheryl M. Larsen, Inc. 2000-2012
Go Back 1 Page
Get from COM AREA for page0
:col1-last = low values :col2-last = 0, :col3-last = low values,:col4-last =0
OPEN BRWSUM1;
COL1
COL2
COL3
COL4
COL5
A
1
TT
99
Data ….
A
1
UU
77
Data ….
A
4
SS
66
Data ….
B
2
RR
66
Data ….
B
3
RR
77
Data ….
B
3
RR
88
Data ….
B
3
SS
66
Data ….
B
4
SS
99
Data ….
B
4
UU
88
Data ….
C
1
SS
66
Data ….
C
1
SS
FETCH BRWSUM1 FOR 5 ROWS;
Store :col1-4-last = :last-fetched-row in COM AREA and :page1
Display screen
77
Data ….
(
(COL1 = low
AND COL2 = 0
AND COL3 = low
AND COL4 > 0)
OR
(COL1 = low
AND COL2 = 0
AND COL3 > low)
OR
(COL1 = low
AND COL2 > 0)
OR
(COL1 > 0))
Last
Screen
8
© Sheryl M. Larsen, Inc. 2000-2012
DB2 10 - Data Paging for Partial Results
SELECT … FROM phoneBook
WHERE lastName = ?
AND firstName >= ?
OR lastName > ?
ORDER BY lastName, firstName
Prior OR would be ugly if no MIA(Multi-Index-Access)
 Now this query can be satisfied with a single index
access (lastname, firstname in this example)
 Via a new access method called 'range list access'
(currently 'NR' in explain).

© Sheryl M. Larsen, Inc. 2000-2012
9
DB2 10 SQL
Enhancements
Index INCLUDE Option
Moving SUM
Moving AVG
Temporal Data Requests
10
DB2 SQL Trends
DB2 for z/OS V8 vs. DB2 for LUW V8
Stage1 unlike data types, Multi-row INSERT, Multi-row FETCH,
Dynamic Scrollable Cursors, Multiple CCSIDs per statement,
Enhanced UNICODE, and Parallel Sort
Inner and Outer Joins, Table Expressions, Subqueries, GROUP BY,
Complex Correlation, Global Temporary Tables, CASE, 100+ Built-in
Functions, Limited Fetch, Scrollable Cursors, UNION Everywhere,
MIN/MAX Single Index Support, Self Referencing Updates with
Subqueries, Sort Avoidance for ORDER BY, and Row Expressions 2M
Statement Length, GROUP BY Expression, Sequences, Scalar Full
select, Materialized Query Tables, Common Table Expressions,
Recursive SQL, CURRENT PACKAGE PATH, VOLATILE Table
Support, Star Join Sparse Index, Qualified Column names, Multiple
DISTINCT clauses, IS NOT DISTINCT FROM, ON COMMIT DROP,
Transparent ROWID Column, GET DIAGNOSTICS
Updateable UNION in Views, INSERT with UPDATE/DELETE, ORDER
BY/FETCH FIRST in subselects & table expressions, GROUPING
SETS, ROLLUP, CUBE, INSTEAD OF TRIGGER, EXCEPT,
INTERSECT, and 16 Built-in Functions
© Sheryl M. Larsen, Inc. 2000-2012
11
DB2 10 for z/OS vs. DB2 9.7 for LUW
Multi-row INSERT, FETCH, Multi-row cursor UPDATE, Dynamic
Scrollable Cursors, GET DIAGNOSTICS, Enhanced UNICODE, MERGE,,
IS NOT DISTINCT FROM, VARBINARY, FETCH CONTINUE
temporal data, access controls
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 Scrollable Cursors, UNION Everywhere,
MIN/MAX Single Index Support, Self Referencing Updates with Subqueries, Sort
Avoidance for ORDER BY, and Row Expressions 2M Statement Length, GROUP
BY Expression, Sequences, Scalar Full select, Materialized Query Tables,
Common Table Expressions, Recursive SQL, CURRENT PACKAGE PATH,
VOLATILE Table Support, 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, DELETE, MERGE, INSTEAD OF TRIGGER, Native SQL
Procedure Language, BIGINT, file reference variables, XML, FETCH FIRST &
ORDER BY IN subselect and full select, caseless comparisons, INTERSECT,
EXCEPT, not logged tables, DECIMAL FLOAT, XQuery, TRUNCATE, OLAP
Functions, Session variables, OmniFind, Spatial, DECIMAL FLOAT,
TRUNCATE, ROLE ,last committed, CREATED temps
Updateable UNION in Views, GROUPING SETS, ROLLUP, CUBE, Many
Built-in Functions, SET CURRENT ISOLATION , multi-site join, MERGE,
ARRAY data type, more vendor friendly syntax, parameterized cursers,
CREATE MODULE
© Sheryl M. Larsen, Inc. 2000-2012
12
Index INCLUDE Option
 For
reduction of indexes
 How many?
 One per table X 9,000 tables!
» DASD savings – sure
» UPDATE/INSERT/DELETE savings – GREAT!
© Sheryl M. Larsen, Inc. 2000-2010
13
DB2 10 - Moving Average
Find the seven day centered moving average of XYZ stock for each day the stock
traded. The window is specified by the row clause.
SELECT date, symbol, close_price,
decimal(avg(close_price) over (order by date rows between 3
preceding and 3 following),6,3) as smooth_cp
FROM stock
DATE
SYMBOL CLOSE_PRICE
SMOOTH_CP
------------------------------------------------------------------------04/23/2007
XYZ
110.125
112.343
04/24/2007
XYZ
109.500
112.000
04/25/2007
XYZ
110.000
111.854
04/26/2007
XYZ
119.750
112.125
04/27/2007
XYZ
110.625
112.678
04/30/2007
XYZ
111.125
113.285
05/01/2007
XYZ
113.750
113.589
05/02/2007
XYZ
114.000
112.160
05/03/2007
XYZ
113.750
112.214
05/04/2007
XYZ
112.125
112.160
05/07/2007
XYZ
109.750
111.339
05/08/2007
XYZ
111.000
110.642
05/09/2007
XYZ
110.750
110.125
05/10/2007
XYZ
108.000
109.725
05/11/2007
XYZ
109.125
109.718
© Sheryl M. Larsen, Inc. 2000-2012
14
Numbers Graphed
CLOSE_PRICE
SMOOTH_CP
122
120
118
116
114
112
110
108
106
104
102
XYZ
XYZ
XYZ
XYZ
XYZ
XYZ
XYZ
XYZ
XYZ
XYZ
XYZ
XYZ
XYZ
XYZ
XYZ
4/23/2007 4/24/2007 4/25/2007 4/26/2007 4/27/2007 4/30/2007 5/1/2007 5/2/2007 5/3/2007 5/4/2007 5/7/2007 5/8/2007 5/9/2007 5/10/2007 5/11/2007
© Sheryl M. Larsen, Inc. 2000-2012
15
DB2 10 - Moving Average
For the stock XYZ, find the 7 day historical average for each day the stock traded.
The window is specified by the range clause.
SELECT date,substr(dayname(date),1,9) as day, close_price,
decimal(avg(close_price) over (order by date range 00000006.
preceding),7,2) as avg_7_range,
count(close_price) over (order by date range 00000006. preceding) as
count_7_range
FROM stock WHERE symbol = ‘XYZ’
DATE
DAY
CLOSE_PRICE
AVG_7_RANGE COUNT_7_RANGE
--------------------------------------------------------------------04/23/2007
Monday
110.125
110.12
1
04/24/2007
Tuesday
109.500
109.81
2
04/25/2007
Wednesday 110.000
109.87
3
04/26/2007
Thursday 119.750
112.34
4
04/27/2007
Friday
110.625
112.00
5
04/30/2007
Monday
111.125
112.20
5
05/01/2007
Tuesday
113.750
113.05
5
05/02/2007
Wednesday 114.000
113.85
5
05/03/2007
Thursday 113.750
112.65
5
05/04/2007
Friday
112.125
112.95
5
05/07/2007
Monday
109.750
112.67
5
05/08/2007
Tuesday 111.000
112.12
5
05/09/2007
Wednesday 110.750
111.47
5
05/10/2007
Thursday 108.000
110.32
5
05/11/2007
Friday
109.125
109.72
5
© Sheryl M. Larsen, Inc. 2000-2012
16
Moving Average
CLOSE_PRICE
AVG_7_RANGE
$122.00
$120.00
$118.00
$116.00
$114.00
$112.00
$110.00
$108.00
$106.00
$104.00
Friday
Thursday
Wednesday
Tuesday
Monday
Friday
Thursday
Wednesday
Tuesday
Monday
Friday
Thursday
Wednesday
Tuesday
Monday
$102.00
4/23/2007
4/24/2007
4/25/2007
4/26/2007
4/27/2007
4/30/2007
5/1/20075/2/20075/3/20075/4/20075/7/20075/8/20075/9/2007
5/10/2007
5/11/2007
© Sheryl M. Larsen, Inc. 2000-2012
17
Origins of Temporal Data
and Databases
Time Based Information
Point-In-Time Backup
PIT1
T1
PIT2
Interval
T2
PIT3
Interval
T3
PIT4
Interval
T4
Time
© Sheryl M. Larsen, Inc., Reed Meseck 2012
19
IBM Bi-Temporal Support
● Table-level specification to control the management of data based upon
time
● Two notions of time:
System time: notes the occurrence of a data base change
 “row xyz was deleted at 10:05 pm”
 Query at current or any prior period of time
 Useful for auditing, compliance
Business time: notes the occurrence of a business event
 “customer xyz’s service contract was modified on March 23”
 Query at current or any prior/future period of time
 Useful for tracking of business events over time, app logic greatly simplified
● New syntax in FROM clause to specify a time criteria for selecting
historical data
© Sheryl M. Larsen, Inc. 2000-2012
20
VIEW Magic
 Can
imitate pointing to the correct portion of
the history table
 Emulates getting data “AS OF” a particular
range.
 Can only solve the history problem
 More application magic is needed to emulate
asking business questions of future
© Sheryl M. Larsen, Inc. 2000-2010
21
FROM T1 FOR SYSTEM_TIME AS
OF timestamp-expression
FROM T1 FOR BUSINESS_TIME
FROM timestamp-expression1
TO timestamp-expression2
© Sheryl M. Larsen, Inc. 2000-2012
22
DB2 10 – WOW!
In Memory
Query techniques
Result
Moving SUM
Moving AVG
SQL
SQL Paging for partial result
Automatic Stats Collection
Work
Files
Caching of dynamic
Stage 2
In Memory
SQL with literals
Caching DB2 Code
And control structures
Buffer pool enhancements
Greater TIMESTAMP precision
Individual cells restricted,
value returned with
access denied
Temporal Semantics
Dynamic Index
ANDing improvements
Catalog
Instance Based SQL Hints
Stage 1
Buffer Pool
Directory
Buffer Manager
Indexing support of DECFLOAT
Enhanced Parallelism
INCLUDE Indexing support
Data
Index
© Sheryl M. Larsen, Inc. 2000-2012
New technique for p-key access
23