JDBC Adapter vs AS400

, ,
1

I have an integration that extracts and inserts data from AS400 table. I am getting data type mismatch errors while inserting. When I tried to select data it is displaying data in a different format (I guess it is Hexa decimal). Below is the query and data, from JDBC adapter 4.6 SP1 connected to broker 6.0.1 SP2.

SELECT t1.VMVENN,t1.VMHOLD,t1.VMPRTC,t1.VMVNNM,t1.VMADD1
FROM VENMST t1 WHERE ( t1.VMVENN = ‘121212’)

event ActiveWorks::Adapters::DMR01_PDM_ODBCAdp::Operations::SELECTOperation::output {
unicode_string VMVENN = “F1F2F1F2”;
unicode_char VMHOLD = ‘\0’;
unicode_char VMPRTC = ‘\0’;
unicode_string VMVNNM = “404040404040404040404040404040”;
unicode_string VMADD1 = “F1F8F4F640C3D6E5C5D5E3D9E840E2”;
};

Has any one know any work around or fix for this?.

2

What is the JDBC Driver version that you are using to connect to the database.

3

OOPS… the previous message was incomplete… my session timed out.

Anyways, I had experienced similar type mismatch issue before and it was due to the JDBC driver version mismatch connecting to the DB2 database.

Hope this helps.

4

This AS400 is not using DB2, rather it is using file system.

JDBC Driver: IBM
JDBC Jar file: jt400.jar
Driver Class: com.ibm.as400.access.AS400JDBCDriver

Just found, this particular AS400 is using EBCDIIC character set. Not sure any settings required in adapter level to convert this to ASCII.

5

Hello, we have been using the webMethods jdbc adapter (v6.0.2.) to read/write/update files on the as/400 file system. Here are the adapter properties we use. I think the parm “TranslateBinary=true” will solve your problem.

Transaction Type NO_TRANSACTION
DataSource Class com.ibm.as400.access.AS400JDBCDataSource
serverName
user:
password ******
databaseName: <enter the as/400 library name where the file is located>
portNumber
networkProtocol
Other Properties: TranslateBinary=true;SelectMethod=direct;libraries=<same as databaseName;Prompt=false

If you have anymore questions let me know…we have been working with this for months…

Good luck !

Wayne

6

I tried this property in 4.6 JDBC adapter as below, but did not work.

jdbc:as400:///;TranslateBinary=true;SelectMethod=direct;

Am I missing some thing?. Is this right place to setup the other properties as in 6.x JDBC adapter?.

7

Hi. I didn’t realize you are using version 4.6 JDBC adapter. Is this the jdbc adapter that runs on the broker enterprise adapter runtime ?

We are using the “new” (version 6) JDBC adapter that runs under the Integration Server Adapter runtime. I’m not sure how you enter the parameters for the v4.6 adapter.
It’s possible the “TranslateBinary=true” will help you…now you just have to find the right place to specify it for your adapter.

Check out the documentation for the adapter. It should give you an example on adding “other properties”.

Wayne

8

The correct propery (case sensitive) is:

translateBinary=true using JDBC 6.03 adapter

9

On 6.1 we use in “other properties”
transaction isolation=read committed;date format=iso;time format=iso;translate binary=true;errors=full;prompt=false;naming=sql

in 4.6 we use for our jdbc string:

jdbc:as400://our400name;date format=iso;time format=iso;translate binary=true;errors=full;prompt=false;naming=sql;transaction isolation=read committed

10

Finally it worked with a space. Thanks to evey one.
I am wondering any documentation is available for these properties with JTOpen driver docs. I could not find in the search.