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.

Why is DB2 Rewriting my SQL?

The EXPLAIN statement provides a view into what DB2 determines is the optimum access path for accessing the data. The valid statements that can be processed by the EXPLAIN statement are SELECT, MERGE and INSERT, as well as search forms of UPDATE and DELETE. The information obtained from the EXPLAIN statement is placed in a set of user tables known as the EXPLAIN tables. The DB2 for z/OS Managing Performance guide has details about using EXPLAIN as well as lists of tables.

With DB2 11, column changes have been made to some existing tables. New tables have also been added to the family of EXPLAIN tables. For a complete list of changes, see Chapter 12 ("EXPLAIN Table Changes in Version 11) in the What's New guide.

As IBM brings new features such as temporal and archive tables to the database, it becomes more important to your tuning efforts to know when DB2 is modifying your SQL. These modifications to the EXPLAIN tables in DB2 11 are designed help you identify these changes:

* The PLAN_TABLE includes a new column, EXPANSION_REASON, that identifies the particular global variable or special register that's used to implicitly modify the query to support temporal or archive table processing.

* The DSN_PREDICATE_TABLE is an existing table with a change to the description. The description is: The predicate table, DSN_PREDICAT_TABLE, contains information about all of the predicates in a query. It is also used as input when you issue a BIND QUERY command to override predicate selectivities for matching SQL statements.

When DB2 rewrites the SQL, the ADDED_PRED values identify the actions that DB2 performed.

A list of ADDED_PRED values follows. I've added my own notes in parentheses.

blank -- DB2 didn't add the predicate.

'B' -- For bubble up. (Note: DB2 copies predicates from sub-select or table-expression  into the outer query to help filter rows more quickly.)

'C' -- For correlation. (Note: When a sub-select is written without a correlation, the optimizer determines that a correlated sub-select will perform better than a non-correlated query.)

'J' -- For join. (Note: When the optimizer detects that related tables are being processed without providing join criteria, join criteria is added.)

'K' -- For LIKE for expression-based index. (Note: The optimizer detected an expression index and modified the SQL to take advantage of this index.)

'L' -- For localization.

'P' -- For push down. (Note: The optimizer detected a predicate from an outer query that can be used in a sub-select or table expression.)

'R' -- For page range.

'S' -- For simplification.

'T' -- For transitive closure.

Hopefully IBM will soon publish additional documentation on these values and how they're being used to improve performance.