DB2 Versions, Service, and Such

Keeping up-to-date with the latest and greatest DB2 versions and functionality can be a time-consuming task. Every 18 to 36 months IBM announces a new version of DB2 with even more features and functionality than ever before.

DB2 will soon celebrate its 25th anniversary. The basis for DB2 began with IBM’s System R research project. In 1982, IBM delivered SQL/DS on VM and VSE, and then a year later in 1983, IBM released DB2 for MVS Version 1. Through subsequent versions and releases IBM has improved the functionality and performance of DB2.

Functionality aside, though, it can be difficult to keep track of new DB2 versions. Questions like “What version has which feature?”, “How much longer will IBM support the DB2 version we are running?”, and “When should we begin to migrate to a new versions… and which one?” are very common in this day and age of rapid technology change. Let’s examine some of these questions.

First of all, we need to understand some basic terminology: “withdrawal from marketing” and “withdrawal from service.” When IBM withdraws a product from marketing the product will no longer be advertised or sold by IBM; but IBM will continue to service and support customers. When IBM withdraws a product from service you will no longer be able to get technical support for that product… unless you negotiate extended service (at an extended price) with IBM.

So, the current version of DB2 for z/OS is Version 8 and it has been available for just over 2 years now. And it seems that the announcement of the next version of DB2 is imminent, what with some of the Vnext presentations IBMers are delivering.

But the big question these days, for most shops, is not the next version, but the current version. Most DB2 users are still running Version 7, but they will need to migrate to Version 8 soon. If you are running an earlier version of DB2 (than V7) you really should waste no time in getting to V7 – not only is it a solid release, but you’ll need to start worrying about V8 soon because the projected end of service date for DB2 Version 7 is September 2007.

You can keep an eye on the current versions of DB2 that are available by bookmarking this link http://www.ibm.com/software/data/db2/zos/support/plc/. IBM keeps this information up-to-date regarding the end of marketing and end of service dates for DB2 versions.

The bottom line, of course, is that more functionality is available to you by keeping up-to-date with the latest DB2 version. But issues such as rapid versioning, complexity, difficulty of migration, and managing new versions can make keeping up-to-date difficult. And diligence is required to keep everything straight. At least we have a lot of information available to us:

• The IBM DB2 UDB for z/OS Home Page
• DB2 UDB for z/OS Information Roadmap
• More information on DB2 V7 end of service
• IBM Pitch on V8 Migration and DB2 Vnext (pdf)
• z/OS, z/OS.e, and OS/390 marketing and service announce, availability, and withdrawal dates

Good luck with DB2…

Returning Only Numeric Data

I frequently get e-mail from folks asking about ways to accomplish things in DB2 and SQL. A recent question I got went something like this:

Is there any option to check whether data “IS NUMERIC” in a DB2 table? We want to examine CHAR data but return only those where the entire data consists only of numbers. For example, can we write a query like this?

SELECT *
FROM TABLENAME
WHERE VAR IS NUMERIC.

The VAR variable is defined as a CHAR(5) column and it will contain data like below.
123aa
2234a
34256
32102
add91

Out of the above 5 records we would want only the 3rd and 4th records to be returned. We tried CAST (VAR as integer), but any other option is there for fetching like above. Please explain

Well, if you try to cast non-numeric data to numeric you will get an error. But you can test the data beforehand - digit by digit - using the SUBSTR function. You’d have to break the VAR column down using SUBSTR to get each individual character and test whether that character is between 0 and 9 – then only if all characters are between 0 and 9 would the result be returned.

Here is what the SQL might look like:

SELECT *
FROM TABLENAME
WHERE SUBSTRING(VAR,1,1) BETWEEN '0' AND '9'
AND SUBSTRING(VAR,2,1) BETWEEN '0' AND '9'
AND SUBSTRING(VAR,3,1) BETWEEN '0' AND '9'
AND SUBSTRING(VAR,4,1) BETWEEN '0' AND '9'
AND SUBSTRING(VAR,5,1) BETWEEN '0' AND '9';

