Products
Rob Brooks-Bilson's Blog
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!
Aura skin for Raymond Camden's BlogCFC inspired by Joe Rinehart & Steven Erat. This blog is running version 5.9.004.
MX Updater 3 and JTOpen 4.0 seem to have improved query performance significantly. In some cases by 100% or more.
I've had this problem in the past as well (even when we used Client Access for ODBC connectivity). I know Macromedia is aware of it and looking into what they might be able to do on their end in an updater for CF MX.
Overall, how do you find query performance on your AS400? My experience has been that it's very slow when you compare it to SQL Server or Oracle. I've been running tests here comparing two different AS400s to other DB platforms, and my early tests show DB2/400 to be an order of magnitude slower (10x), on average! This is all on V4R5, so when we upgrade to V5R2 soon, I'm going to rerun the tests for the new version.
DB2/400 for me is pretty fast over MX. We do a fair amount of tuning on the 400 to assure that jdbc connectivity is given adaquate resources. I have found that tuning is important if you have a big interactive load.
The datasets I have benchmarked are relitively small. My <CFQUERY> results are about 1000 to 1500 records with a few simple joins tossed in. Record length of about 1k. I can get these queries down to the 300 - 400 ms range.
I am running a 740 12 way with about 20GB of main store. The OS is V5R1. The MX server is a Dell 1550 dual 1.13MHz with 2GB memory and raided 10k 18GB drives.
If you are running less than 800 series hardware the 100Mb ethernet card is actually running closer to 33Mb. The bus speed is the limiting factor.
DB2 for me is plenty fast. Most of the problems I have encountered are with all the moving parts in between.
Jeremy
Please bear in mind that I’ve never worked with JDBC before and don’t even know what a CLASS PATH is. Also when we tried to install some other drivers we were asked to “create package” on AS400 and my database administrator is not sure how to do that. Please help!!
The driver that comes with CF MX 6.1 will only work with V5R2, and even then, none of us have been able to get it working yet. So, your next best option (and it may be better than the DataDirect driver anyhow) is JTOpen. Version 4.0 is out now, and you can download it <a href="http://www-124.ibm.com/developerworks/projects/jt4...;.
As for what you need to do to install it, here are the simple steps:
1. After downloading the JTOpen driver, unzip it into a new directory on your server. I like to unzip mine into a directory called jtopen, off of my root drive (c:\jtopen).
2. Open the CF Administrator and go to the Java and JVM section. Enter the following in the Class Path box:
c:/jt400/lib/jt400.jar
Note the forward slashes. This is important for upgrading your version of CF and having your classpath successfully migrated at a later date. You'll need to stop and restart your ColdFusion Aplication Server service for the changes to the class path to take effect.
3. Go to the data sources section.
4. Enter a name for your new data source, and choose the "Other" driver from the dropdown box. Click submit.
5. For the JDBC URL, enter something like this:
jdbc:as400://your_as400_name_here/;libraries=,your_lib1,your_lib2;transaction isolation=none;naming=sql
There are several parameters you can use here for various things. For a complete list, see the JavaDoc that installs with JTOpen.
6. For both the Driver Class and Driver name, enter the following:
com.ibm.as400.access.AS400JDBCDriver
7. Be sure to use a valid username and password. If you don't, it will basically hang the connection to your data source (this is a bug).
Creating a package is an AS400 thing. I've never done it, so unfortunately, I can't help you there.
If you have any other questions, let me know.
"7. Be sure to use a valid username and password. If you don't, it will basically hang the connection to your data source (this is a bug)."
To prevent this put "prompt=false" in your connection URL. By default it is set to true and this will choke your MX service when the AS/400 attempts to prompt it for authentication.
Took me a year to find this little nugget.
Jeremy
Thank you VERY VERY much for your help. It worked like a charm. Thank you!
I´m trying to connect CFMX 6.1 with DB2/390.
We are using the ODBC CONNECT, and we successfully connected the datasource, but sometimes it hangs the Jdbc sercvice and we need to restart the machine (the service wont restart).
Can I use this driver with 390 ? Will it work ?
Could this be another problem ?
Regards,
Marcantonio Silva
I'm glad you found the tip useful.
I know it has been a while since this topic has been covered, but I am having some major speed issues with my companies as400 connection to CF8.
I was wondering if there was a 'best' setup for the driver connection. currently we are using the ODBC 'web client' connection to connect to our iSeries 810(?) box.
All help will be GREATLY appreciated.
Can you find out what OS version of OS400 you're running (it will look something like V5r4).
Beyond that, you really should move from ODBC to the JTOpen JDBC driver if at all possible (it's free, so that shouldn't be an issue). It has more active development, and it's a lot faster.
Also, you should find out what sort of indexing is being used on your DB2/400 database. This can have a big impact. I've often found AS400 developers lacking in knowledge about DB indexes, and most AS400 shops I've talked with don't employ DBAs.
I am going to download and check out the JDBC driver on a development box and see how that fares in comparison. Any advice for me, as this is the first I have delved into this kind of situation?
Thank you again.
William
The only file you need out of what you'll download is the jt400.jar file. If you drop it in your jrun4/servers/lib directory, it will be available to all of your ColdFusion instances.
The docs for using the jtopen driver are fairly good (JavaDoc), but if you run into issues, feel free to contact me. You might also want to read the other iseries blog posts on my site:
http://www.brooks-bilson.com/blogs/rob/index.cfm?m...
I have been testing the 2 connections (client access over odbc & jtopen over jdbc) and have found that the odbc connection (older connection) is running on this 1 query at 60-90 seconds and the jdbc runs at 60-75 seconds. Not too much faster, however I haven't tweaked the setting string too much.
jdbc:as400://[as400 ip address]/;libraries=[file name];transaction isolation=none;naming=sql;prompt=false
Anything hit you that I should try? I have found out that the database is indexed, but the 'guy' is finding out what the technical name is for the type of indexing being used.
Thank you ,
William
The driver setup you have there looks pretty standard. How big is the DB you are hitting? How many records are you bringing back? One type of index we've been successful in the past with are called vector indexes.
Also, you should check to see how much priority is being given to your jobs on the system. Tweaking this so that your interactive SQL queries run at a high priority can have a big impact (many AS400s are tuned to give batch jobs priority). You need to ensure you're getting a big enough slice of the CPU.
Any chance you can contact me 'off-list'?
william [at] seiter [dot] com
William