Wednesday, May 24, 2017

DB2 for z/OS and Data-as-a-Service and Database-as-a-Service

______-as-a-service is all the rage in IT these days, and understandably so -- the "service-ization" of information technology capabilities and interfaces will be transformational; indeed, transformations have already occurred or are underway at many organizations and within many application development and deployment environments. In this blog entry I want to highlight a couple of ______-as-a-service concepts, data-as-a-service (aka DaaS) and database-as-a-service (aka DBaaS), and examine their relevance to DB2 for z/OS. First, a little level-setting terminology clarification is in order.

Data-as-a-service and database-as-a-service -- NOT the same thing

Some folks use the terms "data-as-a-service" and "database-as-a-service" interchangeably, but they do not have the same meaning. The distinction is indicated by the different first words of the terms. When your're talking database-as-a-service, you're talking about the functionality of a database management system, provided as a service. So, what does that mean? Well, it can mean that the database management system in question is deployed in an off-premise cloud environment, but database-as-a-service can also apply to a DBMS that is deployed in an on-premise fashion, and that brings me to what I feel is a very important point: "cloud" doesn't necessarily mean "off-premise," and "off-premise" doesn't necessarily mean "cloud." Take the latter part of this point I just made. For a LONG time, numerous organizations have contracted with various companies (IBM among them) to manage their IT operations. IT outsourcing company X might run organization Y's IT operations out of a data center owned by X. That's off-premise from Y's perspective, but there may be nothing cloudy about the way in which Y's IT operations are managed by X. Similarly, an organization could manage some or all of its IT resources in a cloudy way, even with all the IT resources managed in that way being on-premise. Cloud is about consumption of ______ as a service. To those who consume the service(s) provided, the location of the mechanism of service provision is (or should be) irrelevant.

OK, if database-as-a-service can be implemented in an on-premise way (and again, it can), what kind of services are we talking about, and who consumes those services? Quite often, the consumers are application developers, and the services provided are things like a new schema, or a new database instance, or a data backup or restore operation. More on this a little later on.

While database-as-a-service is concerned with the requisitioning and provisioning of database management system functionality, data-as-a-service is about the programmatic interface to a data server. "Database" is not part of the term because a user, who could be a developer writing a client application, has no need to know (and likely has no desire to know) whether or not a database management system is on the server side of a data request. Many application developers -- even those with strong SQL skills -- see the mechanism by which a data request is serviced (which could be a relational database management system such as DB2 for z/OS) as being part of back-end "plumbing," and plumbing is not their concern. They want a straightforward and consistent interface through which CRUD data services (create, read, update, delete) can be invoked -- an interface that abstracts virtually all of the particulars of the service-providing system. REST (short for REpresentational State Transfer) is one such interface, and in recent years it has become a very popular means of enabling data-as-a-service application architecture.

Data-as-a-service in a DB2 for z/OS context

Responding to the growing preference on the part of application developers for as-a-service interaction with data-providing systems, IBM built a native REST interface into DB2 12 for z/OS, and retrofitted that interface to DB2 11 (via the fix for APAR PI66828). DB2's native REST interface is an extension of the DB2 distributed data facility (DDF), so it leverages existing DDF capabilities such as thread pooling, profile monitoring, workload classification, and accounting and statistics tracing. Another benefit of the DDF tie-in: a SQL statement invoked by way of DB2's REST interface will execute under a preemptible SRB in the DDF address space, making the statement zIIP-eligible (up to 60%).

By way of DB2's REST interface, a single static SQL statement can be exposed in the form of a REST API. Before you start thinking of that "single statement" thing as overly limiting, consider that the single statement can be a call to a DB2 stored procedure (if you decide to make a stored procedure a RESTful service, I'd recommend going with a native SQL procedure, because a native SQL procedure is up to 60% zIIP-eligible when it executes under a DDF preemptible SRB).

In addition to enabling invocation of SQL statements via REST calls, DB2's REST-related capabilities support creation, discovery, management, and securing of SQL-based RESTful services. That functionality becomes more extensive (and more user-friendly) when z/OS Connect front-ends DB2's REST interface (i.e., when DB2 is a REST provider to z/OS Connect); and, z/OS Connect makes all kinds of z/OS-based programmatic assets available to REST clients: not only SQL statements (and stored procedures), but CICS transactions, IMS transactions, WebSphere Application Server for z/OS transactions, and batch jobs, as well.

Database-as-a-service in a DB2 for z/OS context

Quite a lot of work has been going on behind the scenes to provide database-as-a-service capabilities for DB2 for z/OS, and the first fruits of those efforts recently showed up in the form of a pair of DB2 APARs -- more on that in a moment.

Recall my mentioning, early on in this blog entry, that a raison d'etre for an on-premise database-as-a-service implementation could be easy and speedy requisitioning and provisioning of DBMS functions to support the work of application developers. The team within IBM DB2 for z/OS development that is leading the database-as-a-service charge has been focused on precisely that. And so it is that we have new DB2 for z/OS database-as-a-service capabilities that have recently been introduced via the fixes for APARs PI73168 (for DB2 11) and PI73492 (for DB2 12). These APARs leverage the functionality of zOSMF (the z/OS Management Facility), a component of the z/OS operating system that can drive the execution of "workflows" (roughly analogous to what are referred to as "scripts" in the distributed systems world) in response to REST calls. The APARs make available zOSMF workflows that can provide three database functions in an as-a-service manner: creation of a new database, creation of a new schema, and creation of a copy of an existing schema. The latter function, referred to as "schema like" (as in, "create a new schema like that one"), involves using, in addition to z/OSMF, the capabilities of the IBM DB2 Change Management Solution Pack for z/OS, which itself combines the functionality of the IBM DB2 Administration Tool and the IBM Object Comparison Tool.

More DB2 for z/OS database-as-a-service capabilities are being worked on, so watch this space.

All of this is good news for people who support DB2 for z/OS. Making data managed by DB2, and making database functionality provided by DB2, more easily consumable will help development teams to be more agile and more productive in building applications that interact with DB2. Additionally, more fully automating the requisitioning and provisioning of DB2 database management system resources will free DBAs from lots of "blocking and tackling" work around things like setting up new databases and schemas in development environments, and that should enable them to spend more time engaged in higher-value activities like working with developers on the design and architecture of new DB2-accessing applications.

Sunday, April 30, 2017

DB2 for z/OS: Something You Might Not Know About Large Page Frames in a Post-DB2 10 Environment

I well remember the first time I saw it. August, 2016. I was looking at output from the DB2 for z/OS command -DISPLAY BUFFERPOOL(ACTIVE) DETAIL, issued on a DB2 11 system, that a client had sent to me for review. I appreciated the fact that I could see in this command output information about DB2's use of large real storage page frames for page-fixed buffer pools (in a DB2 10 environment, one had to issue -DISPLAY BUFFERPOOL -- for an individual pool, versus for all active pools -- with an odd syntax to get this information). I saw pools for which PGFIX(YES) had been specified, and observed that, as expected, DB2 showed that the preferred real storage frame size for these pools was 1 MB. Then I noticed something I couldn't explain: there was a buffer pool, with a PGFIX(YES) specification, and DB2 was telling me, 


Huh? Why would DB2 prefer 4 KB-sized page frames for that pool? PGFIX(YES) buffer pools are supposed to be backed by 1 MB page frames, right? The indicated preference for 4 KB page frames was not the result of the pool being smaller than 1 MB: the pool's VPSIZE value was several thousand, and all it takes is 256 buffers of 4 KB apiece to fill a 1 MB frame. I continued through the -DISPLAY BUFFERPOOL output, and got even more confused. There were some PGFIX(YES) pools for which 1 MB was the indicated page frame size preference, and other PGFIX(YES) pools for which 4 KB was seen to be the preferred real storage page frame size. There did not appear to me to be any rhyme or reason for this variance. I moved on, and ultimately forgot about the strange-looking DISPLAY BUFFERPOOL output.

