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
                    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;

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.

June 24, 2014

Capturing Dynamic SQL with EXPLAIN

I’ve already written extensively about EXPLAIN, but since IBM continues to develop the feature and enhance the process, there's plenty more to cover.

For instance, I recently started experimenting with the SET CURRENT EXPLAIN MODE statement, which is used to capture EXPLAIN data for dynamically executed statements. The challenge with dynamic SQL is that -- because it's built dynamically based on input during execution -- you may not know how the statement will appear until it's executed. This makes it challenging to prebuild and add an EXPLAIN command to determine the access path DB2 will use. However, starting with DB2 10, applications can be modified to take advantage of the SET CURRENT EXPLAIN MODE statement. The options are:

  • NO specifies that no EXPLAIN information is captured. NO is the initial value of the EXPLAIN MODE special register.
  • YES enables the EXPLAIN facility and causes EXPLAIN information to be inserted into the EXPLAIN tables for eligible dynamic SQL statements after the statement is prepared and executed. All dynamic SQL statements are compiled and executed normally.
  • EXPLAIN enables the EXPLAIN facility and causes EXPLAIN information to be captured for any eligible dynamic SQL statement after the statement is prepared. This setting behaves similarly to YES; however, dynamic statements (except for SET statements) aren't executed.

To enable a program to execute a hard-coded statement, pass in a parameter to drive the program logic or simply pass in a value to be used in a host variable on the SET statement.

This is hard-coded logic:

    IF pass-value = ‘NO’ then


    ELSE IF pass-value = ‘YES’ then


    ELSE IF pass-value = ‘EXPLAIN’ then






Here's how to pass in a value for a host variable:



The host variable must be defined as CHAR or VARCHAR, and it must be set to a valid option (NO, YES or EXPLAIN). Leading blanks aren't allowed. All input values must be uppercase. In addition, they must be left-justified within the host variable and padded on the right with blanks if the length of the value is less than the length of the host variable.

The host variable is a simple option for turning on/off EXPLAIN processing during program execution. I used this script in SPUFI to test it out:




Notice I turned on the auto EXPLAIN feature with statement 3, and then shut it off with statement 5. I didn't want statement 6 to appear in the EXPLAIN tables. I did find an interesting contradiction to the documentation during my testing in SPUFI. When I set mode to EXPLAIN at statement 3, I received the message below warning me that the statement wasn't executed. I also found that nothing after statement 4 was executed.  So obviously, statement 6 wasn't executed, either.

Note: When you use mode EXPLAIN you will receive warning SQLCODE +217:


Have you had an opportunity to use this feature to help you EXPLAIN dynamic SQL statements? Please share your experiences in comments.