Wednesday, March 31, 2021

Db2 for z/OS: Clearing Up Some Matters Pertaining to PGSTEAL(NONE) Buffer Pools

I have written a few entries for this blog in which I provided information about Db2 for z/OS buffer pools defined with PGSTEAL(NONE), including one from a few years ago in which I differentiated the PGSTEAL and PGFIX buffer pool specifications, and another from just a few months ago in which I explained - among other things - why a PGSTEAL(NONE) buffer pools is referred to as a "contiguous buffer pool" in a Db2 12 environment. With z/OS LPAR real storage sizes getting larger and larger and Db2 people looking - rightly - for more ways to exploit "big memory" to gain performance advantages, PGSTEAL(NONE) buffer pools are getting more attention than ever. That said, I have noticed that there continues to be some misunderstanding among Db2 users about a couple of matters pertaining to PGSTEAL(NONE) pools: the use of such pools for read/write versus read-only tables, and the appropriate use of large real storage page frames for PGSTEAL(NONE) pools. In this blog entry I'll try to clear things up on those two points.

First, a little background (for more background information you can check one or both of the blog entries for which I provided links, above). The PGSTEAL(NONE) specification does NOT mean that Db2 cannot steal buffers in such a pool - Db2 will always steal a buffer in a pool when it needs to, and it needs to when the pool's buffers are all occupied and a page of an object assigned to the pool needs to be read from disk storage into memory. PGSTEAL(NONE), rather, is your way of telling Db2, "There should not be a need to steal buffers in this pool, because I have made it (or I think I have made it) large enough to hold all pages of all objects assigned to the pool." A PGSTEAL(NONE) buffer pool, then, is intended for use as a "pinning" pool (i.e., as a buffer pool in which assigned database objects - table spaces and/or indexes - are "pinned" in memory, which is to say, cached in memory in their entirety). Of course, you could set up a pinning pool long before we got the PGSTEAL(NONE) buffer pool specification with Db2 10 for z/OS - all you had to do was make the pool large enough to completely cache the assigned object or objects in memory. Why, then, was the PGSTEAL(NONE) option provided?

PGSTEAL(NONE) was introduced so that Db2 would know that the pool in question is intended for the pinning of objects in memory. You see, when Db2 realizes that the pool is to be used for completely caching objects in memory, it will pitch in to help in achieving your no-read-i/O goal (no read I/Os after an object is fully in memory, that is). When an object assigned to a PGSTEAL(NONE) buffer pool is first accessed after the pool has been allocated, Db2 will immediately get for the associated process (e.g., an application program) the needed page or pages, so that the process can get on with whatever it's doing; then, Db2 will asynchronously (i.e., via prefetch) read into the buffer pool all of the remaining pages of the object. Voila! The object is now completely in-memory. Db2 12 goes a step further and enhances the efficiency of access to an object's pages in a PGSTEAL(NONE) buffer pool by arranging the pages in a contiguous fashion in the pool, as explained in the second of the two blog entries for which I provided links, above.

That is the gist of PGSTEAL(NONE) buffer pool functionality. On now to the two misunderstandings I want to clear up via this blog entry.

Read/write versus read-only tables

This misunderstanding regarding PGSTEAL(NONE) buffer pools has persisted for quite some time. Somehow, a lot of Db2 for z/OS people got the idea that a PGSTEAL(NONE) buffer pool is appropriate only for objects that are read-only (or very nearly so) in nature. WRONG. Read/write versus read-only access will generally not be an important factor when deciding whether an object would be a good candidate for a PGSTEAL(NONE) buffer pool. So what if a page of an object cached in a PGSTEAL(NONE) buffer pool gets updated? The updated page will be externalized (to disk, probably in an asynchronous fashion, or to a group buffer pool, likely in a synchronous manner as part of commit processing, when the Db2 subsystem is part of a data sharing group) as needed and in the usual way. No problem.

