Slide 1

Download Report

Transcript Slide 1 

Session: B08
Physical Database Design
for the 21st Century
David Simpson
Themis Inc.
Mary 13, 2009 . 4:00 p.m. – 5:00 p.m.
Platform: DB2 for z/OS
Agenda
 A Few Things to Know Before Migrating to DB2 9
 Tablespace Design Options in V8 and V9
 Index Design Options in V8 and V9
 Managing New Statistics
 XML vs Relational Data
2
A Few Things to Know Before
Migrating to DB2 9
 The RUNSTATS cluster ratio calculation changes in DB2 9 CM
 The physical format of the row may change in DB2 9 NFM
 The temporary database goes away in DB2 9 CM
 Online reorg of partitions with NPIs changes in DB2 9 CM
 Implicit creation of objects changes in DB2 9 NFM
3
Cluster Ratio Changes
4
Cluster Ratio Changes
SELECT *
FROM EMP
WHERE DEPTNO = 'P01'
V8
Index on DEPTNO
5
Cluster Ratio Changes
SELECT *
FROM EMP
WHERE DEPTNO = 'P01'
DB2 9 Access
Path Changes!
Index on DEPTNO
6
Reordered Row Format
7
Row Format Prior to DB2 9
CREATE TABLE THEMIS.REORDER_ROW
(C1 CHAR(4) NOT NULL
,C2 VARCHAR(10) NOT NULL
,C3 CHAR(4) NOT NULL
,C4 VARCHAR(10) NOT NULL);
INSERT INTO THEMIS.REORDER_ROW
VALUES ('AAAA','BBBB','CCCC','DDDD');
V8
C1C1C1C10004C2C2C2C2C3C3C3C30004C4C4C4C4
C1
Data
C2
Length
C2
Data
C3
Data
C4
Length
C4
Data
8
Row Format – DB2 9 NFM
INSERT INTO THEMIS.REORDER_ROW
VALUES ('AAAA','BBBB','CCCC','DDDD');
DB2 9
NFM
C1C1C1C1C3C3C3C3000C0010C2C2C2C2C4C4C4C4
C1
Data
C3
C2 C4
C2
Data Start Positions Data
C4
Data
Fixed length portion of the row
9
Tempspace Consolidation
WORKFILE
TEMPDB
10
Online Reorg of a Partition
Part 1
BUILD2
Part 2
Part 3
Part 4
Part 5
V8
Partitioned
Tablespace
Partitioned
Index
Non-Partitioned
Index
11
BUILD2 Elimination
Part 1
Part 2
Part 3
Part 4
Part 5
DB2 9
Partitioned
Tablespace
Partitioned
Index
Non-Partitioned
Index
12
BUILD2 Elimination
DB2 9
Unload
Reload
Switch
Build 2
Sortbld
Log
13
Implicit Object Changes
CREATE TABLE PEOPLE
(PERSON_ID
INTEGER
,LAST_NAME
CHAR(20)
,FIRST_NAME
CHAR(20)
,ZIP_CODE
CHAR(5)
,BIRTH_DTE
DATE
,PRIMARY KEY (PERSON_ID) )
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
DSNDB04
Implicit
Tablespace
14
Implicit Object Changes
CREATE TABLE PEOPLE
(PERSON_ID
INTEGER
,LAST_NAME
CHAR(20)
,FIRST_NAME
CHAR(20)
,ZIP_CODE
CHAR(5)
,BIRTH_DTE
DATE
,PRIMARY KEY (PERSON_ID) )
NOT NULL
NOT NULL
NOT NULL
NOT NULL
NOT NULL
DSN00001
DSN00002
DSN00003
DSN00004
Implicit
Tablespace
Implicit Unique Index
on PERSON_ID
…
DSN60000
15
Tablespace Types
 Simple
 Segmented
 Partitioned




Simple (deprecated)
Segmented
“Classic” Partitioned
Universal
 Partition by Range
 Partition by Growth
16
Partitioning Choices
 Index Controlled
 Index Controlled
Range Partitioning
Range Partitioning
 Table Controlled
Range Partitioning
 Index Controlled
Range Partitioning
 Table Controlled
Range Partitioning
 Universal Partition
by Range (PBR)
 Universal Partition
