JDBC v602 adapter for DB2 on AS400

, ,
1

Hello webMethods brain-iacs. Can anyone comment on using the JDBC adapter (v6+) to access DB2 on an AS400 (V5R2) ?
Pros ? Cons ? Gotchas ? Best practices ?

Your feedback is appreciated.

Regards,

Wayne

2

Hello Wayne!

I find that using the WmJDBCAdapter works fine against DB2. I’ve worked with the IS 6.0.1 and respective Adapter SPs. I find that the Type 4 toolkit driver (jt400.jar) is your best bet:

Here is the class/Trans used on a Adapter Connection Config:
Transaction Type = LOCAL_TRANSACTION
DataSource Class = com.ibm.as400.access.AS400JDBCDataSource

If you are running the IS Server from AS/400 make sure to update server.sh and set a MIN_MEM setting low (e.g. ) This reduces Garbage Collection threshold:
set JAVA_MIN_MEM=32M
#set JAVA_MAX_MEM=256M

Also see “Tuning Garbage Collection for JavaTM on iSeries”
It suggest not to include MAX_MEN setting as this is not recommended on AS/400 for Java apps, due to performance issues.

3

Dezi, thanks very much for the info. This really helps.

Regards,

Wayne

4

Hi. We are trying to use the JDBC 6.0.2 adapter with DB2 on the AS/400 (V5R2) and are getting the following errors:

Receiving error when trying to retrieve data from DB2 table on AS400,
release V2R5.

“Adapter runtime metadata unable to look up resource domain values
null on select.expression
[ljava.lang.string;@768d7c
java.lang.exception adapter values are not available”

We are using the classes in the JTOpen (v4.1) jar file jt400.jar. Our webMethods IS is version 6.0.1.

We do have a support ticket open with webMethods (1-39766759) but if anyone has seen this before please let me know.

Regards,

Wayne

5

See if you have Fix 3 for webMethods JDBC Adapter 6.0.2 Version.

1-MO5T7

This fix enables the JDBC Adapter to work with additional
database resources.

6

Dezi, how do I get Fix 3 ? I don’t see it at Advantage…

Wayne

7

Wayne,

You can get it by calling the wm-support

8

Thanks Mike. We did get the fix but it didn’t help our problem. We are currently working with webMethods on this. If anyone using the JDBC 6.0.2. adapter with DB2 on the AS/400 has experienced problems please let me know.
Just to clarify the above problem: Our problem occurs in webMethods Developer while we try to create an adapter service (using SQLSelect template). The adapter instance is connecting to db2 on the as/400. The following error occurs while webMethods Developer is trying to build the “pick list” of columns:

“Adapter runtime metadata unable to look up resource domain values
null on select.expression
[ljava.lang.string;@768d7c
java.lang.exception adapter values are not available”

This error only occurs on specific tables. We haven’t found anything unusual about the tables that are causing the errors.

Anyone have any feedback on this?

Regards,

Wayne

9

Wayne,

Did you get any update on this one ?

Thanks

Mike

10

Wayne,

Does the select work if you create it as a CustomSQL adapter service?

Did you resolve the issue with the Select adapter? I will be involved in a very similar interface in a few weeks and I’d like to know what the resolution was.

Thanks,
Jesse

11

Hi Everyone,

I am getting a similar error to what Wayne got above:

com.wm.app.b2b.server.ServiceException: [ART.114.205] Adapter Runtime (Metadata): Unable to lookup the resource domain values.
numDigits (0): The parameter value is out of the allowed range.
on tables.columnInfo [Ljava.lang.String;@4d4f05java.lang.Exception: Adapter values are not available.

This occurs when I select which schema/table to use in a Select Adapter service going against db2 on an AS/400.

Has anyone found a solution to this problem?
I am running wMIS v6.01 build 425, on winXP.

Thanks for all your help!
Jeff

12

All,

We have resolved this problem. It was caused by a IBM java PTF applied to that AS400 DB2 instance which caused the jt400 driver unable to connect to the db for the introspection. Check with you DB2 admin to see if that’s the case.

13

For those who don’t want to have the popup for a new username/password, make sure you add the prompt=false to the other properties…

(Especially if you’re running as a service, and you don’t want the server to hang :-))

Also, make sure you use the correct version (JTopen 3.3 with JDBC Adapter 6.0.2 and JTopen 4.1 with JDBC Adapter 6.0.3)

14

Another hint for as/400 access is to set “transaction isolation=read committed”. If you don’t do this, a select will return records that have not been committed.

15

Hi Mike. We are following up on this problem. You mentioned in your post above that the problem was “caused by a IBM java PTF applied to that AS400 DB2 instance”. Can you please clarify which PTF was causing the problem ?
There have been many java PTF’s applied in our environment.
Can you please respond a.s.a.p. ?

Thanks!

Wayne

16

Another thing to consider is adding the ‘TranslateBinary=true’ flag to the other properties.
You will need this if some of the values being returned from DB2 are in EBCDIC instead of ASCII.

Does anyone know of issues where webMethods causes an AS400 to crash ? I don’t personally think its wM, but…

Thanks
Rax

17

Please refer to the following thread regarding this problem:
[url=“wmusers.com”]wmusers.com

After the java PTF’s were applied in our AS/400 environment the problem went away…

Wayne

18

Hi,
Can anyone explain on how to use the JDBC adapter (v6+) to access DB2 on an AS400 (V5R2) ?
We have worked on JDBC adapters for Oracle till now and dont know how to approach for DB2 connectivity and data retrieval.
Your feedback is appreciated.

Regards,

Sachin

19

Do adapter notifications work with DB2/AS400?

20

Ulf,

Yes, notifications work on with DB2/400. What difficulties are you having?

– Ted