There are basically two things to consider when you're looking for database objects (table spaces or indexes) that would be good candidates for a PGSTEAL(NONE) buffer pool: size and frequency of access. Let's take a closer look at these two factors:

  • Size - That this is a factor should be obvious. Suppose you have a table with a billion rows of data (not unusual - an organization with which I often work has more than 50 Db2 for z/OS tables that each hold more than 1 billion rows of data). That table's rows might take up several hundred gigabytes of space. Could you cache that table in memory in its entirety? Sure you could - an individual Db2 buffer pool can be multiple terabytes in size. Do you want to use that much memory for a single database object? Probably not. With that said, when you think about size as a factor in evaluating candidates for a PGSTEAL(NONE) buffer pool, you don't have to think, "Tiny." As I mentioned in my opening paragraph, z/OS LPAR real storage sizes are getting larger all the time (I have seen plenty of LPARs with several hundred gigabytes of real storage - the largest real storage size for an LPAR that I've personally seen in the real world is 1.1 TB). Db2 buffer pool configurations are growing along with z/OS LPAR real storage sizes (I've seen a real-world Db2 for z/OS subsystem with an 879 GB buffer pool configuration; I've seen an individual buffer pool sized at 253 GB). With a large real storage resource available on a system, could you reasonably consider assigning to a PGSTEAL(NONE) buffer pool an object that takes up several hundred megabytes of space? Maybe a gigabyte or more? Yes, you could. The size-appropriateness, then, of an object for a PGSTEAL(NONE) buffer pool is going to depend in large part on the size of the relevant z/OS LPAR's real storage resource and the degree to which that resource can be used for the Db2 subsystem (even a large real storage resource can be over-utilized - I like to see a z/OS LPAR's demand paging rate at either zero or less than 1 per second).

[Something else to think about when considering the size of an object that you might assign to a PGSTEAL(NONE) buffer pool: online REORG. If you execute an online REORG for an object in a PGSTEAL(NONE) buffer pool, that buffer pool will also be used - understandably - for the object's shadow data set(s). That, in turn, means that you'll either size the PGSTEAL(NONE) buffer pool to accommodate the shadow data set(s), or you'll be prepared for a lot of I/O activity for the buffer pool when the REORG job is running, or you'll think about not REORGing the object (and that may not be so far-fetched - some objects truly do not have to be reorganized).]

  • Frequency of access - Yes, z/OS LPAR real storage sizes are getting bigger and bigger, but mainframe memory isn't free. You want to use it effectively - maximize its value. Can you put a lot of dinky objects in a PGSTEAL(NONE) pool? Yes. If those objects are not accessed particularly frequently, would that be a good use of mainframe memory? Probably not. What you want in a PGSTEAL(NONE) buffer pool are objects that are accessed very frequently - that will give you the biggest bang for your buck, as we say. You may already know which not-too-big (see the discussion of size, above) table spaces and/or indexes in your system get a great deal of access activity. If you don't know that, information in the real-time statistics tables in the Db2 catalog can provide clarity (for example, the GETPAGES column of SYSTABLESPACESTATS and SYSINDEXSPACESTATS).
So, consider object size, and consider frequency of access when looking for good candidates for a PGSTEAL(NONE) buffer pool. Type of access, in the read/write versus read-only sense, is generally not going to be an important factor (yes, read/write access could impact the need for online REORG, and that has an implication that I noted in the [bracketed] paragraph above).

Large real storage page frames

I've often recommended to Db2 for z/OS-using organizations that they take steps to ensure that busy buffer pools - those, I'd say, for which the GETPAGE rate is greater than 1000 per second - are fully backed by large real storage page frames. As I explained in an entry posted to this blog a few years ago, large real storage page frames improve page-access performance by making the translation of virtual storage to real storage addresses more CPU-efficient.

It's likely that a PGSTEAL(NONE) buffer pool in your system will be quite busy, in a GETPAGEs per second sense (I just got through explaining why frequency of access is an important consideration when evaluating candidate objects for a PGSTEAL(NONE) pool). That being the case, you should ensure that such a pool is backed by large real storage page frames, right? Well, maybe - maybe not.

Why the equivocation? Here's the deal. As I explained in my blog entry covering the "contiguous" nature of a PGSTEAL(NONE) buffer pool in a Db2 12 for z/OS system, a given real storage page frame used for a PGSTEAL(NONE) pool will hold pages belonging to one and only one database object. Suppose you want to assign to a PGSTEAL(NONE) buffer pool a pretty good number of small, very frequently accessed table spaces and/or indexes. Fine. That could be a very good use of a PGSTEAL(NONE) pool. Should that pool be backed by 1 MB real storage page frames, for a performance boost? Maybe not. Consider that it takes 256 buffers of 4 KB each to exactly fill a single 1 MB page frame. What if you assign, to a 4K pool defined with PGSTEAL(NONE) and FRAMESIZE(1M), a frequently accessed "code" or "look-up" table whose table space consists of only, say, 20 pages? Those 20 pages will go in one 1 MB page frame, and none of the rest of the space in that page frame will be usable for any other object assigned to the pool. What if there are quite a few such small objects assigned to the pool? That could mean that the actual size of the pool will have to be quite a bit larger than you'd first thought, owing to the unusable space in the 1 MB frames that each are limited to holding the few pages of a single object. Are you OK with that? Maybe you'll decide to just go with 4 KB page frames for this PGSTEAL(NONE) pool, to make more efficient use of memory, and I'd find that decision to be very much understandable. What if objects assigned to a PGSTEAL(NONE) pool have, on average, a few thousand pages apiece? Might you still have some "wasted" space in 1 MB page frames if it's a FRAMESIZE(1M) pool? Yes, but in a proportionate sense this won't be such a big deal. What if an object assigned to the pool has 5000 pages? Those pages will exactly fill nineteen 1 MB page frames, and the 136 pages left over will go in another 1 MB frame, and the remaining space in the 1 MB frame, which could hold another 120 pages of 4 KB each, will not be usable for another object. That would be nineteen 1 MB pages fully utilized, and about 47% of the twentieth 1 MB page frame "wasted," or a little over 2% wastage when you consider all of the twenty 1 MB page frames used for the object. Personally, I wouldn't lose any sleep over that.

Here's something that follows from what I've just explained: if you have identified a set of objects that you want to assign to a PGSTEAL(NONE) pool, and a number of those objects are quite small (with several having maybe fewer than 50 pages apiece) and others are larger (maybe a few thousand pages apiece), use two different PGSTEAL(NONE) pools. Specify FRAMESIZE(1M) for one of the pools, and assign the larger objects to that pool, and assign the smaller objects to the pool for which you're using the default frame size of 4K (assuming we're talking about objects with 4 KB-sized pages). That will give you some performance boost from large frames for the one pool, while ensuring efficient use of memory for the other pool.

By the way, I'm not talking about FRAMESIZE(2G) for a PGSTEAL(NONE) pool because Db2 does not allow 2 GB page frames to be used for a PGSTEAL(NONE) pool. If you want to back a PGSTEAL(NONE) pool with large frames, go with FRAMESIZE(1M).

I hope that this blog entry will be helpful for you. Thanks for being a reader of the blog.

Wednesday, February 24, 2021

Are You Using the REST Interface to Db2 for z/OS?

Db2 for z/OS got a built-in REST interface about four years ago. Have you put it to work? Perhaps you should.

Some information about REST

REST is short for representational state transfer, an architectural style that enables invocation of services through a straightforward, consistent mechanism. It is increasingly popular among application developers in large part because it is lightweight, easy to use, and provides a high degree of abstraction with respect to the particulars of a service-providing system. That can be really helpful when an application's client-side programs have to interact with a number of disparate back-end systems - if the service requests your programs issue look largely the same in form, even though the responding systems are quite different from each other, that is likely to be a big boost to your coding productivity, versus a scenario in which you have to interact in different ways (maybe very different) with the various systems that provide services your programs consume.

Let me show you what I mean. A REST request to get information related to account ID 123456789, issued by a client program and fielded by a Db2 for z/OS system, might look something like this:


Body: { “ID”: 123456789 } 


The response received by the client from the Db2 for z/OS system could look like this:



  “FIRSTNAME” : “John”,

  “LASTNAME”  : “Smith”,

  “BALANCE”   : 1982.42,

  “LIMIT”     : 3000.00


There is nothing in the REST request issued by the client program, and nothing in the request response received by the program, that gives any indication that the request was processed by a Db2 for z/OS system, and that's the point. This type of programming model, sometimes referred to as data-as-a-service, eliminates the need for a client-side developer to know the technical particulars of a request-processing system. Is it a z/OS system? A Linux or a Windows system? Will a relational database management system be involved? Will data be retrieved from flat files, or maybe from a Hadoop data store or a NoSQL database? For the developer of the REST request-issuing program, the answer to these questions can be, "Don't know, don't care - all that stuff's plumbing, as far as I'm concerned." What matters to the developer is that a requested service - retrieval of data for a given account ID, in the example above - worked as expected.

When Db2 for z/OS is the service-providing system, what happens when the REST request is received, and how did that REST service come to be, in the first place? Read on.

What Db2 does with a REST request

So, let's say that the first part of the example REST request seen above,, resolves to a Db2 for z/OS system (in that case, 4711 would be the Db2 system's SQL listener port - it could be the system's secure SQL port, and if that is true then Db2 will require SSL encryption for the transaction). The request would be received by the Db2 distributed data facility (DDF), as Db2's REST interface is an extension of DDF functionality (that brings a cost-of-computing advantage: as many Db2 people know, when a SQL statement is executed via DDF, up to 60% of the CPU cost of SQL statement execution can be offloaded to a zIIP engine). Db2 parses the HTTP and JSON associated with the request (JSON is short for JavaScript Object Notation, and I'm referring to the request's input of { "ID": 123456789 } - more on that in a moment), and then does security checks. Security check number one is for user authentication purposes. Db2 requires that a REST request be associated with either an ID and a password or an ID and a certificate (if a password is supplied, it will be in the HTTP header of the request). The ID and password (or certificate) will be validated by RACF (or an equivalent z/OS security manager), and if authentication is successful then we move on to security check number two (note that, additionally, a RACF profile for REST access to the target Db2 subsystem can be defined, giving you the ability to permit an ID to access the Db2 subsystem via the REST interface but not through another interface such as CICS, or vice versa).

That second security check is of an authorization nature: does the ID associated with the REST request have the EXECUTE privilege on the Db2 package associated with the requested service? If that required Db2 privilege has indeed been granted to the ID (or to a group ID to which the ID associated with the REST request is connected), it's package execution time: the package is allocated for execution to the REST request's Db2 thread (a DDF thread, also known as a DBAT - short for database access thread), and the corresponding SQL statement is executed (with the input that accompanied the request, if input is required and supplied).


The Db2 REST service example I have used involves execution of this SQL statement:









The input value supplied with the REST request, account ID 123456789, is substituted for the parameter marker (the question mark) in the query text, and the result set is sent, in JSON format, to the REST client. That output document, as we saw before, looks like this:



  “FIRSTNAME” : “John”,

  “LASTNAME”  : “Smith”,

  “BALANCE”   : 1982.42,

  “LIMIT”     : 3000.00



This JSON document shows what a one-row query result set would look like when sent to the REST client. If it were a multi-row result set, the output JSON document would contain several sets of name-value pairs - one such set for each result set row.


A word about these JSON documents used for Db2 REST service input and output "payloads": this is another aspect of the REST model that appeals to a lot of developers. Though it is not technically required that input and output data associated with REST requests be in the form of JSON documents, that tends to be the case, and with good reason: JSON documents, as you can see, are really easy to form and to parse (by programs and by people). That's not always true for, say, XML documents. JSON also tends to be "lighter weight" versus XML - fewer bytes are transmitted between client and server.


With regard to the name-value pairs seen in the JSON documents associated with a Db2 REST service, let's consider first the input. We saw, for my example, { "ID": 123456789 }. Where did that "ID" come from? It's the name of the Db2 table column referenced in the query predicate (referring to the SELECT statement associated with the REST service) that is coded with a parameter marker. "ID" is a pretty intuitive column name. What if that were not the case? What if you wanted to have a Db2 REST service for retrieval of information related to a product ID, and the table column had an obtuse name like PRID? In that case, the predicate of the query to be REST-enabled could be coded with a host variable instead of a parameter marker, and the host variable name could then be used in the input document for the REST request instead of the column name (so, if the query predicate were of the form WHERE PRID = :PRODUCT_ID, the input JSON document could be { "PRODUCT_ID": 56789 } instead of { "PRID" 56789 }).


For an output JSON document that contains data from a query result set, the names in the name-value pairs are, by default, the names of the columns in the query's select-list. As noted above, you could have a table column with a not-very-intuitive name. That situation can be addressed by renaming the column in the query select-list. If, for example, you want to return a mobile phone number from a column named MONUM, you could code the query that you're going to REST-enable in this way: SELECT MONUM AS MOBILE_NUMBER, ... Having done that, you'll have an output JSON document with "MOBILE_NUMBER" : "111-222-3333" instead of "MONUM" : "111-222-3333".


How Db2 for z/OS REST services are created


I've referred to packages associated with Db2 REST services, and that might cause you to think that what you're REST-enabling is static SQL, and you'd be right about that: the Db2 capability I'm talking about enables invocation of a pre-coded, server-side, static SQL statement in response to receipt of a properly coded (and security-checked) REST request. And yes, I do mean a single statement. Sometimes that's all you need for a given Db2 REST service, and in such a case the single SQL statement can be a SELECT, INSERT, UPDATE, DELETE or TRUNCATE. What if you want a Db2 REST service to involve execution of several SQL statements? Can that be done? Absolutely, because the single static SQL statement for which you create a REST service can be a CALL of a Db2 stored procedure, and the called stored procedure could issue any number of SQL statements (if you REST-enable a CALL of a stored procedure, I'd recommend going with a native SQL procedure because execution of such a stored procedure, when the call is through DDF, is up to 60% zIIP-offload-able).


