Bind Option Conflicts

Last week I discussed dirty reads and how isolation level (UR) is used to read uncommitted data. In the course of my research for that post, I came across a related topic: bind parameter conflict resolution for isolation and release parameters. That is, if the PLAN and the package are bound with different isolation options, which one will DB2 use during execution?

For an in-depth look at this topic, read the DB2 for z/OS 11.0 Performance Management manual:

The general rule is when a conflict exists between the bind options, DB2 will use the one that holds the locks the longest. So if the options are RELEASE(COMMIT) and RELEASE(DEALLOCATE), DB2 will always select the latter.

With isolation levels, the lock hold time increases as you go from UR to CS to RS to RR.  So if one option is set to UR and another is set to RS, DB2 will use RS. The aforementioned manual includes a table that displays all of the different combinations.

As you can imagine, if you're expecting the isolation to be CS and it suddenly becomes RR, contention problems, slowdowns and even timeouts could result. I wrote this brief SQL statement to help locate these conflicts:

            SELECT SUBSTR(P.CREATOR,1,8) AS CREATOR
                        , SUBSTR(P.NAME,1,8) AS PLAN_NAME
                        , SUBSTR(PK.NAME,1,8) AS PKG_NAME
                        , P.ISOLATION AS P_ISO
                        , PK.ISOLATION AS PKG_ISO
                        , P.RELEASE AS P_REL
                        , PK.RELEASE AS PKG_REL
              FROM SYSIBM.SYSPLAN P
                        , SYSIBM.SYSPACKLIST PKL
                        , SYSIBM.SYSPACKAGE PK
              WHERE   P.NAME = PKL.PLANNAME
                     AND PKL.LOCATION = '*'
                     AND PK.LOCATION =' '
                     AND PKL.COLLID = PK.COLLID
                     AND ( (NOT P.ISOLATION = PK.ISOLATION)
                                                 OR
                                 (NOT P.RELEASE = PK.RELEASE));

If you have your own bind option conflict and choose to use this SQL, please let me know if it helps.

Implementing 'Dirty Reads'

I was recently asked about "dirty reads." This term refers to setting an application's SELECT statement to avoid taking locks while reading and processing uncommitted data.

Of course, the benefit of doing this is to reduce CPU costs and reap potentially significant savings. As the name implies, however, dirty reads comes with a risk. It can lead to failed transactions due to discrepancies in the data. So before implementing dirty reads, you must understand how your data is being used and if discrepancies can be tolerated.

In DB2, locking behavior is controlled through the isolation level used when the statement is executed. I like this statement from the manual, "DB2 for z/OS 11.0: Managing DB2 Performance":

"Various isolation levels offer less or more concurrency at the cost of more or less protection from other application processes."

 The manual also includes a list of isolation levels with descriptions. Again, read more here:

Warning: Dirty reads -- aka the uncommitted reads (UR) isolation level -- should never be used with transactions where the data must always be accurate or up-to-date (e.g., banking transactions or inventory data). To a DBA this may seem obvious, but I've worked at places where some developers, enticed by how fast programs can run at the UR level, decided on their own to apply it to business transactions. As powerful as UR can be, it's simply an unacceptable choice outside of development. CPU savings are not worth jeopardizing customer relationships or compromising your own critical business data.

Do you use dirty reads? When do you find it beneficial? Please share your experiences in comments.

Remaining IDUG Events in 2014

I shared my thoughts about the IDUG NA conference in May, but this is just one of IDUG's three major annual events. The European and Australian conferences are both coming up in a few weeks.

The IDUG DB2 Tech Conference in Sydney, Australia, is Sept. 10-12. Here are the conference sessions.

Meanwhile, European readers of this blog should note that one of the largest and most diverse IDUG conferences is being held in Prague, Czech Republic, from Nov. 9-14. Educational tracks are dedicated to the latest releases DB2 for z/OS 11.0 and DB2 for LUW 10.5. In addition, there are general tracks on DB2 for z/OS and DB2 for LUW, and more specialized tracks on big data and analytics, along with application development.

If you'll be in attendance, be sure to catch IBMer John Campbell's in-depth two-part session, "DB2 11 for z/OS: Migration Planning and Early Experiences" (AB1).

