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.

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.

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

 

DB2 Accelerator V4.1 Reaches a New Level

In my previous post I noted how much I got out of the opening keynote session at the recent IDUG NA conference. Another presentation I enjoyed was IBM's Peter Bendel discussing the latest enhancements to the IBM DB2 Analytics Accelerator for z/OS V4.1 (DB2 Accelerator).

I've touted the benefits of DB2 Accelerator numerous times, but I was amazed by what I heard about this latest version. We were told that customers are seeing 2,000X improvement in query performance. Yes, that's 2,000, with three zeros. In one specific example, a customer query that had required three hours of processing could now be run in 6 seconds.

One feature many of us have been waiting for is static SQL support. V4.1 brings some new BIND parameters, and I find some of the options quite intriguing. The first is QUERYACCELERATION, which has values of NONE, ENABLE, ENABLE WITH FAILBACK, ELIGIBLE and ALL. If you're wondering how ENABLE WITH FAILBACK works, it's pretty cool. With this setting, DB2 Accelerator will rebind the statement if it fails during execution on the accelerator and will route it back to run on DB2 without returning an error to the application.

Regardless of which bind option you choose, DB2 will report total failures at the accelerator level for each subsystem using IFCID 002. If enabled, IFCID 378/379 will record any SQL request routed to the DB2 Accelerator. These records will not contain SQL statement text. My understanding is more details are coming soon through maintenance for V4.1. You can also view information about past queries or currently running queries by using the SYSPROC.ACCEL_GET_QUERIES stored procedure. If you don’t want the workload to failback and run on DB2 due to resource constraints, then use BIND option ENABLE. This will force DB2 to fail the execution when an error occurs and return an error status back to the application.

Another new bind parameter is GETACCELARCHIVE (with values of NO and YES).  When set to yes and all the requirements for accessing partitioned archived data are met, the statement will retrieve archived data.

With V4.1, the workload is now balanced across multiple accelerators running on machines connected to z/OS and DB2. The accelerators notify DB2 of its capacity and DB2 will route the query to the most optimal accelerator. This is not a smooth migration. The accelerators must be at V4.1 and the tables need to be redefined to the accelerator.

When replicating data you no longer have to stop all tables when reloading a table. The log reader no longer need to read all log records for all tables, but instead can now filter on relevant log records which reduces overall CPU usage on the mainframe.

V4.1 also includes changes to the high-performance storage server, which allows you to store full tables or table partitions on the DB2 Accelerator rather than a dedicated host. The big change in this release is integrity of the data: when the data is archived and moved to the accelerator, the partition archived is put into a PRO state which will restrict modifications such as inserts and deletes.

There's much more I could cover if I had the time and space, but what do you think? If you've had a chance to use DB2 Analytics Accelerator for z/OS with your static SQL applications, please share in comments your impressions of the new V4.1 features.

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.

Redbook Details Key DB2 11 Enhancements

IBM just released a draft version of the Redbook, "DB2 11 for z/OS Performance Topics." The publication details key functions for those migrating from DB2 Version 10.

DB2 10 was a major release that provided out of the box CPU savings, and this direction continues with DB2 11. Most of the DB2 11 CPU reductions are built into the DB2 engine and do not require application changes. You can take advantage of these enhancements even in CM mode (after REBIND for static SQL and PREPARE for dynamic).

IBM points to these CPU reductions for customers migrating from DB2 10:

• Online transaction workloads savings of up to 10 percent.
• Update-intensive batch workload savings up to 10 percent.
• Complex reporting queries savings of 25-40 percent when processing compressed tables. 
• Queries executed on DB2 Analytics Accelerator can see up to 10 percent savings.
• zIIP eligibility can contribute to cost reduction as the majority of asynchronous SRB processing under DB2 address space as well as the RUNSTATS and LOAD utilities are now zIIP-eligible.

Of course there's far more than I can include here, but to give you a broad idea of the material covered in this publication, here's the chapter list:

Chapter 1: Introduction

Chapter 2: Subsystem

Chapter 3: Synergy with z platform

Chapter 4: Availability

Chapter 5: DB2 Analytics Accelerator

Chapter 6: Data sharing

Chapter 7: SQL

Chapter 8: Application enablement

Chapter 9: Distributed environment

Chapter 10: Utilities

Chapter 11: Installation and migration

Chapter 12: Monitoring

If a DB2 10-to-DB2 11 migration is in your near future -- or if you've already completed one -- this Redbook is well worth your time. I'd appreciate hearing about your impressions of the document, as well as your views on DB2 11's most compelling enhancements. Please share your thoughts in comments.

