Monday, August 30, 2021

Db2 for z/OS: Package-Related Things of Which You Should be Aware

Db2 for z/OS packages have been around for a long time - since, I believe, V2R3, back in the early 1990s. Even so, I've found myself talking more about packages to people lately than I have in quite some years. These discussions - conducted in meetings, in phone calls and through email - have highlighted for me some related topics and items of information of which more folks should be aware. I've packaged (no pun intended) these informational nuggets in this blog entry, and I hope you'll find them to be useful. Without further ado, and in no particular order...

Many Db2 for z/OS packages are associated with static SQL, but "static SQL" is a term not universally understood by people who work with Db2.  Sure, Db2 DBAs are familiar with the concept of static SQL. So, too, are application developers who code SQL statements in certain programs - typically, programs that 1) execute in the same z/OS LPAR as the target Db2 system, 2) are written in COBOL, and 3) execute as batch jobs or CICS transactions (though they could be stored procedure programs, and might in that case be written in SQL PL).  Application developers who code Db2-accessing SQL statements in JDBC or ODBC form may not be so familiar with the static SQL concept. For those folks: a static SQL statement is one that has already been prepared for execution, via the Db2 BIND process, before it is ever even issued for execution by a program. A Db2-related SQL statement that is not static is referred to as dynamic - it is prepared for execution when Db2 first sees it coming in from an application program (statement preparation can be relatively expensive, so the prepared form of a dynamic SQL statement is stored in-memory by Db2 in its dynamic statement cache - that prepared form of the statement can be reused if the same dynamic statement comes along again). SQL in JDBC or ODBC form is dynamic from the Db2 perspective. If a Db2-accessing program contains static SQL statements, that program has an associated package, and the package in that case can be thought of as the compiled, executable form of the program's static SQL statements.

Dynamic SQL statements are also associated with packages. Any SQL statement executed in a Db2 for z/OS system will always have a package associated with it. If it's a dynamic SQL statement, the associated package will likely be one of the IBM Data Server Driver packages (sometimes referred to as the Db2 Connect packages - entitlement to use the IBM Data Server Driver is by way of a license for Db2 Connect).

Db2 packages are associated with collections. As far as I'm concerned, this is one of the best ways to conceptualize a collection: a package's collection is kind of like a table's schema name (i.e., its high-level qualifier). Does that mean that the same package can be in more than one collection? Yes - and hold onto that thought.

SQL statements of the DDL variety are also associated with packages. As previously mentioned, any Db2-targeting SQL statement will be associated with a package. That being the case, a DDL statement (data definition language, including ALTER and CREATE) will be issued by way of a package, same as a DML statement (data manipulation language, including SELECT, INSERT and UPDATE). Here's where this sometimes trips up even an experienced Db2 DBA: starting with Db2 12 for z/OS, a package's APPLCOMPAT value affects DDL as well as DML statements (with Db2 11, APPLCOMPAT was relevant only for DML statements). Want to ALTER a table with a KEY LABEL specification, to leverage the data set encryption feature of z/OS? Make sure that the APPLCOMPAT value of the package through which the ALTER TABLE statement will be issued (that might be a SPUFI or a DSNTEP2 package) has an APPLCOMPAT value of V12R1M502 or higher, because the KEY LABEL option of ALTER TABLE (and ALTER STOGROUP) was introduced with Db2 12 function level 502.

