Results 1 to 8 of 8

Thread: SP dependency

  1. #1
    Join Date
    Dec 2004
    Posts
    54

    Unanswered: SP dependency

    How to findout the SP and UDF hierarchical order? for example one SP may call another SP and one UDF may call another UDF, so I would like to know the order.

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I guess you could find this information if you explain the corresponding packages.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Check out syscat.routinedep and syscat.packagedep.

    Andy

  4. #4
    Join Date
    Dec 2004
    Posts
    54
    thanks for the replies,

    but still how can I find out which SP is calling which SP?

    Thanks

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try something like this:

    select pd.bschema,pd.bname from syscat.routines as r inner join syscat.routinedep as rd on (r.routineschema = rd.routineschema and r.routinename = rd.routinename) inner join syscat.packagedep as pd on (rd.bschema = pd.pkgschema and rd.bname = pd.pkgname) where r.routineschema = 'MySchema' and r.routinename = 'MyRoutine' and rd.btype = 'K' and pd.btype = 'F'

    Read the SQL Reference manual and you should be able to expand on this.

    Andy

  6. #6
    Join Date
    Dec 2004
    Posts
    54
    This sql is not giving the correct result

  7. #7
    Join Date
    Feb 2005
    Location
    Bangalore,India
    Posts
    39

    Try this query

    Can you try this query...

    SELECT A.PROCNAME,B.BNAME
    from SYSCAT.PROCEDURES A, SYSCAT.PACKAGEDEP B
    WHERE SUBSTR(A.IMPLEMENTATION,1,8) = B.PKGNAME
    AND BSCHEMA = 'TBLPY00'
    AND BTYPE = 'F'
    ORDER BY 1,2

    NOTE : This query works only if the first 8 characters of IMPLEMENTATION represents the package name.

  8. #8
    Join Date
    Dec 2004
    Posts
    54
    This SQL is giving UDF name being used in SP, what about SP calls another SP?

    Thanks

Posting Permissions

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