Datastage and UDB / DB2 stored procdures

guidomarcel1 · Post by **guidomarcel1** » Wed May 24, 2006 5:29 am

Hi,
is it possible to call a UDB / DB2 stored procedure with Datastage v7? It seems that the stored procedure stage is only foreseen for the database vendor ORACLE.

Regards
GuidoMarcel

DSguru2B · Post by **DSguru2B** » Wed May 24, 2006 7:04 am

Depends upon what kind of stored procedure it is. If all you need to do is fire the stored procedure without any in, inout or out parameters then you can do it in db2 api stage (in the before/after sql tab)
IF the stored procedure has in and inout parameters, you can use the odbc stage (read the documentation for this stage in regards to st. procedures. It has limitations)
The STP stage just supports Oracle. If you read the documentation of STP stage it very clearly says there that any other relational database could be provided via job parameters during run time. I tried that. It came back with an error. I took that error back to IBM, they said that the statement in the STP stage that the database name can be provided via job parameter during runtime doesnt hold true if its not Oracle.
So bottom point, STP stage wont work at all. But if you upgrade to 7.5.2, the STP stage in that version does support DB2.
Hope my info helped.
Regards,

guidomarcel1 · Post by **guidomarcel1** » Wed May 24, 2006 8:07 am

Thanks for your answer. I will run a DB2 stored procedure without any in, inout, out parameters. I will try to do it with the DB2 API stage in the before section.

Regards
GuidoMarcel