Turn the Page on Sequential Insert

IBM continues to innovate to improve DB2 performance and availability. An example of this is the new RANDOM option, which I discussed last week. Found on the CREATE/ALTER INDEX statements, RANDOM stores an ever increasing sequential key index into random locations to help avoid the creation of a hotspot during insert processing on the last index page.

RANDOM is included with the current DB2 9, which brings several other new innovations in the area of index management. One such innovation is the new index asymmetric page splitting feature that will help you reduce wasted space as well as improve database performance.

When an index leaf page is full and you perform an insert, DB2 creates a new page and moves 50 percent of the index keys to this new page. This becomes a problem with ever increasing index key values, because 50 percent of the page left blank goes unused. Asymmetric page split processing is designed to address this issue. Now, when the page is full and DB2 detects sequential insert with ever increasing index keys, instead of conducting the 50:50 split, DB2 creates a new page and continues inserting new rows on this new page. This improves space utilization, reduces CPU consumption to rebalance the index keys and reduces contention while waiting on rebalancing during frequent page splits.

Make sure you are current on maintenance. The latest APAR PK91830 will fix problems related to asymmetric page split processing.

Sequential Insert Hotspot Relief

A common problem database administrators (DBAs) try to avoid when designing an application is a primary key or index that is for ever increasing like a sequence number or timestamp. DBAs go out of their way to avoid keys like this because of locking contention issues. During the insert process, DB2 acquires a lock on the index page. If multiple transactions with a sequential key are inserting at the same time, you'll more than likely experience a slowdown--and in the worst case, you'll get deadlock or timeout failures. This seems to be even more of a problem when online transactions are processing in a data sharing environment.

DB2 9 for z/OS offers relief in this area by allowing the insert of the sequential column through a new RANDOM option on both the CREATE INDEX and ALTER INDEX statements. When RANDOM is used on the column in an index, DB2 stores the key values in random locations within the index tree. This reduces the chance of ever-increasing sequential inserts being on the same page, which decreases the chance of having lock contention.

Randomly ordered indexes can be used with equality lookups on a specific column. In addition, randomly ordered key columns can be used in non-matching index scans, while index-only access can be used in random key columns.

Here's an example of a CREATE INDEX using the RANDOM clause:

CREATE INDEX creator.table_name_idx1
       ON creator.table_name        
       (COL1                       ASC    
       ,COL2                       RANDOM)   

RANDOM cannot be specified on a key column that is varying in length with the NOT PADDED option. If you've taken advantage of RANDOM key columns in your application, please take a moment and tell us of your experience in Comments.

Batch Modernization on z/OS

Much has been written in blogs and z/OS forums about the need to hire a younger generation to take over for the aging workforce. The reality is that fewer computer science graduates are wanting to work on the mainframe because it wasn't taught to them -- and because the mainframe wasn't part of their curriculum, they view it as outdated technology and a career dead-end.

Now, companies like IBM and CA are introducing products that are geared toward this new Xbox and PlayStation generation. And, just as Windows replaced DOS, you'll see the web browser replace ISPF and TSO. This will make the transition from developing and working on Windows and Internet Explorer much smoother and more familiar. However, while all this is great for the online transaction processing systems, what about the boring batch systems that are written in COBOL and execute using Job Control Language (JCL)? How can these batch-running programs send e-mail or save reports in PDF format so they can be shared and used by modern Web-based applications?

The recently released IBM Redbook, "Batch Modernization on z/OS," attempts to answer these questions. The publication explains why we need to implement new technologies like Java and PHP on z/OS. It also provides sample DB2 DDL and source code for implementing sample applications.

From my reading, I learned the interesting fact that some standard Java APIs are available to help you interact with z/OS services. For example:

