Calling ODBC in a routine works in 8.1 but fails in 9.1

[MrBlack]

Calling ODBC in a routine works in 8.1 but fails in 9.1

I have a custom routine where I use the ODBC to connect and execute a query. It was working on 8.1 32-bit linux, but then the SA's upgraded my server to 9.1 64-bit linux and now it's stopped working, no real errors being returned, just "Error while executing SQL".

Now here's the kicker, if I import a table definition though ODBC then go and test my routine, everything works. I can't use the routine in any job but I can test it and works. If I close my designer and reopen, it stops working until I import another table definition.

Anyone have any clues what might being going wrong. If I was more of an expert, I'd say something with the driver has changed between the two versions. Maybe something to do with creating a session or allocation of some sort of environment variable.

[jdinero]

Not sure if this will help but I just completed a migration from 8.7 to 9.1 AIX. I had to make a lot of changes to the .odbc.ini found in the /Server/DSEngine directory. Maybe compare your old .odbc.ini to your new one if possible.

I noticed that 9.1 came with what appeared to be 'older' versions of drivers. For example:

9.1
VMor800.so for Oracle

8.7 had
VMor825.so for Oracle.

It seems to me that 825 came after 800 but what came with 9.1 was 800. The actual drivers are found in the /branded_odbc folder in DSEngine.

Might be a place to start.

Good luck,
Jennifer

[asorrell]

The DataDirect ODBC drivers are provided by a third party (Progress). Each release of DataStage can work with a fairly wide range of driver revisions. When new drivers come out they will try to incorporate those drivers into the next release of any supported revision.

So if your rev 8 system had a patch that was built after your rev 9 system was built, it is very possible that the "old" system had the newer ODBC drivers.