tag:blogger.com,1999:blog-4516533711330247058.post5584842554451527372..comments2024-03-28T07:32:09.246-07:00Comments on Robert's Db2 blog: Db2 for z/OS EXPLAIN: What an Access Path Is, and What it Would BeRoberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.comBlogger34125tag:blogger.com,1999:blog-4516533711330247058.post-41442837463086187972023-12-01T05:10:10.218-08:002023-12-01T05:10:10.218-08:00Thank you RobThank you RobAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-6284052381208102023-11-30T05:57:40.087-08:002023-11-30T05:57:40.087-08:00Two possibilities come to mind:
1) PLANMGMT(OFF) i...Two possibilities come to mind:<br />1) PLANMGMT(OFF) is explicitly specified in REBIND package commands in this environment - that would override the ZPARM setting.<br />2) Check the information in the blue box on this page of the online Db2 for z/OS documentation, to see situations that will cause Db2 to internally turn off plan management functionality: https://www.ibm.com/docs/en/db2-for-zos/12?topic=services-planmgmt-bind-option.<br /><br />If neither one of those possibilities can explain why you are not seeing retention of previous and original copies of packages when they are rebound, you might need to open a case with IBM Support to find out what is going on.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-70927165308385174822023-11-30T03:40:48.708-08:002023-11-30T03:40:48.708-08:00Thanks Rob,
But in our case Auto Rebind/ Rebind ha...Thanks Rob,<br />But in our case Auto Rebind/ Rebind happened on certain packages post the Zparm change but still we are not seeing the copies of package.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-18261147626523568842023-11-28T06:05:33.847-08:002023-11-28T06:05:33.847-08:00With PLANMGMT=EXTENDED in ZPARM, the three copies ...With PLANMGMT=EXTENDED in ZPARM, the three copies of a package that can be retained are the current, previous, and original copies. If a package has not been rebound since PLANMGMT was set to EXTENDED, there will be only the current copy - no previous or original.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-11358834007512963202023-11-28T03:46:44.531-08:002023-11-28T03:46:44.531-08:00Hi Robert
In one of our shops ZPARM Plan Managemen...Hi Robert<br />In one of our shops ZPARM Plan Management is set to Extended but I don't see 3 copies of package being retained.<br />I see only 1 instance of the package. What might be the reason for this.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-15190523434532444942023-11-12T18:25:09.004-08:002023-11-12T18:25:09.004-08:00I was not advising you to execute RUNSTATS with a ...I was not advising you to execute RUNSTATS with a combination of RESET ACCESSPATH and HISTORY ACCESSPATH. You could do that, but as the documentation indicates this would just record when the access path stats were reset (that might be useful for you if you will not otherwise be recording the time of the reset). My recommendation was, again, to execure RUNSTATS TWO TIMES. First with HISTORY ACCESSPATH to record stats prior to resetting them, if you think you might need or want to later restore those stats after having reset them. The SECOND execution of RUNSTATS would be with RESET ACCESSPATH to reset the statistics.<br /><br />HISTORY ACCESSPATH shows up twice in the description of RUNSTATS options because it appears twice on the RUNSTATS utility syntax diagram near the top of that documentation page. Take a look at that syntax diagram. You can specify HISTORY ACCESSPATH in combination with a RESET ACCESSPATH specification, or you can specify HISTORY ACCESSPATH without a RESET ACCESSPATH specification (the latter situation is shown under "history-spec" at the bottom of the utility syntax diagram.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-88905928225552157042023-11-12T05:51:34.776-08:002023-11-12T05:51:34.776-08:00I'm little confused here.per IBM manual it sta...I'm little confused here.per IBM manual it states,HISTORY ACCESSPATH option - does not save the access path statistics values that are reset if so how do you say that it will record stats .Also I see HISTORY ACCESSPATH is mentioned two times in manual one at initial few lines and other bottom of page.Is it something like running this HISTORY ACCESSPATH before & after reset accesspath? If so why do we run HISTORY ACCESSPATH after reset accesspath when it is not gonna save stats.<br />https://www.ibm.com/docs/en/db2-for-zos/12?topic=runstats-tablespace-syntax-options.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-22486128608420174952023-11-10T15:53:34.571-08:002023-11-10T15:53:34.571-08:00Execute RUNSTATS twice: once to record the current...Execute RUNSTATS twice: once to record the current statistics for the object(s) in question via a HISTORY ACCESSPATH specification, then a second time to reset the statistics for the object(s).<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-72587598931914241852023-11-10T10:43:41.320-08:002023-11-10T10:43:41.320-08:00I found one interesting thing.The manual specfies-...I found one interesting thing.The manual specfies-"After your reset access path statistics, the previous values cannot be recovered if no statistics history is available."<br />HISTORY ACCESSPATH option -This option only records that the reset occurred and does not save the access path statistics values that are reset.<br /><br />How to collect statistics history to recover if history accesspath option is not gonna save access path statistics?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-70002630517429683392023-10-26T22:58:24.454-07:002023-10-26T22:58:24.454-07:00INVALIDATECACHE invalidates cached dynamic SQL sta...INVALIDATECACHE invalidates cached dynamic SQL statements, but does not change statistics for the associated object(s). RESET ACCESSPATH invalidates cached dynamic SQL statements and also resets statistics for the associated object(s) back to their default values. For an explanation of why you might want to reset statistics for a database object, please refer to this page in the Db2 for z/OS online documentation:<br />https://www.ibm.com/docs/en/db2-for-zos/12?topic=runstats-resetting-access-path-statistics.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-72242240157337334512023-10-26T02:35:16.310-07:002023-10-26T02:35:16.310-07:00Per your earlier response on using invalidatecache...Per your earlier response on using invalidatecache option as below :<br />Invalidating S1 in the dynamic statement cache will cause S1 to be reoptimized by Db2 when the statement is next issued by the associated application program, and the newly-generated access path might utilize the IX2 index, and performance of the statement might be improved as a result.<br /><br />After readin thro manual-so" invalidatecache" option also is used to have new access path and the "reset accesspath "option also does samething.Then I dont see purpose of having two different parms? Correct my understanding.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-76681149349201092982023-10-22T19:06:05.435-07:002023-10-22T19:06:05.435-07:00INVALIDATECACHE YES is in effect when REORG LIST i...INVALIDATECACHE YES is in effect when REORG LIST is run with REPORT NO UPDATE NONE, probably because before the INVALIDATECACHE option existed one had to execute RUNSTATS with REPORT NO UPDATE NONE in order to get a cache invalidation action to be taken. Now, you do this by specifying INVALIDATECACHE YES instead of specifying REPORT NO UPDATE NONE.<br /><br />The purpose of the RESET ACCESSPATH option of RUNSTATS is explained on this page of the Db2 for z/OS online documentation: https://www.ibm.com/docs/en/db2-for-zos/12?topic=runstats-resetting-access-path-statistics.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-20863935766515304392023-10-20T10:48:11.239-07:002023-10-20T10:48:11.239-07:00I see update none & report no is mandate to ha...I see update none & report no is mandate to have invalidatecache yes.why so? <br />what is significance/use of having 'no' for these two parms? <br />Also ,I came across below.Is invalidatecache yes is different from resetaccess path.I thought both are same.If they meant for different prupose.then im not able to understand resetaccesspath use.<br /> RUNSTATS TABLESPACE LIST LIST1 <br /> RESET ACCESSPATH Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-72063915866710016602023-10-15T19:40:36.067-07:002023-10-15T19:40:36.067-07:00Recycling the Db2 subsystem would do it. So would ...Recycling the Db2 subsystem would do it. So would dropping an object on which a cached dynamic statement is dependent. Most practical approach will typically be to use the INVALIDATECACHE option of the RUNSTATS utility.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-87763259580973661702023-10-13T01:04:04.843-07:002023-10-13T01:04:04.843-07:00What are the other ways of invalidating /clearing ...What are the other ways of invalidating /clearing cache other than having in runstats card.Arunanoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-64501139877699975892023-10-12T18:09:47.089-07:002023-10-12T18:09:47.089-07:00Suppose dynamic SQL statement S1 has been prepared...Suppose dynamic SQL statement S1 has been prepared, and its prepared and executable form is in Db2's dynamic statement cache. Suppose you create a new index, IX2, on table T1 that statement S1 accesses. You created index IX2 because you think statement S1 will execute a lot faster if the index is used in S1's access path. For that to happen, S1 will have to be reoptimized by Db2, and that won't happen as long as the previously-prepared form of S1 is in the Db2 dynamic statement cache. Invalidating S1 in the dynamic statement cache will cause S1 to be reoptimized by Db2 when the statement is next issued by the associated application program, and the newly-generated access path might utilize the IX2 index, and performance of the statement might be improved as a result.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-42751116252939718052023-10-12T05:37:30.734-07:002023-10-12T05:37:30.734-07:00Hello,why we need to invalidate cache/clear cache ...Hello,why we need to invalidate cache/clear cache ? I see runstats card has invalidatecache option ,What is the use of it ?Arunanoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-69484629769259255242023-09-18T18:37:06.807-07:002023-09-18T18:37:06.807-07:00If you have plan management functionality active i...If you have plan management functionality active in your Db2 for z/OS environment (PLANMGMT=EXTENDED or BASIC in ZPARM - see https://www.ibm.com/docs/en/db2-for-zos/12?topic=services-planmgmt-bind-option) then in the event that a package rebind yields a worse-performing access plan, you can easily restore the previous access plan via REBIND with SWITCH(PREVIOUS) - see https://www.ibm.com/docs/en/db2-for-zos/12?topic=change-saving-switching-previous-access-paths.<br /><br />For a dynamic (or a static) SQL statement, you can tell Db2 to use a certain access plan via the BIND QUERY command and the SYSQUERYPLAN catalog table (see https://www.ibm.com/docs/en/db2-for-zos/12?topic=selection-specifying-access-paths-statement-level).<br /><br />Also note that the IBM product Db2 AI for z/OS can detect when an access path has changed for the worse (performance-wise) and can automatically restore the previous better-performing access path for the statement, whether static or dynamic - see https://www.ibm.com/products/db2-ai-for-zos.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-27815473144735955322023-09-17T23:46:42.321-07:002023-09-17T23:46:42.321-07:00Aplogize for the typo.Yes I meant -In what cases/s...Aplogize for the typo.Yes I meant -In what cases/situation there will be a requirement to restore old access path.<br />To your question-Are you talking about static or dynamic SQL?-Both static as well as dynamicAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-60325535331650769252023-09-17T16:18:06.834-07:002023-09-17T16:18:06.834-07:00Two questions for you:
1) Are you talking about st...Two questions for you:<br />1) Are you talking about static or dynamic SQL?<br />2) I'm not sure what you mean by the question, "In what access there will be a requirement to restore old access path?" Do you mean, "When would you want to restore an old access path?" If that is what you mean, the answer would generally be this: you restore an old access path when a newer access path does not perform as well as the old one did.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-32478774754685056842023-09-16T04:33:44.476-07:002023-09-16T04:33:44.476-07:00Is it possible to restore/go back to older access ...Is it possible to restore/go back to older access path ? <br />How do we that?<br />In what acess there will be a requirement to restore old access path?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-82643284111270807022023-09-01T08:39:43.668-07:002023-09-01T08:39:43.668-07:00Wonderful!! Thanks Robert!Wonderful!! Thanks Robert!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-84617376112209054702023-08-30T05:15:19.982-07:002023-08-30T05:15:19.982-07:00In a situation in which RACF is managing Db2-inter...In a situation in which RACF is managing Db2-internal security, if you have AUTHEXIT_CHECK=PRIMARY in ZPARM then it is quite likely that there will be authorization-related auto-bind failures because the primary auth ID of a process executing a package probably does not have the privileges needed to rebind the package. The package's owner ID has those privileges, and the package owner ID will be checked for authorization on auto-bind when AUTHEXIT_CHECK=DB2. The problem here isn't RACF per se - the same auto-bind authorization check would fail if Db2 and not RACF were checking the authority of the ID executing the package at auto-bind time, versus checking the authorization of the package owner ID.<br /><br />Besides this authorization-related auto-bind failure scenario (fix-able by changing the value of AUTHEXIT_CHECK if it is not currently DB2), the only think I can think of that would cause an auto-bind to fail would be unavailability of a database object required for successful rebind of the package in question (a situation indicated by the 00E30064 reason code I mentioned in an earlier response).<br /><br />If an auto-bind operation fails, the package in question is marked inoperative. If you then are able to successfully execute an explicit rebind of the package, the auto-bind failure was very probably authorization-related. If your explicit rebind fails, an error message/code should explain why that happened, and my expectation would be that the failure was due to unavailability of a database object needed for successful rebind.<br /><br />RobertRoberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-89843350998760971992023-08-29T02:08:26.308-07:002023-08-29T02:08:26.308-07:00so looks like with racf managing privliges,it is l...so looks like with racf managing privliges,it is likely that we cant tell/find from our end what is the issue in auto bind concering authority part.But at same time,can we confirm/be sure that authorization is the only issue for auto bind failure ?Can there be different reason apart from racf is what I'm foreseeingAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-4516533711330247058.post-81580439633226410492023-08-25T14:18:39.470-07:002023-08-25T14:18:39.470-07:00OK, historically when RACF has been used to manage...OK, historically when RACF has been used to manage Db2-internal security, there has been a problem related to auto-rebinds. The problem: when an auto-rebind action was initiated, RACF would check to see if the primary auth ID of the process requesting execution of the invalidated package (i.e., the package execution request that leads to the auto-rebind) had the authority or privileges needed to perform the auto-rebind. Often, that ID lacked those privileges (it likely would have only the execute privilege on the package), and the auto-rebind would fail because of an authorization-related error. What you wanted was for RACF to check to see if the package's owner ID had the authority or privileges required for successful auto-rebind, but there was no way to tell RACF to do that. That changed with Db2 11 for z/OS, which introduced the AUTHEXIT_CHECK parameter in ZPARM (see https://www.ibm.com/docs/en/db2-for-zos/12?topic=panel-auth-exit-check-authexit-check-subsystem-parameter). The default value for that parameter is PRIMARY. If you want RACF to check authority/privileges for the owner ID of a package when the package is auto-rebound, you want the value of AUTHEXIT_CHECK to be DB2. Note that this ZPARM is not online-update-able - you'll need to recycle the Db2 subsystem to put a change into effect.<br /><br />As for distinguishing between auto-bind and explicit bind/rebind, the value of the ORIGIN column of the SYSPACKAGE catalog table might tell that tale. I believe that column's value indicates how the EXPLAIN information in the package was generated. One of the possible values of that column is 'A' - short for automatic bind.<br /><br />Robert Roberthttps://www.blogger.com/profile/02058625981006623480noreply@blogger.com