With a SQL statement that you want to REST-enable, you have two service-creation options. One option is to use the Db2-supplied REST service called DB2ServiceManager (a REST service that can be used to create a REST service from a SQL statement). Information on using DB2ServiceManager to create a REST service from a Db2 SQL statement (including an example) can be found in the Db2 for z/OS Knowledge Center on IBM's Web site.

Another option for creating a REST service from a SQL statement is the Db2 command BIND SERVICE, which can be used in a batch job such as the one shown below:



//         DD  DSN=DB2A.SDSNLOAD,DISP=SHR      

//SYSTSPRT DD  SYSOUT=*                        

//SYSPRINT DD  SYSOUT=*                        

//SYSUDUMP DD  SYSOUT=*                        


//             DISP=SHR                        

//SYSTSIN  DD  *                               

 DSN SYSTEM(DB2A)                              



  NAME("simpleSelect1") -                      

  SQLENCODING(1047) -                          

  DESCRIPTION('return a list of deptname-      

 based on input location') 


More information about BIND SERVICE can be found in the Db2 Knowledge Center. Also in the Knowledge Center is additional information on invoking a Db2 REST service. And, check out the series of helpful videos on creating and invoking Db2 REST services (in - yes - the Db2 Knowledge Center).

Db2 REST service capabilities, made even better

As I noted at the beginning of this blog entry, the REST interface is built into Db2 for z/OS: if you have Db2, you have what you need to create and use Db2 REST services. With that said, if you want to have even greater ease of use in creating Db2 REST services, have your Db2 REST services described using Swagger (a standard specification with which many developers are familiar), have more flexibility in invoking Db2 REST services, and have more options for formatting the JSON output document associated with a Db2 REST service, you'll want to take a look at an IBM product called z/OS Connect. In an entry I'll post to this blog in the near future, I'll expand on what z/OS Connect can do to make Db2 REST services even more valuable for your organization.

Wednesday, January 27, 2021

Db2 for z/OS: Why You Might See a Lot of Catalog Access for Package Authorization Checks

From time to time I will receive a note from a Db2 for z/OS DBA, asking for help in understanding why there is a larger-than-expected amount of Db2 catalog access activity associated with package authorization checks, and what can be done to address that situation. In my experience there are two primary causes of this observed Db2 behavior, one more straightforward than the other. In this blog entry I'll describe these two drivers of head-scratchingly high numbers for catalog accesses related to package authorization, and I'll provide associated mitigating actions that you can take.

Before proceeding, a couple of points of clarification. When I mention "package authorization," what I'm talking about, in the context of this blog entry, is authorization to execute a package (not authorization to create a package by way of a BIND PACKAGE operation - that's a different subject). Also, this whole discussion is relevant primarily to packages that are the compiled and executable form of what Db2 people refer to as static SQL statements (there are other packages that essentially enable preparation and execution of dynamic SQL statements). Such a static SQL package could be associated with a Db2 stored procedure, or with a CICS-Db2 transaction program, or with a batch job. If the application process requesting execution of a static SQL package is local to the Db2 subsystem (i.e., it is not accessing the Db2 system by way of a network connection and the Db2 distributed data facility), the package will be executed in conjunction with a Db2 plan, with which it will be associated by way of the plan's package list (more on that to come).

