Rob Brooks-Bilson's Blog - Database

Querying the DB2/400 System Catalog

Mon, 10 Jul 2006 18:09:00 -0400

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:



SELECT count(*) as totalRows
FROM SYSIBM.SQLCOLUMNS
WHERE TABLE_NAME = 'yourTable'

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

JTOpen 5.1.1 Released

Wed, 03 May 2006 12:25:00 -0400

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.

JTOpen 5.0 Released

Tue, 17 Jan 2006 09:36:00 -0400

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.

Back To Basics: An SQL Gotcha

Tue, 27 Apr 2004 07:00:18 -0400

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#')

JTOpen 4.3 Released

Tue, 13 Apr 2004 08:18:12 -0400

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.

JTOpen 4.2 Is Out

Tue, 27 Jan 2004 08:21:00 -0400

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.

Anyone Connecting to DB2 on OS/390?

Mon, 12 Jan 2004 14:39:13 -0400

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.

Using the JTOpen JDBC Driver with Multiple CFMX 6.1 Instances

Wed, 10 Dec 2003 13:29:00 -0400

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.

FIX: Handling Bad Database Username/Passwords with CFMX and DB2/400

Fri, 05 Sep 2003 07:55:00 -0400

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.

JT400 4.1 Released/Back from Vacation

Mon, 18 Aug 2003 08:16:31 -0400

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.

Better Error Messages in JTOpen

Fri, 30 May 2003 12:00:00 -0400

I've often found the ODBC/JDBC error messages returned by DB2/400 to be less than helpful. While reading the JavaDoc for the JTOpen 4.0 driver today, I came across a property I hadn't noticed before. Apparently, you can instruct the driver whether to return a full or basic error description when an error occurs. Of course, the default value is basic. So, if you use JTopen with ColdFusion MX to connect to DB2 on an AS400, you can cause the driver to return the full error (often along with a fix recommendation) by adding errors=full to the JDBC URL you setup in the ColdFusion Administrator. Stay tuned for more JTOpen/CF MX tidbits as I try to figure out why DB2/400 seems to perform like an old dog when compared to the likes of SQL Server and Oracle (Solaris). If you're currently a JTOpen user, or connect ColdFusion to DB2/400 in another way, I'd love to hear your comments!

JTOpen 4.0 (DB2/400 JDBC Driver) Released

Wed, 30 Apr 2003 08:23:00 -0400

IBM just released version 4.0 of their open source JDBC (TYPE IV) driver for DB2/400. I've been using JTOpen since CFMX was in beta, and have been generally happy with the results (sans a few bugs). This release boasts dozens of fixes and improvements. I plan to install and test the new driver next week to see how it performs. If you connect to DB2/400 from CF MX, I highly recommend you check out JTOpen. You can download the latest build here: http://www-124.ibm.com/developerworks/downloads/index.php?group_id=29

JTOpen 3.3 Released

Wed, 27 Nov 2002 15:01:00 -0400

IBM recently released JTOpen 3.3, an open source JDBC driver for connecting ColdFusion to DB2/400. Believe it or not, I'm not the only person out there using DB2/400 as a backend database. Anyhow, you can download the latest version from http://oss.software.ibm.com/developerworks/opensource/jt400/downloads.html. Note that registration is required.

More on JTOpen 3.2 JDBC Driver Problem with CFMX

Sat, 28 Sep 2002 20:42:00 -0400

Well, after another day of experimenting, it looks like the empty recordset I described on 9/26/2002 is a problem with the 3.2 version of JTOpen. The issue only happens during certain complex SP operations, and shouldn't cause problems for relatively simple SPs (selects, selects with multiple joins, etc.). I've posted the problem to the JTOpen project site and will blog another update as soon as I have more info.

New Version of JTOpen (3.2) JDBC Driver Available for DB2/400

Thu, 26 Sep 2002 13:47:00 -0400

If you use CFMX to connect to DB2 on an AS400, you might be interested to know that IBM recently released a new version of the Open Source JTOpen JDBC driver. I have been using version 3.1 for a while now and it's been working fairly well (except for an issue when I run a SP that pulls back no records, the query object that gets created doesn't contain any of the column headers or the recordcount variable, but I'm not sure if this is a driver or a CF issue). I haven't tried 3.2 yet, but I'm planning to install it soon and give it a whirl. If you want JTOpen, you can get it here (registration required).