Wednesday, April 29, 2020

Clearing the Air Regarding Db2 12 for z/OS and "Deprecated Objects"

On a number of occasions over the past several months, I have seen that there is some misunderstanding in the Db2 for z/OS community regarding support for certain types of database objects in a Db2 12 environment. Via this blog entry, I want to clear up these misconceptions.

The misunderstandings to which I alluded are, most often, related to traditional segmented table spaces. By "traditional segmented," I mean table spaces that are not simple, not partitioned and not universal (I say "traditional segmented" instead of just "segmented" because universal table spaces are themselves segmented). In its most extreme form, the misconception is expressed in this way (and in the quoted phrase below, "segmented" refers to what I call a "traditional segmented" table space):

"I hear that segmented table spaces are not supported in a Db2 12 environment."

Wrong, wrong, wrong.

Where did this misconception come from? Most likely, from a change introduced with Db2 12 function level V12R1M504 (or just M504, for short). OK, quick shot of background information: you are probably aware that with Version 12, Db2 for z/OS has gone to a "continuous delivery" mechanism for getting new functionality out to users. What does that mean? It means that, several times per year, new functionality for Db2 12 is introduced by way of the product's maintenance stream. In other words, Db2 12 fixes come out, and applying one of these fixes takes the code in a Db2 12 subsystem's load library to a new function level (fixes are tied to what IBM calls APARs, and indeed every Db2 12 function level from M501 upwards is associated with an APAR number, as indicated on the "What's new in Db2 12 function levels" page on IBM's Web site). Does that mean you need to be careful about applying Db2 12 fixes, out of concern that you might inadvertently take your system's Db2 code to a function level you're not ready to support? No, you don't need to worry about that. The reason: applying a fix that takes a Db2 12 subsystem's code to a new level doesn't change anything with regard to the subsystem's functionality. A new code level's functionality is activated only when - at a time of your choosing - someone issues an -ACTIVATE FUNCTION LEVEL command that specifies the function level in question.

Back to function level M504. A good bit of the information that went out about that function level - in presentations and such - made mention of "deprecated objects." That, in turn, caused a lot of people to think that (in particular) traditional segmented table spaces could not be used in a Db2 12 system with function level M504 (or higher) activated. As mentioned previously, some people even thought that traditional segmented table spaces could not be used at all in a Db2 12 environment, regardless of the activated function level.

Part of this confusion likely results from misinterpretation of that term, "deprecated." "Deprecated" does not mean gone; instead, it means that the deprecated thing (a feature, a ZPARM parameter, a type of database object, whatever) is a) not going to be enhanced going forward, and b) is likely to go away eventually. That "eventually" can be a long time, in some cases. Example: you lost the ability to create so-called simple table spaces (not segmented, not universal, not partitioned) back in the Db2 9 time frame, but existing simple table spaces can still be used, even in a Db2 12 system.

Here's the straight skinny regarding Db2 12 function level M504 and deprecated objects: when SQL DDL statements are issued via a package for which application compatibility level V12R1M504 or higher is in effect, non-universal table spaces cannot be created. [when a package's application compatibility level is V12R1M504 or higher, that also precludes creation of synonyms (we've been telling you for years to use aliases, instead) and hash-organized tables (those haven't been used by many organizations, and their benefits in a modern Db2 system tend to be marginal, at best).] Boom. That's it.

A package's application compatibility level is specified via the package BIND (or REBIND) parameter APPLCOMPAT, about which I wrote in an entry I posted to this blog last year. When you activate Db2 12 function level V12R1M504, does that mean you can no longer create non-universal table spaces (such as traditional segmented table spaces)? No. Remember, what matters here is the APPLCOMPAT value of the package through which a CREATE TABLESPACE statement is issued (that could be, for example, a SPUFI package or a DSNTEP2 package - common avenues for the issuance of Db2 DDL statements). You could have a Db2 12 system on which function level V12R1M506 is activated (that's the lastest function level available as of the posting of this blog entry), but if you're issuing CREATE TABLESPACE statements via a package bound with, for example, APPLCOMPAT(V12R1M503), you can create traditional segmented table spaces. When the package (or packages) through which you typically issue Db2 DDL statements is bound with APPLCOMPAT(V12R1M504) or higher (possible only if function level V12R1M504 or higher has been activated, as the APPLCOMPAT value for a package cannot be higher than the Db2 12 system's currently activated function level), does that mean that the package can no longer be used to create traditional segmented table spaces? No, that is not what it means. If a package (say a DSNTEP2 package) is bound with APPLCOMPAT(V12R1M504) or higher, and you need to use that package to create a traditional segmented table space, you can do that. How? Simple: prior to issuing the CREATE TABLESPACE statement, issue this statement:


Then, issue the CREATE TABLESPACE statement that creates the traditional segmented table space (a dynamic SQL-issuing program can use the SET CURRENT APPLICATION COMPATIBILITY statement to take its application compatibility level to a value below - but not above - the APPLCOMPAT value of the associated package).

And there you have it. Existing traditional segmented table spaces can still be used in a Db2 12 system, and they can be created in a Db2 12 system via a package with an APPLCOMPAT value of V12R1M503 or lower, or via a package with an APPLCOMPAT value of V12R1M504 or higher if, prior to issuance of the CREATE TABLESPACE statement, the package's application compatibility level has been taken below V12R1M504 via a SET CURRENT APPLICATION COMPATIBILITY statement.

Hope this helps.

Monday, March 30, 2020

Db2 for z/OS Online LOAD REPLACE - Minimizing Impact on Transactions

A couple of years ago, IBM introduced an important enhancement for the Db2 for z/OS LOAD utility, via the fix for an APAR (the APARs are PI69095 for Db2 12, and PI67793 for Db2 11). This enhancement provided a capability that is popularly known as "online LOAD REPLACE," but is more formally referred to as LOAD REPLACE SHRLEVEL REFERENCE.  I'll provide an overview of this functionality in a moment, but I'll first get to the impetus for my writing this blog entry: recently I've worked with Db2 people at a pair of sites, helping them to answer this question: How can I execute an online LOAD REPLACE with minimal impact on transactions that concurrently access the target table? The answer to that question involves utility control options that will likely be recognized by people who are familiar with IBM Db2 online REORG processing.

OK, the scenario that the online LOAD REPLACE enhancement was designed to address: suppose you have a need to periodically "switch out" data in a table. In other words, you want the set of rows now in table T1 to be replaced by another set of rows. This might be done, for example, on a quarterly basis if table T1 contains data for the most recently completed quarter. You could of course use traditional LOAD REPLACE functionality to get the task done, but with that approach the table will be inaccessible to application programs for the duration of the LOAD REPLACE job, and it the amount of data you're putting into the table via LOAD REPLACE is large, and if several indexes are defined on the target table, that period of table inaccessibility could be unacceptably long.

