Tuesday, November 24, 2020

Db2 for z/OS EXPLAIN: What an Access Path Is, and What it Would Be

The EXPLAIN functionality of Db2 for z/OS, by which the query optimizer's access path determinations are externalized, has been around as long as I can remember, and Db2 people, by and large, are familiar with the concept. In recent weeks, however, I discovered that there is some lack of awareness of EXPLAIN enhancements delivered through more recent versions of Db2 - specifically, Db2 Version 8, Db2 10 and Db2 12. These enhancements, which provide ways to see what a statement's access path is, and what it would be, can be very helpful in certain scenarios. I will describe the newer EXPLAIN capabilities and related use cases in this blog entry.


Db2 Version 8: using EXPLAIN to get "access path is" information for cached dynamic statements

"Db2 Version 8?" you might ask. "That came out 16 years ago. Are you telling me there's something about Db2 V8 that I still might not know?" Yes, that's what I'm telling you. And it's not because you aren't smart. It's because you're a human being, and there's only so much knowledge that one can put into one's brain and be able to pull out, intact, on-demand. Db2 V8, like most Db2 for z/OS versions, was packed with a lot of Very Important New Features, such as 64-bit addressing; table-controlled range-partitioning; a type 4 JDBC driver; and the ability to change a table column's data type (within a family of data types, such as character string or numeric) with an ALTER, versus unload/drop/re-create/re-load - and that's just to name a few of the Db2 V8-introduced capabilities that were a Big Deal. With all that "wow" stuff, no surprise that some of the less-Big - but still really helpful - new things escaped some peoples' notice (or were noticed and later forgotten).

One of those not-huge-but-really-helpful Db2 V8 enhancements concerned EXPLAIN, in the context of the dynamic statement cache. Now, we got dynamic statement caching well before Db2 V8, and it was very important in making Db2 for z/OS an outstanding data server for applications - very often client-server applications, and often provided by vendors - that exclusively (or almost exclusively) issue SQL statements that are dynamically prepared by Db2. Sometimes, though, there could be a performance analysis challenge presented by the dynamic statement cache: what if you needed to know the access path for a dynamic query that had been prepared and cached? Sure, you could take the text of the query and make that input to an EXPLAIN statement, but the output of that EXPLAIN would tell you what the query's access path would be if it were generated right now. That's not what you want, in this case. What you want is the access path that was generated when the query was initially prepared and cached. Well, depending on the size of the statement cache and the frequency of the cached statement's execution (when a statement has to be removed from the cache to make room for another statement, that is done on a least-recently-used, or LRU, basis), initial preparation of the statement might have happened hours or days ago - who's to say that an access path generated for the statement NOW would be what it was THEN?

Db2 V8 addressed that scenario by providing a new EXPLAIN option which can be utilized as shown in the example below (the Db2 V8 enhancement is highlighted in red):

EXPLAIN STMTCACHE STMTID 1287;

Now, plenty of Db2 people are familiar with the STMTCACHE option of EXPLAIN, but only when that option is used with the keyword ALL, as in, EXPLAIN STMTCACHE ALL. Executing that statement - referred to by many as, "snapping the cache" - will cause Db2 to insert, into the DSN_STATEMENT_CACHE_TABLE qualified by your ID, a row for each statement in the dynamic statement cache. Snapping the cache can be a very handy way to get some very useful information about cached dynamic statements. When EXPLAIN STMTCACHE is issued together with STMTID xxxx, where "xxxx" could be an integer value, as shown in my red-highlighted example, above, or a host variable into which a statement ID had been placed, what Db2 provides is the access path information that was generated for the statement associated with the ID (and statement ID is part of what you get in the output of EXPLAIN STMTCACHE ALL) when that statement was initially prepared and placed in the dynamic statement cache. This is so because EXPLAIN STMTCACHE STMTID does not generate EXPLAIN information - it extracts EXPLAIN information. See, when Db2 prepares a dynamic statement and caches the prepared form of the statement in the statement cache, it also stores access path information for the statement in a compressed form, and that information is what's accessed and expanded into human-usable form when EXPLAIN STMTCACHE is issued with the STMTID option.

