Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to get list of all store procedures that are related to specific table?

    Hi,
    using db2 v9.5 on Linux I would like to get list of all SQL store procedures that are related to specific table.

    Sample:
    Code:
    CREATE PROCEDURE ADMIN.STORE_PROCEDURE
      (
        IN INspID INT,
        OUT OUTspID         INT,
        OUT OUTspUSERTYPE   CHARACTER  (20)
       )
      LANGUAGE SQL
      BEGIN
            SELECT
                ID, USERTYPE
            INTO
                OUTspID, OUTspUSERTYPE
            FROM
               ADMIN.MY_TABLE
            WHERE
                ID = INspID
            ;
      END
    @
    In the above sample is there any way I could write some select statement against SYSCAT views to get all store procedures where ADMIN.MY_TABLE is included in select/insert/update/delete statement? So answer to this query would be ADMIN.STORE_PROCEDURE, because it includes ADMIN.MY_TABLE in select statement.

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check the catalog view SYSCAT.ROUTINEDEP.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Please note, this will work only for Static Statements.

    If you use dynamic sql, this approach will not work and afaik, there is no definitive way of finding this info

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Thanks for help. I have managed to write an SQL to get a list of stored procedures that are related to specific table.
    Code:
    SELECT
        SUBSTR(C.ROUTINESCHEMA,1,15) AS PSCHEMA,
        SUBSTR(C.ROUTINENAME,1,15) AS PNAME, 
        C.TEXT
    FROM
        SYSCAT.PACKAGEDEP A,
        SYSCAT.ROUTINEDEP B,
        SYSCAT.ROUTINES C
    WHERE
        A.PKGSCHEMA=B.BSCHEMA AND
        A.PKGNAME=B.BNAME AND
        B.ROUTINESCHEMA=C.ROUTINESCHEMA AND
        B.ROUTINENAME=C.SPECIFICNAME AND
        A.BSCHEMA='ADMIN' AND
        A.BNAME ='MY_TABLE'
    Note: my table name is ADMIN.MY_TABLE

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
  •