Databases

Can You Control a DB2 for z/OS Client-Server Workload? Yes.

Imposing order on distributed data facility workloads

At sites all over the world, IBM® DB2® for z/OS® workloads are growing. A major driver of this growth has been the upsurge in client-server application activity on the System z® platform. In a DB2 for z/OS context, this pattern of computing usually involves network-attached application servers interacting with the database via the DB2 distributed data facility (DDF). You would think that the IT managers and technical professionals who have mainframe systems support responsibilities would welcome client-server applications paired with a DB2 for z/OS back end. Many do, but quite a few of these individuals view a DB2 DDF workload with some degree of trepidation. Often, that sense of unease has to do with one thing: control.

Consider where these people are coming from. Mainframers manage a platform known for rock-solid reliability, and a pillar of that reliability is control. In a DB2 sense, that means control over data access privileges, SQL statement access paths, workload classification and management, and DB2 connection resources provided to particular applications. Some System z people look at a client-server application—versus the more monolithic application architecture formerly prevalent on mainframes—and think, “I can’t control that.” It’s true that in the early years of DDF back in the 1990s, some controls that you wanted to have weren’t there. Now they are.

 

Let me count the ways…

In fact, there is an abundance of mechanisms by which you can exert control over a DB2 DDF workload. I’ll summarize some of the more important ones here; your local IBM DB2 specialist can tell you more.

  • Roles and trusted contexts for data access control. For a dynamic SQL application, which is common for client-server workloads, this technique enables me to say (for example), “Okay, I will allow read access to this table for an application that connects to DB2 using this particular ID, from this particular application server.” Optionally, you can take that access control down to individual user IDs.
  • Native SQL procedures for SQL access path and data security control. DB2 stored procedures—a great fit for client-server applications—have always been a good way to lock down SQL statement access paths and data access permissions through their use of static SQL. Native SQL procedures made development of stored procedures easier than ever and reduced computing costs by offloading a lot of processing to economical IBM System z Integrated Information Processor (IBM zIIP) engines when called through DDF.
  • Query repository to control access path and execution options at a statement level for dynamic and static SQL. Implemented via a new set of tables in the DB2 10 catalog in new-function mode, the query repository enables the binding of individual SQL statements. When a statement bound this way comes through, perhaps for a particular authorization ID, package, or collection, a predetermined data access path will be used. You can also use the query repository to specify execution options at the SQL statement level (for example, to make a given statement eligible for query parallelization).
  • z/OS Workload Manager for application priority control. Originally, SQL statements coming through DDF executed at the priority of the DDF address space. That is not the case today—and it hasn’t been for a long time. You can use a Workload Manager policy to classify and prioritize the various subcomponents of your overall DDF workload in multiple ways, giving greater importance to operational, transactional work and a lower priority (potentially with period aging) to longer-running queries.
  • Profiles for application-level control of DB2 connection resources. DB2 installation parameters (ZPARMs) specify limits on the number of DDF connections to DB2, the number of concurrently active DDF threads, and the amount of time an active DDF thread can remain idle before timing out. With the profile support introduced with DB2 10, you can specify and monitor these limits for individual client-server applications (for example, at an application server, authorization ID or role, or package level).

And these are just some of the DDF workload control mechanisms that are built into DB2. Even more extensive capabilities for dynamic control of a DB2 client-server workload, especially in a data sharing environment, are provided by the IBM InfoSphere® Optim™ Configuration Manager for DB2 for z/OS.

 

Don’t fear the client-server wave—ride it

Client-server workloads are coming to DB2 for z/OS for very good reasons: the System z platform is without peer in terms of reliability, scalability, availability, and security. As a mainframe DB2 person, roll out the red carpet for these applications, and know that workload control is in your hands.

What do you think? Please let me know in the comments.

Previous post

Database Design for Mere Mortals

Next post

Case Study: Camden Council

Robert Catterall

Robert Catterall is an IBM DB2 specialist. You can reach him at rfcatter@us.ibm.c[email protected]

  • http://twitter.com/Pfitschepfeil Karl Griesser

    One of the best improvement in the past years was that WLM finally takes MAXDBAT into account in his dispatching algorithm.

    This took IBM forever to implement and is still sometimes a mess which can be painful if some members in a Data Sharing group crash and all workload is dispatched to a member that already hit MAXDBAT.

    So in my opinion IBM should put more resources in getting feature that are already in the product to a stage where they may be used instead of extending the feature list with some nice to have stuff that will only be used by a minority of customers.

    • Robert Catterall

      Thanks for speaking up, Karl. MAXDBAT limit pressure can indeed occur if, as you’ve noted, a member of a DB2 data sharing group fails (particularly if the group only has two members and processes a lot of DDF work). I believe that DB2 10 for z/OS will help to address this situation by enabling organizations to support 5 to 10 times the number of concurrently active threads on a DB2 10 subsystem versus a DB2 9 or DB2 Version 8 environment (the big increase in concurrently active threads per subsystem can be achieved when packages are bound or rebound on the DB2 10 system).