by Growth (PBG)
17
Index Controlled Partitioning DDL
CREATE TABLESPACE IXCPTS
IN THEMISDB
NUMPARTS 9
USING STOGROUP TEACHERS
PRIQTY 720 SECQTY 720
PCTFREE 10 FREEPAGE 0
BUFFERPOOL BP0;
CREATE TABLE PEOPLE
(PERSON_ID INTEGER NOT NULL
,LAST_NAME CHAR(20) NOT NULL
,FIRST_NAME CHAR(20) NOT NULL
,ZIP_CODE CHAR(5) NOT NULL
,BIRTH_DTE DATE NOT NULL
)
IN THEMISDB.IXCPTS;
18
Index Controlled Partitioning DDL
CREATE INDEX PEOPLEX1
ON PEOPLE(PERSON_ID)
CLUSTER
(PART 1 VALUES (1000000)
,PART 2 VALUES (2000000)
…
,PART 9 VALUES (9000000)
)
USING STOGROUP TEACHERS
PRIQTY 720 SECQTY 720
PCTFREE 5 FREEPAGE 0
BUFFERPOOL BP0;
19
Table Controlled Partitioning DDL
CREATE TABLESPACE TBCPTS
IN THEMISDB
NUMPARTS 9
USING STOGROUP TEACHERS
PRIQTY 720 SECQTY 720
PCTFREE 10 FREEPAGE 0
BUFFERPOOL BP0;
CREATE TABLE PEOPLE
(PERSON_ID INTEGER NOT NULL
,LAST_NAME CHAR(20) NOT NULL
,FIRST_NAME CHAR(20) NOT NULL
,ZIP_CODE CHAR(5) NOT NULL
,BIRTH_DTE DATE NOT NULL)
PARTITION BY (PERSON_ID)
(PARTITION 1 ENDING AT (1000000)
,PARTITION 2 ENDING AT (2000000)
…
,PARTITION 9 ENDING AT (9000000) )
IN THEMISDB.TBCPTS;
20
Table Controlled Partitioning DDL
CREATE INDEX PEOPLEX1
ON PEOPLE(PERSON_ID)
CLUSTER
PARTITIONED
USING STOGROUP TEACHERS
PRIQTY 720 SECQTY 720
PCTFREE 5 FREEPAGE 0
BUFFERPOOL BP0;
21
Universal Tablespace PBR
CREATE TABLESPACE UNIPBRTS
IN THEMISDB
CREATE TABLE PEOPLE
NUMPARTS 9
(PERSON_ID INTEGER NOT NULL
USING STOGROUP TEACHERS
,LAST_NAME CHAR(20) NOT NULL
PRIQTY 720 SECQTY 720
,FIRST_NAME CHAR(20) NOT NULL
PCTFREE 10 FREEPAGE 0
,ZIP_CODE CHAR(5) NOT NULL
BUFFERPOOL BP0
,BIRTH_DTE DATE NOT NULL)
SEGSIZE 4;
PARTITION BY (PERSON_ID)
(PARTITION 1 ENDING AT (1000000)
,PARTITION 2 ENDING AT (2000000)
…
,PARTITION 9 ENDING AT (9000000) )
IN THEMISDB.UNIPBRTS;
22
Universal Tablespace PBG
CREATE TABLESPACE UNIPBGTS
IN THEMISDB
CREATE TABLE PEOPLE
MAXPARTITIONS 10
(PERSON_ID INTEGER NOT NULL
DSSIZE 4G
,LAST_NAME CHAR(20) NOT NULL
USING STOGROUP TEACHERS
,FIRST_NAME CHAR(20) NOT NULL
PRIQTY 720 SECQTY 720
,ZIP_CODE CHAR(5) NOT NULL
PCTFREE 10 FREEPAGE 0
,BIRTH_DTE DATE NOT NULL)
BUFFERPOOL BP0
IN THEMISDB.UNIPBGTS;
SEGSIZE 4;
23
Tablespace Recommendations
• One table per tablespace.
• On DB2 9, consider Universal Tablespaces for
any new objects.
• Search for Simple Tablespaces and consider
converting to Segmented or Universal PBG.
• Future releases of DB2, IBM will further enhance
the Universal Tablespace and further deprecate
the others.
24
Index Page Sizes
CREATE INDEX THEMIS82.XEMP03
ON THEMIS82.EMP
(LASTNAME ASC, FIRSTNME ASC, MIDINIT ASC)
USING STOGROUP WORKSHOP
PRIQTY 720 SECQTY 720
BUFFERPOOL BP8K1
PCTFREE 10 FREEPAGE 0
CLOSE NO;
25
Index Compression
CREATE INDEX THEMIS82.XEMP03
ON THEMIS82.EMP
(LASTNAME ASC, FIRSTNME ASC, MIDINIT ASC)
USING STOGROUP WORKSHOP PRIQTY 720 SECQTY 720
BUFFERPOOL BP8K1 COMPRESS YES
PCTFREE 10 FREEPAGE 0;
8K Index
Leaf Page
In the Bufferpool
4K Index
Leaf Page
On Disk
26
DSN1COMP with Indexes
//DSN1COMP
//SYSPRINT
//SYSUDUMP
//SYSUT1
EXEC PGM=DSN1COMP
DD SYSOUT=*
DD SYSOUT=*
DD DISP=SHR,DSN=D91A.DSNDBC.DTHM81.XEMP03.I0001.A001
How much space
would I save?
27
DSN1COMP with Indexes
DSN1940I DSN1COMP COMPRESSION REPORT
549
47,785
51,834
1,933
1,006
Index Leaf Pages Processed
Keys Processed
Rids Processed
KB of Key Data Processed
KB of Compressed Keys Produced
EVALUATION OF COMPRESSION WITH DIFFERENT INDEX PAGE SIZES:
8
47
53
5
16
48
52
51
32
48
52
75
---------------------------------------------K Page Buffer Size yields a
% Reduction in Index Leaf Page Space
The Resulting Index would have approximately
% of the original index's Leaf Page Space
% of Bufferpool Space would be unused to
ensure keys fit into compressed buffers
---------------------------------------------K Page Buffer Size yields a
% Reduction in Index Leaf Page Space
The Resulting Index would have approximately
% of the original index's Leaf Page Space
% of Bufferpool Space would be unused to
ensure keys fit into compressed buffers
---------------------------------------------K Page Buffer Size yields a
% Reduction in Index Leaf Page Space
The Resulting Index would have approximately
% of the original index's Leaf Page Space
% of Bufferpool Space would be unused to
ensure keys fit into compressed buffers
----------------------------------------------
28
Index on Expression
CREATE INDEX XEMP05 ON
EMP(UPPER(LASTNAME,'En_US'))
USING STOGROUP WORKSHOP
PRIQTY 720 SECQTY 720
BUFFERPOOL BP1
29
Index on Expression
SELECT LASTNAME, DEPTNO
FROM EMP
WHERE UPPER(LASTNAME,'En_US')
= ‘SMITH’
Stage 1
Indexable!
30
Non Uniform Distribution of Data
• Frequently occurring default values
or uneven distribution of values for a
column often cause optimizer issues
• Additional Statistics may be gathered
to give the optimizer additional
information
• Stats to combat skew
 DB2 V7
 DB2 V8
 DB2 9
