Blog
DB2utor

Categories

September 16, 2014

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.

September 09, 2014

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.

September 02, 2014

Remaining IDUG Events in 2014

I shared my thoughts about the IDUG NA conference in May, but this is just one of IDUG's three major annual events. The European and Australian conferences are both coming up in a few weeks.

The IDUG DB2 Tech Conference in Sydney, Australia, is Sept. 10-12. Here are the conference sessions.

Meanwhile, European readers of this blog should note that one of the largest and most diverse IDUG conferences is being held in Prague, Czech Republic, from Nov. 9-14. Educational tracks are dedicated to the latest releases DB2 for z/OS 11.0 and DB2 for LUW 10.5. In addition, there are general tracks on DB2 for z/OS and DB2 for LUW, and more specialized tracks on big data and analytics, along with application development.

If you'll be in attendance, be sure to catch IBMer John Campbell's in-depth two-part session, "DB2 11 for z/OS: Migration Planning and Early Experiences" (AB1).

Some of my coworkers at CA Technologies will also be presenting:

Steen Rasmussen
* "Application Development Goodies: What’s HOT in DB2 10 and 11" (F12).

* "The DB2 11 Catalog: Something new, something old and something changed" (A14).

Steve Thomas
* "Archiving data: DB2 comes to the rescue (twice)" (B5).

Zeljen Stanic
* "To Do IT(IL) or Not ... " (E13).

Denis Tronin:
* "Lost Without a Trace" (B3).

I've often discussed the value of IDUG conferences in this blog. Attendees receive invaluable education and updates on DB2 from some of the world's foremost database experts. In addition, IDUG events include many other services and features, such as free certification exams, access to one-day seminars, interaction with IBM speakers from the lab and the opportunity to network and share ideas with other DB2 professionals.

If you've attended IDUG events, please share your experiences in comments.

August 26, 2014

IBM Redbook Mobile App

Over the years, I've written often about IBM Redbooks. I enjoy learning from the experts who author these publications. I also enjoy sharing this information with DB2utor readers.

Recently I discovered that IBM has a new Redbook management application for IOS and Android mobile devices. I’ve downloaded the app and I must say, it's pretty cool.

When you first open the application, you'll see six menu options. 

  • Find IBM Redbooks
  • News
  • My Favorites
  • Visit us on the web
  • Connect with us
  • Find IBM resources

“Find IBM Redbooks” and “My Favorites” are my favorite options. With Find IBM Redbooks, you can sort Redbooks by publication data, popularity, subject area and publication type. With the latest publications, you can tap on the title and read an abstract and/or the table of contents. You can also save the link, download it to your device or share in Facebook, Twitter or email.

I use My Favorites to archive and read Redbooks. With this option, you can organize saved Redbooks into user-defined folders. Plus there's a bookmarking capability so you can quickly access key information.

Learn more and download the Redbook app here.

August 19, 2014

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.