How to execute a DB2 Stored Function from Cognos?

[dtremain]

I'm new to Cognos, and am just learning it... (as you can tell, I'm easing into it...)

OmniSource is using JDE OneWorld on an AS400/DB2 database. OneWorld, because it works on four or five different databases, uses its own quirky format to store dates and numeric values. We want to use a DB2 stored function to convert these to a more commonly used representation (e.g., YYYYMMDD).

I've written the Stored Function, and tested it in STRSQL (AS/400 SQL Query execution app), but when I try to use it in a Cognos field definition, I get an error similar to the one DB2 gives when it can't find the stored function.

Does anyone know where to check next, or of a resource they could point me to that could help? (I'm guessing that the Cognos Server's DB2 ODBC connection login does not have execute permissions on stored functions OR that the stored function itself does not have correct permissions, but it could be something totally different). I can provide more details is someone is willing & able to take this on...

[BIsrik]

For accessing stored function u have to call the stored functions in a stored procedure. then define a stored procedure query subject in the framework manager and access the stored procedure. Publish the package and use the stored procedure query subject in ur reports.

If u have any other solution please share with us.

srik

[dtremain]

Thanks. It does not sound easy or straight-forward. If I discover anything else, I'll post it.

[dtremain]

OK. Call me stupid, but I'm still struggling with this.

Can someone post (or direct me to) a sample of an IBM DB2 stored function and stored procedure? On pg. 10-37 to 10-46 of the Cognos ReportNet 1.1 - Metadata Modeling Part 1 Student Guide, it talks about how to use a stored function from Cognos. However, it assumes a working stored procedure to use, and that is what I'm having trouble with.

I've been trying to work through the documentation from IBM, and have been able to write stored procedures, but not able to get information from a table to a variable or to  be visible outside of the stored procedure (without using an INOUT or OUTPUT parameter, because Cognos does not allow them).

Any help on this would be greatly appreciated.