OK, challenge effectively addressed. This Db2 V8 enhancement laid the groundwork for a similar enhancement, for static SQL statements, that was delivered with Db2 10.


Db2 10: getting "what is" access path information for a package when you neglected to ask for it at BIND (or REBIND) time, and getting "what would be" access path information for a package

First, getting "what is" access path information for statements in a package: you might think, "That's easy enough - just specify EXPLAIN(YES) when you bind (or rebind) the package." Okay... but what if you didn't do that at bind or rebind time (the default value for the EXPLAIN option of BIND and REBIND PACKAGE is NO)? EXPLAIN-ing the statements in the package won't be very helpful, as that will give you "would be now" access path information for the statements, versus the "was at bind (or rebind) time" access path information you want. You're out of luck, right?

No, you're not out of luck. That compact-form access path information I mentioned above in describing EXPLAIN STMTCACHE STMTID? Db2 started making that part of a package's structure with Db2 9, and Db2 10 provided a way to extract the information from a package (for packages generated in a Db2 9 environment or beyond). Again, an example of the new (with Db2 10) EXPLAIN statement syntax, with the new part highlighted in red:

EXPLAIN PACKAGE COLLECTION ’COLLA’ PACKAGE ’PKG1234’ COPY ’CURRENT’;

As with EXPLAIN STMTCACHE STMTID, execution of the statement shown above would extract, not generate, access path information for statements in package PKG1234 in collection COLLA, and what you would get is information about the access paths generated at the time the package was last bound or rebound (absent a COPY specification, such as the COPY 'CURRENT' in the example above, the EXPLAIN PACKAGE statement would give you access path information for all available copies of the package, which could be the current, previous and original copies if PLANMGMT(EXTENDED) functionality is in effect).

Let's turn the package scenario around. Suppose you actually want "what would be" access path information for the package's statements. In other words, you want to know what the access paths for the statements would be if they were generated now. Why would you want to know that? Well, maybe there's been a change in the Db2 environment that would prompt you to rebind a package (maybe many packages) in order to get a performance boost - maybe some new indexes have been defined on tables accessed by statements in the package, or maybe catalog statistics have been enriched so as to get better-performing access paths for some of the package's statements (and those statistics-enrichment actions may have been suggested by Db2 itself), or maybe you've migrated to a new version of Db2. You don't want to issue REBIND package with EXPLAIN(YES), because that would actually cause the package to be rebound, and maybe you don't want to do that before seeing what the access paths for the package's statements would be as a result of the rebind. You could issue EXPLAIN for individual statements of the package, but that could be pretty laborious, especially for a package with quite a few statements. What to do?

Simple: you rebind the package with EXPLAIN(ONLY), an option introduced with Db2 10 (and available for BIND PACKAGE as well as for REBIND PACKAGE commands). When EXPLAIN(ONLY) is specified for a BIND or REBIND PACKAGE command, a new package is not generated by Db2; instead, Db2 only generates EXPLAIN information for the package's statements, and it does that as though you had issued EXPLAIN for the individual statements - that is to say, Db2 goes through standard EXPLAIN processing for the package's statements, showing you what the access paths for those statements would be if they were generated right now. With that information in hand, you can make your decision as to whether or not you want Db2 to actually generate a new instance of the package via a REBIND PACKAGE command.


Db2 12: getting "what is" information for stabilized dynamic statements

Db2 12 introduced an important new feature, called dynamic plan stability, whereby Db2 can persist the prepared form of a cached dynamic SQL statement to a new (with Db2 12) catalog table called SYSIBM.SYSDYNQRY. What that means: you can stabilize the access path for a dynamic query, even across stop/starts of a Db2 subsystem, because if a stabilized query comes into the system and its prepared form is not in the dynamic statement cache (as would be the case if this were the first issuance of the query following a stop and start of the subsystem, with the dynamic statement cache initially being empty after the -START DB2), Db2 will not re-prepare the statement - instead, Db2 will retrieve the prepared form of the statement from the SYSDYNQRY catalog table and load that structure into the dynamic statement cache.