OK, on to the first of the two aforementioned drivers of unexpectedly high catalog access activity related to package authorization checking.

A too-small package authorization cache

In a Db2 system in which the workload involves execution of a lot of static SQL statements, there can be a LOT of package authorization checks - like, hundreds per second. To help make this authorization checking as efficient as it can be, Db2 has, in its database services address space, an in-memory cache of package authorization information. When Db2 checks to see if ID ABC is authorized to execute package XYZ, it will check the package authorization cache first, to see if the authorization information is there. If that information is not found in the cache, Db2 checks the catalog (the SYSPACKAUTH table, for example) to see if ID ABC is authorized to execute package XYZ. The information found in the catalog is then placed by Db2 in the package authorization cache in memory, so that the next time a check of ID ABC's authorization to execute package XYZ is needed, the check can be very quickly and efficiently accomplished via the cache.

All well and good, but sometimes there's a bit of a problem here, and it has to do with the size of the in-memory package authorization cache. If it's too small, there could be a lot of cache "misses" for package authorization checks, and those misses will drive catalog accesses. How can you tell if this is happening? Use your Db2 monitor to generate a statistics long report for the Db2 subsystem of interest, covering a busy hour of a busy day (depending on the Db2 monitor in use at your site, this report might be called a statistics detail report). In that report, you should see a set of fields under the heading AUTHORIZATION MANAGEMENT, such as those shown in the report snippet below (headings and field names might vary slightly from one Db2 monitor to another).


---------------------------  --------  -------

PKG-AUTH SUCC-W/O CATALOG     5885.1K  1634.76

PKG-AUTH UNSUCC-CACHE        43464.00    12.07

These numbers look good. Why do I say that? Because more than 99% of the time, a package authorization check was completed without a requirement for catalog access (1634.76 + 12.07 package authorization checks per second, with only 12.07 of those requiring catalog access). There were very few package authorization cache "misses" (as indicated by the smallness of the values in the PKG-AUTH UNSUCC-CACHE row relative to the values in the PKG-AUTH SUCC-W/O CATALOG row).

Sometimes, the numbers in this part of a Db2 monitor statistics long report don't look so good.  I've seen that the value in the PKG-AUTH UNSUCC-CACHE row can be as high as several hundred per second (even over 1000 per second). When that's the case, it can be an indication that the package authorization cache is way smaller that it should be. Why might that be the case? Well, the size of the cache is determined by the value of the CACHEPAC parameter in ZPARM, and prior to Db2 10 the default CACHEPAC value was only 100 KB. Sometimes, an existing ZPARM value is carried forward when a Db2 system is migrated to a new version, and that old and small CACHEPAC value did indeed go forward at quite a few sites; so, check the value of CACHEPAC for your Db2 system. If it is anything less than the max value of 10M (meaning, 10 MB), change the value to 10M.

Having said all this, I'll tell you that depending on the maintenance level of your Db2 system, you might look for CACHEPAC in your ZPARM listing and NOT SEE IT. Why is that? It's because the fix for APAR PH28280, which came out in the latter part of 2020, removed CACHEPAC from ZPARM and set its value internally to 10M (that fix did the same for CACHERAC, the parameter that specifies the size of the routine authorization cache, used to check authorization to execute Db2 routines such as stored procedures and user-define functions).