DB2 and Hadoop an Unlikely Combo

I recently came across a great SHARE presentation that examines the relationship between DB2 for z/OS. "Big Data Strategies with DB2 for z/OS" provides a nice overview of Big Data with Hadoop before going into some technical benefits of using built-in DB2 user-defined functions (UDF) delivered with Infosphere BigInsights to process Hadoop data from DB2 for z/OS applications.

Here's a sample of the SQL to read Hadoop data:

            SELECT TX.*

            FROM TABLE ( hdfs_Read( ‗http://172.16.134.134:8080/data/sample.csv ‘)

                         AS TX (Seller varchar(20)
                                   , Year INT
                                   , Total decimal(15,2));

 

The capability to enrich operational data with big data sources such as Twitter and Facebook provides powerful benefits to existing applications running DB2 for z/OS, and it will be interesting to see where IBM takes this relationship over the next year or two. I can see some customers bringing the data from Hadoop into DB2 for z/OS and accessing it from DB2 accelerators, while others will choose to store DB2 for z/OS data in Hadoop and conduct local analysis on existing big data. Providing a facility to manage the movement of data and controls around the governance of data being accessed on these different systems will, I believe, be key in the ongoing development of Hadoop support on DB2 for z/OS.

Array Support in DB2 11

DB2 11 brings many new SQL processing enhancements that are designed to simplify application coding. One such enhancement is support for arrays. An array is a user-defined data type that consists of an ordered set of elements of a single built-in data type. Elements can be accessed and modified by their index position. An array type is created with an SQL CREATE TYPE (array) statement. An array type can be used as a parameter of a procedure and as a variable in an SQL procedure.

Here's the syntax for creating a user-defined array:

        >>-CREATE--TYPE--array-type-name--AS--| built-in-type |--------->

 

                     .-2147483647-------.      

        >--ARRAY--[--+------------------+--]---------------------------><

                     +-integer-constant-+      

              '-| data-type2 |---'  

There are two array types: ordinary or associative. An ordinary array type is defined using the ARRAY(integer-constant) syntax. The maximum cardinality is based on the value integer-constant. The value must be an integer between 0 and positive 2147483647. The maximum value is the default size (2147483647).

Here's an example of an ordinary array user-defined type. The company-name element can be up to 100 characters in length, while the company-name array can contain up to 75 elements.

            CREATE TYPE company-name AS VARCHAR(100) ARRAY(75)

ARRAY(data-type2) is an associative array that's indexed by data-type2. The data type must be either INTEGER or VARCHAR, and the value assigned to the index must be of the same data type. Each index is unique, and as a new index is assigned, the cardinality is incremented by 1. The maximum cardinality of an array is limited by the total amount of memory available to DB2 applications.

For more information on ARRAY TYPE, see the DB2 11 for z/OS SQL Reference Guide.

Next week I'll explain how user-defined data types (ARRAY TYPE) can be used.

May Mainframe Madness

For all the posts I've had of late about free educational and technical materials, I've been remiss to mention what my own company has been up to.

This month CA is running an event it describes as the world's largest virtual mainframe trade show: CA May Mainframe Madness 2012. By registering here, you can access webcasts, live chats and "on demand" presentations hosted by technical experts from across the mainframe world.

Here are some upcoming webcasts:

• Thursday, May 17, 10 a.m. EDT: Sheryl Larsen
        - DB2 9 and 10 SQL: Index and Optimization Enhancements
• Wednesday, May 23, 10 a.m. EDT: Craig Mullins and Steve Crocker
         - Getting to DB2 10 for z/OS

Here are some presentations:

• On Demand – Craig Mullins
         - DB2 Top 10 Lists Part 1
         - DB2 Top 10 Lists Part 2
• On Demand – Steen Rasmussen
       - Tips for Better DB2 for z/OS Performance
       - Do You Really Want NOT to LOG?
       - The DB2 10 catalog – A Revolution

And on Tuesday, May 29 at 1 p.m. EDT, there's a live chat roundtable with Sheryl Larsen, Steen Rasmussen, Scott Jessee and Dave Schipper. The moderator is none other than yours truly.

Once you've registered, you'll get a better idea of the virtual trade show format (you can also view these photos: Image 1, Image 2, Image 3, Image 4, Image 5). Stop by the "Database Management booth" in the "exhibit hall" to ask questions, or check out the "House Of Mainframe lounge" to network with peers, check for messages, join a group or sign up for a chat.