DSXchange

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.

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.

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?

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.

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.

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

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