DataStage 7.5.x with Sybase IQ 12.6 on Linux

Precious · Post by **Precious** » Fri Oct 27, 2006 3:09 am

Hi,

As far as I know (after searching the forum) this has not being covered before.

We are able to connect to the IQ database using both ODBC and Open Client.
The Sybase Open Client is behaving rather strangely. To just list a couple of scenarios:

Have a Sybase OC Stage connecting to a table and doing a simple select,
select max(skey) from xTable.. Instead of getting one row, all the rows are returned. The sql shows the correct syntax. After changing to a Dynamic RDBMS Stage, same thing happens when Sybase is selected as a server type. But after changing to ODBC as a server type, one row is selected.

Another issue-view data returns a blank error in datastage[message box with exclamation and ok button, but no error message] when using sybase oc and returns data when using ODBC.

The problem is that these issues are not consistant. This happens with some of the jobs and not all of them.

Has anybody come across this before?
Any suggestions?

Thanks,

ray.wurlod · Post by **ray.wurlod** » Fri Oct 27, 2006 7:39 am

I can only suggest that you involve the DBA to track exactly what query was received/executed by the database server.

kcbland · Post by **kcbland** » Fri Oct 27, 2006 8:27 am

The Sybase stuff is flaky. You can see this by looking at the generated SQL in the RDBMS stage and see that it is not correct, or, what you see is what is not sent to the database.

How are you using the Sybase plug-in to generate the SQL? Did you look at using column derivation-based generated SQL? (check the box on the output stage tab) If you tried putting the MAX(...) on the derivation column, it doesn't work without checking this box. The derivations are ignored.

Precious · Post by **Precious** » Sun Oct 29, 2006 11:43 pm

Thanks for the responses.

How are you using the Sybase plug-in to generate the SQL? Did you look at using column derivation-based generated SQL? (check the box on the output stage tab) If you tried putting the MAX(...) on the derivation column, it doesn't work without checking this box. The derivations are ignored.

That is the first thing that I checked. I was using derivation-based generated SQL with the check box for the option checked. The only way to get it to work with the Sybase plug-in was to use user-defined SQL.

I am going to go the route of checking what exactly is getting executed on the database using the various stages and connection types. Will post findings.

Regards,

Precious · Post by **Precious** » Tue Oct 31, 2006 10:37 pm

Hi,

After running a couple of jobs, I can't reproduce the problems.

It seems to be the environment that is unstable, and not necessarily the DataStage Sybase Stages. We are in the process of moving to bigger and better, and hopefully more stable boxes. If the issues re-occur, I will resume testing then.

Regards,

nvalia · Post by **nvalia** » Fri Dec 08, 2006 6:24 am

The reason is happening is due to the fact that Sybase Open Client connectivity used 64-bit architecture while the ODBC driver is designed for 32-bit architecture that is used by Datastage.
Hence this instability & non consistent behaviour.

Regards,
NV

ray.wurlod · Post by **ray.wurlod** » Fri Dec 08, 2006 3:45 pm

Can we presume that you work at the same site as Precious?