DB2 10 Hash Access: Access Path or Collision Course?

1. DB2 10 Hash Access: Access Path or Collision Course? Donna Di Carlo BMC Software Session Code: A13 Wednesday, 16 November 2011 | Platform: DB2 for z/OS

2. Objectives • An introduction to the new DB2 10 Hash Access • See how a hashed table and index are physically organized  • Determine if Hash Access is suitable for your application • Some rules of thumb on how to define hashed tables and indexes • What happens to performance when hashed objects are defined correctly; and worse, what happens when they are defined incorrectly

3. DB2 Access Paths • DB2 has several different methods to access data • DB2 10 has added hash access to its bag of tricks • Single row access • Unique non-updateable key • Fixed hash space size Table space Scan Rid List Scan Index Only Prefetch ORGANIZE BY HASH UNIQUE (column names) HASH SPACE size K|M|G

4. Hash Organization

5. Implementing Hash Access • Create a new table with hash organization • Need to specify hash space size • Unique key can be comprised of multiple columns, but the length must be less than 256 • Entire hash area is allocated, plus 1% - 3% for overflow • Recommendation • Set ZPARM IMPDSDEF=YES so the overflow index is instantiated during the CREATE – avoids penalty for first insert into the overflow

6. Implementing Hash Access (cont.) • Alter existing table to hash organization • Criteria • Must be UTS • Must contain a unique non-updatable key • Must have Reordered Row Format • Table size should be relatively static • Can’t be MEMBER CLUSTER • Can’t have clustering index

7. Implementing Hash Access (cont.) • Alter existing table to hash organization (cont.) • Table space set in AREOR status • Overflow index is created and set in RBDP status • Updates and deletes allowed • Keys cannot be updated • Inserts allowed after overflow index is rebuilt • Not a sparse index at this time • Must REORG table space to instantiate hash access • SHRLEVEL NONE and REORG by part are not supported • REBIND

8. Implementing Hash Access (cont.) • REORG • AUTOESTSPACE(YES/NO) • Estimate the hash space size • Targets about 8% - 15% overflow • Catalog value HASHSPACE remains unchanged • Catalog value HASHDATAPAGES updated with new allocation • FREEPAGE is ignored • PCTFREE is ignored for AUTOESTSPACE(NO) • PCTFREE has a new meaning for AUTOESTSPACE(YES) • Determines target hash space, then increases it by PCTFREE • PCTFREE is ignored for the overflow area

9. Hash Organization (cont.) • REORG (cont.) • Recommendation • Even though AUTOESTSPACE(YES) makes sizing the hash area easy, it’s not fool proof • Overflow estimate is less accurate for varying length rows • If DSSIZE is not large enough to accommodate the hash area and overflow, SQL and utilities can fail for a PBR • It’s expensive for overflows when the hash and overflow are in different data sets of a PBG

10. Implementing Hash Access (cont.) • LOAD • Does not sort input • Hashes each row • FREEPAGE and PCTFREE are ignored • SLOWWWWWWWW • Recommendation • Create non-hashed table • LOAD • ALTER ADD HASHED • REORG • Instead of creating a new table, you can drop hash organization on an existing table, but…UH OH!

11. Implementing Hash Access (cont.) • ALTER TABLE DROP HASH • Overflow index is immediately dropped • Table space is placed in REORP status – must REORG • Cannot recover to a point prior to the DROP • Recommendation • When defining the unique key think carefully - changing it requires a DROP • If you drop hash altogether, consider adding an index

12. Birds Eye View of Index Access - SELECT

13. Bird’s Eye View of Hashing - SELECT

14. Bird’s Eye View of Hashing – SELECT (cont.) • Recommendation • Tables with multi-level indexes are good candidates for Hash Access • Keep the overflow index small – it will increase the odds of finding the index pages in the buffer pool

15. A Bird’s Eye View of Hashing - INSERT • Unique key and hash algorithm determine page number and page location • If another row is at this location, row is added to the collision chain, also known as the Anchor ID Map • There can be several collisions on a single chain • An overflow does not occur until the page is full • The row is stored in the overflow area and the overflow index is updated

16. A Bird’s Eye View of Hashing – INSERT (cont.)

17. A Bird’s Eye View of Hashing – INSERT (cont.) • Recommendation • Tables with high insert activity may not be good candidates for Hash Access

18. Bird’s Eye View of Hashing - UPDATE • Unique key and hash algorithm determines page number and page location • If row does not exist on page, access the overflow index • If row does exist on page • If row length decreased or stayed the same, update in place • If row length increased, move row to another place on the page • If row length increased, but no more room on page • Replace row with a pointer record • Move indirect reference to overflow • No need to index indirect reference

19. Bird’s Eye View of Hashing – UPDATE (cont.) • Recommendation • Tables with high update activity and varying length rows may not be good candidates for Hash Activity • VARCHAR, VARGRAPHIC… • Compression

20. Bird’s Eye View of Hashing – Range Predicate Clustered Data Hashed Data

