DB2 for z/OS People: Wearing Your "Applications" Hat Versus Your "Systems" Hat

Not long ago, a colleague of mine sent to me an e-mail message that he'd received from a person who supports DB2 for z/OS at a US-based company. In this message the IT professional wrote of some DB2-accessing application programs that were running into lock contention issues with page-level locking (the default) in effect. He knew that going to row-level locking for the target tables (accomplished by altering the associated table spaces with LOCKSIZE(ROW)) would likely resolve the locking conflicts that were negatively impacting throughput and response time for the aforementioned applications; however, he was reluctant to pursue this physical database design change because it would be a DB2 response to what he regarded as an application problem.

This person, in a well-meaning way, was wearing his "systems" hat. Wrong choice of figurative headwear, I'd say. It might have been the right hat to wear 10 or 20 years ago, but times have changed. These days, it is more important than ever for a mainframe DB2 person to reach first for his or her "applications" hat. Hereinafter I'll endeavor to explain the reasoning behind this contention of mine.

First, I have a "micro" response regarding the particular matter of page- versus row-level locking, and I believe that this will serve as a perfect lead-in to my take on the larger, "macro" issue which has to do with striking the proper balance between system- and application-focused thinking.

Row- versus page-level locking -- think about it

IBM invented relational database technology, and DB2 for z/OS was, in essence, the first commercial-grade, enterprise-level relational DBMS on the market (it was called DB2 for MVS back then). Page-level was initially the finest locking granularity available in a mainframe DB2 environment, and for years that worked just fine for all kinds of DB2-using organizations. In cases where lock contention problems arose with page-level locking in effect, programmers were often ready and willing to make application code changes that eliminated or at least minimized the occurrence of timeouts and/or deadlocks. On occasion, when an application code change was not feasible, a DB2 DBA would reduce effective lock granularity by limiting the number of rows that could be stored in a table space's pages (this via the MAXROWS option of ALTER TABLESPACE). The marching orders given to most DB2 for z/OS DBAs were, "Focus on making the DB2 workload as CPU-efficient as possible." DBAs heeded these orders, and so routinely wore their "systems" hat.

Time went by, and while DB2 for MVS (and then OS/390 and then z/OS) continued to grow in terms of installations and of data volumes managed and transaction volumes processed, relational database management software for distributed systems servers made the scene and eventually became quite popular. For these DBMSs, row-level was (and still is) the default locking granularity utilized. Page-level locking continued to be (and still is) the default for DB2 for z/OS, but a new index structure introduced with DB2 Version 4 made row-level locking an option for the mainframe platform.

Relational database technology evolved, and so did the ways in which people used it. An important development was the rise of vendor-supplied -- versus in-house-written -- application software. Companies producing this software understandably sought to develop code that would be suitable for use with a wide range of database management systems. That meant designing with row-level locking in mind -- the default in most of the relational database world.

The rise of vendor-supplied applications, of course, didn't mean the end of in-house application development. Organizations continued to build their own apps to address unique demands and opportunities in their respective markets, but in this sphere there was a pronounced shift towards multi-tiered, client-server applications and away from more monolithic application architectures. People developing these applications favored the use of data access interfaces, such as JDBC and ODBC, that worked across many different relational DBMSs. Developing in such a DBMS-agnostic way, programmers quite naturally gravitated towards coding with the most widely-used database locking granularity -- row-level -- in mind.

Fast-forward, then, to the present day. You have row-level-locking-using vendor-supplied applications, and you have in-house developers writing code with an implicit assumption of row-level locking granularity, and you have people who want to pair these purchased and in-house-written applications with DB2 for z/OS (which a friend of mine likes to call the "super-server"), and you have DB2 DBAs and systems programmers looking sternly out from under their "systems" hats and telling these people, "Row-level locking doesn't deliver optimal CPU efficiency. Come back to me after you've changed that application to work well with page-level locking."

EXCUSE ME? There are people coming to you, bearing applications that will drive workload growth on the platform you support, and you're giving them the cold shoulder because the preferred (and maybe required, absent code changes) locking mode might cause DB2-related CPU consumption to be a little higher than it otherwise would be? I have a different response to recommend. How about, "Row-level locking? No problem. Of course we can do that. We've been able to do that for about 20 years now. Works like a champ. Let's talk about setting up a testing environment for the application, and let's start putting a production implementation plan together."

On a macro level: technology enables applications

DB2 for z/OS features all kinds of great technology, and that story gets better with each new release of the software, but guess what? That advanced technology is valuable to an organization to the extent that it facilitates and accelerates the development of applications that make the organization money (enabling new revenue streams, upping business volumes from existing clients, growing the customer base, etc.) or reduce costs (better inventory management, reduced customer churn, reduction of fraud-related losses, etc.), or both; thus, your mindset as a DB2 support person should be about rolling out the red carpet to application developers (and to those interested in storing the data related to vendor-supplied applications in a DB2 for z/OS database). You should aim to make DB2 a preferred DBMS for your organization's programmers, and you don't get there by making those folks do things differently versus other data-serving platforms with which they work.

When you wear your "applications" hat, you think "accommodate" more than "challenge" when working with programmers. Supporting row-level locking is just one example of such accommodation. Another example would be creating an index on a column expression in order to make an otherwise non-indexable predicate indexable (and stage 1). Maybe you utilize concentrate-statements-with-literals functionality (introduced with DB2 10) to have DB2 parameterize dynamic SQL statements that were coded with literal values, when application-side statement parameterization is not feasible. Maybe you create an "instead of" trigger to allow programmers to code data-changing SQL statements targeting what would otherwise be a read-only view. Maybe you create a user-defined function that replicates the capability of a function that was useful to a development team when they worked with some other DBMS. Maybe you define a check constraint for a table to help ensure that only certain data values can be inserted into a column -- this so that programmers won't have to code that data domain-restricting logic themselves. There are many, many more ways in which you can use DB2 functionality to make life easier for application developers -- I've just scratched the surface here. Put on your "applications" hat and get creative and be proactive -- go to programmers with ideas, instead of just waiting for them to bring problems and challenges to you.

Now, reaching first for your "applications" hat doesn't mean throwing your "systems" hat away. Enhancing the CPU efficiency of the DB2 for z/OS workload at your site is a very worthwhile pursuit. That said, you should think about actions that could reduce per-transaction CPU consumption without requiring application code changes. Tuning steps of that nature include page-fixing buffer pools, combining persistent threads with RELEASE(DEALLOCATE) packages, and, in some cases, hash-organizing data in a table. Shoot, just rebinding packages when you go to a new release of DB2 can deliver significant CPU savings for your organization.

DB2 is a tool. When people approach you about using that tool to enable a new in-house-developed application, or to support a vendor-supplied application, you should be delighted, not bothered. Such requests give you the opportunity to show what DB2 for z/OS can do as a data server. Consider DB2 features and functions while wearing your "applications" hat, and you'll be that much more effective in leveraging DB2 technology for the benefit of developers, and to provide an ideal data-serving system for vendor-supplied applications. DB2 for z/OS really is the "super server" -- tops in availability, scalability, and security. Don't stand between it and your organization's applications folks. Be a bridge, not a barrier.