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.

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.

Dynamic Statement Cache: Sharing Prepared Statements

An SQL statement can be executed by an application with either static or dynamic text. Each type of statement execution has its own benefits. The biggest reason to use static SQL is to avoid the overhead with preparing a statement for execution.

Over the past decade, the use of dynamic SQL has grown dramatically, as IBM continues to improve its performance. Much of this improvement has come through providing DBAs with alternatives to having to prepare a dynamic statement each time one is executed. The first innovation in this area was the introduction of the dynamic statement cache, a pool of memory in which DB2 saves the control structures for prepared SQL statements that can be shared among different threads (execution of a program). To enable dynamic statement cache, YES must be specified for the CACHEDYN subsystem parameter.

When a statement is prepared, DB2 will search for the statement in the dynamic statement cache and reuse the control structures. This works great for applications that code predicates with host variable rather than literals. The problem with literals is that potentially every execution of a statement is unique, so in this case the cache will fill up with many statements, none of which will ever be reused. To help in this circumstance, IBM has introduced a new PREPARE parameter: CONCENTRATE STATEMENTS WITH LITERALS.

When using CONCENTRATE STATEMENT WITH LITERALS, the statement is stored in the cache and each literal is replaced with "&."  For example:

SELECT COL1, COL2 from T1 WHERE COL2 = ‘ABC’ is stored in cache as SELECT COL1, COL2 from T1 WHERE COL2 = &

When the statement is prepared, DB2 will search for the statement in the dynamic statement cache searching for the literal such as "ABC." When not found, DB2 searches the cache for the statement containing the replaced character, &. Once it's found, the control structures are reused. This process doesn't occur if the statement is coded with the "?" parameter marker; for example, this statement cannot take advantage of CONCENRATE STATEMENT WITH LITERALS:

            SELECT COL1, COL2 from T1 WHERE COL2 = ‘ABC’ and COL1 > ?

The avoidance of preparing a statement can provide a tremendous CPU savings. See the DB2 10 Performance Management guide for details about dynamic statement cache, including possible reasons why DB2 won't share certain identical statements in the statement cache.

 

A Smarter Optimizer in DB2 11

One of the challenges of using ERP applications such as Oracle or SAP is that the SQL can't be rewritten to improve the performance. However, with the optimizer enhancements in DB2 11, predicates can be automatically rewritten as stage 2, making them indexable.

This information is taken from session Terry Purcell's recent presentation at the IDUG DB2 Tech Conference in Australia. The usual disclaimers apply.

In the sample below, the first line is SQL rewritten as stage 2. The next line is indexable:

            EQUAL Predicates

o    WHERE YEAR(DATE_COL) = 2013      ←Stage 2

            Becomes

o   WHERE DATE_COL BETWEEN ‘2013-01-01’ and ‘2013-12-31’   ←Indexable

This rewrite will also occur with IN, BETWEEN and RANGE predicates. Terry's presentation has more examples.

Here are some additional SQL-related enhancements:

Prune always TRUE predicates: When a predicate always evaluates to TRUE, DB2 removes it from the statement so the predicate won't evaluate at execution time. For example:

• WHERE 1=1 and CUSTNO = ?

      becomes

• WHERE CUSTNO = ?

 

Prune always FALSE predicates: When a statement is written to always evaluate to FALSE, the optimizer will rewrite the SQL and remove the predicate. In this example, ‘A’ = ‘B’ is always false, so that predicate is removed during SQL rewrite:

•  WHERE C1 = ? OR ‘A’=’B’

becomes

•  WHERE C1=?

DB2 11 extends the pruning to complex predicates that include AND Boolean operations. In this example, the predicate after the OR will always be false, so the entire predicate is removed:

•  WHERE (1=1 and T1.C1 = T2.C1)

      OR   (1=2 and T1.C2 = T2.C2)

    becomes

•  WHERE T1.C1 = T2.C1

     The documented statement “OR 0=1” won't be pruned because this is a documented coding tip.

 

