Bringing Control to a DB2 for z/OS Client-Server Workload (Part 1)

At System z sites all over the world, DB2 for z/OS workloads are growing -- often at a brisk clip. You'd think that this would be unqualified good news for the folks who support mainframe DB2 subsystems, but in fact some people view a lot of this new DB2 activity with trepidation. Why? Because the lion's share of application work being implemented these days on a DB2 for z/OS foundation is of the client-server variety. In other words, it involves data access through DB2's distributed data facility, aka DDF. OK, so what's the problem with that? The problem has to do with the belief of some individuals that a DB2 DDF workload is one that they cannot control.

I'll tell you up front that this notion of a DB2 client-server workload as being a chaotic swirl of data requests that execute without regard to rules is NOT grounded in facts -- at least, not in today's facts. And there you have a root cause of a lot of misunderstanding: knowledge of DDF that was current 20 years ago but which hasn't kept pace with enhancements that have been delivered with each succeeding release of DB2. In this blog post and in the part 2 post that will follow, I will provide information on several mechanisms that can be used to establish various controls on a DB2 for z/OS client-server application workload.

Let me start by saying that I don't consider people who are concerned about managing a DB2 DDF workload to be wound too tightly, as we say. No, they're being good mainframers. They support a platform that is known for rock-solid reliability, and a pillar of that reliability is control. In a DB2 sense, that means control over data access privileges, over SQL statement access paths, over workload classification and and management, and over DB2 connection resources provided to particular applications. It's true that in the early years of DDF (back in the 1990s), some controls that you'd like to have weren't there. Now they are. In the paragraphs below I'll describe a few of the techniques that can be utilized to bring control to a DB2 for z/OS client-server workload. More information will be provided in another week or so, in the companion to this part 1 entry.

Native SQL procedures, for data access control and SQL statement access path stability

Now, in truth, the SQL statement access path and data access control benefits of stored procedures are available whether external or native SQL procedures are utilized; however, in a client-server application context I'm particularly keen on native SQL procedures (about which I first started blogging while working as an independent DB2 consultant), for a couple of reasons:

• All kinds of people can write them. This includes people who have worked predominantly with DB2 for LUW, or even with relational database management systems other than DB2. If you can write SQL, you can code a native SQL procedure (and it's even easier when you use Data Studio).
• They give you lots of zIIP engine offload when invoked through DDF. Unlike an external DB2 stored procedure, which runs under a TCB in a WLM-managed stored procedure address space, a native SQL procedure runs under the task of the calling program. When the CALL comes from a DRDA requester, that task is a preemptible SRB in the DB2 DDF address space, and so the processing associated with the native SQL procedure's execution can be largely offloaded (to the tune of about 60%) to an available zIIP engine. Native SQL procedures are pretty CPU-efficient anyway (and more so in a DB2 10 environment). Toss in the high degree of zIIP eligibility in a client-server setting, and native SQL procedures look even more attractive from a cost-of-computing standpoint.

The data access control and access path stability benefits of native SQL procedures owe to the fact that the SQL statements issued by such a procedure are static (or typically so -- one can, of course, code PREPARE statements in native SQL procedures). Data security is enhanced when static SQL is used because the only authorization required for successful execution of SQL statements in that case is the EXECUTE privilege on the package into which the statements were bound (plus, in the case of a stored procedure, the EXECUTE privilege on the stored procedure). By contrast, successful execution of dynamic SQL DML statements requires that the authorization ID of the process issuing the statements have the requisite access privileges (SELECT and/or INSERT and/or UPDATE and/or DELETE) on the tables targeted by the statements. Stored procedures further enhance data security by abstracting from client-side developers the details of the database schema (table names, column names, etc.).

The stable access paths that characterize static SQL statements help to ensure consistent performance when the statements are executed. Static SQL also minimizes CPU consumption: even with a high "hit" percentage in the DB2 dynamic statement cache, dynamic SQL might approach -- but will not equal -- the CPU efficiency of static SQL.

So, static SQL is good, but do you need to utilize stored procedures to get the benefits of static SQL for DB2 client-server applications? Not necessarily. If your client-side code is written in Java, those programs can issue static SQL statements by way of SQLJ; however, while SQLJ is a standard at some sites, it is often the case that Java programmers will have a strong preference for using JDBC, and JDBC means dynamic SQL at the DB2 data server. That's what makes stored procedures a great fit in a DB2 client-server application environment: they provide a means whereby static SQL can be dynamically invoked. If your client-side developers prefer a database interface such as JDBC or ODBC, fine -- let them use JDBC or ODBC to call DB2 stored procedures and to process output parameters and/or result sets returned by those stored procedures.

Roles and trusted contexts for data access control

I won't repeat here everything that I wrote in a blog entry on roles and trusted contexts that I posted a couple of years ago. Let me instead focus on setting the stage as to why these features, introduced with DB2 9 for z/OS, are so valuable when it comes to controlling data access in a DB2 client-server context.

Sometimes, DB2 client-server applications are going to issue dynamic SQL DML statements, whether you like it or not. That will very often be case when the application in question is vendor-supplied (e.g., a purchased enterprise resource planning or human resources application). You'll be dealing with dynamic SQL, as well, if statements are generated by an end-user query or reporting tool. You could have dynamic SQL statements coming from an in-house-developed application, if the aforementioned stored procedure route (or the SQLJ route, for Java programs) is not taken. Whatever the reason for dynamic SQL DML statements, they create a potential data security exposure. As mentioned previously, successful execution of a dynamic SELECT, INSERT, UPDATE, or DELETE statement requires that the application's DB2 authorization ID have the corresponding privilege (i.e., SELECT, INSERT, UPDATE, DELETE) on the targeted table. Now, it's likely that an application issuing dynamic SQL DML statements will connect to DB2 using an authorization ID and password that are unique to the application, but anyone who knows that authorization ID and password would seem to be free to use the table access privileges granted to the ID to access data -- maybe very sensitive data -- outside of the application. That's kind of a scary thought.

Enter roles and trusted contexts. In essence, here's how these two DB2 features work together to tighten data access control for client-server applications that issue dynamic SQL DML statements: first, the table access privileges needed for an application to work are granted not to an authorization ID, but to a role (something created by way of the CREATE ROLE statement). Next, the conditions of the use of the role's privilege set are defined through the creation of a trusted context. In plain English, the CREATE TRUSTED CONTEXT statement says something like this: "OK, I will allow the privileges granted to role XYZ to be used by an application that connects to the DB2 subsystem using authorization ID ABC, but only when that connection is established from the application server with IP address 9.30.131.203." If someone who knows the application's authorization ID and password attempts to use these credentials to connect to the DB2 subsystem from his personal workstation, the connection might be successful but no security exposure will exist because no DB2 privileges were granted to the authorization ID -- they were granted to role XYZ. The unauthorized user of the application's ID will not be able to use the table privileges granted to role XYZ, because he has connected to the DB2 subsystem from an IP address other than the one specified in the CREATE TRUSTED CONTEXT statement. If the rogue user isn't permitted to log in to the app server at (in this example) IP address 9.30.131.203, he can't do anything, DB2-wise, with the application's authorization ID and password. If needs be, a trusted context can further tighten data access control by specifying that only user IDs SMITH, JONES, and MILLER can use the privileges granted to role XYZ, and again only when the "come from" IP address is 9.30.131.203. With roles and trusted contexts, people responsible for safeguarding DB2 data can rest easier, even when client-server applications are issuing dynamic SQL DML statements.

And more to come...

In my part 2 entry, I'll provide information about additional mechanisms -- namely, the z/OS workload manager, DB2 10 profiles, and the DB2 10 query repository -- that you can use to control a DB2 client-server workload.

DB2 for z/OS: What Might "Template" DDL for CREATE TABLESPACE and CREATE INDEX Look Like?

Recently, a DB2 for z/OS DBA sent me a question about a particular clause of the SQL DDL (data definition language) statements CREATE TABLESPACE and CREATE INDEX. In his note containing the question he also sent what you might call his "template" CREATE TABLESPACE and CREATE INDEX statements -- the statements that serve as a pattern that he uses when creating these objects in his DB2 environment. Having such template DDL can be handy, in that it gives you a starting point for coding the statement that will be used to create a particular object. You might vary a clause specification or two to suit a given need, but the general pattern will be followed -- and that to your organization's benefit, if you've chosen the specifications in your template DDL thoughtfully. In this blog entry I'll give you my thoughts on what you might want to have in your template CREATE TABLESPACE and CREATE INDEX statements.

[Note that my use of the word template as it pertains to DDL should not be confused with the TEMPLATE control statement that can be used to facilitate data set allocation when executing some IBM DB2 utilities. Note, too, that I'm writing about application table spaces and indexes, not work file or system (e.g., catalog and directory) objects.]

Let me tell you up front that I'm not going to comment here on every single clause of the CREATE TABLESPACE and CREATE INDEX statements -- I don't have time for that, and even if I did it wouldn't add a lot of value. I'm fine with the default values for many of these clauses (e.g., LOGGED and TRACKMOD YES for CREATE TABLESPACE, and CLOSE YES and GBPCACHE CHANGED for both CREATE TABLESPACE and CREATE INDEX). If you are also OK with the default values for these clauses, you can leave them out of your template DDL (in order to accept the default values), or you can specify some or all of them if you want to see in the DDL a reminder of just what the default values for the clauses are. What I'll provide from here on out are my thoughts concerning some CREATE TABLESPACE and CREATE INDEX clauses about which there is some apparent misunderstanding within the mainframe DB2 community, and/or for which I have some opinions regarding specified values.

For CREATE TABLESPACE and CREATE INDEX

• USING STOGROUP -- You might think that this would go without saying -- that the use of DB2-managed data sets (indicated by USING STOGROUP) versus user-defined data sets (indicated by USING VCAT) is understood by everyone as being the way to go. I hope that's the case, but I'm including this recommendation anyway, just to make sure. Who wants to mess with z/OS Access Method Services statements to create the data sets that DB2 will use for table spaces and indexes? Let DB2 do it. Note, by the way, that this convention has moved beyond application-related objects: starting with DB2 9, work file table spaces can be DB2-managed, and with DB2 10 the catalog and directory objects become DB2-managed (I have some information about DB2-managed work file table spaces in an entry that I posted to this blog last spring, and information about DB2- and SMS-management of catalog and directory objects in a DB2 10 environment in another entry, posted in the fall of 2011). Note also that, starting with DB2 9 (in new-function mode), you can specify SMS data class, management class, and storage class names in a CREATE STOGROUP statement. That could reduce the need for automatic class selection (ACS) routines used for DB2 data sets at your site. 
• PRIQTY and SECQTY -- Leave them off of your CREATE TABLESPACE and CREATE INDEX statements, or specify them with a value of -1 (i.e., PRIQTY -1 and SECQTY -1). Either way, what this means is that you're going with a default primary quantity for the object's underlying data set(s), and you're letting DB2 handle allocation of secondary space as needed for the data set(s). The default primary quantity is specified via two ZPARM parameters (TSQTY for table spaces, and IXQTY for indexes -- both with a default value of one cylinder), and DB2 manages secondary space allocation by way of what's known as the "sliding scale" algorithm (this when the ZPARM parameter MGEXTSZ is set to YES). Letting DB2 manage space allocation for table space and index data sets makes all kinds of sense: it works, it helps to keep you out of trouble (in terms of avoiding data set extend failures), and it frees up DBA time for more high-value tasks. I wrote about DB2 management of data set space allocation in an entry that I posted to the blog that I maintained while working as an independent DB2 consultant.

For CREATE TABLESPACE

• MAXPARTITIONS -- What I'm getting at here is the idea of a default table space type for your environment. Your first decision is universal versus non-universal. You might want to give serious consideration to making universal partition-by-growth your default table space type (that would be indicated by having the MAXPARTITIONS and SEGSIZE clauses in the CREATE TABLESPACE statement). The primary reason for this recommendation: to an increasing extent, exploitation of new DB2 features will require the use of universal table spaces. With DB2 9 (the version with which universal table spaces were introduced), it was clone tables. With DB2 10, it's hash-organized tables, LOB-inlining, and the CURRENTLY COMMITTED mode of data access. I fully expect that this trend will continue with future versions of DB2. Using universal table spaces gives you maximum flexibility when it comes to leveraging DB2 technology. If you're going to go universal, the right choice for most table spaces is likely to be partition-by-growth (PBG) versus partition-by-range (PBR). Partition-by-range has its advantages, but I believe that PBR is best used for certain larger tables in a database (e.g., tables holding at least one million rows) that have columns or column sets that would make for good partitioning keys. Is non-universal ever the right choice for a table space? It could be if you want to assign several tables (perhaps a number of small-ish reference tables) to a single table space -- something you might do to keep a lid on the number of data sets in a DB2 system, and to simplify the backing up of said set of tables (via the COPY utility, which operates at the table space level). A traditional segmented table space can hold several tables, whereas one universal table space can hold one table. Bear in mind that the MEMBER CLUSTER table space definition option, required at some sites to support very high volumes of insert processing, is valid for universal table spaces in a DB2 10 new-function mode environment (it could not be used with universal table spaces in a DB2 9 system).
• SEGSIZE -- Most all of your tables should be segmented -- either traditional segmented table spaces or universal table spaces (the latter are also segmented). An exception to this rule: in a DB2 9 environment, you might choose a traditional range-partitioned table space over a PBR universal table space if the volume of inserts into the associated table will be very high, as the former table space type will likely deliver a performance advantage over the latter (with DB2 10, high-volume insert performance for a universal PBR table space is pretty much equivalent to what you get with a traditional range-partitioned table space). Aside from that case, segmented -- whether universal or non-universal -- is the way to go. The default SEGSIZE value is 4, and that might make sense for a non-universal segmented table space to which several really small tables are assigned. For single-table table spaces, the defualt SEGSIZE of 4 is probably not the best choice. I'd be more inclined to go with something like SEGSIZE 32, with an eye towards boosting prefetch efficiency.
• LOCKSIZE -- The default value here is ANY, which allows DB2 to use any type of locking granularity when accessing the table space. Yes, DB2 will typically use page-level locking for a table space that is defined with LOCKSIZE ANY, but I like to make the locking granularity choice myself. I prefer to go with LOCKSIZE PAGE as a default for table spaces. I'd change that to LOCKSIZE ROW for a table space only if page-level locking resulted in an unacceptable level of lock contention. Some people believe that you can't use row-level locking in a DB2 data sharing environment, or that doing so would be a big mistake. In fact, selective use of row-level locking in a DB2 data sharing system can be quite beneficial in relieving lock contention issues, as I pointed out in a blog entry that I wrote a few years ago. Another thing about a change to row-level locking from page-level locking for a table space: doing that could cause some data-changing programs that target the table space to acquire considerably more locks between commits, and THAT could conceivably cause said programs to hit the limits you've established (via ZPARMs) on the number of locks held by one process across all the tables that it is accessing, and/or the number of locks acquired on a single table space (the latter put into effect for a table space by way of the LOCKMAX SYSTEM clause of CREATE TABLESPACE). To avoid a surprise in this department, consider raising the values of the ZPARM parameters NUMLKUS and NUMLKTS if you change from page-level to row-level locking for one or more of your table spaces. Also keep in mind that if you change the locking granularity for a table space from page-level to row-level (or vice versa), and you want the new locking granularity to be utilized for static SQL statements that target the table (or tables) in the table space, you'll need to rebind the associated packages after altering the LOCKSIZE specification.
• COMPRESS -- DB2 for z/OS data compression is very CPU-efficient, thanks to a hardware assist, and it is therefore widely used. I'm a big fan of COMPRESS YES myself, but I don't think that it should be used for every table in a database. A compression dictionary takes up 64 KB of space in memory, so it's possible that compressing a really small table space could lead to that table space actually having a somewhat larger virtual and real storage "footprint" than it would have were it not compressed. I'd certainly lean towards compressing table spaces with 1000 or more pages.
• PCTFREE -- For a table space, the default value for PCTFREE is 5 (that's 5% of the space in a page). If a table's rows are not clustered by a continuously-ascending key, it would be good to have some "holes" in pages (following a REORG or a LOAD) into which newly-inserted rows could go -- the better to maintain good data organization between REORGs. In that case, PCTFREE 5 might be too small (5% of a 4K page is about 200 bytes, and that may or may not be enough to hold a table row -- and remember to consider compressed row length if you specified COMPRESS YES for the table space). A PCTFREE value of 10 might be better for maintaining data organization than PCTFREE 5. Of course, if a table's rows are clustered by a continuously-ascending key, a non-zero PCTFREE value would probably result in wasted space.

For CREATE INDEX

• FREEPAGE -- The default value of FREEPAGE is 0. Many indexes are defined on keys that do not have continuously-ascending values. For these indexes, new entries will go into the "middle" of the index. Some index leaf page split activity has to be expected, and when that happens it would be nice to be able to put the "split out" index entries into a page that is not too far from the page that was split. For that to happen you need to have some empty pages throughout the index (following an index REORG or a LOAD of the underlying table), because index entries that are relocated as a result of a leaf page split have to go into an empty page. If FREEPAGE 0 is specified for an index, the only empty pages will be at the end of the index, and that's where "split out" entries will go when page splits occur. The result could be fairly rapid disorganization for an index defined on a non-continuously-ascending key, if a lot of rows are inserted into the underlying table. For such an index, consider a FREEPAGE value of between 5 and 10 (FREEPAGE 0 is appropriate for an index on a continuously-ascending key).
• BUFFERPOOL -- Starting with DB2 9 in new-function mode, indexes could be assigned to buffer pools used for pages larger than 4K in size. Going with 8K, 16K, or 32K pages for an index is required if you want to utilize the index compression capability introduced with DB2 9, but even if you aren't interested in compressing an index, a larger page size could be a good choice. In particular, if the index in question is defined on a non-continuously-ascending key, and the volume of inserts into the underlying table is high, a large index page size (32K, for example) could substantially reduce the occurrence of index page splits, and that could deliver a nice performance dividend.

Do you have template DDL that you use as a starting point for CREATE TABLESPACE and CREATE INDEX statements in your DB2 environment? If not, perhaps you should. If you already have DDL that you use as a pattern for CREATE TABLESPACE and CREATE INDEX statements, how long ago was that pattern set up? Has the template DDL kept pace with DB2 changes that provide new options for table spaces and indexes? If not, a review and an update of the template DDL could be in order. It's all part of being ready to put the latest DB2 technology to the best use at your site.

DB2 for z/OS: Boosting UNLOAD and LOAD Performance with Pipes and FORMAT INTERNAL

My colleague Jorn Thyssen, who is based in Denmark, recently let me know of some nice work that he had done in setting up a high-performance process that gets data from one DB2 for z/OS table into another via the UNLOAD and LOAD utilities. What Jorn shared with me I'll share with you in this blog post.

Here's how this got started: Jorn, an IBM System z Information Management Technical Specialist, had delivered presentations on the DB2 Analytics Accelerator for z/OS to a number of organizations. In so doing, he would describe how the copying of DB2 tables into the Analytics Accelerator was performance-optimized in a couple of ways: 1) data is unloaded from the source DB2 tables in internal format, saving the CPU cycles that would otherwise be consumed in converting the data to external format; and 2) the unloaded DB2 data is transferred to the Analytics Accelerator by way of z/OS UNIX System Services (USS) pipes. [Pipes, also known as FIFO (first-in, first-out) files, are commonly used in UNIX environments. One process can read from a pipe as another process is writing data to the pipe, enabling a data load operation to run simultaneously with the unload operation that provides its input.] Following one of these presentations, someone asked Jorn a question: could data be moved from one DB2 for z/OS table to another in a similar fashion, with no data conversion and without the use of an intermediary data set that would force serialization of the load and unload tasks?

In considering this question, Jorn thought first about the cross-loader function of the DB2 LOAD utility. That could be used to get data from one table to another without the need for an "in-between" data set that would first be the output an UNLOAD and then the input to a LOAD. The cross-loader, however, can't be used with the FORMAT INTERNAL option, so while it would address the "no intermediate data set" stipulation, it would leave the "no data conversion" requirement unsatisfied.

It then occurred to Jorn that one could utilize, for a table-to-table data move in a DB2 for z/OS context, the same technique employed for copies of data into a DB2 Analytics Accelerator: combine the FORMAT INTERNAL option with a transference of data through a USS pipe. Jorn went looking for examples of this approach, and when he didn't find any that precisely fit the bill, he created his own UNLOAD and LOAD jobs to show how FORMAT INTERNAL and USS pipes can be used to avoid data conversion and the serializing effect of a traditional "in-between" data set. He ran these jobs on a DB2 subsystem that he uses for testing purposes, and they worked as expected. The jobs were submitted at the same time. The UNLOAD process waited for the LOAD process to open the pipe for reading, whereupon it commenced writing unloaded records to the "back" of the pipe, while the LOAD process read records from the "front" of the pipe. This is all done in memory -- there is no physical I/O involved.

Here is Jorn's UNLOAD job:

//UNLD1 EXEC DSNUPROC,SYSTEM=DB2A,
//             LIB='DB2.V9R1.SDSNLOAD',
//             UID=''
//SYSPUNCH  DD DSN=ABC1234.DB2A.CNTL.DSN8D91A.DSN8S91E.PTALL,
//             DISP=(,CATLG,DELETE),
//             DCB=(LRECL=80,BLKSIZE=0,RECFM=FB,DSORG=PS),
//             SPACE=(TRK,(5,5),RLSE),
//             UNIT=SYSDA
//SYSABC  DD PATH='/tmp/unload.pipe1',DSNTYPE=PIPE,
//        LRECL=107,BLKSIZE=27998,RECFM=VB,
//        PATHOPTS=(OCREAT),
//        PATHMODE=(SIWUSR,SIRUSR),
//        PATHDISP=(DELETE,DELETE)
//SYSTSPRT  DD SYSOUT=*
//SYSPRINT  DD SYSOUT=*
//SYSIN     DD *
 TEMPLATE UD
      PATH('/tmp/unload.pipe1')
      PATHDISP(KEEP,KEEP)
      RECFM(VB)   LRECL(00000049)
      FILEDATA(BINARY)
                         
UNLOAD TABLESPACE DSN06697.TEST UNLDDN(UD)
  FORMAT INTERNAL
/*

And here is the control statement for the complementary LOAD job:

//DSNUPROC.SYSIN DD *
 TEMPLATE D3RYKD6Z
      PATH('/tmp/unload.pipe1')
      PATHDISP(KEEP,KEEP)    PATHOPTS(ORDONLY)
      RECFM(VB)   LRECL(00000049)
      FILEDATA(BINARY)
 LOAD DATA INDDN D3RYKD6Z LOG NO  REPLACE
   FORMAT INTERNAL
  INTO TABLE ABC1234.TESTKOPI

In addition to unloading data from one DB2 for z/OS table and loading the data into another table, Jorn successfully tested some variations on the technique:

• He unloaded data from a table to a USS pipe, and sent that pipe to another system via FTP.
• He sent a file to a USS pipe via FTP, and loaded data into a table from the pipe.

In exploring the use of USS pipes in your DB2 for z/OS environment, you might find the following sources of additional information to be useful:

• DB2 for z/OS APAR PK70269. The fix for this APAR introduced DB2 for z/OS TEMPLATE support for USS files (these are associated PTFs for DB2 for z/OS versions 8 and 9 -- the functionality is part of the DB2 10 base code). The text of this APAR is quite informative.
• The IBM "red book" titled, "DB2 9 for z/OS: Using the Utilities Suite." Section 3.2.8 of this document explains the use of TEMPLATE with USS pipes. Section 7.17 covers unloading and loading data using USS pipes.
• The DB2 for z/OS Utility Guide and Reference contains supporting information in the section on TEMPLATE. Refer to the DB2 9 or the DB2 10 manual, depending on the DB2 release you're running.  

We often think of DB2 for z/OS utilities as workhorses, and they are, but they are workhorses that are constantly being enhanced with new functionality, one example of which I've written about here (with, again, a tip of the hat to Jorn Thyssen). Consider how the combination of FORMAT INTERNAL and USS pipes could enhance the performance of UNLOAD and LOAD operations at your site.

DB2 for z/OS Data Sharing: the Lock List Portion of the Lock Structure

With DB2's data sharing capability having been introduced back in the mid-1990s with DB2 for z/OS Version 4, a great many people -- including lots of folks whose organizations do not run DB2 in data sharing mode -- are quite familiar with the technology (for people new to this subject area, I posted, a few years ago, a "What is DB2 Data Sharing?" entry to the blog that I maintained while working as an independent DB2 consultant). Still, there are some aspects of DB2 data sharing technology that are more widely understood than others. It's recently come to my attention that among mainframe DB2 people there is a bit of a knowledge gap with regard to a particular component of a DB2 data sharing configuration. The component to which I refer here is the lock list portion of the DB2 lock structure, and with this blog entry my aim is to fill that knowledge gap to some degree.

The lock structure is of course one of three coupling facility structure types that DB2 uses to make data sharing work (the other two structure types are the Shared Communications Area and the group buffer pools). Within the lock structure you have the lock table and the lock list. The lock table is, conceptually, at work all the time in a DB2 data sharing environment. It's essentially a mechanism through which global lock contention is detected: when a process running on a member of a DB2 data sharing group (I'll call this member DB2A) wants a lock on something (e.g., a data page or row) that might be locked by a process running on another member of the group, the item on which the lock is being requested is mapped by way of a hashing algorithm to an entry in the lock table. That lock entry is checked for the presence of an incompatible lock held by a process running on another member of the group, and if that turns out to be the case (e.g., a process running on member DB2B has an X-lock on a page on which the process running on DB2A wants an S-lock) then the contention indication is sent back from the coupling facility holding the lock structure to the z/OS LPAR on which member DB2A is running. [Because there will almost certainly be many more lockable "things" in the database than entries in the lock table, some different lockable things will map to the same lock table entry; thus, contention can be falsely detected. Typically, "false contention" is the initial result for a very small percentage of global lock requests, and when this occurs the contention situation is determined to be false in a quick and efficient manner.]

Because the lock table is vitally important for the moment-by-moment operation of a DB2 data sharing group, it gets a lot of attention. By comparison, the lock list portion of the lock structure is usually not front-of-mind when people thing about DB2 data sharing. It's important, sure -- but in an insurance policy kind of way: you're glad it's there, but you'd be pleased if it were never needed. Here's what I mean by this analogy: the purpose of the lock list is to store information about X-type global locks held by members of the data sharing group on behalf of processes executing on the various members ("X-type" refers to locks associated with data-changing actions, and "global" refers to locks of which members other than the lock-acquiring member might need to be aware). In the event of the abnormal termination of a member DB2 subsystem, information about the X-type global locks held by that member at the time of failure will be, in effect, downloaded from the lock list to the surviving members of the group. These locks will be treated by the surviving members as "retained" locks, and they will continue to be retained (and the associated locked items, such as data pages and/or rows, will remain unavailable) until the failed DB2 subsystem releases the locks in the course of restart processing (restart of a failed DB2 member will usually be initiated automatically by the system, and will often complete in less than 2 minutes). Knowing about these retained locks is important for data integrity-preservation purposes: they keep surviving subsystems from accessing data pages or rows that were in the process of being changed through another member when that member failed. The retained locks "seal off" affected pages and/or rows (depending on the granularity of locking used for a table space) until the failed member can, as part of restart processing, roll back the units of work that were in-flight in the subsystem at the time of failure and write to disk those committed data changes that had not yet been externalized at the time of the failure event.

So, what does the lock list need in order to do its job? It needs enough space to hold information about all the X-type global locks held at a given time by the members of the DB2 data sharing group. Often people supporting DB2 data sharing systems don't think much about this, and usually they don't need to: these folks take the recommended approach of making the size of the DB2 lock structure a power of two (e.g., 64 MB, specified by way of the INITSIZE parameter for the structure in the system's coupling facility resource management policy, also known as the CFRM policy). When that's done, half the space in the lock structure will be used for the lock table, and half will be used for the lock list (the lock table size MUST be a power of two). In most cases, this approach provides plenty of lock list space (and that's good -- don't get stingy here).

Suppose your particular situation falls outside of the "most cases" norm? I recently encountered an interesting set of circumstances at a DB2 for z/OS site that made the "power of two" standard for lock structure size inappropriate. At this site, in a test environment, the lock list in a 327 MB lock structure (that's really big) got full. Let me put this in perspective for you: 327 MB is not a power of two. So, how big was the lock list in this lock structure? As mentioned previously, the lock table portion of a lock structure MUST be sized at a power of two. When the lock structure INITSIZE is not a power of two, upon initial structure build (or subsequent rebuild) the lock table portion of the structure will be sized at the power of two that will result in as close to a "50-50" split of space as possible between the lock table and the lock list. For a 327 MB lock structure that power of two for the lock table will be 128 MB (versus 64 MB or 256 MB). Subtract the 128 MB for the lock table from the 327 MB for the overall lock structure, and what you have left is 199 MB for the lock list. Will a lock list of that size have enough room to store a lot of global X-lock information? Yes: at a little over 300 bytes per lock list entry, a 199 MB lock list can accommodate over 600,000 list entries. That kind of capacity would typically be more than enough in a DB2 data sharing system, but in the situation to which I'm referring it fell short of the need. Why? Because at this site the migration of an application from a non-DB2 database to DB2 is requiring, at least for a time, some very large units of work (with respect to the number of update locks acquired by a program between commits). The ZPARM parameters NUMLKTS and NUMLKUS had been set to pretty high values to accommodate this requirement, but as mentioned the lock list in the test system did fill up and programs needing additional global X-locks abended as a result.

At this site, the lock list needs to be made larger (in fact, considerably larger), and that's going to happen -- but not in the way you might suppose. You might think that in this situation you'd just make the lock structure big enough (referring to INITSIZE) so that the lock list will be as big as needed, keeping in mind that, as noted, the size of the lock table part of the structure will be the power of two that gets you as close as possible to a 50-50 space split between the lock table and the lock list. In fact, that is NOT what this organization needs to do, for this simple reason: the lock table portion of the current lock structure, at 128 MB, is already plenty big and doesn't need to get any bigger (the adequacy of the lock table's size is indicated by a very low rate of false contention, which I briefly described above). The solution here is to keep the lock structure's INITSIZE as-is, and to substantially boost the value of the SIZE parameter for the structure in the CFRM policy. The SIZE value specifies the size up to which a coupling facility structure can be dynamically increased (by way of a SETXCF command) after it has been initially built (or rebuilt). Now, here's the really important point in the context of the referenced organization's need for substantially more lock list space: when a lock structure is made dynamically larger by way of a SETXCF command (and again, this can only be done if the value of SIZE exceeds the value of INITSIZE for the structure in the CFRM policy), ALL of the added space is used for the lock list -- the size of the lock table does not change (a larger INITSIZE and a structure rebuild would be needed to increase the size of the lock table). So, if INITSIZE for a lock structure is, say, 256 MB, and SIZE for the structure is 512 MB, and if the structure is initially built and then 100 MB is dynamically added to the structure's size through execution of a SETXCF command, the effect will be a 100 MB increase in the size of the lock list (which should give you room for an extra 320,000 or so additional lock list entries).

Another thing about dynamically increasing the size of the lock list: suppose, as in the example at the end of the preceding paragraph, you had a lock structure with an INITSIZE of 256 MB and a SIZE of 512 MB, and after initial structure build you dynamically increased the size of the structure by 100 MB. That would leave you with another 156 MB of available dynamic-size-increase capability. How would you know when to use that? IRLM (DB2's lock manager component) will tell you. If the lock list gets pretty full (50% full), IRLM will issue message DXR170I. That message will be issued again if the lock list becomes 60% full, and again if it gets to 70% full. If the lock list gets really full (80%), IRLM will issue message DXR142E. That message will be issued again if the lock list gets to 90% full, and again if it gets to 100% full, but you'd want to act on the 80% full message because at 90% full you're likely to start getting application program abends.

In summary: the lock list portion of the lock structure serves its purpose when a DB2 subsystem in a data sharing group fails, and that tends to be a pretty rare event. Still, you need to make sure that you have enough space in your lock list to hold all the global X-locks that might accumulate in your DB2 data sharing system, because running short on this space could result in application program abend events. In my experience, the lock list space that people have is usually plenty big for their needs, but there are situations in which the accumulation of a really large number of X-type global locks across a data sharing group can be expected. If that's the case at your site, be ready with a big-enough lock list that you can dynamically enlarge if needed. In other words, make sure that this insurance policy is in order and reflects the characteristics of your DB2 application environment.

DB2 for z/OS: An Interesting "Flip Side" to Native SQL Procedures

I heard the other day from a DB2 for z/OS DBA who communicated to me an interesting story. His organization had recently gotten into development and deployment of native SQL procedures on the mainframe DB2 platform. The native SQL procedures were working as advertised, but the move to utilize this DB2 technology had engendered grumbling on the part of some of the organizations' application programmers. In this entry I'll explain the basis of the developers' complaints, and I'll describe an approach that other organizations have taken to proactively address similar situations.

It's no secret that I've been bullish on DB2 for z/OS native SQL procedures since the get-go. I've done a lot of presenting and writing on the topic, starting with an entry that I posted back in 2008 to the blog I maintained while working as an independent consultant. Native SQL procedures were introduced with DB2 9 for z/OS in new-function mode (or with DB2 10 in new-function mode, for companies that migrated directly from DB2 Version 8 to DB2 10), and they deliver multiple benefits: significant processing offload to less-expensive zIIP engines on System z servers (when called by network-connected DRDA requesters), execution efficiency (they run in the DB2 DBM1 address space, versus an external-to-DB2 stored procedure address space), and simplified stored procedure management (there are no program object modules or load modules to manage -- a native SQL procedure's "executable" is its DB2 package). A lot to like.

So, what caused the aforementioned discontent among some of the application development colleagues of the DBA who shared his experience with me? In a nutshell, it was this: developing native SQL procedures ups the value of a programmer's ability to write sophisticated SQL statements, and a number of the more senior mainframe application developers at the organization to which I've referred felt that they were not prepared for this circumstance (the DBA explained that this underlying tension came to the fore when it was found that a SQL statement in a native SQL procedure performed more efficiently when it included a common table expression versus relying on a declared global temporary table).

I absolutely get this, and it very much does not reflect poorly on the skills and smarts of the programmers in question. Several of these individuals had been engaged for a long time in writing COBOL programs that included very simple SQL statements. I don't just mean simple in terms of an absence of things like common table expressions, CASE expressions, and CAST specifications. I mean simple as in not involving multi-table access via JOINs and/or UNIONs. Why is it that these folks generally wrote simple statements that didn't come close to leveraging the power of SQL? In large part -- and I've certainly seen this before -- it was because many of the programs running on the mainframe system had originally been written to access data in a non-relational DBMS and had been converted to access DB2 data. The programming interface to the non-relational DBMS was record-oriented, as opposed to being set-oriented. A program would access a record in a file, and if that record needed to be matched with one in another file then another single-record request would target that second file, using selection criteria obtained from the first retrieved record. When the database was migrated to DB2, the structure and logic of the programs that accessed the non-relational database were preserved, with very simple, single-row and single-table SQL statements taking the place of statements used to access data prior to the DB2 migration. This is not an uncommon approach for an organization to take when migrating data from a non-relational database to DB2: for the sake of expediency, existing database-accessing programs are converted to access DB2 with as little change as possible, and in terms of more fully exploiting SQL -- and DB2 for z/OS technology in general -- the focus is put on development of new applications. Consequently, you can end up with a group of programmers -- otherwise highly skilled -- who don't have much SQL knowledge beyond coding very simple statements because they haven't been asked to do more than that. Tell these folks to start developing native SQL procedures that are written entirely in SQL, and you might find that some of them feel under-prepared for that task. It's a recipe for frustration.

There are mainframe DB2-using companies that have anticipated this challenge and responded by creating a new role focused on facilitating the development and deployment of DB2 routines such as stored procedures (which could be native SQL procedures) and user-defined functions (which, starting with DB2 10 for z/OS in new-function mode, can be written using SQL Procedure Language). At one such company the name "procedural DBA" was given to the new role (at that company, some of the new "procedural DBAs" worked previously as traditional DB2 DBAs, while others were former application programmers who were keenly interested in building their SQL coding skills). The idea is to have a center of DB2 SQL excellence through which SQL coding knowledge (including development of native SQL procedures) can be diffused to the wider group of people in the organization who write programs that access DB2 data. I wrote about this new DB2-related role in an article that appeared in 2011 in IBM Data Management Magazine, and I blogged about the value to an organization of SQL coding skills in an entry posted to this blog, also in 2011.

Besides a role aimed at helping people to develop DB2 routines using SQL Procedure Language, make sure that programmers have access to helpful documentation. SQL Procedure Language statements (including statements that control logic flow in a routine) are documented in the DB2 for z/OS SQL Reference for DB2 9 and DB2 10. Information about creating native SQL procedures can be found in the DB2 for z/OS Application Programming and SQL Guide for DB2 9 and DB2 10. Another very useful source of information is the IBM "red book" titled, "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond." And, make sure that people are aware of forums through which DB2 people help other DB2 people with all kinds of questions, including those pertaining to native SQL procedure development. A personal favorite of mine is the DB2-L forum sponsored by the International DB2 Users Group (just go to www.idug.org, request -- for FREE -- a login if you don't already have a member ID, and click on the "DB2-L & Forums" link near the top of the IDUG home page).

SQL Procedure Language (SQL PL, for short) is important and getting more so in a DB2 for z/OS context (as previously mentioned, SQL PL can be used to create user-defined functions in a DB2 10 new-function mode environment, and SQL PL got a nice performance boost with DB2 10). People in your organization who have not heretofore written routines entirely in SQL can do so, effectively and efficiently, but that ability doesn't magically arise. Let people know that SQL coding help is available -- through SQL-knowledgeable individuals in your organization, in the DB2 documentation, and via DB2 forums -- and get ready to be impressed by what they can do. People like to learn new things. Give them that opportunity.

DB2 for z/OS: Clearing Up a Native SQL Procedure Misconception

A few days ago I received a question that I believe reflects a rather widely held misconception about DB2 for z/OS native SQL procedures. In the interest of clearing the air, I'll provide in this entry the information that I communicated to my question-asking friend.

Native SQL procedures were introduced with DB2 9 for z/OS in new-function mode (or with DB2 10 in new-function mode, for those organizations that migrated or are migrating to DB2 10 from DB2 V8). Native SQL procedures changed the DB2 for z/OS stored procedure game in a big way, as I pointed out in an entry posted several years ago to the blog I maintained while working as an independent DB2 consultant. One of the important differences between a native SQL procedure and an external stored procedure (such as a COBOL stored procedure) is what I'd call the execution locale. Whereas an external stored procedure executes at least partially in a WLM-managed stored procedure address space (and I'll explain that "partially" in a moment), a native SQL procedure executes entirely in the DB2 database services address space (also known as DBM1). That difference prompted the question to which I referred in my opening: to what address space is the CPU time associated with execution of a native SQL procedure charged?

The person who asked this question was thinking that because a native SQL procedure executes in the DB2 DBM1 address space, the CPU cost of executing a native SQL procedure would show up in the CPU consumption of DBM1. While I can see how one could arrive at that conclusion, it is not correct. The CPU cost of executing a native SQL procedure is in fact charged to the address space through which the stored procedure call came to DBM1; so, if a native SQL procedure is called by a network-attached DRDA requester, the CPU cost of executing the native SQL procedure will be charged to the DB2 DDF address space. If the caller is a CICS transaction program, the CPU cost of executing a called native SQL procedure will be charged to the CICS region in which the transaction executes.

If you think about it, this makes sense. There are two key factors to keep in mind here. First, though a native SQL procedure executes in the DB2 database services address space, it executes under the task of the application process that issued the stored procedure call (e.g., a DDF enclave SRB or a CICS subtask TCB or a batch address space's TCB). This is true of any SQL statement: the statement executes in DBM1, but under the task of the statement-issuing process, and associated CPU consumption is charged back to the address space through which the statement came to DBM1. [There is a slight exception to this rule: if a SQL statement is issued by an external stored procedure, the CPU cost of executing the statement will be charged to the address space through which the stored procedure call came to DBM1, as opposed to being charged to the WLM-managed address space in which the SQL-issuing stored procedure runs.]

The second key factor is this: a native SQL procedure is comprised wholly of SQL statements. Some of these statements will likely be data manipulation statements (e.g., SELECT, INSERT, DELETE, UPDATE), and some will likely be "logic" statements (e.g., LOOP, IF, WHILE), but it's all SQL. Yes, a native SQL procedure executes in the DBM1 address space, but SQL statements ALWAYS execute in the DBM1 address space. That's why I stated earlier that an external DB2 stored procedure generally executes "partially" in a WLM-managed stored procedure address space: if that external DB2 stored procedure issues SQL statements (and a stored procedure program doesn't have to issue SQL statements), those SQL statements will execute -- as is true of all SQL statements -- in the DB2 DBM1 address space; so, it could be said that the external stored procedure will execute partially in its assigned WLM-managed stored procedure address space (for execution of program code other than SQL statements) and partially in the DB2 DBM1 address space (for execution of SQL statements issued by the stored procedure program). A native SQL procedure executes entirely in DBM1 because it is comprised entirely of SQL statements. And where does the cost of SQL statement execution show up? In the address space through which the SQL statements come to DBM1 (with the aforementioned slight exception related to external stored procedures). And that's why the CPU cost of native SQL procedure execution gets charged to the address space through which the associated CALL statement comes to DB2.

I hope that this information will be useful to you. Best wishes to all for a successful 2013.

DB2 for z/OS: Clone Tables and "Active Versioning"

A DB2 for z/OS DBA contacted me a few days ago and posed a seemingly simple question, the answer to which ended up being a little more involved than might be expected. The DBA's query: how can one alter a table that has a clone?

The specific need in this case was to add a column to a table for which a clone had been created. As is true of other ALTER TABLE operations, addition of a table column cannot be accomplished while the clone relationship exists. The in-a-nutshell solution is to drop the clone table (via ALTER TABLE DROP CLONE), perform the desired alteration of the base table (such as ALTER TABLE ADD COLUMN), and then re-create the clone table (ALTER TABLE ADD CLONE). The thing is, the aforementioned DBA was doing this, and it wasn't working. In this blog entry, I'll explain why that was so, and how we determined the procedural steps required for success.

First, a brief level-set regarding DB2 clone tables: this refers to a feature, introduced with DB2 9 for z/OS in new-function mode, whereby one can create a "clone" of a DB2 table. If you have a base table named TBX, an associated clone could be created with a statement that would look like this:

ALTER TABLE TBX
ADD CLONE TBX_CLONE;

[You don't have to have the word "clone" in the name of the clone table, but having such an identifier is helpful when it comes to accessing a clone table -- it helps to ensure that you're accessing a clone, rather than the base table, when clone access is what you want.]

The clone table created by the statement above would look, in a structural sense, just like the base table: same columns, in the same order, with the same names and data types; same indexes; same check constraints (if any); etc. The one difference between the clone and the base table (other than the table name) is the data in the two tables, and this difference is the essence of a clone table's raison d'etre. Initially, a clone table is empty (and it resides in another "instance" of the base table's table space, distinguished by the fifth-level qualifier in the name of the underlying data set, which at first will be I0002 for the clone, versus I0001 for the base). After being created, the clone table can be populated in the usual way: through SQL inserts or the LOAD utility. Adding data to, and changing data in, the clone table has no effect on the users and programs accessing data in the base table. Once the clone data contains data as desired, that data can be effectively "switched" with the data in the base table -- quickly and programmatically -- by way of the SQL statement EXCHANGE DATA. Users and applications continue to access table TBX (to use my example table-name), but now the data they're accessing is the data that -- before the "switch" -- was the data in TBX_CLONE. In effect, through the EXCHANGE DATA statement, the table that had been the clone in the relationship becomes the new base table and takes on the base table's name, while the table that had been the base table becomes the new clone and takes on the name of the clone table. To learn a little more about what happens when EXCHANGE DATA is executed, you can check out an entry that I posted a few years ago to the blog that I maintained while working as an independent DB2 consultant.

OK, back to the question asked by the DBA. He knew that an ALTER TABLE table statement (other than an ALTER TABLE DROP CLONE) could not be successfully executed for a target table with a clone; so, he dropped the clone and successfully executed an ALTER TABLE ADD COLUMN statement. After doing that, however, the DBA was not able to re-create a clone for the table -- the ALTER TABLE ADD CLONE statement failed with a -148 error code, reason code 9, indicating that the ALTER failed because "active versioning" was in effect for the table.

DB2 uses table space versioning to ensure data integrity and preserve data availability through database schema changes effected by way of ALTER TABLE statements. A new version for a table space is created by DB2 when (for example) a new column is added to a table in the table space via ALTER TABLE ADD COLUMN, and the table space is placed in an advisory REORG-pending state (AREO*). This state will be removed, and the associated schema change will be applied (i.e., physically implemented) when the table space in question is reorganized. You might think that versioning would no longer be "active" for the table space following a reorganization, but post-REORG, the ALTER TABLE ADD CLONE statement to which I've referred still didn't work for the DBA who contacted me for help: again, the error code received was -148, condition code 9 (failure due to active versioning).

Faced with this situation, the DBA ran the REPAIR utility for the affected table space, with the VERSIONS option. Well, that reset the version information for the table space in the SYSIBM.SYSTABLESPACE catalog table (the values of the OLDEST_VERSION and CURRENT_VERSION columns for the table space's row in SYSTABLESPACE were both changed to 0, indicating that versioning had NEVER been active for the table space), and after that the ALTER TABLE ADD CLONE statement could be successfully executed; however, there was a problem: the column added to the table was of the type INTEGER WITH DEFAULT NULL, but the null indicator was not set for the column (a SELECT for the table with an IS NULL predicate referencing the new column returned no results). On top of that, the value sometimes found in the new column (which was supposed to have NULL as its default value) was -2147483648.

That's pretty weird, but weird things can happen when you use REPAIR to "turn off" indicators (such as active versioning) that DB2 uses for data integrity purposes. To be sure, REPAIR has its place as a tool for DBAs, and indeed it is sometimes the ONE way to address an issue impacting data availability. Still, it's a utility with which you can shoot yourself in the foot, and when I hear of a situation in which REPAIR is used to "fix" a DB2 database problem, I am always motivated to find a means by which the problematic object indicator can be "turned off" or reset through actual removal of the circumstances prompting DB2 to set said indicator, versus sledgehammering the indicator with REPAIR. The presence of "active versioning," as indicated by non-equal values for OLDEST_VERSION and CURRENT_VERSION for a table space's row in SYSIBM.SYSTABLESPACE, is addressed by two actions: reorganizing the table space that is in AREO* status and deleting from SYSCOPY any records associated with image copies of the table space that were taken when a version other than the table's current version was in effect. My DBA friend took these two steps for the table space holding the altered table (image copy records associated with older versions of the table space were removed from SYSCOPY via execution of the MODIFY RECOVERY utility), and everything then worked as he wanted it to: a clone could be created for the the altered table, and there were none of the unexpected data retrieval results for the newly added column that he'd seen after using REPAIR to force a resetting of the table space's version number.

So, if you have a table with a clone, and you want to alter that table in some way (e.g., by adding a column to the table), do the following:

1. Drop the clone table with ALTER TABLE DROP CLONE.
2. Alter the table as needed (e.g., issue an ALTER TABLE ADD COLUMN statement).
3. REORG the table space to apply the schema change specified in the preceding ALTER TABLE statement, and take a full image copy in the process (an inline copy is taken anyway if you run an online REORG). The REORG will ensure that only one version is associated with the data in the table space, and the full image copy will allow you to delete older image copy records (those taken when prior versions were in effect for the table space).
4. Run MODIFY RECOVERY for the table space, with a specification that will remove from SYSCOPY records for image copies taken when a prior version was in effect for the table space. A specification of RETAIN LAST(1) should preserve the record for the image copy taken during the REORG (step 3 above) while deleting records for older image copies. Note that this is a special case: it's generally recommended that you retain SYSCOPY records for at least the two most recent full image copies of a table space. So, after running this MODIFY RECOVERY job with RETAIN LAST(1), for subsequent SYSCOPY "housekeeping" go back to a MODIFY RECOVERY specification that will retain records for two or more full image copies of the table space.
5. With versioning no longer "active" for the table space, you can issue an ALTER TABLE ADD CLONE statement to create a clone for the table you altered in step 2.

And leave REPAIR out of this picture -- you don't need to run it to be able to create a clone for an altered table.