This will return only those rows where every digit in the VAR column is a number between zero and nine.

SHARE in Seattle, WA - Week of March 5, 2006

Just a quick post to promote the upcoming SHARE conference in Seattle, WA. For those who don't know, SHARE Inc. is an independent, volunteer run association providing IBM customers with user focused education, professional networking, and industry influence. SHARE is the longest-running, functional computer user group having been founded in 1955, just two years after the release of IBM's first computer.

SHARE holds two conferences annually, one in the spring and one in the fall. And I regularly attend and speak at each conference. The next conference is coming up next week, the week of March 5th 2006. I am scheduled to speak at SHARE in Seattle on Monday (3/6) and Tuesday (3/7). The two presentations I'll be delivering are An Hour of DB2 for z/OS Tips and Techniques and A DB2 for z/OS Performance Tuning Roadmap.

If you plan to be in Seattle, stop by and say "Hello."

DB2 UDB for z/OS Version 8 Performance Topics

Now that many sites are migrating to DB2 V8 performance topics related to this new version are gaining popularity. Although there have been a large number of improvements made to DB2 in terms of scalability, application porting, security, and continuous availability, there are also some things you'll need to prepare your systems for.

Luckily, IBM has just updated its very useful redbook titled DB2 UDB for z/OS Version 8 Performance Topics (SG24-6465). This redbook was initially published in April 2005, but was updated with new V8 performance information and details in late February 2006. So, if you haven't picked this title up yet, now would be a good time to download and peruse it. And if you have already reviewed it, you might want to download the updated version.

The redbook gives guidance on SQL performance, subsystem performance, availability and capacity enhancements, utility performance, networking improvements, data sharing enhancements, installation and migration information, and coverage of IBM's performance tools for DB2.

If you have already migrated to V8, or are planning a migration soon, then DB2 UDB for z/OS Version 8 Performance Topics should definitely be on your reading list.

Can You Write a Redbook?

If you've been working with mainframes for any period of time you have almost certainly become familiar with the IBM redbook. These are supplemental manuals that IBM writes and distributes free-of-charge. They can be on just about any technical topic.

Well, when I said IBM writes them, I should have been more clear. IBM guides the writing of them and solicits its customers to help out. It does this via residencies. A residency is an intensive, multi-week work effort where small teams explore and document product implementation, integration and operations. Each team is directed by an IBMer from their technical publications group. The team will consist of professionals from IBM field and development, from IBM Business Partners, from customers, and from the marketplace in general. So, you too, can research and write an IBM redbook!

Of course, you have to make sure that you have the expertise, willingness, and time to work on the project.

For a list of available residencies, click here:
http://www.redbooks.ibm.com/residents.nsf/ResIndex/

And if you are a DB2 for z/OS practitioner (as most readers of this blog should be) there is a very interesting residency available Powering SOA with DB2 for z/OS.
The redbook is going to depict how DB2 is SOA-enabled to provide web services. Topics to be covered will include:

• the Service Oriented Architecture,
• DB2 z/OS Web Services support,
• Web Services operations,
• Security, Provider and Consumer scenarios,
• SOA runtime environments to access DB2 for z/OS,
• Service Data Objects, and
• XML functions.

So, if you know any of this, are interested in learning how to develop web services for DB2 for z/OS, and have always wanted to help write one of those redbooks, click below and read how to participate:
http://publib-b.boulder.ibm.com/residents.nsf/IntNumber/SD-6Z39-R01?OpenDocument

VSAM Demystified

VSAM is used "under the covers" of every DB2 page set. VSAM is also used as the storage mechanism by some IMS databases. It is also used to store a lot of mainframe data outside the control of any DBMS. But there is a lot of confusion about what exactly VSAM is, how it can be used, and how it differs from a DBMS.

