Rob Brooks-Bilson's Blog - DB2

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.

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.

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!