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

grofaty · #1 (**permalink**) 10-22-10, 07:05

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

n_i · #2 (**permalink**) 10-22-10, 07:29

Check the catalog view SYSCAT.ROUTINEDEP.

sathyaram_s · #3 (**permalink**) 10-22-10, 11:29

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

grofaty · #4 (**permalink**) 10-25-10, 03:48

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

Internet Services

Web Development

Digital Marketing

Consumer Tech