Blog
DB2utor

Categories

August 19, 2014

An In-Depth Look at Buffer Pool Monitoring and Tuning

IBM recently released a Redpaper, "IBM DB2 11 for z/OS Buffer Pool Monitoring and Tuning."

While this document is intended for DB2 systems administrators, it could also be useful for those who specialize in z/OS system tuning. As someone who knows a bit about tuning buffer pools and the challenges this process entails, I'm excited to see IBM devote a Redpaper to this topic.

I've yet to read the whole thing, but the material I've been through is excellent. This isn't surprising given that the author, Jeff Berger, is one of IBM's top performance specialists.

Here's the chapter list:


            Chapter 1. Introduction
            Chapter 2. Prefetch and RID pool
            Chapter 3. Buffer pool writes
            Chapter 4. Data sharing
            Chapter 5. Workfiles
            Chapter 6. Assigning page sets to buffer pools
            Chapter 7. Improving CPU performance
            Chapter 8. Index and data organization

The first five chapters provide a great introduction. You'll learn what buffer pools are and how the tuning knobs are used to control their behavior. This information is very valuable for newcomers, or anyone in need of a buffer pool refresher course.

I believe that splitting up buffer pools is essential to effectively tune them. When I was consulting, I found that many customers solely rely upon the main buffer pool (BP0). My approach was to set up separate pools for random and sequential access -- a buffer pool for sort work files. However, in Chapter 6, Jeff makes a recommendation that I hadn't considered: assigning buffer pools based on sequential insert activity. As I said, he's the expert.

When I was tuning buffer pools, my focus was on reducing physical I/O, so I appreciated the information in Chapter 7, which covers the settings you should use to reduce CPU consumption. The final chapter examines when you should consider reorganizing index and data spaces.

I know from experience that setting up multiple buffer pools isn't simple, but the performance benefits it provides are worth the effort. This Redpaper offers valuable information to help you navigate this process.

Please post your experiences and/or questions on buffer pool tuning.

August 12, 2014

IBM Knowledge Center a DB2 for z/OS Resource

As I've often noted, I prefer storing DB2 for z/OS manuals in PDF format. Even though keeping them updated is a challenge, I believe that being able to access all this information from my laptop is well worth the effort.

Launched earlier this year, the IBM Knowledge Center is the online home for all DB2 for z/OS product information. This site provides links to additional information resources and alternative documentation formats, such as the PDF manuals that I like (which are available in hard copy from the IBM Publications Center). The current sets of manuals available through the Knowledge Center are for DB2 versions 9, 10 and 11. I like that the search feature now automatically limits the search to the DB2 version tab you select.

Figure for 8-12-14 blog

 

I also appreciate that readers can add comments to each document. (Free registration is required to use this feature.) This is a simple way to voice your opinions to IBM as well as learn how others feel about the documentation.

August 05, 2014

A Newbie Perspective on DB2

In her recent post on IDUG's DB2 Beginner's blog, Lauren Turner compares learning database basics with the challenge of picking up a foreign language.

I enjoyed this fresh perspective, and it's something to which we can all relate. Mastering DB2 is an ongoing process. New capabilities are constantly being added, so no matter how long you've worked on the database, you still have more to learn. I encourage all DB2 pros -- new users, experienced DBAs and everyone in between -- to take the time to follow and encourage Lauren as she shares her experience with the DB2 community.

Kudos also to IDUG for not only providing a platform for a new user like Lauren, but for all of the content they publish in the beginner's blog. Be sure to check it out.

July 29, 2014

Identifying Locked DB2 Resources

Last week I explained how to find the cause of a deadlock or timeout. This week, I'll show you how to identify the name and type of DB2 resource being locked. We'll start with the SQLCODE -911 message:

            THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO      DEADLOCK OR TIMEOUT.
            REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME   resource-name

The reason code will be either 00C90088 (deadlock) or 00C9008E (timeout). The resource-type can be found in the DB2 for z/OS CODES manual or at the IBM Information Center.

The common resource types related to objects being locked are:

            TYPE Code   Type of Resource     Name,Content,Format

            00000100      Database                   DB

            00000200      Table space               DB.SP

            00000201      Index space              DB.SP

            00000210      Partition                     DB.SP.PT

            00000D00     DBID/OBID                RD.DI.OI

            00000D01     DBID/OBID                DI.OI

 

What you will see in the error message are codes that have to be translated. For instance, here's a sample message for a locked database where the name is DSN8D11P:


DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK ORTIMEOUT. REASON 00C9008E, TYPE OF RESOURCE 00000100,AND RESOURCE NAME DSN8D11P

And here's a sample message for a locked table where the name is 403.43:

DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK ORTIMEOUT. REASON 00C9008E, TYPE OF RESOURCE 00000D01,AND RESOURCE NAME 00000403.00000043

As noted in the DB2 for z/OS Messages Guide and explained here, when the resource type is D01, you need to look for a table name. To do so, use this query:

             SELECT CREATOR, NAME
            FROM SYSIBM.SYSTABLES
            WHERE DBID = 403 AND OBID = 43;

 

If the resource-type is X'D00' or X'D01', use this SQL statement to determine the table name:

 

            SELECT CREATOR, NAME

               FROM SYSIBM.SYSTABLES
               WHERE DBID = dbid AND OBID = obid;

 

For more information related to this topic, see my previous posts about DB2 locking and application design for concurrency:

* Batch Tuning: Checkpoint Restart

* Batch Tuning:Continuous Availability

* Understanding DB2 Locks

 

July 22, 2014

Finding the Cause of a Deadlock or Timeout

Pinpointing the cause of an application failure is one of the more challenging aspects of a developer's job. Locking problems can be greatly reduced by designing applications that support checkpoint restart and provide the capability to tune commit frequency. Of course, applications failures still happen. Here's some quick advice for dealing with this issue.

Recently I was asked how to find the cause of a deadlock or timeout that led to an application failure with an SQL return code of -911 or -913. First, check the system log and the DB2 master address space log in which the application executed on -- DB2 writes messages to both. (Note: If you're not authorized to view messages in the system and DB2 master address space logs, you'll need access from an authorized user. Another option is to use a performance monitoring tool that provides access to DB2 messages.)

The message you're looking for is either a DSNT375I (for a deadlock) or a DSNT376I (for a timeout.) The key information provided is the plan names involved, along with thread identifying information.

            Deadlock message DSNT375I sample:

            PLAN=plan-name1 WITH CORRELATION-ID=correlation-id1 CONNECTION-ID=connection-id1       LUW-ID=luw-id1 THREAD-INFO=thread-information1
               IS DEADLOCKED WITH PLAN=plan-name2
                   WITH CORRELATION-ID=correlation-id2    CONNECTION-ID=connection-id2
                    LUW-ID=luw-id2 THREAD-INFO= thread-information2 ON MEMBER member-name

For details on this message, click here.

            Timeout message DSNT376I sample:
            PLAN=plan-name1 WITH CORRELATION-ID=correlation-id1
            CONNECTION-ID=connection-id1 LUW-ID=luw-id1 THREAD-INFO=thread-information1
                IS TIMED OUT. ONE HOLDER OF THE RESOURCE IS PLAN=plan-name2
                    WITH CORRELATION-ID=correlation-id2 CONNECTION-ID=connection-id2
                    LUW-ID=luw-id2 THREAD-INFO= thread-information2 ON MEMBER member-name

For details on this message, click here.

Once you pinpoint the cause of a deadlock or timeout, you may want to know the name of the object that's involved in the failure. Next week I'll tell you how to find an object's resource name and determine the cause of the contention.