Fast forward about seven months, and I'm talking to a group of people about DB2 for z/OS buffer pools and large real storage page frames. I told my audience that DB2 will automatically seek to allocate a PGFIX(YES) buffer pool using 1 MB page frames. "That's not true," said a person in the room. I was pretty sure that this guy was wrong on that point, but in the interest of time I told him, "Let's talk about this offline." Over the next couple of days, this individual and I exchanged e-mails on the topic, and he made a very good argument in support of his contention and I tried some things on an IBM-internal DB2 for z/OS subsystem and, by golly, the man was right.

Here's the deal: in a DB2 10 for z/OS environment, 1 MB page frames will be preferred for a buffer pools for which PGFIX(YES) has been specified. In a DB2 11 (or later) system, that may be the case. Why "may," instead of "will?" Because DB2 11 introduced the FRAMESIZE option for the -ALTER BUFFERPOOL command. That option lets you tell DB2 of your preference for page frame size for a buffer pool, and for a PGFIX(YES) pool your preference becomes DB2's preference (assuming that there are enough buffers, per the pool's VPSIZE value, to fill at least one of the page frames of the size indicated via the FRAMESIZE specification). As DB2 10 did not have a FRAMESIZE option for ALTER BUFFERPOOL, that specification was, in essence, implicitly conjoined with the PGFIX specification -- when you requested, in a DB2 10 system, that a pool's buffers be fixed in the z/OS LPAR's real storage, you were also implicitly requesting allocation of the pool's buffers in 1 MB real storage page frames (subject to the availability of such page frames in the LPAR).

DB2 11 for z/OS introduced a choice for real storage page frame size preference for a page-fixed buffer pool: 1 MB or 2 GB. The FRAMESIZE option was added to the -ALTER BUFFERPOOL command to enable explicit specification of the preferred page frame size for a pool. It was made pretty clear that use of 2 GB page frames for a buffer pool required a FRAMESIZE(2G) specification for the pool. Lots of people (myself included) assumed that specifying FRAMESIZE(1M) was not required if one wanted a page-fixed buffer pool to be allocated using 1 MB page frames. We in this camp believed that FRAMESIZE(1M) was automatically specified "under the covers" in a DB2 11 system when an -ALTER BUFFERPOOL command with PGFIX(YES) was issued. As it turns out, that was a FALSE ASSUMPTION on our part. The facts are these: first, in a DB2 11 (or later) system, 1 MB will be the preferred real storage page frame size, even without a FRAMESIZE(1M) request, for a pool that was page-fixed and in-use in the DB2 10 environment, prior to the migration of the system to DB2 11. Second, for a pool newly allocated in a DB2 11 (or later) environment, an explicit FRAMESIZE(1M) specification is required if one wants 1 MB to be the preferred real storage page frame size for a pool.

To put this another way: as part of the migration of a DB2 10 system to DB2 11, existing page-fixed buffer pools were "migrated" to the DB2 11 environment with an automatic FRAMESIZE(1M) specification added to the pools' definition. That makes sense, because it preserves the behavior of the pools in the DB2 11 environment that was in effect in the DB2 10 system. For new pools allocated in a DB2 11 (or later) system, because requesting a page frame size preference is a separate action from requesting page-fixed buffers, you have to explicitly specify FRAMESIZE(1M) or FRAMESIZE(2G) in order make the preferred frame size 1 MB or 2 GB, respectively.

Here are your take-aways:

  1. If your DB2 for z/OS environment is at Version 11 (or later), issue the command DISPLAY BUFFERPOOL(ACTIVE) DETAIL, and examine the output for each pool. Do you see any pools for which the PGFIX attribute is YES, and the indicated PREFERRED FRAME SIZE is 4K? If yes, it is likely that those pools were in use before the system was migrated to DB2 11. If you want 1 MB page frames to be used for the pools, alter them with a FRAMESIZE(1M) specification.
  2. Even if all of the PREFERRED FRAME SIZE attributes for your buffer pools are as you want them to be, give some thought to altering each pool (or at least each pool for which PGFIX(YES) is an attribute) with the appropriate FRAMESIZE specification. I think that there is value in having an explicit FRAMESIZE specification for each of your buffer pools (or, again, at least each of the page-fixed pools), even if that explicit frame size preference is the same as the one that is in effect by default for a pool. This serves to get you into the "make it clear by making it explicit" mind set, and that can be a sound aspect of your buffer pool management strategy.
As always, thanks for visiting the blog.

Sunday, March 26, 2017

DB2 for z/OS: Running REORG to Reclaim Disk Space

Think of why you run the DB2 for z/OS REORG utility, and a number of reasons are likely to come quickly to mind: to restore row order per a table's clustering key; to reestablish free space (for inserts and/or for updates); to remove the AREO* status set for a table space following (for example) an ALTER TABLE ADD COLUMN operation; or to materialize a pending DDL change such as an enlargement of a table space's DSSIZE. How about disk space reclamation? If that REORG motivation has not previously occurred to you, perhaps it should.

Recently, a DBA at a large DB2 for z/OS site communicated to me the success that his team has had in reclaiming substantial amounts of disk space through online reorganization of certain table spaces. He also asked for a recommendation with regard to identifying table spaces for which a REORG could potentially deliver significantly reduced disk space consumption. In this blog entry, I'll describe the disk space reclamation scenario reported by the referenced DBA, I'll explain why there was space to be reclaimed in some of the table spaces administered by the DBA, and I'll provide the "reclamation indicator" metric that I suggested to the DBA as a means of identifying table spaces that could be reorganized in order to free up disk space.

First, the scenario. At the DBA's site, there are some tables, in segmented table spaces ("traditional" segmented table spaces, as opposed to universal table spaces, which also happen to be segmented), that have these key characteristics: they are clustered by a continuously-ascending key (so that "new" rows go to the "end" of the table), and the number of inserts into the table is roughly equaled by the number of rows that are deleted from the table over a period of time.

The DB2 DBA knew that for table spaces with the above-described characteristics, REORGs were not needed to maintain "clusteredness," because of the continuously-ascending clustering key that sent new rows to the end of the table (at least, clustering would remain in good shape until the table space reached its size limit -- more on this in a moment). For the same reason, free space for inserts in "interior" pages of the table space was not a concern. Still, with DB2 real-time statistics showing a very large number of inserts since the last REORG of a couple of these table spaces, the DBA determined that reorganizations might be in order. Online REORGs of the table spaces were executed, and the result was a freeing up of 64 GB of disk space: one table space went from 21 to 4 data sets of 2 GB apiece, and the other went from 17 data sets to 2 (a DB2 segmented table space is comprised of up to 32 data sets of 2 GB apiece, and that is why its size limit is 64 GB).

Why was there so much unused space in these table spaces? Because the continuously-ascending clustering key kept pushing the "end" of the table spaces "outward." Why would that happen? Why would DB2 grow these table spaces as a result of inserts, given the like number of row-delete operations that were targeting the associated tables? Shouldn't DB2 have been using the space freed up by deletes to accommodate new inserts, without growing the table space's size? Actually, DB2 was working as designed. It's true that, given a continuously-ascending clustering key and some deletes of older rows from the "front" of a table space, DB2 can "wrap" to the front and start inserting new rows in space cleared by deletes, but that will only happen if DB2 cannot extend the table space (i.e., if DB2 cannot make the table space larger). If DB2 can extend a segmented table space, it will in order to preserve a table's row-clustering order; so, in advance of hitting the 64 GB size limit for a segmented table space, DB2 would keep making the table space larger so that it could keep adding rows to the end of a table (assuming a continuously-ascending clustering key), and deletes of older rows would result in ever-larger amounts of available-but-unused space in the table space. That's why the disk footprint of the two table spaces became so much smaller following reorganization.

[It is important to keep in mind that, given a continuously-ascending clustering key and at least some row-delete operations, DB2 will insert new rows in the "front" of a segmented table space, using space freed up by DELETEs, if the table space cannot be made any larger (either because of reaching the 64 GB limit or as a result of running into a maximum-extents or a maximum-volumes situation). In that case, "wrapping to the front" for new inserts is better than failing the inserts.]

Having achieved this disk space reclamation success through REORGs, the aforementioned DBA wanted to add "potential for significant disk space reclamation" to the criteria used at his site for identifying table spaces that should be reorganized (a good proactice -- REORG table spaces when you have a good reason for doing so, not just "because it's been X amount of time since the last time this table space was REORGed"). How could he and his colleagues spot table spaces with large amounts of unused space? My recommendation: use for this purpose the ratio of disk space occupied by the table space to space in the table space occupied by rows in the table space. For the numerator, I'd use the SPACE value in the row for the table space in the SYSTABLESPACE catalog table. That value is updated when the STOSPACE utility is executed, so you would want to run STOSPACE on a regular basis (that should not be a big deal -- STOSPACE should be a very inexpensive utility to execute, CPU-wise). For the denominator, I would use the product of TOTALROWS from SYSTABLESPACESTATS (set by REORG and updated when INSERTs and DELETEs are executed) and AVGROWLEN in SYSTABLESPACE (updated by RUNSTATS, or by in-line statistics collected during REORG or LOAD). You can decide when that ratio would prompt you to run REORG to reclaim space. Would you do that when disk-space-to-row-space hits 2 (i.e., when the size of the table space is 2X the space occupied by rows)? When it hits 3? When it hits 4? One of those values might be reasonable for your environment.

One more thing: I have focused on traditional segmented table spaces in this blog entry because that is the table space type to which space reclamation via REORG is most relevant. For a range-partitioned table space, a given partition's size limit is determined by the DSSIZE specification, and the same is true for a partition-by-growth table space. Yes, you could see a partition-by-growth table space come to contain a high percentage of unused space given the combination of a continuously-ascending clustering key and a good deal of DELETE activity, but you could put a limit on that growth by way of a not-larger-than-needed MAXPARTITIONS value. With that said, even with range-partitioned and partition-by-growth table spaces you could see situations in which the ratio of table space size to space occupied by rows (the ratio described in the preceding paragraph) could get to be high enough to make a REORG attractive from a disk space reclamation perspective. And here there's some good news: starting with DB2 11 for z/OS, you can tell DB2 to drop partitions of a partition-by-growth table space made empty by a REORG or the entire table space (that functionality is enabled via the REORG_DROP_PBG_PARTS parameter in ZPARM).

So, add disk space reclamation to your reasons for running REORG (if you have not already done so), and consider using the ratio I've provided to look for candidate table spaces.

Sunday, February 26, 2017

DB2 for z/OS: the PGSTEAL and PGFIX Options of -ALTER BUFFERPOOL

Recently, a DB2 for z/OS professional I’ve known for some years sent to me a question about the relationship between the PGSTEAL and PGFIX options of the DB2 command -ALTER BUFFERPOOL. It took a few iterations of question and answer to get things straightened out, and I thought, “Hmm. If this person, who has lots of DB2 for z/OS experience and knowledge, needed a little help in getting PGSTEAL and PGFIX straightened out in his mind, it's likely that other DB2 people are in the same boat.” And so I’m writing this blog entry, in the hope that it will be helpful to people who have some uncertainty about the dependencies – if any – between PGSTEAL and PGFIX.

OK, the “if any” in the preceding sentence might suggest to you that maybe there isn’t anything in the way of interdependency with regard to PGSTEAL and PGFIX. In fact, there really isn't. Yes, there’s a recommended combination of PGSTEAL and PGFIX settings, and I’ll get to that, but the suggested combination is about the way in which PGSTEAL and PGFIX can both support a particular performance objective, as opposed to having anything to do with the way these two buffer pool specifications affect each other, because they don't affect each other.

“What?” you might ask. “How is it that two buffer pool configuration options that both have 'page' in their long names (‘PG’ is short for ‘page’) don’t really have anything to do with each other?” This is so because PGSTEAL and PGFIX have very different functions. This functional difference might have been more readily apparent to people if PGSTEAL had instead been labeled something like BSTEAL – short for “buffer steal,” because PGSTEAL is about management of buffers, which is a DB2 responsibility, and PGFIX is about management of real storage – a responsibility of the z/OS operating system.

Let me make this distinction even more clear: PGSTEAL is related to virtual storage management – DB2’s management of virtual storage space that belongs to DB2 – and PGFIX is related to real storage management – something in z/OS’s bailiwick.

Consider the possible values of PGSTEAL: LRU, FIFO, and NONE. The first two are specified when you anticipate that there will be some buffer-steal activity for the pool in question (i.e., there are more pages in objects assigned to the pool than there are buffers in the pool). You’d go with LRU (least recently used) if you anticipate that there will be quite a bit of buffer-steal activity for the pool (“OK, DB2, you’re probably going to have to frequently steal buffers for this pool. When you do that – when you have to replace a table or index page currently in a buffer with another page – steal the buffer holding the page that’s gone the longest time without being referenced.”). You’d choose FIFO (first in, first out) when you expect some – but very little – buffer-stealing for the pool (“Hey, DB2. You’re going to have to do a very small amount of buffer stealing for this pool. That being the case, don’t waste CPU cycles keeping track of which buffers hold the pages that have gone the longest time without being referenced. Just steal the buffer holding the page brought into memory the longest time ago.”).

How about PGSTEAL(NONE)? When would you go with that option? You’d go this route if you anticipate that there will be NO buffer-steal activity for a pool – in other words, the pool has more buffers than there are pages belonging to objects assigned to the pool (“Hey, DB2. I’m planning on using this pool to cache one or more table spaces and/or indexes in memory in their entirety. I’m doing that because I want maximum performance for these babies. Help me out.”). And, given the message you’ve sent to DB2, DB2 will help you out: it will asynchronously read into the pool every page belonging to an object assigned to the PGSTEAL(NONE) pool when the object is first referenced after a DB2 start-up, and in optimizing SQL statements targeting objects assigned to the pool, it will perform access path selection with the assumption that read I/O costs will be zero (not counting possible reads associated with use of work file table spaces).

Next, PGFIX settings. There are two: YES and NO. When NO (the default) is in effect, a real storage page frame occupied by a DB2 buffer can be stolen by z/OS (i.e., the contents of the page frame can be moved to auxiliary storage, also known as the page data sets) in order to make room for some other page that has to be brought into real storage (in performing such page steal actions, z/OS utilizes a least-recently-used algorithm). To prevent a z/OS real storage page frame steal action from interfering with a buffer read I/O (DB2 copies a table space or index page from disk – or maybe from a group buffer pool, in a data sharing system – into a buffer) or write I/O operation (DB2 copies a page in a buffer to disk or to a group buffer pool), a buffer will be fixed in memory (i.e., made non-page-able) prior to the I/O action, and released (made page-able again) after the I/O action. When PGFIX(YES) is in effect for a pool, buffers belonging to the pool are fixed in memory (made non-page-able) as soon as the pool is allocated, and they remain fixed in memory as long as the pool is allocated. Because the buffers are always in a fixed-in-memory state, the page-fix/page-release actions required for all buffer read and write I/O operations when PGFIX(NO) is used are not necessary. That makes buffer read and write I/Os more CPU-efficient when PGFIX is set to YES, and that makes PGFIX(YES) particularly beneficial for pools with high rates of read and write I/Os.

PGFIX(YES) is also a prerequisite if you want a DB2 buffer pool to be backed by large real storage page frames (1 MB or, starting with DB2 11 for z/OS, 2 GB frames, versus the traditional 4 KB frames). When a pool’s buffers are located in large real storage page frames, further CPU savings (beyond less-expensive I/O operations) are realized, because large real storage page frames make virtual-storage-to-real-storage address translation more efficient.

If we take a look at some combinations of PGSTEAL and PGFIX settings, you’ll see what I meant about there being essentially nothing in the way of interdependency. Take PGSTEAL(LRU) and PGFIX(YES). Will the fact that the pool’s buffers are fixed in memory have any impact on the level of buffer stealing done by DB2 for this pool? NO! REGARDLESS of whether a pool’s buffers are fixed in memory or not, DB2 will ALWAYS steal a buffer in the pool WHENEVER IT HAS TO, and a buffer HAS TO BE STOLEN whenever all the pool’s buffers are occupied (by table space and/or index pages) and a page has to be read into the pool from disk (or from a group buffer pool in a coupling facility). The PGFIX(YES) setting for this pool simply means that z/OS will not move any of the pool’s buffers out of real storage to auxiliary storage – it in no way restricts DB2’s ability to steal buffers so that pages not yet in the pool can be brought in from disk (or from a coupling facility).

Change the combination of PGSTEAL and PGFIX settings to NONE and NO, respectively, and again you’ll see that there is nothing in the way of an interdependency. PGSTEAL(NONE) means that DB2 does not EXPECT to have to do any buffer stealing for the pool, because there is an assumption that the number of pages belonging to objects assigned to the pool does not exceed the quantity of buffers in the pool (i.e., does not exceed the pool’s VPSIZE). Of course, if the number of pages in objects assigned to the pool does exceed the pool’s VPSIZE (perhaps VPSIZE was too small to begin with, or it was OK initially but objects assigned to the pool have gotten larger), and a buffer has to be stolen because all buffers are occupied and a page has to be brought into the pool, a buffer WILL BE STOLEN because (as noted previously) a buffer will ALWAYS BE STOLEN when a buffer HAS TO BE STOLEN (when PGSTEAL is set to NONE for a pool and some buffer stealing has to be performed, it will be done on a FIFO basis – first in, first out). What about the PGFIX(NO) setting for this pool? Will the pool’s PGSTEAL(NONE) setting in any way restrict the ability of z/OS to move buffers belonging to the pool out of real storage to auxiliary storage, as necessary, to free up real storage page frames so that new pages can be brought into real storage? NO! With PGFIX(NO) in effect, a pool’s buffers are absolutely fair game for being paged out to auxiliary storage, REGARDLESS of the pool’s PGSTEAL setting (that said, buffers in PGFIX(NO) pools are not often paged out of real storage, because z/OS, as noted, steals real storage page frames on a least-recently-used basis, and DB2 tends to “touch” its buffers with a frequency that makes them unlikely to be among the least-recently-used pages in a z/OS LPAR’s real storage).

DB2, then, manages its buffers as it needs to (with some guidance provided by you via a pool’s PGSTEAL setting), and z/OS manages its real storage resource as it needs to (with the understanding that buffers in a PGFIX(YES) pool are off-limits with regard to page-out actions), and the DBMS and the OS attend to these respective tasks pretty much independently. PGSTEAL settings do not impact z/OS page-frame-stealing, and PGFIX settings do not impact DB2 buffet-stealing.

Now, early on in this post I mentioned that while there essentially aren’t any interdependencies between the various settings of PGSTEAL and PGFIX for a buffer pool, there is a recommendation I have concerning a particular combination of PGSTEAL and PGFIX values. Here it is: if you are going to specify PGSTEAL(NONE) for a buffer pool, specify PGFIX(YES) for that same pool, unless the demand paging rate of the z/OS LPAR is higher than it should be (if the demand paging rate – available by way of a z/OS monitor – is in the low single digits or less per second, it’s OK). Why I make this recommendation: presumably, you assign objects to a PGSTEAL(NONE) buffer pool because you in fact want them to be cached in memory in their entirety. You would do this, I imagine, for objects for which maximum performance is really important. If that’s the case, why not really max out performance as it pertains to accessing these objects? Make the pool PGFIX(YES) as well as PGSTEAL(NONE), so that you can get the CPU efficiency benefit of large real storage page frames (of course, to get that benefit you need to have large page frames available in the LPAR to back the PGFIX(YES) pool – information about that can be found in an entry I posted to this blog a fewmonths ago).

And there you have it. Just remember that PGSTEAL is related to DB2’s management of its buffers, while PGFIX is related to z/OS’s management of the LPAR’s real storage resource. Two different buffer pool configuration settings, for two different aspects of DB2 performance.

Tuesday, January 31, 2017

Are You Using System Profile Monitoring to Manage Your DB2 for z/OS DDF Workload? Perhaps You Should

Here's a scenario that might sound familiar to you: you have a particular transaction, which I'll call TRNX, that is the source of quite a lot of deadlock activity in a DB2 for z/OS system. It seems that whenever more than one instance of TRNX is executing at the same time, a deadlock situation is highly likely. You went with row-level locking for the table spaces accessed by TRNX, but the trouble persisted. It is conceivable that rewriting the program code associated with TRNX might eliminate the problem, but the task would be challenging, the development team has limited bandwidth to accomplish the recommended modifications, and it could take months -- or longer -- for the fix to get into production. What can you do?

Well, as I pointed out in an entry posted to this blog a few years ago, sometimes the right approach in a case such as this one is to single-thread TRNX. Though it may at first seem counter-intuitive, there are circumstances for which transactional throughput can be increased through a decrease in the degree of transactional multi-threading, and that approach can be particularly effective when the rate of transaction arrival is not particularly high (i.e., not hundreds or thousands per second), transaction elapsed time is short (ideally, well under a second), and probability of a DB2 deadlock is high if more than one instance of the transaction is executing at the same time.

Lots of people know how to single thread a CICS-DB2 or IMS-DB2 transaction, but what about a DDF transaction (i.e., a transaction associated with a DRDA requester, which would be an application that accesses DB2 for z/OS by way of TCP/IP network connections)? Is there a means by which a DDF transaction can be single-threaded?

The answer to that question is, "Yes," and the means is called system profile monitoring, and DDF transaction single-threading is just one of many useful applications of this DB2 for z/OS capability. I'll provide a general overview of DB2 system profile monitoring, and then I will cover transaction single-threading and a couple of other use cases.

System profile monitoring is put into effect by way of two DB2 tables, SYSIBM.DSN_PROFILE_TABLE, and SYSIBM.DSN_PROFILE_ATTRIBUTES. Those tables were introduced with DB2 9 for z/OS, and DB2 10 enabled their use in managing a DDF workload in a more granular fashion than was previously possible. Prior to DB2 10, the number of connections from DRDA requesters allowed for a DB2 subsystem, and the maximum number of those connections that could be concurrently in-use, and the maximum time that an in-use (i.e., non-pooled) DBAT (database access thread -- in other words, a DDF thread) could sit idle without being timed out, could be controlled only at the DB2 subsystem level via the ZPARM parameters CONDBAT, MAXDBAT, and IDTHTOIN, respectively. What if you want to exert control over a part of a DDF workload in a very specific way? With system profile monitoring, that is not at all hard to do.

A row inserted into SYSIBM.DSN_PROFILE_TABLE indicates the scope of a particular DDF workload managemnt action, and the corresponding row (or rows) in SYSIBM.DSN_PROFILE_ATTRIBUTES indicates what you want to manage for this component of your DDF workload (number of connections, number of active connections, idle thread timeout, or two of the three or all three) and how you want that management function to be effected (e.g., do you want DB2 to take action when a specified limit is exceeded, or just issue a warning message). The columns of the two tables, and their function and allowable values, are well described in the DB2 for z/OS documentation, and I won't repeat all that information here (the DB2 11 information is available online at, and you can easily go from there to the DB2 10 or DB2 12 information, if you'd like). What I will do is take you through a few use cases, starting with the single-threading scenario previously referenced.

