Executing Store Procedures in DB2 UDB

bnagel · Post by **bnagel** » Thu Apr 17, 2003 9:25 am

I have some stored procedures with input parameters that I need to execute in Data Stage. How can this be done?

Brian G Nagel

chulett · Post by **chulett** » Thu Apr 17, 2003 10:48 am

Well... the only stage (other than BCPLoad, that is) that directly supports Stored Procedures is the ODBC stage. Choose the Update Action of "Call stored procedure" and then all of your columns become input parameters.

-craig

ray.wurlod · Post by **ray.wurlod** » Thu Apr 17, 2003 5:52 pm

Provided you get the syntax correct, curly braces and all, you should be able to use CALL via user-defined SQL in pretty much any stage type that supports SQL (for example the DB2 stage).

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

bigpoppa · Post by **bigpoppa** » Tue May 06, 2003 7:02 am

Has anyone been able to successfully call a stored proc from within the DB2 stage?

My java stored procedure is a simple count(*) on a table. My call, which works find from the db2 commandline, has been:

Call Get_Count(?)

I've tried several variations on this call in the 'Before' routine and in the 'user generated sql' sections of the DB2 stage, but I don't think my call syntax is correct. No matter what, I get the same error:

"07001 The number of host variables is not the same as the number of parameter markers."

Does anyone have an example of calling a stored proc from the DB2 stage that actually worked?

Inquisitive · Post by **Inquisitive** » Tue Jan 27, 2004 10:44 am

Hi All,
I have been going through the old posts on this forum, but not been able to get enough inputs.

Has anyone successfully invoked a udb stored procedure using db2 api stage.

Bigpopa, Any inputs for me... Pointers would be appreciated

Thanks

normgreen · Post by **normgreen** » Mon Jul 12, 2004 12:43 pm

Have there been any updates on this topic from the Ascential releases? or does someone have an example of a query that works. We are executing in test on a one server machine (DB2 and DS on same server). In production we have DS on one server and DB2 on another. The call statement used in test does not work in production. We have checked the dsenv and brought in all of the env variables from the server. We get file not found as an error message. The call works from a command line on the DS server in production. SQL statements work as well in DS, we are just having issues with the call of a stored procedure in this environment within DS. Thanks for any assistance.