Blog
DB2utor

Categories

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.

July 15, 2014

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.

July 08, 2014

Happy 7th Anniversary to DB2utor

I was recently talking to another DB2 professional who said that when he's looking for DB2 information, he usually ends up at DB2utor. That was a very nice complement. It's also the sort of impact I hoped this blog would have when I started it.

As it happens, DB2utor launched seven years ago this month. While this site houses a wealth of information on DB2, DB2 itself, of course, is ever-evolving thanks to the efforts of IBM developers. DB2utor will also continue to evolve, and I hope you'll be a part of it. I've always relied on readers to tell me what they're interested in. Check the DB2utor archive and you'll find more than 100 tags covering DB2 and related topics. If there's something you believe should be added to that list, please let me know.

I’m thankful that people take the time to read these posts, and I'm especially appreciative of those who take a moment to post a comment on the blog or contact me directly. In short, I'm grateful for your continued support.

July 01, 2014

Redbooks Examines DB2 Analytics Accelerator 4.1

Recently I shared my impressions of the latest version of IBM DB2 Analytics Accelerator. Now I'm here to let you know that there's a new Redbook that details the features found in Version 4.1.

"Reliability and Performance with IBM DB2 Analytics Accelerator 4.1" is currently a draft publication. The formal publishing date is set for the end of July. I'll list just a few highlights:

* Chapter 5 examines new query accelerator features, including static SQL support as well as support for “NOT IN <subquery>” and many other new sub-query predicates. Numerous customers I talk to believe that their current workloads aren't large enough to run cost-effectively on DB2 accelerators. However, with the recent query enhancements, it may be time to reconsider this. Use the monitoring tools and you may discover that SQL statements that previously couldn't run on the accelerator now can.

* Chapter 6 provides a look into the primary factors that affect throughput of loading data into the accelerators -- partitions and table number and sizes; processors (number and speed), and; Netezza host. The chapter goes through different scenarios to help you understand your database design and the best way to load the data.

* Chapter 7 covers online data archiving with the High-Performance Storage Saver.  When archiving occurs, the table space partition is put into a persistent read mode with a status of PRO. The process to archive and restore is handled by two separate stored procedures. However, I'm not sure how to remove the archived partition when you no longer want to save it on both DB2 and the accelerator. Hopefully the process of purging old data from the accelerator will be clarified in the final version of the Redbook.

* Chapter 9 is devoted to monitoring enhancements. Having spent most of my career tuning SQL, I find this information extremely interesting. There are several options for monitoring the accelerator: DB2 Accelerator Studio, DB2 commands, query history tables and DB2 monitoring tools from IBM or third-party vendors.

The DB2 command –DIS ACCEL(name) DETAIL provides information about active and queued queries, wait times and disk storage utilization for the named accelerator as well as totals for all subsystems connected to the named accelerator.

As for real-time monitors, IBM continues to add information to the standard interfaces it and third-party vendors use to monitor DB2 activity. The first set of information allowed customers to see what is happening on the Netezza box. The next set of information provided more insight into which statements are running on the accelerator and how much was saved from general DB2 processing. In addition to monitoring utilization levels, customers need to know that the statements they create are capable of running on the accelerator. Making this information available real-time via the monitoring tools adds considerable value to this product.

Here's the complete table of contents:

Chapter 1. The analytics lifecycle

Chapter 2. Database design considerations

Chapter 3. Overview of IBM DB2 Analytics Accelerator V4.1

Chapter 4. Installation and maintenance procedures

Chapter 5. Query enhancements

Chapter 6. Load and incremental update

Chapter 7. Online data archiving

Chapter 8. High availability, disaster recovery, and workload balancing

Chapter 9. Monitoring enhancements

Chapter 10. The Accelerator Loader

Appendix A. Preparing input data for Accelerator Loader

I feel this publication is well worth your time. If you read it, please share your thoughts in comments.