OK, so a too-small package authorization cache can lead to high levels of catalog access activity related to package authorization checks. You can make the size of that cache what it should be, which is 10M (or IBM will do that for you, via the fix for the aforementioned APAR), but there's something else that could cause a lot of catalog access for package authorization checks...

Location asterisks in a plan's PKLIST

I noted earlier that a static SQL package used by a local-to-Db2 application will be executed in conjunction with a Db2 plan (remote client applications that access Db2 via the Db2 distributed data facility are associated, by default, with plan DISTSERV, which is not a plan in the traditional sense). The plan for a local-to-Db2 application has something called a PKLIST, which is short for package list. In a plan's PKLIST are the packages that can be executed by way of the plan. You can make use of asterisks in a plan's PKLIST in a couple of ways. One way, which is very common and helpful, is to use an asterisk for the packages in a given package collection; so, if packages in collection COLL1 are to be executed through plan PLAN1, the PKLIST for PLAN1 can have the entry COLL1.*. That covers all the packages in collection COLL1, and it's a lot more convenient than putting each individual package in COLL1 in the PKLIST for PLAN1.

Another use of asterisks in a plan's PKLIST can be problematic. I'm referring here to the location of a collection. You could have, in the PKLIST for PLAN1, the entry *.COLL1.*, and here's what that high-order asterisk means: it means that PLAN1 can be used to execute packages in collection COLL1 at the local Db2 system and at any remote location at which a collection named COLL1 might exist. Sometimes, there actually is a collection COLL1 at a remote server, and there is a need for packages in the remote COLL1 collection to be executable through PLAN1. Usually, that is not the case - the plan is only going to be used with local packages. "So what?" you might say, "If PLAN1 is only going to execute packages in the local COLL1 collection, it can do that it it has an *.COLL1.* entry in its PKLIST." True, but here's the deal: if there is an asterisk in the location-name part of an entry in a plan's PKLIST, there will be a package authorization check done every time a package in that collection is executed via the plan. That, in turn, could lead to a lot of associated catalog access activity. If, instead, the entry is the plan's PKLIST is COLL1.* (i.e., nothing - not even an asterisk - is specified for the collection's location), that will be as though the local Db2 had been explicitly specified as the collection's location, and that single-location specificity will mean that authorization to execute the packages in COLL1 is checked at BIND PLAN time and will NOT be checked when packages in COLL1 are subsequently executed via the plan (the authorization checked at BIND PLAN time is that of the ID of the process issuing the BIND PLAN command - for example, that ID may have been granted EXECUTE ON PACKAGE COLL1.*).

With this in mind, if for a local-to-Db2 application involving static SQL execution you see an unexpectedly high level of catalog accesses (specifically, accesses to catalog tables such as SYSPACKAUTH), check the entries in the plan's PKLIST. If you see an entry of the form *.COLL1.*, you might want to do one of two things: remove the asterisk in front of the collection's name if the collection is local; or, if packages in a collection with the specified name at remote location LOC2 will be executed via the plan, change an entry of the form *.COLL1.* to LOC2.COLL1.*. What if the plan will be used to execute packages both in a local collection named COLL1 and in a COLL1 collection at location LOC2? In that case, have a COLL1.* entry in the plan's PKLIST for the local collection COLL1, and a LOC2.COLL1.* entry for the collection COLL1 at location LOC2. Yes, this is more of a hassle than just putting an *.COLL1.* entry in the plan's PKLIST, but it can result in elimination of execution-time package authorization checks (and associated catalog access activity).

And there you have it. To make package authorization checking as efficient as it can be, make the size of the package authorization cache as large as it can be, which is 10M (if you have not already done this or if Db2 has not already done that for you via the fix for APAR PH28280), and - for local-to-Db2 applications using static SQ packages - don't use an asterisk for the location of the packages' collection in the relevant plan's PKLIST (instead, don't specify anything - not even an asterisk - for the location of a local collection, and explicitly specify the remote location name of a not-local collection).

I hope this information will be useful for you.

Thursday, December 31, 2020

Db2 for z/OS: What Do You Know About Rebind Phase-In?

For many years, DBAs at a lot of Db2 for z/OS sites have been challenged in performing an important operational task: rebinding a frequently executed package. This can be tricky because a package cannot be rebound while it is in use, and if a package is executed with great frequency - in some environments, certain packages are executed hundreds of times per second during busy periods - then its use count may only rarely go to zero. When a REBIND command is issued for such a package, there are two likely outcomes, neither of which is desirable: either the REBIND command times out because the target package is continuously in-use, or the REBIND gets going thanks to a little gap that emerges in the package's in-use status. Why is that latter outcome potentially undesirable? It is undesirable because once the rebind of a package is underway, a subsequent request to execute the package must wait for the rebind to complete. Yes, a package rebind is usually completed quite quickly, but if rebind processing for a package is delayed for some reason, even if only for a few seconds, that could trigger application-side time-outs - not good.

Could you just rebind a high-use package at a less-busy time? Perhaps, but sometimes there is a pressing need to rebind a package now, not later. Besides that, in an increasingly 24X7 world you might have packages that are rarely not-in-use even outside of peak periods. Think, for example, about the packages associated with the IBM Data Server Driver (which you can think of as the successor to Db2 Connect). These packages, whose default collection is named NULLID, are used by applications (other than REST clients) that access a Db2 for z/OS system via Db2's distributed data facility (DDF). A DDF transaction rate in the thousands per second is not terribly unusual these days, and that rate can remain pretty high around the clock, making rebind of the NULLID packages a daunting prospect for some DBA teams.

The rebind challenges associated with ever-higher and ever more round-the-clock package execution frequency are made more serious by the growing need for package rebind actions. There was a time when an organization might rebind packages (and plans) only infrequently, outside of the rebinds that are recommended when you migrate a system to a new version of Db2 (if your organization does not rebind all plans and packages following migration to a new version of Db2, I recommend that you address that situation). Nowadays, we have newer reasons to rebind packages - you might change a RELEASE specification to DEALLOCATE, perhaps to enable high-performance DBAT functionality; you might rebind a package to get a performance gain after executing RUNSTATS to leverage the statistics profile enhancements introduced with Db2 12; you might rebind a package with the new (with Db2 12) CONCENTRATESTMT option, to "turn on" Db2's statement concentration feature for an application from the server side (statement concentration could formerly only be activated by a client-side action); and so on.

