microsoft powerpoint - statenc.ppt [read-only]

Download Microsoft PowerPoint - STATENC.ppt [Read-Only]

Post on 27-Jan-2015

109 views

Category:

Documents

2 download

Embed Size (px)

DESCRIPTION

 

TRANSCRIPT

  • 1. State of North Carolina DB2 for z/OS Utilities Topics August 27th, 2008Craig Friske, IBM, friske@us.ibm.com1

2. Agenda Usability with Listdef and Templates COPY/RECOVER/QUIESCE/MODIFY REORG RTS RUNSTATS LOAD Parallel index build 2008 IBM Corporation We will cover the best practices, including keyword specifications recommended for those utilities that are most significant within the utilities suite because they are used often or may consume large amounts of resources. For several of these utilities, building indexes is an important part of processing, so we will cover some of the details of that process to see how is may affect space needs and performance. Sorting is a critical part of building indexes, and its used at various times for many for other phases of utilities as well. DFSORT is always used as of DB2 V8, so we will look at some of the characteristics and recommendations for DFSORT. This involves using dynamic allocation of sort work data sets and why you should let DB2 manage those data sets when possible. Along the way there will be a few samples to better understand the concepts.2 3. Wildcarding/Allocation Overview Improved utility processing for groups of objectsAvoid job stream changes when application changesSupports table spaces, index spaces, or both Generate object listsLISTDEF statement to generate a list of objects wildcarding (e.g. dbname*) explicit lists INCLUDE or EXCLUDE objects Dynamically allocate datasetsTEMPLATE statement to define datasets intelligent dataset sizing variables (Job, Utility, Object, Date, Time, ... etc.) Control StatementsOPTIONS PREVIEW, EVENT (halt, skip, RC) 2008 IBM Corporation 3 4. Wildcarding using LISTDEF List Example: Process objects x, y, and z in database DBA With V6 QUIESCE TABLESPACE DBA.X TABLESPACE DBA.YTABLESPACE DBA.Z COPY TABLESPACE DBA.X COPYDDN (C1,C2) RECOVERYDDN(C3,C4) TABLESPACE DBA.Y COPYDDN (..,..) RECOVERYDDN(..,..) TABLESPACE DBA.Z COPYDDN (..,..) RECOVERYDDN(..,..) With V7 LISTDEF X INCLUDE TABLESPACE DBA.*EXCLUDE TABLESPACE DBA.ATEMPLATE T1 DSNAME((&DB..&TS..&LOCREM.&PRIBAC..D&JDATE.) QUIESCE LIST XCOPY LIST X COPYDDN(T1,T1) RECOVERYDDN(T1,T1) 2008 IBM Corporation 4 5. Dynamic Allocation TemplatesTemplate example: Allocate image copy datasets (2 local, 2 remote)V6 - 4 DD statements for each object //C1 DD DSN=DBA.X.LP.D20020409.,UNIT=..,VOL=SER=..,DISP= //C2 DD DSN=DBA.X.LB.D20020409.,UNIT=..,VOL=SER=..,DISP= //C3 DD DSN=DBA.X.RP.D20020409.,UNIT=..,VOL=SER=..,DISP= //C4 DD DSN=DBA.X.RB.D20020409.,UNIT=..,VOL=SER=..,DISP= //SYSIN DD *V7 - a single template for local and remote//SYSIN DD *TEMPLATE A DSNAME(&DB..&TS..&LOCREM.&PRIBAC..D&DATE.)Generated datasets DSN=DBA.X.LP.D2002099 April 8th Local Primary for DBA.X DSN=DBA.X.LB.D2002099 DSN=DBA.X.RP.D2002099 DSN=DBA.X.RB.D2002099 2008 IBM Corporation5 6. Utilities supported Utility LISTDEF TEMPLATE CHECK DATANoYes CHECK INDEX Yes Yes CHECK LOB NoYes COPYYes Yes COPYTOCOPYYes Yes LOADNoYes MERGECOPY Yes Yes MODIFY RECOVERY Yes n/a MODIFY STATISTICS Yes n/a QUIESCE Yes n/a REBUILD INDEX Yes Yes RECOVER Yes n/a REORG INDEX Yes Yes REORG TABLESPACEYes Yes REPORTYes n/a RUNSTATSYes n/a UNLOADYes Yes 2008 IBM Corporation 6 7. Automatic Space ManagementSliding secondary allocation quantity sizeApplies to DB2 managed pagesets onlyTries to avoids VSAM maximum extent limit errorsCan reach maximum dataset size before running out of extentsUses cylinder allocation Default PRIQTY 1 cylinder for non-LOB tablespaces and indexes 10 cylinders for LOB tablespaces Improved SQL SELECT and INSERT performance 2x improvement relative to track allocationCan be used for New pagesets: No need for PRIQTY/SECQTY values Existing pagesets: SQL ALTER PRIQTY/SECQTY values to -1 plus schedule a REORG 2008 IBM Corporation7 8. COPY Best Practices COPY PARALLEL keyword provides parallelism for lists of objects (including partitions) CHECKPAGE YES (default in DB2 9, no longer sets copy pending, look for RC=8!) Maximize other utilities access to objects while copying a list with SHRLEVEL CHANGE and OPTIONS EVENT(ITEMERROR,SKIP) Keeps objects in the list in UTRW state *only* as each object is being copied instead of for the duration of the COPY utility UTRW utility allows read/write access by applications, but no access for exclusive utilities Incremental copy rule-of-thumb: Consider using incremental image copy if 5% in data sharing, >10% in non-data sharing REORGINSERTS (number of records inserted since the last Reorg)/TOTALROWS > 25% REORGDELETES (number of records deleted since the last Reorg)/TOTALROWS > 25% EXTENTS (number of extents) > 254 REORGDISORGLOB (number of LOBs inserted since the last Reorg that are not perfectly chunked)/TOTALROWS > 50% SPACE > 2 * (DATASIZE / 1024) (when free space is more than used space) REORGMASSDELETE > 0 (mass deletes on seg tsp and DROP on multi-table tsps) RUNSTATS PERCDROP > 10% SYSIBM.SYSLOBSTATS.ORGRATIO < 50% (changed to a value 0-100 in PQ96460 on V7/V8) (NEARINDREF + FARINDREF) / CARDF > 10% non-data-sharing, > 5% if data sharing FAROFFPOSF / CARDF > 10% Or, if index is a clustering index, CLUSTERRATIOF < 90% (irrelevant if predominantly randomaccess) Other Tsp is in REORP or adv reorg pending status (AREO*) as result of an ALTER TABLE stmnt Index on the tsp is in adv REBUILD pend state (ARBDP) as result an ALTER stmnt 2008 IBM Corporation25 26. Reorg table space V9 recommendations Consider running REORG TABLESPACE in the following situations: Real-time statistics (TABLESPACESTATS) REORGUNCLUSTINS (number of records inserted since the last Reorg that are not well- clustered)/TOTALROWS > 10% Irrelevant if predominantly random access REORGUNCLUSTINS is only an indication of the insert behavior and is correlated to the cluster ratioonly if there are no updates or deletes. To prevent DSNACCOR/X from triggering on these, identifysuch objects and put them in exception list (REORGNEARINDREF+REORGFARINDREF (number of overflow rows since the last Reorg))/TOTALROWS > 5% in data sharing, >10% in non-data sharing REORGINSERTS (number of records inserted since the last Reorg)/TOTALROWS > 25% REORGDELETES (number of records deleted since the last Reorg)/TOTALROWS > 25% EXTENTS (number of extents) > 254 REORGDISORGLOB (number of LOBs inserted since the last Reorg that are not perfectly chunked)/TOTALROWS > 50% SPACE > 2 * (DATASIZE / 1024) (when free space is more than used space) REORGMASSDELETE > 0 (mass deletes on seg tsp and DROP on multi-table tsps) RUNSTATS use RUNSTATS statistics as a trigger to consider running REORG Dont PERCDROP > 10% SYSIBM.SYSLOBSTATS.ORGRATIO < 50% (changed to a value 0-100 in PQ96460 on V7/V8) (NEARINDREF + FARINDREF) / CARDF > 10% non-data-sharing, > 5% if data sharing FAROFFPOSF / CARDF > 10% Or, if index is a clustering index, CLUSTERRATIOF < 90% (irrelevant if predominantly randomaccess) Other Tsp is in REORP or adv reorg pending status (AREO*) as result of an ALTER TABLE stmnt Index on the tsp is in adv REBUILD pend state (ARBDP) as result an ALTER stmnt 2008 IBM Corporation26 27. Reorg table space V9 recommendations Consider running REORG TABLESPACE in the following situations: Real-time statistics (TABLESPACESTATS) REORGUNCLUSTINS (number of records inserted since the last Reorg that arenot well-clustered)/TOTALROWS > 10% Irrelevant if predominantly random access REORGUNCLUSTINS is only an indication of the insert behavior and is correlated tothe cluster ratio only if there are no updates or deletes. To prevent DSNACCOR/Xfrom triggering on these, identify such objects and put them in exception list (REORGNEARINDREF+REORGFARINDREF (number of overflow rows sincethe last Reorg))/TOTALROWS > 5% in data sharing, >10% in non-data sharing REORGINSERTS (# of records inserted since the last Reorg)/TOTALROWS >25% REORGDELETES (# of records deleted since the last Reorg)/TOTALROWS >25% EXTENTS (number of extents) > 254 REORGDISORGLOB (number of LOBs inserted since the last Reorg that are notperfectly chunked)/TOTALROWS > 50% SPACE > 2 * (DATASIZE / 1024) (when free space is more than used space) REORGMASSDELETE > 0 (mass deletes on seg tsp and DROP on multi-tabletsps) Other Tsp is in REORP or adv reorg pending status (AREO*) as result of an ALTERTABLE stmnt Index on the tsp is in adv REBUILD pend state (ARBDP) as result an ALTERstmnt 2008 IBM Corporation 27 28. Reorg Index recommendationsConsider running REORG INDEX in the following cases: Real-time statistics (SYSINDEXSPACESTATS) REORGPSEUDODELETES (number of index entries pseudo-deletedsince the last Reorg)/TOTALENTRIES > 10% in non-data sharing, 5% ifdata sharing as pseudo-deleted entry can cause S-lock/unlock in Insertfor unique index REORGLEAFFAR (number of index leaf page splits since the last Reorgand the new leaf page far from the original leaf page)/NACTIVE > 10% REORGINSERTS ( number of index entries inserted since the lastReorg)/TOTALENTRIES > 25% REORGDELETES ( number of index entries inserted since the lastReorg)/TOTALENTRIES > 25% REORGAPPENDINSERT / TOTALENTRIES > 20% EXTENTS (number of extents) > 254 RUNSTATS LEAFFAR / NLEAF > 10% (NLEAF is a column in SYSIBM.SYSINDEXESand SYSIBM.SYSINDEXPART) PSEUDO_DEL_ENTRIES / CARDF > 10% for non-data sharing and > 5%for data sharing Other The index is in advisory REORG-pending status (AREO*) or advisory-REBUILD-pending status (ARBDP) as the result of an ALTER statement 2008 IBM Corporation 28 29. RUNSTATS Best PracticesRUNSTATS SHRLEVEL CHANGE for availability Collect only column stats on columns used in SQLpredicates Use the Statistics Advisor to detect which stats to collect SAMPLE reduces CPU time when gathering column stats KEYCARD provides valuable info for little processing cost(s