To single-thread a DDF transaction, you would first need to identify that transaction by way of a row inserted into the SYSIBM.DSN_PROFILE_TABLE. You have multiple options here. You might identify the transaction by workstation name (a string that is easily set-able on the client side of a DDF-using application, as described in a blog entry I wrote back in 2014); or, you might identify the transaction via package name, if, for example, it involves execution of a particular stored procedure; or, you might use collection name [Collection name can be specified as a client-side data source property, and it is increasingly used to manage applications that use only IBM Data Server Driver (or DB2 Connect) packages -- these packages, which belong by default in the NULLID collection, can be copied into other collections, and in that way a DDF-using application can be singled out by way of the name of the Data Server Driver (or DB2 Connect) package collection to which it is pointed.] And, there are multiple additional identifier choices available to you -- check the DB2 documentation to which I provided the link above.

In your SYSIBM.DSN_PROFILE_TABLE row used to identify the transaction you want to single-thread, you provide a profile ID. That ID serves as the link to an associated row (or rows) in SYSIBM.DSN_PROFILE_ATTRIBUTES. In a row in that latter table, you would provide the ID of the profile you'll use to single-thread the transaction, 'MONITOR THREADS' in the KEYWORD column, 1 in the ATTRIBUTE2 column (to show that you will allow one active DBAT for the identified transaction), and 'EXCEPTION' in the ATTRIBUTE1 column to indicate that DB2 is to enforce the limit you've specified, as opposed to merely issuing a warning message (you could also specify 'EXCEPTION_DIAGLEVEL2' if you'd like the console message issued by DB2 in the event of an exceeded threshold to be more detailed versus the message issued with EXCEPTION, or its equivalent, EXCEPTION_DIAGLEVEL1, in effect). Then you'd activate the profile with the DB2 command -START PROFILE, and bingo -- you have put single-threading in effect for the DDF transaction in question.

Something to note here: Suppose you have set up single-threading in this way for transaction TRNX, and an instance of TRNX is executing, using the one thread you've made available for the transaction. Suppose another request to execute TRNX arrives. What then? In that case, the second-on-the-scene request for TRNX will be queued until the first-arriving TRNX completes (if TRNX typically executes in, say, less than a second, the wait shouldn't be long). What if a third request for TRNX comes in, while the second request is still queued because the first TRNX has not yet completed? In that case, the third TRNX request will fail with a -30041 SQLCODE. This is so because DB2 will queue requests only up to the value of the threshold specified. If you specify 1 active thread for a transaction, DB2 will queue up to 1 request for that transaction. If you specify a maximum of 4 active threads for a transaction, DB2 will queue up to 4 requests for the transaction if the 4 allowable active threads are busy. With this in mind, you'd want to have the TRNX program code handle the -30041 SQLCODE and retry the request in the event of that SQLCODE being received. Would you like to be able to request a "queue depth" that is greater than your specified threshold value? So would some other folks. That enhancement request has been communicated to the DB2 for z/OS development team.