The IBM Db2 for z/OS development team recognized some time ago that rebind concurrency was an issue that needed to be addressed. The bind concurrency feature provided by Db2 11 for z/OS definitely helped, but more was needed. That "more" was largely delivered via the rebind phase-in capability enabled by way of Db2 12 function level 505. Although the fix (for APAR PH09191) that takes a Db2 system's code to the 505 level came out about 18 months ago, I have found that quite a few Db2 for z/OS people are not aware of what rebind phase-in is and how it works; so, I'll provide explanatory information in the remainder of this blog entry.

In a nutshell, rebind phase-in allows a package to be immediately and successfully rebound even if the package is in-use at the time the REBIND PACKAGE command is issued. Not only that, but when rebind phase-in is in effect, a package execution request that comes in following the issuance of the REBIND command will not be held up, even if the rebind of the package has not yet completed. Gotta love that.

So, how is this goodness made possible? Well, it's based on a Db2 capability, called plan management, that was introduced with Db2 9. When plan management is fully in effect (when the PLANMGMT parameter of ZPARM is set to its default value of EXTENDED), Db2 can retain up to three instances (referred to as copies) of a given package: the original copy, the previous copy, and the current copy. These package copies are retained primarily for the purpose of "taking the fear out of rebinding" (as a former colleague of mine cleverly put it) - if you rebind a package and access paths change in a way that negatively impacts performance, you can quickly and easily go back to the previous copy of the package by issuing a REBIND PACKAGE command with the SWITCH(PREVIOUS) option.

The copies of a package retained by way of plan management functioning in extended mode are differentiated by a number: copy 0 is the current instance of a package, copy 1 is the previous instance, and copy 2 is the original instance. Rebind phase-in builds on this functionality, and in fact having PLANMGMT in ZPARM set to EXTENDED is required if you want to take advantage of the rebind phase-in feature. I'll illustrate with an example: suppose that you issue a REBIND command for package ABC, and Db2 (Version 12, with function level 505 or higher activated) sees that the package is currently in use. In that case, Db2 will utilize rebind phase-in processing (if the package is not in-use at the time of the issuance of the REBIND PACKAGE command, the rebind request will be processed in the traditional way). A new instance of package ABC will be generated, even as the current instance of the package is being used, and this will be do-able because the newly-generated instance of the package will be identified as copy 4 (we jump from copy identifier 0 to 4 because - as noted above - 1 identifies the previous instance of the package and 2 identifies the original, and identifier 3 has been reserved for future use by Db2). If a request to execute package ABC comes in before generation of the new copy 4 has completed, copy 0 of the package will be allocated to the requester's Db2 thread for execution. Requests for execution of package ABC that come in after copy 4 has been generated will result in allocation of copy 4. In time (quickly if the package is bound with RELEASE(COMMIT) - a little longer if the package is bound with RELEASE(DEALLOCATE) and is allocated to a persistent thread such as a high-performance DBAT), instances of copy 0 of package ABC will be released from the threads to which they had been allocated for execution, and copy identifier 0 will then go back into the pool of numbers available for package differentiation when package ABC is again involved in rebind phase-in processing. That pool, for any given package, is the number 0 together with the numbers 4-16. When package ABC, the current copy of which is now 4, is again rebound while in-use, the new instance of the package will be identified as number 5. If package ABC is rebound in a phase-in way enough times for its current copy identifier to be 16, and it is again rebound while in-use, the new copy will be identified as 0, after which the numbers 4-16 will again be used as needed.

Note, by the way, that rebind phase-in processing is also used when a REBIND PACKAGE command with a SWITCH specification is issued and the target package is found to be in-use. Let's say that the current instance of package XYZ is copy 4 (meaning that copy 0 was current when package XYZ was rebound in a phase-in way). If a REBIND PACKAGE command with SWITCH(PREVIOUS) is issued for package XYZ while the package is in-use, the previous instance of the package (always designated as copy 1) will be replicated to generate the new current instance of the package, which will be identified as copy 5. New requests to execute package XYZ will result in copy 5 being allocated to the requester's thread. Copy 4 of package XYZ, the previously current copy which is now the phased-out copy, will be replicated to become the new previous instance of the package (copy 1, as always).

Also note that rebind phase-in functionality is not applicable to packages associated with SQL PL routines (native SQL procedures, compiled SQL scalar functions and advanced triggers). This is not as big a deal as it may seem, owing to the fact that there were already ways to non-disruptively generate and activate a new instance of a SQL PL routine. For example, a new instance of a native SQL procedure can be non-disruptively generated and activated via an ALTER PROCEDURE statement with an ADD VERSION specification, followed by a second ALTER PROCEDURE statement with ACTIVATE VERSION specified (referencing the version added via the ALTER PROCEDURE with ADD VERSION).

Rebind phase-in, enabling successful and non-disruptive rebind operations even for packages executed with great frequency, is one more way in which the IBM Db2 for z/OS development team has made life easier for Db2 DBAs. I hope that you will find this functionality to be useful at your site.

Tuesday, November 24, 2020

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

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

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

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

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

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


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

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

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

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

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


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

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

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

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

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

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

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


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

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

Thursday, October 29, 2020

Db2 for z/OS CHAR versus VARCHAR, Part 2 - a DBA's Story

Last month, I posted to this blog an entry in which I provided my thoughts on the CHAR (fixed-length) versus the VARCHAR (varying-length) data type for character string columns in Db2 for z/OS tables. Soon after, I received a note from a longtime Db2 for z/OS DBA whom I've known for years. He had read the aforementioned blog post, and that prompted him to share with me a story of a VARCHAR issue with which he was presently dealing. I found it to be a quite interesting story, and I am sharing it by way of this post.

