January 14, 2008

Keeping Pace with Changes in DB2

If you’ve been in IT for any length of time you know that change is constant. You must regularly make improvements to the systems you develop and support. On top of that, the tools you use are also changing. As a DB2 for z/OS specialist, it's very important for me to stay up on the latest in DB2 functions and features to ensure that I can provide good advice to the developers I support as well as the DB2 community in general.

A developer I work with told me that he'd wished there was a DVD containing all of the z/OS manuals. To my delight, I've found that there is one: the "z/OS V1R9 and Software Products DVD Collection Online Library" (SK3T-4271). With this tool, I can search across all the manuals and find what I’m looking for in either PDF or IBM soft-copy book format. You can subscribe to this product and receive regular updates, or you can do what I did and place one order for a single copy. (I figure the manuals don’t change that much.)

Some weeks back one of the developers received SQL code -20186. I checked my copy of the Codes manual (dated September 2007) and couldn’t find it. I don’t know why but I checked IBM's Web site before opening a problem ticket with IBM support. (A PDF copy of the manuals is available here.)

I noticed, to my surprise, that the PDF version of Codes manual was dated October 2007. IBM had issued updates just a month after my DVD was published. And, sure enough, the October 2007 manual contained the SQL code the developer received. Having this text was critical to figuring out what was wrong with the SQL statement. And having access to current manuals helped the developer quickly resolve the problem -- not to mention saving the time and money that would have been expended with IBM support.

If you develop programs and need to know the latest SQL options or the meanings of error codes, you should download these DB2 manuals:

  • "SQL Reference Guide"
  • "Messages"
  • "Codes"
  • "Application Programming and SQL Guide"
  • "What’s New"

And if you're an application DBA, you should have, at minimum, the five aforementioned developer manuals, plus:

  • "Command Reference"
  • "Utility Guide and Reference"

The DVD I mentioned (SK3T-4271 contains all z/OS product manuals, which may be more than you need. If you only need a DVD copy of the DB2 manuals, order the "DB2 Version 9.1 for z/OS Licensed Library Collection" (LK3T-7195-01). If you buy a single copy rather than a subscription, check the IBM Web site every few months for new versions of the manuals. The site features "update" link displaying recent changes to the manual that haven't made it into the published documentation. You can also access this information here.

I ended up downloading the October 2007 PDF. Then I noticed the update link and clicked on it. Below is just one entry of changes that didn't make it into the October publication. So much for manuals not changing much!


Topic: Modify SQLCODE -20305

Date of change: October 2007

Change description: Modify the following SQLCODE in the Codes manual:



Explanation: The insertion or update of an XML value failed during insertion or update of the specified index. The statement cannot proceed and the table and index remain unchanged. The schema data type and schema identifier are not applicable for all reason codes. See specific reason codes for details. The error occurred because of one of the following possible reason codes:


One or more XML node values in an XML value being inserted or updated exceeds a length constraint imposed by the identified index. The index on an XML column is defined to use the SQL type VARCHAR with a specific length specified. The length of one or more XMLPATTERN result values exceeds the user specified length constraint for the VARCHAR data type.



Please see the recent DB2 information update page for a complete copy of all the changes to the codes manual.

If you're developing applications that take advantage of new technology or advanced features in DB2, Java and XML, you'll benefit from keeping up with updates to the documentation.