Transcription
Data Warehousing on System z:Best Practices with DB2 for z/OSWillie FaveroIBM Silicon Valley LabData Warehousing on System z Swat Team(DB2 SME)Thursday, August 9, 20121:30 PM – 2:30 PMSession Number: 11954#zdwdb2Copyright IBM Corporation 2012All rights reserved.
For Twitter, use hashtag #zdwdb2 for this sessionPlease Note:Click to edit Master text styleswithout notice at IBM’s sole discretion.regardingpotential future products is intended to outline our general product direction InformationSecondleveland it should not be relied on in making a purchasing decision.The information mentioned regarding potential future products is not a commitment, promise, orobligationto deliver any material, code or functionality. Information about potential future legalThirdlevelproducts may not be incorporated into any contract. The development, release, and timing of anyfuture features or functionality described for our products remains at our sole discretion. Fourth level Fifth level IBM’s statements regarding its plans, directions, and intent are subject to change or withdrawalPerformance is based on measurements and projections using standard IBM benchmarks in acontrolled environment. The actual throughput or performance that any user will experience willvary depending upon many factors, including considerations such as the amount ofmultiprogramming in the user's job stream, the I/O configuration, the storage configuration, and theworkload processed. Therefore, no assurance can be given that an individual user will achieveresults similar to those stated here.10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide2 of3755Slide2 of
For Twitter, use hashtag #zdwdb2 for this sessionAcknowledgements and Disclaimers: Availability. References in this presentation to IBM products, programs, or services do not imply that they will beClicktocountrieseditin whichMastertext stylesavailable in allIBM operates.The workshops, sessions and materials have been prepared by IBM or the session speakers and reflect their ownSecondlevelviews. They are providedfor informational purposes only, and are neither intended to, nor shall have the effect ofbeing, legal or other guidance or advice to any participant. While efforts were made to verify the completenessand accuracy of the information contained in this presentation, it is provided AS-IS without warranty of any kind,Thirdlevelexpress or implied. IBM shall not be responsible for any damages arising out of the use of, or otherwise related to,this presentation or any other materials. Nothing contained in this presentation is intended to, nor shall have theeffect of, creatingany warranties or representations from IBM or its suppliers or licensors, or altering the termsFourthleveland conditions of the applicable license agreement governing the use of IBM softwareAll customerexamples described are presented as illustrations of how those customers have used IBM productsFifthleveland the results they may have achieved. Actual environmental costs and performance characteristics may vary.by customer. Nothing contained in these materials is intended to, nor shall have the effect of, stating or implyingthat any activities undertaken by you will result in any specific sales, revenue growth or other results. Copyright IBM Corporation 2012. All rights reserved.–U.S. Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contractwith IBM Corp.IBM, the IBM logo, and ibm.com are trademarks or registered trademarks of International Business Machines Corporation in the UnitedStates, other countries, or both. If these and other IBM trademarked terms are marked on their first occurrence in this information with atrademark symbol ( or ), these symbols indicate U.S. registered or common law trademarks owned by IBM at the time this informationwas published. Such trademarks may also be registered or common law trademarks in other countries. A current list of IBM trademarks isavailable on the Web at “Copyright and trademark information” at www.ibm.com/legal/copytrade.shtmlOther company, product, or service names may be trademarks or service marks of others.10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide3 of3755Slide3 of
For Twitter, use hashtag #zdwdb2 for this sessionWhat Do “I” Mean by Best PracticesThis presentationconsistofClickto edit Mastertext styles– Stuff I have Secondlevelbeen asked about– Things I have tried that have worked Third level– Techniques I’ve read about that made sense Fourth level Fifth level Please consider this a starting point, not the conclusion All put in one place for you to decide what’s goodand what isn’t10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide4 of3755Slide4 of
For Twitter, use hashtag #zdwdb2 for this sessionHere’s My “Top 10” Best Practices PartitioningClickto edit Master text stylesParallelismSecondlevelDatacompressionThird levelIndex Compression (maybe not)Fourth levelStar (Snowflake) SchemaFifth levelQuery ProcessingManage statisticsMaintain accounting and statistical dataAre backups necessaryDSNZPARMsBuffer Pools10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide5 of3755Slide5 of
For Twitter, use hashtag #zdwdb2 for this sessionPartitioning LargetoobjectsClickedit Master text styles– ManageabilitySecondlevel– PotentialThirdlevel performance improvements ParallelismFourth¾level# CPs and # of PartitionsScan only necessary partitionsFifth level– Should almost always be considered for fact table Use date key for fact table partitioning if available– You could have up to 4096 partitions10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide6 of3755Slide6 of
For Twitter, use hashtag #zdwdb2 for this sessionPartitioning Key Date columnif availableClickto edit Mastertext styles– Year? levelQuarter? Month? Day even?Second– SometimesThirdlevel daily partitions can be attractive option Could be easier to manageFourth levelSurrogate key if not a usable date columnFifthlevel– Useinteger; short key– Use BIGINT; if more values are necessary10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide7 of3755Slide7 of
For Twitter, use hashtag #zdwdb2 for this sessionPartition Pruning Clickto edit Master text stylesSELECTFROM part tableSecondlevelWHERE LASTNAME ‘favero’Third levelPartition by dateFourth levelFifth levelPart 1Part 2favero10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesPart 3Part 4Part xfaverofaveroCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllPart x 1Part x nSlide8 of3755Slide8 of
For Twitter, use hashtag #zdwdb2 for this sessionWhat About Universal Table Spaces? IntroducedDB2 9text stylesClickto editinMasterImprovedin DB2 10SecondlevelThe bestThirdlevelof segmented and partitioning in oneobject levelFourth– Partition-by-growthFifthlevel– Range-partition– Both can have to 128 TB of data– Mass DELETE10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide9 of3755Slide9 of
For Twitter, use hashtag #zdwdb2 for this sessionTable Space Definition No ortolowINSERT/UPDATEClickeditMaster text stylesrate– FREEPAGESecondlevel 0– PCTFREEThirdlevel 0SECQTYset to -1FourthlevelPRIQTYFifthlevelconsider setting to -1LOCKSIZECLOSENOT LOGGED10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide1010ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionTable Space Definition SEGSIZEClickto edit Master text styles– If 0, range-partitioned universal table space if NUMPARTS isSecondlevelalso specified– 0 andlevelNUMPARTS specified, legacy partitioned table spaceThirdFourth levelDSSIZEFifth level NUMPARTS – Defines maximum size of each partition– 1G, 2G, 4G, 8G, 16G, 32G, 64G, 128G, 256G– Indicates partitioned table space– 1 to 4086 partitions can be specified– Page size and DSSIZE drive max NUMPARTS value10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide1111ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionWhat About Dimension Tables? They tohavealsoClickeditdesignMasterconsiderationstext stylesUse integerSecondlevelsurrogate keys if date not availableUsinglevelinteger prevents fact table fromThirdunnecessaryFourthlevel widthFifth level10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide1212ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionParallelism Click to edit Master text stylesSecond levelThird levelFourth levelFifth level10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide1313ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionCP Parallelism (Behind the Scenes)ClicktoeditMastertextstylesMulti-Tasking - How does DB2 do it? Second level Spawning parallel tasks: z/OS preemptable SRBs are used for work done inparallel. Originating Task (TCB) handles SRB creation, cleanup and data Third levelmerging. Fourth levelSRBOriginating Task TCBParallel TasksSRB Fifth level Preemptable SRBs:SRB–Synchronize originating and parallel tasks–Introduced with Enclave Services (MVS 5.2)–Inherit dispatching priority of allied address space. Therefore all work isdone at the same priority (goodness) Originating task does not control scheduling or which CP an SRB is run on –z/OS handles scheduling. DB2 handles synchronization through suspending and resuming tasks1410504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide1414ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionCP Parallelism (Behind the Scenes) Parallel tasks are started at OPEN CURSOR* Click to edit Master text stylesƒApplication might be able to take advantage of this to Secondlevelachieve inter-queryparallelism:DECLARE CURSOR C1 FOR SELECT COUNT(*) FROM ORDERS ThirdlevelWHEREINVOICE AMT 4000.00DECLARE CURSOR C2 FOR SELECT PARTNAME FROM PARTS Fourth levelWHERE INVENTORY AMT 200 Fifth levelParallel Task #1OPEN CURSOR C1Parallel Task #2Parallel Task #315Parallel Task #1OPEN CURSOR C2Parallel Task #2.Parallel Task #3.FETCH C1FETCH C2*Exception if RID sort, but no data sort, then //ism starts at first fetch (same as without //ism)10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide1515ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionCP Parallelism (Behind the Scenes)ClicktoeditMastertextstylesParallel Degree Determination"Why islevelthe degree sometimes less than the number of parts?" Second Third level Fourthlevel Optimaldegreefor parallel group is determined at BIND/PREPARE timeƒAlso called "Planned BIND degree" - shown in EXPLAIN output Fifth level Optimal degree determined by considering:ƒNumber of table space partitionsƒEstimated I/O cost of largest partitionƒEstimated CP cost considering:–Processing cost–MIPS rating of machine–Number of CPs on-line (used for CP parallelism only) Degree determination deferred if access path dependent on host variable1610504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide1616ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionDetermining the degree of parallelism DB2choosesthe smallestthat will still deliver the best Clickto editMasterdegreetext stylespossible elapsed timeSecond leveldegree of parallelismThird levelFourth levelI/O-intensive:Degree of parallelism approaches the number ofpartitionsFifth level With the shared data model, DB2 has the flexibility to choose the Processor-intensive: Degree of parallelism approaches the numberof processors (for DB2 9 - times 4, for DB2 10 – times 2)CP CP CP CP CP CP CP CP CP CP X 4 (or 2)Additionally, skews in the data organization can be detected andcompensated for in choosing the degree of parallelism1710504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllParallel Task #1Parallel Task #2Parallel Task #3Slide1717ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionStar (Snowflake) Schema Star(snowflake)schema astylesrelational database schemaClicktoeditMastertextfor representing multidimensional dataSometimesgraphically represented as a ‘star’ orSecond level‘snowflake’Third– Data levelis stored in a central fact table– Surrounding additional dimension tables hold information about eachFourthlevelperspectiveof the data– Example: store "facts" of the sale (units sold, price, .) with product, time,customer,Fifthleveland store keys in a central fact table. Store full descriptivedetail for each keys in surrounding dimension tables. This allows you toavoid redundantly storing this information (such as product description) foreach individual transactionComplex star schema parallel queries include the acts ofjoining several dimensions of a star schema data set (likepromotion vs. product).Two specific DSNZPARMs must be setup accordingly:STARJOIN and SJTABLES.Proper index design must be present in the star schematables.10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide1818ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionStar Schema DimensionClick to edit Master textTablestylesSecond levelThird levelFourthlevelDimensionTableFifth levelFactDimensionTableTableDimensionTable10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesDimensionTableCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllStar schema Snowflake schemaSlide1919ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionStar Schema Click to edit Master textStorestylesSecond levelThird levelFourthlevelProductFifth levelSaleDateThree or more tables can bejoined at one timeZPARMs SJTABLES andSTARJOIN must be setzIIP eligiblePromotionAssociateStar schema Snowflake schema10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide2020ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionReferential Integrity Goodtoidea just betextcarefulClickedit MasterstylesApply afterLOAD to improve load performanceSecondlevelIntegrityverses performance?Thirdlevel– What levelare the tradeoffsFourth– MaintainFifthlevel integrity– Increase cost of INSERT, DELETE, and UPDATEprocessing10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide2121ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionQueries IncludeWHERE predicateClickto partitioningedit Master keytext asstyleswhen possibleSecondlevelEXPLAIN,Thirdlevel Accounting and Statistics traces– ImprovesFourthlevellimited partition scan EXPLAIN’s Page-Range columnFifthlevel– Knowwhen to STOP tuningTake advantage of technology– Table Expressions– Materialized Query Tables (MQT)– Common Table Expressions (CTE)10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide2222ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionManage Amount of Data Maintained If datetopartitioningoff partitionsClickedit Masterkey,textrollstylesArchive levelSecond– ConsiderThirdlevel offloading partitions no longer accessed Use a technique that removes data from fact table but stillFourthlevelleavesit available to queries if necessaryFifth level10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide2323ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionDB2 Data Compression Compressionbe considered for a dataClickto edit shouldMasteralwaystext styleswarehouseSecondlevelSavings areusually greater than 50%– Havelevelseen as high as 80% in certain situationsThirdOverhead on INSERTFourthlevel– minimalon SELECT– Warehouse queries dominated by sequential prefetch, whichFifthlevelbenefitfrom DB2 compression.Not all rows in a table spaces can be compressed– If the row after compression is not shorter than the originaluncompressed row, the row remains uncompressed. Compression dictionary size– 64K (16 X 4K page) of storage in the DBM1 address space– Dictionary goes above the bar in DB2 Version 8 and later releases Faster hardware, faster compression See hardware chart toward end of presentation10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide2424ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionDictionary – 4K Page Size Click to edit Master text stylesSecond levelThird levelFourth levelFifth levelData pages (18-?)Dictionary pages (20-17)Space map page (1)Header page (0) The dictionary is created by the LOAD and/or REORG utilities only–It occupies: 4K – 16 pages8K - 8 pages16K - 4 pages32K - 2 pagesThe compression dictionary follows the header and first space map pages (next slide)Dictionaries can be at the partition level (Careful, you could have 4096 partitions10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide2525ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionData Compression Rows toareeditcompressedon stylesINSERTClickMaster textFor an UPDATESecond– Expand,levelupdate, then re-compressed row– UPDATEThirdlevelhas the potential to be expensiveChanges (INSERT & UPDATE) are logged inFourthlevel formatcompressed– PossibleFifthlevel reduced logging cost– Active log reductions carried over to the archive logs Larger page sizes may result in better compression.– Resulting rows after compression are variable length– You might be able to fit more rows with less wasted space ina larger page size. You cannot turn compression on for the catalog,directory, work files, or LOB table spacesIndex compression does not use a dictionary10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide2626ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionPossible Performance Gain Whentocompressionis on,data pages are broughtClickedit Master textstylesinto bufferpool in compressed stateSecondlevel– IncreasingThirdlevel the number of rows in the same size poolcould increase buffer pool hit ratioFourthlevel hit ratio could reduce I/O necessary to– IncreasingsatisfyFifthlevelthe same number of getpage requests.If Compression doubles the number of rows perpage– When DB2 loads that page in a buffer pool, it will beloading twice as many rows. Less I/O is always a good thing.10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide2727ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionDB2 Index Compression . Index compressionis newDB2 9 for z/OSClickto edit MastertexttostylesPage levellevelcompressionSecondUnlike data row compression:Thirdlevel– Buffers contain expanded pagesFourth– Pageslevelare decompressed when read from disk– PrefetchFifthlevelperforms the decompression asynchronously– A buffer hit does not need to decompress– Pages are compressed by the deferred write engine Like data row compression:– An I/O bound scan will run faster DSN1COMP utility can be used to predict space savingsIndex compression saves space, it’s not for performance10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide2828ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionIndex Compression: Performance CPU costis Mastermostly inconsequential.Most of theClickto edittext stylescost is asynchronous,the exception being hird levelwith a poor buffer hit ratio.Fourth levelExample: Suppose the index would compress 3-to-1.FifthYoulevelhave three options .1. Use 8K buffer pool. Save 50% of disk. No change in buffer hit ratioor real storage usage.2. Use 16K buffer pool and increase the buffer pool size by 33%. Save67% of disk, increase real storage usage by 33%.3. Use 16K buffer pool, with no change in buffer pool size. Save 67%of disk, no change in real storage used, decrease in buffer hit ratio,with a corresponding increase in synchronous CPU time.10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide2929ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this session .DB2 Index Compression The CItoSizea compressedindex on disk is always 4KClickeditofMastertext stylesA 4K expandsSecondlevelinto a 8K or 16K buffer, which is the DBA’schoice. This choice determines the maximumThirdlevel ratio.compressionFourthlevel of key prefix and RID ListsCompression– A RidList describes all of the rows for a particular index keyFifthlevel– An index with a high level of non-uniqueness, producing long RidLists, achieves about 1.4-to-1 compression– Compression of unique keys depends on prefix commonality10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide3030ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionDo Not Ignore Statistics NeededonlyMasterif you planto run queriesClickto edittext stylesLOAD/REORG/REBUILDutilities with inline statsSecondlevel– CarefulThirdlevel- Do not gather all stats you may needRUNSTATSFourthlevel– Useto gather stats for specific quer needsFifthlevel– Use Data Studio’s Statistics Advisor to determineRUNSTATS control cards10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide3131ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionAre Backups Necessary? Does todatato berecovable?ClickeditneedMastertextstylesCan it belevelreloaded with last refresh?SecondIs warehousedata included in DR planThirdlevelFourth levelFifth level10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide3232ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionPlan on Saving Performance Data DB2 tracesClickto edit Master text styles– AccountingSecondlevelrecords (SMF 100) Classes 1,2 and 3Thirdlevel If using packages, classees 7 and 8Fourthlevelrecords (SMF 101)– StatisticsClasses 1, 3, 4, 5, and 6Fifth level– Design a performance database See Omegamon EXPLAIN outputs10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide3333ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionIs a Test System Necessary Have totestmimicproductionwarehouse in asClickeditMastertext stylesmany waysSecondlevelas possibleStatisticsThirdlevelCache leveldefinitions – Buffer pools, sort pool, etc Fourth– ProfilesFifthlevelNumber and Speed of processors– DSNZPARM10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide3434ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionDSNZPARM Keywords The twobiggies.Clickto editMaster text styles– DSN6SPRMSecondlevel– DSN6SYSPThirdlevelBut don’tignoreFourthlevel– DSN6FACFifth level To a lesser degree – DSN6GRP (if you are using data sharing)– DSN6ARVP– DSN6LOGP Finally– DSN6SPRC10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide3535ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionDSNZPARMs CDSSRDEFClickto edit(DSN6SPRM)Master text Updatestyles Yes– Set to 1 or ANY, 1 is the defaultSecondlevel Can affect your distributed SQLThirdlevel (DSN6SPRM) Update YesPARAMDEG– Controlsthe max degree of parallelismFourthlevel– Set to 0 if DB2 should decide, otherwise 1-254Fifth levelPick a reasonable value based on the number of CPsPARA EFF (DSN6SPRM) Update yes– 0-100, 50 default – how is parallelism effected 1 less parallelism, 99 more parallelism SPRMPTH (DSN6SPRC) Update No– Default 120, may want a higher value10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide3636ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionMXQBCE With systemparameterClickto edit Mastertext MAX OPT STORstyles(default40MB)Second level– DB2 will try to cap the storage usage for bind/prepareThirdlevelat thevalue set– AttemptingFourthlevel to avoid SQLCODE -904 or storage abendsWhenlevelDB2 gets close to the threshold it willFifthengage in "emergency clipping“– Choosing the lowest cost access path at that point andcontinue processing it– Discarding all others– If the threshold is set too low, then DB2 may have onlycompleted small% of the prepare which may not beenough to really distinguish which is the best plan10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide3737ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionMXQBCE cont les MXQBCE instead– Controlsnumber of join combinations considered bySecondlevelthe DB2 OptimiserThird level– Predictive rather than reactiveFourthlevelto clip access paths early, so as to avoid– AttemptsFifthlevel“emergencyclipping”– Use the formula (2**n)-1– Default of (2**15)-1 32767 prepare will take no worsethan a 15 table join– For a 14 table join it is 6383, 13 table 8191, 12 table4095, 11 table 2047 and 10 table 1023– Do not go below 102310504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide3838ofof3755Slide
For Twitter, use hashtag #zdwdb2 for this sessionDNSZPARM CACHEDYN(DSN6SPRM)Update YesClickto edit Mastertext styles– Set to YESSecondlevel(default)Third levelEDMSTMTCFourthlevel (DSN6SPRM) Update Yes– Installation defined based on the amount of dynamicFifthlevelSQLexpected and how long cache will gatherinformation before wrapping10504: DataSocialWarehousingNetworking forDatabase11965:on theSystemz BestProfessionalPracticesCopyright IBMIBM CorporationCorporation 20122012CopyrightAll rightsrights reserved.reserved.AllSlide3939ofof3755Slide
For Twitter, use hashtag #zdwdb2 for
11965: Data Warehousing on System z Best Practices For Twitter, use hashtag #zdwdb2 for this session 15 Parallel tasks are started at OPEN CURSOR* ƒApplication might be able to take advantage of this to achieve inter-query parallelism: Parallel Task #2 Parallel Task #1 Parallel Task #3 Parallel Task #2 Parallel Task #1