How about using clone table functionality for this purpose? Yes, that could be an appropriate option. Clone table functionality, introduced with Db2 9 for z/OS, allows you to create a clone of a given table (via a DDL statement of the form ALTER TABLE... ADD CLONE). Once a clone has been created for table Tab1 (I'll call the clone Tab1_CLONE, though there is no need for "clone" to be in the object's name), you can put data in it any way you want - via LOAD or INSERT - with no impact whatsoever on access to Tab1. Once Tab1_CLONE contains the data for which you want the data now in Tab1 to be switched out, you issue the SQL statement EXCHANGE DATA for the target table, and as soon as Db2 can get a drain on that table (which should not take long if transactions accessing the table are committing frequently), the switch happens and the next time a program issues a SQL statement referencing table Tab1, that table name will resolve to the physical object that had been called Tab1_CLONE prior to execution of the EXCHANGE DATA statement (and the physical object that had been called Tab1 prior to execution of the EXCHANGE DATA statement will subsequently be called, in this example, Tab1_CLONE). To put this another way, from a transaction program's perspective table Tab1 contained a certain set of rows a few seconds ago, and now (following execution of the EXCHANGE DATA statement) Tab1 contains a different set of rows.

Problem solved, right? Not necessarily. You see, a lot of Db2 for z/OS DBAs don't much like clone tables, and I get that. The concern boils down to this: once a clone has been created for a certain table, there are a number of things you can't do with the table without first dropping the clone, necessitating a re-creation of the clone afterwards. A prime example of this hassle factor: what would be a simple ALTER of a table becomes a more convoluted process if said table has a clone (I wrote about such a scenario in an entry I posted to this blog some years ago). Trying to avoid clone-related complications, some DBA teams came up with data switch-out processes that involved two different tables and use of the SQL statement RENAME TABLE. The main problem with that approach, aside from complexity, is the fact that RENAME TABLE leads to invalidation of dependent packages. Ugh.

Enter online LOAD REPLACE functionality, which has been described as "clone table functionality without the hassles of clone tables." When you run LOAD for a table using the REPLACE option and SHRLEVEL REFERENCE, the table will be placed in read-only mode for application access (programs that access tables for which a data "switch-out" is periodically required are typically of the data-retrieval variety, anyway), and the input data for the utility will be loaded into "shadow" objects corresponding to the table, its table space and its indexes. When the shadow objects are all loaded, online LOAD REPLACE does what Db2 online REORG does - it goes through the SWITCH phase, after which references to what had been the original table (and table space and indexes) resolve to the "new" original objects (which, prior to the SWITCH phase of the utility, were the shadow objects). The SWITCH phase of online LOAD REPLACE generally completes very quickly.