Access path changes: don't forget the PLANMGMT safety net, and don't cut a hole through that safety net if you don't have to. For a static SQL statement, access path selection (e.g., will a certain index be used in the evaluation of a predicate, will two tables be joined using the nested loop or merge scan or hybrid join method, etc.) is done at package bind time (for a dynamic SQL statement, access path selection is done when the statement is issued by an application program, unless the statement was previously prepared and its prepared form is still in Db2's dynamic statement cache). If a static SQL-containing package is rebound, access paths for the package's SQL statements could change (the APREUSE option of REBIND PACKAGE can be used to tell Db2 to stick with a package's existing SQL access paths, if possible). If a rebind action results in access path changes, the expectation is that the changes will be beneficial from a performance perspective, but that is not always the case. Sometimes, an access path change will have a negative impact on performance (incomplete or inaccurate catalog statistics, for example, could cause the Db2 query optimizer to choose an access path that appears to be good for performance but in fact is not). In such a case, the plan management feature of Db2, which is "on" by default (check the value of PLANMGMT in ZPARM), provides a safety net: it causes Db2 to retain the previous instance of a package when the package is rebound. In the event of a performance degradation caused by a rebind-related change in one or more query access paths, the previous and better-performing instance of the package can be quickly restored via another REBIND PACKAGE action with SWITCH(PREVIOUS) specified. That's great, but sometimes an organization will cut a big hole in this safety net. How? By unnecessarily doing BIND REPLACE for packages, versus REBIND. BIND REPLACE is needed when a program's static SQL statements are modified, because that necessitates a new precompile of the source program, which generates a new DBRM (a file containing the program's static SQL statements) that subsequently has to be bound to replace the prior version of the related package. If a BIND REPLACE results in a performance problem that is of an access path nature, REBIND with SWITCH(PREVIOUS) is not an option, because that restores the previous instance of the same version of the package, and the BIND REPLACE (assuming it was preceded by a Db2 precompile of the program source) generates a new version of the package. Here's where the "unnecessary" comes in: quite often, an organization will have a process whereby a static SQL-containing program will be re-precompiled and BIND REPLACE-ed whenever a program's source is changed, even when the source change does not change the program's static SQL statements. When source code other then SQL is changed, a new precompile and an associated BIND REPLACE is not necessary. Moral of the story: don't do program re-precompiles and BIND REPLACEs when you don't need to do that. And while I'm on the topic of unnecessary package-related actions...

Don't do unnecessary rebinds of packages. At many Db2 for z/OS sites, certain Db2 table spaces are REORGed quite frequently (maybe on a weekly basis), and catalog statistics are regenerated for the table spaces, and packages with static SQL statements targeting tables in the table spaces are rebound. DON'T DO THAT (the package rebinds, I mean), absent a really good reason. Here's the thing: if you have a package of well-performing static SQL statements, keep in mind that one of the really good things about static SQL is that the good performance is locked in, from an access path perspective. Why mess with that? Leave your well-performing packages alone. REORGing and RUNSTATing table spaces from time to time? Good idea. Rebinding dependent packages after running those utilities? Not such a good idea. Am I saying that you shouldn't ever rebind packages? Of course not. Just do it when it makes sense. When does it make sense? Well, it's a good idea to rebind all of your packages (and plans, too) soon after migrating to a new version of Db2, while still at the 100 function level (what we used to call conversion mode), because a) the new version's optimizer enhancements are available at that time, and b) even when access paths don't change, the package code generated by the new version of Db2 (packages are executable code) is likely to be at least a little more CPU-efficient than the code generated for the same package by the previous version of Db2. It also makes sense to rebind a package when you want a query associated with the package to use a newly created index on a table (that would of course require query re-optimization, which a rebind does). And certainly you'd rebind if you wanted or needed to change a package characteristic such as its RELEASE setting (COMMIT or DEALLOCATE) or its isolation level (e.g., cursor stability or uncommitted read) or its APPLCOMPAT value. Bottom line: have a reason for rebinding a package or packages (and "Because we just REORGed and RUNSTATed a table space on which the package depends" is usually not a good reason).

Remember that EXPLAIN information for a package is always available. Do you need to get access path information for a package that was bound without a specification of EXPLAIN(YES)? No problem: just use the EXPLAIN PACKAGE statement (this will extract access path information from the package itself - that information has been part of a package's structure since Db2 9 for z/OS). Want to know what a package's access path would be, if the package were to be rebound? No problem: just rebind the package with EXPLAIN(ONLY) - you'll get the access path information, but Db2 won't actually regenerate the package. More information about EXPLAIN PACKAGE and rebind with EXPLAIN(ONLY) can be found in an entry I posted to this blog a few months ago.

Get different behaviors for the same package by using different bind specifications and different collections. I mentioned previously that a collection is somewhat akin to a schema name for a package. Just as tables that are logically identical and have the same unqualified name can be in different schemas, so packages that are generated from the same SQL statements can be in different collections. This can be very handy when you want different behaviors for the same package in different situations. For example: suppose that in some cases you want queries associated with package PROGXYZ to be executed with cursor stability isolation, while in other cases you want the same queries to be executed with uncommitted read isolation. No prob: bind the package with ISOLATION(CS) into collection CSCOLL (or whatever name you want to use for the collection) and with ISOLATION(UR) into collection URCOLL (and that second bind action might be a BIND COPY from CSCOLL into URCOLL, with the different ISOLATION value specified). How do you "point" an application that uses the PROGXYZ package to the CSCOLL or the URCOLL collection, as needed? For a static SQL package used with (for example) a batch job or a CICS transaction, you could do this with a plan's PKLIST. You could also let the program itself operate the "switch in the tracks" by using the SET CURRENT PACKAGESET special register; or, for applications that are DRDA requesters you could point to one collection or another by way of the Db2 profile tables (and here we could be talking about alternate collections of the IBM Data Server Driver / Db2 Connect packages). You could turn statement concentration (automatic parameterizing by Db2 of dynamic query predicates that reference literal values) on or off for different applications in this way, or use different APPLCOMPAT values, or provide a different high-level qualifier for unqualified table names in SQL statements, or do a number of other things (to get the ideas flowing, check out the list of package bind options in the online Db2 documentation).

OK, that's enough package stuff for one blog entry. I hope that this information will be helpful for you. As always, thanks for stopping by.

35 comments:

  1. Interesting! How to get the list/find if the table has dynamic queries accessing this table or not?

    ReplyDelete
  2. You could create an audit policy for the table in question that would cause Db2 to generate a trace record every time the table is targeted by a data-read or data-change statement (see https://www.ibm.com/docs/en/db2-for-zos/12?topic=policy-creating-activating-audit-policies). A trace record generated as a result of the audit policy being activated would contain the ID of the data-reading or data-changing statement. You could then check to see if the statement with that ID is in the dynamic statement cache (you can externalize the contents of the dynamic statement cache with the statement EXPLAIN STMTCACHE ALL.

    This task would probably be made easier with a data access auditing tool. IBM's tool of this nature that works with Db2 for z/OS is called IBM Security Guardium Data Protection (see https://www.ibm.com/products/ibm-guardium-data-protection).

    Robert

    ReplyDelete
  3. When I display the package details from RC query -CA tool(I see series of info displayed like creator,binddtae,time,versino etc) .How do I know the catalog tables involved for all this information to be diplayed in the screen.

    ReplyDelete
  4. Check the SYSPACKAGE table in the Db2 catalog (see https://www.ibm.com/docs/en/db2-for-zos/12?topic=tables-syspackage).

    Robert

    ReplyDelete
  5. I saw below error in job.
    DSNT408I SQLCODE = -923, ERROR: CONNECTION NOT ESTABLISHED: DB2 ACCESS, REASON 00E30302, TYPE 00000800, NAME XXXXX.
    reason code states:
    This reason code is used by the automatic BIND function to indicate that the
    plan currently being allocated is not operational. The automatic BIND for the
    plan failed because the plan was dependent on something that was altered or
    dropped. An unavailable database might also cause this problem.
    Is there a way to find what was altered or dropped?

    ReplyDelete
  6. That's an interesting situation. I generally don't think of plans as being dependent on catalog objects - not since the old days, when DBRMs were bound directly into plans. You might check the row(s) for the plan in the SYSPLANDEP catalog table.

    The word, "ACCESS," in the error message suggests to me that the problem was due to something required by the plan being in a state of restricted access. If there is a row for the plan in SYSPLANDEP, and the row identifies a database object on which the plan is dependent, you might issue a -DISPLAY DATABASE command to check on the status of that object.

    Robert

    ReplyDelete
  7. There is no row for the plan in SYSPLANDEP catalog table.

    ReplyDelete
    Replies
    1. Could it be that a privilege or authority held by the plan's owner, and which was necessary for the initial successful bind of the plan, was revoked from the owner? That would presumably cause the plan to become invalid, and that could also cause an auto-rebind of the plan to fail, in which case the plan's status should go to inoperative.

      Robert

      Delete
    2. If that was an privilege issues,Would'nt that get listed somewhere in logs.There is no trace to find what caused this issue.

      Delete
  8. When I tried to rebind a package,I got error due to invalid syntax so ideally rebind was unsuccessfull.When I looked at the package detail,The ORIGIN is "automatic bind " and BOUNDBY is "my user id".How is that possible? .When I dint do binds/rebinds how will it show my id when the origin is automatic bind (which is done by db2 right?).

    ReplyDelete
    Replies
    1. One possibility is that, in this environment, RACF is being used to manage Db2-internal security (i.e., instead of Db2 privileges being managed via GRANT and REVOKE SQL statements, they are managed via RACF resource profiles and associated RACF PERMIT commands). In that case, by default if there is an auto-rebind then the ID that will be checked for the authority to do the rebind is not the ID of the package owner; rather, it will be the auth ID of the user (or application process) that requested execution of the package (an auto-rebind occurs when a package that has been marked invalid - perhaps because of an ALTER of an object on which the package is dependent - is requested for execution). In many cases, the ID of the process that has requested execution of the invalidated package will not have the privileges necessary for a successful rebind of the package. If this is the case, the solution is to change the value of the Db2 ZPARM parameter AUTHEXIT_CHECK to DB2 from PRIMARY (see https://www.ibm.com/docs/en/db2-for-zos/12?topic=panel-auth-exit-check-authexit-check-subsystem-parameter).

      Robert

      Delete
  9. Hello,
    When it will be benefit to use release(deallocate)? A Package is bound with release(commit)-was running into below issue when this package was included for rebinds along with many other packages list.

    DSNT500I DSNTBRB2 RESOURCE UNAVAILABLE
    REASON 00E30083
    TYPE 00000801
    NAME SI001.ALHK555X.1AF62BDB051E74BC
    DSNT233I UNSUCCESSFUL REBIND FOR
    PACKAGE =
    But many of users wanted to try with deallocate? I went thro many sites ,looks both has its own pros.So I'm not sure whe to use which

    ReplyDelete
    Replies
    1. That error suggests that a rebind failed because the target package was in-use and remained in that state until the rebind command timed out. RELEASE(DEALLOCATE) would not have helped in that situation. What would have helped in that situation is the rebind phase-in functionality that was introduced with function level 505 of Db2 12 for z/OS (see https://robertsdb2blog.blogspot.com/2020/12/db2-for-zos-what-do-you-know-about.html).

      RELEASE(DEALLOCATE) can enhance application CPU efficiency for an application that uses "persistent" threads - those being threads that persist through commits.

      Robert

      Delete
    2. I see this in my environment:
      CURRENT FUNCTION LEVEL(V12R1M503)
      HIGHEST ACTIVATED FUNCTION LEVEL(V12R1M503)
      HIGHEST POSSIBLE FUNCTION LEVEL(V12R1M510)
      I see we have 503 ,but highest function level is 510 does that mean we use 505 as well/ 505 features?

      Delete
    3. So looks like then I cannot use rebind phase in feature.If this is the case,then what is best possible approach/option to not have process timeout because of package in use.

      Delete
    4. Prior to the availability of rebind phase-in functionality, about the best you can do is to try to do the rebind during a time when execution of the package is less frequent than at other times.

      Robert

      Delete
  10. There is an trigger(after insert into a table) which has insert to few other tables embedded in it.
    DSNT408I SQLCODE = -723, ERROR: AN ERROR OCCURRED IN A TRIGGERED SQL STATEMENT IN TRIGGER
    INFORMATION RETURNED: SQLCODE -913,
    SQLSTATE 57033, MESSAGE TOKENS 00C900BA
    When I digged into sqlcode 913 -I see that
    DSNT408I SQLCODE = -913, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY
    DEADLOCK OR TIMEOUT. REASON CODE 00C9008E, TYPE OF RESOURCE
    00000801, AND RESOURCE NAME TBLS.1BC274BD1C8634E5
    Why is the resource type points to a package? it should be either table,tablespace(for an insert to table) right?

    ReplyDelete
    Replies
    1. That would likely be the package associated with the trigger (for some information about packages associated with triggers, see this page in the online Db2 for z/OS documentation: https://www.ibm.com/docs/en/db2-for-zos/12?topic=trigger-packages). For some reason, the trigger's package was not available for execution when the trigger was fired. To find out why the trigger's package was not available, you might need to open a case with IBM Support.

      Robert

      Delete
  11. What all activities change Lastused column of a package?As far as I know.rebinds and binds change the lastused date and thats what I see from manuals too.But I see apart from these commands,some other activites are causing update to lastused column but couldnt find what are those from any logs

    ReplyDelete
    Replies
    1. The LASTUSED column of SYSIBM.SYSPACKAGES was added with Db2 10 for z/OS. You can download a PDF version of the IBM redbook titled, "Db2 10 for z/OS Technical Overview" from this web page: https://www.redbooks.ibm.com/abstracts/sg247892.html. In section 7.7 of that redbook, you will see that the value if the LASTUSED column for a package is updated "when the package header is requested from EDM." This will most frequently be associated with a request to execute the package.

      Robert

      Delete
    2. Thanks for sharing!!

      Delete
  12. According to the Db2 for z/OS documentation at https://www.ibm.com/docs/en/db2-for-zos/13?topic=applications-changes-that-invalidate-packages, renaming an index does not invalidate dependent packages. I suspect that when a package references an index, it does so by way of the Db2 identifiers associated with the index (DBID, OBID, ISOBID). My assumption is that these identifiers do not change when an index is renamed.

    Robert

    ReplyDelete
  13. On receiving below error ,I checked the package status -it was "INVALID" but "OPERATIVE".I have seen packages invalid & inoperative whenever I get this reason code-00E30305 .But this time ,it was surprsiing that it was only invalid.How is that possible? shouldn't it be inoperative too?
    DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN
    UNAVAILABLE RESOURCE. REASON 00E30305, TYPE OF RESOURCE
    00000801, AND RESOURCE NAME ED011.DC006.1AE00GG00478C819

    ReplyDelete
    Replies
    1. Are you saying that, for the package in question, you saw VALID = 'N' and OPERATIVE = 'Y' in the package's row in SYSIBM.SYSPACKAGE, after having received this message?

      Robert

      Delete
    2. Yes correct .That's what I meant .

      Delete
    3. Yes Correct that what I meant .How is that possible ? it should be inoperative after we receive-REASON 00E30305, isn't?

      Delete
    4. This does not make sense to me. The 00E30305 reason code means that the package is inoperative (or that it is in a REBIND-advisory state, assuming a Db2 13 environment, a package bound with DEPLEVEL(STATEMENT), and function level V13R1M504 or higher activated). If you saw that this package, in the specified collection and with the specified consistency token, is in an OPERATIVE state, I cannot explain that. You might need to open a case with IBM Support to see what is going on.

      Robert

      Delete
  14. Earlier in DB2 v8 or V9 we had an formula or method to convert contoken value to timestamp .There is an way to get the timestamp from hexa value of contoken.I couldnt recall.Do you know how to do that ?

    ReplyDelete
    Replies
    1. Sorry, I do not know how to convert a Db2 for z/OS consistency token to a timestamp.

      Robert

      Delete