Something else to note here: What if you are running DB2 in data sharing mode. Does a limit specified via SYSIBM.DSN_PROFILE_TABLE and SYSIBM.DSN_PROFILE_ATTRIBUTES apply to the whole data sharing group? No -- it applies to each member of the group. How, then, could you truly single-thread a DDF transaction in a data sharing environment? Not too hard. You'd set up the profile and associated threshold as described above, and you'd start the profile on just one member of the group (-START PROFILE is a member-scope command). On the client side, you'd have the application associated with TRNX connect to a location alias, versus connecting to the group's location, and that alias would map to the one member for which the single-threading profile has been started (I wrote about location aliases in an entry posted to this blog a few years ago -- they are easy to set up and change). If the one member is down, have the profile ready to go for another member of the group (you could have leave the GROUP_MEMBER column blank in the DSN_PROFILE_TABLE row to show that the profile applies to all members of the group, or you could have two rows, one for the "primary" member for the transaction in question, and one for an alternate member, in case the "primary" member for the transaction is not available), and start the profile on that member. You would also change the location alias that maps to the one member, so that it maps instead to the other member (or you could map the alias to two members, and only start the alias on one member at any given time -- location aliases can be dynamically added, started, and stopped by way of the DB2 command -MODIFY DDF); so, no client-side changes would be needed to move single-threading for a transaction from one data sharing member to another.

