Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2016

    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!


  2. #2
    Join Date
    Apr 2012
    Provided Answers: 27
    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.

  3. #3
    Join Date
    Jan 2003
    Provided Answers: 5
    He is talking about the DDL. The DDL is not stored in the catalog, but can be generated from it for external routines. That is what Data Studio is doing.


  4. #4
    Join Date
    Jul 2016
    Provided Answers: 45
    All necessary information you can get from the system catalog using the following couple of views:

    select ...
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts