tag:blogger.com,1999:blog-45165337113302470582024-03-19T04:13:50.020-07:00Robert's Db2 blogThis is the blog of Robert Catterall, an IBM Db2 for z/OS specialist. The opinions expressed herein are the author's, and should not be construed as reflecting official positions of the IBM Corporation.Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.comBlogger234125tag:blogger.com,1999:blog-4516533711330247058.post-35816560468199040102024-02-27T18:14:00.000-08:002024-02-27T18:14:35.752-08:00Db2 13 for z/OS: Now You Can Dynamically Remove, as Well as Add, Active Log Data Sets<p><span style="font-family: arial;">Db2 10 for z/OS (which came out back in 2010) provided a new capability related to management of a Db2 subsystem's active log data sets (known, along with the archive log data sets, as the subsystem's "log inventory"). The enhancement: the NEWLOG option of the Db2 command -SET LOG. With this added functionality, a Db2 for z/OS system administrator could add new active log data sets to a Db2 subsystem's log inventory, <i>without having to bring the subsystem down</i> (the system administrator would probably, in fact, add </span><span style="font-family: arial;">new </span><u style="font-family: arial;">pairs</u><span style="font-family: arial;"> of active log data sets, as you always want to use dual logging to avoid a single point of failure for system and data recovery operations)</span><span style="font-family: arial;">. Prior to this Db2 10 enhancement, adding active log data sets to a subsystem's log inventory could only be accomplished through execution of the DSNJU003 utility (also referred to as the "change log inventory" utility), and DSNJU003 can only be executed when the target Db2 subsystem is down.</span></p><p><span style="font-family: arial;">The ability to dynamically add pairs of active log data sets to a Db2 subsystem's log inventory was welcomed by many Db2 people, and you can probably imagine why. A Db2 subsystem's active log data sets can be thought of, logically, as a ring of data sets around the Db2 subsystem. Suppose there are 20 pairs of active log data sets in this logical ring. Db2 writes information to pair #1, and when that pair of data sets is filled up then information is written to pair #2, and when that pair is filled up then information is written to pair #3, and so on around the ring. Meanwhile, not long after the filling up of active log data set pair #1, the information written to that pair of data sets will be copied to a pair of archive log data sets, and that action will make active log data set pair #1 reusable, so that new information can be written to that pair of data sets when Db2 comes back around the ring to them. The same archive operation is performed for other active log data set pairs after they have been filled up, making them reusable when their turn in the rotation comes up again to be the current active log data set pair.</span></p><p><span style="font-family: arial;">All well and good - unless something goes wrong with the archive log write process. If filled-up active log data set pairs can't be archived, they can't be made reusable, and when Db2 has gone around the ring and comes back to the not-reusable active log data set pairs, logging will stop, and when logging stops just about everything stops. In a pre-Db2 10 environment, you could add active log data set pairs to a subsystem's log inventory to buy more time (by providing more space for logging) as you worked to fix whatever was impeding the log archiving process, but at the cost of stopping the Db2 subsystem in order to execute the DSNJU003 utility. Not good. Being able to buy extra fix-the-archiving-problem time by <u>dynamically</u> adding new pairs of active log data sets to a Db2 subsystem's log inventory, while the subsystem was still up and running, made for a much better situation.</span></p><p><span style="font-family: arial;">Fast-forward to Db2 13 for z/OS, and now we get (once function level V13R1M500 has been activated) the ability to dynamically <u>remove</u> active log data set pairs, thanks to the new REMOVELOG option of the -SET LOG command. The value of dynamic (i.e., while the Db2 subsystem is up and running) removal of active log data set pairs is as a complement to the dynamic-add functionality we've had since Db2 10. Together, the NEWLOG and REMOVELOG options of the -SET LOG command provide a capability that can be very useful - namely, online <u>replacement</u> of a Db2 subsystem's active log data set pairs with <i>better</i> data set pairs.</span></p><p><span style="font-family: arial;">"Better?" How so? Well, usually this will mean bigger and/or encrypted. Let's take the data set size case. Suppose you have a production Db2 subsystem that has 20 pairs of active log data sets, each data set being 2 GB in size. You're going through those active log data sets faster than you'd like - maybe filling up three or four (or more) pairs in an hour when the system is busy. You'd rather have active log data sets that are 8 GB apiece, versus 2 GB (Db2 12 for z/OS took the maximum size of an active log data set from 4 GB to 768 GB). Can you go from 2 GB active log data sets to 8 GB active log data sets without stopping the Db2 subsystem? With Db2 13, you can. Here's how that would work:</span></p><p></p><ol style="text-align: left;"><li><span style="font-family: arial;">You dynamically add 20 pairs of active log data sets that are sized at 8 GB apiece, using the NEWLOG option of the -SET LOG command (a Db2 subsystem can have up to 93 pairs of active log data sets).</span></li><li><span style="font-family: arial;">After the older and smaller active log data sets have been archived, dynamically remove them from the Db2 subsystem's log inventory via the new (with Db2 13) REMOVELOG option of the -SET LOG command.</span></li></ol><span style="font-family: arial;">Now you have 20 pairs of active log data sets, each sized at 8 GB, when before you had 20 pairs of active log data sets sized at 2 GB apiece, and in getting from A to B you never had to stop the Db2 subsystem.</span><p></p><p><span style="font-family: arial;">The same approach could be used to go from 20 pairs (for example) of unencrypted active log data sets to 20 pairs of encrypted active log data sets in an online way (referring here to exploitation of the data set encryption feature of z/OS):</span></p><p></p><ol style="text-align: left;"><li><span style="font-family: arial;">Dynamically add 20 pairs of active log data sets with which an encryption key label was associated at data set creation time.</span></li><li><span style="font-family: arial;">When the older unencrypted data sets have been archived, dynamically remove them from the Db2 subsystem's log inventory.</span></li></ol><span style="font-family: arial;">In these example use cases, I've utilized the phrase, "when the older (smaller and/or unencrypted) actice log data sets have been archived, dynamically remove them." That suggests that trying to dynamically remove a not-yet-archived active log data set could be problematic. Do you need to worry about this? No. Why not? Because Db2 won't let you accidentally shoot yourself in the foot when using the REMOVELOG option of -SET LOG. Specifically:</span><p></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;">Db2 won't let you remove an active log data set to which it is currently writing information.</span></li><li><span style="font-family: arial;">Db2 won't let you remove a log data set in the pair that is next in line for the writing of log information.</span></li><li><span style="font-family: arial;">Db2 won't let you remove an active log data set that has not been archived (i.e., an active log data set that is not in REUSABLE status).</span></li><li><span style="font-family: arial;">Db2 won't let you remove an active log data set that is currently in use (for example, an active log data set that is being read by a RECOVER utility job).</span></li></ul><span style="font-family: arial;">If you try to dynamically remove an active log data set to which Db2 is currently writing, or one that is next in line for writing, or one that has not been archived (i.e., is not in the REUSABLE state), the -SET LOG command will fail with the message DSNJ391I. If the active log data set you're trying to dynamically remove does not have one of these characteristics but is currently in use by some process, that data set will be marked as REMOVAL PENDING, and message DSNJ393I will be issued. In that case, you can remove the data set from the log inventory by issuing -SET LOG with REMOVELOG again when the data set is no longer in use. Alternatively, if the Db2 subsystem is standalone in nature (as opposed to being a member of a Db2 data sharing group), the data set will be removed from the log inventory automatically when the subsystem is next recycled (in a data sharing environment, subsequent re-issuance of -SET LOG with the REMOVELOG option is required to remove a REMOVAL PENDING data set from the log inventory). Note that if an active log data set has been marked as REMOVAL PENDING, it will not be used again by Db2 for read or write purposes. Note also that information about an active log data set that is in REMOVAL PENDING status can be checked via the output of the Db2 command -DISPLAY LOG DETAIL (the DETAIL option was added with function level 500 of Db2 13). When you see, in the output of -DISPLAY LOG DETAIL, that an active log data set in REMOVAL PENDING status has 0 readers, you know that it is no longer in use and can be physically removed from the log inventory with another issuance of -SET LOG with REMOVELOG.</span><p></p><p><span style="font-family: arial;">One more thing: I have been referring to removal of an active log data set "from the log inventory" of a Db2 subsystem. In the Db2 documentation, you'll see references to removal of an active log data set "from the BSDS" of a Db2 subsystem. The documentation is saying the same thing I'm saying. The BSDS - short for bootstrap data set - contains information about a Db2 subsystem's active and archive log data sets.</span></p><p><span style="font-family: arial;">OK, there you have it. If you want to upgrade your active log data sets in one or more ways - maybe bigger than they are now, maybe encrypted versus unencrypted - then the REMOVELOG option of -SET LOG (thanks, Db2 13), together with the NEWLOG option (thanks, Db2 10) is your ticket for getting that done without having to stop the Db2 subsystem in question. Just another way that Db2 for z/OS enables you to take high availability higher than ever before.</span></p>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com1tag:blogger.com,1999:blog-4516533711330247058.post-31818429864676110942024-01-31T06:13:00.000-08:002024-01-31T06:13:30.990-08:00Db2 for z/OS: Stop Making APPLCOMPAT in ZPARM More Important Than It Is<p><span style="font-family: arial;">The APPLCOMPAT option of the Db2 for z/OS BIND and REBIND PACKAGE commands is really important - that's why I posted <a href="https://robertsdb2blog.blogspot.com/2019/06/db2-for-zos-talking-about-applcompat.html"><span style="color: #2b00fe;">part 1</span></a> and <a href="https://robertsdb2blog.blogspot.com/2019/07/db2-for-zos-talking-about-applcompat.html"><span style="color: #2b00fe;">part 2</span></a> blog entries on the topic back in 2019. The APPLCOMPAT parameter in ZPARM, on the other hand (referring to DSNZPARM, the data set that contains a Db2 subsystem's configuration parameter settings), is less important. I pointed this out in part 1 of the aforementioned two-part blog entry on APPLCOMPAT, but I still find that plenty of Db2 for z/OS people ascribe significance to the ZPARM parameter APPLCOMPAT that just doesn't jibe with reality. That being the case, I am writing this blog entry in the hope that it will help to drive home the point that the ZPARM parameter called APPLCOMPAT should (generally speaking) not be the main focus of your APPLCOMPAT-related concerns.</span></p><p><span style="font-family: arial;">To illustrate the point that plenty of people continue to over-inflate the importance of the APPLCOMPAT parameter in ZPARM, I'll share with you a question that a Db2 for z/OS person sent to me by way of one of my colleagues. The question was, basically, "We are getting ready to activate Db2 12 function level V12R1M510 (a prerequisite for migration from Db2 12 to Db2 13 for z/OS). Can we be pretty much assured that doing this will not cause SQL behavioral changes <i><b>if we leave the value of APPLCOMPAT in ZPARM unchanged</b> </i>(they had this ZPARM parameter set to V10R1)?" In responding to this question, I explained that in advancing a Db2 system's active function level, one can indeed protect application programs from the risk of SQL behavioral changes (I'll explain what that means in a moment), but, I noted, this SQL behavioral change protection is provided by the APPLCOMPAT <u>package bind specification</u>, NOT by the APPLCOMPAT parameter in ZPARM. You can take a Db2 system's active function level as high as you want, and that will not lead to application-affecting SQL behavioral changes <u>as long as you don't change the APPLCOMPAT value of your applications' Db2 packages</u>. The value of the APPLCOMPAT parameter in ZPARM is only somewhat relevant to this discussion.</span></p><p><span style="font-family: arial;">OK, what's all this about "SQL behavioral changes?" The term refers to this situation: same SQL statement, same data, <b><i>different result</i></b>. You might think, "How could that happen?" Well, every now and then, the Db2 for z/OS development team decides that the behavior of a given SQL statement should change, for one reason or another (and it's well-considered - these changes are not effected lightly). That change can be introduced with a new version or a new function level of Db2. My favorite example of a Db2 for z/OS SQL behavioral change is one that happened with Db2 11. In a Db2 10 environment, you could use a SQL statement to cast an eight-byte store clock value (a time value that a program can obtain from the z/OS operating system) as a Db2 timestamp value. In a Db2 11 system, that same SQL statement - cast an eight-byte store clock value as a Db2 timestamp - would fail with a -180 SQL error code. Same SQL statement, same data, different result in a Db2 11 versus a Db2 10 environment.</span></p><p><span style="font-family: arial;">Here's one reason I really like this example: how many programs do <u>you</u> have that need to cast an eight-byte store clock value as a Db2 timestamp? Probably none - <i>and this is typically the case for Db2 SQL behavioral changes - they usually affect either zero or very few of an organization's Db2-accessing programs.</i> Alright, but what if you <u>did</u> have programs that needed the Db2 10 behavior of the Db2 TIMESTAMP function? Would you have been up a creek when your Db2 system went from Db2 10 to Db2 11? No - you would have been fine in that case, because you could just bind the Db2 packages used by those programs with APPLCOMPAT(V10R1), and that would mean that the programs would execute with Db2 10 SQL behavior, and <u>that</u> would mean that those programs could cast an eight-byte store clock value as a Db2 timestamp. See - it's the APPLCOMPAT <u>package bind</u> specification that provides protection (when needed) from Db2 SQL behavioral changes.</span></p><p><span style="font-family: arial;">[By the way, in the Db2 for z/OS documentation, what I have been calling "SQL behavioral changes" are referred to as "SQL incompatibilities." <a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=zos-application-compatibility-levels-in-db2"><span style="color: #2b00fe;">These are documented</span></a> for each Db2 application compatibility level, going back to V10R1 (that's as far back as Db2 application compatibility goes).]</span></p><p><span style="font-family: arial;">So, I said up front that the APPLCOMPAT parameter is ZPARM is not as important as the APPLCOMPAT specification for your Db2 packages. Does that mean that the ZPARM has no significance? No. What is the purpose of the APPLCOMPAT parameter in ZPARM? It's this: the ZPARM parameter provides the default value for a package's APPLCOMPAT setting when the BIND PACKAGE command is issued without an APPLCOMPAT specification. That's it. I tell people to think of APPLCOMPAT in ZPARM as being like a cubbyhole. A BIND PACKAGE command may be issued without an APPLCOMPAT specification. The package in question needs an APPLCOMPAT value. Where is Db2 going to get that value, when the value was not provided via the BIND PACKAGE command? Db2 in that case is going to look in the cubbyhole labeled APPLCOMPAT in ZPARM. In that cubbyhole is a piece of paper (figuratively speaking) on which (for example) V12R1M509 is written. OK, that will be the package's APPLCOMPAT value.</span></p><p><span style="font-family: arial;">[You might wonder: what if <u>REBIND</u> PACKAGE is issued without an APPLCOMPAT specification? Will the rebound package in that case get the APPLCOMPAT value to which the ZPARM parameter APPLCOMPAT has been set? Probably not. Why not? Because it is very likely that a package being rebound already has an APPLCOMPAT value, and in that case if the REBIND PACKAGE command is issued without an APPLCOMPAT specification then the package's current APPLCOMPAT value will be retained. For REBIND PACKAGE, then, the APPLCOMPAT parameter in ZPARM is relevant only when the REBIND PACKAGE command is issued without an APPLCOMPAT specification <u>and</u> the package in question does not already have an APPLCOMPAT value (again, unlikely, though not impossible - you can check on this via a query of the SYSIBM.SYSPACKAGE catalog table, which has a column named APPLCOMPAT).]</span></p><p><span style="font-family: arial;">Given that APPLCOMPAT in ZPARM simply provides the default value for APPLCOMPAT when BIND PACKAGE is issued without an APPLCOMPAT specification, what should the value of this ZPARM parameter be? There isn't a right or wrong answer to this question - it's up to you. Personally, I'd lean towards making the value of APPLCOMPAT in ZPARM as high as it can be, which would be equal to the currently active function level in a Db2 system. Why would I want that? Because APPLCOMPAT, in addition to providing protection (when needed) from Db2 SQL incompatibilities, also enables use of newer SQL syntax and functionality. If I have APPLCOMPAT in ZPARM set to, for example, V10R1, and BIND PACKAGE at my site is typically issued without an APPLCOMPAT specification, I am limiting application programmers to SQL syntax and functionality only up to the Db2 10 level - can't use newer built-in functions such as LISTAGG and PERCENTILE_CONT, can't use Db2 global variables, can't use Db2 arrays, can't use newer special registers such as CURRENT LOCK TIMEOUT, etc. Is that what you want? Sure, if a program using one of those newer SQL capabilities fails at bind time because of the default V10R1 APPLCOMPAT value, you can fix that problem by issuing BIND PACKAGE a second time with an APPLCOMPAT specification that is current enough to support the desired functionality, but again, is that what you want?</span></p><p><span style="font-family: arial;">At some Db2 for z/OS sites, APPLCOMPAT in ZPARM is indeed set at V10R1. Why so low? One reason maybe the misunderstanding (to which I've referred) of the purpose of the ZPARM parameter. Alternatively, maybe APPLCOMPAT in ZPARM is set at V10R1 because of concern about BIND PACKAGE issued for programs that aren't net new but rather have had a bit of an SQL change (which would then require a new precompile and BIND in the case of a static SQL-issuing program, as opposed to a REBIND). A person might think, "What if there's an existing program with 20 static SQL statements, and a programmer changes just one of those statements? When there is a BIND PACKAGE (with ADD or REPLACE, as the case may be) for that program's Db2 package, and the BIND PACKAGE is issued without an APPLCOMPAT specification, I want a from-the-ZPARM-parameter default APPLCOMPAT value that will have the 19 SQL statements that weren't changed behaving as they always have." OK. I get that. Like I said, it's up to you. Just keep in mind that the risk of adverse impact on your programs from Db2 SQL incompatibilities is usually very low - these incompatibilities are relatively few and far between, and they tend to affect few if any of your Db2-accessing programs.</span></p><p><span style="font-family: arial;">The main point I want to make is this: when you change the value of the APPLCOMPAT parameter in ZPARM, that action is <u>not</u>, in and of itself, going to cause Db2-accessing programs in your environment to suddenly start behaving differently. All you've done with the ZPARM parameter update is change the APPLCOMPAT value that a package will get if a BIND PACKAGE command is issued without an APPLCOMPAT specification. Consider the ZPARM value change in that light, and act accordingly.</span></p>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com2tag:blogger.com,1999:blog-4516533711330247058.post-38917554049718008202023-12-27T13:46:00.000-08:002023-12-27T13:46:57.815-08:00Db2 for z/OS: Code Level, Catalog Level, Function Level, and More<p><span style="font-family: arial;">In a Db2 for z/OS context, the terms "code level," "catalog level," and "function level" were introduced when the Db2 for z/OS development team went to the continuous delivery mechanism for delivering new product functionality in-between the availability dates of new versions of the DBMS. That was a little over 7 years ago, referring to the general availability of Db2 12 - the first continuous-delivery version of Db2 for z/OS. And yet, there remains a good bit of misunderstanding among some in the Db2 for z/OS user community regarding basic concepts that are of foundational importance in a continuous-delivery sense. That being the case, I'll try to shed some clarifying light on the subject via this blog entry.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Think about an upside-down hierarchy</b></span></p><p><span style="font-family: arial;">By "upside-down" hierarchy, I mean one that goes bottom-up from a dependency perspective. At the bottom - the foundation - you have the Db2 system's code level. This has to do largely with the currency of the code in the Db2 system's load library (or libraries, if this is a Db2 data sharing group - it's a best practice in that case for each member subsystem to have its own load library). This is the code that is loaded into memory when the Db2 system is started. You obviously don't have availability of Db2 functionality if that functionality is not present in the Db2 system's code; so, if a Db2 system's code level is 121505 (indicating code that includes functionality delivered up through Db2 version 12 function level 505), you can't create a stored procedure with <a href="https://robertsdb2blog.blogspot.com/2021/07/create-or-replace-agile-deployment-of.html"><span style="color: #2b00fe;">CREATE OR REPLACE</span></a> syntax because that syntax was introduced with Db2 12 function level (FL) 507 - by definition, a Db2 12 FL505 code level does not include functionality first delivered by Db2 12 FL507 code.</span></p><p><span style="font-family: arial;">I mentioned that a Db2 for z/OS system's code level is generally reflective of the currency of the Db2 code in question. Here's what that means: over the course of time, it's normal for the code of a Db2 system (and for other subsystems in a z/OS LPAR and for the z/OS LPAR itself) to be taken to a more-current maintenance level - ideally, this will be done 2-4 times per year, and often the aim is to take the code in the z/OS LPAR (Db2 code included) to a higher RSU level (RSU - short for <a href="https://www.ibm.com/docs/en/zos/2.5.0?topic=guide-recommended-service-upgrade-rsu"><span style="color: #2b00fe;">Recommended Service Upgrade</span></a> - is a packaging of z/OS and z/OS-related software maintenance that facilitates upgrading the service currency of a z/OS system). This process involves application of PTFs ("fixes," in z/OS parlance) to code in a z/OS system, including Db2 code. Maybe, in the course of one of these service-upgrade procedures, the fix for APAR PH33727 is applied to the system's Db2 code (that which a fix "fixes" is described via the associated APAR, i.e., the APAR describes what is changed or enhanced by the fix). APAR PH33727</span><span style="font-family: arial;"> </span><span style="font-family: arial;">is the one associated with </span><a href="https://www.ibm.com/docs/en/db2-for-zos/12?topic=d1fl-function-level-510-v12r1m510-activation-enabled-by-apar-ph33727-april-2021" style="font-family: arial;"><span style="color: #2b00fe;">Db2 12 function level 510</span></a><span style="font-family: arial;">, and when the corresponding PTF gets applied to a Db2 system's code then that system's Db2 code level will go to 121510. Does that mean that functionality delivered through Db2 12 function level 510 is now available in the system? No - there are further dependencies in the bottom-up hierarchy.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Next Db2 level up from code: catalog</b></span></p><p><span style="font-family: arial;">The Db2 catalog is the set of tables that basically contain metadata - "data about the data," and about the related Db2 structures (e.g., tables, table spaces, indexes) and other associated database objects (e.g., packages, routines). Sometimes, a Db2 function level introduces new Db2 features that have catalog dependencies - in other words, these are new features that cannot be used until some Db2 catalog changes that support the new features have been effected. Take, for example, Db2 12 function level 509. That function level introduced the ability to specify a data compression type at the individual table space level, or at the partition level for a range-partitioned table space (two data compression types are available in a Db2 for z/OS system - one, which is based on the Lempel-Ziv compression algorithm, is referred to as fixed-length, and the other is Huffman compression). For a Db2 DBA to be able to utilize this feature, the first requirement is the ability to specify COMPRESS YES FIXEDLENGTH or COMPRESS YES HUFFMAN in a CREATE or ALTER TABLESPACE statement. That ability is provided in the Db2 code starting with code level 121509; however, the new forms of the COMPRESS YES clause can't be used unless Db2 can record in the catalog the fact that fixed-length or Huffman compression is used for a given table space or table space partition. That cataloging capability is provided by the COMPRESS_USED column that is added to the catalog table SYSIBM.SYSTABLEPART when the Db2 catalog level goes to V12R1M509 - hence, getting the catalog level to V12R1M509 is required for compression-type specification at the table space or partition level in a Db2 12 system (by the way, "fixed length," in a Db2 data compression context, does not refer to the length of rows in a table - it refers to the length of substitution values in a compression dictionary).</span></p><p><span style="font-family: arial;">When there is a requirement to take a Db2 catalog to a higher level, that change is accomplished via execution of the Db2 utility called CATMAINT, with a specification of (for example) UPDATE LEVEL(V12R1M509). Note that if a Db2 system's catalog is currently at, say, the V12R1M500 level, it can be taken straight to the V12R1M509 level with one execution of CATMAINT - that one execution of the utility would make the catalog changes associated with level 509, and also the changes associated with other catalog levels between 500 and 509.</span></p><p><span style="font-family: arial;">Sometimes, a Db2 function level introduces new capabilities that do not require catalog changes. In such cases, the catalog only has to be at the level related to the last preceding function level that did require catalog changes. For example, the features of Db2 12 function level 510 have no catalog dependencies; thus, there us no 510 catalog level, and use of Db2 12 FL510 functionality can be available when the Db2 system's catalog level is V12R1M509 (t</span><span style="font-family: arial;">he description of a function level in the Db2 for z/OS documentation always lets you know if the function level requires catalog changes).</span></p><p><span style="font-family: arial;">I mentioned in the preceding sentence that Db2 12 FL510 functionality "can be" available when the Db2 system's catalog level is V12R1M509. Does that mean that something other than a catalog level change can be required to use the features of a Db2 function level? Yep - that's exactly what that means.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Next level up: activated function level</b></span></p><p><span style="font-family: arial;">For the continuous delivery mechanism for Db2 new-function delivery to work in a practical sense, the "turning on" of a function level's new features had to be made an asynchronous event with respect to the up-leveling of Db2 code that would introduce the features to the Db2 subsystem's load library. If this were not the case - if, instead, a Db2 code level's new features were instantly available once present from a load library perspective - then Db2 for z/OS systems programmers might hesitate to upgrade the maintenance level of a Db2 system out of concern about readiness to provide support and guidance in the use of the new features. That would not be a good thing - z/OS and z/OS subsystems function best when they are at a relatively current level of maintenance.</span></p><p><span style="font-family: arial;">The means through which adding new features to Db2 code is made asynchronous to having that new code be usable in a Db2 system is the Db2 command -ACTIVATE FUNCTION LEVEL; so, a Db2 system's code level might be 121509, and the system's Db2 catalog level might be V12R1M509, but the previously-mentioned ability to issue ALTER TABLESPACE (or CREATE TABLESPACE) with a COMPRESS YES HUFFMAN specification won't be there until a Db2 administrator has issued the command ACTIVATE FUNCTION LEVEL (V12R1M509). Thanks to the -ACTIVATE FUNCTION LEVEL command, a Db2-using organization can decide when they want the features introduced in a Db2 code level to be usable in their Db2 environment.</span></p><p><span style="font-family: arial;">So, does the -ACTIVATE FUNCTION LEVEL command put us at the top of our upside-down Db2 continuous delivery hierarchy? Not quite. One to go...</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>The last level: application compatibility</b></span></p><p><span style="font-family: arial;">In a typical production Db2 for z/OS system, there's a lot going on - lots of different applications accessing Db2 for z/OS-managed data, lots of DBA activity related to administering the system, lots of new-program deployment action, etc. In light of that fact, the -ACTIVATE FUNCTION LEVEL command is a pretty big switch. What if the immediate need that an organization has for a given Db2 function level is related to exploitation of a new feature for a single application, or for one particular database administration task? Db2 application compatibility levels provide a way to very selectively exercise functionality that has been newly activated in a Db2 system. Db2 application compatibility levels are managed primarily through a Db2 package bind parameter called APPLCOMPAT (you might want to check out the <a href="https://robertsdb2blog.blogspot.com/2019/06/db2-for-zos-talking-about-applcompat.html"><span style="color: #2b00fe;">part 1</span></a> and <a href="https://robertsdb2blog.blogspot.com/2019/07/db2-for-zos-talking-about-applcompat.html"><span style="color: #2b00fe;">part 2</span></a> entries on APPLCOMPAT that I posted to this blog a few years ago). Returning to the previously used example, let's say that a Db2 DBA wants to alter a table space to use Huffman compression. Is it enough for the Db2 system's code level to be 121509, and for the catalog level to be V12R1M509, and for V12R1M509 to be the activated function level? No - that's not enough. The DBA will issue the ALTER TABLESPACE statement with a COMPRESS YES HUFFMAN specification by way of a Db2 package (there is <u>always</u> a package associated with execution of a Db2 SQL statement). That package might be related to one of the Db2-provided programs often used by DBAs to do their work - maybe SPUFI, or DSNTEP2. The package, like all packages, will have an APPLCOMPAT specification. For the ALTER TABLESPACE with COMPRESS YES HUFFMAN to execute successfully, the package through which the statement is issued - DSNTEP2, let's say - must have an APPLCOMPAT specification of not less than V12R1M509.</span></p><p><span style="font-family: arial;">As this example suggests, a package's APPLCOMPAT value enables a program that issues SQL through the package to utilize SQL syntax that was introduced with a given Db2 function level. That is one purpose of the APPLCOMPAT package bind specification. The other purpose of APPLCOMPAT is to enable a program to get the SQL <u>behavior</u> of an <u>earlier</u> version and function level of Db2 for z/OS, if that older SQL behavior is needed. See, there are times when, going from one version or function level of Db2 to another, the behavior of a SQL statement will change. What does that mean? It means same SQL statement, same data, different result. This kind of change is referred to in the Db2 for z/OS documentation as a SQL incompatibility. There are times when a program executing in a Db2 system with function level X activated needs the behavior that a SQL statement had with a Db2 version or function level that is older than X. APPLCOMPAT can deliver, for this program, that older Db2 behavior. Here's an example: suppose that a DBA named Steve needs to create a non-universal table space in a Db2 system that he administers, and let's say that the activated function level for this system is V12R1M510. It's a fact that, starting with function level V12R1M504, a CREATE TABLESPACE statement can only create a universal table space. Is Steve stuck? No. Steve can create the needed non-universal table space by using a program (we'll say the Db2-provided DSNTEP2) whose package has an APPLCOMPAT value of V12R1M503. What if the DSNTEP2 package at Steve's shop has an APPLCOMPAT value of V12R1M504 or higher? No problem: Steve just needs to make sure that the first SQL statement issued by his DSNTEP2 job is SET CURRENT APPLICATION COMPATIBILITY = 'V12R1M503'; then, a CREATE TABLESPACE statement can be issued to create a non-universal table space (this scenario is described in <a href="https://robertsdb2blog.blogspot.com/2020/04/clearing-air-regarding-db2-12-for-zos.html"><span style="color: #2b00fe;">an entry I posted to this blog</span></a> in 2020). Note that SET CURRENT APPLICATION COMPATIBILITY can be used (with a dynamic SQL-issuing program) to dynamically take a program's application compatibility level to something <u>below</u> - but not above - the APPLCOMPAT level of the program's Db2 package.</span></p><p><span style="font-family: arial;">For many Db2 DDF-using client-server applications (applications known as DRDA requesters in Db2-speak), the Db2 packages used will be those related to the IBM Data Server Driver. These packages reside, by default, in a collection named NULLID. If the NULLID packages have an APPLCOMPAT value of X, and some DRDA requester application requires an APPLCOMPAT value that is higher or lower than X, this need is often satisfied by BIND COPY-ing the packages in NULLID into an alternate collection (with the required APPLCOMPAT specification), and then using the Db2 profile tables to automatically direct the DRDA requester application in question to the alternate IBM Data Server Driver package collection. This technique is described in <a href="https://robertsdb2blog.blogspot.com/2018/07/db2-for-zos-using-profile-tables-to.html?m=0"><span style="color: #2b00fe;">an entry I posted to this blog</span></a> a while back, and while that entry refers to IBM Data Server Driver packages BIND COPY-ed into an alternate collection with an alternate RELEASE specification, the same thing can be done for IBM Data Server Driver packages that have an alternate APPLCOMPAT specification.</span></p><p><span style="font-family: arial;">OK, so remember this bottom-up thing:</span></p><p></p><ol style="text-align: left;"><li><span style="font-family: arial;">First, the feature you want to use needs to be present in your Db2 system's code - that's the <b><span style="color: #16970b;">code level</span></b>.</span></li><li><span style="font-family: arial;">The feature you want to use may have a catalog level requirement - that's the <b><span style="color: #16970b;">catalog level</span></b>. You can't take the catalog level to X (via execution of the Db2 CATMAINT utility) unless the system's Db2 code level is at least X.</span></li><li><span style="font-family: arial;">When the code and catalog levels are right for the Db2 feature you want to use, you need to make sure that the appropriate function level has been activated on the Db2 system - that's the <span style="color: #16970b;"><b>activated function level</b></span>. Function level X cannot be activated unless the Db2 system's code level is at least X and the Db2 catalog level is at least X (or, if function level X has no catalog dependencies, the catalog level has to be at least the level of the last preceding function level that did have catalog dependencies).</span></li><li><span style="font-family: arial;">For your program to use the Db2 feature of interest, the <b><span style="color: #16970b;">application compatibility level</span></b> has to be set as needed - that's done via the APPLCOMPAT value of the program's Db2 package (or by execution of SET CURRENT APPLICATION COMPATIBILITY, if the program issues dynamic SQL statements and if you need to take the application compatibility level lower than the package's APPLCOMPAT value). A package's application compatibility level cannot be set to X unless the activated function level of the Db2 system is at least X.</span></li></ol><br /><p></p><p><span style="font-family: arial;"><b>Checking on all this in your Db2 for z/OS environment</b></span></p><p><span style="font-family: arial;">To see the upside-down hierarchical lay of the land in your Db2 environment, issue the Db2 command -DISPLAY GROUP. This output will look something like this (and don't be misled by the word GROUP in the command - this is applicable for a standalone Db2 subsystem as well as to a Db2 data sharing group):</span></p><span style="font-family: courier;">*** BEGIN DISPLAY OF GROUP(DSNPROD ) CATALOG LEVEL(<b><span style="color: #ff6f00;">V13R1M100</span></b>) <br /> CURRENT FUNCTION LEVEL(<b><span style="color: #800180;">V13R1M100</span></b>) <br /> HIGHEST ACTIVATED FUNCTION LEVEL(V13R1M100) <br /></span><div><span style="font-family: courier;"> HIGHEST POSSIBLE FUNCTION LEVEL(V13R1M500)</span></div><div><span style="font-family: courier;"> PROTOCOL LEVEL(2)</span></div><div><span style="font-family: courier;"> GROUP ATTACH NAME(DSNP) <br /> ----------------------------------------------------------------- <br /> DB2 SUB DB2 SYSTEM IRLM <br /> MEMBER ID SYS CMDPREF STATUS LVL NAME SUBSYS IRLMPROC <br /> ------- -- ---- -------- -------- ------ -------- ---- -------- <br /> DB1P 1 DB1P DB1P ACTIVE <b><span style="color: #16970b;">131503</span></b> SYS1 IR1P DB1PIRLM <br /> DB2P 2 DB2P DB2P ACTIVE <b><span style="color: #16970b;">131503</span></b> SYS2 IR2P DB2PIRLM <br /> ----------------------------------------------------------------- <br /> SCA STRUCTURE SIZE: 36864 KB, STATUS= AC, SCA IN USE: 1 % <br /> LOCK1 STRUCTURE SIZE: 17408 KB <br /> NUMBER LOCK ENTRIES: 2097152 <br /> NUMBER LIST ENTRIES: 21415, LIST ENTRIES IN USE: 3 <br /> SPT01 INLINE LENGTH: 32138 <br /> *** END DISPLAY OF GROUP(DSNPROD ) </span><br /><p><span style="font-family: arial;">For this 2-member Db2 data sharing system, the code level, highlighted in green, is <b><span style="color: #16970b;">131503</span></b> (Db2 for z/OS Version 13, function level 503). The catalog level, highlighted in orange, is <b><span style="color: #ff6f00;">V13R1M100</span></b>. The activated function level, highlighted in purple, is <span style="color: #800180;"><b>V13R1M100</b></span>. As for a package's APPLCOMPAT level, you can see that via a query of the Db2 catalog table <a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=tables-syspackage"><span style="color: #2b00fe;">SYSIBM.SYSPACKAGE</span></a> (check the value in the APPLCOMPAT column for the package's row in the table).</span></p><p><span style="font-family: arial;">I hope that this information will be useful for you. The end of 2023 is around the corner. I'll post more in '24.</span></p></div>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com0tag:blogger.com,1999:blog-4516533711330247058.post-23468936283568671592023-11-29T19:33:00.000-08:002023-11-29T19:33:04.085-08:00Db2 13 for z/OS: Autobind Phase-In<p><span style="font-family: arial;">Db2 13 <a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=levels-function-level-504-apar-ph54919-october-2023"><span style="color: #2b00fe;">function level 504</span></a> became available last month (October 2023), via the fix for APAR PH54919. One of the new capabilities delivered with FL504 is something called autobind phase-in. I like that new feature a lot, and I think you will, too - especially if you're a Db2 for z/OS DBA. In this blog entry I'll explain what autobind phase-in is, why it's a very welcome addition to Db2 functionality, and how you can get ready to leverage the feature, even before you've activated function level V13R1M504.</span></p><p><span style="font-family: arial;">First, a shout-out to my coworker Dengfeng Gao, a member of the IBM Db2 for z/OS development team. Dengfeng had a lot to do with making autobind phase-in a reality, and she provided me with much of the information I'm now passing along to you. Thanks, Dengfeng!</span></p><p><span style="font-family: arial;">OK, to begin the story...</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>The way things were</b></span></p><p><span style="font-family: arial;">Way, way back (early 1990s, as I recall), Db2 for z/OS introduced packages. For a Db2-accessing program that issues static SQL statements, you can think of the associated package as being, in essence, the compiled and executable form of the program's SQL statements (what distinguishes static SQL statements: they are prepared for execution prior to being issued by a program, via a Db2 process known as bind). When a static SQL-issuing program executes (this could be, for example, a CICS transaction, or a batch job, or a stored procedure, or a Db2 REST service), the program's package is allocated to the Db2 thread being used by the application process, and the part of the package corresponding to a particular SQL statement is executed when it's time to run that SQL statement.</span></p><p><span style="font-family: arial;">SQL statements, by and large, reference Db2 tables; thus, packages are dependent on the tables referenced by SQL statements associated with the packages. Packages are also dependent on database objects that are not referenced in SQL statements (indexes are a prime example), when those objects are part of a SQL statement's access plan (i.e., the paths and mechanisms by which data targeted by a SQL statement will be accessed - for example, via a nested loop join that will employ certain indexes on the outer and inner tables). The dependencies of packages on database objects (tables, table spaces, views, indexes, etc.) are recorded in the <a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=tables-syspackdep"><span style="color: #2b00fe;">SYSIBM.SYSPACKDEP</span></a> table in the Db2 catalog.</span></p><p><span style="font-family: arial;">Sometimes, a database object on which a package depends is changed in a way that requires regeneration of the package; or, an object on which the package depends (such as an index) might be dropped; or, a privilege needed by the package's owner might be revoked. In such situations, the package in question is marked as <a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=applications-changes-that-invalidate-packages"><span style="color: #2b00fe;">"invalid"</span></a> by Db2 (such a package will have a value of 'N' in the VALID column of the <a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=tables-syspackage"><span style="color: #2b00fe;">SYSIBM.SYSPACKAGE</span></a> catalog table). When that happens (in a pre-Db2 13 FL504 environment), the package cannot be executed again until it is regenerated by Db2. That regeneration could be accomplished through a REBIND command issued for the package by a DBA (or issued from a batch job coded by a DBA); but, what if there is a request to execute an invalidated package before that package has been regenerated through a REBIND command? In that case, Db2 will automatically regenerate the package, and that process is called autobind (it's sometimes referred to as auto-rebind).</span></p><p><span style="font-family: arial;">When autobind happens for a package (again, we're talking about a pre-Db2 13 FL504 environment), it can be disruptive for the application(s) that drive execution of the package. This disruption can take several forms:</span></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;">The application process whose request for execution of an invalidated package triggered the autobind has to wait until the autobind completes.</span></li><li><span style="font-family: arial;">If another application process also requests execution of the invalidated package before the first autobind completes, that will result in a second attempt to autobind the package. That second attempt will have to wait, because it requires a lock on the package held by the first autobind process; thus, this second requester of the package will also sit and wait (if the first autobind finishes successfully and the second requester has not timed out in the meantime, the second requester will use the package as regenerated by the initial autobind process).</span></li><li><span style="font-family: arial;">If the autobind fails (this could happen as the result of an authorization issue, among other things), the package will be marked as "inoperative" by Db2 (indicated by the value 'N' in the OPERATIVE column of the package's row in SYSIBM.SYSPACKAGE). In that case, any attempt to execute the package will fail until the package is explicitly rebound (usually, by a DBA).</span></li></ul><span style="font-family: arial;"><p><span style="font-family: arial;"><br /></span></p>This not-good situation changes dramatically (for the better) with Db2 13 FL504 autobind phase-in functionality. Before getting to that, I'll cover some prep work in which DBAs will want to engage.</span><p></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Laying the autobind phase-in groundwork: a new catalog table, and a new BIND/REBIND option</b></span></p><p><span style="font-family: arial;">When function level 500 has been activated in a Db2 13 system, the CATMAINT utility can be executed to take the catalog level toV13R1M501. When that happens, some new tables get added to the catalog. One of those new catalog tables is SYSIBM.SYSPACKSTMTDEP. As the name implies, Db2 will use this table to record static SQL dependencies on database objects at the <i>statement level</i>. Does that just happen? Nope - and this is where DBA action comes in.</span></p><p><span style="font-family: arial;">When function level V13R1M502 has been activated, new packages can be bound - and existing packages can be rebound - with the new DEPLEVEL option. If you bind or rebind a package with DEPLEVEL(STATEMENT) then Db2 will record statement-level dependency information in the SYSPACKSTMTDEP catalog table, in addition to recording package-level dependency information in SYSPACKDEP (if you bind or rebind with a specification of DEPLEVEL(PACKAGE), it'll be business as usual - only package-level dependency information will be recorded in the catalog).</span></p><p><span style="font-family: arial;">Would you like to make DEPLEVEL(STATEMENT) the default for package BIND and REBIND actions? If so, set the value of the ZPARM parameter </span><span style="font-family: arial;">PACKAGE_DEPENDENCY_LEVEL to STATEMENT.</span></p><p><span style="font-family: arial;">Is there any downside to binding or rebinding packages with DEPLEVEL(STATEMENT)? A small one (in my opinion): because of the extra work of recording statement-level dependency information in SYSPACKSTMTDEP, binding or rebinding with DEPLEVEL(STATEMENT) will somewhat increase elapsed and CPU time for the bind/rebind operation. My expectation is that in a typical production Db2 for z/OS system, CPU consumption related to BIND and REBIND activity is a very small fraction of total CPU consumption.</span></p><p><span style="font-family: arial;">Is there an upside to binding and rebinding packages with DEPLEVEL(STATEMENT)? Oh, yeah...</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Enter autobind phase-in</b></span></p><p><span style="font-family: arial;">Once a Db2 13 system's activated function level. is V13R1M504 or higher, this is what happens when a package <u>that has been bound or rebound with DEPLEVEL(STATEMENT)</u> is invalidated: the first request to execute the package following invalidation (assuming the invalidated package wasn't explicitly rebound before that execution request) will trigger an autobind of the package.</span></p><p><span style="font-family: arial;">"Wait," you might think. "Isn't that the same thing that happened before the advent of autobind phase-in?" Yes, but the autobind itself takes a very different path, and has a very different workload impact, versus the prior process. To wit:</span></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;">The package will be regenerated <i>in the background</i>. The process that requested execution of the invalidated package <u>will be allowed to execute the package - it will not have to wait for the autobind to complete</u>.</span></li><li><span style="font-family: arial;">When the invalidated package is executed, <i>statements that were not invalidated by the action that invalidated the package (e.g., an ALTER of a table that is referenced by some - but not all - of the package's statements) will continue to execute as they did before the invalidation of the package.</i></span></li><li><span style="font-family: arial;">Also when the invalidated package is executed, <i>statements that were invalidated by the (for example) ALTER action will be incrementally bound when issued by the associated program</i>. This means that they will be dynamically prepared for execution, and that will mean a temporary additional CPU cost (temporary until the in-the-background autobind completes the regeneration of the package that had been invalidated), <i>but the statements will be executed</i>.</span></li><li><span style="font-family: arial;">And if, before the in-the-background autobind completes, there is a second request to execute the invalidated package, will that trigger a second autobind action? Nope - the one autobind is for any and all package requesters. That second requester will be allowed to execute the invalidated package, just as was the case for the requester that triggered the in-the-background autobind - still-valid statements will execute as usual, and invalidated statements will be incrementally bound <i>and then executed</i>.</span></li><li><span style="font-family: arial;">When the in-the-background autobind has finished its work, the newly regenerated package will be <i>phased into use</i>, in much the same way that the <a href="https://robertsdb2blog.blogspot.com/2020/12/db2-for-zos-what-do-you-know-about.html"><span style="color: #2b00fe;">rebind phase-in</span></a> functionality introduced with Db2 12 FL505 phases a newly rebound package into use: the first request for execution of the package following completion of the in-the-background autobind will get the regenerated (and now valid) package. Eventually, processes that had been executing the previous instance of the package (the instance that had been invalidated) will be done with that, and all processes will be using the regenerated package when they request its execution.</span></li><li><span style="font-family: arial;">If the in-the-background autobind fails, will the invalidated package be marked inoperative (with attendant error situations for processes that request execution of the package)? Nope. In that case, the package will be marked with rebind-advisory status ('R' in the OPERATIVE column for the package's row in SYSPACKAGE). The package can still be executed (as described above: as-usual for not-invalidated statements, incremental bind for invalidated statements), but an explicit REBIND is recommended to get the package regenerated and back into a valid status.</span></li></ul><span style="font-family: arial;"><p><span style="font-family: arial;"><br /></span></p>Bottom line: with autobind phase-in, autobind activity will have hugely less impact on throughput and service levels for applications that execute packages that have been invalidated.</span><p></p><p><span style="font-family: arial;">Note that the above-described much-better autobind process applies <u>only to packages that have been bound or rebound with DEPLEVEL(STATEMENT)</u> - and you can start doing that (as previously mentioned) once you've activated function level 502 in a Db2 13 system.</span></p><p><span style="font-family: arial;">One other item of information: the in-the-background autobind done for autobind phase-in will execute in access-path-reuse mode - in other words, Db2 will reuse the access paths previously utilized for the package's SQL statements, if that can be done (it of course could not be done for all statements if, for example, package invalidation resulted from the dropping of an index on which some of the package's SQL statements depended). The same goes for the incremental bind of invalidated SQL statements when the invalidated package is requested for execution before the in-the-background autobind has completed - access paths will be reused if possible.</span></p><p><span style="font-family: arial;">OK, so if you've gotten to Db2 13 at your site, give major consideration to rebinding packages (and binding new packages) with DEPLEVEL(STATEMENT), once function level V13R1M502 or higher has been activated; and, look forward to a much more application workload-friendly autobind process when you get function level V13R1M504 activated.</span></p>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com0tag:blogger.com,1999:blog-4516533711330247058.post-48903430828694224022023-10-30T18:05:00.000-07:002023-10-30T18:05:56.194-07:00Db2 13 for z/OS: A New Means for Managing RELEASE(DEALLOCATE) Packages<p><span style="font-family: arial;">It has long been understood by many Db2 for z/OS DBAs that a combination of the RELEASE(DEALLOCATE) package bind specification (especially for frequently executed Db2 packages that consume little CPU time per execution) and persistent threads can significantly reduce in-Db2 CPU time for Db2-accessing applications (a "persistent thread" is one that persists through commits - examples include CICS-Db2 protected threads, the threads between IMS wait-for-input regions and Db2, the Db2 threads associated with batch jobs, and high-performance database access threads, aka high-performance DBATs). The CPU efficiency benefit of RELEASE(DEALLOCATE) + persistent threads comes mainly from avoiding the cost of constantly releasing the package in question (i.e., separating it from the thread) at a commit point, only to reallocate it to the thread when it is subsequently (often, very soon - maybe a fraction of a second later) again requested for execution (some additional CPU savings are achieved via retention across commits of "parent" locks acquired in the execution of the package - these are table space- or partition-level locks, and they are almost always non-exclusive in nature).</span></p><p><span style="font-family: arial;">Always nice to get a Db2 workload CPU efficiency boost, but those CPU savings came at one time with several "flip side" concerns. One of those concerns - conflict with Db2 utilities caused by retained parent locks - was addressed a <u>long</u> time ago (back in the mid-1990s) with the advent of the drain locking mechanism that utilities can use to gain exclusive access to a database object. Another concern from days past had to do with virtual storage constraint - the combination of RELEASE(DEALLOCATE) and persistent threads causes said threads to consume more virtual storage, and when that virtual storage was within the quite-limited confines of the EDM pool, there was a real risk of that getting filled up and causing application failures if one were not careful. Thankfully, that virtual storage-related risk was eliminated with Db2 10, when the virtual storage space used for allocation of packages to threads for execution moved from the EDM pool to above-the-bar agent local pool space, of which there is a very large quantity.</span></p><p><span style="font-family: arial;">That left us with one more operational challenge associated with the RELEASE(DEALLOCATE) + persistent threads combo: conflict between in-use packages and processes that need to either rebind or invalidate a package. See, a package can't be rebound or invalidated when it is in-use, and a RELEASE(DEALLOCATE) package allocated for execution to a persistent thread is considered by Db2 to be <u>continuously</u> in-use until the thread is terminated, and that could be a while. This last operational challenge was partly addressed with the <a href="https://robertsdb2blog.blogspot.com/2020/12/db2-for-zos-what-do-you-know-about.html"><span style="color: #2b00fe;">rebind phase-in functionality</span></a> that was introduced with function level 505 of Db2 12 for z/OS. OK, great - you can successfully and non-disruptively rebind a package even when the package is in-use at the time of the issuance of the REBIND PACKAGE command; but, what about the situation in which a package needs to be invalidated, perhaps as a result of execution of an ALTER statement targeting an object on which the package is dependent (or as a result of an online REORG that materializes a pending DDL change)? Db2 11, in new-fucntion mode, provided an assist in this case: Db2 gained the ability to detect when a package-invalidating action was being blocked by a dependent package bound with RELEASE(DEALLOCATE) and allocated to a persistent thread - detecting that situation, Db2 could automatically, dynamically and temporarily change the package's behavior to RELEASE(COMMIT) at its next commit point. This enhancement, though welcome, did not fully eliminate the problem.</span></p><p><span style="font-family: arial;">Why did Db2 11's automatic detection and reaction to a RELEASE(DEALLOCATE) package being allocated to a persistent thread not totally resolve the blocked package invalidation problem? Two reasons:</span></p><p></p><ol style="text-align: left;"><li><span style="font-family: arial;">The persistent thread to which the RELEASE(DEALLOCATE) package is allocated may be outside of Db2 - being, perhaps, in a between-the-last-and-the-next-transaction situation (this can particularly be an issue for high-performance DBATs).</span></li><li><span style="font-family: arial;">There might be several different dependent packages bound with RELEASE(DEALLOCATE), and copies of these various packages could be allocated to a large number of threads, such that Db2 is not able to separate all the packages from all those threads in time to keep the package-invalidating action from timing out.</span></li></ol><span style="font-family: arial;">For these situations, Db2 13 provides the solution in the form of a new option that can be exercised via the profile tables (SYSIBM.DSN_PROFILE_TABLE and SYSIBM.DSN_PROFILE_ATTRIBUTES). The new option, available when Db2 13 function level 500 has been activated, comes in the form of a new keyword for the DSN_PROFILE_ATTRIBUTES table: RELEASE_PACKAGE. This keyword has one available associated ATTRIBUTE1 value: COMMIT, and specification of that value will cause Db2 to override RELEASE(DEALLOCATE) behavior for the package (or packages) associated with a profile to RELEASE(COMMIT) behavior, until further notice. And, this new profile table option can be used for local-to-Db2 applications (e.g., CICS-Db2 or IMS TM-Db2 or batch Db2) as well as for DDF-using applications (the profile tables had previously, essentially, been relevant only to DDF-using applications).</span><p></p><p><span style="font-family: arial;">Let's consider a use-case scenario to illustrate exploitation of this Db2 13-delivered capability. Suppose you're a Db2 DBA, and you need to issue an ALTER TABLE statement that will invalidate several packages, in collection COLL_A, that are bound with RELEASE(DEALLOCATE) and are executed by way of persistent threads. Not wanting this ALTER TABLE statement to time out due to conflict with the dependent RELEASE(DEALLOCATE) packages, you take this approach (and we'll assume that this is a Db2 13 system with function level 500 or higher activated):</span></p><p></p><ol style="text-align: left;"><li><span style="font-family: arial;">Sometime prior to the time you want to issue the ALTER TABLE statement (maybe 15 minutes ahead of that time, as an example), you insert a row in <a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=tables-dsn-profile-table"><span style="color: #2b00fe;">DSN_PROFILE_TABLE</span></a> to identify a profile that is associated with the collection COLL_A (you could also do this for a specific package in COLL_A, but we'll do this for the whole collection in this case). In doing this, you put the value 'Y' in the PROFILE_ENABLED column, to let Db2 know that this profile (and its associated attributes) is "live" (i.e., in effect).</span></li><li><span style="font-family: arial;">The just-created profile, like all profiles, has an ID associated with it (an integer value). We'll say that the ID for this COLL_A-related profile is 5. In the <a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=tables-dsn-profile-attributes"><span style="color: #2b00fe;">DSN_PROFILE_ATTRIBUTES</span></a> table, you insert a row for profile 5. In that row, you specify 'RELEASE_PACKAGE' for the KEYWORDS column value, and 'COMMIT' for the ATTRIBUTE1 value. For the ATTRIBUTE2 column you specify a value of 1, because (in this example) you want the RELEASE(DEALLOCATE) override action to apply only to local-to-Db2 processes (a value of NULL in the ATTRIBUTE2 column would indicate that the RELEASE(DEALLOCATE) override action is to be taken only for DDF-using processes, and a value of 2 would mean, "Take this action for all related processes, whether local-to-Db2 or involving DDF").</span></li><li><span style="font-family: arial;">You issue the Db2 command -START PROFILE, so that Db2 will load the information in the profile tables into memory. Db2 sees that profile 5 (among, possibly, others) is enabled, and takes action: every time a package in COLL_A is loaded for execution, it will be treated as though bound with RELEASE(COMMIT), even if RELEASE(DEALLOCATE) had been specified for the most recent bind or rebind of the package.</span></li><li><span style="font-family: arial;">Because you took these steps 15 minutes prior to the time for issuing the ALTER TABLE statement, Db2 had plenty of time to switch to RELEASE(COMMIT) behavior for every instance of a RELEASE(DEALLOCATE) package in COLL_A that is allocated for execution to a persistent thread. You issue the ALTER TABLE statement, and it succeeds because there are no dependent packages bound with RELEASE(DEALLOCATE) and allocated to persistent threads to block execution of the statement. Note that the application workload associated with the RELEASE(DEALLOCATE) packages continues to execute - just with RELEASE(COMMIT) behavior in effect for those packages. That means you temporarily do without the CPU efficiency benefit of RELEASE(DEALLOCATE) for the associated application(s).</span></li><li><span style="font-family: arial;">With the ALTER TABLE statement having been successfully executed, you update the row for profile 5 in DSN_PROFILE_TABLE to have 'N' in the PROFILE_ENABLED column, to show Db2 that this profile (and its attributes) is no longer in effect. A subsequent issuance of the -START PROFILE command lets Db2 know of this profile status change by re-loading the profile table information into memory.</span></li></ol><span style="font-family: arial;">And that's it. A problem that might have been standing in your way is taken care of, easily and non-disruptively.</span><p></p><p><span style="font-family: arial;">Of course, this story is not yet over. With the ALTER TABLE statement having been successfully executed, packages dependent on the table are invalidated. What happens after that? The invalidated packages will be auto-rebound by Db2 when next requested for execution, if you don't explicitly rebind them before that happens. Db2 13 function level 504, which came out just a few days ago, delivers big news on the auto-rebind front. I'll post a blog entry on that enhancement within the next few weeks.</span></p>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com0tag:blogger.com,1999:blog-4516533711330247058.post-9957220169320625142023-09-27T12:54:00.000-07:002023-09-27T12:54:36.013-07:00Db2 for z/OS: Two Stories of Temporary Tables<p><span style="font-family: arial;">Db2 for z/OS provides, for your use, two different temporary table types: declared and created. I recently worked with Db2 for z/OS people at a couple of different sites, providing assistance in understanding and effectively utilizing Db2 temporary tables, and I think information related to these experiences might be useful to folks in the larger user community - thus this blog entry. The first story points up an advantage of declared global temporary tables, while the second illustrates advantageous use of a created global temporary table. I hope that what follows will be of interest to you.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Db2 declared global temporary tables - the index advantage</b></span></p><p><span style="font-family: arial;">A Db2 <a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=statements-declare-global-temporary-table"><span style="color: #2b00fe;">declared global temporary table</span></a> (DGTT) is so-called because it comes into existence when it is declared - usually in an application program. There can be plenty of reasons for a program to declare a temporary Db2 table. For example, an application might need to get a preliminary set of result set rows from Db2 and then operate further on that data - easily done by inserting the result set rows into a declared temporary table and subsequently issuing various SQL statements that target the DGTT.</span></p><p><span style="font-family: arial;">A Db2 for z/OS DBA sent me the other day some information about a use case at his site that made a declared global temporary table the right choice. B</span><span style="font-family: arial;">ecause the temporary table in this case was going to be fairly large, and because DELETE statements targeting the table would be coded with predicates, there would be a performance advantage to the use of a declared versus a created global temporary table (CGTT): an index can be defined on a DGTT, but not on a CGTT </span><span style="font-family: arial;">(this and other </span><a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=tables-distinctions-between-db2-base-temporary" style="font-family: arial;"><span style="color: #2b00fe;">differences between declared and created global temporary tables are listed in the Db2 for z/OS online documentation</span></a><span style="font-family: arial;">).</span></p><p><span style="font-family: arial;">So, with the available option of indexing, DGTTs will generally be preferred over CGTTs, right? Not necessarily...</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>When a CGTT can majorly reduce elapsed and CPU time for a process</b></span></p><p><span style="font-family: arial;">The second Db2 temporary table story I'll relate came to me by way of a Db2 for z/OS systems programmer who works for a financial services organization. In this case, a z/OS-based COBOL batch program was retrieving data from a VSAM file and inserting related information into a Db2 temporary table. Rows were then SELECTed from the temporary table for further processing, after which the rows in the temporary table were deleted (via a predicate-less DELETE). These insert/retrieve/delete actions involving the temporary table were repeated about 300,000 times in an execution of the batch job. At first, the COBOL program declared and utilized a declared global temporary table. The problem? That approach did not perform as needed: the job consumed around 20 minutes of CPU time, and the SQL activity drove approximately 23 million GETPAGE requests (a GETPAGE happens when Db2 needs to examine the contents of a page of a database object, and GETPAGE activity is a major factor in the CPU cost of SQL statement execution). The Db2 sysprog also noticed that the batch process generated a lot of PREPARE activity (referring to the dynamic preparation of SQL statements for execution by Db2 - something that can significantly add to an application program's CPU consumption).<br /><br />To try to reduce the CPU cost of the batch job, the Db2 team at this financial services organization switched from using a DGTT to a "permanent" (i.e., a "regular") Db2 table. Performance indeed got way better: GETPAGE requests dropped by over 70%, and CPU time for the job went from about 20 minutes to about 4 minutes. Why the big drop in GETPAGEs and CPU time? Probably this had to do with elimination of expensive SQL statement preparation activity. See, you might think that the SQL statements hard-coded in your COBOL (for example) program are all static ("static" SQL statements are prepared by Db2 for execution prior to program execution, via a process known as BIND), but when those statements refer to a DGTT they have to be dynamically prepared for execution when issued by the program because there is no definition of a DGTT in the Db2 catalog.</span></p><p><span style="font-family: arial;">This big improvement in CPU efficiency notwithstanding, there was a side-effect of the switch from a DGTT to a permanent table that did not sit well with the Db2 team at the financial services company: as previously noted, the set of SQL statements targeting (initially) the DGTT and then the permanent Db2 table involved a delete of all rows in the table, and that happened 300,000 times in the execution of the batch job. When a permanent table was used in place of the DGTT, these 300,000 mass DELETEs (a mass DELETE is a DELETE without predicates) caused 300,000 rows to be inserted for the permanent table in the SYSCOPY table in the Db2 system's catalog. Running a MODIFY RECOVERY utility job to clear those rows out of SYSCOPY, and having to take an image copy of the permanent table to keep it from going into COPY-pending status, were viewed as significant hassles by the Db2 team. Was a still-better way forward available to them?</span></p><p><span style="font-family: arial;">Indeed so. I suggested going with a created global temporary table. [Like a permanent table, a CGTT is defined by way of a <a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=statements-create-global-temporary-table"><span style="color: #2b00fe;">CREATE statement</span></a>, and there is information about a CGTT in the Db2 catalog. When a program references the CGTT it gets its own instance of the CGTT which (like a DGTT) is physically provisioned in the Db2 work file database.] The Db2 team did that, and the results were very positive. CPU time for the job - originally about 20 minutes with the DGTT and then about 4 minutes with the permanent table, went down to just over 2 minutes with the CGTT (as with the permanent table, no SQL statement dynamic preparation was needed, thanks to the CGTT being defined in the Db2 catalog); <u>and</u>, there were no inserts into SYSCOPY in association with the repeated mass DELETEs (same as with the DGTT); <u>and</u>, there was no need for an image copy of the CGTT because the instance of the table goes away automatically when the process using the table completes (same as with the DGTT). So, the CGTT in this case provided the advantages of a DGTT and of a permanent Db2 table, minus the relative disadvantages of those options (the dynamic statement preparation costs of the DGTT, and the mass-DELETE-related SYSCOPY inserts and the image copy requirements of the permanent table).</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>The bottom-line message</b></span></p><p><span style="font-family: arial;">Declared and created global temporary tables both have their places in a Db2 for z/OS application environment. When considering the use of a Db2 temporary table for an application, be careful not to jump too quickly to a DGTT versus a CGTT decision (though sometimes there will be only one choice - as when, for example, UPDATE access to the temporary table is needed - something that can only be done with a DGTT). Consider the DGTT versus CGTT choice in light of the particulars of the use case, and choose accordingly. A thoughtful choice can yield a substantial performance advantage - so use your head.</span></p>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com10tag:blogger.com,1999:blog-4516533711330247058.post-10119370983563641272023-08-31T15:03:00.000-07:002023-08-31T15:03:07.396-07:00Db2 for z/OS: An Important Difference Between Data in Memory and Data on Disk<p><span style="font-family: arial;">For the past several years, I've tried to post an entry per month to this blog. Sometimes, it will be very near the end of a month, and I haven't posted anything since the previous month, and I don't have any good ideas for a new entry. Then, I'll have an exchange with someone - could be via email, a phone call, a face-to-face discussion, whatever - and BOOM! Right there I'll find the inspiration for a blog entry. That happened again yesterday - the day before the last day of the month. Whew!</span></p><p><span style="font-family: arial;">Here's what happened: in an email message, an IT professional recounted to me a situation that had her perplexed. The scenario: 11,000 rows of data were loaded into a table using the IBM Db2 for z/OS LOAD utility. Immediately after the completion of that load job, a program that updated 8 of those just-loaded rows executed and ran successfully to completion. Right after that, an unload job for the table in question was executed. This unload was performed using an IBM software product called Db2 High Performance Unload for z/OS, or HPU, for short (see <a href="https://www.ibm.com/docs/en/dhpufz/5.1.0?topic=documentation-db2-high-performance-unload-overview"><span style="color: #2b00fe;">https://www.ibm.com/docs/en/dhpufz/5.1.0?topic=documentation-db2-high-performance-unload-overview</span></a>). HPU has two modes of operation: it can perform an unload by operating directly on the VSAM data set(s) associated with the target table, or it can do the unload through Db2, in which case the data is accessed in memory (i.e., in the buffer pool to which the table's table space is assigned). This unload was done in the former of these modes - operating directly on the VSAM data set(s) associated with the table's table space. The result of the unload surprised the person who emailed me. How so? Well, the unload was done using a predicate (the WHERE clause that you might see in a query), and the update program that ran between the load (of the 11,000 rows) and the unload changed values in a way that should have caused 8 of the 11,000 loaded rows to be filtered out by the unload process's predicate (the other 10,992 rows would be qualified by the predicate). The person who emailed me expected 10,992 records in the unload data set, but there were in fact 11,000 rows in that data set. The updates that should have caused 8 rows to be not-qualified by the unload process's predicate were committed before the unload job ran, so why was this update action not reflected in the contents of the unload data set? Consternation increased when another unload of the table, executed a few hours</span><span style="font-family: arial;"> later</span><span style="font-family: arial;"> (again, with the unload process using a predicate and operating directly on the table's associated VSAM data set(s)), generated an unload data set that </span><u style="font-family: arial;">did</u><span style="font-family: arial;"> contain the expected 10,992 rows.</span></p><p><span style="font-family: arial;">What in the world was going on here?</span></p><p><span style="font-family: arial;">Here's what was going on: this all has to do with a big difference between a <i>committed</i> data change (which has relevance for Db2 data-in-memory) and an <i>externalized</i> data change (which relates to Db2 data-on-disk). What's important to know is that Db2 for z/OS does <u>not</u> externalize data changes (i.e., does not write changed data to the associated VSAM data set) as part of commit processing. Instead, </span><span style="font-family: arial;">database write I/O operations (to externalize data changes to VSAM data sets on disk) are done in a deferred way (and usually asynchronously, at that). This aspect of Db2's operation is critically important to scalability when it comes to data-change operations (e.g., INSERT, UPDATE and DELETE). If Db2 had to write changed pages to disk at commit time, data-change throughput would be majorly impacted in a negative way. In the scenario described above, the first unload generated by HPU (done right after the programmatic update of 8 of the 11,000 rows previously LOAD-ed into the table), operating directly on the table space's underlying VSAM data set(s), did not reflect the post-LOAD update of the 8 rows because the page(s) changed by the updating program were not written to the underlying VSAM data set(s) at commit time. The changed page(s) were externalized later by Db2 via deferred write processing, and that is why the second unload process, also operating directly on the table space's VSAM data set(s), reflected the aforementioned updates of 8 of the 11,000 table rows.</span></p><p><span style="font-family: arial;">If Db2 deferred write action did eventually get the changed pages (associated with the updating of 8 of the table's rows) written to the associated VSAM data sets on disk - and it did - then what caused that deferred write action to happen? Usually Db2 deferred write operations for a given buffer pool are driven by one of two deferred write thresholds being reached for the pool. The deferred write queue threshold (abbreviated as DWQT) is expressed as a percentage of the total number of buffers allocated for a pool that are occupied by changed-but-not-externalized pages (the default value is 30), and the vertical deferred write queue threshold (VDWQT) is expressed as a percentage of the pool's buffers that are occupied by changed-but-not-externalized pages that belong to a particular data set (the default value is 5). Whenever either of those limits is reached (and it's usually the VDWQT limit), deferred write activity is triggered. The deferred write I/Os, by the way, are generally multi-page in nature (multiple pages written to disk in one I/O operation), and that is good for CPU-efficiency on a per-page basis. The CPU time associated with database writes (usually not much - I/Os don't require many CPU cycles) is charged to the Db2 database services address space (aka the DBM1 address space).</span></p><p><span style="font-family: arial;">What about synchronous database write actions? Are they also deferred? Yes, they are - they're just triggered by something besides deferred write queue thresholds being reached. In the case of synchronous writes, the trigger is Db2 checkpoint processing. How that works: when Db2 executes a system checkpoint (which it does, by default, every 3 minutes), it notes all pages in each buffer pool that are in changed-but-not-yet-externalized status. When the next system checkpoint rolls around, Db2 checks to see if any of the changed-but-not-yet-externalized pages noted at the last checkpoint have still not been written to disk. If there are any such pages then they will be synchronously written to disk as part of checkpoint processing. Here, "synchronous" means that Db2 will immediately start writing those pages to disk, and it will continue to do that until they are all externalized.</span></p><p><span style="font-family: arial;">OK, back to the story that prompted this blog entry. Is there a way that the initial HPU unload (the one executed very shortly after the programmatic update of 8 of the 11,000 rows LOAD-ed into the table) could have generated an unload data set with the desired 10,992 rows? Yes. In fact, there were at least two options for getting that done. One option would be to execute the Db2 QUIESCE utility for the table's table space prior to running the HPU unload. This would cause Db2 to write all changed-but-not-yet-externalized pages of the table's table space to disk, and then an HPU unload operating directly on the table space's VSAM data sets would have reflected the update of the 8 rows.</span></p><p><span style="font-family: arial;">The second option would be to have HPU do the unload through Db2, as opposed to operating directly on the table space's underlying VSAM data sets - this is something that can be done through an HPU keyword. That, in turn, would have caused the HPU unload to be accomplished using data in memory (i.e., in the table space's assigned buffer pool) - any of the to-be-unloaded pages that were not already in memory would have been read into memory to as part of the unload process. This approach would have reflected the programmatic update of the 8 rows because those updates had been committed, and Db2 data-in-memory is always in a transactionally consistent state (any in-memory data that is not transactionally consistent because of an in-flight - that is, not-yet-completed - data change operation is blocked from access by X-type locks, taken at a page or a row level, that are not released until the data changes in question are committed).</span></p><p><span style="font-family: arial;">Which of these options would you choose? It would depend on what is most important for you. The QUIESCE option would allow the HPU unload to operate directly on the VSAM data set(s) associated with the table space, and that would yield a CPU efficiency benefit, but the QUIESCE itself could be at least somewhat disruptive for applications accessing the target table. The "through Db2" option would not disrupt any application processes that were accessing the table at the time, but it would cause the HPU unload operation to consume some additional CPU time.</span></p><p><span style="font-family: arial;">By the way, if you're wondering, "If committed data changes are written to disk in a deferred way, how is loss of committed data changes prevented in the event of an abnormal termination (i.e., a "crash") of the Db2 subsystem that happens when there are changed-but-not-yet-externalized pages in memory?" Worry not - data recorded in the Db2 transaction log is used to process those "pending writes" as part of the "roll-forward" phase of Db2 restart processing following a subsystem failure.</span></p><p><span style="font-family: arial;">One more thing: the mechanics of all this are different in a Db2 data sharing environment (involving group buffer pool writes and associated castout operations to eventually get changed pages written to VSAM data sets on disk), but the net effect is the same.</span></p><p><span style="font-family: arial;">And there you have it. I'm thankful for your visiting this blog, and I'm thankful for interesting questions that come in when I'm trying to figure out what I'm going to blog about.</span></p>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com2tag:blogger.com,1999:blog-4516533711330247058.post-10684690284719604672023-07-28T16:11:00.000-07:002023-07-28T16:11:55.056-07:00Db2 for z/OS: What I Would Say to Application Developers (Part 2)<p><span style="font-family: arial;">In the <a href="http://robertsdb2blog.blogspot.com/2023/06/db2-for-zos-what-i-would-say-to.html"><span style="color: #2b00fe;">part 1</span></a> of this 2-part entry (posted last month), I emphasized what I consider to be job one for a developer coding a Db2 for z/OS-targeted query (write a SQL statement that will retrieve the data your program requires, and don't worry too much about the statement's performance - that's mostly taken care of by Db2), while also noting ways in which a developer can effectively work in partnership with a DBA to enhance the performance of a Db2-based application. In this part 2 entry I will focus on leveraging application-enabling features of Db2 for z/OS.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>First, what interface is right for a Db2-accessing application?</b></span></p><p><span style="font-family: arial;">For a long time, this was not a very meaningful question, as there was essentially one application interface to Db2 for z/OS: the SQL interface. Sure, there could be discussions around dynamic versus static SQL (i.e., SQL statements prepared by Db2 for execution when initially issued by a program, versus statements that are pre-prepared for execution via a Db2 process known as "bind"), or about the use of "generic" (i.e., non-DBMS-specific) SQL forms such as JDBC and ODBC, but in any case you were talking about SQL, period. That changed with Db2 12 for z/OS, which introduced <a href="http://robertsdb2blog.blogspot.com/2021/02/are-you-using-rest-interface-to-db2-for.html"><span style="color: #2b00fe;">Db2's REST interface</span></a>. Using Db2's REST interface does not involve compromising performance, security or <a href="http://robertsdb2blog.blogspot.com/2021/06/db2-for-zos-rest-services-scalability.html"><span style="color: #2b00fe;">scalability</span></a>, so I'd say use it when it makes sense. When might it make sense? Here are some considerations:</span></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;"><i>Client-side programming language flexibility</i> - The IBM Data Server Driver, which supports the use of SQL forms such as JDBC and ODBC with Db2 for z/OS, can be used with applications written in a number of programming languages (generally speaking, programs coded with embedded static SQL statements don't require a driver), but suppose your team wants to use a language for which the IBM Data Server Driver is not applicable? Well, does the language allow a program to issue a REST request? If the answer to that question is, "Yes" (often the case), programs written in that language can access Db2 via its REST interface.</span></li><li><span style="font-family: arial;"><i>Total abstraction of the particulars of the service-providing system</i> - If you're using a generic SQL form such as JDBC or ODBC, you don't have to know the specifics of the relational database management system being accessed, but you still know that your program is accessing a relational DBMS (or something that through virtualization software appears to be a relational DBMS). Maybe you don't want to have to know that (even if you have strong SQL skills) - you just want to request some service and have it performed as expected by some system, and you don't care a whit about what that system is and how it does what it does. In that case, the REST interface to Db2 looks really good.</span></li><li><span style="font-family: arial;"><i>Separation of programming duties</i> - When your client-side program accesses Db2 using REST requests, your program isn't issuing SQL statements - you're instead coding REST requests that invoke server-side SQL statements that were <a href="https://robertsdb2blog.blogspot.com/2021/05/the-rest-interface-db2-for-zos-as.html"><span style="color: #2b00fe;">likely written by someone else</span></a>. That separation of programming duties - client-side developers code programs that issue REST requests and process any returned results, and server-side developers write the Db2 SQL statements and/or stored procedures that will be REST-invoked - might suit you (and your organization's IT leadership) just fine.</span></li></ul><span style="font-family: arial;">So, think this over for a new application that will access Db2 for z/OS, and make the appropriate choice.</span><p></p><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><b>Let Db2 for z/OS do work so you don't have to</b></span></div><div><span style="font-family: arial;"><b><br /></b></span></div><div><span style="font-family: arial;">There are a number of Db2 features that can provide useful functionality for an application, and when you leverage one of these features that's functionality that you don't have to provide via program code. Here are some of the features in this category, delivered through recent versions of Db2 for z/OS:</span></div><div><ul style="text-align: left;"><li><span style="font-family: arial;"><i>Temporal data support (introduced with Db2 10 for z/OS)</i> - This capability, through which a time dimension can be added to data in a table, comes in two forms (both can be implemented for a given table, if desired, or one or the other can be used):</span></li><ul><li><span style="font-family: arial;"><i>System-time temporal (also known as row versioning)</i> - The way this works: when a row in table T1 is deleted or updated, the "before" image of that row (the row as it existed prior to the update or delete operation) is automatically inserted by Db2 into the "history" table associated with T1, and Db2 also updates timestamp values in the history table row indicating when that "version" of the row first became the current version (i.e., when the row was first inserted into T1, or when it was updated to that version) and when the row stopped being the current version (i.e., when it was deleted from T1, or when it was updated to a new version). Here's what this means: using pretty simple Db2 temporal query syntax, your application can easily find out from Db2 what the row for a given entity (a bank account, an insurance policy, a customer record, whatever) looked like <u>at a previous point in time</u>. For example, when it comes to insurance claim adjudication, what's important is not what the policy holder's coverage is <u>now</u> - what's important is the coverage that was in effect <u>when the event prompting the claim occurred</u>. That's easy to determine with Db2 row versioning functionality. Also easy is seeing how a given entity's row in a table changed over a period of time, and <a href="http://robertsdb2blog.blogspot.com/2015/04/a-db2-11-for-zos-temporal-data.html"><span style="color: #2b00fe;">who made changes to the row</span></a>.</span></li><li><span style="font-family: arial;"><i>Business-time temporal</i> - This form of Db2 temporal functionality lets you add <u>future</u> data changes to a table with an indication of when that change will go into effect and how long it will be in effect (if not indefinitely). For example, you could through a business-time temporal UPDATE statement indicate (and this will be reflected in the target table) that the price of product XYZ is going to change from $10 to $12 on May 1 of next year. Updates of this nature will not impact programs that, by default, are accessing rows that, from a business perspective, are currently in effect. Having future prices (for example) in a product table provides at least a couple of benefits: 1) it ensures that price changes will actually go into effect when scheduled, and 2) it allows financial analysts to issue queries that will show what revenue and profits will be with prices that <u>will be in effect</u> at a future date.</span></li></ul><li><span style="font-family: arial;"><i><a href="http://robertsdb2blog.blogspot.com/2015/03/the-db2-managed-data-archiving-feature.html"><span style="color: #2b00fe;">Db2 transparent archiving</span></a> (introduced with Db2 11 for z/OS)</i> - This feature can be helpful, especially for performance, in this scenario: table T1 has 20 years (for example) of historical transaction data (maybe related to account activity), but the vast majority of accesses to the table target rows that have been added in the past 90 days; further, because the table's row-clustering key is not continuously-ascending and because row inserts far outnumber row deletes, the "popular" rows in the table (the ones not more than 90 days old) are physically separated from each other by ever-larger numbers of "old and cold" rows (the ones rarely accessed by programs). In that case the performance for access to popular rows will get progressively worse, and the cost of administering the table (e.g., backing it up, periodically reorganizing it, etc.) will steadily increase. When Db2 transparent archiving is activated for the table T1 (easily done by a DBA), T1 will end up holding only the most recent 90 days of data (the popular rows), while all of the "old and cold" rows are physically stored in the "archive" table that - in an "under the covers" way - is associated with T1 (result: substantially better performance for access to the popular rows, because they are concentrated in a smaller table); <u>and</u>, for query purposes Db2 makes the base table and its associated archive table appear logically as a single table, so a query referencing only the base table can retrieve archived rows as needed; <u>and</u>, when a row is deleted from the base table (after it's been there for - in this example - 90 days), that row is automatically inserted by Db2 in the associated archive table.</span></li><li><span style="font-family: arial;"><i><a href="http://robertsdb2blog.blogspot.com/2017/07/db2-12-for-zos-sql-enhancements-result.html"><span style="color: #2b00fe;">Result set pagination</span></a> (introduced with Db2 12 for z/OS)</i> - Db2 12 made a new clause, OFFSET, available for a SELECT statement. OFFSET, used in combination with the FETCH FIRST n ROWS clause, makes it programmatically easier to return a multi-row result set in "pages" that a user can scroll through.</span></li><li><span style="font-family: arial;"><i><a href="http://robertsdb2blog.blogspot.com/2017/06/db2-12-for-zos-sql-enhancements-piece.html"><span style="color: #2b00fe;">"Piece-wise" DELETE</span></a> (introduced with Db2 12 for z/OS)</i> - Starting with Db2 12, you can use the FETCH FIRST n ROWS clause with a DELETE statement, and that makes it really easy to write a program that will remove a large number of rows in a table in a series of small units of work (so that your data-purge program will not acquire too many locks at one time - nice for concurrency of access with other programs targeting the table).</span></li><li><span style="font-family: arial;"><i>Newer built-in functions</i> - Db2 12 for z/OS added some nice built-in functions, including:</span></li><ul><li><span style="font-family: arial;"><i><a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=functions-percentile-cont"><span style="color: #2b00fe;">PERCENTILE_CONT</span></a> and <a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=functions-percentile-disc"><span style="color: #2b00fe;">PERCENTILE_DISC</span></a></i> - These functions (the former treats values in a column as points in a continuous distribution of values, while the latter treats column values as discrete data values) are useful for writing a query that answers a question such as, "Show me the value that is the 90th percentile with regard to salaries of employees in department A01."</span></li><li><span style="font-family: arial;"><i><a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=functions-listagg"><span style="color: #2b00fe;">LISTAGG</span></a></i> - This function makes it easy to have a comma-separated list of values (e.g., last names of employees who have more than 10 years of service with the company, for a given department) as a column of a query result set.</span></li><li><span style="font-family: arial;"><i><a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=sf-hash-crc32-hash-md5-hash-sha1-hash-sha256"><span style="color: #2b00fe;">HASH_MD5</span></a></i> - With this function, you can use a SQL statement to get an MD5 hash of a value before inserting that value into a table (and there are three related built-in functions associated with other popular hashing algorithms).</span></li></ul><li><span style="font-family: arial;"><i>Application-specific lock timeout value (introduced with Db2 13 for z/OS)</i> - Db2 13 provided the new <a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=registers-current-lock-timeout"><span style="color: #2b00fe;">CURRENT LOCK TIMEOUT</span></a> special register, through which an application can set a lock timeout value that is different from the default lock timeout value for the Db2 system. Suppose, for example, that the system's default lock timeout value is 30 seconds. Maybe the development team for a mobile app that will drive accesses to a Db2 for z/OS database wants to make sure that a user will never have to look for 30 seconds at a spinning colored wheel on a mobile phone screen if a lock required by the app can't be readily obtained. The application team might decide (probably rightly) that it would be better to have a lock timeout value of 3 seconds for this app, have a condition handler in the Db2-accessing program for a lock timeout error, and in the event of a lock timeout (noticed in 3 seconds, versus 30 seconds) send an "Oops! Something went wrong - please try again" message to the user. I as a user would prefer that to looking at the spinning colored wheel for 30 seconds. Similarly, the development team for a long-running, mission-critical batch application might not want their job to time out unless a lock wait exceeds 10 minutes. Easily done with a SET CURRENT LOCK TIMEOUT statement.</span></li><li><span style="font-family: arial;"><i><a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=running-ai-queries-sql-data-insights"><span style="color: #2b00fe;">SQL Data Insights</span></a> (introduced with Db2 13 for z/OS)</i> - This feature represents the first embedding of advanced machine learning technology in the Db2 for z/OS "engine." It's easy for a DBA to set up and easy for a developer (or a user utilizing a query tool) to use, because the data science work was done by IBM development teams. SQL Data Insights is made usable in the form of three new built-in Db2 functions (more will be delivered via future Db2 13 function levels): AI_SIMILARITY, AI_SEMANTIC_CLUSTER and AI_ANALOGY. These new functions allow for the asking of what I like to call "fuzzy" queries. Here's one example: suppose the fraud analysis team at an insurance company finally caught someone who had been submitting fraudulent claims (and had been very good at covering his tracks). Among the company's several million other policy holders, who else might be engaging in hard-to-detect fraudulent activity? Via the AI_SIMILARITY built-in function of Db2, you can (using standard query syntax for a built-in function) easily code a query that will do this: "Hey, Db2. Here is the ID of a policy holder. Show me the IDs of the 20 other policy holders who are most like this one." And here's the kicker: in coding that query, <i>you don't have to tell Db2 what you mean by "like this one."</i> Db2 will detect patterns of similarity in the data in the specified table - patterns that a human being might be challenged to discern - and return the rows with the highest "similarity scores" in relation to the policy holder ID provided as input to the function. You can turn that list over to the fraud detection team and say, "Hey, guys. Do a deep-dive analysis of activity for the policy holders associated with these 20 IDs - they are the ones most similar to the fraudster that we recently caught."</span></li></ul><span style="font-family: arial;">And there are more application-enabling Db2 features where these came from. Again, let Db2 do work that you might otherwise have to do programmatically. Not only will that save you time and effort - it's likely that Db2 will get the work done more CPU-efficiently than program code would.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><b>Take advantage of Db2 global variable functionality</b></span></div><div><span style="font-family: arial;"><b><br /></b></span></div><div><span style="font-family: arial;">Db2 11 for z/OS introduced <a href="http://robertsdb2blog.blogspot.com/2015/07/how-will-you-use-db2-for-zos-global.html"><span style="color: #2b00fe;">global variables</span></a>. Unlike a traditional host variable, which you have to define in your program code, a Db2 global variable is created by a Db2 DBA. Once a global variable has been created, it can be used by an application process (a Db2 DBA just has to grant to the ID of the process the privilege to use the global variable). When an application references a Db2 global variable in a SQL statement, it gets its own instance of that global variable (in other words, if there is a Db2 global variable called GLOBVAR, and application process A puts 'cat' in the global variable and application process B puts 'dog' in the global variable, when the two processes look at the value of GLOBVAR then A will see 'cat' and B will see 'dog'). A Db2 global variable makes it really easy to get a value from a Db2 table and pass it via the global variable to a subsequent SQL statement (when the subsequent SQL statement references the global variable in, say, a query predicate, it is as though the predicate were referencing the value previously placed in the global variable).</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">Another Db2 global variable use case: a global variable makes it really easy to get a value from a Db2 advanced trigger (that's a trigger that has as part of its definition a SQL procedure language routine - more on SQL PL, below): the trigger just puts the value in a global variable, and when the trigger's processing is done and control is returned to the program that caused the trigger to fire, the program looks in the global variable and, voila - there's the value placed by the trigger.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><b>Leverage SQL procedure language, and manage and deploy Db2 for z/OS SQL routines in an agile way</b></span></div><div><span style="font-family: arial;"><b><br /></b></span></div><div><span style="font-family: arial;"><a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=sql-procedural-language-pl"><span style="color: #2b00fe;">SQL procedure language</span></a> (SQL PL) lets you code Db2 routines (stored procedures, user-defined functions and advanced triggers) using only SQL statements. This is do-able thanks to a class of Db2 SQL statements called control statements (a reference to logic flow control). These statements have names such as LOOP, ITERATE, WHILE and GOTO - you get the picture.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">SQL PL has become really popular over the past 10-15 years (it was introduced with Db2 9 for z/OS). When it comes to Db2 data processing routines, those written in SQL PL can have both functional and performance advantages over routines written in other languages (<a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=procedures-autonomous"><span style="color: #2b00fe;">autonomous procedures</span></a> are just one example of a functional benefit of stored procedures written in SQL PL). If you decide to use SQL PL routines for an application, I encourage you to manage and deploy these routines in an agile way. In terms of SQL PL routine management, consider how associated source code will be managed. The source code of a Db2 for z/OS "native SQL procedure" (i.e., a stored procedure written in SQL PL) is the CREATE PROCEDURE statement that defines the stored procedure. How should you manage this source code? <i>The same way you'd manage any other source code</i> - don't get thrown off by the fact that this source code has a CREATE in it. Does your organization use an open-source source code management (SCM) tool such as GitLab? OK, fine: use GitLab to manage the source for your Db2 native SQL procedures - you wouldn't be the first to do that.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">How about deployment of Db2 SQL PL routines - especially native SQL procedures? To do that in the most agile way possible, use <a href="http://robertsdb2blog.blogspot.com/2021/07/create-or-replace-agile-deployment-of.html"><span style="color: #2b00fe;">CREATE OR REPLACE syntax</span></a> when coding these routines. This is the best fit for a unified DevOps pipeline (i.e., an application deployment pipeline used for all of your organization's applications, regardless of the platform(s) on which application programs will run).</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><b>The bottom line</b></span></div><div><span style="font-family: arial;"><b><br /></b></span></div><div><span style="font-family: arial;">In the minds of the IBM folks who develop Db2 for z/OS, application developers are a tremendously important constituency. There are all kinds of Db2 features and functions that were expressly designed to make life easier for application developers working on programs that will access Db2 data servers. Learn about this stuff and take advantage of it. And work with your organization's Db2 for z/OS DBAs. They can help you leverage Db2's application-enabling capabilities.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">Code on!</span></div>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com0tag:blogger.com,1999:blog-4516533711330247058.post-83362832021219795202023-06-30T07:48:00.000-07:002023-06-30T07:48:39.210-07:00Db2 for z/OS: What I Would Say to Application Developers (Part 1)<p><span style="font-family: arial;">Not long ago, I received a request to deliver a couple of Db2 for z/OS-focused webcasts for an organization's application developers. The person who asked about this initially gave me the impression that the purpose of the webcasts would be to help developers write "efficient SQL." This did not have much appeal for me (as I'll explain below), and I communicated as much back to the requester. Subsequently, this individual rephrased the request, indicating that the aim of the webcasts would be to provide "insights for developers to increase their confidence and skills around Db2 [for z/OS] in both development and problem analysis." "OK," I thought to myself, "Now we're talking." This ask gave me an opportunity to think about what I'd like to say to people who write (or might write in the future) application programs that involve accessing Db2 for z/OS-managed data. I'll share these thoughts of mine in a two-part blog entry. In this first part I'll talk about application performance - but maybe not in the way you'd expect. In the part two entry, which I'll likely post in the next 2-3 weeks, I'll focus on application enablement from a Db2 for z/OS perspective.</span></p><p><span style="font-family: arial;">OK, why will a request to talk to developers about "writing efficient SQL" generally rub me the wrong way? Two reasons: first, something I heard a few years ago. I was at a big Db2-related conference, sitting in the audience for a session delivered by the person who was at that time the leader of the optimizer team in the IBM Db2 for z/OS development organization (Db2's optimizer parses a query and generates for that query the access plan that it estimates will produce the requested result set at the lowest cost and with the best performance). The presenter said (in words to this effect, and with emphasis added by me), "As the leader of the Db2 for z/OS optimizer team, my message for application developers is this: <b><i>job one for you is to write the query that will retrieve the data that your program needs.</i></b> If that query could be written differently so as to retrieve the same result faster, we'll take care of that." What he was specifically referring to is the Db2 optimizer's ability to re-write a query under the covers so that the same result will be generated faster (more on that re-write capability momentarily). That statement by the optimizer team leader made a huge impression on me, and I think his words were absolutely spot-on.</span></p><p><span style="font-family: arial;">I feel that it's very important for an application developer, when writing SQL targeting a Db2 for z/OS database, to focus on a query's <i style="font-weight: bold;">objective</i>, versus its <i style="font-weight: bold;">form</i>. Why? For one thing, job one really is to get the right data. If a query returns incorrect or incomplete data to a program, who cares if the query runs quickly? A bad result that is returned in a short time is still a bad result. Nothing is more important than retrieving the data that a program requires. Secondly, I believe it's very important for a developer writing Db2-targeting SQL to <b><i>not have to think about the fact</i></b> that the target DBMS is Db2 for z/OS. All you as an application developer should really have to think about is that the target DBMS is relational in nature. If you have to stop and think, "Oh, let's see - the data I'm going after is in a Db2 for z/OS database. That means I have to do X, Y and Z in order to get good performance," that's going to negatively impact your productivity, assuming that you're also called on to write SQL that targets other relational DBMSs. As far as I'm concerned, when Db2 for z/OS is the target DBMS you should just think, "relational DBMS," and go from there.</span></p><p><span style="font-family: arial;">Here's another reason that a request to "tell developers how to write efficient Db2 for z/OS SQL" raises my hackles: too many Db2 for z/OS DBAs, in my opinion, just assume that the average application developer writes inefficient SQL. It's kind of like complaining about the food at college just because it's college food, regardless of whether or not it's actually tasty. That's not a helpful attitude. I've advised Db2 for z/OS DBAs that they should think of themselves as partners with developers when it comes to getting new applications and new application functionality into production. Similarly, I would advise developers to be partners with Db2 for z/OS DBAs when it comes to analyzing and addressing performance issues related to Db2 for z/OS-accessing applications.</span></p><p><span style="font-family: arial;">How can a developer be a partner when it comes to taking action to resolve performance issues related to Db2 for z/OS-targeted queries? Some thoughts on that matter:</span></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;"><i style="font-weight: bold;">Learn some of the lingo.</i> Sometimes, Db2 for z/OS DBAs will say things like, "This SQL statement isn't performing well because it has this stage 2 predicate?" Huh? OK, here's what that means: predicates (the result set row-qualifying parts of a query, such as WHERE ACCOUNT_NUM = 1234) in Db2 for z/OS SQL statements can be either stage 1 or stage 2 in nature. These terms refer, respectively, to two components of Db2 for z/OS: the data manager (stage 1) and the relational data system (stage 2). A stage 1 predicate can be evaluated by the Db2 data manager, while a stage 2 predicate has to be processed by the Db2 relational data system. Stage 2 predicates generally require more CPU time for processing than stage 1 predicates; furthermore, stage 1 predicates are usually index-able, while stage 2 predicates are almost never index-able. That index-able versus non-index-able characteristic of a query might result in a requirement for a table space scan when the query is processed, and that could <u>really</u> slow down query execution, especially when the table in question is really large. On the other hand, a stage 2 predicate in a query might not be a big deal if the query has another predicate or predicates that are highly filtering (i.e., that are evaluated as "true" for only a small number of a table's rows) and index-able.</span></li></ul><p></p><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p></p><div style="text-align: left;"><span style="font-family: arial;">If you're interested, you can read about stage 1 and stage 2 and index-able and non-index-able predicates on <a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=efficiently-summary-predicate-processing"><span style="color: #2b00fe;">this page</span></a> of the Db2 for z/OS online documentation. Do you need to sweat a lot about stage 1 versus stage 2 predicates when writing SQL statements for an application? I'd say, not necessarily. Remember that job one is to write a query that returns the data that your program needs. On top of that, Db2 for z/OS, especially over the most recent several versions, has gained more and more query re-write capabilities (as I mentioned previously). Suppose, for example, that you need to get from a Db2 for z/OS table all customers whose accounts were opened in 2010, and you write a predicate like this one to get those rows:</span> </div><p></p></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p></p><div style="text-align: left;"><span style="font-family: arial;">WHERE YEAR(DATE_OPENED) = 2010</span></div><p></p></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p style="text-align: left;"><span style="font-family: arial;">That predicate is stage 2 and non-index-able; however, Db2 for z/OS, in preparing your query for execution, can automatically re-write that predicate in this form, which is stage 1 and index-able:</span></p></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p style="text-align: left;"><span style="font-family: arial;">WHERE DATE_OPENED BETWEEN '2010-01-01' AND '2010-12-31'</span></p></blockquote><p></p><ul style="text-align: left;"><li><span style="font-family: arial;"><i style="font-weight: bold;">Know something about what's possible for improving a query's performance.</i> If a query processed by Db2 for z/OS is not performing as it needs to, re-writing the query in some way is one possible solution, but there may be other performance-boosting actions that could be taken instead. One possible solution could be a Db2 for z/OS index created on an expression - something do-able since Db2 Version 9 (as of the date of this blog post, the current Db2 for z/OS version is 13). Suppose, for example, that your program needs rows selected from a Db2 for z/OS table based on an upper-case comparison of values in column COL1 with a provided character string constant. Your query might have a predicate that looks like this:</span></li></ul><p></p><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p></p><div style="text-align: left;"><span style="font-family: arial;">WHERE UPPER(COL1, 'EN_US') = 'ABCDE'</span></div><p></p></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p style="text-align: left;"><span style="font-family: arial;">That predicate is stage 2 and non-index-able; however, it will become index-able if an index is defined on that expression, as shown below (assume that COL1 is a column of table T1):</span></p></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><span style="font-family: arial;">CREATE INDEX UPPER_VAL ON T1<br /></span><span style="font-family: arial;">(UPPER(COL1, 'EN_US'))</span></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p style="text-align: left;"><span style="font-family: arial;">Another possible query tuning action is to provide the Db2 optimizer with the catalog statistics that it needs to choose a better-performing access plan for the query. In fact, colleagues of mine who are part of the team in IBM Support that works on cases (i.e., problem tickets) related to Db2 for z/OS query performance have said that <i style="font-weight: bold;">the large majority</i> of query performance issues on which they work are ultimately resolved in this manner. Here's the deal: the primary input to Db2 for z/OS access path selection is statistics about objects related to the query - e.g., tables, and indexes on those tables, and columns in tables - that are periodically collected (often by a Db2 for z/OS utility called RUNSTATS) and stored in the Db2 catalog. The richer and more complete the catalog statistics are, the better the Db2 optimizer can do in generating a well-performing access plan for a query. The tricky part is this: what statistics should be gathered for tables, columns, indexes, etc. to enable the optimizer to choose a well-performing access path for a particular query? Would histogram statistics on a given column help? How about frequent-value percentage information for a column? How about correlation statistics for a certain pair of table columns? Telling RUNSTATS to generate every possible statistic on everything would make that utility too costly to execute, so the utility is often executed with a specification that generates what you might call a good "base" of statistics (TABLE(ALL) INDEX(ALL) is typically the specification used for this purpose). How do you know when the optimizer needs additional statistics - and which additional statistics - in order to generate a well-performing access plan for a query that is currently performing poorly? Fortunately, starting with Db2 12 for z/OS the optimizer answers this question for you in the form of statistics profiles, as described in <a href="http://robertsdb2blog.blogspot.com/2019/04/db2-12-for-zos-statistics-profiles-just.html"><span style="color: #2b00fe;">an entry I posted to this blog a few years ago</span></a>. I'd say, if a query you wrote is not performing as it needs to then before trying to re-write the query or asking a DBA to add or alter an index to address the situation, see if Db2 has inserted a statistics profile in the SYSTABLES_PROFILES catalog table for one or more of the tables accessed by your query (as described in the aforementioned blog entry). If there is such a statistics profile or profiles, work with a DBA to get RUNSTATS executed using the profile(s) and then let Db2 re-optimize the query using the statistics added to the catalog by that RUNSTATS job (for a so-called static query, re-optimization would be accomplished via a REBIND of the associated Db2 package; for a query that is dynamic in the Db2 sense of that word, re-optimization is triggered by invalidating the previous prepared form of the query in Db2's dynamic statement cache). In plenty of cases this will resolve a query performance issue.</span></p></blockquote><p></p><ul style="text-align: left;"><li><span style="font-family: arial;"><i style="font-weight: bold;">Know something about EXPLAIN.</i> EXPLAIN is a Db2 SQL statement (also an option of the BIND and REBIND PACKAGE commands, for static SQL statements) through which you can get information about the access path selected by the optimizer for a query (you can read about the EXPLAIN statement in the <a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=statements-explain"><span style="color: #2b00fe;">online Db2 for z/OS documentation</span></a>). EXPLAIN-generated access path information, in its traditional form, is written to the EXPLAIN tables (these are Db2 tables, as you might expect). The most important of these tables is the one called <a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=tables-plan-table"><span style="color: #2b00fe;">PLAN_TABLE</span></a>. Information in this table shows, among other things, the order in which the parts of a query are executed (for example, the order of table access when a statement involves a multi-table join), how data in a table is accessed (e.g., through an index and, if so, which index), the number of columns in an index key that are a match for columns referenced in a query predicate (MATCHCOLS - a higher number is generally a good thing), and the type of join method used when tables are joined (e.g., nested loop or merge scan). If you know something about the information in PLAN_TABLE, you'll be better equipped to partner with a Db2 for z/OS DBA to see how execution of a query that is not performing as desired could potentially be sped up.</span></li></ul><blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;"><div style="text-align: left;"><span style="font-family: arial;">A lot of veteran Db2 for z/OS DBAs are very familiar with EXPLAIN information in its PLAN_TABLE form. If you're interested in viewing EXPLAIN information in a different form, consider using the <a href="https://ibm.github.io/db2forzosdeveloperextension-about/docs/tuning-sql-queries/viewing-a-graphical-explanation-of-access-plans"><span style="color: #2b00fe;">Visual Explain</span></a> feature of the <a href="https://marketplace.visualstudio.com/items?itemName=IBM.db2forzosdeveloperextension"><span style="color: #2b00fe;">IBM </span></a></span><span style="font-family: arial;"><a href="https://marketplace.visualstudio.com/items?itemName=IBM.db2forzosdeveloperextension"><span style="color: #2b00fe;">Db2 for z/OS Developer Extension for Visual Studio Code</span></a> (a no-charge IBM software tool designed to facilitate development of applications that access Db2 for z/OS data). Visual Explain (as the feature's name implies) provides a visual representation of the access path selected by the Db2 optimizer for a query; and, it's not just pretty pictures - hovering over or clicking on a part of the displayed access path provides very useful related information. Among other things, you can see the number of result set rows that Db2 thinks there will be following execution of that part of the access plan. For a query that is not performing as desired, you might see such information and think, "That's not right. After accessing that table the result set should have way more (or way fewer) rows than indicated by this estimate." The implication here is that you know something about the data that Db2 doesn't know (thus the Db2 optimizer's off-the-mark estimate concerning refinement of the result set as the query's access plan is processed). That, in turn, could suggest that catalog statistics might need to be augmented to provide Db2 with a clearer view of the characteristics of data in a target table (as mentioned in the reference, above, to Db2's SYSTABLES_PROFILES catalog table); or, that might prompt you to consider adding or modifying a query predicate to provide Db2 with a different form of the data request - one that might generate the required result set more quickly.</span></div></blockquote><p><span style="font-family: arial;">The bottom line is this: while your primary focus in writing Db2 for z/OS SQL statements should be on retrieving the data your program requires (and I'm focusing on queries because INSERT, UPDATE and DELETE operations are usually more straightforward from a performance perspective), there could be a situation in which a query you coded needs to execute with greater speed and efficiency. Query performance tuning is something to which many Db2 for z/OS DBAs are accustomed, but success in that endeavor can be accelerated and made more likely when developers and DBAs work on this as a team. Don't worry about knowing as much about Db2 for z/OS as your DBA teammate - that's not your job; but, realize that your understanding of</span><span style="font-family: arial;"> your application's data requirements, and of the data the application is accessing in Db2, can be a big help when it comes to tuning a query's performance. It's definitely a case in which 1 (your specialized knowledge as a developer) plus 1 (the DBA's specialized knowledge of Db2 for z/OS) is greater than 2.</span></p><p><span style="font-family: arial;">In the part 2 of this blog entry I'll have some things to say about application enablement in a Db2 for z/OS context - that is, about ways that you can leverage Db2 functionality to get more feature-rich applications developed more quickly.</span></p><p></p>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com0tag:blogger.com,1999:blog-4516533711330247058.post-53906041516334802472023-05-25T14:15:00.000-07:002023-05-25T14:15:14.938-07:00OUTBUFF: A Db2 for z/OS ZPARM You Really Ought to Check<p><span style="font-family: arial;">Over the past year or so, I've seen more and more situations in which a too-small Db2 for z/OS log output buffer is negatively impacting system and application performance. The Db2 development team took aggressive action to remedy that situation via a change to the default value of OUTBUFF (the relevant ZPARM parameter) with Db2 13, but if you are not yet on Db2 13 you should make this change yourself in your Db2 12 environment (and, if you are on Db2 13, you should definitely be using the new OUTBUFF default, or an even higher value). In this blog entry I'll provide information that I hope will make all of this clear and meaningful for you.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>The Db2 for z/OS log output buffer</b></span></p><p><span style="font-family: arial;">Db2, of course, logs changes made to database objects (aside from the situation in which a table is defined with the NOT LOGGED attribute - unusual, in my experience). This is data integrity 101 - data changes have to be logged so that they can be rolled back if a unit of work fails before completing, and so that database objects can be recovered when that is required, and so that a Db2 subsystem can be restarted and restored to a consistent state after an abnormal termination, etc., etc.</span></p><p><span style="font-family: arial;">Db2 data changes are physically written to the active log data sets, which are made reusable (i.e. made available for further use after being filled) via the log archive process. Prior to being written to the current pair of active log data sets (you ALWAYS want to do dual-logging, at least in a production Db2 environment), data changes are written to the log output buffer in memory. Information in the log output buffer is externalized (i.e., written to the disk subsystem) when the log output buffer is full, and also when a data-changing unit of work commits.<br /><br />The size of the log output buffer is specified by way of the OUTBUFF parameter in the Db2 ZPARM module (think of the ZPARM module as the configuration parameter settings for a Db2 subsystem). For Db2 12, the default value for OUTBUFF is 4000 KB (that became the default value for OUTBUFF starting with Db2 10 for z/OS). With Db2 13, the OUTBUFF default value was changed to 102400 KB. Yeah, that's a 25X increase (when I communicated that in writing to the Db2 for z/OS team at a certain site recently, the Db2 systems programmer on the team asked me, "Is that a typo?"). Why this major increase in the OUTBUFF default value? Two reasons:</span></p><p></p><ol style="text-align: left;"><li><span style="font-family: arial;">It's eminently do-able for the vast majority of production Db2 subsystems that I have seen. Yes, in a relative sense a 25X increase in a ZPARM parameter's default value may seem to be a really aggressive move, but in absolute terms the increase - about 98 MB - is a drop in the bucket for a z/OS LPAR with a large real storage resource. Many production z/OS LPARs these days are generously configured with memory, because mainframe memory keeps getting cheaper on a per-gigabyte basis, and because leveraging that memory can be very good for system performance. It's increasingly common for production z/OS LPARs to have multiple hundreds of GB - or more - of central storage.</span></li><li><span style="font-family: arial;">It can be very helpful for Db2 system and application performance, as noted below.</span></li></ol><span style="font-family: arial;">From a performance perspective, a larger Db2 log output buffer has two main benefits. They are...</span><p></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Larger OUTBUFF benefit 1: reduced application log write wait time</b></span></p><p><span style="font-family: arial;">Db2 accounting trace data - specifically, data provided by accounting trace class 3 (which, along with accounting trace classes 1 and 2, is almost always active at all times for a production Db2 subsystem) - records (among other things) the time that Db2-accessing applications wait for log write operations to complete. Average wait-for-log-write-I/O time (available via <a href="http://robertsdb2blog.blogspot.com/2011/03/monitoring-db2-for-zos-whats-in-your.html"><span style="color: #2b00fe;">a Db2 monitor-generated accounting long report</span></a>) is generally quite small - often less than 1% of total in-Db2 wait time (i.e., class 3 time) for a Db2 application workload. In some cases, however, this can be a significantly larger percentage of in-Db2 wait time. Now, there are multiple factors that can contribute to elevated wait-for-log-write-I/O time, but one of these factors can be a too-small log output buffer. If you see higher levels of wait-for-log-write-I/O time for your Db2 application workload, check the value of the field labeled UNAVAILABLE OUTPUT LOG BUFF (or something similar to that - different Db2 monitor products sometimes label the same field in slightly different ways) in a Db2 monitor-generated statistics long report (the field will be in a section of the report with the heading LOG ACTIVITY, or something similar to that). In my experience the value of this field is usually 0, but if the field has a non-zero value then it could be a good idea to set OUTBUFF to a larger value for the Db2 subsystem in question. Even if the value of UNAVAILABLE OUTPUT LOG BUFF is 0, if your Db2 subsystem has a log output buffer that's on the small side then making it larger via an increase in the OUTBUFF value could help to make Db2 log write operations more efficient, thereby potentially contributing to a decrease in wait-for-log-write-I/O time for your Db2-accessing applications.</span></p><p><span style="font-family: arial;"><br /></span></p><p><b style="font-family: arial;">Larger OUTBUFF benefit 2: better log read performance</b></p><p><span style="font-family: arial;">The importance of good Db2 log write performance should be obvious: Db2 is writing to its log all the time, so getting that work done quickly and efficiently is good for any Db2 data-changing application. Can log <u>read</u> performance be important for a Db2-accessing process? YES - and that's especially true for a Db2 data-change-replication process.</span></p><p><span style="font-family: arial;">It's not unusual for Db2 for z/OS-managed data to be replicated to some other location for some purpose. The data replication tools, from IBM and other vendors, that capture Db2 for z/OS data changes and send them in near-real time to another location generally do their data change capture work by issuing requests to the log manager component of Db2 to retrieve data change information (this is done using a Db2 trace record, IFCID 306, that can be requested synchronously by a process such as a data replication tool). Especially when the volume of changes made to data in a replicated Db2 table is high, you REALLY want the Db2 log manager to be able to retrieve the requested data change information from the log output buffer in memory, versus having to read the information from the log data sets on disk. If the log output buffer is too small, you can see a high percentage of log read requests that require access to the log data sets on disk. The volume of such log data set read I/Os can be very high - like, thousands per second. That chews up CPU cycles and adds to data replication latency (this latency refers to the time between a change being made to data on the source Db2 for z/OS system and that change being reflected in the corresponding data at the replication target location) - both things you'd rather avoid.</span></p><p><span style="font-family: arial;">How can you check on this? Again, go to a statistics long report generated by your Db2 monitor, and again go to the section under the heading, LOG ACTIVITY (or something similar to that). Check the fields labeled READS SATISFIED-OUTP.BUF(%) and READS SATISFIED-ACTV.LOG(%). What you want to see (what I'd certainly like to see) is a value for </span><span style="font-family: arial;">READS SATISFIED-OUTP.BUF(%) that is well north of 90, and - conversely - a value for </span><span style="font-family: arial;">READS SATISFIED-ACTV.LOG(%) that is in the single digits (ideally, low single digits). If you see a lower than desired value for the percentage of log reads satisfied from the log output buffer, make OUTBUFF larger if you can.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Can you make OUTBUFF larger, and if so, how high should you go?</b></span></p><p><span style="font-family: arial;">The answer to the first part of that question (assuming that the value of OUTBUFF is not already at the maximum of 400000 KB) depends on the pressure (or lack thereof) on the real storage resource of the z/OS LPAR in which the Db2 subsystem of interest is running. My favorite indicator of real storage constraint is the LPAR's demand paging rate, available from <a href="http://robertsdb2blog.blogspot.com/2023/02/two-rmf-zos-monitor-reports-with-which.html"><span style="color: #2b00fe;">a z/OS monitor-generated summary report</span></a>. If the LPAR's demand paging rate is less than 1 per second, the real storage resource is not at all constrained, and you have (as far as I'm concerned) a green light for increasing the OUTBUFF value. If the LPAR's demand paging rate is over 1 per second, you might want to see if more memory can be configured for the system, or if some memory can be freed up (perhaps by shrinking a Db2 buffer pool that is larger than it needs to be), prior to making the value of OUTBUFF significantly larger than its existing value.</span></p><p><span style="font-family: arial;">If the z/OS LPAR's real storage is not constrained (as described above), and you want to make a Db2 subsystem's OUTBUFF value larger, how high should you go? First of all, I would highly recommend setting OUTBUFF at least to the new-with-Db2-13 default value of 102400 KB. Should you go higher than that? Well, I would if the value of </span><span style="font-family: arial;">READS SATISFIED-OUTP.BUF(%) is less than 90. One thing to keep in mind here: OUTBUFF is not an online-updatable ZPARM. That means you have to recycle a Db2 subsystem (i.e., stop and restart it) in order to put a new OUTBUFF value into effect. In a Db2 data sharing system, that may not be a big deal (application work can continue to process on other members of the data sharing group as the one member is recycled), and even for some standalone Db2 subsystems there are regular opportunities to "bounce" the subsystem. On the other hand, at some sites where Db2 runs in standalone mode there are only a few times per year when a production Db2 subsystem can be recycled. If that's your situation, you might want to consider going to the maximum OUTBUFF value of 400000 KB (again, if the LPAR's memory is not constrained - and it's not if the LPAR's demand paging rate is less than 1 per second).</span></p><p><span style="font-family: arial;">And that's what I have to say about OUTBUFF. Check yours, and check the relevant information in Db2 monitor-generated accounting and statistics reports to see if an OUTBUFF increase would be good for your system.</span></p>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com0tag:blogger.com,1999:blog-4516533711330247058.post-55372045529149834912023-04-28T15:23:00.000-07:002023-04-28T15:23:42.402-07:00Migrating to Db2 13 for z/OS When You Have Old (pre-11.1) Db2 Client Code on Your App Servers<p><span style="font-family: arial;">Not long ago, I had a talk with a Db2 for z/OS systems programmer who works at a pretty big site. In a somewhat dramatized form, our conversation went something like this:</span></p><p><span style="font-family: arial;"><b>Me:</b> "When are you guys going to migrate your production Db2 for z/OS systems to Db2 13?"</span></p><p><span style="font-family: arial;"><b>Db2 sysprog:</b> "Later than I'd like."</span></p><p><span style="font-family: arial;"><b>Me:</b> "Why's that?"</span></p><p><span style="font-family: arial;"><b>Db2 sysprog:</b> "We have some old Db2 client code on some of our application servers."</span></p><p><span style="font-family: arial;"><b>Me:</b> "So?"</span></p><p><span style="font-family: arial;"><b>Db2 sysprog:</b> "So, I can't take APPLCOMPAT for our NULLID packages above V12R1M500."</span></p><p><span style="font-family: arial;"><b>Me:</b> "No prob. Just leave the APPLCOMPAT value for the NULLID packages at V12R1M500, and go ahead and activate function level V12R1M510, and then migrate the systems to Db2 13."</span></p><p><span style="font-family: arial;"><b>Db2 sysprog:</b> "I can do that?"</span></p><p><span style="font-family: arial;"><b>Me:</b> "YES."</span></p><p><span style="font-family: arial;">The very next week, I had a very similar exchange with another Db2 for z/OS administrator at a different site. It seems clear to me that there's some misunderstanding in this area out there, with people thinking that way-old Db2 client code represents a roadblock on the way from Db2 12 for z/OS to Db2 13. NOT TRUE, as I hope to make clear in this blog entry.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Terminology: "Db2 client code"</b></span></p><p><span style="font-family: arial;">This term refers to the piece of IBM code that runs on a remote (from the Db2 for z/OS perspective) server that enables an application on that server to be a DRDA requester (DRDA is short for distributed relational database architecture - the protocol used for Db2 distributed database processing). A DRDA requester application is one that sends SQL statements to Db2 by way of a driver such as IBM's JDBC or ODBC driver. Most often, the Db2 client code is the IBM Data Server Driver Package (for which entitlement is related to an organization's license for IBM Db2 Connect). Sometimes, it's something like the IBM Db2 Connect Runtime Client. In any case, the Db2 client code is considered to be part of the Db2 for Linux/UNIX/Windows (LUW) product family, and it will have a version that corresponds to a Db2 for LUW version.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>What is "old Db2 client code" in this context?</b></span></p><p><span style="font-family: arial;">Short answer: any version prior to 11.1. Now, to explain that short answer: some would say (understandably) that "old code" means out-of-support code. The 11.1 version of Db2 client code is out of support (and has been since April of 2022 - see <a href="https://www.ibm.com/support/pages/db2-distributed-end-support-eos-dates"><span style="color: #2b00fe;">https://www.ibm.com/support/pages/db2-distributed-end-support-eos-dates</span></a>). Why, then, do I refer to pre-11.1 Db2 client code as being "old," implying that 11.1 Db2 client code, though out of support, is not "old?" It all has to do with context, and the context in this case is a Db2 12 for z/OS system that is the DRDA server for DRDA requester applications.</span></p><p><span style="font-family: arial;">APPLCOMPAT is a Db2 for z/OS package bind parameter that specifies the Db2 application compatibility level that will be in effect when the package is executed (for more information about APPLCOMPAT, see the <a href="https://robertsdb2blog.blogspot.com/2019/06/db2-for-zos-talking-about-applcompat.html"><span style="color: #2b00fe;">part 1</span></a> and <a href="http://robertsdb2blog.blogspot.com/2019/07/db2-for-zos-talking-about-applcompat.html"><span style="color: #2b00fe;">part 2</span></a> blog entries I posted on that topic a few years ago). With the possibility of a few exceptions, every Db2 for z/OS package will have an APPLCOMPAT value, and that is true for the packages in the package collection called NULLID. NULLID is the "home" collection for the Db2 for z/OS packages that are executed when a DRDA requester application accesses the Db2 for z/OS system.</span></p><p><span style="font-family: arial;">Here's the crux of the matter at hand: if the APPLCOMPAT value for the NULLID packages is taken above V12R1M500, DRDA requester applications will get an error when trying to connect to the Db2 for z/OS system if they are using pre-11.1 Db2 client code.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Why the preceding sentence does not amount to a Db2 13 migration roadblock</b></span></p><p><span style="font-family: arial;">Before you can <a href="http://robertsdb2blog.blogspot.com/2022/10/getting-ready-to-migrate-to-db2-13-for.html"><span style="color: #2b00fe;">migrate to Db2 13 for z/OS</span></a> from Db2 12, you have to activate Db2 12 function level V12R1M510 (the last of the Db2 12 function levels). What the Db2 for z/OS systems programmer I referenced at the beginning of this blog entry thought, and what apparently a number of other Db2 for z/OS people think, is that the APPLCOMPAT value for the NULLID packages (and maybe for other Db2 for z/OS packages, as well) has to be V12R1M510 before you can migrate a Db2 12 system to Db2 13. THAT IS NOT TRUE. Can you have, in a Db2 13 system, packages in the NULLID collection (and in other collections) that have an APPLCOMPAT value of V12R1M500? YES, YOU CAN. In fact, APPLCOMPAT values as low as V10R1 are supported in a Db2 13 environment. So, if your NULLID packages are at APPLCOMPAT(V12R1M500), and old (as defined above) Db2 client code is keeping you from upping that APPLCOMPAT value for your NULLID packages, <i>leave the NULLID packages at APPLCOMPAT(V12R1M500), and activate function level V12R1M510 (when your Db2 code and catalog are at the right level), and then migrate the Db2 12 system to Db2 13.</i> There is NOTHING about having NULLID packages at APPLCOMPAT(V12R1M500) that gets in the way of your doing this.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>But what if you really want to take APPLCOMPAT for your NULLID packages to a higher level?</b></span></p><p><span style="font-family: arial;">First, why might you want to do this? Best answer, I'd say: because you want developers of DRDA applications in your environment to be able to use the latest Db2 for z/OS SQL syntax and functionality (one particularly noteworthy example: the new built-in AI functions of Db2 13 for z/OS, part of that version's SQL Data Insights feature, which can be used via packages with an APPLCOMPAT value of V13R1M500 or higher). If there's pre-11.1 Db2 client code on some of your application servers, and you really want to take APPLCOMPAT higher than V12R1M500 for your NULLID packages (I would), you have a couple of options:</span></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;">One option: update your Db2 client code. This would be for many people the ideal approach. Get the Db2 client code to the current level, which is 11.5, and you get two benefits: 1) you're actually using Db2 client code that is supported by IBM (always nice), and 2) you can take APPLCOMPAT for your Db2 for z/OS NULLID packages as high as you want. Of course, updating the Db2 client code will likely require working in concert with application server administrators in your organization that can perform the code update.</span></li><li><span style="font-family: arial;">Another option: leave the old Db2 client code out there, and raise the APPLCOMPAT value for your NULLID packages anyway. This might be the required approach, at least in the near term, if your application server administrators are not presently able to help update old versions of Db2 client code within your IT infrastructure. How can you do this without causing connection errors for DRDA requester applications that are using old Db2 client code? You do that with the Db2 profile tables, together with an "alternate" collection for the IBM Data Server Driver / Db2 Connect packages, as explained below.</span></li></ul><br /><p></p><p><span style="font-family: arial;"><b>Creating (and, more importantly, using) an alternate collection for the IBM Data Server Driver / Db2 Connect packages</b></span></p><p><span style="font-family: arial;">Step 1 for this approach is to create the alternate collection for the packages whose "home" collection is NULLID. This is pretty easy to do: just BIND COPY the packages in the NULLID collection into a collection with some other name (I'll go with OLD_COLL for this example), and in doing that specify APPLCOMPAT(V12R1M500). DRDA requester applications using pre-11.1 Db2 client code will not get connection errors when they use the packages in that OLD_COLL collection. Ah, but how do you get those applications to use the OLD_COLL collection when they will, by default, be looking to use packages in the NULLID collection (NULLID is the default Db2 for z/OS package collection for DRDA requester applications)? This is where the Db2 profile tables come in.</span></p><p><span style="font-family: arial;">You can use SYSIBM.DSN_PROFILE_TABLE to identify a component of your DDF workload for which you want Db2 to take some action. The component of the DDF workload of interest here is the DRDA requester applications that are using pre-11.1 Db2 client code. How can you identify that DDF workload component as a profile? Easy: use the PRDID (short for product identifier) column of DSN_PROFILE_TABLE (see <a href="https://www.ibm.com/docs/en/db2-for-zos/12?topic=tables-dsn-profile-table"><span style="color: #2b00fe;">https://www.ibm.com/docs/en/db2-for-zos/12?topic=tables-dsn-profile-table</span></a>). How do you know which product ID(s) to use? You can get that information via output of the Db2 command -DISPLAY LOCATION (see <a href="https://www.ibm.com/docs/en/db2-for-zos/12?topic=work-displaying-information-about-connections-other-locations"><span style="color: #2b00fe;">https://www.ibm.com/docs/en/db2-for-zos/12?topic=work-displaying-information-about-connections-other-locations</span></a>). In the PRDID column of the command output, you'll see the product IDs associated with requesters, and there you'll see the version and release of the Db2 client code that a requester is using (see <a href="https://www.ibm.com/docs/en/db2-for-zos/12?topic=work-product-identifier-prdid-values-in-db2-zos"><span style="color: #2b00fe;">https://www.ibm.com/docs/en/db2-for-zos/12?topic=work-product-identifier-prdid-values-in-db2-zos</span></a>). Using the PRDID information provided via -DISPLAY LOCATION, insert a row (or rows) in DSN_PROFILE_TABLE for the pre-11.1 Db2 client code that is used in your environment. Having done that, for that row (or rows) in DSN_PROFILE_TABLE, insert a corresponding row (or rows) in SYSIBM.DSN_PROFILE_ATTRIBUTES to tell Db2 what you want it to do when one of the DRDA requesters using pre-11.1 Db2 client code requests a connection to the Db2 for z/OS system. And what do you want Db2 to do? You want Db2 to issue SET CURRENT PACKAGE PATH = OLD_COLL (using my example name for the collection into which you BIND COPY-ed the NULLID packages with a specification of APPLCOMPAT(V12R1M500)). This will make OLD_COLL the default collection for the DRDA requester applications using pre-11.1 Db2 client code. Having done this, you can take APPLCOMPT for the NULLID packages higher than V12R1M500, to the benefit of DRDA requester applications that are using 11.1-or-higher versions of the Db2 client code (note that the SET CURRENT PACKAGE PATH = OLD_COLL will happen at application connection time, so after doing the BIND COPY and profile table work you may need to have someone recycle the application servers on which old Db2 client code is running, so they'll get new connections to the Db2 for z/OS system and will be pointed to the OLD_COLL package collection). There is additional information on this approach in <a href="http://robertsdb2blog.blogspot.com/2018/07/db2-for-zos-using-profile-tables-to.html"><span style="color: #2b00fe;">an entry I posted to this blog a few years ago</span></a> (that entry concerns an alternate collection of IBM Data Server Driver / Db2 Connect packages used to get high-performance DBAT functionality, but the collection redirection technique is the same).</span></p><p><span style="font-family: arial;">And there you have it. I hope you don't have old Db2 client code on your application servers, but if you do, don't worry about that being an impediment to getting to Db2 13, because it isn't.</span></p>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com0tag:blogger.com,1999:blog-4516533711330247058.post-53232948696055327752023-03-30T14:30:00.000-07:002023-03-30T14:30:06.141-07:00Db2 for z/OS: If Index FTB Functionality is Disabled at Your Site, It's Time to Reconsider That<p><span style="font-family: arial;">Over the course of the past three weeks, I reviewed ZPARM settings (i.e., configuration parameter values) for three different production Db2 12 for z/OS environments at three different sites, and I noticed that index FTB (fast traverse block) functionality had been disabled in all three cases. I recommended to all three associated Db2-using organizations that they change the relevant ZPARM setting to re-enable FTB functionality, after first validating that the fixes for a set of related Db2 APARs have been applied to their Db2 12 code (the changes made by the fixes are part of the base Db2 13 code). My recommendation for you, if the FTB feature of Db2 has been "turned off" at your site, is to do the same: turn it on, after doing the aforementioned check of Db2 software maintenance if you're using Db2 12. In this blog entry, I'll explain what FTB functionality is, why it was disabled at some sites, and why it's time to go from "disabled" to "enabled" where feature deactivation has happened. I'll also provide information about the fixes (PTFs) that should be on your system to ensure the robust functioning of FTB-supporting Db2 code (again, if we're talking about Db2 12 - the base Db2 13 code has the solidified FTB functionality provided by the Db2 12 fixes).</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>The FTB raison d'etre: efficient use of non-leaf information in Db2 indexes</b></span></p><p><span style="font-family: arial;">Db2 for z/OS indexes serve various purposes, such as assisting with maintenance of a desired ordering of rows in a table and ensuring uniqueness of key values for which duplicates would be problematic, but for the most part indexes in a Db2 system are there to speed the execution of queries (and of non-query SQL statements that contain predicates, aka search clauses). Indexes deliver this performance boost by enabling identification of query result set rows without a laborious row-by-row examination of values. It can be said that Db2 indexes provide shortcuts that get you to your destination (a query's result set) faster than would otherwise be possible.</span></p><p><span style="font-family: arial;">The information in a Db2 index is arranged in what is known as a B-tree structure. The logical representation of this structure has something of the appearance of an upside-down tree: you have the root page at the top, and the leaf pages at the bottom. In-between the root page and the leaf pages of an index (unless the underlying Db2 table is quite small), you will have one or more levels of non-leaf pages. Finding a row in a table by way of an index on the table involves what is known as an index probe operation: Db2 starts at the root page and navigates down through the other non-leaf levels to reach the leaf page that contains the searched-for key value and the ID of the row (i.e., the row ID, or RID in Db2 parlance) or rows in which the key value can be found.</span></p><p><span style="font-family: arial;">OK, so what is the value of index fast traverse blocks? Well, an index probe involves GETPAGE activity. A GETPAGE is a Db2 request to examine the contents of a page in an index or a table space (when the page in question is not already in a Db2 buffer pool in memory, the GETPAGE drives a read I/O request). The more rows a table has, the more levels an index on the table can have. More index levels means more GETPAGE activity associated with use of the index, and that matters because GETPAGE activity is one of the main determinants of the CPU cost of executing a query. Index fast traverse block functionality, introduced by Db2 12 for z/OS, improves CPU efficiency for query execution by reducing index-related GETPAGE activity.</span></p><p><span style="font-family: arial;">An FTB reduces index GETPAGE activity by providing Db2 with a way to get to the leaf page of an index in which a query-predicate-matching key value is found without having to perform a top-to-bottom index probe. How that works: when Db2 builds an FTB structure in memory that is based on a given index, Db2 puts in that FTB structure the information in the non-leaf pages of the index (note that this is NOT just a matter of caching the index's non-leaf pages in memory - the FTB structure has a space requirement that is considerably smaller than what would be required to cache the index's non-leaf pages in an as-is manner); furthermore, navigation through an FTB structure does not require GETPAGE activity. Yes, FTB navigation does involve some instruction path length, but less than would be needed for the index GETPAGEs that would otherwise be required to get to a target leaf page. Let's say that an index on a large table has five levels. Retrieving a table row via the index will require six GETPAGEs - five for the index and one for the table space. If, on the other hand, Db2 has built an FTB structure from the index, when a query having a predicate that matches on the index's key is executed then Db2 can go to the FTB structure with the key value referenced in the predicate, and the FTB will tell Db2, "This is the leaf page in which you'll find that key value." Db2 then does <u>one</u> GETPAGE to examine that leaf page's contents, finds the key value and the associated RID, and does one more GETPAGE to access the row in the table space. Thanks to the FTB, we've gone from six GETPAGEs (five for the index and one for the table space) to two GETPAGEs (one for the index leaf page, one for the table space). Pretty good.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>How is FTB functionality turned off, and why would anyone do that?</b></span></p><p><span style="font-family: arial;">The FTB "on/off switch" is the ZPARM parameter INDEX_MEMORY_CONTROL. The default value for that parameter is AUTO. When INDEX_MEMORY_CONTROL is set to AUTO, Db2 notes the size of the subsystem's buffer pool configuration (i.e., the aggregate size of the subsystem's buffer pools) and says (figuratively speaking), "OK, I can create FTB structures from indexes, and the maximum amount of in-memory space I'll use for those FTB structures is equivalent to 20% of the size of the buffer pool configuration." Note that this is not space taken <u>away</u> from the buffer pools - it's net additional use of the z/OS LPAR's real storage by Db2. Consider an example: Db2 subsystem DB2P has 50 GB of buffer pools. If INDEX_MEMORY_CONTROL for DB2P is set to AUTO, DB2P can use up to 10 GB (20% times 50 GB) of memory for index FTBs. The size of the DB2P buffer pool configuration is not affected by FTBs - it remains at 50 GB. Got it?</span></p><p><span style="font-family: arial;">Besides AUTO, another acceptable value for INDEX_MEMORY_CONTROL is an integer between 10 and 200,000. That would set the FTB memory usage limit in terms of megabytes. Using the previous example, if the z/OS LPAR in which subsystem DB2P is running is generously configured with memory, the organization might decide to set INDEX_MEMORY_CONTROL to 20000 if they want Db2 to be able to use up to about 20 GB of memory for index FTBs, versus the 10 GB limit established via the AUTO setting (20% of the 50 GB size of the buffer pool configuration assumed for the example). If, on the other hand, the z/OS LPAR's memory resource is quite limited, the organization might opt to set INDEX_MEMORY_CONTROL to 1000, to restrict DB2P's use of memory for index FTBs to about 1 GB (I say, "about," because 1 GB is actually 1024 MB).</span></p><p><span style="font-family: arial;">INDEX_MEMORY_CONTROL can also be set to DISABLE. That has the effect of turning FTB functionality off. Why would someone disable a CPU efficiency-boosting Db2 feature? Well, relatively early on in the lifecycle of Db2 12 for z/OS (which became generally available in October of 2016), a few sites encountered some issues related to index FTB functionality. In some cases, use of an FTB was seen to cause a query to return incorrect output. These situations were pretty uncommon (recall that index FTB functionality is on by default, and most Db2 12 sites with INDEX_MEMORY_CONTROL set to AUTO encountered no problems in leveraging the technology), but they were real. Some organizations heard that other organizations had had some problems related to FTB usage, so they disabled the feature as a preemptive measure. I get it.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Why using FTB functionality makes sense now</b></span></p><p><span style="font-family: arial;">In response to the FTB-related issues mentioned above, the IBM Db2 for z/OS development team created a number of code fixes that addressed the problems reported by Db2-using organizations. These fixes and their associated APARs (</span><span style="font-family: arial;">an APAR is an official description of a software problem for which IBM commits to providing corrective service) are noted in a blog entry, written by members of the Db2 development organization, that can be viewed at </span><span style="font-family: arial;"><a href="https://community.ibm.com/community/user/datamanagement/blogs/paul-mcwilliams1/2020/10/08/new-look-ftb-db2-12"><span style="color: #2b00fe;">https://community.ibm.com/community/user/datamanagement/blogs/paul-mcwilliams1/2020/10/08/new-look-ftb-db2-12</span></a>. If INDEX_MEMORY_CONTROL is set to DISABLE at your site, and if you are using Db2 12 for z/OS, check to see if the PTFs listed in this blog entry have been applied to your Db2 code. If they have been applied (or if you are using Db2 13), you can use index FTB functionality with confidence. If you are using Db2 12 and the fixes have not been applied in your environment, my recommendation is to get them applied, perhaps as part of a roll-out of a new and more-current level of z/OS software maintenance at your site.</span></p><p><span style="font-family: arial;">The confidence that the IBM Db2 for z/OS development team has in FTB functionality, with the corrective maintenance applied, is evidenced by a couple of things. First, Db2 12 function level 508 extended FTB functionality to non-unique indexes (it had originally been limited to unique indexes). Second, Db2 13 for z/OS makes FTB functionality available for a larger set of indexes by doubling the key-length limit for FTB-qualifying indexes - from 64 bytes to 128 bytes for unique indexes, and from 56 bytes to 120 bytes for non-unique indexes (as previously mentioned, the code corrections made for Db2 12 by the FTB-related fixes listed in the above-referenced blog entry are part of the Db2 13 base code). The Db2 development team would not have made FTB functionality available for a wider range of indexes if they were anything less than highly confident in the quality of the FTB-supporting code.</span></p><p><span style="font-family: arial;">Note that if you have INDEX_MEMORY_CONTROL set to DISABLE, and you're interested in turning FTB functionality on but would like to do so in a more-controlled and more-limited way before going to a setting of AUTO, that option is available to you. As noted in the blog entry for which I provided the link, above, and in the <a href="https://www.ibm.com/docs/en/db2-for-zos/12?topic=dsodp2-index-memory-control-field-index-memory-control-subsystem-parameter"><span style="color: #2b00fe;">Db2 12</span></a> and <a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=dsodp2-index-memory-control-field-index-memory-control-subsystem-parameter"><span style="color: #2b00fe;">Db2 13</span></a> online documentation, you can tell Db2, via a specification of (SELECTED, AUTO) or (SELECTED, n) for INDEX_MEMORY_CONTROL (where n would be a user-designated limit, in MB, on the memory that Db2 can use for FTB structures), that FTB structures can be built only for indexes that you have identified as FTB candidates by way of the SYSINDEXCONTROL table in the Db2 catalog.</span></p><p><span style="font-family: arial;">In summary, if you have the FTB-solidifying fixes applied in your Db2 12 environment, or if you are running with Db2 13, and you have INDEX_MEMORY_CONTROL set to DISABLE, you should rethink that. The current FTB code is very robust, and if you don't leverage the functionality then you're leaving CPU savings on the table. I'd prefer to see you realize those CPU savings.</span></p>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com3tag:blogger.com,1999:blog-4516533711330247058.post-16197970469199004082023-02-23T15:16:00.000-08:002023-02-23T15:16:07.628-08:00Two RMF (z/OS Monitor) Reports with which Db2 for z/OS People Should be Familiar<p><span style="font-family: arial;">When it comes to analyzing the performance of a Db2 for z/OS system and associated applications, I think of a set of concentric circles. The outermost circle represents the operational environment in which Db2 is processing work - that would be the z/OS LPAR (logical partition) in which the Db2 subsystem runs. The next circle within that outer one represents the Db2 subsystem itself - its buffer pool configuration, EDM pool, RID pool, lock manager, recovery log, etc. The innermost of these concentric circles relates to the applications that access Db2-managed data. With the three circles in mind, I take an "outside-in" approach to Db2 system and application tuning. In other words, I begin with a look at the z/OS system within which Db2 is running, then I turn to the Db2 subsystem itself and lastly I analyze application-centric information. The reason for this approach? If the z/OS system in which Db2 is running is constrained in some way, there's a good chance that Db2 subsystem and application tuning actions will yield little positive impact. Similarly, if the Db2 subsystem is operating in a constrained fashion then application tuning actions may not do much good.</span></p><p><span style="font-family: arial;">So, if assessing the operation of a z/OS system is important prior to turning to Db2 subsystem and/or application performance analysis, how do you determine whether the z/OS LPAR in question is running in a constrained or an unconstrained way? I do this based on examination of information in two RMF reports: the Summary report and the CPU Activity report. If you support a Db2 for z/OS system, you should be familiar with the content of these reports - in particular, some key metrics provided by the reports. In this blog entry I'll point out those key metrics and explain how I use them.</span></p><p><span style="font-family: arial;">[Note: I am referring to reports generated by IBM's RMF z/OS monitor because RMF is the z/OS monitor with which I am most familiar. If your organization uses another vendor's z/OS monitor, that monitor might be able to generate reports similar to the RMF reports that are the subject of this blog entry - if need be, check with the vendor on that.]</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>The RMF CPU Activity report</b></span></p><p><span style="font-family: arial;">RMF, by default (you can change this), carves the time period covered by a CPU Activity report into 15-minute intervals (so, if you requested a report for a one-hour time period you will see within that report four sub-reports, each providing information for a 15-minute part of the overall one-hour time period). Within a given 15-minute interval you will see, for the z/OS LPAR for which the report was requested, information that looks something like this (I highlighted two important values in <span style="color: red;"><b>red</b></span>):</span></p><span style="font-family: courier;"><br />---CPU--- ---------------- TIME % ----------------<br />NUM TYPE ONLINE LPAR BUSY MVS BUSY PARKED<br /> 0 CP 100.00 87.03 86.85 0.00<br /> 1 CP 100.00 77.76 77.68 0.00<br /> 2 CP 100.00 83.88 83.78 0.00<br /> 3 CP 100.00 87.07 86.91 0.00<br /> 4 CP 100.00 76.23 76.14 0.00<br /> 5 CP 100.00 76.79 76.71 0.00<br /> 6 CP 100.00 80.45 80.35 0.00<br /> 7 CP 100.00 73.29 73.24 0.00<br /> 8 CP 100.00 63.83 69.22 0.00<br /> 9 CP 100.00 57.78 62.95 0.00<br /> A CP 100.00 35.28 48.33 17.01<br />TOTAL/AVERAGE <span>72.67</span> <span style="color: red;"><b>75.16</b></span><br />12 IIP 100.00 66.63 58.68 0.00<br /> 46.30 0.00<br />13 IIP 100.00 26.70 23.42 0.00<br /> 18.24 0.00<br />14 IIP 100.00 9.21 8.07 0.00<br /> 6.42 0.00<br />3E IIP 100.00 0.00 ----- 100.00<br /> ----- 100.00<br />TOTAL/AVERAGE <span>25.64</span> <span style="color: red;"><b>26.86</b></span></span><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: arial;">Here is an explanation of what you see in the report snippet above:</span></div><div><ul style="text-align: left;"><li><span style="font-family: arial;">NUM - This is the ID of a given "engine" (processor core) configured for the LPAR.</span></li><li><span style="font-family: arial;">TYPE - CP is short for central processor (typically referred to as a "general-purpose engine"); IIP is short for integrated information processor (typically referred to as a "zIIP engine").</span></li><li><span style="font-family: arial;">LPAR BUSY - Engine utilization from the LPAR perspective.</span></li><li><span style="font-family: arial;">MVS BUSY - I think of this as the busy-ness of the physical engine - if the engine is used exclusively (or almost exclusively) by the LPAR in question, the LPAR busy and MVS busy numbers should be very similar.</span></li><li><span style="font-family: arial;">PARKED - This is the extent to which an engine's capacity is NOT available to the LPAR during the 15-minute interval (so, if the engine is seen to be 75% parked then the LPAR has access to 25% of that engine's processing capacity). When engines in a mainframe "box" (sometimes called a CEC - short for central electronic complex) are shared between several LPAR's it's not unusual to see a non-zero parked value for at least some of an LPAR's engines).</span></li></ul><span style="font-family: arial;">Note that for this LPAR, there are two MVS BUSY values for each zIIP engine. Why is that? Well, it indicates that the zIIP engines are running in SMT2 mode. SMT2 is short for simultaneous multi-threading 2, with the "2" meaning that z/OS can dispatch two pieces of work simultaneously to the one zIIP core. Running a zIIP engine in SMT2 mode does not double the engine's capacity (each of the two pieces of work dispatched to the one zIIP core will not run as fast as would be the case if the zIIP engine were running in "uni-thread" mode), but for a transactional workload SMT2 can enable a zIIP engine to deliver around 25-40% more throughput versus uni-thread mode (think of a one-way, single-lane road with a speed limit of 60 miles per hour versus a one-way, 2-lane road with a speed limit of 45 miles per hour - the latter will get more cars from A to B in a given time period if there's enough traffic to take advantage of the two lanes). For more information on SMT2 mode for zIIPs, see <a href="http://robertsdb2blog.blogspot.com/2018/02/db2-for-zos-ddf-ziip-engines-and-smt2.html"><span style="color: #2b00fe;">the entry on that topic</span></a> that I posted to this blog a few years ago.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">OK, to the values highlighted in <span style="color: red;">red</span> in the report snippet:</span></div><div><ul style="text-align: left;"><li><span style="font-family: arial;">TOTAL/AVERAGE MVS BUSY for the general-purpose engines (75.16 in the report snippet) - As a general rule, application performance - especially for transactional applications (e.g., CICS-Db2, IMS TM-Db2, Db2 DDF) - will be optimal if average MVS busy for an LPAR's general-purpose engines does not exceed 80%. When average MVS busy for the general-purpose engines exceeds 90%, you can see a negative impact on the performance of Db2-accessing applications in the form of what is labeled "not accounted for" time in a Db2 monitor-generated accounting long report or an online monitor display of Db2 thread detail information. Not-accounted-for time is in-Db2 elapsed time that is not CPU time and not one of the "known" Db2 wait times (those are so-called class 3 wait times, such as wait for database synchronous read, wait for lock/latch, wait for update/commit processing, etc.). It's literally elapsed time, related to SQL statement execution, for which Db2 cannot account. In my experience, in-Db2 not-accounted-for time is most often a reflection of wait-for-dispatch time, which itself is indicative of CPU contention. I'm generally not too concerned about not-accounted-for time as long as it's less than 10% of in-Db2 elapsed time for an application workload - particularly when it's a higher-priority transactional workload (you might tolerate a higher percentage of not-accounted-for time for a lower-priority batch workload). If not-accounted-for time exceeds 10% of in-Db2 elapsed time (again, especially for a higher-priority transactional workload), that would be a matter of concern for me, indicating that CPU contention is negatively impacting application throughput.</span></li><li><span style="font-family: arial;">TOTAL/AVERAGE MVS BUSY for the zIIP engines (26.86 in the report snippet) - How "hot" can you run zIIP engines before zIIP engine contention becomes a concern? That depends on how many zIIP engines the LPAR has (and, to a lesser extent, whether or not the zIIPs are running in SMT2 mode). The more zIIP engines an LPAR has, the higher the average MVS busy figure for those engines can go before zIIP contention becomes an issue (in the example shown above, the LPAR has three zIIP engines that are running in SMT2 mode - in such a situation average MVS busy for the zIIP engines could probably go to 40-50% without zIIP contention becoming an issue). And when does zIIP contention become an issue? When the zIIP spill-over percentage gets too high, as explained in <a href="http://robertsdb2blog.blogspot.com/2014/09/db2-for-zos-avoiding-ziip-engine.html"><span style="color: #2b00fe;">an entry I posted a few years ago</span></a> to this blog. [Note: the report snippet shows four zIIP engines, but the fourth - the one identified as processor number 3E - is 100% parked from the LPAR's perspective. That means the LPAR had no access to zIIP processor 3E's capacity, so in effect the LPAR had three zIIP engines during the time interval.]</span></li></ul><span style="font-family: arial;">Below the information shown in the report snippet above, you'll see a sideways bar chart that looks something like this (again, I've highlighted some key information in <b><span style="color: red;">red</span></b>):</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: courier;"><div style="font-size: small;"><br /></div><div>-----------------------DISTRIBUTION OF IN-READY WORK UNIT QUEUE-</div><div> NUMBER OF 0 10 20 30 40 50 60 70 </div><div> WORK UNITS (%) |....|....|....|....|....|....|....|....</div><div><br /></div><div><b><span style="color: red;"><= N 55.9</span></b> >>>>>>>>>>>>>>>>>>>>>>>>>>>></div><div> = N + 1 3.5 >></div><div> = N + 2 3.1 >></div><div> = N + 3 3.5 >></div><div><= N + 5 5.5 >>></div><div><= N + 10 10.9 >>>>>></div><div><= N + 15 5.7 >>></div><div><= N + 20 4.2 >>></div><div><= N + 30 3.1 >></div><div><= N + 40 1.5 ></div><div><= N + 60 1.3 ></div><div><= N + 80 0.4 ></div><div><= N + 100 0.2 ></div><div><= N + 120 0.1 ></div><div><= N + 150 0.2 ></div><div>> N + 150 0.2 ></div><div><br /></div><div><b><span style="color: red;">N = NUMBER OF PROCESSORS ONLINE UNPARKED (16.8 ON AVG)</span></b></div><div style="font-size: small;"><b><span style="color: red;"><br /></span></b></div></span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">With regard to the report snippet above, the first thing to which I want to draw your attention is the bottom-line information about "N". We see that, for this LPAR during this 15-minute interval, N = 16.8. What does that mean? It means that the LPAR had "16.8 processor targets to which pieces of work could be dispatched." Why do I use the phrase "processor targets" instead of "processors?" It's because we tend to think of "mainframe processors" as meaning "mainframe engines," and that's not quite the case here. This report snippet goes with the first one we looked at (the second snippet appears right after the first one in the source RMF CPU Activity report), and you might recall that the first snippet showed that the LPAR's three zIIP engines are running in SMT2 mode. For that reason, those <u>three</u> zIIP engines are counted as <u>six</u> processor targets to which pieces of work can be dispatched. Thus, when the report shows that N = 16.8, we can say that 6 of the 16.8 relate to the LPAR's zIIP engines. That leaves 10.8 (16.8 - 6 = 10.8). We've accounted for the zIIP engines, so the 10.8 number relates to general-purpose engines. Where does that 10.8 come from? Refer again to the first report snippet. You'll see that the LPAR had 10 general-purpose processors that were not at all parked (i.e. that were 0% parked from the LPAR's perspective). An 11th general-purpose engine, identified as processor number A, was 17.01% parked during the interval. That means that 83% of the capacity of general-purpose processor number A (that's a hexadecimal A) was available to the LPAR during the time interval. That 83% is equivalent to 0.83, and RMF rounds 0.83 down to 0.8, and that's where the ".8" of N = 16.8 comes from. So, then, the LPAR had 6 zIIP "targets" to which work could be dispatched (3 engines, each running in SMT2 mode), and 10.8 general-purpose targets to which work could be dispatched, and that's why we have N = 16.8.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">With N now understood, we can turn our attention to the other bit of information I highlighted in red: <span style="color: red;"><= N 55.9</span>. What does that mean? It means that for 55.9% of the time in the 15-minute report interval, the number of "in and ready" tasks (i.e., the number of tasks ready for dispatch) was less than or equal to the number of processor targets to which pieces of work in the LPAR could be dispatched. When that is true - when the number of in-and-ready tasks is <= N - there is essentially nothing in the way of CPU constraint, because an in-and-read task won't have to wait in line to get dispatched to a processor. In my experience, when the <= N figure is above 80%, the LPAR is very unconstrained in terms of processing capacity. A figure between 50% and 80% is indicative of what I'd call moderate CPU constraint, and performance (particularly in terms of throughput) is likely not impacted much by a lack of processing capacity. When the figure is below 50%, I'd say that CPU constraint could be impacting throughput in a noticeable way, and if it's below 10% the performance impact of CPU constraint for the LPAR could be severe. As previously mentioned, the Db2 performance impact of a CPU-constrained system is typically apparent in elevated levels of in-Db2 not-accounted-for time, as seen in a Db2 monitor-generated accounting long report or an online monitor display of Db2 thread detail information.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">One more thing about an RMF CPU Activity report: the number of engines configured for an LPAR - something that is shown in the report - should be balanced by an adequate amount of memory (aka real storage) so that the LPAR's processing power can be fully exploited to maximize application performance. For a z/OS LPAR in which a production Db2 workload runs, my rule of thumb, based on years of analyzing system and application performance data, is this: the LPAR should have at least 20 GB of memory per engine with which it is configured. The first report snippet included above shows that the LPAR has 13.8 engines: 10.8 general-purpose engines (as previously mentioned, the ".8" relates to an engine that is about 20% parked from the LPAR's perspective) and 3 zIIP engines (and for balanced-configuration purposes, I count physical zIIP cores - I don't double-count a zIIP engine because it is running in SMT2 mode). I'd round the 13.8 to 14 (the nearest integer) and say that on that basis the LPAR should have at least 14 X 20 GB = 280 GB of memory. If that seems like a lot to you, it shouldn't - mainframe memory sizes are getting bigger all the time, and real storage resources in the hundreds of GB are no longer unusual for production z/OS LPARs, especially those in which Db2 workloads run (the biggest real storage size I've personally seen for a z/OS LPAR is about 1100 GB).</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><b>The RMF Summary report</b></span></div><div><span style="font-family: arial;"><b><br /></b></span></div><div><span style="font-family: arial;">An RMF Summary report is smaller in size than a CPU Activity report - typically, one line of information for each 15 minute interval within the report time period. An RMF Summary report for a one-hour period could look something like what you see below (I removed some columns so that I could use a big-enough-to-read font size - the really important column is the one on the far right, with the numbers highlighted in <span style="color: #40bc13;">green</span>):</span></div><div><br /></div><div><div><span style="font-family: courier;"> </span></div><div><span style="font-family: courier;">NUMBER OF INTERVALS 4 TOTAL LENGTH OF INTERVALS 00.59.58</span></div><div><span style="font-family: courier;">-DATE TIME INT ... JOB JOB TSO TSO STC ... SWAP DEMAND</span></div><div><span style="font-family: courier;"> MM/DD HH.MM.SS MM.SS ... MAX AVE MAX AVE MAX ... RATE PAGING</span></div><div><span style="font-family: courier;"> 11/03 09.15.00 15.00 ... 83 72 96 92 371 ... 0.00 <span style="color: #40bc13;"><b>0.00</b></span></span></div><div><span style="font-family: courier;"> 11/03 09.30.00 14.59 ... 85 68 98 95 369 ... 0.00 <span style="color: #40bc13;"><b>0.00</b></span></span></div><div><span style="font-family: courier;"> 11/03 09.45.00 15.00 ... 75 68 95 92 363 ... 0.00 <span style="color: #40bc13;"><b>0.00</b></span></span></div><div><span style="font-family: courier;"> 11/03 10.00.00 14.59 ... 82 70 94 91 365 ... 0.00 <span style="color: #40bc13;"><b>0.00</b></span></span></div><div><span style="font-family: courier;">-TOTAL/AVERAGE ... 85 69 98 93 371 ... 0.00 <span style="color: #40bc13;"><b>0.00</b></span></span></div></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">So, what's the LPAR's demand paging rate? It's the rate, per second, at which pages that had been moved by z/OS from real to auxiliary storage (to make room for other pages that needed to be brought into real storage) were brought back into real storage on-demand (i.e., because some process needs to access the page). Why is the demand paging rate important? Here's why: it is, in my opinion, the best indicator of whether or not memory usage can be expanded without putting too much pressure on the LPAR's real storage resource. Here's what I mean by that: suppose you have a Db2 buffer pool that has a total read I/O rate (synchronous reads + sequential prefetch reads + list prefetch reads + dynamic prefetch reads, per second) that's higher than you like - maybe the total read I/O rate for the pool is north of 1000 per second, and you want to bring that down substantially to boost application performance and CPU efficiency (every read I/O eliminated saves CPU and elapsed time). The best way to lower a buffer pool's total read I/O rate is to make the pool larger. Can you do that without putting too much pressure on the LPAR's real storage resource? Here's what I'd say: If the LPAR's demand paging rate is consistently less than 1 per second, you have a green light for using more memory to get a performance boost. If the LPAR's demand paging rate is consistently greater than 1 per second, I'd hold off on using more memory until the LPAR is configured with additional real storage. This goes for any action that would increase memory usage by DB2 - besides enlarging a buffer pool, that could be a RID pool or a sort pool or an EDM pool size increase, or increasing the use of RELEASE(DEALLOCATE) packages with threads that persist through commits, or whatever. Before doing something that will increase memory usage, check the LPAR's demand paging rate.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">That's it for now. If you haven't had a look at these two RMF reports before, get them for an LPAR of interest to you and give them a look-see - a z/OS systems programmer at your site would probably be happy to generate the reports for you. Knowing the key utilization and configuration characteristics of the z/OS LPAR in which a Db2 subsystem runs is an important part of effective performance management of the Db2 environment.</span></div>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com8tag:blogger.com,1999:blog-4516533711330247058.post-27515741955774096382023-01-20T11:41:00.000-08:002023-01-20T11:41:28.952-08:00Db2 for z/OS: What is "Wait for Other Read" Time, and What Can You Do About It?<p><span style="font-family: arial;">A recent conversation I had with some folks who support a large Db2 for z/OS system reminded me of the importance of something called "wait for other read time." In this blog entry I want to make clear to people what Db2 wait-for-other-read time is, why it's important, how to monitor it and what to do about it if it becomes an issue.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>What is Db2 for z/OS "wait for other read" time?</b></span></p><p><span style="font-family: arial;">In Db2 performance monitoring parlance, time associated with SQL statement execution is known as "in-Db2" time. It's also called "class 2" time, because it is recorded, for monitoring purposes, in records that are generated when Db2 accounting trace class 2 is active. Class 2 elapsed time (elapsed time pertaining to SQL statement execution) has two main components: CPU time (some of which is consumed on so-called general-purpose processors - aka "engines" - of a mainframe server, and some of which might be consumed on what are known as zIIP engines) and suspend time (on a busy system there can be another component of in-Db2 time, called "not accounted for" time, that generally reflects wait-for-dispatch time). In-Db2 suspend time is also known as "class 3" time, because it is recorded in Db2 accounting trace records when accounting trace class 3 is active. Class 3 time is broken out in a number of categories, and these show up in an accounting long report that might be generated by your Db2 monitor, or by a Db2 monitor's online display of thread detail information.</span></p><p><span style="font-family: arial;">In a Db2 monitor-generated accounting long report, class 3 suspend times are shown as "average" values. Average per what? Well, if you're looking at information for a Db2-accessing batch workload (referring to jobs that run in z/OS JES initiator address spaces and access Db2 by way of Db2's call attachment facility or TSO attachment facility), it'll be average per batch job (generally speaking, activity for one batch job will be recorded in one Db2 accounting trace record). If you're looking at a transactional workload (e.g., a CICS-Db2 workload, or a Db2-accessing IMS transactional workload, or a DDF client-server workload), the "average" values seen in a Db2 monitor-generated accounting long report will typically be average per transaction.</span></p><p><span style="font-family: arial;">In many cases, the majority of in-Db2 time for a batch or a transactional workload will be class 3 suspend time (it is a little unusual, but certainly not unheard of, for a Db2 workload's in-Db2 time to be mostly CPU time). More often than not, the largest component of in-Db2 class 3 suspension time will be wait-for-synchronous-database-read time. Another wait-for-read time is labeled "wait for other read." What's that? Well, if it's "other than" synchronous read wait time, it must be asynchronous read time, right? Right, indeed. And what are asynchronous reads? Those are prefetch reads: read I/Os driven by Db2 <i>in anticipation</i> that the pages read into memory in bulk in this way <i>will be</i> requested by the process (such as an application process) that prompted Db2 to issue the prefetch read requests. Well, if a prefetch read I/O operation is executed because Db2 is aiming to get pages into a buffer pool in memory <u>before</u> they are requested by (for example) an application process, why would there be such a thing as a process having to <u>wait</u> for a prefetch read to complete?</span></p><p><span style="font-family: arial;">Wait-for-prefetch read (reported as "wait for other read") happens because there are usually lots of Db2-accessing processes active in a system at one time. Let's call two of these processes process A and process B, and let's say that Db2 is driving prefetch reads (these could be sequential, list or dynamic prefetch reads - more on that in a moment) for process A. We'll further suppose that Db2 needs to access page 123 of table space TS1 on behalf of process B (i.e., Db2 issues a GETPAGE request for page 123 of table space TS1). If page 123 of table space TS1 is not already in the buffer pool to which TS1 is assigned, Db2 will drive a synchronous read request to get that page into memory, right? <i>Not necessarily.</i> It could be that page 123 of TS1 <i>is already scheduled to be brought into memory via a prefetch read that is being executed on behalf of process A.</i> If that is the case then process B will wait for that in-flight prefetch read to complete, and that wait time will be recorded as "wait for other read time" for process B. [It is also possible that process A has gotten to the point that it needs to access page 123 of TS1, and the prefetch read that will bring that page into memory is currently in-flight, and that would end up causing wait-for-other-read time for process A related to the prefetch request being driven on behalf of process A, but I think it's more likely that wait-for-other-read time will be associated with one process waiting on completion of a prefetch read operation that is being executed on behalf of another process.]</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Why is wait-for-other-read time important?</b></span></p><p><span style="font-family: arial;">Usually, wait-for-other-read time is a relatively small percentage of total class 3 suspend time for a process (it's typically much smaller than wait-for-synchronous-read time), but that's not always the case. In some situations, wait-for-other-read time is a major component of overall in-Db2 suspend time. The performance impact of elevated wait-for-other-read time can be especially significant for batch applications, as these Db2 processes are often particularly reliant on prefetch to achieve elapsed time objectives. If wait-for-other-read time gets too large then service levels could degrade, leading to user dissatisfaction.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>How can wait-for-other-read time be monitored?</b></span></p><p><span style="font-family: arial;">As mentioned previously, wait-for-other read time is recorded in accounting long (i.e., accounting detail) reports that can be generated by a Db2 performance monitor; so, you can track that for a process or a workload over time and note trends. Besides wait-for-other-read time itself, are there any other related fields in Db2 monitor-generated reports that you should keep your eye on to help ensure that a wait-for-other-read time problem does not sneak up on you? Yes, as explained below.</span></p><p><span style="font-family: arial;">The "other related fields" that I'd recommend checking out are found in a Db2 monitor-generated statistics long report (i.e., statistics detail report). In such a report you would see, </span><span style="font-family: arial;">for each buffer pool,</span><span style="font-family: arial;"> a set of fields like those shown below (this is a snippet of a statistics long report generated by the IBM OMEGAMON for Db2 for z/OS performance monitor - I've added some A, B, C labels that I'll subsequently use in referencing various of these fields):</span></p><span style="font-family: courier;"><br /><b><span style="color: #8e1d92;">BP1 READ OPERATIONS QUANTITY /SECOND</span><br /><span style="color: #8e1d92;">--------------------------- -------- -------</span><br /><br /><span style="color: #8e1d92;">SEQUENTIAL PREFETCH REQUEST 5622.00 3.23 </span><span style="color: #249c2c;">A</span><br /><span style="color: #8e1d92;">SEQUENTIAL PREFETCH READS 5587.00 3.21 </span><span style="color: #249c2c;">B</span><br /><span style="color: #8e1d92;">PAGES READ VIA SEQ.PREFETCH 52950.00 30.43 </span><span style="color: #249c2c;">C</span><br /><span style="color: #8e1d92;">S.PRF.PAGES READ/S.PRF.READ 9.48 </span><span style="color: #249c2c;">D</span><br /><span style="color: #8e1d92;">LIST PREFETCH REQUESTS 47394.00 27.24 </span><span style="color: #249c2c;">E</span><br /><span style="color: #8e1d92;">LIST PREFETCH READS 5876.00 3.38 </span><span style="color: #249c2c;">F</span><br /><span style="color: #8e1d92;">PAGES READ VIA LIST PREFTCH 154.9K 89.03 </span><span style="color: #249c2c;">G</span><br /><span style="color: #8e1d92;">L.PRF.PAGES READ/L.PRF.READ 26.36 </span><span style="color: #249c2c;">H</span><br /><span style="color: #8e1d92;">DYNAMIC PREFETCH REQUESTED 378.3K 217.42 </span><span style="color: #249c2c;">I</span><br /><span style="color: #8e1d92;">DYNAMIC PREFETCH READS 157.6K 90.59 </span><span style="color: #249c2c;">J</span><br /><span style="color: #8e1d92;">PAGES READ VIA DYN.PREFETCH 3110.6K 1787.68 </span><span style="color: #249c2c;">K</span><br /><span style="color: #8e1d92;">D.PRF.PAGES READ/D.PRF.READ 19.73 </span><span style="color: #249c2c;">L</span></b></span><div><span style="font-family: courier;"><br /></span><p><span style="font-family: arial;">By way of explanation, I'll first point out that what you see above are three repeating sets of fields (4 fields in each set) that pertain to sequential, list and dynamic prefetch activity. Here are thumbnail definitions of these prefetch types:</span></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;"><i>Sequential</i> - Generally speaking, this is the prefetch mode used for table space scans or for non-matching index scans. In other words, if Db2 determines that a front-to-back scan of a table space or index will be required, sequential prefetch will be used (assuming that the table or index in question is not super-small, in which case prefetch of any kind would usually not make sense).</span></li><li><span style="font-family: arial;"><i>List</i> - This is the prefetch type used when Db2 is retrieving table rows based on a list of row IDs (RIDs) that have been retrieved from an index (or from more than one index, if index ANDing or index ORing is part of the access plan for the query). List prefetch can be efficient if the clustering sequence of rows in the target table is substantially uncorrelated with respect to the order of entries in the index in question (the list of RIDs obtained from the index is sorted in ascending RID sequence and then the sorted RID list is used to prefetch pages of associated rows from the target table). The hybrid method of joining tables is another driver of list prefetch activity.</span></li><li><span style="font-family: arial;"><i>Dynamic</i> - This prefetch method is dynamically initiated at statement execution time when Db2 recognizes a sequential pattern of data access as it retrieves rows. Matching index scans are often drivers of dynamic prefetch activity.</span></li></ul><span style="font-family: arial;">OK, so here are a couple of things to keep an eye on, if you want to avoid a surprise situation involving elevated levels of wait-for-other-read time for processes that use prefetch to access pages of objects assigned to a given buffer pool:</span><p></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;"><i>Prefetch reads relative to prefetch requests</i> - This tends to be more important for list and dynamic prefetch (less so for sequential prefetch, owing to locality of reference being less of a factor in that case). For list and dynamic prefetch, then, compare the number of prefetch reads to the number of prefetch requests (i.e., compare F to E, and J to I, using the letter-labels I added to the statistics report snippet shown above). What's this about? Well, a prefetch request is just that - a request to read a certain chunk of pages from a table space or an index into the assigned buffer pool. Suppose the prefetch request is for 32 pages (the most common quantity), and suppose that all 32 of those pages are already in the buffer pool. <i>In that case, the prefetch request will <u>not</u> drive a prefetch read I/O operation.</i> The larger the number of buffers allocated for a pool, the greater the likelihood that all pages associated with a prefetch request will already be in memory, thereby reducing prefetch reads as a percentage of prefetch requests. If you see the percentage of prefetch reads relative to prefetch requests going up over time for a pool, especially for list and/or dynamic prefetch, that's an indication that elevated levels of wait-for-other-read time could be in the offing. Why? Because more prefetch reads will generally mean more waiting for prefetch reads to complete.</span></li><li><span style="font-family: arial;"><i>The number of pages read per prefetch read</i> - These are the fields labeled D, H and L in the example statistics report snippet. If you see that number going up for one or more prefetch types (sequential, list, dynamic), it could be an early-warning sign of higher wait-for-other-read times. Why? Because a prefetch read that will bring 25 pages into memory is likely to take longer than a prefetch read that will bring 5 pages into memory (recall that a prefetch read I/O is driven to bring into memory the pages, associated with a prefetch request, that are not already in the buffer pool). When prefetch reads take longer to complete, it is likely that application processes will see higher levels of wait-for-other-read time.</span></li></ul><span style="font-family: arial;">At this point you may have put two and two together, and are thinking, "Hmm. It seems to me that a growing number of prefetch reads relative to prefetch requests <u>combined with</u> an increase in the number of pages read into memory per prefetch read would <u>really</u> be a flashing yellow light with respect to wait-for-other-read time." Right you are. In that case, two things are happening, and both have negative implications for wait-for-other-read time: there are more prefetch reads (because there are fewer cases in which all pages associated with a prefetch request are already in memory) <u>and</u> each prefetch read, on average, is taking longer to complete (because each read, on average, is brining more pages into memory). If too much of that goes on, you could "hit the curve of the hockey stick" and see a sharp and sudden increase in applications' wait-for-other-read times. Better to take a corrective action before that happens. But what?</span><p></p><p><span style="font-family: arial;">Glad you asked...</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>What can you do to reduce (or head off an increase in) wait-for-other-read time?</b></span></p><p><span style="font-family: arial;">If wait-for-other-read time has become problematic, or if you see the warning signs and want to take a preemptive action, what can you do? Here are some possibilities:</span></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;"><i>Increase the size of the buffer pool in question</i> - Simple: more buffers in a pool leads to increased page residency time, and that leads to 1) more prefetch requests NOT leading to prefetch reads (because all pages associated with a request are already in memory) and 2) fewer pages, on average, per prefetch read. Fewer prefetch reads + quicker execution of prefetch reads that do occur = less wait-for-other-read time. Obvious related question: "<u>Can</u> I make a buffer pool bigger? I don't want to put too much pressure on the z/OS LPAR's real storage resource." My response: check the LPAR's demand paging rate (available via an RMF Summary Report for the LPAR). If the demand paging rate is zero or a very small non-zero value (i.e., less than 1 per second), there is little to no pressure on the real storage resource, and you have a green light for making the buffer pool bigger. If the demand paging rate is 2-3 per second or more, and you don't want it to go higher than that (I wouldn't want it to go higher than that), consider reducing the size of a buffer pool that has low GETPAGE-per-second and read-I/O-per-second values, and increase the size of the buffer pool of concern by a like amount (so, the overall size of the buffer pool configuration remains the same). In my experience, plenty of Db2 for z/OS-using organization under-utilize the real storage resources of production z/OS LPARs.</span></li><li><span style="font-family: arial;"><i>Change some query access plans</i> - If it looks as though sequential prefetch reads are the primary contributor to higher wait-for-other read times, you can consider taking actions that would reduce table space scan and/or non-matching index scan activity. For that, you could potentially use a query monitor to identify longer-running queries that access objects assigned to the buffer pool in question, and examine EXPLAIN output for those queries to see if any of them have table space scans and/or non-matching index scans in their access plans that involve objects assigned to the buffer pool. Then, consider whether it would be worth it to create a new index or indexes to eliminate such scans (there are cost factors associated with new indexes - you want the benefit to outweigh the cost), or whether simply adding a column to an existing index might reduce scan activity (there is a cost associated with that, too, but it's not as high as the cost of a new index). For dynamic prefetch, keep in mind that this is often related to matching index scans. You can sometimes reduce that activity by enabling Db2 to do more result set row filtering at the index level, and that often involves trying to increase MATCHOLS values for one or more predicates of longer-running and/or more-frequently-executed queries (referring to the name of a column in the PLAN_TABLE in which EXPLAIN output is found). Boosting MATCHCOLS can involve things such as changing an index (add a column, or change the order of columns in a key - keeping in mind that the latter change could benefit some queries and negatively impact others), or maybe re-coding some non-indexable predicates to make them index-able, or maybe adding a predicate that does not change a query's result set. For list prefetch, keep in mind that this often has to do with rows in a table being clustered in a sequence that is very different from the sequence of the index used in the list prefetch operation. You might consider whether a table's clustering key is what it should be - the clustering key of a table can always be changed, and sometimes it makes sense to do that. Also, when index ANDing is driving a lot of list prefetch activity, increasing index-level filtering can help (maybe by adding a column to an index involved in the ANDing, to increase the column-match number, or adding an index that would take the number of indexes AND-ed from n to n+1).</span></li><li><span style="font-family: arial;"><i>Take a look at RID pool activity</i> - List prefetch operations involve use of the Db2 subsystem's RID pool. If the RID pool can't be used for a RID processing operation, Db2 will fall back to a table space scan for the target table, and that can drive sequential prefetch numbers up. RID pools these days are MUCH larger than they used to be (the default RID pool size in a Db2 12 or Db2 13 system is 1 GB), so incidences of RID processing "failed (or not used) due to lack of storage" - something that is indicated in Db2 monitor-generated accounting long as well as statistics long reports - are now quite rare. What you could potentially see, however, in the RID processing block of a statistics long report is a relatively large number of occurrences of "failed due to RDS limit exceeded." What this RID-pool-not-used counter means: If Db2 is executing a query, and a RID list processing action commences, and Db2 determines that more than 25% of the RIDs in the index being accessed will be qualified by the predicate in question, Db2 will abandon the RID list processing action in favor of a table space scan. Can you do anything about this? Maybe. In the case of a static SQL statement, it is my recollection that this RDS limit value is embedded in the associated Db2 package; so, at bind time, Db2 notes the number of RIDs that would exceed 25% of the RIDs in an index that is to be used as part of a RID-list-utilizing access plan action. Why is this potentially important? Because an index could grow substantially in the months (or years) following the most recent bind or rebind of a package. What this means: if you have an application process for which there are many occurrences of "RID list processing failed - RDS limit exceeded," check how long it has been since the package (or packages) associated with the process were last bound or rebound. If it's been a long time, and if you think that relevant indexes have grown substantially since then, consider rebinding the packages - if that rebind results in new and larger "this is the RDS limit threshold for this index" values being embedded in the package, that value increase might be enough to reduce incidences of "RID list processing failed - RDS limit exceeded" for the package.</span></li></ul><span style="font-family: arial;">OK, that's what I've got on this topic. As I mentioned up front: in-Db2 wait-for-other-read time is <u>usually</u> not a matter of concern for Db2 application performance. In some cases, it can be an issue. This blog entry is aimed at helping you should such a case arise at your site (or even better, to help ensure that it <i>doesn't</i> become an issue for you).</span><p></p></div>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com5tag:blogger.com,1999:blog-4516533711330247058.post-60366672938872209692022-12-21T08:43:00.000-08:002022-12-21T08:43:43.816-08:00Db2 13 for z/OS: Setting Lock Timeout Limit and Deadlock Priority at the Application Level<p><span style="font-family: arial;">Db2 13 for z/OS, which became generally available about seven months ago, introduced two interesting features that are similar in some ways but differ in one important aspect (about which I'll comment momentarily). These new features allow an application (or, more broadly, a "process") to set its own lock timeout limit and/or its own deadlock priority. With this blog entry I aim to provide related information that will be useful for you.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Application-level lock timeout</b></span></p><p><span style="font-family: arial;">First, let's establish the need for this Db2 13 enhancement. Historically, there has been one lock timeout limit - specified via the IRLMRWT parameter in ZPARM - that applies to all processes interacting with a Db2 subsystem. While IRLMRWT is still there in a Db2 13 environment, it became apparent some time ago that "one size fits all" will often NOT be ideal when it comes to lock timeout in a Db2 system. Think about it. Suppose the value of IRLMRWT is at the default of 30 seconds for a production Db2 system at your site. You might have a developer of a Db2-accessing online application say, "What? NO! This app has a mobile front-end and users can get VERY frustrated if they have to wait more than a few seconds for a transaction to complete. It would be TERRIBLE to have a transaction sit and wait for 30 seconds to get a Db2 lock. We need the lock timeout value to be WAY lower than 30 seconds." At the same time, a developer of a long-running batch application might say, "What? NO! This job HAS to complete once it gets started or we miss SLAs and have angry customers. The job typically runs for five hours, and maybe it's been running for four hours and you want to time it out because it's been waiting for a lock for 30 seconds? 30 seconds is NOTHING as far as this job's concerned. The Db2 lock timeout value should be SUBSTANTIALLY greater than 30 seconds." Both of the developers are expressing legit concerns. How can those disparate concerns be addressed?</span></p><p><span style="font-family: arial;">They can be addressed via the new (with Db2 13) special register named CURRENT LOCK TIMEOUT (available for use when Db2 13 function level 500 has been activated). Here are some things to know about CURRENT LOCK TIMEOUT:</span></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;">The value of the special register - expressed in seconds - can be anything between -1 and 32767 (or a site-specified upper bound - see the next item in this list). A value if -1 means that the process will not be timed out if it ends up waiting for a lock - it will wait until it gets the requested lock or becomes deadlocked with some other process. A value of 0 means that the process does not want to wait at all for a lock - it wants to get an error message if a requested lock can't be obtained <u>immediately</u> (this basically makes available for application use a formerly Db2-internal mechanism known as a conditional lock request).</span></li><li><span style="font-family: arial;">If the default upper-limit value of 32767 seconds is deemed by a Db2-using organization to be too high, a different max value can be provided via the new (with Db2 13) ZPARM parameter SPREG_LOCK_TIMEOUT_MAX. If you set that value to (for example) 1800, no process will be able to set the CURRENT LOCK TIMEOUT special register to a value greater than 1800 seconds.</span></li><li><span style="font-family: arial;">If a lock timeout occurs and an application-level timeout limit was in effect for the lock requester and/or for the lock holder, that will be reflected in the information provided via the DSNT376I lock timeout message generated by Db2.</span></li><li><span style="font-family: arial;">The value of the CURRENT LOCK TIMEOUT special register can be set automatically for an application by way of the Db2 profile tables, <u>and not just for DDF-using applications</u> (more information on this is provided below).</span></li></ul><br /><p></p><div><span style="font-family: arial;"><b>Application-level deadlock priority</b></span></div><div><span style="font-family: arial;"><b><br /></b></span></div><div><span style="font-family: arial;">A deadlock, of course, happens when process A holds a lock that process B needs in order to proceed, and process B holds a lock that process A needs in order to proceed. With both processes in a not-able-to-proceed state, Db2 detects the deadlock and chooses a "winner" and a "loser." The "loser" process is rolled back, causing it to release locks it had held, and that enables the other process (the "winner") to acquire the lock for which it had been waiting.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">All well and good, except for the fact that one traditionally has been able to do little to nothing to influence Db2's choice of winner and loser in deadlock situations. That changes starting with function level 501 of Db2 13, thanks to a new built-in global variable named DEADLOCK_RESOLUTION_PRIORITY.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">Before providing some helpful (I hope) items of information about DEADLOCK_RESOLUTION_PRIORITY, let me point out a very important difference between this new feature and the previously-described CURRENT LOCK TIMEOUT: the latter is a special register, while the former is a global variable. Why is that notable? One simple reason: <i>any process can set the value of a special register, but a process must have <u>permission</u> to set the value of a global variable</i>. The rationale for making DEADLOCK_RESOLUTION_PRIORITY a global variable may already be clear to you: if a value for DEADLOCK_RESOLUTION_PRIORITY could be set by any process, one could imagine everyone setting the global variable to its maximum value ("I always want to be the winner in a deadlock situation"), and that would defeat the purpose of the new capability (as the bad guy, Syndrome, in the movie <i>The Incredibles</i> put it, "When everyone is super, no one will be"). The permission-only nature of DEADLOCK_RESOLUTION_PRIORITY means that (for example) a Db2 DBA can assign the max-priority value to a "must-complete" database administration process, and know that under almost any circumstances (exceptions noted below) the process will be the winner in case of a deadlock. The same could be done - with permission received from a DBA - for a high-priority application process.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">OK, items of related information that might be good for you to know:</span></div><div><ul style="text-align: left;"><li><span style="font-family: arial;">The maximum value for DEADLOCK_RESOLUTION_PRIORITY is 255 (the range of acceptable values for the global variable is 0-255).</span></li><li><span style="font-family: arial;">If you're a Db2 DBA, you might think, "I have a process that I think of as 'should-complete,' versus 'must-complete.' I want that process to generally be the winner in a deadlock situation, but I don't want it to get in the way of a 'must-complete' process. If 255 is a good DEADLOCK_RESOLUTION_PRIORITY value for a 'must-complete' process, what would be a reasonable priority value for a 'should-complete' process?" There is not a totally straightforward answer to that question. What you could do is this: start with some value for the 'should complete' process (maybe 150, or maybe 200, for example), and see if it ends up becoming the loser in a deadlock situation. If that happens, you can see how the priority of the "winner" process compared to the priority that you assigned to your 'should-complete' process, and potentially adjust your process's priority accordingly. How could you see the deadlock priority of a process that "beat" your process? That information is available via the IFCID 172 Db2 trace record. Activating IFCID 172 should involve very little overhead, as the trace record captures information about deadlocks, and deadlocks tend to be unusual in most Db2 systems I've seen. By the way, you should be able to use your Db2 monitor to generate a report with formatted information from IFCID 172 trace records (if you use IBM's OMEGAMON for Db2 monitor, the report to use for this purpose is called the Record Trace Report - that report can format the information in most any Db2 trace record).</span></li><li><span style="font-family: arial;">I mentioned previously that there are exceptions to the "255 always wins" rule. Even if DEADLOCK_RESOLUTION_PRIORITY has been set to 255 for a process, that process could be the loser if it gets deadlocked with a process that is changing data in a table space defined with NOT LOGGED (hard for Db2 to roll back a unit of work when there are no associated undo records in the log), or if it gets deadlocked with a rollback or an abort or a backout process.</span></li></ul><br /></div><div><span style="font-family: arial;"><b>Setting the lock timeout limit or deadlock priority automatically for an application</b></span></div><div><span style="font-family: arial;"><b><br /></b></span></div><div><span style="font-family: arial;">What if you want an application to have a certain lock timeout limit or a certain deadlock priority, but you don't want the application to have to issue a SET CURRENT LOCK TIMEOUT or a SET SYSIBMADM.DEADLOCK_RESOLUTION_PRIORITY statement in order to accomplish the objective (SYSIBMADM is the schema for user-set-able built-in Db2 global variables - SYSIBM is the schema for built-in global variables that are set by Db2)? No problem: you can get that done using the Db2 profile tables. "Yeah," you might say, "but I want to do this for a local-to-Db2 application, and the profile tables can be used to set special register or built-in global variable values only for DDF-using applications." Actually, with Db2 13 that statement is no longer entirely true. Db2 13 allows the setting of CURRENT LOCK TIMEOUT (starting with function level 500) and DEADLOCK_RESOLUTION_PRIORITY (starting with function level 501) via profile table entries for <u>local-to-Db2</u> as well as for DDF-using applications (for other special registers and built-in global variables, value-setting by way of the profile tables remains do-able only for DDF-using applications).</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">For a DDF-using application, the profile-defining specification (what you put in SYSIBM.DSN_PROFILE_TABLE) can be in a Db2 13 environment what it could be in a Db2 12 environment (the auth ID an application uses when connecting to the Db2 system is one example; the IP address of an application server is another example). For a local-to-Db2 application, the profile-defining specification can be auth ID and/or role, or collection name and/or package name, or the value of CLIENT_APPLNAME or CLIENT_USERID or CLIENT_WRKSTNNAME. The online Db2 13 for z/OS documentation provides additional details on using the profile tables to set values <a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=connections-setting-special-registers-by-using-profile-tables"><span style="color: #2b00fe;">for special registers</span></a> and <a href="https://www.ibm.com/docs/en/db2-for-zos/13?topic=mcdic-setting-built-in-global-variables-by-using-profile-tables"><span style="color: #2b00fe;">for built-in global variables</span></a>.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">And there you have it. Db2 13 provides more control - and more-granular control - over two important aspects of application execution. I hope that these new capabilities will be useful at your site.</span></div>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com6tag:blogger.com,1999:blog-4516533711330247058.post-57394199683416496252022-11-11T13:21:00.000-08:002022-11-11T13:21:05.723-08:00A Case Study: Using Db2 for z/OS Monitor Reports to Zero In on a Performance Problem <p><span style="font-family: arial;">I had an interesting exchange recently with a Db2 for z/OS systems programmer. This individual had been asked to assist in determining the cause of a performance problem impacting a Db2-accessing application. The sysprog shared with me a Db2 monitor-generated accounting report showing activity for the application process, and a Db2 monitor statistics report covering the same time period for the same Db2 subsystem. In those two reports were the clues that pointed to the source of the application performance problem, and with the probable problem source identified the associated mitigating actions were readily determined. In this blog entry, I'll take you through my analysis of the Db2 monitor-provided accounting and statistics information, and the rationale behind my recommended steps for resolving the problem. My aim is not only to shed light on a particular performance-affecting issue and related remediating moves, but also to illustrate a methodical approach for analyzing Db2 application performance issues in general.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Input for analysis</b></span></p><p><span style="font-family: arial;">I find Db2 monitor generated accounting and statistics reports to be extremely useful for analysis of application performance problems. In both cases, what you want is the detailed form of the report. In the case of IBM's OMEGAMON for Db2 performance monitor (the one with which I'm most familiar), you're talking about the ACCOUNTING REPORT - LONG and the STATISTICS REPORT - LONG (for other Db2 monitor products, these reports might have titles like, "detailed summary of accounting information" or "statistics detail report"). For the accounting report, your preference is to see activity pertaining exclusively to the application process for which the performance issue has arisen. This is usually done by using the monitor's data-filtering capabilities to include, for report-generation purposes, only the Db2 accounting records of interest (an accounting report is basically information from Db2 accounting trace records, formatted for readability). Db2 accounting trace records have all kinds of identifier fields, so you can get pretty specific. In the case about which I'm writing here, the relevant filtering criteria were the authorization ID of the application process of interest, and the FROM and TO times that bracketed the period during which the performance problem occurred.</span></p><p><span style="font-family: arial;">As for the statistics report, what you want is one that covers the same time period as the accounting report (same FROM and TO times), for the same Db2 subsystem.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Looking for clues</b></span></p><p><span style="font-family: arial;">What the Db2 sysprog had been told by the development team is that the application in question started out performing well, and then slowed down a lot (important input). The sysprog and I looked first at the accounting report, and in doing that we focused initially on the "class 3 suspension" information (this information, obtained from Db2 accounting trace class 3 records, has to do with "known" wait events, as opposed to "not accounted for" time, about which I'll comment momentarily). Why did we look there first? Because (in my experience), when a Db2-accessing process starts out performing well and then slows way down, it's often due to a substantial increase in one or more of the "wait times" captured by accounting trace class 3 (yes, an application slowdown could be related to a significant CPU time increase, but I've seen that less often than I've seen large wait time increases).</span></p><p><span style="font-family: arial;">Looking at the class 3 times for the application process, what jumped out was a very large value for the average DB2 LATCH time ("average" is average per accounting trace record, which typically equates to average per transaction or average per batch job, depending on the workload type). Here, I mean "large" in terms of average DB2 LATCH time being a large percentage of average TOTAL CLASS 3 time. Usually, DB2 LATCH time is a very small percentage of TOTAL CLASS 3 time, with "wait time" categories such as SYNCHRON DATABASE I/O and OTHER READ I/O accounting for the bulk of TOTAL CLASS 3 time. A (proportionately) really large DB2 LATCH time is usually an indicator that something's not right.</span></p><p><span style="font-family: arial;">The first thing I look at when I see unusually large DB2 LATCH time for a Db2-accessing process is the "in-Db2 not-accounted-for time" for that process. Several Db2 monitor products calculate that for you - in an IBM OMEGAMON for Db2 accounting report, the field is labeled NOTACC, and it's shown, on a sideways bar chart at the top of an accounting report, as a percentage of average in-Db2 elapsed time. If you need to calculate this figure for yourself, the denominator is average in-Db2 elapsed time (aka "class 2" elapsed time), and the numerator is average in-Db2 elapsed time minus in-Db2 CPU time (general-purpose plus zIIP, or "specialty engine," CPU time) minus TOTAL CLASS 3 time. In other words, it's the percentage of in-Db2 elapsed time that is not CPU time and not "identifiable" wait time. For a transactional application process (as was the case for the situation about which I'm writing), as a general rule you want in-Db2 not-accounted-for time to be less than 10%. If that figure is substantially greater than 10%, it's indicative of a CPU-constrained environment, and if the environment is highly CPU-constrained then DB2 LATCH time can get really large (as pointed out in <a href="http://robertsdb2blog.blogspot.com/2019/10/db2-for-zos-potential-performance.html"><span style="color: #2b00fe;">an entry I posted to this blog</span></a> a few years ago).</span></p><p><span style="font-family: arial;">Well, in this particular case the average in-Db2 not-accounted-for time for the application process was 11% of in-Db2 elapsed time - a little on the high side for a transactional process, but not high enough to explain a really large DB2 LATCH time. With that cause of elevated DB2 LATCH time pretty much eliminated, it was time to turn to the breakdown of latch wait events for different latch categories, and that's where the statistics report comes in. In a Db2 monitor-generated statistics long report, the latch suspend count information will likely look something like this (what you see below is from an OMEGAMON for Db2 statistics long report, but it is NOT from the report I reviewed with the Db2 systems programmer - we jointly viewed that report in a web meeting, and I do not have a copy of the report):</span></p><div style="text-align: left;"><span style="color: #800180; font-family: courier;">LATCH CNT /SECOND /SECOND /SECOND /SECOND<br /></span><span style="color: #800180; font-family: courier;">--------- -------- -------- -------- --------<br /></span><span style="color: #800180; font-family: courier;">LC01-LC04 0.00 0.00 0.00 0.00<br /></span><span style="font-family: courier;"><span style="color: #800180;">LC05-LC08 0.00 </span><b><span style="color: #07b931;">0.74</span></b><span style="color: #800180;"> 0.00 0.41</span><br /></span><span style="color: #800180; font-family: courier;">LC09-LC12 0.00 0.02 0.00 0.32<br /></span><span style="color: #800180; font-family: courier;">LC13-LC16 0.00 12.89 0.00 0.00<br /></span><span style="color: #800180; font-family: courier;">LC17-LC20 0.00 0.00 0.01 0.00<br /></span><span style="color: #800180; font-family: courier;">LC21-LC24 0.04 0.00 1.96 2.84<br /></span><span style="color: #800180; font-family: courier;">LC25-LC28 0.12 0.02 0.01 0.00<br /></span><span style="color: #800180; font-family: courier;">LC29-LC32 0.06 0.04 0.00 0.28<br /></span><span style="color: #800180; font-family: courier;">LC254 0.00</span></div><p><span style="font-family: arial;">What I saw in the report I reviewed in the web meeting with the Db2 sysprog (and again, that's NOT what you see above - the snippet above is provided so that you can see what the latch suspend count information looks like in a statistics report) was a particularly high value for latch class 6 suspend events (that would be in the <u>position</u> highlighted in green in the sample report snippet above). What is latch class 6? It has to do with index page split activity in a Db2 data sharing environment (by the way, a handy page for seeing the activities associated with various Db2 latch classes is <a href="https://www.ibm.com/docs/en/om-db2-pe/5.4.0?topic=blocks-latch-counters"><span style="color: #2b00fe;">this one</span></a> from the OMEGAMON for Db2 documentation).</span></p><p><span style="font-family: arial;">Let's unpack that. An index page split occurs when Db2 has to insert an entry in an index page because of an insert (or an update of an indexed column) and that page is full. In that situation, a portion of the entries in the page will be moved to what had been an empty page in the index, so that there will be room in the formerly-full page for the new entry. What does Db2 data sharing have to do with this (and in the environment about which I'm writing, Db2 is running in data sharing mode)? In a data sharing system (versus a standalone Db2 subsystem), an index page split action has a greater impact on throughput because it forces a log-write operation.</span></p><p><span style="font-family: arial;">Seeing the high level of index page split activity suggested by the numerous latch class 6 wait events, we turned again to the accounting report to see the average number of insert operations executed by the performance-impacted application. Sure enough, we saw that this was an insert-intensive process - more by the average number of rows inserted, versus the number of INSERT statements executed (the average number of rows inserted, per the accounting report, was about 100 times larger than the average number of INSERT statements executed, indicating use of block-level inserts by the application).</span></p><p><span style="font-family: arial;">The elevated count of latch class 6 suspend events (related to index page splits) and the insert-intensive nature of the process also dove-tailed with the observed "started out fine, then slowed down" behavior of the application: in all probability, when the process started there was a pretty good amount of free space in leaf pages of indexes on the table(s) into which rows were being inserted. After a while these "holes" in index leaf pages filled up, and that resulted in a large number of index page split actions to make space for new index entries, and THAT - partly due to the fact that this was a Db2 data sharing system - had a majorly negative impact on application performance (the keys of affected indexes were clearly not of the continuously-ascending variety, because index page split actions are not required for an index defined on a continuously-ascending key).</span></p><p><span style="font-family: arial;">With the source of the performance problem identified, the next matter to consider was...</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>What to do about it?</b></span></p><p><span style="font-family: arial;">The Db2 sysprog and I discussed two problem-mitigating actions - one an "absolutely do" and the other a "maybe do." The "absolutely do" step was to increase the amount of free space in indexes to accommodate new entries. That step, in turn, was comprised of two sub-steps, each of which is applicable to </span><span style="font-family: arial;">indexes defined on non-continuously-ascending keys</span><span style="font-family: arial;">. Sub-step one: increase the index's PCTFREE value. Whereas the default PCTFREE value for an index is 10, a value of 20 or 25 might make more sense for an index defined on a non-continuously-ascending key for an insert-heavy table. Sub-step two: increase the index's FREEPAGE value. The default FREEPAGE value is 0. Here's why boosting the FREEPAGE value - for example, to 5 (in which case there would be an empty index page after every 5 pages containing index entries) - can be helpful for </span><span style="font-family: arial;">an index defined on a non-continuously-ascending key for an insert-heavy table: as previously mentioned, when an index page is split a portion of that page's entries are moved to a <i>previously empty</i> page in the index. If FREEPAGE 0 (the default) is in effect, the only empty index pages <i>will be at the very end of the index</i> - potentially a long way from the page that was split. That situation creates a drag on performance through degradation of the index's organization (reflected in the LEAFDIST value for the index - or index partition, in the case of a partitioned index - in the SYSIBM.SYSINDEXPART catalog table). With a non-zero and relatively low value for FREEPAGE (meaning, an empty page following each relatively-low-number of populated pages), when an index split does occur then there should be a "nearby" empty page into which entries from the full page can be moved.</span></p><p><span style="font-family: arial;">Both PCTFREE and FREEPAGE can be changed for an index via an ALTER INDEX statement, and both take effect when the index is subsequently reorganized (or loaded). The larger PCTFREE value will reduce index page split activity between REORGs, and the non-zero FREEPAGE value will reduce the impact of page splits if they do occur.</span></p><p><span style="font-family: arial;">And what about the "maybe do" step? That would be an increase in the size of the index's pages, from the default of 4 KB to maybe the maximum of 32 KB. How can that help? Here's how: because a 32 KB index page (for example) can hold 8 times as many entries as a 4 KB page, going to 32 KB-sized pages for an index (via an ALTER INDEX statement that assigns the index to a 32K buffer pool) can potentially result in an 87.5% reduction (seven eighths) in page split activity for an index, other things being equal (e.g., same rate of inserts for the underlying table). Why is this a "maybe do" thing versus an "absolutely do" thing? Because if access to table rows through the index (e.g., for queries) is truly random in nature with respect to key values, 32 KB-sized pages could mean somewhat less-effective use of buffer pool resources versus 4 KB-sized pages. It's a matter, then, of what's more important in a particular situation: is it minimizing index page split activity, or maximizing the effectiveness of buffer pool resources for random patterns of row access by applications?</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>In conclusion...</b></span></p><p><span style="font-family: arial;">I hope that this entry has provided you with some "news you can use." Utilize Db2 monitor accounting and statistics reports to determine the source of an application performance problem, then take appropriate remedial action.</span></p><p><span style="font-family: arial;">One more thing: Db2 13 for z/OS made it a lot easier to verify that index page splits are an issue, through enhanced instrumentation (n</span><span style="font-family: arial;">ew IFCID 396, associated with statistics trace class 3, which is on by default, indicates when an index page split operation takes more than 1 second, which would be unusually long) and through the new REORGTOTALSPLITS, REORGSPLITTIME and REORGEXCSPLITS columns of the SYSIBM.SYSINDEXSPACESTATS real-time statistics table in the Db2 catalog.</span></p><div><br /></div>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com18tag:blogger.com,1999:blog-4516533711330247058.post-51797138143602424192022-10-28T13:39:00.000-07:002022-10-28T13:39:42.881-07:00Getting Ready to Migrate to Db2 13 for z/OS from Db2 12<p><span style="font-family: arial;">It could be said that preparation for migration of a Db2 12 for z/OS system to Db2 13 comes down to one thing: activate Db2 12 function level 510. That's a pretty simple-looking migration plan, but there's more to it than meets the eye - as I'll explain in this blog entry.</span></p><p><span style="font-family: arial;">First, let's consider function level 510 itself. Unique among Db2 12 function levels, 510 provides no new functionality in the traditional sense - there's nothing new there that a DBA or a developer would use, or that could make an application execute with greater efficiency. The purpose of function level 510 is simply this: to validate that a Db2 12 system is technically ready for migration to Db2 13 (here, "system" refers to a standalone Db2 subsystem or a Db2 data sharing group).</span></p><p><span style="font-family: arial;">If activating function level 510 gets a Db2 12 system ready for migration to Db2 13, what makes a Db2 12 system ready for activation of function level 510? Three things:</span></p><p></p><ol style="text-align: left;"><li><span style="font-family: arial;">The system's code level has to be 510. That can be easily verified by issuing the Db2 command -DISPLAY GROUP: in the command output, look for the value 121510 in a column labeled DB2 LVL (if your Db2 system's maintenance level is relatively current, the code level is likely to be 510 already - the <a href="https://www.ibm.com/docs/en/db2-for-zos/12?topic=d1fl-function-level-510-v12r1m510-activation-enabled-by-apar-ph33727-april-2021"><span style="color: #2b00fe;">PTF that takes a Db2 system's code to the 510 level</span></a> came out in April of 2021).</span></li><li><span style="font-family: arial;">The catalog level has to be V12R1M509 - again, -DISPLAY GROUP output tells the tale.</span></li><li><span style="font-family: arial;">There can't be any packages in the system, used within the past 18 months, that were last bound or rebound prior to Db2 11.</span></li></ol><span style="font-family: arial;">Let me expand on items 2 and 3 in that list.</span><p></p><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><b>Getting to the right catalog level</b></span></div><div><span style="font-family: arial;"><b><br /></b></span></div><div><span style="font-family: arial;">First, you might be wondering, "How is it that I need to get to catalog level V12R1M509 before I can activate function level V12R1M510? Wouldn't the catalog level have to be V12R1M510?" No. There is no catalog level V12R1M510. Why is that? Because function level 510 has no catalog dependencies (i.e., no changes to a catalog at the V12R1M509 level are necessary to support function level 510). This is not at all unprecedented. Several of the Db2 12 function levels had no catalog dependencies. For example, function level 504 can be activated when the catalog level is V12R1M503 - there is no V12R1M504 catalog level because function level 504 did not require any changes to a catalog at the 503 level.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">Second, suppose your Db2 catalog level is, say, V12R1M500. Can you take that catalog right to the 509 level? YES. Assuming your code level is at least 121509, you can execute the CATMAINT utility with a specification of UPDATE LEVEL(V12R1M509). That one execution of CATMAINT will make the changes to the catalog associated with the 509 catalog level, <u>and</u> it will make all the changes associated with all the other catalog levels between 500 and 509.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><b>About those old packages...</b></span></div><div><span style="font-family: arial;"><b><br /></b></span></div><div><span style="font-family: arial;">"What's with that?" you might wonder. "Why would the existence of one or more packages, that we've used in the past 18 months and that were last bound or rebound prior to Db2 11, keep me from being able to activate function level 510?" Short answer: it's for your own good. Longer answer: packages are executable code. The code generated for a package by a Db2 version prior to V11 <u>cannot be executed in a Db2 13 system</u>. If there is a request to execute a pre-Db2 11 package in a Db2 13 environment, that package will be auto-rebound so that Db2 13 can generate for that package code that can be executed in the Db2 13 system. "OK," you say, "I get that auto-rebinds of packages can be a little disruptive with respect to my application workload, but it's not THAT big of a deal - the auto-rebind gets done and we go trucking on." Not so fast, I'd say. What if, as a result of the auto-rebind of a package, there's an access path change that negatively impacts - perhaps majorly - the performance of the associated program? Your possible response: "Again, not a huge deal. We run with PLANMGMT=EXTENDED in ZPARM, and so we'd have the prior copy of that package available, and we can just execute a REBIND with SWITCH(PREVIOUS) to restore the previous better-performing copy of the package." <b>WRONG!</b> You CAN'T restore the previous copy of that package, because the previous copy was generated prior to Db2 11, <i>and that means the previous copy can't be executed in a Db2 13 system.</i> You're STUCK with that poor-performing package. Sure, you can take steps to try to correct the performance regression - maybe update catalog statistics or take an index action and then rebind and hope for a better performance outcome - but do you want to do that while some critically important production program is performing in an unacceptable way and you're phone is going off non-stop because upper management wants to know WHEN YOU'RE GOING TO GET THIS FIXED? Probably not; so, we're going to prevent that scenario from happening by not letting you go to Db2 13 if you still have any pre-Db2 11 packages that have been used within the past 18 months (the thinking of the Db2 for z/OS development team: if a package was last used more than 18 months ago, it's highly likely that it's a package that's just not used anymore in your environment - it's still in SYSPACKAGE simply because no one has FREE-ed the old package).</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">This "keep you out of trouble" action taken by the Db2 for z/OS development team is based on the painful experience some organizations had when they migrated to Db2 12 from Db2 11. In that situation, we made it clear in the documentation that pre-Db2 10 packages would need to be rebound prior to going to Db2 12 because pre-Db2 10 packages could not be executed in a Db2 12 environment. Well, some Db2 for z/OS people either didn't see that warning, or saw it and decided to ignore it and take their chances, and in a few cases the problem described in the preceding paragraph was encountered. At some sites, the problem's impact was severe enough to warrant falling back to Db2 11, at which point people would rebind the pre-Db2 10 packages (as had been strongly encouraged by us) and then re-migrate to Db2 12. Not wanting to see reoccurrences of those difficulties, with Db2 13 we're basically saying, "We are not going to let you get into the potentially ugly situation you could see if a package that cannot be executed in a Db2 13 system is requested for execution in that environment - you <u>cannot go to Db2 13 if you have pre-Db2 11 packages that might still be in use at your site</u>.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">By the way, if you want to see if you have any packages that would prevent successful activation of function level V12R1M510, you can execute this query on a Db2 12 system (and note that this query is also provided <a href="https://www.ibm.com/docs/en/db2-for-zos/12?topic=d1fl-function-level-510-activation-enabled-by-apar-ph33727-april-2021"><span style="color: #2b00fe;">in the Db2 12 documentation</span></a>):</span></div><div><span style="font-family: arial;"><br /></span></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div><span style="color: #800180; font-family: courier;">SELECT * FROM SYSIBM.SYSPACKAGE </span></div><div><span style="color: #800180; font-family: courier;"> WHERE LASTUSED >= DATE(DAYS(CURRENT DATE) - 548) </span></div><div><span style="color: #800180; font-family: courier;"> AND RELBOUND NOT IN ('P','Q',’R’)</span></div><div><span style="color: #800180; font-family: courier;"> AND VALID <> ‘N’ </span></div><div><span style="color: #800180; font-family: courier;"> AND OPERATIVE <> ‘N’;</span></div></blockquote><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">One more thing: as previously mentioned, it's highly likely that a package that has not been executed in a Db2 system within the past 18 months will not be executed at some future time in that Db2 environment. That said, maybe you're concerned that, for some reason, a package in your environment is executed every two years (24 months). The chances of that being true are almost certainly very small, but perhaps non-zero. If that's bugging you, disregard the "18 months" window and rebind ANY pre-Db2 11 package in your system prior to going to Db2 13.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><b>If you're nervous about a "big jump" to Db2 12 function level 510...</b></span></div><div><span style="font-family: arial;"><b><br /></b></span></div><div><span style="font-family: arial;">Consider this hypothetical situation: you're running with Db2 12 function level 500 activated and you're contemplating going to function level 510 to prepare for migration to Db2 13. That's actually not so hypothetical - a good number of Db2 12 systems are running with an activated function level of V12R1M500. If that looks familiar to you, there might be a couple of thoughts running through your mind:</span></div><div style="text-align: left;"><ul style="text-align: left;"><li><span style="font-family: arial;"><i>Function level 500 to 510 looks like a really big jump to me. How do I get that done with a minimized risk of complications?</i> The key here is the APPLCOMPAT specification for your packages. Maybe you're concerned that making a big jump up in the activated function level for your Db2 12 systems could lead to programs being impacted by a "SQL incompatibility" (basically, that's a SQL behavioral change: same SQL, same data, different result - these incompatibilities are pretty few and far between, and they often affect either few or none of your programs, but they can indeed arise on occasion). If you're indeed worried about that, you can guard against that <i>by leaving the APPLCOMPAT values for your packages where they are when you activate a higher function level of Db2 12 for z/OS. </i>If you have a package bound with, for example, APPLCOMPAT(V12R1M500), and you activate function level 510, <i>SQL issued through the package bound with APPLCOMPAT(V12R1M500) will still get the SQL behavior associated with <u>function level 500</u>.</i> You can find lots more information about APPLCOMPAT in the <a href="http://robertsdb2blog.blogspot.com/2019/06/db2-for-zos-talking-about-applcompat.html"><span style="color: #2b00fe;">part 1</span></a> and <a href="http://robertsdb2blog.blogspot.com/2019/07/db2-for-zos-talking-about-applcompat.html"><span style="color: #2b00fe;">part 2</span></a> posts of the 2-part entry on APPLCOMPAT that I added to this blog in 2019.</span></li><li><span style="font-family: arial;"><i>If function level 510 is a good ways beyond where my Db2 12 system is at present, could I maybe go from where we are to some intermediate function level, and later to level 510?</i> Of course you can. "If I decide to do that," you might be thinking, "what would a good intermediate function level be for us?" That's really up to you. My advice: go to the Db2 12 function levels <a href="https://www.ibm.com/docs/en/db2-for-zos/12?topic=12-db2-function-levels"><span style="color: #2b00fe;">"main page"</span></a> in the Db2 12 online documentation, and check out the features and functions introduced with each function level between 510 and where you are now. If there's a function level that provides an enhancement that would be particularly helpful in your environment, go to that one, and later, at a time of your choosing, go from that level to level 510 (I'll tell you that a pretty popular "intermediate" Db2 12 function level is 505 - this because a lot of Db2 DBAs really like the <a href="http://robertsdb2blog.blogspot.com/2020/12/db2-for-zos-what-do-you-know-about.html"><span style="color: #2b00fe;">"rebind phase-in"</span></a> functionality that was introduced via function level 505).</span></li></ul></div><div><span style="font-family: arial;">OK, that about wraps it up for this blog entry. I hope that this information will be helpful for you as you plan for your site's migration from Db2 12 to Db2 13.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">P.S. In addition to activating Db2 12 function level 510, don't forget to apply the "fallback SPE" to your Db2 12 systems prior to migrating to Db2 13 - that PTF allows you to fallback from Db2 13 to Db2 12, in the unlikely event that that should be deemed necessary for you. The APAR associated with this fallback SPE is <a href="https://www.ibm.com/support/pages/apar/PH37108"><span style="color: #2b00fe;">PH37108</span></a>.</span></div>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com0tag:blogger.com,1999:blog-4516533711330247058.post-77782907514641336722022-09-28T07:10:00.001-07:002022-09-28T07:10:40.779-07:00Db2 for z/OS: GETPAGEs, Predicates, SELECTs and DELETEs <p><span style="font-family: arial;">About 15 years ago, I posted to the blog I maintained while working as an independent consultant (prior to rejoining IBM in 2010) <a href="http://catterallconsulting.blogspot.com/2007/09/most-important-db2-for-zos-performance.html"><span style="color: #2b00fe;">an entry</span></a> in which I named GETPAGEs the most important factor with regard to the CPU consumption of a Db2 for z/OS application workload. GETPAGEs, which are requests by Db2 (typically on behalf of application or user processes) to access pages of table spaces or indexes, are indeed a prime driver of SQL statement execution cost. That being the case, people who work on tuning Db2 query performance often aim to reduce GETPAGE activity associated with executing the query. That work, in turn, tends to focus on a query's predicates (the clauses of the query that determine which rows will qualify for the query's result set). Can an index be added or modified to enable index-level (versus table-level) row filtering for a given predicate? Can a non-index-able predicate be rewritten to be index-able? Can a predicate be added to further refine the query's result set? And so on.</span></p><p><span style="font-family: arial;">The thing is, the predicate focus of query tuning could lead one to believe that the same GETPAGE-reducing actions could yield similar results for any SQL statement containing predicates, regardless of whether the statement is a SELECT or, say, a DELETE. That is not the case, especially for DELETEs versus queries, and the difference basically boils down to one thing: indexes. The same indexes that reduce GETPAGE activity for a query can make GETPAGE counts stubbornly high for a DELETE statement, in spite of tuning actions related to the DELETE statement's </span><span style="font-family: arial;">predicates. A Db2 SQL programmer recently ran up against this reality. He asked me about it, and I think his situation could be instructive for others.</span></p><p><span style="font-family: arial;">The SQL programmer (I'll refer to him as R - the first letter of his first name) was analyzing the performance of a row-deleting process that would remove a few hundred thousand rows from a table in a given execution. Thinking that reducing GETPAGE activity would lower the CPU cost of the process, and approaching the tuning effort for the process's DELETE statement as one would approach tuning the performance of a query (thinking about an analogous query that would have the same predicates as the DELETE statement of interest), R had a DBA create on the target table a new index with a key comprised of the five columns referenced in a series of ANDed "equals" predicates in the DELETE statement (in other words, the DELETE had the series of predicates WHERE C1 = ? AND C2 =? AND C3 = ? AND C4 = ? AND C5 = ?, and the new index had the key C1 | C2 | C3 | C4 | C5). That would make for a MATCHCOLS value of 5 for the DELETE statement, right (referring to a column in the Db2 access path-explaining <a href="https://www.ibm.com/docs/en/db2-for-zos/12?topic=tables-plan-table"><span style="color: #2b00fe;">PLAN_TABLE</span></a>)? And, that should reduce GETPAGE activity for the DELETE by enabling a greater degree of row filtering at the index level, right? Well, not exactly, as things turned out: R was surprised to see that the new index had very little impact on the GETPAGE count for the DELETE statement. What was going on here?<br /></span></p><p><span style="font-family: arial;">The index-impact result that surprised R comes down to essentially one thing, that being the fundamentally different nature of DELETE versus SELECT statements. Yes, both can have predicates, but that doesn't mean that a DELETE with predicates can be thought of as a "query" - DELETEs and SELECTs are like apples and oranges when it comes to their execution characteristics. A SELECT returns values (specifically, rows of values), and that's it. A certain index might sharply reduce GETPAGE activity for a query by reducing the need for Db2 to examine table space pages in order to find qualifying rows (in fact, an index could virtually eliminate table space GETPAGEs for a query, if all columns referenced in the query - in the query's select-list and in its predicates - are part of the index's key). A DELETE, on the other hand, <u>changes pages</u>, many of which - and this is of critical importance - will be pages in index spaces (excepting the unusual, but not unheard-of, situation in which a table has no indexes).</span></p><p><span style="font-family: arial;">Given this aspect of DELETE processing, not only will adding an index to a table potentially have little impact on GETPAGE activity for a DELETE statement - it might even <u>increase</u> GETPAGE activity for the DELETE. Think about it: for every row removed from a table by a DELETE statement, an entry has to be removed <i>from each and every index defined on the table </i>(yes, it's actually a "pseudo-delete" of index entries, with the relevant entries just marked for later physical deletion, but this still involves index GETPAGEs)<i>.</i> Not only that, but Db2 very often can't just march through an index's leaf pages, deleting index entries as corresponding rows are deleted from the underlying table - not when a given index's keys have a way-different sequence relative to the sequence in which table rows are being deleted. Maybe, because of matches on predicate columns, a DELETE statement is guided to rows qualifying for deletion by index X, but index Y, on the same table, may have a key whose ordering is very different from that of index X's (i.e., the two indexes' key values correlate little, if at all, on a row-by-row basis). In that case, finding the entry in index Y to delete as part of deleting a table row could well require an index probe operation (i.e., a top-down traversal of index Y, from root page to leaf page). If that kind of thing is happening for several indexes on the table, the number of GETPAGEs for a DELETE statement could be several times larger than the number of rows deleted; <i>and, that's not because the DELETE has a "bad" access path - it's because the statement is a DELETE and not a SELECT.</i></span></p><p><span style="font-family: arial;">Bottom line: comparing GETPAGE counts between SELECT and DELETE statements is not useful or meaningful, even if the statements have identical predicates. If you have a row-delete process that is consuming more CPU than you'd like, what can you do about it? Here are a couple of possibilities:</span></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;"><i><u>Reduce</u> - don't increase - the number of indexes on the target table</i>. As I've mentioned, it's a general truism that while indexes can boost query performance, they tend to increase CPU and elapsed time for DELETE statements. That being the case, a good way to boost DELETE performance is often to find and remove indexes on the target table that are not doing any good. I call this "index trimming," and I provided some information on that topic in <a href="http://robertsdb2blog.blogspot.com/2012/09/db2-10-for-zos-new-options-for-trimming.html"><span style="color: #2b00fe;">an entry I posted to this blog</span></a> some years ago (I'd ignore the part of that blog entry that deals with hash-organized tables - that Db2 feature was deprecated with function level 504 of Db2 12 for z/OS).</span></li><li><span style="font-family: arial;"><i>Consider using the DISCARD option for online REORG</i>. Particularly when the row-delete criterion (or criteria) is not particularly complex, and can be expressed in the form of a predicate or predicates referencing the target table, executing an online REORG of the table's table space with the DISCARD option can be an attractive way to efficiently remove a large number of rows from the table with minimal disruption of application access to the data (there will always be at least a brief - sometimes just a few seconds - period of no data access, when a REORG job enters the SWITCH phase near end-of-execution).</span></li></ul><span style="font-family: arial;">I hope that this information will be useful for you. Don't confuse DELETE-statement tuning with SELECT-statement tuning. Two different animals, as we say.</span><p></p>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com9tag:blogger.com,1999:blog-4516533711330247058.post-70921213803183840742022-08-23T16:40:00.000-07:002022-08-23T16:40:13.901-07:00What Db2 for z/OS People Should Know About Data Fabric <p><span style="font-family: arial;">"Data fabric" is an increasingly hot topic in IT circles, and with good reason - an effectively implemented data fabric can deliver significant dividends by enabling an organization to get more value from its data assets. Db2 for z/OS people should have some familiarity with the data fabric concept and associated technology, not only as preparation for participating in data fabric-related discussions but also because data fabric is of major strategic importance for Db2 for z/OS (and for other z/OS-based data sources). In this blog entry I'll provide information on data fabric that I hope will be helpful to Db2 for z/OS people.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>What is "data fabric," anyway?</b></span></p><p><span style="font-family: arial;">Essentially, data fabric is an architecture that brings uniformity and consistency to data originating in a disparate collection of sources - sources which could be (likely would be) housed in a mix of on-premise and in-cloud systems (and, especially for larger enterprises, "in cloud" would involve several different public cloud providers and perhaps some private cloud environments). That uniformity and consistency is manifest in multiple aspects of data interaction via the data fabric, including </span><span style="font-family: arial;">data access, </span><span style="font-family: arial;">discovery, utilization, </span><span style="font-family: arial;">cataloging, </span><span style="font-family: arial;">protection and governance;</span><span style="font-family: arial;"> further, a data fabric is likely to have a "smart" dimension, with </span><span style="font-family: arial;">AI and machine learning technology leveraged to provide intelligent automation of data management tasks.</span></p><p><span style="font-family: arial;">I mentioned that the data fabric payoff is increased value gained from an organization's data assets. How does data fabric deliver that payoff? Basically, by eliminating friction that would otherwise impede data access, discovery, utilization and integration - and doing that without compromising data security. The promise of a data fabric can be largely summed up in this way: it provides an environment in which <u>the right data</u> (i.e., data that is current, trusted, understood and complete) is available to <u>the right people</u> (people who know the data, people who know what data they need, people who know what they want to do with data) at <u>the right time</u> (i.e., when the data is needed).</span></p><p><span style="font-family: arial;">In thinking about the value of the consistency and uniformity that a data fabric brings to what would otherwise be a disjointed data landscape, it can be helpful to consider a cake-baking analogy. </span><span style="font-family: arial;">Suppose you are tasked with baking a cake, and suppose further that the ingredients must be ordered from different countries, and you have to communicate with suppliers using the primary language of each source country and you have to remunerate the suppliers using source-specific modes of payment. Here's how that might go (and in your mind, substitute any countries you want for the ones I mention - I'm not picking on anyone):</span></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;">The eggs for the cake are to come from Japan, but there is a delay in procurement because you don't speak Japanese.</span></li><li><span style="font-family: arial;">The butter is to come from Australia, but the supplier will only send the butter after having received payment in coins that were sent via sailboat.</span></li><li><span style="font-family: arial;">The flour will come from a supplier in Germany. Your German is a little rusty, but pretty good so there's not much of a delay there.</span></li><li><span style="font-family: arial;">The sugar is to be sourced from Brazil, but your lack of familiarity with the ingredient-ordering user interface results in your being unable to locate a supplier.</span></li><li><span style="font-family: arial;">This all leads to your getting a late start in baking the cake, and on top of that the eggs went bad while you were waiting for the butter, and you never got the sugar. The people who were looking forward to consuming your confection had to wait a frustratingly long time to get a very un-tasty cake. Not good.</span></li></ul><span style="font-family: arial;">Now imagine a different scenario, in which a cake-ingredient-ordering front end abstracts the particulars of the ingredient suppliers (such as native language) and provides uniformity for payment and shipment. Using that front end, you get the ingredients you need - and <u>all</u> the ingredients you need - in a timely manner, and your cake consumers are delighted with the product of your kitchen, which satisfied their sweet-tooth needs and arrived at the right time.</span><p></p><p><span style="font-family: arial;">So it is with a data fabric: different data elements from different data sources are the “ingredients” that provide a complete (sometimes called a “360”) view of a subject of interest - be that customers, processes, supply chains, products, whatever. And here's the upshot: when the right (and <u>all</u> the right) data ingredients get to the right people at the right time, the result is <i><b>better</b></i>: better decisions, better and more timely applications, better outcomes.</span></p><p><span style="font-family: arial;">There is technology that can make the promise of data fabric a reality, but before getting into that I want to emphasize that data fabric is NOT just a matter of leveraging technology. I'd go so far as to say...</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Data fabric is culture</b></span></p><p><span style="font-family: arial;">There were people who said the same thing a few years ago about DevOps, and for the same reason: full and effective implementation of a data fabric can require new organizational roles and new ways of thinking about and managing data. To appreciate this assertion, consider the "personas" (i.e., the people-roles) associated with individuals who would work with, and in relation to, a data fabric. That exercise is facilitated if you think of </span><span style="font-family: arial;">a data fabric as something that enables a “data store,” in which people “shop for data.” For a traditional retail store, relevant personas include the following:</span></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;">Consumers acquire products from the store.</span></li><li><span style="font-family: arial;">Suppliers provide products for the store.</span></li><li><span style="font-family: arial;">A store manager decides which products should go on which shelves.</span></li><li><span style="font-family: arial;">A sales associate puts the right products on the right shelves.</span></li></ul><span style="font-family: arial;">OK, so what are the personas that have a relationship with the "data store" enabled by a data fabric? Some are listed below.</span><p></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;">A <i>data consumer</i> might be a developer working on a new application, or a business analyst researching the viability of a proposed new product.</span></li><li><span style="font-family: arial;">A <i>database administrator</i> oversees a data source that supplies the data store.</span></li><li><span style="font-family: arial;">A <i>data curator</i> might make decisions on what data will be available through the data store, and to whom.</span></li><li><span style="font-family: arial;">A <i>data steward</i> might “stock the shelves” of the data store, based on decisions made by a data curator.</span></li></ul><span style="font-family: arial;">Look again at those last two personas in the list above - data curator and data steward. I can tell you for a fact that those roles exist today in multiple organizations - are they present in your workplace? And note: a data fabric's impact goes beyond new organizational roles - it involves new ways of thinking about data management. Here's what I mean: h</span><span style="font-family: arial;">istorically, data was often thought of in relation to where it was stored. That manner of thinking led to “silo” situations, and the difficulty of working with data in a “cross-silo” way interfered with organizations’ extracting maximum value from their data assets. By contrast, a data fabric will deliver the greatest benefit when it supports a data management approach that focuses more on <i>data itself</i>, and less on where data is stored. One implication of a data-centric (versus a data-source-centric) approach to data management is that data access decisions (i.e., who can access what data, and in what form) are made by <i>data</i> professionals (e.g., data curators), as opposed to being made by <i>database</i> professionals (e.g., DBAs). In such an environment, data <i>source</i> administrators are implementers of data access decisions made by data curators.</span><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">If a data fabric puts <i>data administration</i> (versus database administration) responsibility on data professionals (e.g., data curators), does that diminish the role of a Db2 for z/OS DBA? I would say it does not. I see this is being part of an ongoing evolution of the Db2 for z/OS DBA role to be more engaged in <i>application development </i>(for distributed systems DBAs, this role shift became widespread some years ago). This is a good thing. I am convinced (and more importantly, so are a lot of IT leaders at Db2 for z/OS sites) that the value a mainframe Db2 DBA delivers to an organization goes <u>up</u> when that DBA's work has more of an application-enabling focus.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">Let me shift now from organizational impact to enabling technology.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><b>IBM's foundational data fabric-enabling technology</b></span></div><div><span style="font-family: arial;"><b><br /></b></span></div><div><span style="font-family: arial;">Multiple IBM offerings have a connection with data fabric, but the most foundationally important of these is called Cloud Pak for Data. Cloud Pak for Data's importance has a lot to do with IBM's point of view regarding data fabric implementation. We believe that a data fabric is most effectively implemented as an abstraction layer extended over <i>an existing data landscape</i>. Such an implementation approach acknowledges the significance of “data gravity” - the idea that data usage actions should flow to the data, rather than vice versa. A data fabric enabled via Cloud Pak for Data is characterized by "in-place” access to data on systems of origin. This approach delivers multiple benefits, including:</span></div><div><ul style="text-align: left;"><li><span style="font-family: arial;">Minimization of data replication costs.</span></li><li><span style="font-family: arial;">Protection of data security and consistency.</span></li><li><span style="font-family: arial;">Optimized performance.</span></li></ul><span style="font-family: arial;">Cloud Pak for Data itself can be thought of as a set of software-powered services that relate to access, governance and usage of data. Cloud Pak for Data can be deployed anywhere Red Hat OpenShift (a Kubernetes container platform) can be deployed: on-premise, in a private cloud or in a variety of public cloud environments (it is also available in a fully managed, as-a-service form). Cloud Pak for Data can be used with a wide range of data sources on Linux, UNIX, Windows and z/OS systems, and those data sources can be on-premise and/or in-cloud.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">How would Cloud Pak for Data be used by people in an organization? Here's one scenario: let's say that Val leads a development team that will soon begin work on a new application. </span><span style="font-family: arial;">To support this work, Val’s team will need access to some data (which happens to be in a Db2 for z/OS database) and associated metadata (data about the data). Val sends a request to this effect to Steve, a data curator. Steve is very familiar with the data that the new application will process. He logs in to Cloud Pak for Data's user interface and creates a project that will provide Val’s team with the data and metadata they need. Db2 for z/OS is one of <u>many</u> data sources supported by Cloud Pak for Data, and Steve creates a connection to the relevant Db2 system. Steve selects the particular tables holding the data that the new application will process and associates them with the project he created for Val's team. Steve also imports metadata for the selected tables, and enriches that metadata with statistical values, data quality scores and business terms. Finally, Steve creates a masking rule for sensitive data in a column of one of the selected Db2 tables - Val's team will be able to reference the column in their program code, but they will only see masked values when they view the column's contents. With the project created and the associated data assets published to a catalog to which Val and her teammates have access, the developers will be able to easily view the data and the related metadata, and this will enable them to move ahead quickly and productively with coding and testing.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">The point I really want to make here is not so much, "Look what the data curator can do for the application development team." Even more important to me is the fact that, had Val's team needed access to data (and with it, associated metadata) in a Db2 for Linux/UNIX/Windows database, or a SQL Server database, or an Oracle database, or Apache Cassandra, or Amazon S3, or MariaDB or one of the myriad other data sources supported by Cloud Pak for Data, <i>the actions of the data curator would have been largely the same.</i> And, that would be the case for all kinds of other Cloud Pak for Data usage scenarios - a data scientist needing to develop and train a predictive model, a business person wanting to create a report with accompanying data visualizations, a data curator implementing new rules and policies concerning access to certain data assets, a data administrator virtualizing non-relational data to make it more easily accessible and consumable, whatever. <u>That</u>, as much as anything, is the "secret sauce" of a Cloud Pak for Data-enabled data fabric: it makes all kinds of data sources more easily accessible and effectively consumable by all kinds of people, without sacrificing data governance and security. And when more of an organization’s data assets are used more easily and effectively by more people, the organization <i>works better</i>.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><b>Data fabric is strategically really important for z/OS as a data-serving platform</b></span></div><div><span style="font-family: arial;"><b><br /></b></span></div><div><span style="font-family: arial;">The uniformity brought to a data landscape by a data fabric is of outsized importance in the context of z/OS as a data-serving platform. How so? Think about it. What gets in the way of z/OS-based data being more effectively - and more widely - used by people in an organization? Often, it's the perceived “other-ness” of the mainframe – the sense non-mainframe people have that z/OS-based data is inherently harder to access, understand and use than data on other platforms. Truth be told, that perception has, historically, been at least partly fact-based – it <u>has</u> been harder for many people to access and use z/OS-based data versus off-mainframe data. The great value, then, of an effectively implemented data fabric, from a z/OS perspective, is not so much that it makes z/OS-based data easier to access and use versus off-mainframe data; rather, it’s the fact that the data fabric makes z/OS-based data <u>as easy to access and use</u> as off-mainframe data. Why that's so powerful: while mainframe systems have been recognized for decades as being unmatched in terms of reliability, security, scalability, efficiency and performance, there have been plenty of people who would say, "Yeah, <b>but</b> mainframe-based data is hard to access and use." An effective data fabric eliminates that "yeah, but..."</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">Let that sink in: by making discovery, understanding, consumption and usage of data in z/OS systems as easy it is for data on other platforms, a data fabric makes IBM zSystems an even higher-value platform for an organization's most valuable data assets.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">If your organization has not yet looked at implementing an enterprise data fabric, now could be a good time to start down that path. And, the "in-place access to data on systems of origin" that characterizes a data fabric implemented with IBM's Cloud Pak for Data could well be the approach that will deliver maximum benefits in your environment. Give it some thought, and get engaged.</span></div>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com0tag:blogger.com,1999:blog-4516533711330247058.post-56672925263613356032022-07-26T19:29:00.000-07:002022-07-26T19:29:33.260-07:00What Should a Modern Db2 for z/OS Client-Server Application Environment Look Like?<p><span style="font-family: arial;">The distributed data facility (aka DDF) is the component of Db2 for z/OS that enables data access by applications that connect to Db2 via TCP/IP communication links. DDF has been around for over 30 years, but especially during the past 10 years or so DDF workloads have become very large at many Db2 for z/OS sites, with individual Db2 subsystems processing DDF transactions at sustained rates of over 4000 per second (and many more than that for Db2 data sharing groups running on Parallel Sysplex clusters of mainframe servers). For an ever-larger number of Db2-using organizations, the DDF workload is the largest - and the fastest-growing - component of the overall Db2 workload. Given the importance of DDF in the Db2 workload mix, it's worthwhile to consider what a modern Db2 client-server application environment should look like. In looking over the Db2 DDF scene in recent years I've seen a lot of things that I like. In this blog entry I'll share Db2 DDF application environment characteristics that get a thumbs-up from me.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Straight from the app servers to Db2 (for DRDA requesters)</b></span></p><p><span style="font-family: arial;">For a number of years, organizations have been migrating away from the use of Db2 Connect "gateway servers" (application server to Db2 Connect gateway server, Db2 Connect gateway server to Db2 for z/OS system) in favor of direct connections from application servers to Db2 for z/OS systems by way of the IBM Data Server Driver (at many sites this transition is already complete). When access to Db2 for z/OS from DRDA requester applications is accomplished through the IBM Data Server Driver, "Db2 Connect" becomes, essentially, just a product licensing term, versus an actual product used - this because entitlement to use the IBM Data Server Driver is provided through an organization's Db2 Connect license (so, if an organization is licensed for Db2 Connect Unlimited Edition for System z, that organization can deploy the IBM Data Server Driver in an unlimited way for applications that access the mainframe system(s) associated with the Db2 Connect license).</span></p><p><span style="font-family: arial;">There are several advantages to going with the direct connection to Db2 for z/OS versus going through a Db2 Connect gateway server. One is performance: with the "hop" to a Db2 Connect gateway server eliminated, better response times and throughput can be achieved. Another direct-connection benefit is <a href="http://robertsdb2blog.blogspot.com/2015/02/of-db2-connect-gateway-servers-and-db2.html"><span style="color: #2b00fe;">improved problem diagnosis capabilities</span></a> - error messages have more-specific meaning when the network-connected server that is "adjacent" to Db2 for z/OS is an application server, versus a Db2 Connect gateway server. The direct connection approach also tends to make Db2 client configuration and upgrade work more straightforward.</span></p><p><span style="font-family: arial;">Note my mention that this Db2 Connect gateway versus direct Db2 for z/OS connection matter is relevant for DRDA requester applications. It is not pertinent to clients that utilize the REST interface to Db2 for z/OS, as such interactions do not involve the DRDA protocol. See below for more information about Db2 REST clients.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Greater use of the Db2 for z/OS REST interface (which is part of DDF functionality)</b></span></p><p><span style="font-family: arial;">By "greater use" I don't mean to suggest that the REST interface to Db2 for z/OS is somehow "better" than the DRDA interface (which I like to call the SQL interface to Db2, as a DRDA requester application issues Db2-targeting SQL statements). The REST interface is different versus the SQL interface, and sometimes that difference makes it a good choice for a Db2-accessing client-server application. I wrote <a href="http://robertsdb2blog.blogspot.com/2018/09/the-two-paths-to-db2-for-zos.html"><span style="color: #2b00fe;">a blog entry a few years ago</span></a> with a lot of compare-and-contrast information about the REST and SQL interfaces to Db2, and I won't repeat all that here. To Db2 for z/OS DBAs, I'd say this: 1) make sure your application developers know that Db2 has a built-in REST interface, and 2) be ready to help support use of the REST interface when that is the choice of a development team. Sometimes, developers - even those who have strong SQL skills - have a preference for the REST architectural style, often because it so fully abstracts the particulars of service-providing systems.</span></p><p><span style="font-family: arial;">If you do make use of Db2's REST interface, and think you might expand on that in the future, consider what <a href="https://www.ibm.com/products/zos-connect"><span style="color: #2b00fe;">IBM z/OS Connect</span></a> could do for your organization. When Db2 for z/OS is accessed through z/OS Connect, it's still Db2's REST interface that's being used (Db2 in that case is a REST provider to z/OS Connect), but z/OS Connect provides some important benefits: it makes creation of z/OS-based REST services easier, it provides richer "service discovery" information to client application developers, it adds flexibility to the formatting of service-output JSON documents, and it provides a single access point through which all manner of z/OS-based programmatic assets can be invoked through REST requests - not only Db2 SQL statements and stored procedures, but also CICS and IMS transactions (which might or might not involve access to Db2) and JES batch jobs.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Leveraging SQL procedure language (SQL PL)</b></span></p><p><span style="font-family: arial;">SQL PL is for Db2 (for z/OS and for Linux/UNIX/Windows) what T-SQL is for SQL Server and what PLSQL is for Oracle - a way to write data-processing programs using only SQL statements. SQL PL makes that do-able via a set of SQL statements called control statements - "control" being short for "logic flow control." Among these statements are ITERATE, WHILE, GOTO, IF and LOOP - you get the idea. There are all kinds of reasons for using SQL PL, one being related to cost-of-computing: when a SQL PL routine (such as a native SQL procedure) is invoked through Db2's distributed data facility - whether through a SQL CALL issued by a DRDA requester or via a REST request - its execution is up to 60% offload-able to zIIP engines (mainframe processors that cost less than general-purpose processors and do not factor into the determination of z/OS software charges).</span></p><p><span style="font-family: arial;">Besides the economic advantage of SQL PL for DDF-using applications, there are functional advantages. For example, a native SQL procedure (a stored procedure written in SQL PL) - and only a native SQL procedure - can be created (or altered) with the AUTONOMOUS option, which means that if the calling transaction fails and is rolled back by Db2, the data-changing actions (e.g., INSERT/UPDATE/DELETE) performed by the autonomous procedure <i>will not be rolled back</i> (this can make autonomous procedures very useful for "transaction initiation audit trail" purposes - you can use an autonomous procedure to record the fact that a transaction got started, and that information will be preserved even if the transaction ends up failing). SQL PL routines can also accept Db2-defined arrays as input, whereas external Db2 routines (written in languages such as COBOL) cannot.</span></p><p><span style="font-family: arial;">Something else to consider: if you're using SQL PL only for stored procedure programs, you're missing out. <a href="http://robertsdb2blog.blogspot.com/2013/09/db2-10-for-zos-take-advantage-of-native.html"><span style="color: #2b00fe;">SQL PL can also be used to write user-defined functions</span></a>, and a SQL PL routine can be included in the body of an <a href="http://robertsdb2blog.blogspot.com/2017/08/db2-12-for-zos-sql-enhancements.html"><span style="color: #2b00fe;">advanced trigger</span></a> (advanced triggers were introduced with Db2 12 for z/OS).</span></p><p><span style="font-family: arial;"><u>And</u>, you should take note of how the <a href="http://robertsdb2blog.blogspot.com/2021/07/create-or-replace-agile-deployment-of.html"><span style="color: #2b00fe;">CREATE OR REPLACE PROCEDURE</span></a> syntax introduced with Db2 12 function level 507 can enable greater agility when it comes to deploying Db2 stored procedure programs, especially those written in SQL PL.</span></p><p><span style="font-family: arial;"><u>And</u>, you should be managing SQL PL source code (that would be, in the case of native SQL procedures, the associated CREATE PROCEDURE statements) with a source code management (SCM) tool - the SYSROUTINES table in the Db2 catalog is not intended to be a SQL PL SCM. Which SCM? Whichever one(s) your organization's developers use to manage their source code - that could be a vendor-supplied SCM or an open-source tool such as Git.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Using the right (no-charge) Db2 SQL development tool</b></span></p><p><span style="font-family: arial;">If you (or others in your organization) are using IBM Data Studio for Db2 for z/OS SQL testing and for SQL PL routine development and debugging, it's time for a change. IBM's strategic replacement for Data Studio is the (also no-charge) <a href="http://robertsdb2blog.blogspot.com/2021/10/update-on-no-charge-ibm-tools-for-db2.html"><span style="color: #2b00fe;">Db2 for z/OS Developer Extension for Visual Studio Code</span></a> (also available for the Eclipse Theia IDE).</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Leveraging the Db2 profile tables</b></span></p><p><span style="font-family: arial;">The <a href="http://robertsdb2blog.blogspot.com/2017/01/are-you-using-system-profile-monitoring.html"><span style="color: #2b00fe;">Db2 for z/OS profile tables</span></a> - SYSIBM.DSN_PROFILE_TABLE and SYSIBM.DSN_PROFILE_ATTRIBUTES - can be very helpful for a DRDA requester application workload. For one thing, they can be used to specify <i>application-specific</i> limits on concurrent DBAT (DDF thread) usage and/or connections established with a Db2 system and/or idle thread time - handy when the system-wide DBAT, connection and idle thread limits established via the ZPARM parameters MAXDBAT, CONDBAT and IDTHTOIN are not as granular as you need them to be. The Db2 profile tables can also be used to set the value of a number of Db2 special registers and/or built-in global variables, automatically when an application connects to the Db2 system. <a href="http://robertsdb2blog.blogspot.com/2018/07/db2-for-zos-using-profile-tables-to.html"><span style="color: #2b00fe;">One example of this kind of profile table usage</span></a> is setting the value of the CURRENT PACKAGE PATH special register to point a DRDA requester application to a collection in which the IBM Data Server Driver packages are bound with RELEASE(DEALLOCATE), as a means of getting high-performance DBAT functionality for the application.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Db2 for z/OS DBAs being heavily involved in application development</b></span></p><p><span style="font-family: arial;">In way too many cases, DRDA requester or Db2 REST interface-using applications are developed with little Db2 DBA involvement, until late in the game when a production deadline is looming and physical implementation of tables is done in a rushed and sub-optimal way. Logical database design may also have happened with little DBA input, with negative consequences down the road. This situation is typically not a result of application developers giving Db2 DBAs the cold shoulder. Rather, my observation has been that some Db2 for z/OS DBAs view developers as a nuisance or an irritant - as "them." Wrong mindset. Way wrong. Db2 for z/OS DBAs maximize the value they deliver to an organization when they team with developers at the very early stages of an application development project. Not only can that help to ensure a logical and physical database design that will deliver optimal benefits for the application (and for application users), it also provides an opportunity for DBAs to ensure that developers are aware of Db2 features - temporal data support, transparent archiving, the REST interface, autonomous native SQL procedures, non-traditional data types (e.g., XML), global variables, newer built-in functions (e.g., LISTAGG), advanced triggers, whatever - that could enable and accelerate development of functionality of importance for an application. My advice is for Db2 for z/OS DBAs to think of themselves as part of the extended development team for Db2-accessing applications. That approach can be especially effective for modern Db2 client-server applications.</span></p><p><span style="font-family: arial;">I hope that the information in this blog entry will be useful for you. As always, thanks for stopping by.</span></p>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com0tag:blogger.com,1999:blog-4516533711330247058.post-65817952238208614362022-06-26T20:14:00.000-07:002022-06-26T20:14:15.704-07:00Db2 for z/OS: The Online Path from a Partition-by-Growth Table Space to Partition-by-Range<p><span style="font-family: arial;">Last month, <a href="http://robertsdb2blog.blogspot.com/2022/05/db2-for-zos-online-path-from-multi.html"><span style="color: #2b00fe;">I posted to this blog an entry</span></a> on the long-awaited capability to migrate tables, in an online way, from a multi-table segmented or simple table space to multiple single-table partition-by-growth table spaces (a capability delivered with function level 508 of Db2 12 for z/OS). This month, I'll describe an even newer Db2 feature that also enables online migration of tables from one table space type to another. This feature, introduced with function level 500 of Db2 13 for z/OS, allows a DBA to migrate a table from a partition-by-growth table space to a partition-by-range table space with an ALTER TABLE statement and a subsequent online REORG. Read on to learn more.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>The motivation</b></span></p><p><span style="font-family: arial;">When the universal partition-by-growth (PBG) table space type was introduced with Db2 9 for z/OS, the response among a lot of DBAs was very enthusiastic, and there was good reason for this: it enabled a table to grow beyond 64 GB in size without having to be range-partitioned. Range-partitioning a table, after all, requires a good bit of up-front analysis. What should be the table's partitioning key? How many partitions should the table have? What should be the limit key value for each partition? By contrast, a PBG table space has more of a "set it and forget it" quality - you just determine the appropriate DSSIZE value for the table space (the maximum size for a partition of the table space), and a maximum number of partitions (the MAXPARTITIONS specification - easily changed at a later time if need be), and you're done. If the table space's DSSIZE value is, for example, 16G (i.e., 16 GB), when partition 1 reaches that size then Db2 will automatically add a second partition for the table space, and when that one hits 16 GB then a third partition will be added by Db2, and so on. Easy.</span></p><p><span style="font-family: arial;">Ah, but there came to be some "buyer's remorse" at more than a few Db2 for z/OS sites as certain PBG table spaces got larger and larger. Why? Because the larger a table gets the more advantageous it can be to have the table in a partition-by-range (PBR) table space. I described these PBR versus PBG advantages (for large tables) <a href="http://robertsdb2blog.blogspot.com/2015/05/for-large-db2-for-zos-table-should-you.html"><span style="color: #2b00fe;">in an entry I posted to this blog</span></a> a few years ago. They include potentially greater (maybe much greater) insert throughput, thanks to the ability to have multiple row-inserting processes execute concurrently for different partition of the PBR table space; great suitability for data rows managed on a time-series basis; and maximum partition-level utility independence. Here was the dilemma, though: prior to Db2 13, the only way to get a table from a PBG to a PBR table space was to unload the table, drop the table, re-create the table in a PBR table space, and reload the table's data. You had, then, this irritating situation: the advantages of PBR versus PBG would be more pronounced as a table got larger, but changing from PBG to PBR was more challenging as a table got larger, due to the unload/drop/re-create/re-load requirement.</span></p><p><span style="font-family: arial;">Enter Db2 13, and this situation changes, big-time.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>The Db2 13 difference</b></span></p><p><span style="font-family: arial;">Let's say you have table BIGTAB in a PBG table space, and you'd really like for BIGTAB to be in a PBR table space. In a Db2 13 system (Db2 13 became generally available on May 31 of this year), with function level 500 (or higher) activated, you can issue the following SQL statement (I have highlighted the new syntax in green, and I am assuming that the ACCT_NUM column of BIGTAB is the desired partitioning key):</span></p><p><span style="font-family: courier;"><span style="color: #800180;">ALTER TABLE BIGTAB</span><br /> <span style="color: #1bb456;">ALTER PARTITIONING TO PARTITION BY RANGE (ACCT_NUM)<br /> (PARTITION 1 ENDING AT (199),<br /> PARTITION 2 ENDING AT (299),<br /> PARTITION 3 ENDING AT (399),<br /> PARTITION 4 ENDING AT (MAXVALUE));</span><br /></span><br /><span style="font-family: arial;">That ALTER is a pending change. When an online REORG is subsequently executed for BIGTAB's table space, coming out of that online REORG the BIGTAB table will be in a PBR table space. Done. The table will have the same indexes that it had before, and it'll be immediately available for access by users and programs.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Some additional information, and considerations</b></span></p><p><span style="font-family: arial;">Here are a few things to keep in mind:</span></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;">The partitions of the new PBR table space will initially have the same DSSIZE as the PBG table space that's being migrated to PBR, and that's kind of important. Here's why: when you're determining the partitioning scheme for the new PBR table space you need to consider whether all the rows that belong to a given partition (per the partitioning scheme of the PBR table space) will fit in the partition, given the DSSIZE. Suppose, for example (and continuing with the BIGTAB table referenced previously), that the DSSIZE value for BIGTAB's PBG table space is 4G, and the number of rows in BIGTAB with an ACCT_NUM value greater than 199 and less than or equal to 299 (i.e., rows that would go into partition 2 of the new PBR table space) will not fit into a 4 GB data set. In that case the online REORG after the ALTER will fail. To avoid that failure, you'd need to either change the partitioning scheme so that the rows assigned to a given partition will fit in a 4 GB data set, or change the DSSIZE value of BIGTAB's PBG table space to something larger than 4G. If you decide on the latter action (increase the DSSIZE value for BIGTAB's PBG table space), understand that you'll need to issue that ALTER for the table space (to go to a large DSSIZE value) and then execute an online REORG to materialize that change and <u>then</u> issue the ALTER to change from PBG to PBR and execute another online REORG to materialize that pending change. Why two online REORGs? Because, when you take action to change a PBG table space to PBR the online way, there can't be any other outstanding (i.e., not yet materialized) pending changes for the PBG table space - the change to PBR has to be the only pending change for the PBG table space.</span></li></ul><div style="text-align: left;"><ul style="text-align: left;"><li><span style="font-family: arial;">The new PBR table space will use relative page numbering (RPN), which was introduced with Db2 12 for z/OS. This is a very good thing. To see why, check out <a href="http://robertsdb2blog.blogspot.com/2020/08/db2-12-for-zos-rpn-table-spaces-new.html"><span style="color: #2b00fe;">the blog entry I wrote about RPN</span></a> a couple of years ago.</span></li></ul><div><ul style="text-align: left;"><li><span style="font-family: arial;">As is very often the case when a pending change is materialized, the online REORG that changes a PBG table space to PBR will invalidate packages dependent on the associated table. You can identify those dependent packages by querying the SYSPACKDEP table in the Db2 catalog.</span></li></ul></div><div><ul style="text-align: left;"><li><span style="font-family: arial;">This ALTER + online REORG route from PBG to PBR is not available for a table that has an XML or a LOB column.</span></li></ul><br /></div><div><span style="font-family: arial;"><b>A closing thought</b></span></div><div><span style="font-family: arial;"><b><br /></b></span></div><div><span style="font-family: arial;">If you are on Db2 12 and you have some PBG table spaces that you'd like to change - easily, and in an online way - to PBR, the enhancement I've described herein could be a good reason for getting your Db2 13 migration project going.</span></div></div><p></p>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com0tag:blogger.com,1999:blog-4516533711330247058.post-85339227454303141932022-05-30T19:45:00.000-07:002022-05-30T19:45:08.337-07:00Db2 for z/OS: The Online Path from a Multi-Table Table Space to Universal Table Spaces<p><span style="font-family: arial;">Back in 2014, I posted an entry to this blog on the topic of <a href="https://robertsdb2blog.blogspot.com/2014/06/db2-for-zos-getting-to-universal-table.html"><span style="color: #2b00fe;">getting to universal table spaces</span></a> from non-universal table spaces. In that entry, I noted that there was an online path (ALTER followed by online REORG) for getting from a "classic" partitioned table space to a universal partition-by-range (PBR) table space, and for getting from a <u>single-table</u> simple table space or traditional segmented table space to a universal partition-by-growth (PBG) table space. I also pointed out that no such online path to universal table spaces existed for a multi-table table space: <i>"For a simple or segmented table space containing multiple tables, you'd have to either go the unload/drop/create/re-load route (with one PBG universal table space created for each table in the multi-table simple or segmented table space), or wait and see if a future release of Db2 provides a non-disruptive universal table space conversion process for multi-table simple and segmented table spaces (this is a known requirement)."</i> The wait for the hoped-for Db2 enhancement ended in October of 2020, when <a href="https://www.ibm.com/docs/en/db2-for-zos/12?topic=d1fl-function-level-508-activation-enabled-by-apar-ph29392-october-2020"><span style="color: #2b00fe;">Db2 12 function level 508</span></a> became available (via the fix for APAR PH29392). In this blog entry, I will describe how a capability introduced with Db2 12 function level 508 enables <u>online</u> migration of tables from multi-table table spaces to universal PBG table spaces.</span></p><p><span style="font-family: arial;">For illustrative purposes, let's say that you have a traditional segmented table space containing four tables (I say, "<i>traditional</i> segmented table space" because universal table spaces are also segmented). The tables are named T1, T2, T3 and T4. You have function level 508 (or later) activated on your Db2 12 system (or you have a Db2 13 system). How do you get tables T1, T2, T3 and T4 from the traditional segmented table space into universal PBG table spaces, in an online way? Here's how:</span></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;">First, create a table space for each of the tables T1, T2 and T3 (I'll get to T4 momentarily). Here's the form of the CREATE TABLESPACE statement you should use for this purpose (let's assume that the new table spaces will be named TS1, TS2, etc.):</span></li></ul><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><span style="color: #800180; font-family: courier;"><b>CREATE TABLESPACE TS1<br /> IN <i>dbname</i><br /> ...<br /> MAXPARTITIONS 1<br /> DEFINE NO<br /> DSSIZE xxx;</b></span></blockquote><blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;"><p><span style="font-family: arial;">Note: the database for each of these new PBG table spaces will need to be the same as the database of multi-table table space of interest (similarly, the CCSID of each of the new table spaces will have to be the same as the CCSID of the multi-table table space of interest). Also, MAXPARTITIONS 1 is required, at least initially (you can change the MAXPARTITIONS value later if desired). DEFINE NO is also required (the table space data sets will be created by Db2 later). DSSIZE can be any value appropriate for the tables that will be moved to the new table spaces (consider that 64G would work for any table, since a traditional segmented table space cannot exceed 64 GB in size).</span></p></blockquote><p></p><ul style="text-align: left;"><li><span style="font-family: arial;">Next, issue the following ALTER statement for the multi-table traditional segmented table space (the new ALTER TABLESPACE option introduced with Db2 12 function level 508 is highlighted in green):</span></li></ul><p></p><blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;"><span style="font-family: courier;"><b><span style="color: #800180;">ALTER TABLESPACE <i>dbname.source-table-space-name</i></span><br /> <span style="color: #25b416;">MOVE TABLE T1 TO TABLESPACE <i>dbname</i>.TS1;</span></b></span></blockquote><blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;"><p style="text-align: left;"><span style="font-family: arial;">Note that this is a pending change for the source table space - the actual table move will be effected via a subsequent online REORG of the source table space, as explained below. The statement above would be executed as well for tables T2 and T3 (I haven't forgotten about table T4 - I'll get to that). Keep in mind that, starting with Db2 12, the APPLCOMPAT package bind specification applies to DDL as well as DML statements. What does that mean for the ALTER TABLESPACE statement shown above? It means that the package through which the ALTER is issued (e.g., a DSNTEP2 package, or a SPUFI package) needs to have an APPLCOMPAT value of V12R1M508 or higher.</span></p></blockquote><p style="text-align: left;"></p><ul style="text-align: left;"><li><span style="font-family: arial;">OK, online REORG time. An online REORG executed for the source table space (the one in which tables T1, T2, T3 and T4 had been located) will cause each table for which an ALTER TABLESPACE with MOVE TABLE has been executed to be relocated to its designated PBG table space. When the online REORG has completed, each relocated table will be ready to use - it will have its indexes and everything.</span></li></ul><div style="text-align: left;"><span style="font-family: arial;">So, what about table T4? You have a choice here. One option would be to do for table T4 what you did for tables T1, T2 and T3: create a new PBG table space for the table, and execute an ALTER TABLESPACE with MOVE TABLE T4. And the other option? Well, consider the situation after you've moved tables T1, T2 and T3 to their respective PBG table spaces. The source table space, which formerly held four tables, now holds only one table: T4. What does that mean? It means that you can alter the source table space with a MAXPARTITIONS value and then online REORG it to convert it to a PBG table space - you've been able to do that for a single-table traditional segmented table space or a single-table simple table space since Db2 10 for z/OS.</span></div><div style="text-align: left;"><span style="font-family: arial;"><br /></span></div><div style="text-align: left;"><span style="font-family: arial;">Here are a few things to keep in mind with regard to online migration of tables from a multi-table table table space to PBG table spaces:</span></div><div style="text-align: left;"><ul style="text-align: left;"><li><span style="font-family: arial;">Moving tables from multi-table table spaces to PBG table spaces is likely to mean an increase in the number of data sets for the Db2 system. Given that a table's database can't change when it goes from a multi-table table space to a PBG table space, you'll want to make sure that the number of OBIDs (object identifiers) for the database in question will not exceed the limit of 32,767. <a href="https://www.ibm.com/docs/en/db2-for-zos/12?topic=design-identifying-databases-that-might-exceed-obid-limit"><span style="color: #2b00fe;">A page in the Db2 for z/OS documentation</span></a> contains a query that you can issue to identify databases that might be getting close to the OBID limit.</span></li><li><span style="font-family: arial;">More data sets could also lead you to increase the size of the DBD cache in the EDM pool (the associated ZPARM parameter is EDMDBDC), and/or to increase the Db2 subsystem's DSMAX value. Regarding the DBD cache, you generally want the ratio of "DBD requests" to "DBD not found" (referring to fields in a Db2 monitor-generated statistics long report, or in an online display of EDM pool activity) to be at least in the tens of thousands to one. As for DSMAX, you usually want that value to be sufficiently high so as to either not be reached (per your Db2 monitor) or so that only a few data sets per hour are closed as a result of hitting the DSMAX limit (again, per your Db2 monitor).</span></li><li><span style="font-family: arial;">An online REORG that materializes a MOVE TABLE pending change will invalidate packages that depend on the table or tables being moved, so plan for rebinding those packages (the SYSPACKDEP catalog table contains information to identify dependent packages).</span></li><li><span style="font-family: arial;">Additionally, an online REORG that materializes a MOVE TABLE pending change will operate on both the source and target table spaces; accordingly, inline image copies will be created for those table spaces. These will establish a recovery base for the objects, but note that after execution of the table-moving online REORG you will not be able to recover the source table space to a point in time prior to the table-moving online REORG.</span></li><li><span style="font-family: arial;">Don't worry if a source table space holds hundreds of tables (as some do in the real world) - there's no requirement that all the tables be moved to PBG table spaces in one fell swoop. You can move a few at a time, no problem. Just keep in mind that an online REORG of the source table space will move every table that has not already been moved and for which there is a pending MOVE TABLE change.</span></li></ul><span style="font-family: arial;">There you have it. Universal table spaces are what you want, and you now have an online way to get there for your multi-table table spaces. Happy moving.</span></div><p></p>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com0tag:blogger.com,1999:blog-4516533711330247058.post-79110634795393349162022-04-28T12:29:00.000-07:002022-04-28T12:29:32.876-07:00Db2 for z/OS: Answering Some Questions About DBATs (i.e., DDF Threads)<p><span style="font-family: arial;">Sometimes, a DBA will email me a question about </span><span style="font-family: arial;">Db2 for z/OS, and I'll respond in a pretty comprehensive way, and I'll look at that outbound message and think to myself, "Hmm. I may have written most of a blog entry there." This is one of those cases. I recently got a couple of questions about Db2 database access threads, or DBATs (threads associated with access to a Db2 subsystem through the distributed data facility, aka DDF), and I think the questions and answers might be of interest to a lot of people in the Db2 community. I am therefore packaging them in this blog entry. Off we go:</span></p><p><span style="font-family: arial;"><b><br /></b></span></p><p><span style="font-family: arial;"><b>Question: W</b></span><span style="font-family: arial;"><b>hen does a DBAT go from being <u>active</u> to <u>idle</u>, so that it is subject to the idle thread timeout setting for the Db2 subsystem?</b></span></p><p><span style="font-family: arial;"><i>Answer:</i> OK, the first thing to understand here is that a DBAT <i>is always active</i>. Even when it's in the DBAT pool, a DBAT is active - it's just in a so-called disconnected state. A DBAT is <u>idle</u> when it is in-use (i.e., paired with a connection, which happens when a transaction comes along by way of that connection) <u>and</u> it’s not doing anything (or, more accurately, the transaction that was using the DBAT appears to Db2 to be doing nothing). It's normal for there to be some idle thread time for a DDF transaction - a client-side program issues a SQL statement, the result is sent back to that program, and the DBAT is briefly idle until the transaction's next SQL statement is issued. No big deal there. It's when idle time becomes really elongated that a DBAT might be affected by the Db2 subsystem's idle thread timeout value. That timeout value is specified via the IDTHTOIN parameter in the Db2 DSNZPARM module. The default value for IDTHTOIN is 120 seconds (check out the value on your Db2 system, and see if it's set to something other than 120).</span></p><p><span style="font-family: arial;">Normally, at end-of-transaction there is a commit, and at that time the DBAT that had been used in processing the transaction goes back to the DBAT pool and the connection with which the DBAT had been paired goes back to an inactive state (inactive connections, a key contributor to Db2's connection scalability, are a server-side thing, invisible to a connected application - an inactive connection will go back to an active state when the next transaction associated with the connection begins). Can a DBAT in the pool be affected by the Db2 system's idle thread timeout value? No, but it is subject a limit specified by another ZPARM parameter called POOLINAC (more on that to come).</span></p><p><span style="font-family: arial;">Let's say that a DDF transaction starts but then never commits. That could happen because of a problem on the client application side, or it could be that the developer of the transaction program decided that a commit is not necessary because the transaction is read-only in nature (that in fact would be a not-good decision - every DDF transaction needs to commit, because even a read-only transaction will hold one or more table space or partition locks and one or more claims on database objects, and those locks and claims will not be released without a commit). Because the transaction has not committed, it is perceived by Db2 to be still in-flight, and for that reason the transaction's DBAT can't be separated from the associated connection and returned to the DBAT pool. The apparently (to Db2) in-flight transaction continues to do nothing, and the related DBAT remains idle for a longer and longer period of time. Eventually the IDTHTOIN limit will be reached for the idle thread </span><span style="font-family: arial;">(unless IDTHTOIN is set to 0, which means a DBAT can remain indefinitely idle)</span><span style="font-family: arial;">, and Db2 terminates the DBAT and the associated connection.</span></p><p><span style="font-family: arial;">So, to recap: first, a DBAT does not go from active to idle, because a DBAT is always considered to be active - it's an in-use DBAT, as opposed to an in-the-pool DBAT, that can be idle. Second, an in-use DBAT will typically have at least some idle time (time when it seems to Db2 that the transaction associated with the DBAT is not doing anything - or, at least, not doing any SQL-related thing); it's when that "nothing SQL-related is happening" time gets long that the Db2 idle thread timeout limit can be reached for a DDF transaction and its DBAT.</span></p><p><span style="font-family: arial;">[By the way, I mentioned earlier that when Db2 terminates a DDF transaction and its DBAT due to the idle thread timeout limit being reached, Db2 also terminates the connection with which the DDF transaction had been associated. If you'd prefer for Db2 to preserve the connection while terminating the transaction and the DBAT, you can get that behavior thanks to an enhancement introduced with Db2 12 for z/OS. The enhancement is enabled via specification of </span><span style="font-family: arial;">EXCEPTION_ROLLBACK as an attribute of a MONITOR IDLE THREADS row in the </span><span style="font-family: arial;">Db2 table SYSIBM.DSN_PROFILE_ATTRIBUTES. You can find more information about this enhancement in the Db2 for z/OS online documentation, at </span><span style="font-family: arial;"><a href="https://www.ibm.com/docs/en/db2-for-zos/12?topic=threads-monitoring-idle-by-using-profile-tables"><span style="color: #2b00fe;">https://www.ibm.com/docs/en/db2-for-zos/12?topic=threads-monitoring-idle-by-using-profile-tables</span></a>.]</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;"><b>Question: We had a DDF transaction surge, and as a result the number of DBATs went way up. The surge passed, and several minutes later I checked on the number of DBATs and it was still way high. What's with that</b></span><span style="font-family: arial;"><b>?</b></span></p><div><span style="font-family: arial;"><i>Answer: </i>There were (I'm pretty sure) two factors involved here. First, the POOLINAC value. That's a ZPARM parameter. If a DBAT in the pool has gone a POOLINAC number of seconds without being reused for a transaction, that DBAT will be subject to termination by Db2. For the Db2 subsystem looked after by the DBA who asked me this question, the POOLINAC value was 900 seconds, considerably higher than the default value of 120 seconds (I personally favor setting POOLINAC to the default value of 120 and leaving it there unless there's a good reason to make a change). A high POOLINAC value will definitely slow down the trimming of the number of pooled DBATs after the passing of a DDF transaction surge, but I think something else was going on, as well.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">That "something else" was a change in Db2's termination of "too-long-in-the-pool-without-reuse" DBATs, effected by the application of the fix for Db2 APAR <a href="https://www.ibm.com/support/pages/apar/PH36114"><span style="color: #2b00fe;">PH36114</span></a> (that fix came out in June of 2021, and I say that "I think" the fix was involved in this situation, rather than "I know," because I did not verify that the fix was applied to the Db2 subsystem in question - I'm inferring that based on the behavior reported by the DBA). Here's the deal: prior to the change associated with PH36114, Db2 would check the DBAT pool every two minutes to see if any DBATs in the pool had been there for a POOLINAC number of seconds without being reused. However many "</span><span style="font-family: arial;">too-long-in-the-pool-without-reuse" DBATs were found, were terminated by Db2. If a lot of DBATs went back to the pool at around the same time following the rapid subsidence of a DDF transaction surge, Db2 might find in a subsequent pooled DBAT purge cycle that a lot of DBATs needed to be terminated at one time due to the POOLINAC limit being exceeded. With the ZPARM parameter </span><span style="font-family: arial;">REALSTORAGE_MANAGEMENT set to AUTO or ON (and AUTO is the default), t</span><span style="font-family: arial;">erminating a lot of DBATs at one time could put a good bit of pressure on the z/OS LPAR's ESQA resource, which in turn could cause spill-over into ECSA, which in turn could be bad news for an LPAR with only a small cushion of unused ECSA space.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">With the fix for PH36114 applied, two things changed in this picture:</span></div><div><ol style="text-align: left;"><li><span style="font-family: arial;">Db2 went from checking every two minutes for DBATs with "too-long-in-the-pool-without-reuse" status to doing that every 15 seconds.</span></li><li><span style="font-family: arial;">In a given purge cycle (again, now once every 15 seconds), Db2 will terminate a maximum of 50 DBATs in the "too-long-in-the-pool-without-reuse" category.</span></li></ol><span style="font-family: arial;">What this means: a big pile of pooled DBATs left over from a since-passed DDF transaction surge will be worked down more frequently <u>and</u> more gradually. That could somewhat elongate the process of finalizing the right-sizing of the DBAT pool for a now-back-to-normal volume of DDF transactions, but it will avoid the pressure on ESQA that could result from the more-aggressive purging of "too-long-in-the-pool-without-reuse" DBATs that Db2 did prior to the PH36114 fix. It's a good trade-off, in my opinion.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">Maybe you now know a few things about DBATs that you didn't know before. Thanks for visiting the blog, and I hope you'll return sometime.</span></div>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com4tag:blogger.com,1999:blog-4516533711330247058.post-18926674214467931362022-03-09T18:57:00.000-08:002022-03-09T18:57:37.816-08:00Thoroughly Assessing Data Security in a Db2 for z/OS Environment - Part 2<p><span style="font-family: arial;">In <a href="https://robertsdb2blog.blogspot.com/2022/02/thoroughly-assessing-data-security-in.html"><span style="color: #2b00fe;">part 1</span></a> of this two-part blog entry on thoroughly assessing data security in a Db2 for z/OS environment, I covered four aspects of Db2 data protection: privilege management, client authentication, data encryption and column masks/row permissions. In this part 2 entry we'll take a look at auditing, application architecture, test data management and RACF (or equivalent) management of Db2-internal security.</span></p><p><span style="font-family: arial;">Off we go:</span></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;"><b>Auditing -</b> You can manage Db2 privileges in a careful and responsible way, but at the end of the day users have to have <u>some</u> privileges in order to perform assigned duties, and some users are going to require extensive privileges. Privileges that were properly granted can be abused. The best defense against that possibility is effective auditing of users' data access activities. What you want in this case is to identify improper data access activity so that steps can be taken to shut it down. An important assist in this area was delivered with Db2 10 for z/OS, which introduced audit policy functionality. Db2's audit policy capability enables you to monitor (among other things):</span></li><ul><li><span style="font-family: arial;"><i>Occurrences of access actions that failed due to inadequate authorization</i> (if a particular user is getting a pretty good number of these, that could indicate attempts to probe for "holes" in your organization's data protection measures).</span></li><li><span style="font-family: arial;"><i>Occurrences of a user changing his or her SQL ID </i>(there are times when this is a legitimate action, and times when it is not)</span></li><li><span style="font-family: arial;"><i>Occurrences of tables being altered</i> (depending on the ALTER action, this could be an attempt to circumvent a data protection measure).</span></li><li><span style="font-family: arial;"><i>Occurrences of a particular table being accessed in read or data-change mode</i> (is a table holding sensitive data values being accessed at odd hours?).</span></li><li><span style="font-family: arial;"><i>Utility execution</i> (could someone be trying to use a Db2 utility as a "back door" means of data access?).</span></li><li><span style="font-family: arial;"><i>Incidences of privileges being granted or revoked</i> (inappropriate granting of Db2 privileges can be a warning sign).</span></li><li><span style="font-family: arial;"><i>Use of system administration "super-user" privileges: install SYSADM, install SYSOPR, SYSOPR, SYSCTRL, or SYSADM</i> (to quote a line from several "Spider-Man" movies: "With great power comes great responsibility").</span></li><li><span style="font-family: arial;"><i>Use of database and security administration "super-user" privileges: DBMAINT, DBCTRL, DBADM, PACKADM, SQLADM, system DBADM, DATAACCESS, ACCESSCTRL, or SECADM</i> (see the "Spider-Man" quote above).</span></li></ul></ul><p></p><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p></p><div><span style="font-family: arial;">Db2 12 function level 509 introduced an important audit policy enhancement: tamper-proof audit policies. With that enhancement, an audit policy can be set up so that it can be changed only with the permitting action of a person outside the Db2 team (specifically, a RACF administrator).</span></div><p></p></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p style="text-align: left;"><span style="font-family: arial;">You can read all about developing, activating and using audit policies in the <a href="https://www.ibm.com/docs/en/db2-for-zos/12?topic=db2-audit-policy"><span style="color: #2b00fe;">Db2 for z/OS online documentation</span></a>.</span></p></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p style="text-align: left;"><span style="font-family: arial;">If you're interested in a data access monitoring solution that can span all of your enterprise's data stores, both on-prem and cloud-based, check out <a href="https://www.ibm.com/products/ibm-guardium-data-protection"><span style="color: #2b00fe;">I</span></a></span><span style="font-family: arial;"><a href="https://www.ibm.com/products/ibm-guardium-data-protection"><span style="color: #2b00fe;">BM Security Guardium Data Protection</span></a>.</span></p></blockquote><p></p><ul style="text-align: left;"><li><span style="font-family: arial;"><b>Application architecture -</b> Can application architecture enhance Db2 data protection? Absolutely. Consider, for example, the security advantage of static versus dynamic SQL statements. If an application process will access data in table T1 via a dynamic query, the authorization ID of the application process will require the SELECT privilege on T1. If, on the other hand, the application process will access data in T1 by way of a static query, the application's ID will not need any table access privileges; instead, the ID will need only the EXECUTE privilege on the Db2 package associated with the static query. Reducing the granting of table-access privileges to application and/or user IDs can strengthen Db2 data security.</span></li></ul><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div style="text-align: left;"><span style="font-family: arial;">OK. But what about applications of the client-server variety, particularly those that access Db2 for z/OS data from network-connected Linux or UNIX or Windows servers? For such applications, use of client-issued static SQL statements is often either not possible or, if possible, not favored by client-side programmers (for example, a Java program can issue static SQL statements in SQLJ form, but in my experience Java programmers overwhelmingly prefer JDBC to SQLJ, and JDBC means dynamic SQL on the Db2 side). In those cases, two convenient ways to utilize static SQL are 1) Db2 stored procedures and 2) Db2 REST services.</span></div></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p style="text-align: left;"><span style="font-family: arial;">Lots of application developers who prefer the JDBC and ODBC forms of SQL (to name two very popular forms of non-DBMS-specific SQL) are plenty happy with a stored procedure approach, as stored procedures are widely used with relational database management systems such as Db2. A programmer can use (for example) JDBC statements to call Db2 stored procedures and to retrieve rows from associated query result sets (when a stored procedure declares and opens a cursor). The stored procedure calls will be dynamic on the Db2 side, but the "table-touching" SQL statements issued by the stored procedures will be static, and that means that the application's ID will require only the EXECUTE privilege on the called stored procedures - not table access privileges.</span></p></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p style="text-align: left;"><span style="font-family: arial;">Db2's <a href="http://robertsdb2blog.blogspot.com/2021/02/are-you-using-rest-interface-to-db2-for.html"><span style="color: #2b00fe;">built-in REST interface</span></a> is another way to make static SQL easy to use from a client-side programmer's perspective. By way of this interface, which is an extension of the Db2 distributed data facility, a static SQL statement can be invoked via a REST request. The static SQL statement associated with a Db2 REST service can be a SELECT, an INSERT, an UPDATE, a DELETE, a TRUNCATE or a CALL (of a stored procedure).</span></p></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p style="text-align: left;"><span style="font-family: arial;">Here's another security advantage of client-side programs invoking Db2 server-side static SQL statements, whether through the use of stored procedures or the Db2 REST interface (which can itself be used, as noted, to invoke stored procedures): when this approach is used, client-side programmers do not have to know anything about table or column names - that knowledge is needed only by the people who code the server-side static SQL statements. How does this shielding of database schema information enhance data security? Well, the fewer the people who know stuff like table and column names, the less likely it is that a database will be hacked by bad guys.</span></p></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p style="text-align: left;"><span style="font-family: arial;">[Note: when a stored procedure is to be invoked through the Db2 distributed data facility, either through a SQL call or a REST request, that stored procedure will get up to 60% zIIP offload when executed IF the stored procedure is written in SQL PL (i.e., if it is a so-called native SQL procedure). A stored procedure written in a language other than SQL PL will get little to no zIIP offload when called through DDF.]</span></p></blockquote><p style="text-align: left;"></p><ul style="text-align: left;"><li><span style="font-family: arial;"><b>Test data management -</b> Let's say you have a production Db2 database in which some sensitive data values are stored (e.g., credit card numbers). Perhaps you have taken a number of steps to protect those sensitive data values. Great. But now an application team wants tables in their Db2 development environment populated with data from the production system. You could copy data over from the production to the development system, but will the sensitive data values be protected in the development environment as they are in production? Even if the data protection measures in the development environment are as strong as those in place for the production Db2 system, creating another copy of data that includes sensitive data values will still involve some data-security risk because the data copy increases what a security auditor might call the "threat area" - do you want to accept that risk?</span></li></ul><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div style="text-align: left;"><span style="font-family: arial;">Often, the best approach to use in this situation is to mask or otherwise obscure the sensitive data values before (or as part of) copying production data to a development or test system. How would you accomplish that? You could do it on your own, but that can be a time-consuming effort and the "roll-your-own" data masking could impact the CPU and elapsed times of a production-to-test data copy operation. An easier (and often better-performing) way to get this done would be to use a software tool designed for the purpose. Two options in this space that are available from IBM are <a href="https://www.ibm.com/docs/en/iotdmfz/11.7?topic=overview-optim-test-data-management-solution-zos"><span style="color: #2b00fe;">IBM </span></a></span><span style="font-family: arial;"><a href="https://www.ibm.com/docs/en/iotdmfz/11.7?topic=overview-optim-test-data-management-solution-zos"><span style="color: #2b00fe;">InfoSphere Optim Test Data Management Solution for z/OS</span></a> and <a href="https://www.ibm.com/docs/en/db2-cloning-tool/3.2.0"><span style="color: #2b00fe;">IBM Db2 Cloning Tool for z/OS</span></a> (the former is useful for copying a referentially complete subset of data rows from one Db2 </span><span style="font-family: arial;">system to another, while the latter is more appropriate for copying entire table spaces and indexes - or even an entire subsystem's data - from one Db2 system to another). Both of those tools have data masking capabilities, to prevent sensitive data values from being copied "as-is" from a production environment to a test or development system.</span></div></blockquote><p></p><ul style="text-align: left;"><li><span style="font-family: arial;"><b>RACF (or equivalent) management of Db2-internal security -</b> It is very common for RACF (or an equivalent z/OS security management subsystem) to be <a href="http://robertsdb2blog.blogspot.com/2019/12/db2-for-zos-and-racf-part-1-external.html"><span style="color: #2b00fe;">used for external Db2 security purposes</span></a> - that is, to control which application processes and/or users can connect to a Db2 subsystem, and how. Once an application process or a user has successfully connected to a Db2 subsystem, what happens next is a matter of Db2-internal security: does the ID of the application or user have the Db2 privileges needed to (for example) read data in a table, or update data in a table, or create an index or bind a package? In my experience, Db2-internal security is most often managed within Db2 by the Db2 administration team, who use the SQL statements GRANT and REVOKE to provide privileges for, or remove privileges from, various authorization IDs. It is possible to use RACF (or equivalent) to manage Db2-internal security as well as Db2-external security, and a growing number of organizations are doing just that.</span></li></ul><p></p><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p></p><div><span style="font-family: arial;">I posted an entry to this blog a couple of years ago with a good bit of information about <a href="http://robertsdb2blog.blogspot.com/2020/01/db2-for-zos-and-racf-part-2-db2.html"><span style="color: #2b00fe;">using RACF to manage Db2-internal security</span></a>. I won't repeat that content here; rather, I'll provide some thoughts and observations on this topic:</span></div></blockquote><p></p><ul style="text-align: left;"><ul><li><span style="font-family: arial;"><i>Why</i> <i>do organizations do this? </i>Quite often (in my experience) it's because someone - perhaps a security auditor - told them that it has to be done. Why might that pronouncement be made? Well, in the minds of many security people, it's a good thing for a single group of people to manage all aspects of security for a database management system. Because RACF can be used to manage both Db2-external and Db2-internal security, while Db2's security features apply mainly to internal security, if one team is going to manage all aspects of Db2 for z/OS security then it's going to be the RACF team.</span></li><li><span style="font-family: arial;"><i>Db2 11 for z/OS eliminated what were just about the last two hassles that were formerly associated with RACF management of Db2-internal security.</i> Prior to Db2 11, auto-rebinds could fail with authorization errors when RACF was used to manage Db2-internal security. Why? Because when an auto-rebind occurs you generally want Db2 to do that based on the privileges held by the ID of the <u>owner</u> of the package. It used to be that when RACF managed Db2-internal security, the authorization check for an auto-rebind looked at the privileges held by the ID of the application process that prompted the auto-rebind by requesting execution of a package that had been marked invalid by Db2, and that ID rarely has the privileges needed for a successful auto-rebind. Db2 11 fixed that problem by enabling RACF to check the ID of a package owner for auto-rebind authorization. The other nagging problem fixed by Db2 11 concerned caches of authorization information that Db2 maintains in memory. Information in those caches was formerly not updated to reflect security changes effected through RACF, the result being a frustrating lag between some RACF-side changes and enforcement of same in Db2. Db2 11 fixed that problem by having Db2 listen for ENF signals (referring to the z/OS event notification facility) sent by RACF when authorization changes are made.</span></li><li><span style="font-family: arial;"><i>Organizations that have gone to RACF management of Db2-internal security are pretty happy with the arrangement, and that includes the Db2 for z/OS DBAs.</i> Yes, there is a good bit of set-up work involved in making this transition, and that can seem more challenging than it really is because Db2 DBAs and RACF administrators speak different languages in a technical sense, but once things are set up and the transition has been completed, people find that it really works as advertised. Ask a Db2 DBA at a site that has gone to RACF management of Db2-internal security if he or she is OK with the change, and you'll likely get a thumbs-up. I haven't found many (any, actually) DBAs in these organizations that pine for the days when they had to issue GRANTs and REVOKEs to manage Db2-internal security. Letting the RACF team handle Db2-internal security lets the DBAs focus on database administration tasks (e.g., performance tuning, application enablement) that they generally find to me more satisfying.</span></li></ul></ul><span style="font-family: arial;">OK, that's what I've got. Consider these areas, and those I covered in <span style="color: #2b00fe;"><a href="http://robertsdb2blog.blogspot.com/2022/02/thoroughly-assessing-data-security-in.html">part 1</a></span> of this two-part entry, and I think you'll be able to comprehensively evaluate the Db2 security set-up you have at your site. I hope that this information will be helpful for you.</span><p></p><p></p><p></p>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com0tag:blogger.com,1999:blog-4516533711330247058.post-3312613139986180592022-02-24T17:12:00.000-08:002022-02-24T17:12:32.190-08:00Thoroughly Assessing Data Security in a Db2 for z/OS Environment - Part 1<p><span style="font-family: arial;">I regularly get questions from Db2 for z/OS people that pertain to data security. Most of the time, these questions have a pretty narrow focus - a DBA, for example, wants to know more about Db2 roles and trusted contexts, or about SECADM authority, or about "at-rest" encryption of Db2 data on disk. Recently, I had a meeting with some people from a client's mainframe IT staff, and they wanted to know what a comprehensive Db2 for z/OS data security review would look like. For me, that was a refreshingly wide-scope question. What areas would one want to examine, if one wanted to thoroughly assess the data security posture of a Db2 for z/OS system? In this part one of a two-part blog entry I will give you my take on the matter, starting with four areas of Db2 data protection: privilege management, client authentication, data encryption, and column masks and row permissions. In the part two entry, which I hope to post in about two weeks, I will cover four other areas of Db2 data protection: auditing, application architecture, test data management and RACF (or equivalent) management of Db2-internal security.</span></p><p><span style="font-family: arial;">Onward, then, to the first four areas of Db2 data protection I would recommend considering as part of a comprehensive Db2 security assessment:</span></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;"><b>Privilege management -</b> This is about the Db2 privileges that have been granted to user, group and application IDs. With regard to user privileges, the best-practice approach is to grant to a given individual only those privileges that minimally enable the individual to do his or her job. One area where there has been a lot of cracking down in recent years concerns the granting of SYSADM authority. That's basically super-user status, and years ago it was common for organizations to give the SYSADM authority level to quite a few people on the Db2 support team. Why did that practice become problematic? Mainly because someone with SYSADM authority can look at (and even change) the data in any table. You could say, "It's OK - no one on my Db2 team is a bad actor," but that argument is not likely to sway security auditors these days. For some organizations, the solution to over-granting of SYSADM is to change that authority, for many or even most of the people on the Db2 team, to DBADM WITHOUT DATAACCESS ON SYSTEM. That move can spark protest from someone who loses SYSADM authority, but in fact many DBAs can do the large majority of things they need to do with system DBADM authority. If access to data in a specific table is required for a DBA with system DBADM WITHOUT DATAACCESS authority, the SELECT privilege on the table can be granted and then revoked when the task requiring access to the table's data has been completed.</span></li></ul><p></p><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p></p><div style="text-align: left;"><span style="font-family: arial;">Here's another way in which super-user authority has been reigned in lately: organizations can set the value of the Db2 ZPARM parameter SEPARATE_SECURITY to YES. What does that do? It removes from SYSADM authority the ability to create and manage security objects (e.g., roles, trusted contexts, row permissions and column masks), and the ability to grant privileges to others (unless the ID with SYSADM authority holds the privilege in question WITH GRANT OPTION, or owns the object on which a privilege is being granted). How do those things get done, if a SYSADM can't do them? They are done by an ID with SECADM authority (more information in this area can be found <a href="http://robertsdb2blog.blogspot.com/2021/09/db2-for-zos-separatesecurity-and-secadm.html">i<span style="color: #2b00fe;">n an entry I posted to this blog last year</span></a>).</span></div></blockquote><span style="font-family: arial;"><div><span style="font-family: arial;"><br /></span></div></span><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><span style="font-family: arial;">What about the group IDs that I mentioned above? Those have been around for a long time. A group ID is so called because it refers to a RACF (or equivalent z/OS security manager) group to which individual IDs can be connected. When the Db2-supplied sample connection and sign-on exits are used by an organization (very widely done), the RACF group IDs to which your primary authorization ID are connected become your secondary authorization IDs in Db2, and the ability to execute most Db2 SQL statements and commands depends on the privileges held by your primary authorization ID a<i>nd by any of your secondary authorization IDs</i>. This can make Db2 privilege management much simpler, especially if a set of privileges tailor-made to enable execution of a certain set of Db2 actions is fairly complex - you just grant that set of privileges to a RACF group ID, and then connect to that group ID the IDs of individuals who need to perform the associated set of Db2 actions.</span></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p style="text-align: left;"><span style="font-family: arial;">Some security administrators and auditors worry - understandably - about the privileges granted to the ID of an application that issues dynamic SQL statements, especially when that application connects to Db2 via TCP/IP communication links and through the Db2 distributed data facility (DDF). Why the worry? Well, for a dynamic SQL statement, such as a SELECT, to execute successfully, the associated Db2 authorization ID needs to have the SELECT privilege on the target table. A DDF-using application that issues SQL statements in JDBC or ODBC form (these will be dynamic SQL statements on the Db2 side) usually connects to the Db2 system using a certain ID and an associated password. What if someone who knows that ID and password tries to use those credentials to connect to the Db2 system from a PC, and then view data in tables that the application can access? An effective defense against that scenario can be implemented using Db2 roles and trusted contexts, as described <a href="http://robertsdb2blog.blogspot.com/2019/03/a-case-study-implementing-db2-for-zos.html"><span style="color: #2b00fe;">in a blog entry I posted a few years ago</span></a>.</span></p></blockquote><p></p><ul style="text-align: left;"><li><span style="font-family: arial;"><b>Client authentication -</b> I just now referred to applications that access Db2 systems via TCP/IP communication links and through the Db2 distributed data facility, and there are often individual users that do the same thing, perhaps using a workstation-based query and reporting tool. How are these applications and users authenticated at connection time? As noted above, this is usually done by way of a password. Typically, an organization requires a user to change his or her password on a regular basis - for example, every three months. What about the password associated with an application's ID? There was a time when it was quite common for such a password to be of the "never expire" type. That kind of password is increasingly deemed unacceptable by security auditors, who insist that the password associated with an application's ID be regularly changed, just as is done for passwords associated with user IDs. That is in fact a good policy from a security perspective, but it can lead to authentication-related connection errors when an application's password is changed. What if an application's password is changed in RACF before it is changed on the app server side, or vice versa? The strategy I've seen employed for non-disruptively changing a Db2 client-server application's password involves having two IDs for a given application. Shortly before the password for ID1 is set to expire, the application starts connecting to Db2 using ID2 (whose password will be good for the next three months or whatever). Once all instances of the application have switched over to ID2, the password for ID1 can be updated (and maybe that doesn't happen until a little before ID2's password is set to expire). As long as the application is running on at least two app servers, the switch from the one ID to the other can be accomplished with no application outage. What makes this work on the Db2 side is the fact that all privileges needed for the application's dynamic SQL statements to execute successfully are granted to ID1 and to ID2.</span></li></ul><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p style="text-align: left;"><span style="font-family: arial;">What if you'd prefer for an application or a user ID to be authenticated by some means other than a password? Are there alternatives? Yes. One alternative is to use RACF PassTickets - you can find more information about RACF PassTickets in the <a href="https://www.ibm.com/docs/en/zos/2.4.0?topic=guide-using-passtickets"><span style="color: #2b00fe;">online z/OS documentation</span></a>. Another option is to use certificate-based authentication. Often, when one thinks about certificates in a Db2 for z/OS context, it is in relation to SSL encryption for communication between Db2 and a network-connected client application (more on encryption to come - see below); however, in my experience Db2 SSL encryption typically involves use of a <i>server</i> certificate versus <i>client</i> certificates (the host certificate is transmitted to the client system and is presented at connection time by the client application - the host recognizes its own certificate and the "SSL handshake" can proceed to successful completion). That said, it is also possible for a client system to present its own certificate as a means of authentication when requesting a connection to a Db2 system. A good source of information on certificate-based authentication for a Db2 client is a document titled, "Db2 for z/OS: Configuring TLS/SSL for Secure Client/Server Communications," which can be downloaded from the <a href="http://www.redbooks.ibm.com/abstracts/redp4799.html?Open"><span style="color: #2b00fe;">IBM redbooks Web site</span></a>. R</span><span style="font-family: arial;">efer to the information under the heading, </span><span style="font-family: arial;">"Client access to Db2 using TLS/SSL client authentication,</span><span style="font-family: arial;">" on page 61 of the </span><span style="font-family: arial;">document.</span></p></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div style="text-align: left;"><span style="font-family: arial;">One other thing regarding client authentication. There is a parameter in the Db2 DSNZPARM module called TCPALVER. The default value for that parameter is NO, and that is almost certainly the value you want. If TCPALVER is set to YES in your Db2 environment, it means that Db2 assumes that a process wanting to connect to the Db2 system via TCP/IP is already verified. That being the case, the Db2 system will accept a TCP/IP client connection request that provides an ID but no authentication credential - no password or PassTicket or client certificate is required. Now, if you see that TCPALVER is set to YES for a Db2 subsystem, don't panic - almost certainly, RACF (or equivalent) will block a connection request that lacks an authentication credential; still, in the interest of having "belt and suspenders" security safeguards (a good idea), you'll probably want to change the TCPALVER value from YES to NO in the very near future. Before making that change, consider that a setting of TCPALVER=YES might have been put in place a long time ago, when the only clients connecting to the Db2 system via TCP/IP were other Db2 for z/OS systems. When that kind of communication was first happening (again, a long time ago), a requester Db2 for z/OS system might not have been sending a password when requesting a connection to a server Db2 for z/OS system, the thinking being that user authentication had already happened on the requester Db2 for z/OS side. If you have Db2 for z/OS systems communicating with other Db2 for z/OS systems using DRDA, without passwords being sent, those connection requests will fail after you change the TCPALVER value from YES to NO. To avoid that problem, update the Db2 communications database on the Db2 for z/OS requester side to have a password sent with a connection request to a DRDA server, and THEN change the TCPALVER setting from YES to NO.</span></div></blockquote><ul style="text-align: left;"><li><span style="font-family: arial;"><b>Data encryption -</b> I posted an entry to this blog in 2020 <a href="https://robertsdb2blog.blogspot.com/2020/06/the-various-aspects-of-db2-for-zos-data.html"><span style="color: #2b00fe;">about data encryption in a Db2 for z/OS context</span></a>, and I won't repeat that content here. Instead, I'll add a few extra nuggets of information:</span></li><ul><li><span style="font-family: arial;">APAR PH08188 (April of 2019) made it possible to configure a Db2 for z/OS system so that its <u>only</u> SQL listener port is a "secure" port (i.e., one that requires a requester to use SSL encryption). When a Db2 system is set up that way, it is not possible for a client application to establish a non-SSL connection to the Db2 server.</span></li><li><span style="font-family: arial;">Db2's leveraging of the data set encryption feature of z/OS for encryption of Db2 table space and index data "at rest" (i.e., on disk) involves associating an encryption key label with a data set and RACF-permitting use of that key label. This has caused some people to be concerned about the need to provide RACF permission for various encryption ket labels to various application and user IDs. That concern is unfounded. When a user or an application issues a SQL statement that targets a Db2 table, and table space and/or index data sets on disk are accessed as a result, from the z/OS perspective it is not the user or application accessing the data sets - it's Db2 accessing the data sets (and the same is true for the "online" IBM Db2 utilities, such as LOAD and COPY and REORG - they access database objects through Db2). That being the case, only the IDs of the Db2 database services and system services address spaces need to be RACF-permitted to use the key labels associated with encrypted Db2 data sets (if a "standalone" Db2 utility, such as DSN1PRNT, is to be run for an encrypted database object, the ID of that utility job will need RACF permission for the object's key label, as the standalone utilities operate outside of Db2).</span></li><li><span style="font-family: arial;">Plenty of people get data encryption and data masking mixed up. They are two different things (see below)</span></li></ul></ul><ul style="text-align: left;"><li><span style="font-family: arial;"><b>Column masks and row permissions -</b> This is another area I covered pretty thoroughly <a href="http://robertsdb2blog.blogspot.com/2013/04/db2-for-zos-goodbye-security-views.html"><span style="color: #2b00fe;">in a previous blog post</span></a>, and I won't repeat that content in this entry. What I will do is try to clear up some misunderstandings I've encountered over the years since Db2 introduced column mask and row permission functionality:</span></li><ul><li><span style="font-family: arial;">Data masking and data encryption really are two different things. One difference is that encryption is reversible (if you have access to the encryption key) while a data masking transformation can be irreversible (if, for example, a column mask changes a credit card number to XXXXXXXXXXX1234, there is no way for a user or a program to reverse those X's back to their pre-masked values).</span></li><li><span style="font-family: arial;">A Db2 column mask <u>changes no values in a table</u>; instead, the mask transforms the values in a column before they are returned to a user or a program; so, the actual unmasked values are in the table, but a column mask prevents a user (or a set of users, if it checks for a certain group ID) from being able to see the unmasked values. The masking is accomplished by a SQL CASE expression that is automatically added to a query when the ID (or group ID) of a query-issuing process matches one specified in the CREATE MASK statement.</span></li><li><span style="font-family: arial;">One of the nice things about a column mask is that it <u>doesn't</u> change values in a column of a table. That means a column mask will not affect a query's predicates (including join predicates) - those predicates will be evaluated using the unmasked values in the referenced column. The mask is applied (as previously noted) when the column in question appears in a query's select-list. If the mask changed values in a column, it could really throw query results out of whack in a bad way.</span></li><li><span style="font-family: arial;">Column masks and row permissions really are a very robust way to prevent access to certain data values (a row permission prevents a process with a certain ID or group ID from being able to access particular rows in a table, through addition of a row-filtering predicate to queries issued by an ID that matches one specified in the CREATE PERMISSION statement). Here's what I mean by that: the CASE expression associated with a column mask, and the predicate associated with a row permission, will be <i>automatically added to ANY query - static OR dynamic - issued by ANY ID specified in the CREATE MASK or the CREATE PERMISSION statement</i>. Does your ID (which I'll call SMITH) have SYSADM authority? Doesn't matter - if I have a column mask or a row permission that states that SMITH (or, maybe, any ID <u>other than</u>, for example, XYZ) cannot see certain rows in a table, or unmasked values in a certain column, you're not going to be able to access those rows or those unmasked column values. The Db2 privileges held by your ID are irrelevant.</span></li></ul></ul><p></p><p><span style="font-family: arial;"></span></p><p><span style="font-family: arial;">And that's a wrap for this part 1 blog entry. Check back in a couple of weeks for part 2, which will cover auditing, application architecture, test data management and RACF (or equivalent) management of Db2-internal security.</span></p><div><br /></div>Roberthttp://www.blogger.com/profile/02058625981006623480noreply@blogger.com5