Using the JDBC Adapter to connect to DB2 on mainframe

We are attempting to use the JDBC adpter to connect to DB2 on the mainframe. Our first attempt was with DB2 Connect V7 but found out that WebMethods did not support that version. We install version 6 which is working using SQL but will not work with Stored Procs. When attempting to Configured the Stored Procedure operation the WebMethods operation attempts to determine the Procedure Name: returns what looks like the same Catalog structure that contains tables but list the Stored Procedures repeated under each of the tables schema.
I guess my first questions is: Has anyone sucessfully connected to mainframe DB2 and what kind of issues did you have?

We successfully used the ODBC adapter to connect to DB2 on OS/390 a couple years ago. The only issue was that the adapter didn’t recover gracefully when the DB2 connections were dropped nightly. Used a scheduled task to restart the adapter each night.

Didn’t use it to run SPs though.

As I recall, the client side settings of Connect needed to be just so or you’d run into problems. I assume you’re working with your DBA to resolve. As it has been a couple of years, I don’t recall which settings were important, but they’re likely different for your environment anyway.

Sorry I’m not much help.

We had a similar problem and used a work-around provided by webMethods. Instead of using the Stored Procedure operation, we created a Scripted Operation. In the Custom Code step, we connected to our AS400 and executed the SQL statement that calls the stored procedure. From there we pass in any input parms and retrieve the return values.

Hope this helps

We are using Oracle as our logging database for the Enterprise Server. What’s the best way to provide inquiry capability? I have installed the new browser but it does not seem to support the ES logs.

We are connecting to DB2 OS/390 with Enterprise Server 4.1.1. We have the runtime client installed on the same box as Enterprise Server. DB2 Connect is running on Linux (actually, it’s Linux running on the mainframe but I won’t get into that).

The first issue we ran into was not being able to use the IBM ‘net’ driver which the documentation recommended when DB2 Connect resided on a separate machine. We had to use the ‘app’ driver because it at least would make a connection.

The next issue we ran into was how to make the specified tables in the configured operations more portable. The table selector is Current Catalog.Current Schema but tables would never show up under the current schema. We’d always have to select a specific schema then the table. Schemas differed from developement, test, and production. We got around this by having the dbas create a database alias for the use id and assign the desired tables to the alias. However, the tables still never showed up under Current Schema. To make long story short, the uid in the adapter configuration had to be in upper case!

Finally, with stored prodedures (SQL, not cobol) we’ve had very few problems. There is a stored procedure property called TOP SECRET that needs to be set otherwise we would receive an exception when calling the stored procedure. It needed to be set to some user other than the user id that is in the adapter config. In our case the user was ‘DB2’ which might have been a dba user id.

In your situation, I would recommend working with your dba to put a trace on the queries the adapter is doing during start up and when trying to create the configured operation. That’s how we found out the uid needed to be in upper case.

Final note by author of original JDBC Adapter / Stored Procedure issue: We were successful in connecting the JDBC Adapter to the IBM mainframe using DB2Connect v7. A number of issues made this process somewhat complex were the fact that the JDBC adapter does not retrieved the SP Input and Output fields during the Introspection step. The Adapter instructions could have been more descriptive. And finally the mainframe Schema design was using the old SYSPROC library to track all SP’s. The JDBC adapter does not and should not use the SYSPROC library. I hope this helps the next person.

A correction on the last part of my response. The property (attribute) is SECURITY not TOP SECRET. Sorry for any confusion that might have caused.

Hi Cort ccable@crowechizek.com,

It seems u have worked with JDBC adapter for DB/2-AS/400.

we are able create insert,upadate,delete notifications on user defined SQL tables. when coming to Item Master (F4101 JDEWorld)
we are unable to create triggers and same with the files.

could u help us…

Thanks
Uday