My DBA friend made an interesting discovery while analyzing indexes to see if space savings could be achieved via ALTER with NOT PADDED, for indexes created with a PADDED specification and defined on a key containing at least one varying-length column (more on NOT PADDED and index space savings at the end of this entry). He saw that there was a table column, defined as VARCHAR(50), that appeared to contain 50 bytes of data in every row of the table. Curious, he investigated further and found that, in fact, most of the bytes in most of those column values were trailing blanks - for some of the table's rows, ALL of the bytes in the column were blanks. The use of VARCHAR(50) instead of CHAR(50) was not saving ANY space for this column - in fact, it was causing a little more space to be used versus CHAR(50), owing to the 2-byte offset indicator for the VARCHAR column (assuming the use of reordered row format - with basic row format there would still be a two-byte indicator for each varying-length column, but it would be a length indicator instead of an offset indicator).

Now, a Db2 for z/OS DBA might be inclined to think, on having made such a discovery, "What were those application developers thinking? Why in the world would they pad a value (even a non-existent value) with blanks to completely fill up a VARCHAR column?" I would tell you, in that case, "First of all, chill out. Application developers who write programs that access Db2 for z/OS data are your best friends - don't rush to accuse them of malfeasance. Second, this odd fill-up-the-VARCHAR-column application behavior may not be - in fact very likely is not - the fault of a development team."

"Not the developers' fault?" you might ask. "What do you mean? How could it not be the fault of developers?" Two words: code generator. My DBA friend whose story I'm retelling here discovered that it was a code-generation tool that was padding to-be-inserted values, with blanks, to the maximum length allowed by a VARCHAR column. Not only that, but he reached into his personal memory bank and recalled a very similar situation he had encountered some years ago, when working for another company: similarly, values inserted into VARCHAR columns of Db2 tables were padded with blanks to the maximum allowable length, and similarly, this behavior was traced to the action of a code generation tool, as opposed to code written by application developers.

The DBA named names with regard to the code generation tool used at his site, and the different code generator product used at his former place of work. I won't name those products here, but I recognized the names and a lot of you would, too. My point: you may have, in your Db2 for z/OS environment, the same issue my DBA friend encountered in his. You, too, may have VARCHAR columns in Db2 tables that appear to be as full as they can be with data values, when in fact the columns largely - perhaps mostly - contain blanks (hexadecimal '40' values, if we're talking about EBCDIC-encoded tables).

How could you make this determination? You could do what the DBA at the center of this story did: issue a query similar to the one below, for VARCHAR column C1 of table T1 (and note that the really important information about trailing blanks is provided via the RTRIM built-in Db2 function - the STRIP function was used, as well, to see how prevalent leading blanks were in column values, in addition to trailing blanks):


That query yielded this result:

1,493,634,404   844,348,683     844,345,206    89,019,782

Wow. Over 43% of the space occupied by values in the table column was filled with trailing blanks that had been added by the code generation tool to actual values as part of insert processing (1,493,634,404 - 844,345,206 = 649,285,721 trailing blanks, and 649,285,721 / 1,493,634,404 = 43.4%). The DBA did some due diligence to verify that data-retrieving programs would continue to function properly if, say, a SELECT of C1 from T1 for a given row returned 'ABCDEFGHIJ' instead of 'ABCDEFGHIJ' followed by 40 blanks. When it was determined that the functionality of data-retrieving programs would not be adversely impacted by the removal of trailing blanks from values in column C1 of table T1, the DBA initiated a process of removing those trailing blanks. Execution of that process has freed up disk space and improved the efficiency of Db2 utilities operating on the table. It has also resulted in leaner indexes.

Indexes? Yep, and it was an initial focus on indexes that led to the discovery of the padded-out column values. The DBA noticed that a number of indexes at his site with keys that included varying-length columns were defined with the PADDED option, so that the values would be padded to their maximum length in index entries. He figured that altering these indexes to NOT PADDED would save disk space and potentially lead to a reduction in the number of levels for the indexes (fewer index levels means fewer GETPAGEs for processes that access table rows using the index, and GETPAGEs are a major factor in the CPU cost of SQL statement execution). When the DBA issued an ALTER with NOT PADDED for a PADDED index that had a varying-length column in its key, and saw that the index did NOT get smaller as a result, he investigated to see why that was so, and that investigation led to the discovery of the VARCHAR column whose values were padded to max length with trailing blanks. Thanks to the aforementioned process that is removing those trailing blanks, the change to NOT PADDED for associated indexes is now yielding positive results.

You might want to do a little investigating at your site, to see if you have Db2 tables with VARCHAR columns that in fact are not varying-length because their values are all padded with trailing blanks to their maximum length (the query, shown above, that my DBA friend used could be helpful to you). If you find one or more such columns, do the due diligence that I mentioned - work with developers to ensure that removing the trailing blanks would do no harm, program functionality-wise. If you get an "all clear" from that due diligence work, get rid of those trailing blanks and watch the tables and associated indexes (if the latter are defined with NOT PADDED) become more space-efficient, and watch application and utility processes that access the tables and indexes become more CPU-efficient. This can be your story, too.

Wednesday, September 30, 2020

Db2 for z/OS Character Columns: Thoughts on CHAR versus VARCHAR

Recently, I received from a Db2 for z/OS DBA a question: for a table column that will hold character string values, are there any recommendations concerning the use of CHAR (fixed-length) versus VARCHAR (variable-length) for the column's data type?

That question has been asked, I think, many times by many different people over the 35+ years since Db2 made the scene. Even so, I find it to be an interesting query to this day. Why? Because various Db2 developments - some relatively recent and some from way back - have caused the answer to change over time. You can read on and see if you agree with me.