A couple more use cases. What else can be accomplished via DB2 system profile monitoring? There are many possibilities. Consider this one: you have a certain DDF-using application for which you want to allow, say, 50 active threads. Easily done: if the application connects to DB2 using a particular authorization ID -- very commonly the case -- then set up a profile that is associated with the application's ID, and in the associated DSN_PROFILE_ATTRIBUTES row indicate that you want to MONITOR THREADS, that the threshold is 50, and the action is EXCEPTION. Note, then, that up to 50 requests associated with the application could be queued, if the 50 allotted DBATs are all in-use.

Or how about this: there are hundreds (maybe thousands) of people employed by your organization that can connect to one of your DB2 for z/OS systems directly from their laptop PCs. You know that a single individual could establish a large number of connections to the DB2 system, and you are concerned that, were that to happen, your system could hit its CONDBAT limit, to the detriment of other DDF users and applications (and maybe that's actually happened at your shop -- you wouldn't be the first to encounter this situation). How could you limit individuals' laptop PCs to, say, no more than 5 host connections apiece? Would you have to enter hundreds (or thousands) of rows in DSN_PROFILE_TABLE, each specifying a different user ID (or IP address or whatever)? That is what you WOULD have had to do, before a very important system profile monitoring enhancement was delivered with DB2 12 for z/OS (and retrofitted to DB2 11 via the fix for APAR PI70250). That enhancement, in a word: wildcarding. By leveraging this enhancement (explained below), you could limit EACH AND EVERY "laptop-direct" user to no more than 5 connections to the DB2 for z/OS subsystem by way of a single row in DSN_PROFILE_TABLE (and an associated MONITOR CONNECTIONS row in DSN_PROFILE_ATTRIBUTES).

More on wildcarding support for system profile monitoring: with DB2 12 (or DB2 11 with the fix for the aforementioned APAR applied), you can use an asterisk ('*') in the AUTHID or the PRDID column of DSN_PROFILE_TABLE (the latter can identify the type of client from which a request has come); so, an AUTHID value of 'PRD*' would apply to all authorization IDs beginning with the characters PRD (including 'PRD' by itself), and an asterisk by itself would apply to ALL authorization IDs (with regard to rows in DSN_PROFILE_TABLE, a DRDA request will map to the profile that matches it most specifically, so if there were a profile row for auth ID 'PROD123' and another row for auth ID '*', the former would apply to requests associated auth ID PROD123 because that is the more specific match).

You can also use wildcarding for the IP address in the LOCATION column of a row in SYSIBM.DSN_PROFILE_TABLE, but in a different form. For an IPv4 TCP/IP address, a wildcard-using entry would be of the form address/mm where mm is 8, 16, or 24. Those numbers refer to bits in the IP address. Here's what that means: think of an IPv4 address as being of the form A.B.C.D. Each of those four parts of the address consists of a string of 8 bits. If you want to wildcard an IPv4 address in the LOCATION column of a DSN_PROFILE_TABLE row, so that the row will apply to all addresses that start with A.B.C but have any possible value (1-254) for the D part of the address, the specification would look like this (if A, B, and C were 9, 30, and 222, respectively):

And note that a specification of applies to all IP addresses from which requests could come for the target DB2 for z/OS system. A similar convention is used for IPv6 addresses -- you can read about that in the text of the APAR for which I provided a link, above.

Why use this convention, instead of something like 9.30.222.* for addresses through, or an * by itself for all IP addresses? Because the convention used is already prevalent in the TCP/IP world, and in this case it made sense to go with the flow.

