1 / 89

DB2 9 for z/OS Planning and Experiences

DB2 9 for z/OS Planning and Experiences. Jim Brogan IBM DB2 Advisor jambrog@us.ibm.com. Disclaimer and Trademarks.

niveditha
Download Presentation

DB2 9 for z/OS Planning and Experiences

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. DB2 9 for z/OS Planning and Experiences Jim Brogan IBM DB2 Advisor jambrog@us.ibm.com

  2. Disclaimer and Trademarks Information contained in this material has not been submitted to anyformal IBM review and is distributed on "as is" basis without any warranty either expressed or implied. Measurements data have been obtained in laboratory environment. Information in this presentation about IBM's future plans reflect current thinking and is subject to change at IBM's business discretion. You should not rely on such information to make business plans. The use of this information is a customer responsibility. IBM MAY HAVE PATENTS OR PENDING PATENT APPLICATIONS COVERING SUBJECT MATTER IN THIS DOCUMENT. THE FURNISHING OF THIS DOCUMENT DOES NOT IMPLY GIVING LICENSE TO THESE PATENTS. TRADEMARKS: THE FOLLOWING TERMS ARE TRADEMARKS OR ® REGISTERED TRADEMARKS OF THE IBM CORPORATION IN THE UNITED STATES AND/OR OTHER COUNTRIES: AIX, AS/400, DATABASE 2, DB2, e-business logo, Enterprise Storage Server, ESCON, FICON, OS/390, OS/400, ES/9000, MVS/ESA, Netfinity, RISC, RISC SYSTEM/6000, iSeries, pSeries, xSeries, SYSTEM/390, IBM, Lotus, NOTES, WebSphere, z/Architecture, z/OS, zSeries, System z, pureXML The FOLLOWING TERMS ARE TRADEMARKS OR REGISTERED TRADEMARKS OF THE MICROSOFT CORPORATION IN THE UNITED STATES AND/OR OTHER COUNTRIES: MICROSOFT, WINDOWS, WINDOWS NT, ODBC, WINDOWS 95 For additional information see ibm.com/legal/copytrade.phtml 1 1

  3. V8/V9 OverviewWorldwide Experience in the Field

  4. DB2 z/OS Announce / End Of Service • No skip release • No GA date announced for DB2 z/OS Vx (next) • No date announced for DB2 z/OS V8 EOS • won’t occur till after Vx ‘GA’d’

  5. DB2 z/OS Availability Summary June 2008

  6. DB2 Connect and DB2 z/OS v9 • MINIMUM requirements for DB2 Connect to work with DB2 z/OS V9. • V8 FP13, V8.2 FP 6, V9 FP1 • The more current the FIXPACs the better. • DB2 UDB LUW V8 products are OUT OF SUPPORT • would need to purchase extended support

  7. DB2 for z/OS Adoption • >85% WW Customers are Current • DB2 V8: Majority have Migrated • 100% of Top 100 >99% of Top 200 • V7 End of Service: June 30, 2008 • V8 Withdrawal from Marketing • Announced: Dec. 2, 2008 • Effective: Sept. 8, 2009 • DB2 9: Climbing Sharply • About 1/3 of Top 200 customers • 15% of TOTAL EAST • 15% of TOTAL NE/UNY (vast majority of DB2 Data Sharing Customers)

  8. Beneficial Activities • DB2 z/OS V9 Migration Planning Workshop • When ready to ORDER • OPEN PMR for Upgrade/Migration • When your ready to BEGIN • Stay CURRENT on MAINT

  9. Maintenance • Sound maintenance strategy is essential for all customers • Recommended to exploit CST/RSU process • Apply 2 to 3 preventative service drops annually • Exploit Enhanced HOLDDATA to be vigilant on HIPERs and PEs • No one-size-fits-all strategy • Review installation guide and the material supplied to ensure that RSU only service is installed • Can enforce installing RSU only service by adding the SOURCEID (RSU*) option in the supplied APPLY and ACCEPT jobs • Note '*' will pull ALL RSUs off of a particular tape

  10. Important CONSIDERATIONs • WLM Buffer Pool Management • Maybe NOT yet • RRF • caution if data Compressed • Plan Stability • SPT01 (64GB limit) • (8) 3390 mod 9’s, (64) 3390 mod 1’s • zPARM • BIND/Rebind • Converged TEMP Space • PERFORMANCE

  11. DSNTIJPM(9) • JP9 shipped with V8 APAR PK31841 • Checks for: • Check for V8 Sample Database • V8 job DSNTEJ1* • Report of user-defined indexes on the DB2 Catalog that reside on user-managed storage • On table spaces that will be converted during ENFM • DSNTIJEN needs modification for their shadow datasets • DB2 Managed Stored Procedures (SPAS) • Convert to WLM established stored procedures before migrating • Plans & Packages bound prior to V4 that need to be rebound • They will automatically rebind if ABIND = YES or COEXIST • If ABIND = NO, a -908 will be received at execute time • Incomplete table space, table, and columns • Optimization Service Center table format changes required before migration.

  12. Migrating to DB2 9 • Complete pre-migration checks against DB2 V8 (DSNTIJP9) • This will be the same as DSNTIJPM delivered with DB2 9 • Check / correct incompatibilities • The BSDS needs to be expanded to V8 format (DSNJCNVB) • If not done before migrating to V9, DSNTIJUZ will convert the BSDS(s) • Must be on DB2 for z/OS V8 New Function Mode • Reestablish V8 IVP to test DB2 9 before NFM • Assess ISV Requirements • Tools and applications • Some vendors may add instructions for migration and / or require maintenance • Assess the training requirements for your organization • Establish a project team and project plan

  13. Migrating to DB2 9 • Develop conversion and coexistence goals • How did your V8 test plans work? • Reuse and improve upon your experiences • Establish performance baselines • Migration occurs in three familiar phases • Conversion Mode (CM) • Enable New Function Mode (ENFM) • New Function Mode (NFM) • With more flexibility to move between modes

  14. DB2 9 for z/OS CM DB2 9 for z/OS ENFM DB2 9 for z/OS NFM Migrate Convert Convert V8 Catalog Catalog V9 State A Catalog V9 State B Catalog V9 State B DB2 9 for z/OS Migration Modes • Catalog Modes Illustrated • V8 to DB2 9 NFM DSNTIJTC DSNTIJEN DSNTIJNF DB2 for z/OS V8 NFM Install Fallback SPE & cycle DB2 (all members if data sharing) BSDS reformatted ( V8 DSNJCNVB) Run DSNTIJP9 Resolve inconsistencies & incompatibilities Can fallback to V8 Data sharing coexistence support Most new function unavailable Running DB2 9 code Regression testing RUNSTATS / REBIND Primary catalog migration phase Online REORGs of SYSOBJ & SYSPKAGE Start & Load of RTS Most new function unavailable Most new features available REORG table spaces containing tables with variable length columns to use RRF

  15. DB2 9 for z/OS CM DB2 9 for z/OS ENFM DB2 9 for z/OS NFM Migrate Convert Convert V8 Catalog Catalog V9 State A Catalog V9 State B Catalog V9 State B Some CM New Features • Catalog Modes Illustrated • V8 to DB2 9 NFM DSNTIJTC DSNTIJEN DSNTIJNF DB2 for z/OS V8 NFM Install Fallback SPE & cycle DB2 (all members if data sharing) BSDS reformatted ( V8 DSNJCNVB) Run DSNTIJP9 Resolve inconsistencies & incompatibilities Can fallback to V8 Data sharing coexistence support Most new function unavailable Running DB2 9 code Regression testing RUNSTAT / REBIND Primary catalog migration phase Online REORGs of SYSOBJ & SYSPKAGE Start & Load of RTS Most new function unavailable Most new features available REORG table spaces containing tables with variable length columns to use RRF • Additional 64 bit improvements • Rebind to gain these benefits with static Plans / Packages • Asymmetric index page splits • New access paths available with rebind • More archive logging buffers • DB2 9 Utilities (except online utility support for large format input data sets & RECOVER to PIT with consistency) • Data sharing improvements (except for log contention relief) • Rebinding can also help to identify incompatibilities (like new reserved words) • No new SQL features

  16. DB2 9 for z/OS CM DB2 9 for z/OS ENFM DB2 9 for z/OS NFM Migrate Convert Convert DB2 9 for z/OS CM* DB2 9 for z/OS CM* DB2 9 for z/OS ENFM* V8 Catalog Catalog V9 State A Catalog V9 State B Catalog V9 State B DB2 9 for z/OS Migration Modes • Catalog Modes Illustrated • Convert / Revert Mode Options • The “star” modes (CM*, ENFM*) help explain why some new function may appear before its expected time ( like a Universal Table Space in CM ) DB2 for z/OS V8 NFM DSNTIJCS Revert Revert DSNTIJES DSNTIJCS DSNTIJCS

  17. V9 Modes – An Overview CMCompatibility Mode - This is the mode DB2 is in when V9 is started for the first time from V8. It will still be in CM when migration job DSNTIJTC has completed. No new function can be executed in CM. Data sharing systems can have V8 and V9 members in this mode. DB2 can only migrate to CM from V8 NFM. ENFMEnabling New Function Mode - This mode is entered when CATENFM START is executed (the first step of job DSNTIJEN). DB2 remains in this mode until all the enabling functions are completed. Data sharing systems can only have V9 members in this mode. NFMNew Function Mode - This mode is entered when CATENFM COMPLETE is executed (the only step of job DSNTIJNF). This mode indicates that all catalog changes are complete and new function can be used. ENFM*This is the same as ENFM but the * indicates that at one time DB2 was at NFM. Objects that were created when the system was at NFM can still be accessed but no new objects can be created. When the system is in ENFM* it can not fallback to V8 or coexist with a V8 system. CM*This is the same as CM but the * indicates that at one time DB2 was at a higher level. Objects that were created at the higher level can still be accessed. When DB2 is in CM* it can not fallback to V8 or coexist with a V8 system.

  18. DB2 9 Catalog • New Catalog Table Spaces for • Real-Time Statistics • New page size for SYSOBJ • XML • Trusted Context • Extended Index definitions

  19. Catalog Table Spaces DB2 for z/OS V8 DB2 9 for z/OS TABLESPACE PAGESIZE ---------+---------+--- SYSCOPY 4 SYSDBAUT 4 SYSDDF 4 SYSEBCDC 4 SYSGPAUT 4 SYSGROUP 4 SYSJAUXA 4 SYSJAUXB 4 SYSJAVA 4 SYSPKAGE 4 SYSPLAN 4 SYSRTSTS 4 SYSSEQ 4 SYSSEQ2 4 SYSUSER 4 SYSDBASE 8 SYSGRTNS 8 SYSHIST 8 SYSPLUXA 8 SYSSTR 8 SYSVIEWS 8 SYSXML 8 SYSCONTX 16 SYSOBJ 16 SYSROLES 16 SYSSTATS 16 SYSTARG 16 SYSALTER 32 TABLESPACE PAGESIZE ---------+---------+---- SYSCOPY 4 SYSDBAUT 4 SYSDDF 4 SYSEBCDC 4 SYSGPAUT 4 SYSGROUP 4 SYSJAUXA 4 SYSJAUXB 4 SYSJAVA 4 SYSPKAGE 4 SYSPLAN 4 SYSSEQ 4 SYSSEQ2 4 SYSUSER 4 SYSDBASE 8 SYSGRTNS 8 SYSHIST 8 SYSOBJ 8 SYSSTR 8 SYSVIEWS 8 SYSSTATS 16 SYSALTER 32 New TS for Real-Time Stats Auxiliary Table to hold TEXT from Routines New page size XML & Trusted Context Extended Index Definitions 22 Tablespaces 28 Tablespaces

  20. DB2 9 CPU Performance The target for DB2 9 CPU performance is to be roughly equivalent or marginally better relative to V8 Mileage will vary Customers running DB2 9 on old hardware (z800/z900) will likely see CPU regression - maybe 10% Data sharing customers running on DB2 9 (NFM) may see significant savings from reduced LC19 contention and less spin to get unique LRSN

  21. Utilities Performance Improvements • Parallelism for REORG – V9 • 10-40% elapsed time improvement • Parallel log apply for REORG – V9 Parallelism for CHECK INDEX – V9 • Up to 30% improvement in elapsed time, 5% CPU degradation • Utility CPU time reduction – V9 • 5-20% for RECOVER, REBUILD, REORG • 5-30% for LOAD • 20-60% for CHECK INDEX • 35% for LOAD partition • 15% for COPY • 30-50% for RUNSTATS INDEX • 40-50% for REORG INDEX • Up to 70% for LOAD REPLACE of single partition UTL

  22. WLM assisted buffer pool management • DB2 registers the BPOOL with WLM. • DB2 provides sizing information to WLM. • DB2 communicates to WLM each time allied agents encounter delays due to read I/O. • DB2 periodically reports BPOOL size and random read hit ratios to WLM. • just as though an ALTER BUFFERPOOL VPSIZE command had been issued. • DB2 V9 restricts the total adjustment to +/- 25% the size of the buffer pool at DB2 startup • if a buffer pool size is changed and later DB2 is shut down and subsequently brought up, the last used buffer pool size is remembered across DB2 restarts !!!!!!

  23. WLM assisted buffer pool management ALTER BUFFERPOOL AUTOSIZE option DBM1 WLM Data Collection DB2 Periodic Report Buffer Pool Sizes Hit Ratio for Random Reads BP0 BP1 1 Plots size and hit ratio over time. 2 Projects effects of changing the size BP2 BP7 Bufferpool Adjustment + - 25%

  24. REORDERED ROW FORMAT • in DB2 9 new function mode (NFM) • REORG or LOAD REPLACE, changes the row format from basic row format (BRF) to reordered row format (RRF) • NO EDITPROC or VALIDPROC • more efficient compression dictionary IF you rebuild the dictionary AFTER converting over to reordered row format. • REORG and LOAD jobs with KEEPDICTIONARY specified • the introduction of APAR PK41156 that makes a change to REORG and LOAD REPLACE so they ignore KEEPDICTIONARY for that one time run when the rows are reordered and allows for a rebuild of the dictionary regardless of the KEEPDICTIONARY setting. • APAR also introduces a new keyword APAR also introduces a new keyword HONOR_KEEPDICTIONARY and it defaults to NO

  25. Varying Length Cols Varchar Indicators Prefix Fixed Length Cols Reordered Row Format (RRF) • Automatic repositioning of variable length columns to end of row • Length attributes replaced with indicators positioned after fixed length columns • Any table space created in DB2 9 NFM • To Convert: • REORG or LOAD REPLACE a table space or partition • ADD PARTITION • No EDITPROCs or VALIDPROCs • EDITPROCs may need to be updated if implemented for specific columns • Byte RFMTTYPE passed to indicate fixed length, basic, or reordered format • Consider this impact on tables encrypted via an EDITPROC • DSN1COPY impact during the transition period across environments • PIT RECOVER will set the table space to the row format of the PIT • Catalog / Directory remains in Basic Row Format (BRF) DSN

  26. Varying Length Cols Varchar Indicators Prefix Fixed Length Cols Reordered Row Format (RRF) • Logging Considerations • Variable length rows that DO NOT change length: • Logging is from first byte of first changed column to last byte of last changed column • RRF should not negatively impact logging in this case • For variable length rows changing length & compressed rows • Logging is from first changed byte until the end of the row • One consideration may be where variable length columns are placed at the end of the row in V8 AND where the length changes • Now the logging will be from the indicators (offsets). DSN

  27. RRF

  28. F1 F2 V3 F4 F5 V6 Varchar Performance Improvement • Old tuning recommendation for rows with many columns with any varchar present • V9 DB2 internally executes this recommendation and more • 2 times or more improvement observed when many rows with many varchars are scanned and/or fetched using many predicates • No difference if no varchar , Under 5% improvement for a typical online transaction • Reorg with rebuild compression dictionary if varchar columns when migrating to V9

  29. Access Path Stability • New function of DB2 9 (PK52523) • Protects customers against access path regression • Allows for a “safe” way to REBIND (fall back) • Available even in DB2 9 (CM) as it can benefit migration and fallback • Strongly recommended (SPT01) • Make sure that the pre-conditioning APAR for Plan Stability (PK52522) is • applied on all V8 (NFM) systems • What is the problem? • REBINDs can cause access path changes • Most of the time, this improves query performance … … But when it doesn’t No easy way to undo the REBIND Can lead to a lot of grief to our customers and to IBM

  30. Access Path Stability • Delivered with APAR PK52523 (V9) • Preconditioning APAR PK52522 (V8 / V9) • For fallback to V8 and coexistence with V8 / V9) – V8 toleration of multiple package copies • Also causes DB2 to delete old copies of PLANMGMT packages invalidated due to database changes • REBIND PACKAGE... PLANMGMT(OFF | BASIC | EXTENDED) • Or REBIND TRIGGER PACKAGE • ZParm PLANMGMT is online updateable • Options: • OFF (default) • Do not use plan stability • Package continues to have one active copy • BASIC: • Package has one active copy and one old (previous) copy • EXTENDED: • One active and two old / preserved package copies • The preserved copies are the previous and original copies OPT

  31. Access Path Stability • REBIND PACKAGE...SWITCH(PREVIOUS | ORIGINAL) • SWITCH(PREVIOUS): toggles previous and current copies • SWITCH(ORIGINAL): previous deleted; current->previous; original cloned to current • FREE PACKAGE...PLANMGMTSCOPE(ALL | INACTIVE) • ALL is the default and frees all copies • INACTIVE frees all old copies • SYSPACKAGE reflects the current copy • Other package related tables reflect dependencies of all packages • DTYPE column of SYSPACKDEP overloaded to indicate ‘P’revious or ‘O’riginal • To keep one V8 package • REBIND with PLANMGMT(BASIC) once • Subsequent REBINDs with PLANMGMT(OFF) • REBIND SWITCH(PREVIOUS) can be used to use the original V8 package OPT

  32. Access Path Stability • Before falling back to V8 • REBIND...SWITCH to the V8 package before fallback • The V8 preconditioning maintenance will tolerate additional copies • Restrictions • No Native SQL Procedure support today • No support for DBRMs bound into Plans. • Impacts • Requires additional SPT01 space • Double for packages with BASIC • Triple for those with EXTENDED • REBIND can take 10 – 40% additional CPU • Access Path Stability is not “sticky”. Except for the ZParm, it the chosen level must be specified on the REBIND. OPT

  33. Optimization Service Center • Identify Problem Query • Tune Problem Query • Monitor & Capture Query Workload • Tune Query Workload

  34. Converged TEMP Space • Single source for all temporary space in DB2 • Workfile (work files and Created Global Temporary Tables) • Temp DB (Static Scrollable Cursors and Declared Global Temporary Tables) • Merged into Workfile Database • In CM & NFM • The workfile database is the only temporary database • Supports 4K and 32K page sizes, with automatic selection of the appropriate page size • Expect an increased use of the 32K temp space • Consider sizing your 32K @ 50% - 100% of your 4K buffers • Monitor statistics and adjust for actual usage • Access is virtualized for small amounts of data, eliminating cost of work file creation (reduced CPU and I/O) • At runtime, a result fitting in 1 data page does not create a workfile • ORDER BY and FETCH FIRST n ROWS without index support • Uses memory replacement technique if result fits within a 32k page • Sort is avoided • New ZParm for preventing workfile monopolization (MAXTEMPS) • IFCID 002 & 343 updated to report usage and exceptions VST

  35. Temporary Space – The DB2 V8 Picture Installation support (DSNTIJTM) CREATE DATABASE xxx as WORKFILE … * Define VSAM Dataset CREATE TABLESPACE DSN4K01 IN xxx … No installation support CREATE DATABASE xxx as TEMP … WORKFILE TEMP Declared temporary tables for SSC Work files Created global temporary tables Declared global temporary tables * Only in a data sharing environment – in non-data sharing syntax is CREATE DATABASE DSNDB07

  36. Temporary Space – The DB2 9 Picture Declared Global Temporary Tables and Static Scrollable Cursors now use the WORKFILE database instead of the TEMP database Uses DB2-managed (instead of user-managed) storage in SYSDEFLT storage group Segmented table space organisation (user-defined SEGSIZE or default of 16) 4KB and 32KB page sizes only – no 8KB or 16KB Installation and migration support (REXX program called by DSNTIJTM) CREATE DATABASE xxx as WORKFILE; DSNTWFG DB41 DB2ADM xxx + 3 10 16 BP0 SYSDEFLT + 1 20 16 BP32K SYSDEFLT Declared global temporary tables Created global temporary tables WORKFILE Declared temporary tables for SSC Work files

  37. Planning For Converged TEMP Space Migration from DB2 V8 To reclaim TEMP database storage, *YOU* must drop the TEMP database and reallocate the storage Recommendation: Do not drop the TEMP database until you are sure that you will not return be falling back to V8, to avoid having to recreate it after fallback New installation panel for work file database definitions (DSNTIP9) In migration mode, if you specify non-zero values Migration job DSNTIJTM will create additional DB2-managed WORKFILE table spaces in the SYSDEFLT storage group  new REXX program DSNTWFG DB2 does not take into account the existing work file table spaces Recommendation: set the 'DSVCI' ZPARM to YES to allow DB2 to match VSAM CI size to table space page size Ensure you have 32KB WORKFILE table spaces for Declared Global Temporary Tables and Static Scrollable Cursors

  38. Controlling Temporary Space Utilization Control of temporary space utilization at the agent level New ZPARM: MAXTEMPS Macro DSN6SYSP, panel DSNTIP9 If MAXTEMPS is exceeded for any given agent: WORK FILE DATABASE SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00C90305, TYPE OF RESOURCE 100, AND RESOURCE NAME = 'WORKFILE DATABASE' SQLSTATE = 57011

  39. DB2 9 for z/OS – Addressing corporate data goals • Improved IT Infrastructure for Compliance Efforts • Trusted security context • Database roles • Auditing, encryption improved • Simplify development and porting • Many SQL improvements • Native SQL stored procedures • Default databases and table spaces • Data Warehousing • Dynamic index ANDing for star schema • EXCEPT and INTERSECT • Decrease Complexity and Cost • Partition by growth • Performance improvements • Volume-based COPY/RECOVER • Index compression • Optimization Service Center • Evolve Your Environment & SOA • Integrated pureXML® • WebSphere®integration • Continuous Availability • Schema evolution enhancements • Fast table replacement

  40. Native SQL Procedural Language zIIP Enabledfor DRDA • Eliminates generated C code and compilation • Fully integrated into the DB2 engine • Any SQL procedure created without the FENCED or EXTERNAL keywords are native SQL procedures • zIIP enabled for DRDA clients • Extensive support for versioning: • VERSION keyword on CREATE PROCEDURE • CURRENT ROUTINE VERSION special register • ALTER ADD VERSION • ALTER REPLACE VERSION • ALTER ACTIVATE VERSION • BIND PACKAGE with new DEPLOY keyword SQL

  41. Past Table Spaces Options • Past table space options • Simple • Multi table, interleaved • Segmented • Multi table, no page sharing • Good with mass deletes • 64GB • Partitioned • One table per table space • 128TB • Doesn’t have the internal space map like that of a segmented table space. DSN

  42. Universal Table Spaces • Universal Table Space • Combination of segmented with partitioning options • Better space management • Support of mass deletes / TRUNCATE • If partitioned • Still must be one table per table space • Can choose Range Based partitioning (as before: PBR) • Can choose Partitioned By Growth (PBG) • DROP / CREATE to migrate existing page sets • Simple table spaces can not be created • Default table space is now Segmented DSN

  43. Universal Table Spaces – Partitioned By Growth • Partition By Growth (PBG) • Single-table table space, where each partition contains a segmented page set (allows segmented to increase from 64GB to 16TB or 128 TB with 32K pages) • Eliminates need to define partitioning key and assign key ranges • Partitions are added on demand • A new partition is created when a given partition reaches DSSIZE • See the SQL Reference for DSSIZE rules given the page size & number of partitions • Up to MAXPARTITIONS • Retains benefits of Utilities and SQL parallelism optimizations for partitioned tables • SEGSIZE defaults to 4 & LOCKSIZE defaults to ROW DSN

  44. Universal Table Spaces • Partition By Growth (PBG) • CREATE TABLESPACE…. MAXPARTITIONS n • Can specify DSSIZE • Only the first partition is created with the CREATE statement (if DEFINE YES) • No USING VCAT. • The compression dictionary is copied as new partitions are created. • Also syntax to specify PGB on CREATE TABLE, when defaulting the DB & TS. • Considerations: • Single-table table space • Always defines as LARGE • Need PBR for query partition elimination • No LOAD PART, ALTER ADD PART, or ROTATE PART • All indexes are NPSIs DSN

  45. Universal Table Spaces • What kind of Table Space will be created? * * DSN

  46. Index Changes • INDEX on expression • Page sizes 8K, 16K, 32K • Improved page split • Index compression • Online REBUILD INDEX • REORG without BUILD2 – not just for DPSI • Randomized index key • Not logged index space • XML index

  47. Index Compression • Compression of indexes for BI workloads • Indexes are often larger than tables in BI • Solution provides page-level compression • Data is compressed to 4K pages on disk • 8K, 16K or 32K pages results in 2x, 4X or 8x disk savings • No compression dictionaries – compression on the fly

  48. Index Compression: Differences between data and index compression

  49. Asymmetric Index Page Splits Multiple Sequential Insert Patterns on an Index Sequential inserts into the middle of an index resulted in some pages with 50% free space prior to V9 New algorithm dynamically accommodates a varying pattern of inserts IDX

  50. Relief for Sequential Key INSERT • New page sizes: 8K, 16K, 32K for INDEX pages • Fewer page splits for long keys • More key values per page • INSERT at the end of the key range used to result in 50% free space in each index page • Enhanced support dynamically adapts page split boundary to minimize wasted space in index pages • Index key randomization

More Related