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!