1 / 43

Mary 13, 2009 . 4:00 p.m. – 5:00 p.m. Platform: DB2 for z/OS

Session: B08 Physical Database Design for the 21 st 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

barr
Download Presentation

Mary 13, 2009 . 4:00 p.m. – 5:00 p.m. Platform: DB2 for z/OS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Session: B08Physical 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

  2. 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

  3. 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

  4. Cluster Ratio Changes

  5. Cluster Ratio Changes SELECT * FROM EMP WHERE DEPTNO = 'P01' V8 Index on DEPTNO

  6. Cluster Ratio Changes SELECT * FROM EMP WHERE DEPTNO = 'P01' DB2 9 Access Path Changes! Index on DEPTNO

  7. Reordered Row Format

  8. 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

  9. Row Format – DB2 9 NFM DB2 9 NFM INSERT INTO THEMIS.REORDER_ROW VALUES ('AAAA','BBBB','CCCC','DDDD'); C1C1C1C1C3C3C3C3000C0010C2C2C2C2C4C4C4C4 C1 Data C3 Data C2 C4 C2 Data C4 Data Start Positions Fixed length portion of the row

  10. Tempspace Consolidation WORKFILE TEMPDB

  11. BUILD2 Online Reorg of a Partition Part 1 Part 2 Part 3 Part 4 Part 5 V8 Partitioned Tablespace Partitioned Index Non-Partitioned Index

  12. BUILD2 Elimination Part 1 Part 2 Part 3 Part 4 Part 5 DB2 9 Partitioned Tablespace Partitioned Index Non-Partitioned Index

  13. BUILD2 Elimination DB2 9 Unload Reload Sortbld Log Build 2 Switch

  14. DSNDB04 Implicit Object Changes 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 ,PRIMARY KEY (PERSON_ID) ) Implicit Tablespace

  15. DSN00001 DSN00002 DSN00003 DSN00004 DSN60000 Implicit Object Changes 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 ,PRIMARY KEY (PERSON_ID) ) Implicit Tablespace … Implicit Unique Index on PERSON_ID

  16. Tablespace Types DB2 V8 DB2 9 • Simple • Segmented • Partitioned • Simple (deprecated) • Segmented • “Classic” Partitioned • Universal • Partition by Range • Partition by Growth

  17. Index Controlled Range Partitioning Partitioning Choices DB2 V7 DB2 V8 DB2 9 • Index Controlled Range Partitioning • Table Controlled Range Partitioning • Index Controlled Range Partitioning • Table Controlled Range Partitioning • Universal Partition by Range (PBR) • Universal Partition by Growth (PBG)

  18. 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;

  19. 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;

  20. 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; DB2 V8

  21. 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; DB2 V8

  22. Universal Tablespace PBR CREATE TABLESPACE UNIPBRTS IN THEMISDB NUMPARTS 9 USING STOGROUP TEACHERS PRIQTY 720 SECQTY 720 PCTFREE 10 FREEPAGE 0 BUFFERPOOL BP0 SEGSIZE 4; 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.UNIPBRTS; DB2 9

  23. Universal Tablespace PBG CREATE TABLESPACE UNIPBGTS IN THEMISDB MAXPARTITIONS 10 DSSIZE 4G USING STOGROUP TEACHERS PRIQTY 720 SECQTY 720 PCTFREE 10 FREEPAGE 0 BUFFERPOOL BP0 SEGSIZE 4; 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.UNIPBGTS; DB2 9

  24. 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.

  25. 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;

  26. 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

  27. DSN1COMP with Indexes //DSN1COMP EXEC PGM=DSN1COMP //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSUT1 DD DISP=SHR,DSN=D91A.DSNDBC.DTHM81.XEMP03.I0001.A001 How much space would I save?

  28. DSN1COMP with Indexes DSN1940I DSN1COMP COMPRESSION REPORT 549 Index Leaf Pages Processed 47,785 Keys Processed 51,834 Rids Processed 1,933 KB of Key Data Processed 1,006 KB of Compressed Keys Produced EVALUATION OF COMPRESSION WITH DIFFERENT INDEX PAGE SIZES: ---------------------------------------------- 8 K Page Buffer Size yields a 47 % Reduction in Index Leaf Page Space The Resulting Index would have approximately 53 % of the original index's Leaf Page Space 5 % of Bufferpool Space would be unused to ensure keys fit into compressed buffers ---------------------------------------------- 16 K Page Buffer Size yields a 48 % Reduction in Index Leaf Page Space The Resulting Index would have approximately 52 % of the original index's Leaf Page Space 51 % of Bufferpool Space would be unused to ensure keys fit into compressed buffers ---------------------------------------------- 32 K Page Buffer Size yields a 48 % Reduction in Index Leaf Page Space The Resulting Index would have approximately 52 % of the original index's Leaf Page Space 75 % of Bufferpool Space would be unused to ensure keys fit into compressed buffers ----------------------------------------------

  29. Index on Expression CREATE INDEX XEMP05 ON EMP(UPPER(LASTNAME,'En_US')) USING STOGROUP WORKSHOP PRIQTY 720 SECQTY 720 BUFFERPOOL BP1

  30. Index on Expression SELECT LASTNAME, DEPTNO FROM EMP WHERE UPPER(LASTNAME,'En_US') = ‘SMITH’ Stage 1 Indexable!

  31. 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

  32. 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

  33. 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

  34. Viewing Histogram Statistics NAME LOW HIPCT_IN_RANGE DEPTNO A00 E21 0.05 DEPTNO P01 P04 3.96 DEPTNO P05 P08 2.98 DEPTNO P08 P12 3.76 DEPTNO P12 P16 3.45 DEPTNO P16 P20 3.35 DEPTNO P20 P24 3.44 DEPTNO P24 P29 3.73 DEPTNO P30 P35 5.28 DEPTNO P35 P39 3.11 DEPTNO P39 P43 3.78 DEPTNO P43 P50 5.46 DEPTNO P50 P54 3.83 DEPTNO P54 P59 3.56 DEPTNO P59 P62 2.67 DEPTNO P62 P66 3.49 DEPTNO P66 P70 3.46 DEPTNO P70 P74 3.80 DEPTNO P74 P80 5.45 DEPTNO P80 P84 3.39 DEPTNO P84 P90 4.99 DEPTNO P90 P94 3.82 DEPTNO P94 P98 3.66 DEPTNO P98 P99 1.06

  35. Optimizer & Histogram Statistics SELECT EMPNO, LASTNAME, FIRSTNME FROM EMP WHERE DEPTNO <= ‘M99’ Without XEMP02 Histogram Stats With XEMP02 Histogram Stats

  36. PROJECT_MANAGE_XML CLASS_ID DB2_GENERATED_ DOCID_FOR_XML XML Column Admin Overview XML1TS 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; Implicit index on DOCID XPRO0000 Implicitly Created XML Tablespace XPROJECT_MANAGE_XML DOC_ID MIN_NODEID XMLDATA Implicit index on DOCID, XMLDATA

  37. XML Tablespace Type Source: DB2 9 for z/OS Technical Overview (SG24-7330), Section 8.3.2

  38. XML Tablespace DSNT360I -9A *********************************** DSNT361I -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 ***

  39. dept emp emp 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>’) deptno deptname ename hiredate salary project ename hiredate salary project projname projname

  40. 1124 1127 1127 XML Document Tree Storage SYSIBM.SYSXMLSTRINGS STRINGID STRING 1124 dept 1125 deptno 1126 deptname 1127 emp 1128 ename 1129 hiredate 1130 salary 1131 project 1132 projname 1125 1126 1128 1129 1130 1131 1128 1129 1130 1131 1132 1132

  41. XML Index Creation CREATE INDEX XITEM1 ON PROJECT_MANAGE_XML (DEPT_XML) GENERATE KEY USING XMLPATTERN ‘/dept/emp/empname’ AS SQL VARCHAR(40) NOT PADDED USING STOGROUP WORKSHOP PRIQTY 720 SECQTY 720 PCTFREE 10 FREEPAGE 0; XML Pattern Expression XML Column Name SQL Data Type (VARCHAR or DECFLOAT)

  42. XML Index Exploitation SELECT * FROM PROJECT_MANAGE_XML WHERE XMLEXISTS('$x/dept/emp[empname = ‘Smith’]' PASSING BY REF COMMENT AS "x");

  43. Session: B08 Physical Database Design for the 21st Century David Simpson Themis Inc. dsimpson@themisinc.com

More Related