CASE statements (formerly stage 2) are now indexable: For example:

              WHERE C1 = CASE(CAST(? AS INT))

                                          WHEN 1 THEN ‘A’

                                          WHEN 2 THEN ‘B’

                                          ELSE ‘C END;

 

CASE statement when JOIN predicate:

• When the CASE expression must be evaluated first.
• In the example below, join predicate is indexable if C1 is evaluated first.

 

 WHERE T2.C1 = CASE WHEN T1.C1 = ‘Y’

                                          THEN T1.C2

                                           ELSE   T1.C3    END;

 

Predicate PUSH DOWN:The process of predicate push-down involves the optimizer rewriting the SQL in such a way the predicate can be evaluated sooner; this reduces the number of rows that are evaluated in stage 2. The IBM Redbook, "DB2 10 for z/OS Performance Topics," has a detailed explanation.

DB2 11 extends predicate push-down to non-Boolean term (OR) predicates and stage 2 predicates (expressions). Here's an example of each:

Example 1: With non-Boolean term (OR) predicates, this:

SELECT EMPNO, SALARY, DEPTCOUNT

FROM EMP A ,

(SELECT WORKDEPT, COUNT(*)

FROM EMP

GROUP BY WORKDEPT) AS B(WORKDEPT, DEPTCOUNT)

WHERE A.WORKDEPT = B.WORKDEPT

