Rob Brooks-Bilson

DB2 stores information about database objects in what it calls the System Catalog. There are a number of views in the system catalog you can use to obtain metadata about your database such as columns, key relationships, schemas, stored procs, etc.

Today, I was working on a report where I wanted to display statistics about a number of staging files we use in our applications. I wanted to list things like number of records, number of columns, column names, etc. Things like the number of columns were easy to do just by selecting count(*) from the source table. To get the number of columns, though, I didn't want to use a hack like selecting a single record and getting the column list from that. What I wanted to do was query the system catalog. Unlike SQL Server, Oracle, and just about every other DB platform out there, documentation for DB2/400 is rather scarce.

After a lot of googling, I found a DB2/400 reference that had the information on the system catalog I needed. For those interested, the full documentation set can be found here. As IBM documentation has a tendency to disappear, here's the SYSIBM.SQLCOLUMNS library/view I used to query column information from the system catalog:

If you want to get a list of all columns, along with a lot of additional info about each column, just do a SELECT * instead of the count.

There are several other useful SQL views you can use:

SQLCOLPRIVILEGES, SQLFOREIGNKEYS, SQLPRIMARYKEYS, SQLPROCEDURECOLS, SQLPROCEDURES, SQLSCHEMAS, SQLSPECIALCOLUMNS, SQLSTATISTICS, SQLTABLEPRIVILEGES, SQLTABLES, SQLTYPEINFO, SQLUDTS

For all you ColdFusion junkies working with DB2 on iSeries/AS400's out there, IBM has just released version 5.1.1 of their open source JTOpen JDBC driver. You can download it from SourceForge.

For anyone out there using ColdFusion MX with DB2 running on an AS400, iSeries, or i5, you may be interested to know that JTOpen 5.0 has just been released. JTOpen is the open source JDBC driver and toolkit from IBM for working with both DB2 and OS/i5 / OS/400. You can download JTOpen 5.0 from Sourceforge.

I took a call from our help desk last night around midnight. One of our users in Asia had called in reporting an error in one of our applications. I checked our error logs, and sure enough there was an error - an SQL error: SQL0117 - Statement contains wrong number of values. This server happens to be an old ColdFusion server still running ODBC. We're in the process of upgrading t to MX, but that's another story. Anyhow, at first, I was puzzled by the error as we hadn't made any changes to the code in quite some time. Then it hit me. Earlier in the day, we added a new column to one of the database tables used by the application in order to facilitate the upgrade. Harmless enough, right? Wrong.

Turns out the application in question had an SQL insert that looked something like this:

INSER INTO myTable VALUES('#foo#', goo, '#boo#')

Now granted this code was written about 6 years ago, but the developer who wrote it took a shortcut that came back to bite us. Instead of explicitly declaring the fields to be inserted, he or she simply expressed the values to insert. This only works when the number of values exactly matches the number of fields in the database table. In my case, the extra field we added caused the application to break. Now granted this is something that could have been caught in testing/qa, but we didn't have that luxury in this particular instance.

The moral of the story here is that in order to future proof your application, it's best to always declare the columns you intend to insert:

INSER INTO myTable (foo, goo, boo) VALUES('#foo#', goo, '#boo#')

IBM released JTOpen 4.3 today. JTOpen is an opensource Type IV JDBC driver for accessing DB2 running on the IBM iSeries midrange computer. Version 4.3 adds several bug fixes, performance enhancements, etc.

Although this isn't really *new* (the driver was released about a month ago), I never got around to blogging it. For those of you who connect ot an AS400/iSeries with CFMX, you might be interested to know that IBM released version 4.2 of JTOpen, their open source JDBC driver for DB2/400. You can download the driver here.

A fellow ColdFusion developer is having database connectivity issues connecting CFMX 6.1 to DB2 on an IBM OS/390. I was wondering if any of you other ColdFusion developers out there were connecting to that platform and could offer up some help as far as what drivers you use, connection strings, and any other advice. Contact me via the comments or at rbils@amkor.com if you can help.

I recently setup a new development box running multiple instances of ColdFusion MX 6.1. I posted a question to the CFGURU mailing list asking about the best place to put my 3rd party JDBC driver. Sean Corfield and Simon Horwith both chimed in that they recommended placing it in:

{jrun.home}/servers/lib/

In my previous non J2EE installations of ColdFusion, I always placed my driver in c:\jt400\jtopen\bin. Whenever I setup a new server, I also had to add this location to the JAva CLASSPATH in the ColdFusion Administrator. What's my point? Well, using Sean/Simon's location, I no longer have to add the CLASSPATH to my ColdFusion server as the location is already known to the JRun/ColdFusion server. Very convenient!

Also for you iSeries fans out there, a new article by Jeremy Lyon is in this month's ColdFusion Developer Journal. The article, "MX to iSeries Demystified - A world-class database platform paired up with a world-class Web application server" gives some decent information on using CFMX to query data from DB2 on that platform.

A problem that's been plaguing a lot of us that use CF MX and IBM's JTOpen JDBC driver to connect to DB2 running on IBM's iSeries (AS400) has to do with CF MX hanging when a bad username or password is passed to the database. It's really frustrating on so many levels, especially since the only way to free the hang is to restart the CFMX service. Sometimes even this isn't enough, and the server must eb rebooted. Obviously, this isn't good in a production environment.

Jeremy Lyon emailed me today to let me know that there's a JDBC URL parameter that you can set to fix this problem. Basically, there's a parameter called prompt. The JavaDoc definition for prompt is:

"Specifies whether the user should be prompted if a user name or password is needed to connect to the server. If a connection can not be made without prompting the user, and this property is set to "false", then an attempt to connect will fail."

What this means is that by default, prompt is set to True. If you pass a bad username or password, the system attempts to "prompt" the user for a username/password. Since you aren't using an interactive application to make your connection, you never see this prompt, and the system "hangs" indefinitely.

The fix is to ALWAYS specify prompt=false in your JDBC url. This way, id a bad username/password is passed, the JDBC driver will report this back to ColdFuion, and an exception can be thrown.

After a nice long week off, I'm back from vacation. Man, I can't wait for retirement ;-) Of more important note is the release of JTOpen 4.1 by IBM (an open source JDBC driver for iSeries [AS400] DB2). This is basically a maintenance release that fixes all sorts of minor issues, as well as adds some enhancements to the driver. You can download it from IBM. I've already installed it on two machines, and it's been working for a few hours on a rather large application with no apparent issues.