Stored Procedure Stage with Sybase on unix

blewip · Post by **blewip** » Tue May 17, 2005 4:27 am

I am trying to obtain the result set from a stored procedure. The database is Sybase and as I am running 7.5.1 I have the new Stored Procedure Stage. Unfortunately there seems to be no documentation on it. When I try and use it, I am asked to supply a DSN.
My questions are therefore.
1) Can I only use it through a ODBC connection.
2) Do I have to buy some ODBC software for my unix box.
3) Will there be a performance overhead, going through ODBC.
4) Is there any other way to get the result set from Sybase.

Thanks

chulett · Post by **chulett** » Tue May 17, 2005 6:14 am

The first question is - does it support Sybase? I don't recall - but I think the answer is no.

Check the documentation, there should be a pdf file in the Docs folder where you installed the DataStage Client.

If you want to use ODBC for this, then you'd need to use the ODBC stage. There are branded drivers that install with the server, so no - you don't have to buy any for use with DataStage. Any other way to get stuff from Sybase? Sure, the Sybase OC stage - as long as the client is co-resident with your DataStage server.

However, I'm not sure any of this is applicable to a PX job... but I'll let others chime in on that.

blewip · Post by **blewip** » Tue May 17, 2005 8:19 am

It seems to support Sybase, as it is an option from the property help of the Database vendor field. I had a look at the documentation you mentioned and it does document the Stored Procedure stage but only from a Oracle point if view, so I am guessing that 7.5.1. supports Sybase Stored procedures, however the documentation has not caught up yet.

I can see various companies that produce an ODBC driver, intersolv, openlink, merant and easysoft. I haven't found one that is free yet, although I can get a free trial from easysoft, etc

What do you mean by

Sure, the Sybase OC stage - as long as the client is co-resident with your DataStage server.

I have tried to call the Sybase Stored procedure using the Sybase OC Stage, using a User Defined SQL query, but no joy

chulett · Post by **chulett** » Tue May 17, 2005 8:26 am

Never said the OC stage would work to call a stored procedure. Doesn't mean it won't.

I just mentioned it as another way to get data from Sybase.

The Sybase OC stage is a native stage, so like the other native stages it requires that the software it uses be installed on the DataStage server. For the Oracle OCI stages, that software is the Oracle client. For the Sybase OC stage - the Sybase client. And then DataStage must be properly configured to support them.

It's spelled out in the Installation and Configuration Guide for the plug-ins.

blewip · Post by **blewip** » Tue May 17, 2005 8:37 am

I'm sure that the Sybase OC software is installed correctly on the DataStage server as I can get data out of the Sybase tables, I just can't get a resultset from a Stored Procedure.

It seems that I need to obtain and install an ODBC driver on my Solaris box. Oh well

chulett · Post by **chulett** » Tue May 17, 2005 9:05 am

No, the ODBC stage will happily use the branded ODBC drivers that come with DataStage. And is supposed to support Stored Procedures. You'd only need to buy new drivers if you wanted to do this in a routine or via hand coded job control.

blewip · Post by **blewip** » Tue May 17, 2005 9:23 am

Thanks, that sounds like the solution to my problem.

palmeal · Post by **palmeal** » Wed May 18, 2005 4:48 am

Seems you may have sorted your problems but here's my findings re the sybase stored procedures.

Sybase Procedures are supported in version 7.5.1 - this is the only reason that we upgraded to this version.

I haven't found any documentation on these but the online help with a bit of trial and error were enough for me.
Drop the stored procedure stage onto your server job window and attach a file to it. Set the Database Vendor to Sybase.

When you add the stored procedure to the syntax tab then you will be prompted with a pop up stating that no columns have been defined for the result set. Just click ok to get past this.

On the Stage tab of the stored procedure you have to add in all your parameters (i.e. columns that you are going to return) and define the parameter type as output. This list of parameters will (probably) match the columns list on the Output tab.
On the output tab select "Procedure Returns Multiple Rows" if you expect more than one row.

Hope this helps.

blewip · Post by **blewip** » Wed May 18, 2005 8:19 am

Thanks guys this work a treat.

I set the return code = no.
Also my Stored Procedure just returns a result set, so the Stored Procedure Parameters were left blank.