So, that's what I have to say about DB2 system profile monitoring. It's a great way to manage a DB2 for z/OS DDF workload in a more granular way than is offered by the ZPARMs CONDBAT, MAXDBAT, and IDTHTOIN (though those ZPARM values remain in effect in an overall sense when system profile monitoring is in effect). If you aren't yet using this functionality, think of what it could do for your system. If you are using system profile monitoring but haven't used the new wildcard support, consider how that enhancement could provide benefits for you. In an age of ever-growing DB2 DDF application volumes, system profile monitoring is a really good thing.

Friday, December 30, 2016

In Praise of the Invisible DB2 for z/OS System

I've been working with DB2 for z/OS -- as an IBMer, as a user, as an independent consultant -- for 30 years. I was with IBM when DB2 was introduced to the market. I've met people who were part of the core team at IBM that invented relational database technology. I was privileged to serve for a year as president of the International DB2 Users Group. DB2 has been very good to me, and the work I do as a DB2 for z/OS specialist I do with commitment and passion. And here I am, getting ready to tell you that I hope your DB2 systems will become invisible to a key segment of the user community. Have I gone off the rails? You can read on and decide that for yourself.

To begin, what is that key segment of the user community for which, I hope, DB2 for z/OS will be invisible? Application developers. Why? Simple: the most meaningful indicator of the vitality of a DB2 for z/OS environment is new application workload. Growth, volume-wise, of existing DB2 for z/OS-accessing applications is certainly welcome, but you know, as a person supporting DB2, that your baby is in really good shape when newly-developed applications that interact with DB2 are coming online. If you want this to happen -- and you should -- then you should understand that a vital success factor is having application developers who are favorably inclined towards writing code that accesses DB2 for z/OS-managed data. And THAT depends largely on those developers not having to do anything different, in creating DB2-accessing applications, than they would do in working with another data server. In other words, you want application developers who are working with DB2 to not have to notice that DB2 is the database management system with which they are working.

Why is invisibility, from an application developer's perspective, of major import when it comes to growing new application workload for a DB2 for z/OS system? Several reasons:
  • First, you need to realize that a fundamental balance-of-power shift has occurred over the past 10-15 years: developers, once beholden, in many shops, to "systems" people, are now in the driver's seat. As far as I'm concerned, this is as it should be. I believe that the value of IT systems (and the value of the data housed in those systems) is largely dependent on the value of the applications that use those systems (and access that data). Applications bring in revenue, serve customers, schedule deliveries, determine product mix, pay suppliers, manage inventory, etc., etc. If you are a DB2 "systems" person (e.g. a systems programmer or a database administrator), my advice to you is this: view your organization's application developers as your customers, and seek to excel in customer service. If what those folks want is to not have to worry about the particulars of a given database management system when developing an application, deliver that.
  • Second (and related to the first point, above), DB2 invisibility, in the sense in which I'm using that phrase, removes a reason for which some developers might be opposed to writing DB2 for z/OS-accessing code. Put yourself in a developer's shoes. Wouldn't your productivity be enhanced if you could write database-accessing code without having to stop and think, "Hmm, for this application, I'm working with DBMS X versus DBMS Y. I know there are some things I need to do differently for DBMS X. Hope I can recall the details of those differences?" Wouldn't it be better if you could just code the same way regardless of the target data server?
  • Third, when a DB2 for z/OS system looks to a developer like other data-serving platforms, what becomes more noticeable is the way it acts versus other platforms. Developers might notice that this one data-serving system (which happens to be powered by DB2 for z/OS), for which they code as they do for other systems, always seems to be up, never seems to get hacked, and performs well no matter how much work gets thrown at it. Noticing these characteristics, developers might start expressing a preference for the data associated with their new applications being stored on the platform with the industrial-strength qualities of service. A friend of mine who is a long-time DB2 for z/OS DBA has a good nickname for the system he supports: the "super-server." I can certainly imagine a team of developers making a request along the lines of, "Could we put this application's data on the super-server?" Who wouldn't want that?

OK, so DB2 invisibility is a good thing. How to achieve it? The answer, in a word, is: abstraction. I want to talk here about two especially important forms of abstraction available for DB2 for z/OS systems -- one with which you are likely familiar, and one which may be news to you.

The familiar form of DBMS abstraction to which I referred above is the kind that makes a particular relational database management system (like DB2 for z/OS) look, to an application program, like a generic relational DBMS. In my experience, the two most common of these DBMS-abstracting interfaces are JDBC (Java Database Connectivity) and ODBC (Open Database Connectivity). ADO.NET is another example. These interfaces are enabled by drivers. In the case of DB2 for z/OS, JDBC and ODBC (and ADO.NET) drivers are provided, for network-attached applications (i.e., applications that access a target data server through a TCP/IP connection), via the IBM Data Server Driver (or DB2 Connect, though the former is recommended, as I pointed out in an entry posted to this blog a couple of months ago). IBM also provides, with DB2, JDBC and ODBC drivers that can be used by local-to-DB2 programs (i.e., programs running in the same z/OS system as the target DB2 for z/OS data server).

Lots and lots of applications, at lots and lots of sites, have been accessing DB2 for z/OS systems for years using JDBC, ODBC, and other interfaces that make DB2 look like a generic relational database management system. More recently, a growing number of developers have indicated a preference for a still-higher level of abstraction with regard to data server interaction. To these developers, using a DBMS-abstracting data interface such as JDBC or ODBC is less than ideal, because such interfaces indicate that the target data-serving system is a relational database management system. While it's true that the use of JDBC or ODBC (or a similar interface) means that a programmer does not have to be aware of the particular relational database management system being accessed, many developers feel -- and they have a point -- that even the form of a target data-serving system (one form being a relational DBMS) is something they should not have to know. From their standpoint, the form of a target data-serving system is a matter of "plumbing" -- and plumbing is not their concern. These developers want to access data-related services (retrieve data, create data, update data, delete data) as just that -- services. When data-as-a-service (DaaS) capability is available to a developer, the nature of what is on the other side of a service request -- a relational DBMS (like DB2), a hierarchical DBMS (like IBM's IMS), a Hadoop-managed data store, a file system -- is not consequential.

What is consequential to developers who want data-as-a-service capability is consistency and ease of use, and those concerns have much to do with the rapid rise of REST as a means of invoking programmatically-consumable services. REST -- short for Representational State Transfer -- is a lightweight protocol through which services can be invoked using HTTP verbs such as GET and PUT (services accessed in this way can be referred to as RESTful services). Adding to the appeal of REST is the use of JSON, or JavaScript Object Notation -- an intuitive, easy-to-interpret data format -- for the data "payloads" (input and output) associated with REST calls.

REST enables client-side programmers to be very productive and agile in assembling services to create applications, and that likely has much to do with REST being widely used for applications that access a server from a mobile device front-end and/or from cloud-based application servers. But DON'T think that the cloud and mobile application worlds are the only ones to which RESTful services are applicable. There are plenty of developers who want to use REST API calls to build in-house applications that may have nothing to do mobile devices or clouds, for the reasons previously mentioned: productivity and agility. Mobile and cloud application developers aren't the only ones who don't want to deal with IT infrastructure plumbing.

DB2 for z/OS moved into the realm of RESTful services in a big way with the native REST interface built into DB2 12 (and retrofitted to DB2 11 via the fix for APAR PI66828). That DB2 REST API can be invoked directly from a client, or by way of IBM z/OS Connect (as mentioned in my blog entry referenced previously in this post). When z/OS Connect is in the picture, not only DB2 services, but CICS, IMS, WebSphere Application Server for z/OS, and batch services, as well, can be accessed via REST calls from clients, with data payloads sent back and forth in JSON format. z/OS Connect also provides useful tooling that facilitates discovery (by client-side developers) and management of z/OS-based RESTful services. Whether the DB2 REST API is accessed directly or through z/OS Connect, what it does is enable you to make a single SQL statement -- which could be a call to a stored procedure or maybe a query -- invoke-able via a REST call with a JSON data payload. Some very helpful information on creating and using DB2 RESTful services can be found in an IBM developerWorks article by Andrew Mattingly, in examples posted to developerWorks by IBM DB2 for z/OS developer Jane Man, and in the DB2 for z/OS IBM Knowledge Center. Keep in mind that because the DB2 REST API is a function of the DB2 for z/OS Distributed Data Facility (DDF), SQL statements and routines invoked via that interface are eligible for zIIP engine offload (up to 60%) just as are any other SQL statements and routines that execute under DDF tasks.

