ODBC stage to connect to iseries

Pete Morris · Post by **Pete Morris** » Wed Aug 30, 2006 2:44 am

Hello......
I am trying to access a db2 table on the iseries using an ODBC stage.

I have configured the odbc.ini file to use a db2 wired protocal driver - Driver=/usr/local/ascential/Ascential/DataStage/branded_odbc/lib/VMdb220.so
Description=DataDirect 4.20 DB2 Wire Protocol D20ver.

The signed numeric columns contain unreadable data whereas all other datatypes such as packed decimal etc appear OK.

When i access the same table using the DB2 Connect stage the signed numeric columns appear OK.

Am i missing something in the configuration of the ODBC connectivity.

P.S. i need to use the ODBC stage as i want to make use of it's multirow lookup functionality.

ray.wurlod · Post by **ray.wurlod** » Wed Aug 30, 2006 3:13 am

It may be preferable to bring the data to something local using DB2 Connect (perhaps via DB2 stage). Then you can use ODBC or UV stage to perform lookups locally and still use the multi-row return capability - it will be much faster than trying to do it over a network and, in bringing the data to the local machine, you can decode the packed decimal fields.

Pete Morris · Post by **Pete Morris** » Wed Aug 30, 2006 3:31 am

Ray,
unfortunately the table in question will contain 70 million rows therefore it won't be practicable to download it to the local server everynight. Any other thoughts?

ray.wurlod · Post by **ray.wurlod** » Wed Aug 30, 2006 6:16 pm

You should be able to stream 70 million rows in an hour or two. After that you can stream only the changed rows, if you have some way of detecting them.

Pete Morris · Post by **Pete Morris** » Thu Aug 31, 2006 2:23 am

ray.wurlod wrote:You should be able to stream 70 million rows in an hour or two. After that you can stream only the changed rows, if you have some way of detecting them.

I have been in touch with Ascential and they stated that the DB2 odbc driver for unix does not translate iseries signed numeric data types to the ascii equivalent and that i should translate them myself using the supplied DS routine DataTypeEbcdicPic9.

ray.wurlod · Post by **ray.wurlod** » Thu Aug 31, 2006 3:19 am

OK, you can do that in a job after the data have arrived.

kommven · Post by **kommven** » Fri Sep 01, 2006 5:56 pm

I remember something a parameter called CharsetFor65535, maybe that works...

For information on this parameter contact branded odbc providers or contact IBM they will solve.

This is a kind of charset conversation that missing in this entry

-R