Network World: Mainframes Still Relevant

Just finished reading a great new article at NetworkWorld.com called Working on mainframes not just for old fogies. Well, that is sure good to know. I may be getting older, but I sure don't think of myself as one of those "old fogies" yet... and I still work on mainframes.

The article talks about the continuing relevance of mainframe computing in a novel way -- by talking to several young mainframe newbies. Yes, there are twenty-somethings out there who are working on mainframes, they are just hard to find. And the article makes the point that all of us in the mainframe world know -- we need more young 'uns to learn the mainframe.

The article goes on to point out some interesting mainframe statistics from the industry analysts. According to Gartner "large mainframe users have been increasing their mainframe environments for years." They say that installed MIPs will continue to gorw at a CAGR of 15 to 20 percent through 2009. And the analysts at IDC seem to agree, with 2006 MIPS shipments up 14.2 percent.

If you work on mainframes be sure to click over and give the article a read. To me, anyway, it is invigorating to hear about young people embracing the mainframe. And the more younger people who learn about mainframe computing, the stronger the platform becomes... and that is good, too.

IBM Software Support Toolbar

It seems like everyone under the sun is offering a toolbar to add in to the Internet Explorer browser. Google has one, Yahoo has one, and so do countless others. Now, IBM has one, too.

The new IBM Software Support Toolbar plugs into your browser to allow you to search IBM's Support knowledge base using keywords, specific error codes or exact phrases. You can also use it to search or browse for product downloads including Fix Packs, Updates, Patches, etc. So if you have trouble remembering how to get to the IBM site for support, or just want a quicker way to get there, the IBM Software Support Toolbar could prove itself to be quite helpful.

The toolbar even allows you to seek specific results for a specific brand. So you can scroll to the "Information Management" brand and select from that sub-menu. That way you'd only be looking for the "database stuff"...

How and when to use DB2 scrollable cursors

As I've mentioned here before, sometimes I will use this blog to post answers to questions that have been sent to me. The question being answered today, is this: I have a select query that returns 1000 rows and I want to display the rows from 200 to 500. Could you please let me how to code after the where clause? I know how to get the first 100 rows using -- FETCH FIRST 100 rows only clause.

If you are using DB2 V7 or higher, consider using scrollable cursors. With scrollable cursors, you can move directly to the rows you want without having to FETCH every other row returned by the cursor.

In DB2 V7, scrollable cursors require you to use declared temporary tables, another new feature of DB2 Version 7. If you are using DB2 V8, dynamic scrollable cursors are available and thus temprorary tables are not required.

In V7, DB2 uses a declared temporary table to hold and maintain the data returned by a scrollable cursor. Scrollable cursors allow developers to move through the results of a query in multiple ways. The following key words are supported when fetching data from a scrollable cursor:

• NEXT - will FETCH the next row, the same way that the pre-V7
• FETCH statement functioned
• PRIOR - will FETCH the previous row
• FIRST - will FETCH the first row in the results set
• LAST - will FETCH the last row in the results set
• CURRENT - will re-FETCH the current row from the result set
• BEFORE - positions the cursor before the first row of the results set
• AFTER - positions the cursor after the last row of the results set
• ABSOLUTE n - will FETCH the row that is n rows away from the first row in the results set
• RELATIVE n - will FETCH the row that is n rows away from the last row fetched

For both ABSOLUTE and RELATIVE, the number n must be an integer. It can be either a positive or a negative number and it can be represented as a numeric constant or as a host variable. All of the FETCH options for scrollable cursors also reposition the cursor before fetching the data. For example, let's consider your problem of a cursor that returns 1000 rows, but you only want rows 200 through 500.

Consider the following cursor logic:

DECLARE csr1 SENSITIVE STATIC SCROLL CURSOR
FOR SELECT FIRSTNAME, LASTNME
FROM DSN8710.EMP
ORDER BY LASTNME
FETCH FIRST 1000 ROWS ONLY;

OPEN csr1;

FETCH ABSOLUTE 200 csr1 INTO :FN, :LN;

I used the FETCH FIRST 1000 ROWS ONLY clause to ensure that no more than 1,000 rows were returned. This clause is, of course, optional (and if not specified, DB2 will not limit the result set returned by the cursor). Then I open the cursor and FETCH row 200. This positions the cursor just after the 200 result row that was just fetched. After that, all you would need would be to create a loop that just issues FETCH NEXT 300 times and that would retrieve only rows 200 through 500.

Basically, scrollable cursors reduce the amount of time and effort required to move backward and forward through the results of SQL queries. But as helpful as scrollable cursors are, do not make every cursor a scrollable cursor. Scrollable cursors require substantially more overhead than a traditional, non-scrollable cursor. Analyze the requirements of your applications and deploy scrollable cursors only where it makes sense to do so. Also, be sure to discuss this with your DBAs before implementing as there will probably be some setup work required of the DBA group to facilitate this solution.

Good luck...

Free DB2 Statistics Health Check Software