•    MVS dataset and VSAM access to interact with z/OS specific data
•    Condition code passing for integration of Java batch jobs into z/OS job nets
•    z/OS Catalog search
•    Interaction with the MVS Console
•    Conversion of COBOL/ASM data types to Java types
•    Invoking DFSORT to effectively sort data
•    Access to z/OS Access Method Services (IDCAMS)
•    RACF APIs to integrate Java into the z/OS security model
•    Writing of Logstreams (e.g. as an Appender to LOG4J)
•    Submission of Jobs from Java

Since batch jobs do a lot with reading, writing and sorting sequential and VSAM files, it makes sense to provide these services available to Java programs. The world is changing very quickly, and the way we build and support applications on z/OS is changing just as rapidly.

As one who's repeatedly expressed his love of Redbooks, let me tell you that, from what I've read, "Batch Modernization on z/OS" is one of the most valuable Redbooks available to both traditional mainframe personnel and LUW (Linux, UNIX, Windows) pros who are getting started with z/OS. If your company is modernizing its batch systems, you owe it to yourself to download and review this publication.

And, if you or your company has already gone down the road of modernization, please take a moment to post a comment. It's always nice to learn from those who've gone through the process.

A Preview of the New DB2

On Nov. 3, IBM is providing a sneak peek at the next release of DB2 for z/OS. I was fortunate enough to be briefed by the IBM lab last year on the new features and functions are being targeted for delivery in the upcoming release. It's an impressive list.

Beyond all the great new functionality, one primary reason I think most companies will want to move to DB2 X for z/OS is the potential to reduce CPU cost by about 5 percent simply by installing it. This number comes from IBM.

If you're involved with DB2 as a database administrator, IT manager, systems architect, application developer, application architect, system administrator and systems programmer -- or if you'd just like to learn more -- check out this webinar. Again, it's set for Tuesday, Nov. 3, with an 11 a.m. EST start time.

IBMer Jeff Josten is the presenter. If you've not heard him, he's a fantastic speaker.

Register for IBM DB2 X for z/OS Technical Overview.

IBM Providing the Migration Path to Universal Table Space

I believe that the future for all DB2 data storage on z/OS is in universal table space (UTS). This may be a strong statement, but as I look at IBM's major DB2 enhancements over the past few years, they revolve around UTS.

A UTS is a table space that is both segmented and partitioned. It can be defined as either partitioned-by-growth or range-partitioned. In a short post back in July 2007, I mentioned that the one drawback with UTSs is that they must contain only one table per table space.

While we DB2 for z/OS DBAs have been making the recommendation of one table per table space for years, some vendors have ignored this rule of thumb, delivering table spaces with thousands of tables in them. Having one table per table space is desirable because, among other reasons, it simplifies maintenance (backup, restore, reorg). In addition, with partitioning you not only gain the benefit of much larger table size but improved availability and access to performance-running utilities such as image copy and reorg.

At a recent Midwest Database User Group meeting in Chicago, IBM's Roger Miller presented on the topic of migrating from simple, segmented and partitioned table space to either UTS range-partitioned or UTS partition by growth table space. This will be enabled by using the ALTER table space statement, which is expected to be part of the next DB2 release (DB2 X for z/OS).

DBAs generally conduct benchmarks before and after making any structural changes, and you should, if possible, run tests in parallel on both the classic and UTS table sets in your development environment before altering table spaces in production. It's important to note that once you use either ALTER table space option (range-partitioned or partition by growth), you won't be able to convert back to your previous table space type (simple, segmented or partitioned). If you use ALTER table space, have problems and decide you want to fall back, your only option will be to unload, drop, create and reload the data.

Making ALTER table space a one-way conversion seems an odd choice, but I imagine it's because IBM wants to move forward and standardize table file structure to simplify its development processes and reduce costs. I'm sure it takes a lot of code to support simple, segmented and partitioned table spaces.

When you consider that new DB2 9 for z/OS features like clone and XML data type require UTS, it makes sense that the need to convert to UTS will grow with future DB2 releases. With ALTER table space, IBM is providing its customers with the migration path to UTS.