stored procedures dependencies

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Jon.Hakkinen@gmail.com
    #1

    stored procedures dependencies

    Hi all,

    I'm running db2 9 on windows.
    I'm looking for a query to find dependencies between stored
    procedures.
    For instance if I have a procedure P that calls p1 and p2, I'd like to
    know that.
    I was looking at syscat.routined ep and syscat.packaged ep but they
    don't seem to do the job.
    Even if P needs p1 and p2 to compile, it is still listed only once in
    the syscat.routinde p catalog. Apparently this view is designed to keep
    the dependencies between routines and packages rather that
    dependencies between routines
    Also, the syscat.packaged ep show only dependencies to views, tables,
    etc, but not to other packages.
    Is there any way I can query this? Thanks!
    Tags: None
    • 4.spam@mail.ru
      #2
      Re: stored procedures dependencies

      On Oct 28, 11:23 pm, "Jon.Hakki...@g mail.com" <Jon.Hakki...@g mail.com>
      wrote:
      Hi all,
      >
      I'm running db2 9 on windows.
      I'm looking for a query to find dependencies between stored
      procedures.
      For instance if I have a procedure P that calls p1 and p2, I'd like to
      know that.
      I was looking at syscat.routined ep and syscat.packaged ep but they
      don't seem to do the job.
      Even if P needs p1 and p2 to compile, it is still listed only once in
      the syscat.routinde p catalog. Apparently this view is designed to keep
      the dependencies between routines and packages rather that
      dependencies between routines
      Also, the syscat.packaged ep show only dependencies to views, tables,
      etc, but not to other packages.
      Is there any way I can query this? Thanks!
      Hi Jon,

      Package for routine P will depend on routines P1 and P2.
      So you have to find that package and look at all routine instances
      which this package depends on:

      SELECT C.ROUTINESCHEMA , C.ROUTINENAME
      FROM SYSCAT.ROUTINES R
      JOIN SYSCAT.ROUTINED EP D ON R.ROUTINESCHEMA =D.ROUTINESCHEM A AND
      R.SPECIFICNAME= D.SPECIFICNAME
      JOIN SYSCAT.PACKAGED EP P ON P.PKGSCHEMA=D.B SCHEMA AND
      P.PKGNAME=D.BNA ME
      JOIN SYSCAT.ROUTINES C ON P.BSCHEMA=C.ROU TINESCHEMA AND
      P.BNAME=C.SPECI FICNAME
      WHERE R.ROUTINESCHEMA ='your_schema' and R.ROUTINENAME=' P' AND
      D.BTYPE='K'
      AND P.BTYPE='F'

      Hope this help.

      Sincerely,
      Mark B.

        Comment

        • Jon.Hakkinen@gmail.com
          #3
          Re: stored procedures dependencies

          On Oct 29, 6:09 am, 4.s...@mail.ru wrote:
          On Oct 28, 11:23 pm, "Jon.Hakki...@g mail.com" <Jon.Hakki...@g mail.com>
          wrote:
          >
          >
          >
          Hi all,
          >
          I'm running db2 9 on windows.
          I'm looking for a query to find dependencies between stored
          procedures.
          For instance if I have a procedure P that calls p1 and p2, I'd like to
          know that.
          I was looking at syscat.routined ep and syscat.packaged ep but they
          don't seem to do the job.
          Even if P needs p1 and p2 to compile, it is still listed only once in
          the syscat.routinde p catalog. Apparently this view is designed to keep
          the dependencies between routines and packages rather that
          dependencies between routines
          Also, the syscat.packaged ep show only dependencies to views, tables,
          etc, but not to other packages.
          Is there any way I can query this? Thanks!
          >
          Hi Jon,
          >
          Package for routine P will depend on routines P1 and P2.
          So you have to find that package and look at all routine instances
          which this package depends on:
          >
          SELECT C.ROUTINESCHEMA , C.ROUTINENAME
          FROM SYSCAT.ROUTINES R
          JOIN SYSCAT.ROUTINED EP D ON R.ROUTINESCHEMA =D.ROUTINESCHEM A AND
          R.SPECIFICNAME= D.SPECIFICNAME
          JOIN SYSCAT.PACKAGED EP P ON P.PKGSCHEMA=D.B SCHEMA AND
          P.PKGNAME=D.BNA ME
          JOIN SYSCAT.ROUTINES C ON P.BSCHEMA=C.ROU TINESCHEMA AND
          P.BNAME=C.SPECI FICNAME
          WHERE R.ROUTINESCHEMA ='your_schema' and R.ROUTINENAME=' P' AND
          D.BTYPE='K'
          AND P.BTYPE='F'
          >
          Hope this help.
          >
          Sincerely,
          Mark B.
          Great, it works like a charm, thanks a lot!!

            Comment

            Previous template Next
            Working...