ODBC and native connection to same data source?

inter5566 · Post by **inter5566** » Wed Jun 25, 2003 9:56 am

Good Morning,

I'm receiving abnormal termination warnings when using both an ODBC stage and a native Informix stage to connect to the same database.
I would like to retain this structure if possible, native connection for efficent stream flow and ODBC for multirow lookup.
Does DS not allow multiple type concurrent database connections?

Steve
DS 5.2
HP 11.11

tonystark622 · Post by **tonystark622** » Wed Jun 25, 2003 10:29 am

Hey Steve,

I've got several jobs that mix stage types, like you suggest. Oracle OCI8 and ODBC stages all at the same time.

What is the message you're getting?

Tony

inter5566 · Post by **inter5566** » Thu Jun 26, 2003 8:40 am

Tony,

When I try to manually run the job, it stops with an 'abnormal termination' warning, only information listed in the job log.
However, if I open the job with designer and go to the properties page of an ODBC stage and click on view data, I will see the correct data from the table. Or if I open designer and look at an Informix stage and click on view data, I will see the correct data. But, if after checking the ODBC stage first then checking the Informix stage I receive this error:

DSBrowser..dbTors01_job: [Informix][Informix ODBC Driver][Informix]Unspecified System Error = -25507.
Unable to connect to Informix ODBC data source 'tor_s01_p'.
DSBrowser..dbTors01_job.DSLink1: DSP.Open GCI $DSP.Open error -2.

And if I open an Informix stage first then the ODBC, I receive this error:

Error calling subroutine: DSD.Browse (Action=3); check DataStage is set up correctly in project tor_s01

followed by 'data source is empty' message box then this error:

Error calling subroutine: DSD.Browse (Action=2); check DataStage is set up correctly in project tor_s01
(The connection is broken (81002))

I would guess that we have some UNIX environment variable set incorrectly or we're using the wrong ODBC driver.

Any help would be appreciated before I open a ticket with support.

Thanks,
Steve

tonystark622 · Post by **tonystark622** » Thu Jun 26, 2003 4:31 pm

You got me, buddy. I'm having similar problems with ODBC here, but they're constant. I'll start a new thread if we don't get it fixed soon.

Good Luck,

Tony

inter5566 · Post by **inter5566** » Mon Jul 07, 2003 11:05 am

Tony,

Sorry for the long break (on vacation), I received word from Ascential that Informix and ODBC stages do not play nicely together. At least with DS version 5.2

Hope this helps,
Steve

tonystark622 · Post by **tonystark622** » Mon Jul 07, 2003 11:17 am

Heh. Wouldn't you know it would be something like that.

Ah, well, hope your vacation was good!

Thanks for posting the answer.

Tony

WoMaWil · Post by **WoMaWil** » Tue Jul 08, 2003 8:06 am

Hi Steve,

it is true, we have and had same problems on 5.2, and last week, a job, which ran half a year after a change we made, without thinking about the sense it worked.

So try putting seq-files in between or change hashStage to UVStage, which is about the same and for sure you will be surprise you finanly get a version of the job which is running.

So do any try and error, without thinking about if this makes any additional sense.

Wolfgang Huerter
=====================
Cologne, Germany

inter5566 · Post by **inter5566** » Tue Jul 08, 2003 8:57 am

Wolfgang,

Thanks for the suggestion. The job I designed needed to perform a multirow lookup and reference to reference linking. The appearent problem is that DS ties itself to the Informix DB through one type of connection ( InformixCLI or ODBC ) and cannot establish a separate link using the opposite type. I resolved the Issue by changing all my DB stages to ODBC type, about nine separate lookups. Any performance losses due to non-native DB connections were gained by using a smaller driver stream with a multirow lookup vs. driving the stream with data from the table used in the multirow. 20% gain by using this method.
Regarding the reference to reference linking, I was able to use a column from the first reference link to join to the second reference link within the same transform stage. Esentially creating a three table join. Drag and drop doesn't work, but you can type in the key expression. The transform will show the key expression as invalid (red) but the job will still compile and execute properly.

Steve