db2 12 for zos technical overview part 1 - ning
TRANSCRIPT
DB212forz/OSTechnicalOverviewPart1
JohnCampbellDistinguishedEngineerIBMDB2forz/OSDevelopment
LogisticsandQuestions
• Thepresentationisavailablefordownloadfromtheresourceslist
• Thewebcastswillbeavailableonreplay
• Youcansubmitquestionsbytypingintothequestionsareaofyourwebcastcontrolpanelatleast15minutesbeforetheendofthewebcast.
• Questionswillbeansweredastimepermits
• Anyquestionsnotansweredduetotimeconstraintswillbeansweredafterthewebcastandaddedtothe“Resourceslist”
2
Disclaimer/Trademarks
3
© Copyright IBM Corporation 2017. All rights reserved.U.S. Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
THE INFORMATION CONTAINED IN THIS DOCUMENT HAS NOT BEEN SUBMITTED TO ANY FORMAL IBM TEST AND IS DISTRIBUTED AS IS. THE USE OF THIS INFORMATION OR THE IMPLEMENTATION OF ANY OF THESE TECHNIQUES IS A CUSTOMER RESPONSIBILITY AND DEPENDS ON THE CUSTOMER’S ABILITY TO EVALUATE AND INTEGRATE THEM INTO THE CUSTOMER’S OPERATIONAL ENVIRONMENT. WHILE IBM MAY HAVE REVIEWED EACH ITEM FOR ACCURACY IN ASPECIFIC SITUATION, THERE IS NO GUARANTEE THAT THE SAME OR SIMILAR RESULTS WILL BE OBTAINED ELSEWHERE. ANYONE ATTEMPTING TO ADAPT THESE TECHNIQUES TO THEIR OWN ENVIRONMENTS DO SO AT THEIR OWN RISK.
ANY PERFORMANCE DATA CONTAINED IN THIS DOCUMENT WERE DETERMINED IN VARIOUS CONTROLLED LABORATORY ENVIRONMENTS AND ARE FOR REFERENCE PURPOSES ONLY. CUSTOMERS SHOULD NOT ADAPT THESE PERFORMANCE NUMBERS TO THEIR OWN ENVIRONMENTS AS SYSTEM PERFORMANCESTANDARDS. THE RESULTS THAT MAY BE OBTAINED IN OTHER OPERATING ENVIRONMENTS MAY VARY SIGNIFICANTLY. USERS OF THIS DOCUMENT SHOULD VERIFY THE APPLICABLE DATA FOR THEIR SPECIFIC ENVIRONMENT.
TrademarksIBM, the IBM logo, ibm.com, DB2, System z and z/OS are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at “Copyright and trademark information” at www.ibm.com/legal/copytrade.shtml.
Objectives• IntroduceanddiscussthenewfeaturesofDB212forz/OS• Provideplanninginformationformigration• Understandthenewperformancefeatures
4
Agenda• Introduction• Performancefocus– traditionalworkloads• Performancefocus– enablingmodernapplications• Migration• Applicationenablement• Reliability,availability,scalability,security
5
INTRODUCTION
6
DBAFunction
OLTPPerformance
QueryPerformance
• IDAAimprovementstoexpandtonewusecases
• SQL/SQLPLimprovementsfornextwaveofapplications
• 5-10%CPUreductionwithuseofin-memoryfeatures
• 2xincreaseinInsertthroughputfornon-clustered
• Relievetablescalabilitylimits• Simplifylargetablemanagement
• 20-30%CPUreductionforqueryworkloads
• Improveefficiencybyreducingotherresourceconsumption
ApplicationEnablement
• Addresskeycustomerrequirementstoexpanduseofexistingfeatures
• Mobile,hybridcloud,andDevOpsenablement
• Removebiggest24x7inhibitors• Securityandcomplianceimprovements
• Removesystemscalingbottlenecksforhighn-waysystems
• Serviceability,availability
• 80%UNIONALLperformanceimprovement
• Simplifyaccesspathmanagement
Goals
7
QuickHits• Scaleandspeedforthenexteraofmobileapplications
• Over11MillionInsertspersecondmeasuredwhenhitting“sweetspot”• 6trillionrowsinasingletable,withagilepartitiontechnology
• InMemorydatabase• Upto23%CPUreductionforindexlookupwithadvancedin-memorytechniques
• NextGenerationapplicationsupport• 360milliontransactionsperhourthroughRESTfulwebAPI
• Deliveranalyticalinsightsfaster• Upto25%CPUsavingfortraditionalqueryworkloads• Upto2xspeedupformodern(complexOLTPorreal-timeanalytics)workloads
8
PERFORMANCEFOCUSTRADITIONALWORKLOADS
9
PerformanceEnhancements• In-memorycontiguousbufferpools
• Directpageaccessin-memory,greatlyreducedGetPage overhead• HashandLRUchainsnotmaintained• Upto8%CPUreductionmeasuredforOLTP• PGSTEAL(NONE)– improvedinDB212toavoidLRUandhashchainmanagementoverheads• Overflowarea(10%ofVPSIZE,maxof6400buffers)
• Usedincaseobjectsdonotfit• AutomaticallymanagedbyDB2usingFIFOstealalgorithm• Allocatedwhenbufferpoolisallocated,butonlybackedwhenused
• In-memoryindexforfasttraversal(seenextslide)• MoregranularGlobalCommitLSNandReadLSN
• Potentialhugeimprovementinlockavoidance(datasharing)• HelpspacereuseforLOBinsert
10
In-MemoryIndexOptimization• AnewIndexFastTraverseBlock(FTB)isintroduced
• Memoryoptimizedstructureforfastindexlookups• Residesinmemoryareasoutsideofthebufferpool
• Newzparm INDEX_MEMORY_CONTROL• Default=AUTO(minof500MBor20%ofallocatedbufferpoolstorage)
• UNIQUEindexesonly,keysize64bytesorless• DB2automaticallydetermineswhichindexeswouldbenefitfromFTB• DISPLAYSTATScommandshowswhichindexesareusingFTBs• NewSYSINDEXCONTROLcatalogtable
• SpecifytimewindowstocontroluseofFTBsforanindex• NewIFCIDs389and477totrackFTBusage
11
SimpleIndexLook-up:Faster&Cheaper• Upto23%CPUreductionforindexlookupusingDB212In-memoryindextree
12
6%
11%
16%
23%
2 3 4 5
IndexLevels
CPUImprovement(%)fromSimpleLookupinDB212
PerformanceEnhancements…• Avoidschedulingunnecessaryprefetch
• Problem:whenallpagesareinmemory,dynamicprefetch needlesslyschedulesprefetch• WastesCPU,cancause“outofprefetch engine”condition• Attemptstosolvethisinthepastfailed- solvedinDB212• Upto6.8%CPUreductionforOLTP,4.5%forquery
13
PerformanceEnhancements…• INSERTAlgorithm2(seenextslides)• RLFcontrolforstaticpackages• DB2/DASDsynergyenhancements– retrofittoV10,V11
• Exploitz/OSHyperWrite (PPRClogwriteaccelerator)• Upto30%logwritelatencyreduction
• ImprovedIntegrationwithDS8870EasyTiermulti-temperaturemanagement• AvoidI/OdegradationafterREORG
14
INSERTAlgorithm2Performance• Insertworkloadsareamongstthemostprevalentandperformancecritical• Performancebottleneckwillvaryacrossdifferentinsertworkloads
• Indexmaintenance?• LogwriteI/O?• Spacesearch(pagep-lock,pagelatchcontention)• PPRCdiskmirroring• Networklatency• etc
15
INSERTAlgorithm2Performance…• DB212maypotentiallydeliversignificantimprovementfornon-clusteredinsert(e.g.,journaltable
patternwherebothconcurrent insertactivityandspacesearchistheconstraint onoverallinsertthroughput
• AppliestoUTSwithMEMBERCLUSTER(bothwith/withoutAPPEND)• Implementedadvancednewinsertalgorithmtostreamlinespacesearch
• Defaultistousethenewfastalgorithmforqualifyingtablespaces• INSERTALGORITHMzparm canchangethedefault• INSERTALGORITHMtablespaceattributecanoverridezparm
• Yourmileagewillvary• Someinsertworkloadswillseenoimprovement• Somespecificinsertworkloadsmayseesignificantimprovement
• Willshiftthebottlenecktothenextconstrainingfactor
16
INSERTAlgorithm2Performance– ShiftingTheBottleneck…
17
454035302520151050
Application Response DB2 elapsed Class 2 CPU Getpage
Insert Algorithm 2
V11 V12
INSERTAlgorithm2Performance- DB211PMRRecreate…
18
27089453
65417
1460
40000
30000
20000
10000
60000
50000
Throughput (insert/sec) TOTAL CPU per commit (us)V11 V12
24x1/64
UTSPBG with Member Cluster, RLL, with 400 bytes per row, one index,800 concurrent threads, 10 insert per commit
PerformanceEnhancements…• BufferPooladvisorymodetosimulatelargerbufferpools– rollbacktoV11
• LowCPUandrealmemoryoverhead• StatisticsprovidedtoindicateI/Osavings• RetrofittoV11
• StreamlinedClaim/Declaimprocessing• Avoidre-claimoverheadacrossmultiplecommitscopesseriallyreusingpersistentthreadrunning
RELEASE(DEALLOCATE)• OnlineREORGandotherdrainerscanstillbreakin
19
OtherPerformanceEnhancements…• Improvementsinpoolmanagementtosimplifyandremovethescalabilityinhibitors
• EDMpools• LOB/XMLstorage
• OtherImprovements• ReducingDGTTDECLAREoverhead• Removelogforcewritefromidentitycolumnandsequencecachingindatasharing
20
SystemScalingEnhancements• Largen-wayscaling
• ImprovedefficiencyonLPARswithhighnumberofCPs• Loglatchcontentionreduction:testingshowsupto41%CPUreductionand6%throughputimprovementfor
highcontentioncases• BufferPoolscalingimprovements:
• LC23reduction,PLOavoidance• 5-30%CPUimprovementwhenaccessinghotpages
• IRLMlatchcontentionreduction• EDMDBDandskeletonpoolscalabilityimprovements
• Optimizationsfornewhardware• Exploitationofz13decompressionenhancement• Internalstructurechangesforcacheefficiency,moreprocessorprefetch
• Raisetotalbufferpoolsizelimitto16TB• >4Gsizeactivelogdatasets
21
Highlevelperformanceexpectation• SystemandOLTPperformance
• 2-3%CPUreductionwithoutIndexIn-Memoryfeature• 5-10%CPUreductionbyexploitingIndexIn-Memoryfeature• Furtherreductionispossiblewithcontiguousbufferpools,and/orpersistentthreadwith
RELEASE(DEALLOCATE)• Queryperformance
• Widerangeofimprovement• Typically0-20%withoutnewaccesspath• Typically10-40%withnewaccesspath• Upto90%reductionisobservedinourevaluations
• ConcurrentinsertagainstUTSandMEMBERCLUSTER• 5-10%CPUreduction• Throughputimprovementifcurrentbottleneckisspacesearchorpagecontentions
22
InstrumentationEnhancements• MoregranularwaittimesforIFCIDs316(dynamic)and401(static)
• Accumulatedwaittimeduetoglobalcontentionforlocks(brokenoutbytype)• EnhanceIFCIDs53/58statementlevelsectionforPREPARE
• SimilartoINSERT/UPDATE/DELETE• EnhanceSQLperformancetracingaddingRDISectionNumberinIFCIDs53/58• AddbatchjobSTEPnameincorrelationheader• AddREFRESHTABLEtocountsinDSNDQXST(SQLdatasection)• Addworkfile,tempfile usageinformationtoAccountingtrace• EnhancedprecisionforIFCID199datasetI/Ostats- microseconds
23
Accesspath(plan)stability• DynamicSQLplanstability• Staticplanstabilityusability• Preservelocaldynamicstatementcacheatrollback• IntegratedRUNSTATSwithoptimizer
• Automatedupdateofstatisticsprofilesbyoptimizer• Statisticsprofilesupport
• AutomatedupdateforIndexDDL• Inlinestatsusageofprofiles
• SimplifycreationofalltablesrequiredbyExplain• NewADMIN_EXPLAIN_MAINTstoredprocedure
24
DynamicSQLPlanStability• Problem:
• UnstableperformanceofrepeatingdynamicSQLstatements• Environmentalchangescanresultinchangeinaccesspathorperformanceregression,andthiscanbetoughto
manage• RUNSTATS• Applyingsoftwaremaintenance• DB2releasemigration• zparm changes• Schemachanges
• StaticSQLhasseveraladvantages• AccesspathestablishedatBINDtime• Staticplanmanagementgivesadvancedmanagementfunctions
• Objective:extendstaticSQLadvantagestodynamicSQL
25
DynamicSQLPlanStability…• Baseinfrastructure
• OpaqueparameterCACHEDYN_STABILIZATION• Capturevia-STARTDYNQUERYCAPTURESTBLGRP(APP1)…
• Commandwith/withoutmonitoring• Globalvariable
• FREE• EXPLAIN(current,invalid)• Invalidation• LASTUSED(identifystalestatements)• Instrumentation(queryhash,explain,cache+cataloghitratio)• APPLCOMPATispartofmatchingcriteria
• Savequeryandcachestructurestocatalogforsubsequentreuseoncachemis
26
DynamicSQLPlanStability…• Keylimitations
• Literalconcentrationandtemporalstabilizationnotcurrentlyincluded• REBINDsupportnotincluded
• NoPLANMGMT/SWITCH/APREUSE
27
StaticPlanStability:Usability• BINDREPLACEofsameversiontokeepLASTUSED
• FREEPACKAGEimprovements• SelectivelyFREEeitheroriginalorprevious• ChoosetoFREEifinvalidonly• AllowFREEofinactivepackagecopieswhileapplicationisrunning
• REBINDPACKAGEimprovements• SWITCHtoincludeAPREUSESOURCEoptionsoastochooseORIGINALorPREVIOUScopyassourceforAPREUSE• Addressesissueofswitchaccidentallycausinginvalidcopytobecomecurrent• APREUSESOURCEoptionavoidsthetwostepprocessof
• REBINDSWITCHfollowedby• REBINDAPREUSE
28
RUNSTATSEnhancementsforSQLPerformance• RUNSTATS(foraccesspathselectionbenefit)
• CLUSTERRATIOformulaimprovements• Statementcacheinvalidation
• OptionalforRUNSTATS(newdefault)• ForotherutilitiesONLYifobjectsinpendingstatebeforeutilityexecuted
• Profilesupportforinlinestatistics• AutomatedCOUNTforFREQVAL
• AllowDB2tocollecttheskewedvalues• Uptotop100oruntilnoskewforremainingvalues
• OptimizertoautomaticallyupdatestatisticsPROFILEwithRUNSTATSrecommendations• DSNACCOXtorecommendRUNSTATSafterprofileupdate
• SpecifyUSEPROFILEonRUNSTATStocollectcurrentstatisticsrecommendations
29
Enhancedstatisticsprofilemanagement
30
AccessPath
Query
OptimizerInlinestatistics &
RUNSTATS
StatisticsinCatalog
ProfilesinCatalog
AllnewwithV12
TablestructureinCatalog
Create/DropIndex
PERFORMANCEFOCUSENABLING NEWAPPLICATIONS
31
QueryPerformanceEnhancements• Upto25%CPUimprovementfortraditionalqueryworkloads
• Upto2ximprovementformodernSQLapplications• PerformanceimprovementsfornextgenerationSAPapplications• Real-timeanalytics,complexOLTPworkloads
• 100%zIIP eligibilityforparallelquerychildtasks
• ModernapplicationscontainmorecomplexSQLpatterns(targetedinDB212),andmoresorting,joins,stage2predicatesetc.• ThesecomplexpatternsarelesscommonintraditionalOLTP/batch
32
QueryWorkloadCPUandElapsedtimeImprovementfromV11(%)
33
0 20 40 60 80 100
BIDAY-long
SAP/Bw
TPCD
Customer3
Customer1
TPCH-SQLPL
CrystalReports
TPCH30GBNPI
BIDAY-short
Customer2
SAPSFIN
SAPSFIORI
WASPortal
diff(%)
CPU
Elapsed
UNIONALLw/View
ComplexOuterJoin,UDF
Complexreporting,large
sort
Simplequeryorlargedatascan
High-levelPerformanceFocus• Query(RDS)focusbaseduponnew(er)workloads
• ComplexviewsortableUDFs• UNIONALL• Outerjoins• Joinpredicateswith(Stage2)expressions
• CASEexpressions,CASTfunctions,scalarfunctions• Query- GeneralBottlenecks
• Sort/workfile reductions• Reducingpreparecostandfrequency• I/Operformance
• Reduceunnecessaryprefetch scheduling
34
QueryPerformanceFocus• Improveperformanceof
• UNIONALLandouterjoinperformanceenhancements• Performanceissuesaresimilarwithbothtypesofquerypattern• Reducematerializations
• Bypassworkfile usagewhenmaterializationrequired• Trimunnecessarycolumnsfrommaterializations
• PushpredicatesinsideUNIONALLlegsorOUTERJOINqueryblocks• PushORDERBYandFETCHFIRSTintoUNIONALLlegs• ReorderOUTERJOINtablestoavoidmaterializations
• TableUDFs• Improvemergesimilartoviews• Indexability ofjoinpredicates
35
QueryPerformanceFocus…• Improveperformanceof…
• JoinpredicateswithStage2expressions• Stage2toindexable
• VARBINARY,COLLATION_KEYIOE• Expressionevaluation
• CASE,SUBSTR,etc• Expressionsharing(SELECTlistonly)• CachingdeterministicUDFresults
• Enablingparallelism• 100%zIIP offloadforparallelchildtasks• Reducecostandresourceconsumption
• Sort• Reduceworkfile usageforGROUPBY/DISTINCT• ReducekeylengthforGROUPBY/DISTINCTandsparseindex• Continuedprogresstowardsin-memoryforsmallersorts(beguninV9)
36
QueryPerformanceFocus…• Adaptiveindex
• SimpleexampleoftargetedusecaseSELECT*FROMTAB1WHERECOL1<?ANDCOL2<?ANDCOL3<?;INDEXES:IX1(col1),IX2(col2),IX3(col3)
• Filteringoftheabovequeryisdependentonliteralsatexecution• Commonpatternforsearchscreens(withBETWEENsorLIKEs)• Commonthat1indexisfilteringunlessahighlyskewedvalueissearched
37
QueryPerformanceFocus…• Adaptiveindex…
• AllowRIDbasedplans(singleindexlistPFormulti-index)toquicklydeterminefilteringfromindex• WithoutrequiringREOPT(ALWAYS)• Forlistprefetch ormulti-indexORing
• Earlieropportunitytofallbacktotablespace scaniflarge%oftabletoberead
• Formulti-indexANDing• Reorderindexlegsfrommosttoleastfiltering• Early-outfornon-filteringlegs,andfallbacktorscan ifnofiltering
• Optimizertouseuncertaintytodetermineriskofasingleindexplan• Quickevaluationdonebaseduponliteralsused• Anyfurtherevaluationoffilteringdeferreduntilafter1RIDblockretrieved
• Ensuringthatveryshortrunningqueriesdonotincuroverhead
38
MIGRATION
39
MigrationPrerequisites– Hardware&OperatingSystem• Processorrequirements:
• z196,orhigher,processorsrunningz/OSV2.1,orlater• DB212willprobablyrequireincreasedrealstorageforaworkloadcomparedtoDB211forz/OS
• SoftwareRequirements:• z/OSV2.1BaseServices,(5650-ZOS),orlater• DFSMSV2.1,orlater• LanguageEnvironmentBaseServices• z/OSV2.1SecurityServer(RACF),orlater• IRLMVersion2Release3(DeliveredwithDB212)
• Additionaldetails:• http://www.ibm.com/common/ssi/rep_ca/1/897/ENUS215-371/ENUS215-371.PDF
40
Migration&Catalog• Singlephasemigrationprocess
• NoENFMphase• Newfunctionactivatedthroughnewcommand
• -ACTIVATEFUNCTIONLEVELwith"V12R1Mnnn”wherennn >=500• APPLCOMPATrules,fallbackrulescontinuetoapply
• BSDSconversiontosupport10bytelogRBAispre-requisite• Nopre-V10boundpackages
• Getridof31-bitruntime,someperformanceimprovements• BRFisdeprecated
• BRFpagesetsstillsupported,butzparm andREORGoptionsareremoved• TemporalRTStables
• Definedincatalog,enablementisoptional
41
OnlineMigrationImprovements• Pausestatisticsexternalizationduringmigrationtoreducecontention(partoffallbackSPE)
• Realtimestatistics• Statsfeedback• SYSPACKAGE.LASTUSED
• Catalog/directorylockdurationreductionAPARs(impactsonlinemigration,catalogREORGs)• PI43662– Acceleratorresourcesreleasedinatimelymanner• PI43916– DB2planallocationlocksreleasedintimelymanner• PI39053– AvoidlocksfromSETstatement• PI40755– DynamicSQLreleasepreparelocksearlier
42
TopDB2zSocial MediaChannels#DB2z
• Join the WorldofDB2 www.worldofdb2.com
• Follow@IBMDB2onTwitterhttps://twitter.com/IBMDB2
• Join DB2zLinkedInGroup
• https://www.youtube.com/user/IBMDB2forzOS
• DB2zonFacebook• https://www.facebook.com/IBMDB2forzOS/
43
RegisterNowcomingDB2Webcasts#DB2z- http://ibm.biz/DB2z2017WebcastSeries
• DB2forz/OS RESTful APIenablingtheMobileEconomy - 11thApril
• DB212MigrationPlanningandVeryEarlyExperiencesPART1- 16thMay
• DB212MigrationPlanningandVeryEarlyExperiencesPART2- 17thMay
• DB212forz/OSDataSharingEnhancements6thJune
• DB2forz/OSandFlashCopy:PracticalUseCases- 13thJune
• UsingLargerRealMemorySizewithDB2forz/OStoreduceCPU- June27th
44
Now… LiveQ&AwithJohnCampbell