That's great, when you want to avoid the performance instability that can result from reoptimization of a query, but it also would seem to present an access path analysis challenge. We know that access path information is available (via EXPLAIN STMTCACHE STMTID) for a statement in the dynamic statement cache, but it is possible (as just pointed out) that a stabilized dynamic statement is not in the dynamic statement cache. What then? How can we, in that case, obtain access path information for the stabilized query?

No problem. You get that information using this statement (with the new part, as before, highlighted in red):

EXPLAIN STABILIZED DYNAMIC QUERY STMTID 179 COPY 'CURRENT';

Here, the STMTID is not the ID assigned by Db2 to the statement when it is inserted into the dynamic statement cache - it is, rather, the persistent statement ID generated for the statement by Db2 when the statement is stabilized by way of the new (with Db2 12) command -START DYNQUERYCAPTURE (this is the value placed in the SDQ_STMT_ID column of the query's row in the SYSDYNQRY catalog table). Execution of the EXPLAIN statement shown above extracts, from the SYSDYNQRY catalog table (actually, from an associated auxiliary table in a LOB table space), the access path information for the query as it was when the query was initially stabilized (that being the access path information stored with the prepared form of the statement in the dynamic statement cache when the query was last optimized - the information that would be extracted via EXPLAIN STMTCACHE STMTID for a statement currently in the dynamic statement cache).

And there you have it. The access path information you want, Db2 has (when you want "access path is" information) or can generate (when you want "what would be" information). You just need to know how to get it. And now you do.

34 comments:

  1. Hi Rob,i have added three few columns at end of table 'with default' clause.but the user wants to add null.I tried to alter using set clause but it dint work(not sure if syntax is wrong).is there an option to
    1) unload three columns,drop and create column column with null definiton and load three column s with old data?
    2)if I alter the column using set null clause-what happens to existing data ?will the existing data of that column also change/overridden to null or only new rows that will be added gets null values?

    ReplyDelete
  2. I assume you are referring to a situation in which you have added a NOT NULL column to a table, and now you need that column to be nullable. I am not aware of a way to do that with an ALTER TABLE statement (i.e., there is not a "not-NOT NULL" option for the ALTER COLUMN part of an ALTER TABLE statement). Unloading the values of the column in question, and dropping the column and then re-adding the column WITHOUT a NOT NULL specification, and then restoring the unloaded column values would appear to me to be the way to accomplish the objective.

    Robert

    ReplyDelete
    Replies
    1. if the original column was defined with 'null 'clause ,the unloaded data may have null values.after i re-add column with 'not null' clause,and now if i try to restore unloaded columns ,will 'not null' column accept null vaues?

      Delete
    2. You originally asked about changing a NOT NULL column to be nullable. If you want to do the reverse (make a nullable column NOT NULL), you can unload values from the nullable column, drop the nullable column, add the column back as NOT NULL, and restore the unloaded data values. The new column will not accept the NULL value, so any unloaded NULL values would have to be changed to not-null values before being restored.

      Robert

      Delete
    3. I was just thinking of both ways(not null to null & null to not null) that made me to ask for both cases.Looks like if I encounter either of situation ,it is preferable to do unload,drop column and restore(it can't be accomplished with alter).let me know if there are any other options as well.

      Delete
    4. That's the only approach that occurs to me. Either unload/drop-column/add-column/restore values or do something similar but at the whole-table level (either way, with the understanding that - in the case of going nullable to NOT NULL - any unloaded already-existing NULL values could not be restored to a column that has been changed to NOT NULL).

      Robert

      Delete
  3. Hello Robert,
    I encountered 904 situation as the package was in inoperative state(REASON 00E30305 ,TYPE 00000801).I can see alter caused the package to be invalidated & subsequent invocation of pkg to inoperative state..I'm not finding a clue on why autobind would fail & not seeing anywhere in logs stating reasons that made autorebind fail.
    Questions:
    where can I get to see the reasons/msgs staing reasons that caused autobind to fail ?
    why auto rebind would fail?
    How to find the alter statement applied to the table?
    thanks,

    ReplyDelete
    Replies
    1. As to why an auto-rebind would fail, sometimes that is related to a situation in which RACF is being used to manage Db2-internal security (in other words, SQL GRANT and REVOKE statements are NOT used to manage Db2 privileges and authorities). If that is the case at your shop, let me know.

      I would expect a failed auto-rebind operation to cause a Db2 error message to be generated, but I'm not sure as to what that message would be. Note that the 00E30064 error reason code pertains to auto-rebinds (it indicates that an object needed for the auto-rebind was not available when the auto-rebind was attempted - see https://www.ibm.com/docs/en/db2-for-zos/12?topic=codes-00e30064).

      With regard to finding the ALTER action that caused the package to be invalidated, you could check information in the SYSCOPY catalog table - a lot of ALTER actions drive SYSCOPY inserts.

      You might need to open a case with IBM Support to get additional help with this issue.

      Robert

      You might need to

      Delete
    2. In my shop,we dont issue grants & revokes.Security team manages the privilges.Dont see any messages in db2 indicating reason for autobind failing?
      Also,How do we find the if the rebind done by user or auto bind.Catalog tables gives the last rebind date & time but does not indicate if its done by auto rebind functionality or user.

      Delete
    3. 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.

      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.

      Robert

      Delete
    4. 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 foreseeing

      Delete
    5. 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.

      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).

      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.

      Robert

      Delete
    6. Wonderful!! Thanks Robert!

      Delete
  4. Is it possible to restore/go back to older access path ?
    How do we that?
    In what acess there will be a requirement to restore old access path?

    ReplyDelete
    Replies
    1. Two questions for you:
      1) Are you talking about static or dynamic SQL?
      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.

      Robert

      Delete
    2. Aplogize for the typo.Yes I meant -In what cases/situation there will be a requirement to restore old access path.
      To your question-Are you talking about static or dynamic SQL?-Both static as well as dynamic

      Delete
    3. 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.

      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).

      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.

      Robert

      Delete
  5. Hello,why we need to invalidate cache/clear cache ? I see runstats card has invalidatecache option ,What is the use of it ?

    ReplyDelete
    Replies
    1. 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.

      Robert

      Delete
    2. What are the other ways of invalidating /clearing cache other than having in runstats card.

      Delete
    3. 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.

      Robert

      Delete
    4. I see update none & report no is mandate to have invalidatecache yes.why so?
      what is significance/use of having 'no' for these two parms?
      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.
      RUNSTATS TABLESPACE LIST LIST1
      RESET ACCESSPATH

      Delete
    5. 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.

      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.

      Robert

      Delete
    6. Per your earlier response on using invalidatecache option as below :
      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.

      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.

      Delete
    7. 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:
      https://www.ibm.com/docs/en/db2-for-zos/12?topic=runstats-resetting-access-path-statistics.

      Robert

      Delete
    8. 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."
      HISTORY ACCESSPATH option -This option only records that the reset occurred and does not save the access path statistics values that are reset.

      How to collect statistics history to recover if history accesspath option is not gonna save access path statistics?

      Delete
    9. 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).

      Robert

      Delete
    10. 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.
      https://www.ibm.com/docs/en/db2-for-zos/12?topic=runstats-tablespace-syntax-options.

      Delete
    11. 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.

      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.

      Robert

      Delete
  6. Hi Robert
    In one of our shops ZPARM Plan Management is set to Extended but I don't see 3 copies of package being retained.
    I see only 1 instance of the package. What might be the reason for this.

    ReplyDelete
    Replies
    1. 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.

      Robert

      Delete
    2. Thanks Rob,
      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.

      Delete
    3. Two possibilities come to mind:
      1) PLANMGMT(OFF) is explicitly specified in REBIND package commands in this environment - that would override the ZPARM setting.
      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.

      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.

      Robert

      Delete