Some of my coworkers at CA Technologies will also be presenting:

Steen Rasmussen
* "Application Development Goodies: What’s HOT in DB2 10 and 11" (F12).

* "The DB2 11 Catalog: Something new, something old and something changed" (A14).

Steve Thomas
* "Archiving data: DB2 comes to the rescue (twice)" (B5).

Zeljen Stanic
* "To Do IT(IL) or Not ... " (E13).

Denis Tronin:
* "Lost Without a Trace" (B3).

I've often discussed the value of IDUG conferences in this blog. Attendees receive invaluable education and updates on DB2 from some of the world's foremost database experts. In addition, IDUG events include many other services and features, such as free certification exams, access to one-day seminars, interaction with IBM speakers from the lab and the opportunity to network and share ideas with other DB2 professionals.

If you've attended IDUG events, please share your experiences in comments.

IBM Redbook Mobile App

Over the years, I've written often about IBM Redbooks. I enjoy learning from the experts who author these publications. I also enjoy sharing this information with DB2utor readers.

Recently I discovered that IBM has a new Redbook management application for IOS and Android mobile devices. I’ve downloaded the app and I must say, it's pretty cool.

When you first open the application, you'll see six menu options. 

• Find IBM Redbooks
• News
• My Favorites
• Visit us on the web
• Connect with us
• Find IBM resources

“Find IBM Redbooks” and “My Favorites” are my favorite options. With Find IBM Redbooks, you can sort Redbooks by publication data, popularity, subject area and publication type. With the latest publications, you can tap on the title and read an abstract and/or the table of contents. You can also save the link, download it to your device or share in Facebook, Twitter or email.

I use My Favorites to archive and read Redbooks. With this option, you can organize saved Redbooks into user-defined folders. Plus there's a bookmarking capability so you can quickly access key information.

Learn more and download the Redbook app here.

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.

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.

 

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.

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.

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

 

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.

Using the New Compatibility Mode

In May when I posted a summary of new DB2 11 features, I mentioned that the latest release allows applications with SQL or XML to be run in compatibility mode. Since this is an important feature that will help customers much more quickly migrate to DB2 11, I'll provide some detail.

In DB2 11, you can set the SQL function and features available to an application package based on an application compatibility value. The values are either V10R1 or V11R1. Leaving the application value at V10R1 gives you time to evaluate any changes in behavior when switching over to V11R1. Should you experience a negative behavior, you can just switch back to V10R1. The application compatibility value can be set through a bind option, a subsystem parameter or the special register.

A new subsystem parameter, APPLCOMPAT, is used to set the application programming value. During a migration this will default to V10R1; during a new install it will default to V11R1. These default behaviors can be changed using install panel DSNTIP41. It's recommended that the default value be the same for all members of a data sharing group. 

With a value of V10R1 during a migration, all existing applications will continue to execute as if they're on a DB2 10 version subsystem -- even when running in DB2 11 new function mode.

Over time, as you ensure everything is stable in DB2 11 (with compatibility mode set to V10R1), you can start using the BIND/REBIND parameter, APPLCOMPAT, with a value of V11R1. This, obviously, enables the application to exploit the new features available in DB2 11 new function mode. Note: The BIND/REBIND command parameter APPLCOMPAT only sets the compatibility mode for static SQL statements.

For dynamic SQL, use the special register, CURRENT APPLICATION COMPATIBILITY, to specify the compatibility level. The initial value of CURRENT APPLICATION COMPATIBILITY is based on the APPLCOMPAT value used in the BIND PACKAGE:

            Example to find the current application compatibility value:
                SET :host-variable = CURRENT APPLICATION COMPATIBILITY;

            Examples to set the special register:
                 SET CURRENT APPLICATION COMPATIBILITY = :host-variable;
                 SET CURRENT APPLICATION COMPATIBILITY = ‘V10R1’;

By providing the capability to manage and reduce negative behaviors that can occur during the migration process, the new compatibility mode should make it easier and quicker for customers to move to new DB2 releases.

If you've already used the new compatibility mode during a migration to DB2 11, please share your thoughts about this feature in comments.