31
Histogram Statistics
RUNSTATS INDEX(THEMIS82.XEMP02)
HISTOGRAM NUMCOLS 1 NUMQUANTILES 20
For Indexed Columns or Column Groups
RUNSTATS TABLESPACE DTHM82.TS00EMP
TABLE(THEMIS82.EMP)
COLGROUP(SALARY)
HISTOGRAM NUMQUANTILES 20
For ANY Column or Column Group
32
Viewing Histogram Statistics
SELECT
CHAR(NAME,18) AS NAME,
CHAR(STRIP(LOWVALUE,B),10) AS LOW,
CHAR(STRIP(HIGHVALUE,B),18) AS HI,
CAST (FREQUENCYF * 100 AS DECIMAL(5,2))
AS PCT_IN_RANGE
FROM SYSIBM.SYSCOLDIST
WHERE TBNAME = 'EMP'
AND TBOWNER = 'THEMIS82'
AND TYPE = 'H'
ORDER BY NAME, LOW
33
Viewing Histogram Statistics
NAME
LOW
HI
PCT_IN_RANGE
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
DEPTNO
A00
P01
P05
P08
P12
P16
P20
P24
P30
P35
P39
P43
P50
P54
P59
P62
P66
P70
P74
P80
P84
P90
P94
P98
E21
P04
P08
P12
P16
P20
P24
P29
P35
P39
P43
P50
P54
P59
P62
P66
P70
P74
P80
P84
P90
P94
P98
P99
0.05
3.96
2.98
3.76
3.45
3.35
3.44
3.73
5.28
3.11
3.78
5.46
3.83
3.56
2.67
3.49
3.46
3.80
5.45
3.39
4.99
3.82
3.66
1.06
34
Optimizer & Histogram Statistics
SELECT EMPNO, LASTNAME, FIRSTNME
FROM EMP
WHERE DEPTNO <= ‘M99’
Without XEMP02
Histogram Stats
With XEMP02
Histogram Stats
35
XML Column Admin Overview
CREATE TABLESPACE XML1TS
IN XMLDB
MAXPARTITIONS 20
DSSIZE 1G
USING STOGROUP WORKSHOP
PRIQTY -1 SECQTY -1
PCTFREE 10 FREEPAGE 0
SEGSIZE 16
BUFFERPOOL BP1;
CREATE TABLE THEMIS.PROJECT_MANAGE_XML
(DEPTNO CHAR(3) NOT NULL
,DEPT_XML XML)
IN XMLDB.XML1TS;
Implicitly Created
XML Tablespace
XML1TS
PROJECT_MANAGE_XML
CLASS_ID
DB2_GENERATED_
DOCID_FOR_XML
Implicit index on
DOCID
XPRO0000
XPROJECT_MANAGE_XML
DOC_ID MIN_NODEID XMLDATA
Implicit index on
DOCID, XMLDATA
36
XML Tablespace Type
Base Tablespace
XML Tablespace
Notes
Simple
Universal PBG
No DPSI allowed
Segmented
Universal PBG
No DPSI allowed
“Classic” Partitioned
Universal PBR
If row changes partition, XML
moves too.
Universal PBR
Universal PBR
If row changes partition, XML
moves too.
Universal PBG
Universal PBG
XML doc may span partitions
Source: DB2 9 for z/OS Technical Overview (SG24-7330), Section 8.3.2
37
XML Tablespace
DSNT360I
DSNT361I
-9A ***********************************
-9A * DISPLAY DATABASE SUMMARY
*
GLOBAL
DSNT360I -9A ***********************************
DSNT362I -9A
DATABASE = XMLDB STATUS = RW
DBD LENGTH = 4028
DSNT397I -9A
NAME
TYPE PART STATUS
PHYERRLO PHYERRHI CATALOG PIECE
-------- ---- ----- ----------------- -------- -------- -------- ----XML1TS
TS
0001 RW
XPRO0000 XS
0001 RW
IRDOCIDX IX
L*
RW
IRNODEID IX
L*
RW
******* DISPLAY OF DATABASE XMLDB
ENDED
**********************
DSN9022I -9A DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION
***
38
XML Column Storage
INSERT INTO THEMIS.PROJECT_MANAGE_XML
(DEPTNO,DEPT_XML)
VALUES(‘C01’,
‘<dept><deptno>C01</deptno><deptname>INFORMATION CENTER</deptname>
<emp><ename>KWAN</ename><hiredate>1975-04-05</hiredate><salary>38250.00</salary>
<project><projname>QUERY SERVICES</projname></project>
<project><projname>USER EDUCATION</projname></project>
</emp>
<emp><ename>NICHOLLS</ename><hiredate>1976-12-15</hiredate><salary>28420.00</salary></emp>
<emp><ename>QUINTANA</ename><hiredate>1971-07-28</hiredate><salary>23800.00</salary></emp>
</dept>’)
dept
deptno
deptname
ename
hiredate
emp
salary
emp
project
projname
ename
hiredate
salary
project
projname
39
XML Document Tree Storage
SYSIBM.SYSXMLSTRINGS
STRINGID
1124
1125
1126
1127
1128
1129
1130
1131
1132
1124
1125
1126
1128
1129
1127
1130
STRING
dept
deptno
deptname
emp
ename
hiredate
salary
project
projname
1127
1131
1132
1128
1129
1130
1131
1132
40
XML Index Creation
CREATE INDEX XITEM1 ON
PROJECT_MANAGE_XML (DEPT_XML)
GENERATE KEY USING XMLPATTERN
‘/dept/emp/empname’
XML Pattern
Expression
AS SQL VARCHAR(40)
NOT PADDED
USING STOGROUP WORKSHOP
PRIQTY 720 SECQTY 720
SQL Data Type
PCTFREE 10 FREEPAGE 0;
XML Column Name
(VARCHAR or DECFLOAT)
41
XML Index Exploitation
SELECT * FROM PROJECT_MANAGE_XML
WHERE XMLEXISTS('$x/dept/emp[empname = ‘Smith’]'
PASSING BY REF COMMENT AS "x");
42
Session: B08
Physical Database Design for the 21st Century
David Simpson
Themis Inc.
[email protected]
43