21. Bird’s Eye View of Hashed Data – Range Predicate (cont.) • Recommendation • Tables that have queries with range predicates are poor choices for hashed tables • What used to be clustered will now be random • If you must use a query with a range predicate, consider creating an index to access the data • You may be able to force index access, but the query may still require a RID sort

22. Hash Organization • Header page • HPGHASHMOD – number of pages in the Hash Area • Rounded down to the nearest prime number • HPGHASHFIXIDMAP – number of hash Anchor ID Map entries • More on that later • HPGLASTHASHPG_P - page number of last page in hash area

23. Hash Organization (cont.) • Space Map • Hashed inserts do not have to use the space map to determine if a page has room • Hash Area segment entries are not chained - SEGNEXT always zero • Free space bit indicators are still maintained • During creation, all space maps are formatted for the Hash Area • There will always be a dictionary and system page segments, even if the space is not compressed or versioned • If more system pages are needed, extra segments will be allocated in the overflow

24. Data/Overflow Page • The first x14 bytes contain header information • Page number, free space management, number of rows • The RID map is at the bottom of the page • Each RID position contains an offset to the row in the page

25. Hash Home Page • PGCOMB contains hash home page bit • Hash header • PGAPBEGIN - pointer to beginning of data rows • Anchor ID Map contains collision chains • The number of anchors, HPGHASHFIXIDMAP, is stored in the header page • The number of IDs vary from table to table based on page and row size

26. Non-Hash Data Row • RID Map • Contains offset of row location • Can have up to 255 RID values on one page • Row header • PGSFLAGS • Pointer • Indirection • Compressed • Versioned • PGSLTH – row length • PGSOBD - OBID of table • PGSID - RID map ID or version number

27. Hash Row • PGSOBD is not needed for UTS • One table per table space • Hash home pages reuse this value to point to the hash anchor ID map or the next row in the collision chain • The first bit in the second byte indicates if this is the last entry in the chain

28. Hash Algorithm • Key is passed to the hash algorithm • Returns a double word integer - HASHEDINT • Page Number = HASHEDINT % HPGHASHMOD • Anchor ID = HASHEDINT % HPGHASHFIXIDMAP • If a row already exists at this anchor, the new row is chained

29. Accessing a Hashed Row

30. Anchor ID Map and Overflow • Second byte of anchor ID is updated when overflow occurs • It doesn’t necessarily mean there was a collision

31. Real Time Statistics (RTS) • SYSTABLESPACESTATS.DATASIZE • Total number of data bytes in the table • SYSTABLESPACESTATS.TOTALROWS • Total number of rows in the table • SYSINDEXSPACESTATS .TOTALENTRIES • Total number of overflow rows

32. Real Time Statistics (RTS) • SYSTABLESPACESTATS. HASHLASTUSED • Last time the table was accessed by hash • SYSTABLESPACESTATS. REORGHASHACCESS • Number of hash accesses • SYSTABLESPACESTATS. REORGSCANACCESS • Number of table space scans

33. RUNSTATS • No new statistics collected • Columns for sparse index not valid (contains -2) • SYSIBM.SYSINDEXPART • LEAFDIST • FAROFFPOSF • NEAROFFPOSF • SYSIBM.SYSCOLUMNS • COLCARDF • HIGH2KEY • SYSIBM.INDEXES • CLUSTERRATIOF • LEAFNEAR • LEAFFAR  • LOW2KEY • LEAFDIST

34. Explain & Plan Table • ACCESSTYPE • H – hash access • HN – hash access using an IN predicate • Some SQL may be converted by DB2 to IN predicate • MH – multiple index scan using the hash overflow index

35. Sizing Your Hash Area • Delicate balance between storage utilization and GETPAGE activity • Slow response and high CPU if more than one GETPAGE is needed • How much are you willing to pay for storage to get excellent response? • All of this can be measured! GETPAGE STORAGE

36. Hash Storage Efficiency

37. Hash Access GETPAGE Counts

38. Hash vs. Non-hash Access GETPAGE Counts • Range predicates on a hashed table can be disastrous • SELECT * FROM DMD.DMDPBGTB01 WHERE COL1_CHAR8 LIKE 'ABC%'; • Hash table space • 32,049 table GETPAGE requests • Non-hash table space with clustering unique key • 6 table GETPAGE requests • 3 index GETPAGE requests • Can add extra index to hash

39. Summary • Choose hash candidates carefully • Tables with mostly random access • Tables with multi-level unique indexes • Tables with low insert activity • Tables with update activity should have static length rows • Monitor to ensure continued efficiency • As overflow area grows, so does GETPAGE counts • RTS TOTALENTRIES • Use REORG AUTOESTSPACE(YES) • Beware of table space scans • RTS REORGSCANACCESS

40. Donna Di CarloBMC Softwaredonna_di_carlo@bmc.com Session DB2 10 Hash Access: Access Path or Collision Course?