Code Place is the Place for DB2 Code Samples

The International DB2 User Group (IDUG) Web site is a great place to share your experience and to get help with your DB2 questions. The IDUG is also a great place because of the Code Place.

The Code Place contains a treasure trove of downloadable sample programs and useful tools that people have freely shared with the DB2 community. Having just written about the EXPLAIN statement, I thought I'd see what free code is available to help with Explain processing.

When I entered "explain" in the title filter, it returned three entries:

• Explain comparison DB2 V8 NFM -- This application reads the DB2 Plan tables and presents the data via ISPF. It also loads Changes packages into a DB2 table for review.
   
• Customize Explain for your application -- This application allows you to customize Explain for your application.
  
• DB2 for z/OS explain compare -- This application contains a set of panels and batch jobs that report changes in access path selection based on the data in the plan table. Panels show package SQL and support the creation of HINTS.
  
These look like some great sample applications to help you use the EXPLAIN statement. And this is just one topic. IDUG Code Place features almost 150 sample applications in all. If you're looking for some help in virtually any area of DB2, login to IDUG.org and check out Code Place.

You can also share your own scripts and programs with the DB2 community by contributing to Code Place. Go here, and then select the upload option to attach your code.

Note the IDUG site has recently been redesigned. This is an obvious work-around until IDUG's Web team can find time to add an upload link to the Code Place tab. I should add that volunteers support and maintain IDUG's Web site, and that the organization is currently seeking a webmaster. If you're interested, contact IDUG through the Volunteer tab.

Thank you for considering sharing your code and or your time to the DB2 community.

Virtual Index Cost Analysis

During the process of tuning SQL statements, DBAs will use the EXPLAIN statement to determine the access path taken by DB2 to retrieve the data.

In "Explaining How DB2 Will Get Data for a Given SQL Statement," I noted that DB2 has introduced a few other tables used by EXPLAIN. One of these tables is DSN_STATEMNT_TABLE, which contains an estimated cost in service units and milliseconds to execute the explained statement. During SQL development, this helps DBAs understand the cost ramifications of choosing one access path over another.

For instance, the DBA may review an access path and determine that the cost of executing the statement is too high. In response, the DBA may decide to experiment with different ways of coding the SQL statement to improve the access path. However, the final solution may require that a new index on the table be accessed. Prior to DB2 9 the only way to verify whether creating a new index would help in such situations was to actually go through the iterative process of creating the new index and then running runstats before performing an EXPLAIN on the statement. But with DB2 9, IBM introduced the DSN_VIRTUAL_INDEXES table. (Note: This was later applied to DB2 8 through APAR PK46687 with the name DSN_VIRTUAL_INDEX).

EXPLAIN will consider any index that's defined in the DSN_VIRTUAL_INDEX table, along with any real index defined in the catalog. DSN_VIRTUAL_INDEX is meant only for use through the Optimization Expert, the Optimization Service Center (OSC) monitor that's available as an add-on. The OSC install job creates this table with a default owner of DB2OSC. When I first installed the OSC, I tried to populate and use the default installed table, DB2OSC.DSN_VIRTUAL_INDEX, but I couldn't get EXPLAIN to use this table for index design. I figured I needed the OSC, so I gave up. Later though I learned that what I actually needed was a copy of the table created with my user ID. Just as the PLAN_TABLE needs to be under your user ID, all other EXPLAIN tables, including DSN_VIRTUAL_INDEXES, follow this format.

With DSN_VIRTUAL_INDEXES, not only can you simulate the creation of an index, you can simulate a dropped index. So you can gauge the impact of dropping an index without actually deleting.

This SQL statement currently uses index SYSIBM.DSNDTX03:

     SELECT *                           
     FROM SYSIBM.SYSTABLES              
     WHERE TBCREATOR = 'XXXXXXXX'        
       AND   NAME = 'YYYYYYYYYYYYYYYYYYYYYYYYYYYY’

The sample INSERT below simulates the DROP of the existing index (SYSIBM.DSNDTX03) on table SYSIBM.SYSTABLES, which contains columns TBCREATOR and TBNAME. This insert uses existing values in the catalog as well as an override for ENABLE and MODE along with COLNOx ordering.

Remember that the column order is very important. In this example, COLNO1 is 30 and COLNO2 is 32, representing the columns TBCREATOR and TBNAME.

INSERT INTO COLTR05.DSN_VIRTUAL_INDEXES                      
 (TBCREATOR, TBNAME, IXCREATOR, IXNAME, ENABLE, MODE,        
 UNIQUERULE, COLCOUNT, CLUSTERING, NLEAF, NLEVELS, INDEXTYPE,
 PGSIZE, FIRSTKEYCARDF, FULLKEYCARDF, CLUSTERRATIOF, PADDED  
, COLNO1, ORDERING1                                          
, COLNO2, ORDERING2)                                         
SELECT TBCREATOR, TBNAME, CREATOR, NAME, 'Y', 'D',           
 UNIQUERULE, COLCOUNT, CLUSTERING, NLEAF, NLEVELS, '2', 4,   
 FIRSTKEYCARDF, FULLKEYCARDF, CLUSTERRATIOF, PADDED          
 , 30, 'A'                                                   
 , 32, 'A'                                                   
 FROM SYSIBM.SYSINDEXES                                      
 WHERE CREATOR = 'SYSIBM'                                    
   AND NAME = 'DSNDTX03'                                     
   AND TBCREATOR = 'SYSIBM'                                  
   AND TBNAME = 'SYSTABLES'                                  

For more information about virtual indexes, see the "DB2 Performance Monitoring and Tuning Guide" and the "DB2 Application Programming and SQL Guide."

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.