And lest you think that DB2 invisibility is applicable only to "operational" applications...

An invisibility provider for analytical DB2 for z/OS workloads

Data scientists, like application developers, can be more productive when they don't have to think about the particulars of the data server with which they are working. As interfaces such as JDBC and ODBC and REST provide much-appreciated abstraction of data server "plumbing" to application developers, so, too, for data scientists, does an analytics-oriented software technology called Spark.

Spark, produced by the Apache Software Foundation, is an open-source framework for data analytics. It can run natively under z/OS (and on zIIP engines, to boot) because, from an executable standpoint, it's Java byte code (Spark is written in the Scala programming language, which when compiled executes as Java byte code). The IBM z/OS Platform for Apache Spark provides connectors that enable Spark to ingest data from z/OS-based sources, such as VSAM, that do not have a JDBC interface. Add that to Spark's native ability to access data sources with a JDBC interface, such as DB2 and IMS, and data in Hadoop-managed data stores, and you have, with Spark under z/OS, some very interesting possibilities for federated data analytics (Spark builds in-memory structures from data it reads from one or more sources, and subsequent access to those in-memory data structures can deliver high-performance analytical processing).

If you go to one of your organization's data scientists and say, "There is some data on the mainframe that could probably yield for you some valuable insights," you might get, in response, "Thanks, but I don't know anything about mainframe systems." If, on the other hand, you lead with, "We've got some really important data on one of our systems, and we're running Spark on that system, and you could analyze that data using R and uncover some insights that would be very helpful to the company," you might well get, "Okay!" (the R language is very popular with data scientists, and there is an R interface to Spark); so, platform abstraction can be a big plus for analytical, as well as operational, DB2 for z/OS workloads.

And on more thing...

(As Steve Jobs used to like to say)

I generally can't leave a discussion about DB2 for z/OS invisibility (from the application development perspective) without bringing up DB2 row-level locking. I'm often left shaking my head (and have been for over 20 years) over how reluctant many DB2 for z/OS DBAs are to allow the use of row-level (versus the default page-level) locking granularity. It's as though DBAs -- especially verteran DBAs -- think that they will have to turn in their DB2 for z/OS Club cards if they are caught with table spaces defined with LOCKSIZE(ROW). Recall what I pointed out near the beginning of this blog entry: the really meaningful measure of the vitality of the data-serving platform you support is growth in the form of new application workloads. Getting that type of workload growth means presenting a developer-friendly environment and attitude. Platform invisibility is very helpful in this regard, and other relational DBMSs that protect data integrity with locks (including DB2 for Linux, UNIX, and Windows) do so, by default, with row-level locking granularity. Telling a development team with programs that are experiencing lock contention issues with DB2 for z/OS page-level locking in effect that the problem is their code is not going to win you friends, and you want to be friends with your organization's developers. Yes, you should use page-level locking where you can (and that is often going to be for the large majority of your table spaces), but selectively using row-level locking and incurring what is likely to be a relatively small amount of additional CPU cost in return for growth in new-application workload is a good trade in my book. I have more to say about this in an entry I posted to this blog a couple of years ago.

And that's that. Yeah, DB2 for z/OS is your baby, and you like it when people tell you how beautiful your baby is, but to keep that baby healthy and growing it often pays to make the little darling invisible to application developers (and data scientists). When those developers talk about that whatever-it-is data-serving platform that's as solid as a rock, you can smile and think to yourself, "That's my baby!"

Sunday, November 27, 2016

DB2 for z/OS ZPARMs that Organizations Consistently Set in a Sub-Optimal Fashion