NEON Enterprise Software SEGUS, Inc. is offering free software for checking the health of the statistics in your DB2 Catalog.

Statistics HealthCheck for DB2 z/OS enables you to quickly and effectively analyze and judge the quality of your DB2 Catalog statistics. Checking the health of your DB2 subsystem is especially important considering the heightened sensitivity of DB2 V8 and V9 to bad statistics.

The extensive rule system used by Statistics HealthCheck is based on IBM’s own recommendations for maintaining good statistics. Using a violation system, Statistics HealthCheck pinpoints precisely those objects that could benefit from a RUNSTATS utility health check—or that otherwise require statistics housekeeping.

Statistics HealthCheck is particularly useful as a prerequisite to Bind ImpactExpert during a V8 migration. First, Statistics HealthCheck identifies the RUNSTATs you need. Then, Bind ImpactExpert provides REBIND insurance to further guarantee consistent and improved access paths.

With Statistics HealthCheck, you know exactly what is “wrong” with your statistics so that you can proactively correct any problems that might negatively affect DB2 subsystem performance.

And come on, it is free after all. What do you have to lose by downloading it and trying it on your DB2 subsystems today?

--------------

Just a quick note to explain that since the links in this blog posting were no longer valid, I modified them. This offering is now being provided by SEGUS, and it is still a free download. The statistics health check software can be downloaded at the following link:

http://www.segus.com/index.php/productdetails/index/en_product_014_Statistics_HealthCheck

New Red Paper on DB2 and Storage

Disk storage has changed rapidly over the past few years with the delivery of new functions and improved performance. DB2 has made many changes to keep pace and make use of the disk improvements. In fact, there is a new "red paper" that outlines these changes titled Disk storage access with DB2 for z/OS.

A red paper is similar to a redbook, but it is shorter (in length) and focuses on a more limited subject matter. They are kind of in between a white paper and a redbook. But they are still free to download - and they are easier to digest quickly than a redbook.

If you don't know what a redbook is, check out my earlier blog posting on the subject: Can You Write a Redbook?

"Messages & Codes" Now "Messages" and "Codes"

Has anyone noticed? What used to be a single IBM DB2 manual is now two separate manuals. IBM has split the Messages & Codes manual into one manual for Messages and a separate manual for Codes. The links in the previous sentence take you directly to the web versions of the manuals. If you prefer to download PDF documents, use this link for DB2 V8.

So, if you are looking for the meaning of a DSN message you would use the Messages manual; if you are looking for a SQLCODE or SQLSTATE, use the Codes manual. I kinda liked them both in one manual, but I guess the combined manual was getting a bit too big to manage...

Also, if you haven't used it already you might want to become familiar with LookAt. LookAt is an online facility for displaying explanations for most IBM messages, as well as for some system abends and codes.

You can use LookAt on the web at: www.ibm.com/eserver/zseries/zos/bkserv/lookat/

Or, you can use it from anywhere you can access a TSO/E command line. Of course, to use LookAt as a TSO/E command, LookAt must first be installed on your system. You can get the LookAt code via ftp at ftp.software.ibm.com/ps/products/ibmreader/tools/lookat/ZOS/

Free DB2 Webinars

Well, everyone in the US should be recovered from the 4th of July holiday by now and be back to work... unless you took a vacation, in which case, you'll be reading this later. It rained here in Texas on the 4th, but that didn't stop the barbecues. Hope your 4th was relaxing (and for my international readers, I hope you'll forgive the brief discussion of a US holiday)...

Anyway, I wanted to take this opportunity during this holiday week to point you to a couple of pre-recorded webinars that I conducted earlier this year. Both are available to be streamed free-of-charge over the Internet.

The first one is titled Managing Common DB2 Performance Problems . In it I discuss some of the more common performance issues with DB2 -- and I also offer some guidance on how to manage DB2 performance at your site. Every DBA and data management professional knows that database performance is an on-going mission, and if you pick up tip or two in this session your time will be well spent.

The second webinar is titled Using Real Time Statistics to Improve DB2 Administration. Even though the Real Time Statistics (RTS) feature was delivered in DB2 Version 7, the number of organizations using them is still slim. This is unfortunate because RTS can be extremely beneficial in terms of analyzing and automating your DB2 maintenance tasks. This webcast offers an overview of RTS, including discussions on implementing them, integrating them with traditional statistics, and using them to help automate utility processing.

These webinars were sponsored by NEON Enterprise Software - and they offer some good tools for managing DB2 performance, automating DB2 administration, and managing changing DB2 access paths and BINDs.

Finally, you might want to hear what Roger Miller has to say about the next version of DB2 for the mainframe -- DB2 for z/OS Version 9. You can listen to a free replay of his webinar outlining this new version here. This webcast talks about many of the new features of the upcoming V9 release of DB2 for z/OS and it offers a great opportunity to learn from Roger - that jack of all trades and master of several...


NOTE
As of late 2011, the webinars referenced in this blog post are no longer available for replay.