I wrote an article for IDUG Solutions Journal titled On VSAM and DB2 that offers some high-level details on the differences between the two. But if you are looking for a great, in-depth publication on VSAM there is a great redbook you should check out with the same name as this blog entry: VSAM Demystified.

This book is intended to be used as an initial point of reference for VSAM functions. It is sure a lot easier starting here than wading through the many IBM manuals on the subject. And it is less costly than some of the great books on VSAM out there. (For those those interested in some good VSAM books for further research consider these: VSAM Access Method Services by James Martin, VSAM by Doug Lowe, and VSAM for the COBOL Programmer also by Doug Lowe).

Anyway, back to the IBM redbook: this book helps to demystify VSAM and gives you the information necessary to understand, evaluate, and use VSAM properly. It covers VSAM basics, performance issues, assists with VSAM problem determination, VSAM recovery, gives guidance on managing VSAM data sets, and even discusses issues like VSAM Record Level Sharing and DFSMStvs.

If you are looking for a concise, useful, and cost-effective resource to bolster your VSAM knowledge, you should download and read VSAM Demystified today!

Evaluating DB2 Tool Vendors

Although the most important aspect of DB2 tool selection is functionality and the way it satisfies your organization’s needs, the nature and stability of the vendor that provides the product is also important. So, of course, you will need to be sure that the tool you are evaluating meets your functional specifications.

And in this day and age you should really do the investigative work required to find out the real level of support for DB2 V8 that is in the GA version of the tool. Most vendors have implemented V8 in stages, so be sure the features you plan to use first are supported within the tool.