So there you have it, right? An easy, quick (from an application program's perspective) way to switch out data in a table for other data, right? Well, in fact some contention issues can arise when an online LOAD REPLACE job is executed. Twice over the past few months I have had Db2 for z/OS people contact me with questions about application disruption related to execution of the LOAD utility with REPLACE and SHRLEVEL REFERENCE. Similar points came up in these discussions, and I want to share pertinent information with you in the remainder of this blog entry.

The problem the aforementioned Db2 people faced was this: when an online LOAD REPLACE job was executed, some transactions would time out. The "why" of those timeouts has to do with Db2 claims and drains. Every program process that accesses Db2 for z/OS data has to get a claim (read or write) on every table space or index it accesses. Claims acquired by an application process are released when the process commits. When a Db2 utility requires exclusive access to an object (as a LOAD REPLACE SHRLEVEL REFERENCE job does when it is time to do the data set switch between the "original" and shadow data sets), it requests a drain on that object. Once a drain has been requested, in-flight units of work associated with programs accessing the target object are allowed to run to a commit point, but new units of work that will access the object are prevented from acquiring the necessary claim, effectively holding up their execution. If these held-up transactions have to wait too long on a claim, they will time out. Will that happen because a Db2 lock timeout value is reached? Possibly, but more probably the concern is a timeout value enforced by an application server. In this era of transactions that can be initiated at any time by someone using a smartphone app, application-side timeout values can be quite stringent - 10 seconds, for example, is not unusual (if a transaction accesses a data server such as Db2 for z/OS, and the application server waits for 10 seconds without getting a response, the application server will time the transaction out).

Why might it take more than a second or two for a Db2 utility, such as an online LOAD REPLACE that is ready for its SWITCH phase, to get the drain it needs to proceed? Won't in-flight transactions reach a commit point very quickly, since they tend to be sub-second in terms of elapsed time? Sure, most - maybe very nearly all - of the in-flight transactions accessing a table space or index (or a partition of same, if the object is partitioned) on which a utility wants a drain will get to a commit point very quickly, but all it takes is one transaction that's taking its time before committing to cause other transactions to be held up long enough to be timed out. As long as any ONE claim on a Db2 page set or partition (table space or index) has not been released via commit, the drain requested by a utility such as LOAD REPLACE SHRLEVEL REFERENCE (at SWITCH phase time) will not be acquired. When a not-committing-any-time-soon process is in the picture, something's gotta give. Either the utility will give up it's drain request (or will fail because it waited too long to get the drain), or transactions held up because of the drain will time out.

Here is where, in the case of an online LOAD REPLACE, you have to answer a question: what is your priority? Is it getting data in the target table switched out by time X, no matter what? Or, if you have at least some wiggle room regarding the time at which the data switch-out for the table is to be accomplished (at least a few minutes, let's say), is the priority on getting the data switch-out done with minimal (preferably zero) application impact in the form of transaction time-outs? If the priority is getting the data switch-out done by time X, no matter what, the probability of at least a few transactions timing out is going to be greater. If, on the other hand, the data switch-out for the target table needs to be accomplished not more than 10 minutes (for example) after time X, and transaction time-outs must be minimized (ideally, eliminated), you can provide the online LOAD REPLACE job with some flexibility regarding drain acquisition when it's time for the SWITCH phase.

How is that flexibility implemented? By way of the same options available when you're executing a Db2 online REORG utility. I'm talking about the utility options DRAIN_WAIT, RETRY, and RETRY_DELAY:

  • DRAIN_WAIT - If transaction time-out limits are really stringent (and again, that is very often enforced at the application server level) and time-outs must be avoided if at all possible, you might want to go really low with this value - maybe something like 8 seconds. If you do that, and an online LOAD REPLACE requests a drain on a target object in order to perform a data set switch with the associated shadow object, and the drain has not been acquired within 8 seconds (perhaps because some transaction that can run long but is infrequently executed is accessing the object and hasn't committed), the utility will give up on trying to get the drain. The thinking here is that we may be able to avoid transaction time-outs if transactions will be held up by a utility-driven drain request for no more than 8 seconds. Will giving up on a drain request cause the utility to fail? No - not if the utility can try again (see the next item, below).
  • RETRY - If you make the drain wait limit really short, thereby increasing the chance that the drain won't be acquired within the specified wait time, use RETRY to allow the utility to try again for the drain a little later. A RETRY value of 20, for example, would give the online LOAD REPLACE job quite a few chances to acquire the drain needed for SWITCH processing. How long will the utility wait to try again for the drain, if at first it doesn't succeed? That's determined by the setting of the next item, below.
  • RETRY_DELAY - If the online LOAD REPLACE tries and fails to get the drain it needs to get the SWITCH phase done, trying again immediately for the drain might not make sense. Better to give the process that prevented drain acquisition a little time to complete and get out of the way; but, don't wait too long - we haven't got all day to get the job done. Maybe waiting 20 seconds between successive attempts to get the drain needed for utility completion would make sense (in that case you'd specify RETRY_DELAY 20).

OK, so online LOAD REPLACE is a very nice Db2 for z/OS utility enhancement. That said, it doesn't do what it does using pixie dust. When the shadow table space and indexes are loaded and ready for switching for the corresponding "original" objects, the online LOAD REPLACE job needs to have - briefly - exclusive access to the "original" objects that are perhaps being concurrently accessed by transactions. Exclusive access is achieved via a drain request, and that drain request will hold up incoming transactions that are going to access the target objects. Transactions can be timed out if they are held up too long. Minimizing transaction impact, then, can necessitate a really short drain wait time for the utility. That short drain wait time increases odds of drain acquisition failure, but let the utility try again for a the drain a few times, with a reasonable time between successive drain requests, and you can have your cake and eat it, too: switch-out of data in a table (even involving a whole lot of data) accomplished easily, with minimal - quite possibly zero - time-out impact on transactions concurrently accessing the target table.

Friday, February 28, 2020

Db2 for z/OS: Clearing Up Some Matters Pertaining to 10-Byte RBA and LRSN Values

At this time, many Db2 for z/OS-using organizations have already completed their migration to Db2 12. Others are still in the process of accomplishing that migration, or are finalizing migration plans. Especially at sites at which migration to Db2 12 is not yet a fait accompli, I have seen that there is often some degree of confusion pertaining to "extended" RBA and LRSN values, particularly with regard to what needs to be accomplished prior to migrating from Db2 11 to Db2 12, and what actions should be taken in which order. Through this blog entry I hope to bring a measure of clarity to the topic.


As is typical for a database management system, Db2 for z/OS records information about database changes in its log. This is done to enable things like recovery of objects in a database, and rollback of data changes made by a transaction that terminates abnormally. While Db2's log exists physically in some number of active and archive log data sets, logically it is essentially a single large (maybe VERY large) file. In that single logical log file, Db2 records the location of individual records by their position in the log - a position known as the relative byte address, or RBA. That is literally the number of bytes into the (potentially very large) single logical log file at which a record can be found.

For years and years, log RBA values were stored in a six-byte field. Six bytes allowed for 256 TB of data to be recorded in the Db2 log, and way back around 1983, when Db2 for z/OS (then called Db2 for MVS) came to market, that seemed like plenty of capacity. Db2 ended up being used for a lot of very large databases that were characterized by very large volumes of data-change activity, and it eventually became clear that the Db2 log needed more than 256 TB of space for records - a LOT more. Db2 11 addressed that need by enabling a transition from 6-byte to 10-byte RBA and LRSN values (LRSN values pertain to Db2 data sharing groups - more on that in a moment). A 10-byte RBA value provides 1 yottabyte of log capacity (2 to the 80th power). Log space problem solved, once you transition to the 10-byte values.

And how do you make that transition? There are two aspects of the task - what you might call a system aspect and a database aspect. The system aspect involves getting a Db2 subsystem to use 10-byte RBA values in the records it writes to the log. That is done by running a Db2-provided utility, called DSNJCNVT, that converts the Db2 bootstrap data set, or BSDS, to accommodate 10-byte RBA (and LRSN) values (the BSDS contains, among other things, information about a Db2 subsystem's log). This BSDS conversion utility can be executed any time after the associated Db2 11 subsystem is in new-function mode (NFM). Here we come to an important point, and to a clarification I want to communicate: the BSDS must be converted to accommodate 10-byte RBA (and LRSN) values before you migrate a Db2 11 subsystem to Db2 12, and that is the only action pertaining to extended RBA (and LRSN) values that must be completed prior to migrating to Db2 12. In other words, the system part of getting to 10-byte RBA (and LRSN) values must be taken care of prior to migrating from Db2 11 to Db2 12, but the database part of transition to extended RBA (and LRSN) values can be addressed following migration to Db2 12.

Why I mentioned "clarification" in the preceding paragraph: I've found that a number of Db2 people think that both the system and database parts of extended RBA (and LRSN) transition have to be accomplished prior to migrating a Db2 11 subsystem to Db2 12. NOT TRUE - it's just the system part (BSDS conversion) that has to be done before you can migrate to Db2 12 from Db2 11.

OK, so that should make things pretty straightforward, right? You get your BSDS converted to accommodate 10-byte RBA (and LRSN) values, and then you can tackle the database part of extended RBA (and LRSN) transition at a time of your choosing, either before or after migrating to Db2 12, right? Well, maybe not so fast there. There is an important consideration regarding the timing of BSDS conversion versus database conversion that is relevant in a standalone Db2 environment and not relevant in a Db2 data sharing environment. I'll explain this critical distinction below.

The database part of extended RBA (and LRSN) transition, and the data sharing difference

First, what is this "database part" of extended RBA (and LRSN) transition to which I've been referring? Simple: in every page of every database object (table space or index), Db2 records the point in the log at which data in the page was last updated (among other things, that page-level information is used by Db2 for lock avoidance purposes). The particular field in a table space or index page in which this information is recorded is called the PGLOGRBA field. Even if Db2 is using 10-byte RBA (and LRSN) values in records it writes to the log, if the PGLOGRBA field is using 6-byte values, and we reach the limit of those 6-byte values, the data in the database objects can't be updated. Not good. That means that we have to change the database objects so that the PGLOGRBA field can contain 10-byte values. That change is generally accomplished via online REORGs of table spaces and indexes (it can also be accomplished by other utilities that involve creation of new data sets to replace the old, such as LOAD REPLACE and REBUILD INDEX). Db2 provides a job, DSNTIJCV, that will convert the catalog and directory objects to accommodate 10-byte PGLOGRBA values, and you can get this done for user and application table spaces and indexes by executing online REORG (or LOAD REPLACE or REBUILD INDEX) with the RBALRSN_CONVERSION EXTENDED option (or with the ZPARM parameter UTILITY_OBJECT_CONVERSION set to EXTENDED or NOBASIC). Note that if you want to "keep score," with respect to progress made in converting table spaces and indexes to accommodate 10-byte PGLOGRBA values, you can do that via the RBA_FORMAT column of the SYSTABLEPART and SYSINDEXPART tables in the Db2 catalog.

So, what about that "timing" thing I mentioned previously? That has to do with converting the BSDS to accommodate 10-byte RBA (and LRSN) values (the "system" part of transition to 10-byte RBA/LRSN values) and converting database objects to accommodate 10-byte values in the PGLOGRBA field in table space and index pages (the "database" part of transition to extended values). Which should you tackle first? Well, the system part, right, because that's required prior to migration to Db2 12, while the database part can be taken care of either before or after migration to Db2 12 (or some before and some after migration), right? In fact, the proper way to proceed, with regard to the order in which you address the system and database parts of transition to extended RBA/LRSN values, depends in large part on whether the Db2 subsystem in question is operating in standalone mode or as a member of a Db2 data sharing group.

What does Db2 data sharing have to with this? Here is the key distinction: in a Db2 data sharing environment, RBA values are NOT stored in the PGLOGRBA field of a table space or index page; instead, LRSN values are stored in that field. LRSN stands for log record sequence number. It's a timestamp-based value, and we need to use that for log record location in a Db2 data sharing system because in such an environment it is very common for a given database object to be updated concurrently by processes running on different members of the data sharing group. Each Db2 group member writes records to its log data sets that pertain to database changes made by processes running on that member, and each member is still writing RBA values to its log, but different members of the data sharing group will have different (often WAY different) RBA values, and what's needed is a COMMON expression of a log point, and LRSN values provide that consistency of log point references across members of the data sharing group; thus, in a data sharing group a Db2 member will write LRSN values as well as RBA values to its log, whereas the PGLOGRBA field in table space and index pages in a data sharing environment will contain LRSN values instead of RBA values. Make sure that you understand this: in a Db2 data sharing environment, log RBA values are NOT found in the pages of database objects, whereas in a standalone Db2 system log RBA values ARE recorded in the PGLOGRBA field of table space and index pages.

With that established, here's the implication for Db2 systems operating in standalone mode: when the BSDS is converted to use 10-byte RBA values, the pace at which the RBA value advances will accelerate. Why? Because the RBA values being written to the log are now 4 bytes longer than they were previously. Will the change in the pace of increase for RBA values be pretty small, or fairly large? That depends on the nature of the log records. If data-change activity in a Db2 system primarily involves inserts, and if inserted rows are rather long, the addition to the length of the typical log record due to the extra 4 bytes of the RBA value will be relatively small. If, on the other hand, data change activity in the system is primarily related to updates, and if the associated log records are pretty short (Db2 might be logging just the change of a single column caused by an update), the increase in the size of the log records caused by the extra 4 bytes of the RBA value can be quite significant.

Alright, so the pace of RBA value-increase quickens after conversion of the BSDS. Is that something about which you should be concerned, assuming that you're running Db2 in standalone versus data sharing mode? Yes, if your system's RBA value is getting pretty close to the 6-byte limit. You can check the latest log RBA value on your system via the Db2 command -DISPLAY LOG, and if the high-order value of the 6-byte RBA field is 'C' or less (hexadecimal format), you likely have a good bit of headroom. If, on the other hand, you see that the high-order value of the 6-byte RBA field is 'D' or higher, you'd best get cracking on avoiding problems related to hitting the 6-byte limit. In that situation, converting database objects to accommodate 10-byte values in the PGLOGRBA field (the database part of transition to extended values) BEFORE converting the BSDS could be a good idea, because you'll maximize the time available to you to accomplish the conversion of database objects (because you haven't sped up the advance in RBA values by converting the BSDS). If your system's 6-byte RBA value is a good ways off from the limit, you could convert the BSDS prior to converting table spaces and indexes, because you're not looking at a crunch-time situation.

In a data sharing environment, go ahead and convert the BSDS first. Yes, that will cause the advancement of RBA values for the Db2 member subsystem to accelerate, but so what? RBA values are not recorded in the PGLOGRBA field of table space and index pages (LRSN values are there, instead), and the conversion to 10-byte RBA values means that the system's logging capacity has been enormously increased.

Even if you are running Db2 in data-sharing mode, or even if (in a standalone Db2 environment) you're a long way from hitting the 6-byte RBA limit, you should get the database part of the transition to 10-byte RBA/LRSN values done within a reasonable time frame - like, maybe within the next year or so. Why? Because eventually that will have to get done, and you don't want the task to be put on the back burner and subsequently forgotten. Get catalog and directory objects converted (using the Db2-supplied job to which I referred above), and use online REORG (or LOAD REPLACE or REBUILD INDEX) to convert user and application table spaces, and track progress (as previously noted) via the RBA_FORMAT column of the SYSTABLEPART and SYSINDEXPART catalog tables. Once you're done, you'll never have to worry about this again - and that would be a good thing.

I hope that the information I've provided in this blog entry will be useful for you. For more information on extended RBA and LRSN values, and on the transition to same, see the excellent and comprehensive write-up in section 3.1 of the IBM rebook titled, IBM DB2 11 for z/OS Technical Overview (download-able from

Thursday, January 30, 2020

Db2 for z/OS and RACF, Part 2: Db2-Internal Security

In the Part 1 post of this two-part blog entry, I described how RACF can be used to provide "external" security for a Db2 for z/OS system - external security being concerned with who (i.e., which IDs) can connect to the Db2 system, and how (e.g., in the form of a batch program and/or through CICS and/or as a DRDA requester and/or as a REST client, etc.). That is a very common use of RACF in a Db2 security sense. RACF can optionally be used to also manage Db2-internal security, which is all about what one can do (i.e., what a process with a given ID can do) when one has successfully connected to a Db2 system. This Part 2 blog entry is focused on that second topic: using RACF to manage Db2-internal security. [Note: as in the Part 1 entry, I will be referring explicitly to IBM's RACF - if you use another vendor's z/OS security management product, you can check with the vendor to see how what I have written about RACF pertains to the alternate product.]

For quite a few years, the only way to manage Db2-internal security was through Db2 itself. This was done using the SQL statements GRANT and REVOKE. For example, if an application connecting to Db2 using ID XYZ needed to be able to read data from table T1 using dynamic SQL statements, a DBA would execute a statement like this one:


Several Db2 versions ago, a new option for managing Db2-internal security was introduced: you could do that with RACF, via a Db2-provided exit routine called DSNXRXAC. Before getting further into that, I'll bring up the obvious question: why would an organization want to use RACF to manage Db2-internal security, given that the job could be effectively done with Db2 GRANTs and REVOKEs? I have seen that the RACF route is usually taken because a decision has been made that one team will be responsible for both external and internal Db2 for z/OS security. In such a case, the one team will likely choose the RACF-based approach because RACF can be used for external and internal Db2 security (thanks to the aforementioned Db2-supplied exit), while Db2's built-in functionality is aimed at managing only Db2-internal security.

So, if RACF is going to be used to manage Db2-internal security, you implement the DSNXRXAC routine and that's it, right? Nope. There's more to be done. Why? Because when a Db2 thing requiring some privilege or authority is to be done, Db2 drives the RACF exit and essentially asks RACF whether ID X can do action Y. To answer that question, RACF needs to see that ID X has been RACF-permitted to perform action Y. If RACF has not been set up to manage authorization for performing Db2 action Y, RACF will defer to Db2 ("This doesn't appear to be in my purview, Db2 - you make the call"), and Db2 will check to see if the required privilege or authority has been provided for ID X through a SQL GRANT statement.

How does RACF get set up to manage Db2 privileges and authorities? That involves activating various RACF classes and defining profiles in those classes. Consider the privilege of reading data from a table using a dynamic SQL statement. Permission to do that, from a RACF perspective, requires that a RACF profile, specifying the SELECT privilege for the table in question, be defined in the RACF class MDSNTB (and that RACF class has to be active). Additionally, the ID needing the privilege has to be RACF-permitted to access the profile associated with the privilege. Another example: if a user needs to be able to execute a certain Db2 package then on the RACF side a profile is defined for the execute privilege on the package in question (or perhaps for that package and all others in the associated package collection), and that profile is defined in the RACF class MDSNPK, and that RACF class has to be active. On top of that, the user's ID (or the ID of an application process that needs to be able to execute the package) is RACF-permitted to access the aforementioned profile covering the EXECUTE privilege on the package (or on all packages in the collection, if that is how the RACF profile was defined).

Got it? In RACF, profiles are defined - in the appropriate RACF classes - that cover specific privileges that can be provided for specific resources (e.g., SELECT on table T1, EXECUTE on package PKG1, etc.), and IDs are RACF-permitted to access the various profiles as needed. Information on all of this - the RACF classes that cover various Db2 privileges and authorities, defining profiles, permitting access to profiles, and more - can be found in a Db2 for z/OS manual titled RACF Access Control Module Guide. The information in that manual is also available online, in the Db2 for z/OS Knowledge Center on IBM's Web site, at

I want to wrap up this entry with information about a couple of important enhancements pertaining to RACF management of Db2-internal security, both of which were delivered with Db2 11 for z/OS. The first of these enhancements has to do with the way that RACF would do authorization checking for a package auto-rebind situation. Consider this scenario: package PKG1 is dependent on table T1. A DBA alters T1 in a way that invalidates PKG1. A request to execute PKG1 comes along, and because PKG1 has been marked invalid by Db2, an attempt is made to auto-rebind the package. Historically, in a situation in which RACF is managing Db2-internal security, the Db2-RACF authorization exit is driven, and RACF checks to see if the ID of the process that wants to execute PKG1 has the privileges needed to successfully rebind the package. That was a problem, because the ID that wants to execute the invalidated PKG1 (thereby triggering the auto-rebind) almost certainly does NOT have the privileges needed to successfully rebind the package. More than likely, those privileges are instead held by the ID of the OWNER of the package. So, ID APPL1, which wants to execute the invalidated PKG1, is checked for privileges needed for a successful rebind of PKG1, and because those privileges are not held by ID APPL1 the auto-rebind fails, and PKG1 ends up getting marked "inoperative" by Db2. Bummer. To address that situation, Db2 11 for z/OS introduced a new ZPARM parameter called AUTHEXIT_CHECK. If that parameter is set to its default value of PRIMARY, and an auto-rebind operation occurs and RACF is managing Db2-internal security, the primary authorization ID of the process that triggered the auto-rebind (by attempting to execute an invalidated package) will be checked by RACF for privileges needed to successfully complete the rebind of the package in question. If AUTHEXIT_CHECK is set to DB2, in the auto-rebind scenario RACF will check to see if the ID of the package OWNER has the privileges required for a successful rebind of the package. Problem solved.

The second of these Db2 11-delivered enhancements pertaining to RACF management of Db2-internal security has to do with in-memory caches of authorization information that Db2 maintains to speed up authorization checks. Consider this scenario: ID SMITH has the EXECUTE privilege for package PKG1, and Db2 has that information stored in the package authorization cache for quick look-aside reference (the size of this cache is specified via the CACHEPAC parameter in ZPARM). RACF is being used to manage Db2-internal security, and on the RACF side the ID SMITH is de-permitted access to the profile that relates to the EXECUTE privilege on PKG1. The RACF change is made active via a SETROPTS RACLIST REFRESH command, and then ID SMITH tries to execute PKG1 again, and SMITH is successful in doing that. How can that be, given that SMITH was de-permitted access to the profile relating to the EXECUTE privilege on PKG1? SMITH can still execute package PKG1 because the information in Db2's in-memory package authorization cache has not been changed to reflect the change in package execution authority made on the RACF side. Bummer. To address that situation, the ZPARM parameter AUTHEXIT_CACHEREFRESH was added with Db2 11 for z/OS. When the value of that ZPARM is set to ALL (versus the default value of NONE), authorization information cached in memory by Db2 (in the package cache or the routine cache or the dynamic statement cache) will be updated to reflect RACF-side changes after those changes have been RACF-activated via a SETROPTS RACLIST REFRESH command. Problem solved.

Those two Db2 11-introduced ZPARMs eliminated just about the last two irritating snags associated with RACF management of Db2-internal security. A growing number of organizations are managing both external and internal Db2 security through RACF. It's a totally viable approach, so don't fret if you learn that your organization wants to go this route. Sure, there's some set-up work that has to be done, and RACF people will need to pick up some Db2 terminology and vice versa, but your Db2 security situation will continue to be robust and manageable.

Monday, December 30, 2019

Db2 for z/OS and RACF, Part 1: External Db2 Security

In a Db2 for z/OS context, RACF (the IBM security management subsystem for z/OS systems) can serve two important purposes, providing security for a Db2 environment in an external and an internal sense. Db2 external security is about determining which users/processes can connect to a Db2 subsystem, and how. Internal security concerns the Db2-related things that a user/process can do once the user/process has connected to a Db2 subsystem. External security for a Db2 subsystem is almost entirely a RACF matter, whereas internal security can be managed either through Db2 (largely via GRANT and REVOKE statements) or through RACF (via a Db2-supplied exit provided for this purpose). The overall subject is a little too broad for a single blog entry, so I'll tackle it in two parts. This first entry will focus on RACF in a Db2 external security sense. Part two, which I'll post within the next 3-4 weeks, will cover RACF management of Db2-internal security (which, again, is an alternative to Db2 management of Db2-internal security).

[Note: in this blog entry (and the next) I will focus exclusively on RACF, versus alternative z/OS security management products available from other vendors. If you use such an alternative product, you can check with the appropriate vendor to see how the RACF features and functions that I describe herein map to the alternate product's capabilities.]

With regard to RACF-provided Db2 external security, there is first of all a fundamental area of RACF functionality of which most every Db2-using organization makes use. Here, I am referring to the ability RACF provides to create (and authenticate) user profiles - basically, the ability to define and manage authorization IDs in a z/OS system. These are often referred to as "user IDs," but a user ID may or may not be associated with a user who is a person - it may be an ID associated with an application process. When a user or an application process wants to connect to a Db2 for z/OS subsystem, it presents an ID and an associated authentication credential (that credential is typically a password, but it could be a certificate - more on the latter below). When the ID and password are presented, they are usually processed through one of two Db2-supplied exits, called the sample connection exit (named DSN3SATH) and the sample sign-on exit (named DSN3SSGN). These exits are referred to as "sample" because they are provided in source form, and that source code can be modified if desired or required (note that when the sample connection and sign-on exits are used, from an execution perspective they are named DSN3@ATH and DSN3@SGN, respectively). The connection exit is driven when a subsystem/started task (e.g., CICS or IMS) or a user or a process wants to connect to a Db2 subsystem, and the sign-on exit is driven when a process (usually a CICS or an IMS transaction) wants to access Db2 through a subsystem/started task (e.g., CICS or IMS) that is already connected to the Db2 subsystem in question.

[As noted, passwords are typically used to authenticate presented IDs because that is a very easy thing for a security administrator to set up. If, however, there is a desire or a need to use certificates instead of passwords for ID authentication, that support has been there since Db2 10 for z/OS. Information on using certificates for ID authentication in a Db2 context can be found in an IBM "red paper" titled, DB2 for z/OS: Configuring TLS/SSL for Secure Client/Server Communications (that paper can be downloaded from the IBM Web page at]

When the Db2 sample connection or sign-on exit is driven, two main things happen: first, Db2 calls RACF to verify that the presented ID is valid and that the accompanying authentication credential (password or certificate) is in fact the one that is associated with the ID - if that user verification test fails, the connection or sign-on request is rejected. Secondarily, Db2 will request that RACF provide a list of all the group IDs (if any) to which the presented user ID is connected. These RACF group IDs become the list of Db2 secondary authorization IDs with which the primary authorization ID is associated (secondary authorization IDs are very helpful in managing Db2 privileges - when a privilege is granted to a RACF group ID, it is available to all the associated user IDs, saving the trouble of granting the privilege separately to each of those individual user IDs).

OK, that's basic RACF external security for Db2: no valid user ID (or lack of the required password or certificate), no connection to the Db2 subsystem. Suppose, though, that you need connection-permission security to be handled in a more granular fashion. For example, you may have a need for ID SMITH to be able to connect to a Db2 subsystem called DBP1, but only as a DRDA requester, or only as a REST requester, or only by way of TSO. In other words, SMITH can connect to DBP1 this way, but not that way. If such a need exists, RACF can do the job, but a little more set-up is required. For one thing, a RACF general resource class called DSNR has to be active. The DSNR resource class pertains specifically to Db2 connection permissions from a RACF perspective, and activating that resource class enables one to be more granular in the management of Db2-external security.

With the DSNR resource class activated, a security administrator can set up various profiles that enable management of Db2 connection permissions based on particular "come-from" environments. The available profiles of this nature are listed below (and one would substitute for "ssn" the name of the Db2 subsystem for which connection permission is being managed):
  • ssn.MASS for IMS (MASS is a reference to multi-address space security - access to Db2 via IMS typically involves several IMS address spaces) 
  • ssn.SASS for CICS (SASS is a reference to single-address space security)
  • ssn.RRSAF for the Recoverable Resource Services access facility
  • ssn.BATCH for the TSO attachment facility, the call attachment facility (aka CAF) and Db2 utilities
  • ssn.DIST for the DDF address space
  • ssn.REST for access via Db2's REST interface

Once a protected access profile has been defined for a Db2 subsystem, IDs can be permitted (in RACF terminology) to access the named Db2 subsystem by way of the specified access profile. To get a better handle on how all this comes together, consider a situation in which a user with ID SMITH wants to access a Db2 subsystem called DBP1:
  • If the RACF resource class DSNR has not been activated then from a RACF perspective SMITH can access DBP1 via any type of "come-from" environment (e.g., via TSO, a batch job, as a DRDA requester, etc.), though of course SMITH will not be able to get into the z/OS system itself unless RACF has determined that SMITH is a valid ID and that the required password (or certificate) has been provided for or by SMITH.
  • If the RACF resource class DSNR is active and a profile named DBP1.BATCH has been defined, SMITH will not be able to access DBP1 via TSO or a batch job or a Db2 utility unless the ID SMITH has been permitted to use the DBP1.BATCH profile (alternatively, SMITH could access DBP1 via TSO or a batch job or a utility if SMITH is connected to a RACF group ID that has been permitted to use the DBP1.BATCH profile).

A little something extra to note about the ssn.DIST profile: if user SMITH can access DBP1 via a batch job (because SMITH has been RACF-permitted to do so), and SMITH submits a batch job that references a table in Db2 subsystem DBP2, that access to the table in the DBP2 subsystem will require the ID SMITH to be RACF-permitted to use the DBP2.DIST profile on the z/OS system in which DBP2 runs (assuming that the DSNR resource class is active on that z/OS system, and assuming that DBP1's Db2 communications database has been set up so that DBP1 can be a DRDA requester to DBP2). In this case SMITH will not require RACF permission to use the DBP1.DIST profile, even though SMITH is using DBP1's DDF address space to get to the table in the DBP2 subsystem, because the DBP1.DIST profile is checked at connection-request time, and SMITH is already connected to DBP1 by way of a batch job (from a connection-request perspective, it is the DDF address space of DBP2 that is involved here, and that is why SMITH needs RACF permission to use the DBP2.DIST profile).

And yet one more thing about access to a Db2 subsystem via DDF: suppose you want to restrict SMITH's access to DBP1 so that connection is only permitted via DBP1's DDF address space (i.e., not batch, TSO, etc.) and only from certain IP addresses. Getting granular to that degree for connection-permission management requires some additional set-up actions. In particular, the RACF SERVAUTH class will need to be used, in combination with z/OS TCP/IP Network Access Control. Information on doing this can be found on this page in the Db2 for z/OS Knowledge Center on IBM's Web site:

Information on other matters I have covered in this blog entry, such as defining protected access profiles for a Db2 subsystem and permitting IDs to use those profiles, can be found here:

Information on the Db2 connection and sign-on exits can be found here:

I hope that this information will be useful for you. Tune in next month for a look at how RACF can be used to manage Db2-internal security.

Tuesday, November 26, 2019

Clearing Some Things Up Regarding the CICS-Db2 for z/OS Interface

Not long ago, a very experienced Db2 for z/OS person contacted me with some questions about the CICS-Db2 interface. He mentioned that he did this because he was having trouble finding the information he was seeking in the product documentation. I provided this person with answers to his questions, and then thought to myself, "If a veteran Db2 for z/OS technical professional was challenged in getting a clear picture of the CICS-Db2 interface, perhaps other people are in the same boat." In this blog entry, I'll try to explain how the piece parts of the relationship between a CICS region and a Db2 subsystem fit together, and in doing that I'll aim as well to clarify some of the terminology one might encounter in this domain.

The specific questions

There were a couple of questions that sparked my exchange with the Db2 person. First, he noted that when he and a CICS colleague of his displayed information about CICS-Db2 threads for a certain CICS region (using either a performance monitor or the Db2 command -DISPLAY THREAD), they'd see that a lot of the threads were in IDENTIFY status, and they weren't sure as to what that meant.

Second, they saw that the total number of threads appeared to equal the value of the TCBLIMIT parameter for the CICS region in question (more on TCBLIMIT to come), and they also observed that the TCBLIMIT values for the CICS regions that access a Db2 subsystem added up to a number that exceeded the CTHREAD value in the Db2 subsystem's ZPARM module, and this caused them some concern (CTHREAD is the maximum number of "local to Db2" threads - i.e., not DDF threads - that can be allocated at one time).

Where is the interface between a CICS region and a Db2 subsystem defined?

The veteran Db2 person who directed these questions to me referenced a parameter in "the RCT." I knew exactly what he meant, and "RCT" is an acronym often used in describing a CICS-Db2 set-up; however, it is a technically outdated term. Here's the deal: RCT, which is short for resource control table, refers to a macro that, long ago, provided the specifications for the interface between a CICS region and a Db2 subsystem. More than 20 years ago, the specifications pertaining to this interface moved from the RCT to CICS RDO, which is short for resource definition online. You have a DB2CONN resource in which things like the maximum number of CICS-Db2 subtask TCBs ("subtask TCBs" is explained below) and the maximum number of pool threads for the CICS region are defined, and one or more DB2ENTRY resources where a plan can be associated with a CICS transaction (or a group of transactions) and where the number entry threads for a transaction (or group of transactions) - including, perhaps, protected entry threads - is specified (another CICS resource, called DB2TRAN, can be used to associate a CICS transaction with a DB2ENTRY resource, when that transaction is not explicitly specified in the DB2ENTRY resource).

Answering the questions: IDENTIFY status

In spite of being something you see in a Db2 monitor display of thread information, or in the output of the Db2 command -DISPLAY THREAD, IDENTIFY status is something I think of more in terms of tasks than of threads. Here, I'm referring specifically to the subtask TCBs that are used by CICS-Db2 transactions. When such a task is in IDENTIFY status, I see it as being a task is known to Db2 - known, I would say, as essentially a connection from a particular CICS region to the Db2 subsystem. When a CICS-Db2 transaction executes, it uses a subtask TCB, and that TCB in turn uses a Db2 thread.

When a CICS-Db2 transaction finishes, the Db2 thread it used goes away (unless it was a protected thread), but the subtask TCB it used remains. In my view, a CICS subtask TCB used for CICS-Db2 transactions is in IDENTIFY status when it is not being used. Note that when a CICS-Db2 transaction uses a protected entry thread, the thread persists when the transaction completes, and remains associated with the transaction's subtask TCB, so that the thread can be reused for another execution of the transaction (more information on how protected threads drive CICS thread reuse can be found in an entry I posted to this blog a few years ago).

Answering the questions: CICS's TCBLIMIT and Db2's CTHREAD

In the DB2CONN resource for a CICS region, there is a value, TCBLIMIT, that indicates the maximum number of CICS-Db2 subtask TCBs that can exist for the CICS region. If all of those subtask TCBs are being used by transactions and another transaction request is received, that request will be queued to wait for a subtask TCB to free up.

I referred previously to the CTHREAD parameter in the ZPARM module of a Db2 subsystem. The value of CTHREAD, as noted, specifies the maximum number of "local to Db2" threads (i.e., not DDF threads) that can be allocated at one time. Threads used by CICS-Db2 transactions are among those considered to be be "local to Db2."

Can the sum of the TCBLIMIT values for all of the CICS regions connected to a given Db2 subsystem exceed the Db2 subsystem's CTHREAD limit? Yes, because at any given time there could be a number of subtask TCBs that aren't being used by transactions, and those subtask TCBs don't need threads (though, as mentioned, a protected thread will remain associated with a subtask TCB as long as the protected thread exists).

How many Db2 threads can a particular CICS region use at once? That would be the sum of the THREADLIMIT values for all the DB2ENTRY resources and for the DB2CONN resource for the region (THREADLIMIT for the DB2CONN resource refers to CICS pool threads), plus the value of COMTHREADLIMIT in the DB2CONN resource (this value pertains to CICS command threads). Note that the TCBLIMIT value in a CICS region's DB2CONN resource should not be smaller that that sum of THREADLIMIT values plus the COMTHREADLIMIT value - having enough CICS-Db2 threads for a CICS region is not of much value if there are not enough subtask TCBs that can be used for CICS-Db2 transactions (it should be OK if the value of TCBLIMIT is a little higher than the sum of the THREADLIMIT values plus the COMTHREADLIMIT value).

Another thing to consider in this area: have enough CICS-Db2 pool threads, if you have Db2 transactions that always use pool threads, or that can overflow to the pool if all the transaction's entry threads are in-use. I recommend issuing the CICS command DSNC DISPLAY STATISTICS, and examining the output, which might look something like this (I added color highlighting):

DFHDB2014 07/09/98 14:35:45 IYK4Z2G1 Statistics report follows for RCTJT
accessing DB2 DB3A
*COMMAND             1       1     1    1       0         0       0
*POOL    ********    4       1     0    1       0         2       0
XC05     TESTP05    22       1    11    2       0         7       5
XP05     ********    5       2     0    1       0         1       1

DFHDB2020 01/17/98 15:45:27 IYKA4Z2G1 The display command is complete.

The row I highlighted in red is for the pool threads. In that row, check the number in the W/P column (I highlighted the W/P heading in blue above), which shows the number of times there was a wait or an overflow to the pool. For the pool threads, overflow is not relevant because you're already in the pool, so a non-zero number under W/P for the pool indicates a number of waits, and that suggests that the demand for pool threads exceeded the supply at times. In that case, it might be a good idea to increase THREADLIMIT for the DB2CONN resource (and doing that could potentially lead you to increase the TCBLIMIT value in DB2CONN).

As always, thanks for stopping by. I hope that this information will be helpful to you.

Wednesday, October 30, 2019

Db2 for z/OS: The Potential Performance Impact of 100% CPU Utilization

Twice during the current calendar year, I've worked on a Db2 for z/OS performance issue that I've encountered at various times over the past couple of decades: a transactional Db2 for z/OS workload (versus batch - more on that in a moment) has periods characterized by major increases in response times, with related symptoms being high in-Db2 not-accounted-for time and high - sometimes very high - in-Db2 latch wait times (more on those metrics to come). Analysis of z/OS monitor data shows that the general-purpose "engines" (processors) of the z/OS system in question are running at or near 100% busy during the time of the performance slow-down, and then a disagreement might arise, with some people (me among them) saying that the 100% busy CPU situation is the root cause of the response time elongation, and others saying that this is not the case because a z/OS system, as they see it, should be able to deliver good application performance even with the general-purpose engines running at or near 100%.

In this blog entry, I am aiming to explain why you do not want to have your z/OS system's general-purpose engines running at 100% busy for a Db2 for z/OS transactional workload (zIIP engine utilization should be managed differently versus general-purpose engine utilization, as noted in an entry I posted to this blog a few years ago).

First, let's distinguish between "transactional" and "batch" workloads

When I refer to a transactional workload in this blog entry, I most fundamentally mean, "not batch," and with "batch" I am referring to a traditional (in a z/OS system) JES batch workload. This workload distinction is important for the following reason: in a situation in which a Db2 for z/OS workload is entirely (or almost entirely) of a batch nature, as might be the case during certain overnight hours for certain organizations, it can be OK to try to drive general-purpose engine utilization towards 100%. Why? Because for a traditional z/OS batch workload, maximizing general-purpose engine utilization can be a key factor in getting the batch jobs done within a certain time window (and that can be beneficial in a financial sense: your organization might have service level agreements with clients that stipulate that files sent to you by those clients will be processed before a certain time, with failure to achieve that objective potentially triggering financial penalties).

The transactional workloads to which I refer in this blog entry are those that have two main characteristics: 1) the units of work are relatively small in scale, versus the often-large units of work that are common for batch jobs; and 2) response time for individual transactions is of prime importance. For a batch workload, "response time" is not often part of the conversation - throughput is key (referring to getting a certain amount of work done within a certain time frame). For a transactional workload, it is often very important that individual transactions have very good response time, and when that is a performance goal then you do NOT want the general-purpose engines in your z/OS system running at or near 100% busy.

Second, there is 100% busy, and there is 100% busy

OK, so what am I saying with that odd-looking phrase? I'm saying that there may be a particular level of transactional work on your system that would take general-purpose engine utilization for the LPAR just to 100% busy, and it may be that performance for the transactional workload will be OK in that situation. Trouble is, you're quite likely to blow right past that workload level, and general-purpose engine utilization is still at 100% (it can't go higher) and performance (elapsed time for individual transactions) starts to get pretty bad.

Consider this example: suppose that, at 1000 transactions per second, the general-purpose (GP) engines of the z/OS LPAR are running at 90% busy, and performance is good. Suppose that the transaction rate goes to 1070 per second and GP utilization goes to 98% and performance is still OK. Maybe a transaction rate of 1090 per second takes GP utilization right to 100%, and performance is still OK. What happens when the transaction arrival rate (meaning the rate at which transactions come into the system) goes to 1100 per second, or 1150 per second, or 1200 per second? GP utilization is still 100% (again, it can't go higher), but performance (response times for individual transactions) is likely degrading, and it will keep getting worse as the transaction arrival rate continues to climb. The point is this: to say that a Db2 for z/OS transactional workload can get good performance with GP engines running at 100% is often, in a practical sense, what you might call an academic point, versus a real-world truism, because it is quite unlikely that a transaction arrival rate that just takes GP engine utilization to 100% will stay constant - either it will soon decrease to a level that takes GP utilization below 100% or it will soon increase to a level that will cause performance problems.

What does the performance data tell you?

If you encounter a Db2 transactional application performance problem (unacceptably high response times), and you think that an overloaded system might be the cause, check the utilization of GP engines during the period of the performance problem. This can be done using your z/OS monitor. If that monitor is IBM's RMF product, look at an RMF CPU Activity Report for the z/OS LPAR in which the Db2 subsystem is running (you can see an example of this report on a page in the z/OS Knowledge Center on IBM's Web site). The report typically shows activity in 15-minute intervals of time. For a given 15-minute interval, find the section with CPU utilization numbers. The processors identified as "CP" under the "TYPE" heading are general-purpose engines (zIIP engines are identified by "IIP" under the "TYPE" heading). For the general-purpose engines (TYPE=CP), look at the figure under the heading  "MVS BUSY" in the "TOTAL/AVERAGE" row. See if that value is at or near 100%.

If you see that GP utilization was at or close to 100% during the period of the Db2 transactional performance problem, look next at figures in an accounting report generated by your Db2 monitor. If your monitor is IBM's OMEGAMON for Db2, the report is called an Accounting Long report. You can see an example of that report on this page in the Db2 for z/OS Knowledge Center. [A quick word about this example: it shows the standard aggregation level for data summarization, which is primary authorization ID within plan name. That aggregation level can be useful at times, but my preference is aggregation at the connection type level, which you get when you specify ORDER (CONNTYPE) in the report control statement. When data is ordered by connection type, you get, within the larger report, a sub-report showing all activity in the FROM and TO time period for each connection type used during the period - for example, one sub-report showing all CICS-Db2 activity for the subsystem, one showing all call-attach activity for the subsystem, one showing all DRDA activity (the DDF workload) for the subsystem, etc. I like that aggregation level because if I'm investigating, say, a DDF workload performance problem then I look at the sub-report for the DRDA connection type.] Anyway, if you're looking at the example report pointed to by the preceding link, go to page 1-5 of that sample report. You'll see, at the top of that page, a couple of sideways bar charts. One of the fields of the right-side bar chart is NOTACC. That's in-Db2 not-accounted-for time. For the transactional workload for which there was a performance problem, check that value. If it's greater than 10%, that's an indication of an elevated level of wait-for-dispatch time in the z/OS system, and that, in turn, is an indication of overly-busy GP engines (for more information on in-Db2 not-accounted-for time, you can look at an entry I posted to the Db2 blog I maintained while working as an independent consultant, prior to rejoining IBM in 2010).

Something else to check in that accounting report: Db2 latch wait time, one of the so-called "class 3" suspension times provided in a Db2 monitor accounting report. Normally, latch wait time is a very small percentage of in-Db2 elapsed time for a transactional workload, and a big increase in latch wait time is an indicator of an overloaded z/OS system. In my experience, when highly elevated latch wait times are seen during a period in which Db2 transactional performance is degraded due to an overloaded z/OS system, the data is often misinterpreted. People will see, in a Db2 monitor-generated statistics long report (that may be called a statistics detail report, depending on the monitor used), a big increase in the number of what is labeled "latch counts" for one or more latch classes during the period of elongated transactional response times, and they'll say, "Wow - why are we getting so many more latch class 14 (or 23 or 25 or whatever) events during this period?" and they'll look for things that might be done to reduce those seemingly high latch counts. The thing is, what's being shown in the statistics report is not the count of latch requests, per se - it's the count of latch suspends, meaning, the number of times that a process was suspended while waiting to get a required latch (Db2 uses what are called latches internally to ensure serialization of certain activities that have to be serialized - an example is the latch required when Db2 splits an index page). See, when a z/OS system is not overloaded, a required Db2 latch is often acquired right away, with no need to suspend the associated application process. Conversely, when a z/OS system is overloaded (GP engines running at or near 100%), a single latch request can lead to several suspensions; thus, a big increase in "latch counts" does NOT necessarily mean that more latch requests occurred - it could well be that about the same amount of latch activity was accompanied by many more latch-related suspensions. The problem, in other words, is not more latch activity - it's more suspends related to latch activity, and the increase in latch suspends can very much be a result of an overloaded z/OS system.

Dealing with an overloaded z/OS system

I'd like you to be in a situation in which utilization of the GP engines of your z/OS system doesn't exceed 90% during periods of peak Db2 for z/OS transactional traffic, but maybe that's not the case at your site. What if you are dealing with GP engine utilization that gets to 100% or very close to that when you have a lot of transactional work going through the system, and you see during those times the often-related Db2 performance symptoms: elongated transaction response times, high in-Db2 not-accounted-for time, high Db2 latch suspension time and high "latch count" numbers? What can be done? Some thoughts:

  • Maybe you can reduce CPU consumption through performance tuning actions. Do you have a lot of memory on the system? Maybe you can reduce CPU by enlarging and page-fixing buffer pools, and backing those pools with large page frames. Maybe you can utilize high-performance DBATs via the Db2 profile tables. Maybe other performance tuning actions would get GP engine utilization below 100%. Worth a try.
  • Maybe you can keep transactions flowing quickly through the system by inducing some degree of transaction queuing during peak processing times. One way to do that would be to set MAXDBAT to a value that will keep the Db2 system from getting flooded with DDF transactions (that can be done in a more granular fashion - for particular DDF-using applications - by way of the Db2 profile tables).
  • Maybe you can adjust some dispatching priorities. Getting a z/OS system's workload manager (WLM) policy right is especially important when the system is really busy. I have some advice for priorities that pertain to a DDF workload.
  • Maybe you can add processing capacity. It's likely that, at various times in the past, your organization has increased mainframe processing capacity as application workloads have increased. Maybe it's time to do that again.

Can you run a transactional Db2 workload on a z/OS system on which GP engine utilization is at or near 100%? Yes, in the sense that the system won't fall over. Do you want to run a transactional Db2 workload on a z/OS system on which GP utilization is at or near 100%? I would say, "No." Tuning actions, efficient application program code, proper workload prioritization - all are important for good performance. So is processing capacity - have enough of it.