Now, it is of course true that in at least one case the question has a very simple answer: if a value to be stored in the column can exceed 255 byes in length, CHAR cannot be the data type because 255 bytes is the maximum length you can specify for a fixed-length Db2 character string column. Longer than that and it has to be VARCHAR, right? Probably right, but maybe not - you could go with CLOB. Do you think that CLOB is the right choice only when the length of a character string value that will be stored in a column might exceed 32,704 bytes - the maximum length of a VARCHAR column? I'd say that that is not always true - there could be a situation in which you'd choose CLOB over VARCHAR even if no value longer than 32,704 bytes will go in the column. Suppose that the maximum length of a character string value that will be stored in a table is "only" 20,000 bytes, but the column will very rarely be referenced in a query's select-list and maybe never (or almost never) referenced in a query predicate. In that case, CLOB could be a good choice for the column's data type. Why? Because CLOB values (unless in-lined, and you wouldn't inline CLOB values that are rarely retrieved and rarely referenced in predicates) are physically stored in an auxiliary table in a LOB table space. The LOB table space could (should) be assigned to a buffer pool other than the one used for the base table's table space, and THAT would allow a lot more of the base table's pages (sans the LOB values) to be cached in a given amount of buffer pool space. You'd also have more rows in each base table page. More pages in memory and more rows per page means fewer GETPAGEs, fewer read I/Os, better CPU efficiency and better throughput. CLOB can be the right choice for a character string column, even when VARCHAR is technically usable.

How about the other end of the column-length spectrum: super-short character strings? Here again, the best choice is often pretty clear, owing to the two bytes of "baggage" that come along with a variable-length character column. For many years, you knew what that two bytes of baggage was: it was the length indicator for the VARCHAR column. These days, it could be a length indicator, or it could be an offset indicator. It will be one or the other depending on whether basic row format is used for the table's table space or partition (in which case it will be a length indicator) or reordered row format is in effect (in which case it is an offset indicator). I'll have more to say about basic versus reordered row format in a moment, but either way you're looking at two extra bytes for a varying-length value. Because of those two extra bytes, going with VARCHAR for a column that will hold hold values with a maximum length of one or two bytes does not make the best use of disk or buffer pool space - for optimal efficiency you should use CHAR(1) or CHAR(2) for a column that will hold values that will be, respectively, one byte or not more than two bytes in length.

Having considered values longer than 255 bytes and shorter than 3 bytes, we are left with the byte-length range 3-255 to ponder. Within that range, you could go with CHAR or VARCHAR. What might influence your decision? Here are some thoughts:

  • What is the variability in length of values that will go in the table column? The greater the variability, the more space that could be saved via use of VARCHAR versus CHAR (though table space compression, which is widely used, offsets much of this advantage of VARCHAR).
  • How important is it to make access to the data values in a column absolutely as efficient as possible? Maximum efficiency of access is generally associated with fixed-length columns, though reordered row format (RRF) makes access to VARCHAR columns more efficient than it used to be. How does reordered row format do that? It does that by physically storing variable-length columns at the end of a table's rows (logical column order is not affected by reordered versus basic row format) and preceding those variable-length columns with a series of 2-byte offset indicators - one for each variable-length column. The offset indicator for a given variable-length column shows where in the row the first byte of that column can be found. This is good for performance because to access a value in a variable-length column, Db2 goes straight to that column's offset indicator (easily done, because Db2 knows which of those 2-byte indicators it is going to, and the offset indicators are preceded by whatever fixed-length columns are in the row). Having gotten efficiently to the right offset indicator, Db2 sees exactly where to go to get to the variable-length column's first byte - boom-boom, done. Contrast that with the basic row format (BRF) situation, in which physical column order is the same as logical order in a row. If Db2 wants to access the fourth variable-length column in a BRF row, it can't get there without checking the length of each of the three variable-length columns ahead of the column of interest. Now, does RRF make access to variable-length columns just as efficient as access to fixed length columns? Not quite, but RRF does reduce the access-efficiency gap between fixed-length and variable-length columns.
  • How important is it to minimize the volume of data written to the Db2 log? Where UPDATE operations are concerned, Db2 tends to log fewer bytes when fixed-length character columns are updated versus updating of values in variable-length columns. That said, as reordered row format reduces the access-efficiency penalty associated with variable-length columns (as explained above), so too does RRF reduce the logging-volume penalty that goes along with such columns. How so? Well, when one fixed-length column in a row is updated, just that column change is logged. When a variable-length column is updated and that update changes the row's length (typically the case), data is logged from the first changed byte to the end of the row. Because RRF physically moves variable-length columns to the end of data rows, there will likely be fewer bytes between a variable-length column and the end of its row, and that means fewer bytes written to the log when that variable-length column is updated in a way that changes the column's (and thus, the row's) length. I have to say here that data compression muddies the water a bit, since with compression in effect an update of even a fixed-length column could change a row's length, leading to more bytes logged due to update activity (though that effect can be mitigated by the fact that data associated with changes to compressed tables is logged in compressed form). Still, when all is said and done, it is likely that maximum efficiency from a log-write perspective would be achieved by use of fixed-length versus variable-length columns, particularly for tables in uncompressed table spaces. You might have - or might want to have - heavily updated tables in uncompressed table spaces when those tables are not too large (the value of compression is a little less compelling for tables that are not so big).
  • Do you want to avoid row-location changes that result from UPDATEs that increase a row's length? When an UPDATE of a variable-length column increases a row's length so that the row will no longer fit in its page, Db2 will relocate the row in the table and will put in the old location a pointer to the row's new location. That's called an indirect reference, and those can have a negative impact on data-access efficiency (REORGs clean those indirect references up). Use of fixed-length character columns tends to result in fewer indirect references for a table (with data compression being a bit of a muddying factor, as it is for log-write volumes, and for the same reason: an update of a fixed-length column in a compressed row can cause that row to get longer). That said, the indirect-reference penalty associated with variable-length columns can be mitigated through use of the PCTFREE FOR UPDATE table space specification that was introduced with Db2 11 for z/OS (I posted an entry on PCTFREE FOR UPDATE to this blog a few years ago).

And with that, I've provided just about everything I can think of on the subject of CHAR versus VARCHAR for character string values. I'd sum it up this way: the CHAR versus VARCHAR choice is most interesting for character string values that will be between 3 and 255 bytes in length. For such columns, there are situations that would favor CHAR versus VARCHAR, and vice versa. Even so, Db2 features and functions such as table space compression, reordered row format, and PCTFREE FOR UPDATE can reduce the negative effects associated with the use of CHAR or VARCHAR in these various situations. My advice to you: take advantage of Db2 functionality, and make the CHAR and VARCHAR decisions that seem reasonable to you given your application and data environment.