Moreover, you should also investigate the vendor offering the tool (or tools) under consideration. Keep in mind that older does not always mean better -- and newer does not always mean more innovative. It depends (doesn't it always). You need to investigate the vendors thoroughly and the following list of questions can help:

1. How long has the vendor been in business?

2. How long has the vendor been supplying DB2 tools?

3. Does your company have other tools from this vendor? How satisfied are the users of those tools?

4. Are other organizations satisfied with the tool you are selecting? Obtain a list of other organizations that use the same tool and contact several of them.

5. Does the vendor provide a 24-hour support number? If not, what are its hours of operation?

6. Does the vendor have a toll-free number? If not, how far away is the company from your site? You want to avoid accumulating long-distance charges when you are requesting customer support from a vendor.

7. Does the vendor provide Web support? The Web support should be in addition to phone support, not a replacement.

8. Evaluate the response of the technical support number. Call the number with technical questions at least four times throughout the day: before 8 a.m., around noon, just before 5 p.m., and again after 9 p.m. These are the times when you could find problems with the level of support provided by the vendor. Was the phone busy? Were you put on hold? If so, for how long? When you got a response, was it accurate and friendly? Did the person who answered the phone have enough technical knowledge to be useful?

9. How knowledgeable are the technical support representatives who answer your test calls? Do they know their products inside and out, or do they struggle? Do they know DB2 well (such as a former DBA) or are they unseasoned?

10. Will the vendor answer DB2 questions free of charge in addition to questions about its product? Sometimes vendors will, but they don’t advertise the fact. Try it out by calling the technical support number.

11. Does the vendor provide a newsletter? How technical is it? Does it provide information on DB2 and the vendor's tools or just on the vendor's tools? Is it printed and mailed, e-mailed, or available over the web?

12. Does this vendor supply other DB2 tools your organization might need later? If so, are they functionally integrated with this one? Does the vendor supply a full suite of DB2 products or just a few?

13. Does the vendor integrate its tools with other tools? For example, can a product that analyzes databases to determine whether a REORG is required integrate with your shop's job scheduler?

14. Does the vendor provide training? Is it onsite training? DB2 training and product training?

15. Are installation, technical, and user manuals provided free of charge? Are the manuals available in both hard and soft copy? Will the vendor deliver additional documentation or error-resolution information by overnight mail? e-mail? fax?

16. How are software fixes provided? Electronically? By tape? On the Web? Is a complete reinstallation required? Are fixes typically accomplished using zaps? Does the vendor support SMP/E?

17. How many man hours, on a short notice, is the vendor willing to spend to solve problems? Is there a guaranteed time limit?

18. Is the vendor willing to send a representative to your site to do a tailored product presentation? How knowledgeable is the rep?

19. Is the vendor an IBM business partner? How soon will the vendor's tools be modified to support new DB2 releases and versions? Does the vendor participate in IBM's Early Ship Program (ESP) for new DB2 versions and releases?

20. Have the vendor's tools been recently reviewed or highlighted in industry publications? If so, read the articles.

21. Have the vendor's tools been assessed by industry analyst groups (e.g. Gartner, Forrester, etc.)? If so, read the reviews.

22. Will the vendor assist in developing a cost justification? Most tool vendors are more than willing to provide cost justification to help you sell upper management on the need for the tool.

23. Does the vendor provide sample JCL to run its product? Can any needed JCL be automatically generated by the product? Are templates provided to tweak the JCL to your shop standards?

24. Does the vendor charge an upgrade fee when the processor is upgraded? How flexible are the contract terms and conditions? Do they offer usage-based licensing? Other terms?

26. What guarantees are available from the vendor against it being sold or going out of business? Will the vendor supply the source code for the tool, or perhaps offer a money-back guarantee?

27. Is the vendor willing to set a ceiling for increases in annual maintenance charges?

28. Does the vendor supply DBA tools for other DBMSes used at your shop? Can the same tool, using the same interface, be used to manage multiple databases across multiple operating systems?

29. How does the vendor rank enhancement requests?

30. What is the status of the vendor? Have recent business down turns resulted in lower market share? If so, what is the company doing to regain its position?

31. Did the company recently undergo a layoff? What is the retention rate of their development and support staff?

32. Are there any outstanding lawsuits? Have recent events resulted in downsizing? What are their plans to reverse this trend?

Use these questions to provide a basis for evaluating DB2 tool vendors. You can judge for yourself which criteria are most important to your organization.

Adding Column Names to an Unload File

I received an e-mail from a reader asking an interesting question. She wanted to know if any of the DB2 unload utilities are able to include the column names in the same file as the unload output data. This was a requirement because one of the applications her company was planning to use takes the column headings and dynamically creates tables.

My initial reaction was that she could write a program that take the output file and reads the system catalog to gather the appropriate column names. That would work, but she actually came up with a better solution.

She used a third party Unload utility (but it would also have worked with IBM's Unload, too) to perform two SELECT statements. The first just creates the column headers and the second for the actual data. The column and data goes to two separate datasets, but they used IDCAMS to concatenate the 2 separate column/data sets into 1 dataset.

The SQL is simple, something like as follows:

SELECT 'COLUMN NAME 1', 'COLUMN NAME 2',
'COLUMN NAME 3', 'COLUMN NAME 4'
FROM SYSIBM.SYSDUMMY1

SELECT COL1, COL2, COL3, COL4
FROM XXXXXX.TABLE1

Of course, you'd just plug in the correct column names in the literals of the first SELECT statement -- and the correct column names in the second.

My DB2 Articles

I've written a lot about DB2 over the years and I try to keep most everything I've written up and available over the web. If you are ever interested in finding a DB2 article of mine that you've read but can't find try the following link:

http://www.craigsmullins.com/art_db2.htm

That page contains links to all of the DB2 articles that I've written. It is in reverse chronological order...

Design Guidelines for High Performance and Availability

Just a short blog entry this week to alert everyone to a newly published IBM redbook titled "DB2 UDB for z/OS: Design Guidelines for High Performance and Availability." This redbook is an essential read for anyone who is interested in squeezing the highest levels of availability and optimum performance from a mainframe DB2 application.

In just under 500 pages the authors share a wealth of tuning techniques that all DB2 shops should have access to. Download the manual (SG24-7134) for free from the web by clicking here.

Basic DB2 Buffering and Memory Guidelines

One of the most important areas for tuning DB2 subsystem performance is memory usage. DB2 for z/OS uses memory for buffer pools, the EDM pool, RID pool and sort pools to cache data and structures in memory. The better memory is allocated to these structures, the better DB2 will perform.

When allocating DB2 buffer pools, keep these rules of thumb in mind:

• Don't allocate everything to a single buffer pool (e.g., BP0); use a multiple buffer pool strategy.
• Explicitly specify a buffer pool for every table space and index.
• Isolate the DB2 Catalog in BP0; put user and application DB2 objects into other buffer pools.
• Consider separating indexes from table spaces with each in their own dedicated buffer pools.
• Consider isolating heavily hit data into its own buffer pool to better control performance.
• Consider isolating sorts into a single buffer pool and tuning for mostly sequential access (e.g. BP7).
• Consider separating DB2 objects into separate buffer pools that have been configured for sequential verses random access.

Forget about trying to follow a cookie-cutter approach to buffer pool management. Every shop must create and optimize a buffer pool strategy for its own data and application mix. DB2 offers the following buffer pool tuning "knobs" that can be used to configure virutal buffer pools to the type of processing they support:

DWQT –this value is the deferred write threshold; it is expressed as a percentage of the virtual buffer pool that might be occupied by unavailable pages. When this threshold is reached DB2 will start to schedule write I/Os to externalize data. The default is 50%, which is likely to be too high for most shops.

VDWQT – this value is the vertical deferred write threshold; it is basically the same as DWQT, but for individual data sets. The default is 10%, which once again is quite likely to be too high for many shops.

VPSEQT – this value is the sequential steal threshold; it is a expressed as a percentage of the virtual buffer pool that can be occupied by sequentially accessed pages. Tune buffer pools for sequential access such as scans and sorting by modifying VPSEQT to a larger value. The default is 80%.

VPPSEQT – this value is the sequential steal threshold for parallel operations; the default value is 50%.

VPXPSEQT – this value is assisting parallel sequential threshold; it is basically the VPPSEQT for opertaions from another DB2 subsystem in the data sharing group.

These parameters can be changed using the ALTER BUFFERPOOL command. Additionally, prior to DB2 V8, hiperpools can be created to back up DB2 virtual buffer pools with additional memory. DB2 provides several tuning knobs for hiperpools, too, including HPSIZE to adjust the size of hiperpools and HPSEQT to adjust the hiperpool sequential steal threshold. Hiperpools are obsolete as of V8 though, so if you don't use them today you should probably spend your time migrating to V8 instead of implementing soon-to-be-obsolete hiperpools.

With the advent of DB2 V8, there is more memory at your disposal for DB2's use. V8 is able to surmount the limitation of 2GB real storage that was imposed due to S/390's 31-bit addressing. Theoretically, with 64-bit addressing DB2 could have up to 16 exabytes of virtual storage addressability to be used by a single DB2 address space. Now there is some room for growth!

In addition to buffer pools, DB2 uses memory for the EDM pool. The EDM pool is used for caching internal structures used by DB2 programs. This includes DBDs, SKCTs, CTs, SKPTs, and PTs. It also includes the authorization cache for plans and packages, as well as the cache for dynamic SQL mini-plans.

With V8, DB2 breaks the EDM pool into separate pools: one for DBDs, one for the dynamic statement cache, and the final one for program elements (CTs, SKCTs, PTs, SKPTs).

As a general rule of thumb, shoot for an 80 percent hit rate with the EDM pool; this means that only one out every five times should a structure need to be loaded from disk into the EDM pool.

Finally, remember that buffer and EDM pool tuning are in-depth subjects that cannot be adequately covered in a high-level blog entry such as this. So, study those IBM DB2 manuals - and learn by doing. Additionally, there is much more to proper DB2 system performance tuning than memory tuning. Other system elements requiring attention include allied agent setup (CICS, TSO, etc.), network configuration, locking, logging, and Parallel Sysplex configuration and management for DB2 data-sharing shops.