DB2 ZPARMs 管理

Bart Steegmans – IMS/DB2 GSE meeting 03 December 2009Managing your ZPARMs In sickness and in health health, For better or for worse .. As long,..© 2009 IBM CorporationDisclaimer and acknowledgementsƒ The information contained in this presentation has not been submitted to any formal IBM review and is distributed on an “As Is” basis without any warranty either expressed or implied. The use of this information is a customer responsibility. responsibility ƒ The materials in this presentation are subject to– Enhancements at some future date, – A new release of DB2, or – A Programming Temporary Fix (PTF) – Jason Alpers – Bruce McAlister – Jan J n Ti Tielemans l m nƒ Many M thanks th k to t the th following f ll i people l f for th their i i input tƒ mm/yy date format used2© 2009 IBM CorporationObjectivesƒ Understand the different “types” types of ZPARMs ƒ How do you manage your ZPARMs ƒ Why are new ZPARMs introduced ƒ How do you manage ZPARM changes in the maintenance stream ƒ Discuss Di a few f new ZPARMs ZPARM i introduced d d i in via i maintenance i i in V8/V93© 2009 IBM CorporationLevel setƒ DSNZPARMs is data only module that contains DB2 system wide parameters ƒ Created by y running g DSNTIJUZ installation job j ƒ Consists of the following macros:– DSN6ENV – DSN6SPRM – DSN6ARVP – DSN6LOGP – DSN6SYSP – DSN6FAC – DSN6GRP (Environment XA vs 370 – expected to disappear it tdf) itntdf) (DBM1 initialization parms – has most of the maintenance) (Archive dataset parms) (Logging paramters) (Misc. system parameters) (DDF paramters) (Group related paramters related to data sharing)4© 2009 IBM CorporationDifferent types of ZPARMsƒ Visible (regular) ZPARMs– Can be adjusted by running the installation CLIST (DSNTINST) – Appear A on the h i install ll panels l – Customized value appears in the DSNTIJUZ job• This is the job that assembles the DSNZPARM module • Can be manipulated directly by editing the DSNTIUZ job o BUT you should avoid doing so (see later)– Documented in the Installation Guideƒ Opaque ZPARMs– Don’t appear on the install panels – Can be manually added to the DSNTIJUZ job – Mostly documented in the Installation Guide• “Subsystem parameters that are not on installation panels” sectionƒ Hidden ZPARMs– Can only be changed by directly editing the macros – Don’t try this at home (unless you really know what you are doing) ! – Use under IBM guidance• Or make sure y your resume is up p to date5© 2009 IBM CorporationList the current ZPARM settingsƒ Look at DSNTIJUZ job– How do you know it is the current one ?ƒ Use DSNWZP stored procedure– Sample program (DSN8ED7) to invoke this SP can be found in SDSNSAMP(DSNTEJ6Z) – This SP is also used by tools like Visual Explain and OSCƒ Msys for Setup DB2 Customization Center– Not been enhanced (“deprecated”) – Likely to be removed in the future6© 2009 IBM CorporationExcerpt from DSN8ED7 outputDSN8ED7: Sample DB2 for z/OS Configuration Setting Report Generator Macro Name -------DSN6SYSP DSN6SYSP DSN6SYSP … DSN6SPRM DSN6SPRM DSN6SPRM DSN6SPRM DSN6SPRM DSN6SPRM DSN6SPRM DSN6SPRM … DSNHDECP DSNHDECP DSNHDECP DSNHDECP DSNHDECP DSNHDECP DSNHDECP DSNHDECP DSNHDECP …7Parameter Name ----------------------CTHREAD DLDFREQ PCLOSEN MAX_CONCURRENT_PKG_OPS ADMTPROC HONOR HONOR_KEEPDICTIONARY KEEPDICTIONARY MAX_UTIL_PARTS OPTIXOPREF OPTJBPR ZOSMETRICS OPTJBPL DB2SUPLD DEFLANG DECIMAL DELIM SQLDELI DSQLDELI MIXED SCCSID MCCSIDCurrent Setting -----------00400 00005 00005 00010 DB9AADMT NO 00254 OFF OFF YES ON NO IBMCOB PERIOD DEFAULT DEFAULT APOST NO 00037 65534Description/ Install Field Name ------------------------MAX USERS LEVELID UPDATE FREQUENCY RO SWITCH CHKPTSInstall Fld Panel ID No. -------- ---DSNTIPE 2 DSNTIPL 14 DSNTIPL 12LANGUAGE DEFAULT DECIMAL POINT IS STRING DELIMITER SQL STRING DELIMITER DIST SQL STR DELIMITER MIXED DATA EBCDIC CCSID EBCDIC CCSIDDSNTIPF DSNTIPF DSNTIPF DSNTIPF DSNTIPF DSNTIPF DSNTIPF DSNTIPF1 2 3 4 5 6 7 7© 2009 IBM CorporationWhich ZPARM module is used during startup ?ƒ -DB9A STA DB2 PARM(xxxx) ƒ Messages in MSTR address space job log during startup:18.39.46 STC20668 IEF403I DB9AMSTR - STARTED - TIME=18.39.46 - ASID=0099 18.39.47 STC20668 DSNY024I -DB9A DSNYASCP MSTR INITIALIZATION IS STARTING 18.39.47 STC20668 DSNZ002I -DB9A DSNZINIT SUBSYSTEM DB9A SYSTEM PARAMETERS LOAD MODULE NAME IS DSNZPARM 18.39.47 STC20668 DSNZ016I -DB9A DSNZINIT 171 171 SUBSYSTEM DB9A SYSTEM PARAMETERS ARE AS FOLLOWS: 171 SYSTEM PARM DB9A9.SDSNEXIT(DSNZPARM) 171 DSNHDECP DB9A9.SDSNEXIT(DSNHDECP) ( ) 171 ACCESS EXIT DB9A9.SDSNEXIT(DSNX@XAC) 171 IDAUTH EXIT DB9A9.SDSNEXIT(DSN3@ATH) 171 SIGNON EXIT DB9A9.SDSNEXIT(DSN3@SGN) 18.39.47 STC20668 S DB9AIRLM 18.39.48 STC20668 DSNY001I -DB9A SUBSYSTEM STARTING8© 2009 IBM CorporationZPARM maintenanceƒ When installing– Fill out the panels – SET SYSPARM command (online ZPARM change)• • • •ƒ When changing g g your y environmentCannot change individual parms (reload of complete ZPARM module) Cannot change all parms N t all chan Not changes es are effective immediatel immediately Works at the member level when running DB2 data sharingƒ When migrating g g– New ZPARMs are documented – Will appear on the panels – Use input member from previous install/migration• So if f you manually ll edit d DSNTIJUZ, you will ll l lose those h updates d !ƒ When applying maintenance– Read PTF cover letters – SMP/E ++HOLD info9© 2009 IBM CorporationZPARMs in the DB2 Installation Guideƒ Overview of all ZPARMs (and DSNHDECP) parms– Name, macro, panel, online changeable, where to find more info10© 2009 IBM CorporationInstallation process –Use the install CLISTInstallation process Use the install CLIST DSNTIDXA –memberith d f lt tti DSNTIDXA with default parm settings (comes with product)DSNTINST –Installation DSNTIDXAclist that will invoke theinstallation panels in INSTALL modeDSNTINST CLIST (i)Generates several data sets and many members, including D NT JUZ b h ll DSNTIJUZ DSNTIDxxDSNTIJUZ –job that will create DSNZPARM data only moduleDSNTIDxx -updated set of parameters•Used for future input to install (i) = INSTALL MODEclist in UPDATE or MIGRATE modeUpdate process Update processDSNTIDxx –customized b ith tti DSNTID member with parm settings from a previous runDSNTINST –InstallationDSNTIDxx clist will be invoked in UPDATE mode•Used to update system parms*h i h DSNTINSTCLIST (u)*when environment has to change* after maint was applied •Not all fields can be changedDSNTIJUZUpdated DSNTID yyNot all fields can be changed Update process only generates DSNTIJUZ –to create DSNZPARM data only module•Actived by –SET SYSPARM cmdDSNTIDyy -new updated set (u) = UPDATE MODEDSNTIDyy -new updated set of parametersMigrate process Migrate processDSNTIDXA –defaults for new l release DSNTIDxx –customized member with parm settings DSNTID xx (curr. version)DSNTIDXA (new version)p g from current release DSNTINST –Installation clist will be invoked in DSNTINST clist will be invoked in MIGRATE mode•Not all values can be changedG tCLIST (m)Generates DSNTIJUZ –to create DSNZPARM data only module DSNTIJUZUpdated DSNTID yyy DSNTIDyy -new updated set of parameters(m) = MIGRATE MODEConclusionHave a DSNTIDxx member per subsystem–ZPARMs for each subsystem/data sharing member can differZPARM f h b/d h i b diffKeep DSNTIDxx member currentUsed as input during update and migrate processing–Used as input during update and migrate processingUse a different member for each change to track history –DSNTID01, DSNTID02 …–Maybe better to use the subsystem name as part of the member name •DB9A01, DB9A02, …Avoid manual editing of DSNTIJUZAvoid manual editing of DSNTIJUZ–Can cause regression during migrationMigration always uses DSNTIDxx as inputOpaque ZPARMs may force manual editing of DSNTIJUZ –For any ZPARM that is not (yet) supported by the installation panelsH t h dl thi ill b di d l t–How to handle this will be discussed later onQuiz –Know your ZPARMsQuiz–Know your ZPARMsWhat are the following ZPARMs used for ?What are the following ZPARMs used for?What is their recommended setting ?EDMBFIT–Uses best fit algorithm when searching for free space in the EDM pool –Recommended value: NO –because of performance overheadSTATSINT–Real-time stats write out intervaly–Recommended value: Default -every 30 minutesSTATIME–DB2 Statistics interval (to write DB2 statistics trace records)–Recommended value : 1 (default 5)R mm nd d l:1(d f lt5)NEWFUNNot a Z M,ut a DSNHDE parm–Not a ZPARM, but a DSNHDECP parmZPARM life cycleZPARM life cycleNew ZPARMs are usually introduced with a new release or version New ZPARMs are usually introduced with a new release or version –Part of a new function introduced with the release•Eg. Dynamic statement cache (introduced in V5)pEnabled via the CACHEDYN zparm•Allows you to enable/disable if you want/don’t want to use the functionality –Will appear on one of the installation panels–Can be maintained via the install CLIST–Well documented in the manualNew ZPARMs are sometimes introduced via the maintenanceNew ZPARMs are sometimes introduced via the maintenance streamChicken strip or chicken switch Chicken strip or chicken switchp y Chicken Strips on a motorcycle tiredemonstrate how “brave”a biker you areNew DB2 ZPARMs that come with maintenance are sometimes calledll d“chicken switches”Are you “brave” enoughto use them ?ZPARMs or ZPARM changes via the maintenance stream ZPARMs or ZPARM changes via the maintenance stream Typically used to fence off a code change where the expectationTypically used to fence off a code change where the expectation is that not everybody is affected–Pro:Reduce impact on code•Reduce impact on code•Reduce impact on customersZPARMs introduced in the maintenance stream often don’t appear on the install panelsh i ll l–Pro:•Reduce impact of the PTF on the code –less chance for PE APARC:–Con:•More chance of regression during migrationZPARM cycleZPARM cycleTypically used to fence off a code change where the expectation Typically used to fence off a code change where the expectation is that not everybody is affected–Default value is normally to disable the code change–Should IBM more aggressive in turning them on right away ?Should IBM more aggressive in turning them on right away?ZPARMs introduced in the maintenance stream often don’t appear on the install panelspAre typically opaque–When you want to exploit them, you manually add the keyword toDSNTIJUZ jobDSNTIJUZ j bIn the next release/versionAppear on the install panels–Appear on the install panels–Get activated by default–No longer supported (always active)Why a ZPARM and not a keyword or BIND optionWhy a ZPARM and not a keyword or BIND optionA new ZPARM in the maintenance stream is part of a PTFA new ZPARM in the maintenance stream is part of a PTFthis is a Program TEMPORARY Fix–Fixes a problem that is not expected to affect everybodyIf needed a more permanent solution can be implemented in a future –If needed, a more permanent solution can be implemented in a future release or versionpTo reduce the impact–On customers that are not affected by the problem–On the code•Fewer lines of codes, fewer chances of errors–On other parties involvedO th ti i l d•A new keyword or bind options is a change in the externalsthat also has to be supported by Tools (IBM and Others)Change in customer operational procedures to implement using a new•Change in customer operational procedures to implement using a newkeywordo We all know how hard this is, even for new options that come with a newreleaseHow do I know there is a new ZPARM ?How do I know there is a new ZPARM?APAR text / PTF cover letter should tell youy–Search keywords can be found online:/support/docview.wss?rs=64&uid=swg21210011–eg. SQLACCESSPATH for access path related problemsp y g–No specific keyword for ZPARM changes•Would it help if we did ?–Seach on DSNDQWPZ (IFCID106 mapping macro)•Not bullet proof technique as function and IFCID mapping can be delivered indifferent PTFs(that PRE or COREQ each other)different PTFs (that PRE or COREQ each other)SMP/E ++HOLD info should alert you•HOLD(ACTION)•HOLD(DOC)Create a ++USERMOD to do a dummy update againstAll of the macros that are input to the ZPARM module–All of the macros that are input to the ZPARM module–DSNDQWPZ (maps IFCID106)–Each time you try to APPLY a PTF that goes against the module(s)with a USERMOD it will fail. This failure indicates this PTF touches with a++USERMOD it will fail This failure indicates this PTF touches the ZPARMs and you can have a closer look.How do I know whether this applies to meHow do I know whether this applies to meOnly you know your system –you will have to judgeOnly you know your system–you will have to judgey yThe APAR/PTF contains information to let you decide whether you can be affected–Eg. A SELECT statement that you can run against some catalog tables Strong focus to improve APAR resolution textAPAR text old styleAPAR text“old style”Problem summary:Problem summary:******************************************************** USERS AFFECTED: For all DB2 for z/OS users. ******************************************************** PROBLEM DESCRIPTION:An inefficient access path can be chosen over the optimal index when the q y p gquery has local predicates on the columns with significant data skew. RECOMMENDATION:An inefficient access path can be chosen over the optimal index when the q y p gquery has local predicates on the columns with significant data skew.Additional Keywords:SQLACCESSPATH SQLINDEX DATASKEWProblem conclusion:With the logic corrected by this fix, DB2 has a better probability of choosing a more efficient index.h i ffi i t i dAPAR text new style -1APAR text“new style”1Problem summary*************************************************************USERS AFFECTED:For all DB2for z/OS users who haveUSERS AFFECTED: For all DB2 for z/OS users who have* subsystem parameter OPTJBPR set ON.************************************************************PROBLEM DESCRIPTION:When subsystem parameter OPTJBPR is set ON, DB2 enables the logic to factor in join When subsystem parameter OPTJBPR is set ON DB2enables the logic to factor in join predicate filtering when estimating the reduced I/O cost due to buffer pool caching. There are two problems in OPTJBPR logic:1. DB2 considers data skew for a single column referred to by local predicates. However, it may break the costing balance when the join is skewedmay break the costing balance when the join is skewed.2. Reduced I/O cost may be estimated incorrectly due to a bug in the logic.RECOMMENDATION:When subsystem parameter OPTJBPR is set ON DB2enables the logic to factor in join When subsystem parameter OPTJBPR is set ON, DB2 enables the logic to factor in join predicate filtering when estimating reduced I/O cost due to buffer pool caching. However, there are two problems in OPTJBPR logic. One is the costing balance may break when a join has skew since DB2 only considers data skew on a single column referred to by local predicates. The other is reduced I/O cost can be estimated incorrectly due to a bug in the predicates The other is reduced I/O cost can be estimated incorrectly due to a bug in the logic. Both problems can cause an inefficient access path to be chosen when OPTJBPR is set ON.Additional Keywords:Additional Keywords:SQLACCESSPATH PERFM OPTJBPR DATASKEWAPAR text new style -2APAR text“new style”2Problem conclusionProblem conclusionThis APAR adds a new DB2 subsystem parameter, OPTJBPL, for controlling whether DB2 should consider the data skew of the column referred by local predicates on the inner table when OPTJBPR is set ON. The default setting of di t th i t bl h OPTJBPR i t ON Th d f lt tti f OPTJBPL is ON.OPTJBPL needs to be set ON in order to enable the logic of considering the date skew of the column referred by local predicates on the inner table. Details about skew of the column referred by local predicates on the inner table.Details about how to set OPTJBPL can be referred from ++HOLD of this APAR.This APAR also fixes a bug which causes incorrect reduced I/O cost when OPTJBPR is set ON.OPTJBPR i t ONAPAR text new style -3APAR text“new style”3++ HOLD(UK49930) SYS FMID(HDB9910) REASON(ACTION) DATE(09252)This PTF adds a new DB2 subsystem parameter, OPTJBPL, for controlling whether DB2 should consider the data skew of the column referred to by local predicates on the inner table when estimating the reduced I/O cost as a result of buffer pool caching. Valid settings are:-ON (the default): Consider the data skew-OFF: Disregard the data skewIf you have already installed or migrated to this release of DB2, you need to take the following actions after applying this PTF:(1) Update your DB2 subsystem parameter (DSNZPxxx) modules:* Optionally add the keyword parameter OPTJBPL=<n>, where <n> is either ON or OFF (ON is recommended) to the invocation of the DSN6SPRM macro in your customized version of the installation job DSNTIJUZ. Make sure to add a in ti n f th DSN6SPRM m in u ust mi d si n f th inst ll ti n j b DSNTIJUZ M k su t dd continuation character in column 72 if needed. If you omit adding OPTJBPL here, the value will be set to the default of ON when you assemble the DSNZPxxx module.* Run the first two steps of the DSNTIJUZ job you modified, to assemble and link the load module. * After the job completes, you must either use the SET SYSPARM command or stop and start DB2 for the change to take effect.take effect(2) This PTF adds an entry for OPTJBPL to the CLIST default input member, DSNTIDXA, in the SDSNSAMP target library. You need to add this entry to all private copies of your CLIST output (DSNTIDxx) member for DB2. In each such copy, add the following line:OPTJBPL CHAR M OFF ON<n>OPTJBPL CHAR M OFF ON <n>where <n> is the value you specified for OPTJBPL when you modified DSNTIJUZ in step (1), above.(3) Update customized copies of installation CLIST member DSNTINST:This PTF modifies CLIST member DSNTINST in the SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your copies of this CLIST. You may also want to move it to format changes and reapply any tailoring you have done to your copies of this CLIST You may also want to move it to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside.).How do you manage themHow do you manage themCreate an SMP/E usermod so you don t lose the changeCreate an SMP/E usermod so you don’t lose the changeHow to track changes ?g gGood change managementComments in modulesNew DSNTIDxx member each timeTypically does not work for new ZPARMs in the maintenance streamWould it help if DB2 provided a job that would allow to generate an updated DSNTIDxx member based on the current active ZPARM module ? updated DSNTIDxx member based on the current active ZPARM module?MR1005054929y g pAnything else we can do to help make ZPARM maintenance easier ?Do you know these ZPARMs ?Do you know these ZPARMs?PLANMGMT (See next presentation from Paul) PLANMGMT(See next presentation from Paul) RRFZOSMETRICSSPRMRSMXi i ffSome optimizer stuff–Several new ZPARMs introduced via maint stream –Good ROT –use the DEFAULT provided by IBM –PREDPRUNE–OPTIXOPREF–OPTIOWGTAll online changeable except for SPRMRSMXRRF –Reordered row format defaultRRF R d d f t d f ltIntroduced by PK87348 (9/09) –V9 NFMIntroduced by PK87348(9/09)–V9NFMChanges SPRMRRF hidden ZPARM to opaque RRF ZPARM and gonline changeableShould appear on the panels in the next release/versionRRF ENABLE(default)/DISABLERRF=ENABLE(default)/DISABLEIn DSNTIDXA CLIST input parm memberAPAR also adds support for BRF for UTSAPAR also adds‘support’for BRF for UTS–With RRF=DISABLE active and create new UTS -> uses BRFAPAR also enables LOAD/REORG to switch between BRF and RRF New keyword: ROWFORMAT RRF/BRFRecommendation: use RRF=ENABLE and keep only those object where you experience problem in BRFh i bl i BRFZOSMETRICSIntroduced by PK62116 and PK66373 (7/08) –V8 and V9Also needs RMF APAR OA24404Al d RMF APAR OA24404Default = NOIn DSNTIDXA CLIST input parm memberGathers extra stats about the machine and z/OS environmentCPU AND STORAGE METRICS QUANTITY---------------------------------------------CP LPAR 6CPU UTILIZATION LPAR 41CPU UTILIZATION DB2 0CPU UTILIZATION DB2 MSTR 0CPU UTILIZATION DB2 DBM1 0UNREFERENCED INTERVAL COUNT 65534REAL STORAGE LPAR (MB) 4095FREE REAL STORAGE LPAR (MB) 744USED REAL STORAGE DB2 (MB) 645VIRTUAL STORAGE LPAR (MB) 15743VIRTUAL STORAGE LPAR(MB)15743FREE VIRTUAL STOR LPAR (MB) 10880USED VIRTUAL STOR DB2 (MB) 648SPRMRSMXIntroduced by PK18354 (4/06)Hidden ZPARM (&SPRMRSMX in DSN6SPRC)Hidd ZPARM(&SPRMRSMX i DSN6SPRC)To be used under IBM guidance(eg. When suffering from a massive storage leak problem)(eg When suffering from a massive storage leak problem)Keep z/OS alive at the expense of DB2(y)(to avoid system wait state)This APAR adds new serviceability subsystem parameter for customers who wish to ABEND and TERMINATE DB2 if the amount of DB2 REAL + Auxiliary storage exceeds the user value.f DB2REAL A ili d h l This value over rides the MEMLIMIT provided by MVS to control above-the-bar virtual storage because DB2 usage of this space is sparse populated.DSNS001I = csect_name SUBSYSTEM HAS EXCEEDED THE USER SPECIFIED REAL STORAGE USAGE THRESHOLDUSER SPECIFIED REAL STORAGE USAGE THRESHOLDPrune “Always False”Predicates (V8)Prune Always False Predicates (V8) Opaque ZPARM PREDPRUNEDefault OFFNot in DSNTIDXA CLIST input parm member p pV8 APAR PK49265 (11/07)–Literal “IN” or “=“ only (no host vars or REOPT)–Becomes….WHERE ('A' = 'B ' OR COL1 IN ('B', 'C'))–Original “OR”is stage 2WHERE COL1 IN ('B', 'C')Original OR is stage 2•Disables index access and many query transformations –Good news for query generators •They often use this OR construct–Documented tricks are NOT pruned (OR 0=1)Prune Always False Predicates (V9)Prune“Always False”Predicates(V9)ZPARM PREDPRUNEDefault OFFV9 APAR PK55076 (10/08)S t V8APAR PLUS–Same support as V8 APAR, PLUSWHERE NOT_NULL_COL IS NULL OR COL1 = 'B'–Becomes….WHERE T1.COL1 = 'B'–IS NULL check on NOT NULL column is pruned when contained in OR predicatePrune Unnecessary Tables Prune Unnecessary TablesZPARM PREDPRUNE–V8 & 9 APAR PK73013 (12/08)–LEFT OUTER JOIN •Without SELECTing columns from the RIGHT tableo Right table is unnecessary if no duplicates if either:»SELECT DISTINCT T1.C3FROM T1 LEFT OUTER JOIN T2SELECT DISTINCT T1.C3FROM T1Unique index on join key of right table»Or, SELECT DISTINCT ON T1.C2 = T2.C2WHERE T1.C1 = ?FROM T1 WHERE T1.C1 = ?,May want to consider using PREDPRNE=YESMay want to consider using PREDPRNE=YESOPTIXOPREF Index only PreferenceOPTIXOPREF -Index-only Preference Ever created an index to support index-only?l h h d d ?–Only to have optimizer choose index + data?SELECT C2FROM T1WHERE C1 = ?Index 1(C1)Index +data Index 1 (C1)Index 2 (C1, C2)ÅIndex + data ÅIndex-onlyOpaque ZPARM OPTIXOPREF–Prioritize index-only over index + data with same index prefix •V8 (V9) APAR PK51734 (11/07)•V9 APAR PK77426 (10/09) changes default to ON (enable)•[not in DSNTIDXA member –manual maint required][not in DSNTIDXA membermanual maint required]OPTIOWGT –Better balance CPU and I/O cost OPTIOWGT–Better balance CPU and I/O costIntroduced by–PK61274 (6/08)–V8 –Default is DISABLEPK61274(6/08)V8D f l i DI ABLE–PK61277 (8/08) + PK75643 (12/08)-V9 –Default is ENABLE Opaque ZPARM and online changeableOpaque ZPARM and online changeableIn DSNTIDXA CLIST input parm member (V8 and V9 –diff dflt) With faster machines, optimizer tends to favour access paths With faster machines optimizer tends to favour access paths that are more CPU intensive. I/O cost was often over estimated compared to CPU cost.ENABLE allow the optimizer to use an improved formula for balancing the costs of input/output and CPU speeds.Recommendation: use OPTIOWGT=ENABLE especially on faster machines like z9 and z10And now for something completely different…And now for something completely differentAccess path with EXPLAIN NOAccess path with EXPLAIN NOI performed a BIND/REBIND with EXPLAIN NOH I th t th????–How can I see the current access path????•Without issuing a new explainBIND PACKAGE(STLEC1.TEST) MEMBER(TEST1) ACTION(REP)DSNT254I ) DSNTBCM2 BIND OPTIONS FORPACKAGE = STLEC1.TEST.TEST1.()ACTION REPLACEOWNER SYSADMQUALIFIER ADMF001VALIDATE BINDEXPLAIN NOISOLATION CSRELEASECOPYCOPYOSC Monitor Profile Steps OSC Monitor Profile Steps Setup a Profile Monitor i OSC View in OSC–For package you wish to show PLAN_TABLE Monitor optionCh thChoose the DB2 subsytemChoose NewMonitor ProfileOSC Monitor Profile StepsOSC Monitor Profile StepsCreateCreatea newmonitorprofileChoose packageChoose packageOSC Monitor Profile Steps OSC Monitor Profile StepsChoosepush outEXPLAINChoose small time interval if you want to see the information soon want to see the information soonAccess path with EXPLAIN NOAccess path with EXPLAIN NONext time the package executes…–SYSIBM.PLAN_TABLE will be populated//BATCH EXEC TSOBATCH//SYSTSIN DD *DSN S(V91A) R(1) T(1)RUN PROGRAM(TEST1) PLAN(TEST1)//SYSTSPRT DD SYSOUT=*//SYSTSPRT DD SYSOUT=*//SYSOUT DD SYSOUT=*//SYSPRINT DD SYSOUT=*Retrieve access path from SYSIBM.PLAN_TABLE*SELECT * FROM SYSIBM.PLAN_TABLE WHERE PROGNAME = 'TEST1‘ORDER BY QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ;+----------------------------------------------------------------------+|QRYNO|QBLKNO|PLANNO|METHOD| TNAME |ACCESSTYPE|MCOLS|ACCNAME|IDXONLY|+----------------------------------------------------------------------+| 1 | 1 | 1 | 0 | CUSTOMERS| N | 1 |CUSTIX2| N ||1|1|1|0|CUSTOMERS|N|1|CUSTIX2|N|| 1 | 1 | 2 | 1 | ACCOUNTS | I | 1 |ACCTIX2| Y || 1 | 1 | 3 | 3 | | | 0 | | N | +----------------------------------------------------------------------+*Subset of PLAN_TABLE columns shown here。