Over the past several years, I have reviewed DB2 for z/OS systems running at quite a few client sites. Part of the analysis work I do in performing these reviews involves looking over a DB2 subsystem's DSNZPARM values (or ZPARMs, for short -- the parameters through which the set-up of a DB2 system is largely specified). I have seen that certain ZPARM parameters are very regularly set to values that are not ideal. In this blog entry I will spotlight those ZPARMs, showing how they often ARE set and how they SHOULD be set (and why). Without further ado, here they are:

  • PLANMGMT -- Often set to OFF -- should be set to EXTENDED (the default), or at least BASIC. "Plan management" functionality (which, though not suggested by the name, is actually about packages) was introduced with DB2 9 for z/OS. At that time, it was one of those "not on a panel" ZPARMs (referring to the installation/migration CLIST panels), and it had a default value of OFF. Starting with DB2 10, PLANMGMT landed on a panel (DSNTIP8), and its default value changed to EXTENDED (BASIC is the other possible value). Here is why PLANMGMT should be set to EXTENDED (or BASIC -- and I'll explain the difference between these options momentarily): when that is the case, upon execution of a REBIND PACKAGE command, DB2 will retain the previous "instance" of the package. Why is that good? Because, if the previous instance of a package has been retained by DB2, that instance can very quickly and easily be put back into effect via REBIND PACKAGE with the SWITCH option specified -- very useful in the event that a REBIND results in an access path change that negatively impacts program performance. In this sense, plan management functionality (along with REBIND PACKAGE options APREUSE and APCOMPARE) is intended to "take the fear out of rebinding" (in the words of a former colleague of mine). That is important, because, starting with DB2 10, we (IBM) have been strongly encouraging folks to rebind ALL plans and packages after going to a new version of DB2 (and that should be done upon initial migration to the new version, i.e., in conversion mode if we are talking about DB2 10 or 11). Those package rebinds are critically important for realizing the performance improvements delivered by new versions of DB2, and they also promote stability because system reliability is likely to be optimized when package code -- which is, after all, executable code, being, essentially, the compiled form of static SQL statements -- is generated in the current-version DB2 environment. As for the difference between the EXTENDED and BASIC options for PLANMGMT, it's pretty simple: while both specifications will cause DB2 to retain the previous instance of a package when a REBIND PACKAGE command is executed, EXTENDED will result in DB2 also retaining a third instance of the package, that being the "original" instance -- the one that existed when REBIND was first executed for the package with plan management functionality enabled (you can periodically execute the FREE PACKAGE command for a package with PLANMGMTSCOPE(INACTIVE) to free previous and original instances of the package, to establish a new "original" instance of the package). With plan management functionality serving such a useful purpose, why is PLANMGMT regularly set to OFF? That could be due to a package storage concern that was effectively eliminated with a DB2 directory change wrought via the move to DB2 10 enabling new function mode. Lack of understanding about that change, I suspect, explains why this next ZPARM is commonly set in a sub-optimal way.

  • COMPRESS_SPT01 -- Often set to YES -- should be set to NO (the default). Some organizations have one or more DB2 systems with LOTS of packages -- so many, that hitting the 64 GB limit for the SPT01 table space in the DB2 directory (where packages are stored on disk) became a real concern. This concern was exacerbated by the just-described plan management capability of DB2, because that functionality, as noted above, causes DB2 to retain two, and maybe three, instances of a given package. To allay this concern, DB2 9 for z/OS introduced the COMPRESS_SPT01 parameter in DSNZPARM (another ZPARM that was of the "not on a panel" variety in a DB2 9 environment and landed on a panel -- DSNTIPA2 -- starting with DB2 10). With COMPRESS_SPT01 set to YES, DB2 would compress the contents of SPT01, causing the table space to occupy considerably less disk space than would otherwise be the case. Why would you not want to take advantage of that compression option? Because you don't have to. How is it that you don't have to? Because when the CATENFM utility was run as part of your move to DB2 10 enabling new function mode, one of the many physical changes effected for the DB2 catalog and directory saw the bulk of SPT01 content go to a couple of BLOB columns (and LOB columns can hold a TON of data, as noted in an entry I posted to this blog a few years ago). As a consequence of that change, the relatively small amount of non-LOB data in rows in SPT01 should easily fit within 64 GB of disk space, without being compressed (if, for example, the average row length in the SPTR table in SPT01 were 400 bytes -- and that would mean using unusually long location, collection, and package names -- then SPT01, uncompressed, would accommodate about 160 million packages). It's true that compressing SPT01 does not cost a lot in terms of added CPU overhead, but why spend even a little CPU on something you don't need?

  • UNIT (and UNIT2) -- Often set to TAPE (the default) -- should be set to a value that will cause DB2 to write archive log data sets to disk. Why do you want DB2 archive log data sets to be written to disk? Because, if multiple database objects have to be recovered and those recoveries will all require information from a given archive log data set, the recoveries will have to be SINGLE-THREADED if that archive log data set is on tape. Why? Because multiple jobs cannot access the same archive log data set at the same time if the data set is on tape. If the archive log data set is on disk, the aforementioned recovery jobs can be run in parallel, and the multi-object recovery operation will complete much more quickly as a result. Wouldn't the storage of archive log data sets on disk consume a whole lot of space? Yes, if that's where the archive log data sets stayed. In practice, what typically occurs is an initial write of an archive log data set to disk, followed by an automatic move, a day or two later (by way of HSM, which is more formally known as DFSMShsm), of the archive log data set to tape. In the event of a recovery operation that requires an archive log data set that has been HSM-migrated to tape, there will be a brief delay while the data set is restored to disk, and then multiple concurrently executing RECOVER jobs will be able to read from the archive log data set on disk. [Note that single-threading of RECOVER jobs is forced when archive log data sets are written to virtual tape, even though the actual media used is disk -- it's still tape from a DB2 perspective.]

  • UTSORTAL -- Often set to NO -- should be set to YES (the default). When a DB2 utility such as REORG is executed, and one or more sort operations will be required, by default that sort work will be accomplished by DFSORT. You could, in the JCL of REORG (and other sort-using) utility jobs, explicitly allocate sort work data sets for DFSORT's use. A better approach would be to let DFSORT dynamically allocate required sort work data sets. An EVEN BETTER approach is to have DFSORT dynamically allocate required sort work data sets AS DIRECTED BY DB2. That latter approach depends, among other things, on the UTSORTAL parameter in ZPARM being set to YES. More information on this topic can be found in an entry I posted to this blog back in 2011 -- that information is still applicable today.

  • MGEXTSZ -- Often set to NO -- should be set to YES (the default). This ZPARM pertains to secondary disk space allocation requests for DB2-managed data sets (i.e., STOGROUP-defined data sets -- and all your DB2 data sets should be DB2-managed). For such data sets, you can set a SECQTY value yourself that will be used when a data set needs to be extended, but a MUCH BETTER approach is to have DB2 manage secondary disk space allocation requests for DB2-managed data sets. That will be the case when the ZPARM parameter MGEXTSZ is set to YES, and when SECQTY is either omitted for a table space or index at CREATE time, or ALTERed, after CREATE, to a value of -1. When DB2 manages secondary disk space allocation requests, it does so with a "sliding scale" algorithm that gradually increases the requested allocation quantity from one extend operation to the next. This algorithm is virtually guaranteed to enable a data set to reach its maximum allowable size, if needs be, without running into an extend failure situation. DB2 management of secondary space allocation requests has generally been a big success at sites that have leveraged this functionality (as I noted some years ago in an entry posted to the blog I maintained while working as an independent DB2 consultant, prior to re-joining IBM in 2010). About the only time I've seen a situation in which DB2 management of secondary space allocation requests might be problematic is when disk volumes used by DB2 are super-highly utilized from a space perspective. If space on disk volumes used by DB2 is more than 90% utilized, on average, it could be that DB2 management of secondary space allocation requests might lead to extend failures for certain large data sets (caused by reaching the maximum number of volumes across which a single data set can be spread), due to very high degrees of space fragmentation on disk volumes (such high levels of volume space utilization can also preclude use of online REORG, as there might not be sufficient space for shadow data sets). Personally, I like to see space utilization of DB2 disk volumes not exceed 80%, on average. The cost "savings" achieved by utilizing more than that amount of space are, in my mind, more than offset by the reduction in operational flexibility that accompanies overly-high utilization of space on DB2 disk volumes.

  • REORG_PART_SORT_NPSI -- Often set to NO -- should be set to AUTO (the default) in a DB2 11 or later environment. A few years ago, a new option was introduced concerning the processing of non-partitioned indexes (aka NPSIs) for partition-level executions of online REORG. With the old (and still available) processing option, shadow NPSIs would be built by way of a sort of the keys associated with table space partitions NOT being reorganized via the REORG job, and then entries associated with rows in partitions being REORGed would be inserted into the shadow indexes. With the new option, shadow NPSIs would be built by way of a sort of ALL keys from ALL partitions, and then entries for rows of partitions being reorganized would be updated with the new row ID (RID) values reflecting row positions in the reorganized partitions. It turns out that the new option (sort all keys, then update entries with new RID values, as needed) can save a LOT of CPU and elapsed time for some partition-level REORG jobs versus the old option (sort keys of rows in partitions not being reorganized, then insert entries for rows in partitioned being reorganized). The REORG_PART_SORT_NPSI parameter in ZPARM specifies the default value for NPSI processing for partition-level REORGs (it can be overridden for a particular REORG job), and when it is set to AUTO then DB2 will use the newer processing option (sort all, then update as needed) when DB2 estimates that doing so will result in CPU and elapsed time savings for the REORG job in question (versus using the other option: sort keys from non-affected partitions, then insert entries for partitions being REORGed). I highly recommend going with the autonomic functionality you get with AUTO.

  • RRF -- Often set to DISABLE -- should be set to ENABLE (the default). With Version 9, DB2 introduced a new mode by which columns of a table's rows can be ordered (and here I am referring to physical order -- the logical order of columns in a table's rows is always determined by the order in which the columns were specified in the CREATE TABLE statement). When reordered row format (RRF) is in effect, a table's varying-length columns (if any) are all placed at the end of a row, after the fixed-length columns, and in between the fixed-length and varying-length columns is a set of offset indicators -- one for each varying-length row (the 2-byte offset indicators replace the 2-byte column-length indicators used with basic row format, and each offset indicator provides the offset at which the first byte of the corresponding varying-length row can be found). Reordered row format improves the efficiency of access to varying-length columns (an efficiency gain that increases with the number of varying-length columns in a table), and can reduce the volume of data written to the log when varying-length column values are updated. The ZPARM parameter RRF can be set to ENABLE or DISABLE. With the former value, new table spaces, and new partitions of existing partitioned table spaces, will be created with RRF in effect; furthermore, tables for which basic row format (BRF) is in effect will, by default, be converted to reordered row format when operated on by REORG or LOAD REPLACE utility jobs. Besides the aforementioned benefits of greater efficiency of access to varying-length columns and potentially reduced data volume for logging, RRF is GOING to be the primary column-ordering arrangement in a DB2 12 environment: the RRF parameter in ZPARM is gone with that version, new table spaces WILL be created with RRF in effect, and existing table spaces WILL be converted to RRF by way of REORG and LOAD REPLACE. My advice is to get ahead of the game here, and setting the ZPARM parameter RRF (in a DB2 10 or 11 environment) to ENABLE will help you in this regard.

You might want to examine ZPARM settings at your site, and see if you spot any of these (in my opinion) less-than-optimal specifications. If you do, consider making changes to help boost the efficiency, resiliency, and ease-of-administration of your DB2 systems.