AND (B.WORKDEPT LIKE 'C%' OR B.WORKDEPT LIKE 'A%‘);

 

     becomes this:

 

SELECT EMPNO, SALARY, DEPTCOUNT

FROM EMP A ,

(SELECT WORKDEPT, COUNT(*)

FROM EMP

WHERE WORKDEPT LIKE 'C%' OR WORKDEPT LIKE 'A%‘)

GROUP BY WORKDEPT) AS B(WORKDEPT, DEPTCOUNT)

WHERE A.WORKDEPT = B.WORKDEPT;

 

Example 2: With stage 2 predicates (expressions), this:

 

SELECT EMPNO, SALARY, DEPTCOUNT

FROM EMP A ,

(SELECT WORKDEPT, COUNT(*)

FROM EMP

GROUP BY WORKDEPT) AS B(WORKDEPT, DEPTCOUNT)

WHERE A.WORKDEPT = B.WORKDEPT

AND UPPER( B.WORKDEPT) = 'C01'

 

      becomes this:

SELECT EMPNO, SALARY, DEPTCOUNT

FROM EMP A ,

(SELECT WORKDEPT, COUNT(*)

FROM EMP

WHERE UPPER( B.WORKDEPT) = 'C01'

GROUP BY WORKDEPT) AS B(WORKDEPT, DEPTCOUNT)

WHERE A.WORKDEPT = B.WORKDEPT

The optimizer enhancements in DB2 11 are quite impressive. Keep in mind though that the performance gains you'll receive by upgrading will vary depending on your SQL. However, the good news is the optimizer is now capable of rewriting the SQL automatically. As noted, this is especially handy when you're running package applications that don't allow for SQL coding changes.

Runtime Optimization Hints

In writing about changes to SQL optimization hints, I neglected to note that there are actually two types of statement-level hints. I only covered access-level optimization hints last week. The other type of statement level optimization hint is to set the runtime options for a given SQL statement text.

The setup for runtime options hints is similar to that of optimization access path hints. Insert a row into DSN_USERQUERY_TABLE with a unique QUERYNO. In doing this, make sure that no row is found in the user PLAN_TABLE for the given statement, because if there's a row in the PLAN_TABLE when the BIND QUERY LOOKUP(NO) runs, the SYSIBM.SYSQUERYOPTS table won't contain a row. That said, if you do make this mistake, it's a simple fix. Just delete the rows from the user PLAN_TABLE and rerun the BIND QUERY command to populate SYSIBM.SYSQUERYOPTS.

Hints are an either-or proposition. It's not possible for one hint to be both an optimization access path hint and a optimization options hint. Remember: If you're creating an options hint, you must not have rows in the PLAN_TABLE for the given statement. I always delete the rows in DSN_USERQUERY_TABLE and insert new rows with the settings I want to be populated into the access path repository.

The runtime options that can be set using DSN_USERQUERY_TABLE are:

REOPT:    Set bind options that is in effect for the plan
(A:AUTO, 1:ONCE, N:NONE, Y:ALWAYS)
STARJOIN:    Whether Starjoin is Enabled
 (Y:Enabled, N:Disabled, blank:Starjoin not specified)
MAX_PAR_DEGREE:    The maximum parallel degree. 
Valid values between 0 and 254, -1:Not Specified
DEF_CURR_DEGREE:    Whether query parallelism is enabled
            (ONE: Disabled, ANY:Enabled, blank: Disabled
SJTABLES:        The number of tables specified in a query to qualify for star joins processing.
-1: Not Specified
GROUP_MEMBER:    The group member name to which the parameters are to be applied. This column is
 blank if no group member name is specified.

Sample Insert into user DSN_USERQUERY_TABLE to define a system-level option hint for a statement to be executed with REOPT(ALWAYS). The HINT_SCOPE is set to 0 and REOPT is set to ‘Y’:

INSERT INTO DSN_USERQUERY_TABLE          
( QUERYNO, SCHEMA, HINT_SCOPE            
 , QUERY_TEXT, USERFILTER, OTHER_OPTIONS 
 , COLLECTION, PACKAGE, VERSION          
 , REOPT, STARJOIN, MAX_PAR_DEGREE       
 , DEF_CURR_DEGREE, SJTABLES, OTHER_PARMS
)                                        
SELECT 1                                 
     , '' , 0                            
     , PS.STATEMENT, '',''               
     , '', '', ''                        
     , 'Y','', -1                        
     , '', -1, ''                        
FROM SYSIBM.SYSPACKSTMT PS               
WHERE PS.COLLID = 'COLTR05_COLLID'       
  AND PS.NAME = 'ROWSET'                 
  AND PS.VERSION = 'MYVERSION_1'         
  AND PS.STMTNO = 264;

SQL Optimization Hints: What's New

Last week I gave a quick introduction of DB2 SQL optimization hints and noted that DB2 10 brings some key enhancements in this area. Now I'll get into the specifics of what's new.

Prior to DB2 10, the QUERYNO clause was used to associate a query to the related hints. However, because programming changes could potentially alter QUERYNO, errors often occurred. To deal with this problem, programmers began using QUERYNO on the SQL statement. Assigning a fixed value eliminated the possibility of numbers changing due to coding changes. This work-around is effective provided you own the code, but in the case of vendor products, it's generally not an option.

But in the latest release, DB2 addresses the QUERYNO issue by providing statement-level optimization hints -- aka instance-based statement hints. With this method, hints are enforced based on the statement text for the entire DB2 subsystem. This is why they're also referred to as "instance-based" hints. Statement-level hints are stored in the DB2 catalog in a set of tables known as the access path repository.

Use BIND QUERY  LOOKUP(NO) to populate the access path repository. This command reads the statement text, default schema and a set of bind options from every row of DSN_USERQUERY_TABLE, as well as system-level access path hint information from correlated PLAN_TABLE rows. BIND QUERY then inserts the pertinent data into the SYSIBM.SYSQUERY, SYSIBM.SYSQUERYPLAN and SYSIBM.SYSQUERYOPTS catalog tables.

When using statement-level hints, follow these steps:

1)     BIND the package with EXPLAIN(YES) to populate a user PLAN_TABLE with system-level access path hints.

2)     Populate user table DSN_USERQUERY_TABLE with the query text. Use install member SDSNSAMP(DSNTESH) to create the access path repository tables. The best way to populate this table with the statement is to use an INSERT with a select against the SYSIBM.SYSPACKSTMT catalog table.

In this sample, insert statement 264 is used for package name rowset found in collection coltr05_collid.

INSERT INTO DSN_USERQUERY_TABLE          

