Get Control of Access Path Changes for Dynamic SQL when Migrating to a New Version of DB2

Are you making plans to migrate to a new version of DB2? Do you know what impact the access changes for dynamic SQL will have on performance? When migrating to a new DB2 version, access path changes for dynamic SQL are unpredictable – as is the impact those changes will have on application performance. Learn how you can use Bind ImpactExpert to eliminate the unwanted surprises in version migration by performing a “precheck” on dynamic SQL access path changes.

This webinar will be presented by myself (Craig Mullins) and Joe Brockert, Sr. Software Consultant for NEON Enterprise Software. We'll discuss the issues associated with dynamic SQL during a DB2 migration and offer a live demo of Bind ImpactExpert. Join us to see the solution that provides predictability in access path changes.

Enroll by clicking on this link.

Q+A: Locking

I get questions e-mailed to me all the time. Although I try to read and answer them all, sometimes I don't. I hope those whom I've ignored over the years will forgive me, but I can't always answer everything (not enough time/energy) and sometimes things get lost or drop through the cracks.

Anyway, at times I will take a question I get and blog about it in Q+A format. Today is one of those days!

The question was: I want to perform a retry on an INSERT under DB2 Z/OS when I get a deadlock/timeout. -911 causes a rollback automatically. Is there a ZPARM or other method of turning this off? I am inserting millions of rows and do not want a rollback to the last commit point.

Here is my answer:

Well, first of all, let me recommend that you minimize the size of your unit of work. If you are inserting millions of rows without a COMMIT you are likely causing locking issues in your environment. The pages you have locked while you are waiting for your millions of inserts to finish are all unavailable to any other user of the table (assuming page locking). That means any data on any page that you have locked cannot be read by anyone else until your unit of work is committed. Any other user, running at the same time as you are, trying to get to any page you have modified, would be getting -911 too.

That being said, you can control whether or not the work is rolled back automatically in CICS (on a thread basis) using an RDO parameter (or RCT if on an ancient CICS). The parameter is called ROLBE (RCT) or DROLLBACK (RDO). If it is set to YES a CICS SYNCPOINT ROLLBACK is issued and a -911 SQLCODE is returned to the program. If NO is coded, a CICS SYNCPOINT ROLLBACK is not issued and the SQLCODE is set to -913. You will have to programmatically either specify COMMIT or ROLLBACK for the unit of work.

In a batch environment you will need to code your programs to periodically issue COMMITs after so many modifications (or using some other method like a timer or loop counter). There is no method I am aware of to automatically control this behavior outside of looking into a third party product (for example, Softbase Checkpoint Restart, and others).

Blog Tagged

I have been blog tagged by Willie Favero. Don't worry, its not as painful as it sounds!

Basically, blog-tagging is a game, of sorts, that has been crawling its way through the blogosphere for awhile now. The way it works, when you are tagged by another blogger, you have to write a blog posting about yourself, with 8 things that others might not know. . . and then tag 8 other bloggers.

So here goes:

1. I am an avid music fan. At last count, I have 5,281 CDs and albums (yes, I still have records). I know exactly how many I have because, geek that I am, I keep a list of them in a Filemaker database that I sync up with my Treo. I need that list on my Treo because, without it, I have been known to buy a CD I already own.
2. I currently live in Texas, but I was born and raised in Pittsburgh, PA. Go Steelers (we'll get 'em next year)! My Mom, my brother, and his family still live in the Pittsburgh area and I get back to visit them at least once a year.
3. I've also lived in the Chicago area. When people ask how I like it in Texas after living up North for so long, I tell 'em "I like it. I basically traded snow for humidity, and you don't have to shovel humidity!"
4. I've written two books - DB2 Developer's Guide and Database Administration: The Complete Guide to Practices & Procedures... and I'm working on co-authoring another one on DB2 performance.
5. I'm married, and I met my wife while working at PLATINUM technology. Remember them? A lot of good things happened during my days at PLATINUM! In fact...
6. At one point, I used to write those monthly DB2 tips you DB2 people used to get in the mail from PLATINUM.
7. I currently write four different columns for industry publications, as well as several blogs.
8. I own a dog, an English springer spaniel named Jerry... I call him my Jerry Springer spaniel... and two cockatiels.

OK, I guess that means I now have to tag eight others. Willie beat me to the punch on a lot of my favorite DB2 bloggers, though. So I'll tag Peter Armstrong, Chris Foot, Chris Eaton, Trevor Eddolls, Dave Moore, Phil Nelson, Fred Sobotka, and Ralph Wilson.

You folks are now "it"...

History of the Mainframe

While researching some items on the web I ran across a couple of interesting mainframe-related sites that I'd like to share with you.

First up, on The History of Computing Project's site, is this entertaining and informative timeline of mainframe history. The timeline starts in 1939 with the creation of the Atanasoff-Berry Computer at Iowa State. If you are looking for historical events in the life of the mainframe, then this is a good place to start. It contains links to information about, and pictures of, some early mainframes including the ENIAC and the IBM 701.

Another interesting mainframe-related page is at Carnegie-Mellon's Software Engineering Institute site. I point it out not because I agree with the "stuff" written there, but because I find it amusing to see the word "LEGACY" stamped over every inch of the page. Wise up! The mainframe is not just legacy, folks!

And finally a nice little article with the proper perspective on mainframe architecture from IBM. I particularly liked the way this article ended:

As the image of the mainframe computer continues to evolve, you might ask: Is the mainframe computer a self-contained computing environment, or is it one part of the puzzle in distributed computing? The answer is that The New Mainframe is both...

STOGROUPs and SMS [DB2 9 for z/OS]

With today’s posting we return to our examination of the new features of DB2 9 for z/OS. With V9, DB2 storage groups can be better integrated with SMS storage classes.

Prior to DB2 9, you could only spcify SMS storage classes, data classes, and management classes when using explicit IDCAMS defines. You could use those SMS specifications with your SMS ACS routings, but ACS routines filter on data set names, so those routines could become large and unwieldy if you defined multiple different combinations for different data sets.

The improvement in DB2 9 modifies the CREATE and ALTER STOGROUP statements to utilize SMS classes. This can greatly improve ease-of-use by minimizing the manual effort involved in managing DB2 data sets using DFSMS.

There are three new keywords in the CREATE STOGROUP syntax. You can specify just one, two or even all three of them on one CREATE STOGROUP statement:

• DATACLAS - influences characteristics such as the data set control block (DCB), striping, extended format usage, extended addressability usage and so on.
  
• MGMTCLAS – defines data set frequency of volume backups, migration requirement and related tasks.
  
• STORCLAS - define guaranteed spaced and other requirements.

DB2 will not check to verify that the data class, management class, or storage class specified actually exist. In that regard, the parameters are designed to work the same way that the VCAT and VOLUMES parameters have always worked. When the STOGROUP is used to allocate a data set, the specified classes are passed to DFSMS, which does the actual work.

The intent of this posting is not to act as an SMS tutorial. If you wish to investigate the details of SMS in more depth, consult the IBM manual titled -- z/OS DFSMS Implementing System-Managed Storage, SC26-7407.

Additionally, these same parameters have been added to the ALTER STOGROUP statement. When you alter SMS class names of a DB2 STOGROUP, this does not affect the existing data sets. However, if you run the REORG, RECOVER, or LOAD REPLACE utility, DB2 deletes the associated data set and redefines it using the new description of the storage group.

Finally, to accommodate the metadata for these new parameters, three new columns have been added to the SYSIBM.SYSSTOGROUP DB2 catalog table: DATACLAS, MGMTCLAS, and STORCLAS.