Unanswered: Non-SQL Function and Stored Procedure DDL
IBM Data Studio will return the DDL for a single procedure or function even if it is non-SQL procedure or function.
The DB2 catalog doesn't provide the DDL for these non-SQL procedures and functions so I'm wondering how IBM Data Studio does it because we'd like to be able to add this functionality to a program we're working on.
A program like DB2LOOK will give you everything, but we're just looking for single DDL statements for particular non-SQL procedures and functions. Thanks for your help!
Can you clarify? A stored-procedure or external user-defined function that is written in C or C++ etc will not have its code in the catalog. Similarly if an hostname runs a DB2 server and only has the class files for java sprocs or java external functions deployed (i.e no source code) then their source code will not be visible to DataStudio if they were not developed with DataStudio.
All necessary information you can get from the system catalog using the following couple of views:
from syscat.routines r
join syscat.routineparms p on r.routineschema=p.routineschema and r.specificname=p.specificname and (r.routinemoduleid=p.routinemoduleid or coalesce(r.routinemoduleid, p.routinemoduleid) is null)
where origin='E' ...
Looking at the syntax of the CREATE FUNCTION or CREATE PROCEDURE you can construct these statements using the fields from these views.