( QUERYNO, SCHEMA, HINT_SCOPE, QUERY_TEXT, USERFILTER, OTHER_OPTIONS 

 , COLLECTION, PACKAGE, VERSION , REOPT, STARJOIN, MAX_PAR_DEGREE       

 , DEF_CURR_DEGREE, SJTABLES, OTHER_PARMS

)                                        

SELECT PS.STMTNO                         

     , 'COLTR05' , 1                     

     , PS.STATEMENT, '',''               

     , PS.COLLID, PS.NAME, PS.VERSION    

     , '', '', -1                        

     , '', -1, ''                        

FROM SYSIBM.SYSPACKSTMT PS               

WHERE PS.COLLID = 'COLTR05_COLLID'       

  AND PS.NAME = 'ROWSET'                 

  AND PS.VERSION = 'MYVERSION_1'         

  AND PS.STMTNO = 264;

 

3)    Run a new BIND QUERY LOOKUP(NO) command. This will insert hints into the DSN_USERQUERY_TABLE repository based on the statements in the PLAN_TABLE. 

4)     Use BIND QUERY LOOKUP(YES) to verify you have a row in the access repository.  Insert the SQL statement into the DSN_USERQUERY_TABLE and after  executing BIND QUERY LOOKUP(YES) the QUERYID column will be set.

5)     The FREE_QUERY command is used to remove the hint from the access path repository.

As with optimization hints in general, once a statement-level optimization hint is created, DB2 validates and tries to enforce the hints. Hints for static SQL statements are validated and applied when you rebind the package that contains the statements. Hints for dynamic SQL statements are validated and enforced when the statements are prepared.

Once the access-path repository is populated, you should remove the statement from DSN_USERQUERY_TABLE to avoid replacing hints on subsequent BIND QUERY commands. 

Hints are created or replaced for every row in DSN_USERQUERY_TABLE when BIND_QUERY is issued. Consequently, changes to PLAN_TABLE data might result in the replacement of existing hints of either type if old rows remain in the DSN_USERQUERY_TABLE and BIND_QUERY is issued.

 

*updated 7/20/11

 

 

Using DB2 SQL Optimization Hints

Have you considered using DB2 optimization hints? The feature debuted on DB2 for z/OS Version 6 with the introduction of OPTHINT, HINT_USED and PRIMARY_ACCESSTYPE columns on the PLAN_TABLE. 

Hints provide:

• A temporary fix to resolve an immediate crisis
• Access path regression from previously good path
• Lock in access path to stabilize desired access path

However, along with those benefits, using hints has these drawbacks:

• Locked in access path will not change to take advantage of new improved access paths
• Error prone due to changes in QUERYNO during program development
• Complicated to understand and figure out what values should be used when defining the hint.

Before you can start using optimization hints, your DB2 environment must be set up to accomodate hints. First, during the DB2 install, set OPTIMIZATION HINTS on installation panel DSNTIP8 to YES. I also strongly suggest upgrading to the most current PLAN_TABLE definition. Under the default, the PLAN_TABLE used for hints must contain at least the 49 columns defined in DB2 Version 6. By upgrading, you can benefit from all the extra information DB2 can provide. DB2 10 install job hlq.SDSNSAMP(DSNTESC) creates the PLAN_TABLE with 65 columns.

For static SQL, I take these the steps:

1. BIND package with EXPLAIN (YES) to populate the plan_table.
2. Update the rows in the PLAN_TABLE with OPTHINT name (e.g., "goodpath").
3. Set up the BIND package to use OPTHINT('goodpath').
4. Query the PLAN_TABLE to see if the hint was used. Also note the messages in the BIND job, which tell you how many hints have been applied.

DB2 10 brings some useful enhancements for managing hints. "Instance-based" optimization hints help you avoid errors due to changing QUERYNO. I'll go into details about this next week.

Has your organization taken advantage of hints? What advice would you give to those who are looking to use hints? Please share your experiences, good and bad, in Comments.