Results 1 to 7 of 7
  1. #1
    Join Date
    May 2011
    Posts
    11

    Unanswered: How To Find Dependency

    Hello,
    I want to drop table Student, but would like to find views and sprocs using table Student. Does anyone have an example of using Student to find all dependent objects?

    For view, is there a function that provides the actual schema, suchas sp_helptext in SQL?

    I notice some people use SYSCAT.ROUTINEDEP. But how do I query that function to return the objects related to Table Student?

    What about for VIEWS that reference table Student? How do I query that?

    Thanks for your help!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    SYSCAT.ROUTINEDEP is a catalog view, not a function. There are others like that, e.g. TABDEP (for tables and views), PACKAGEDEP (for SQL packages) etc. You use standard SQL to query them.

    More information here: System catalog views - IBM DB2 9.7 for Linux, UNIX, and Windows

  3. #3
    Join Date
    May 2011
    Posts
    11
    I'm having issue with the syntax when using these catalog.
    What's the syntax for all dependent objects for table [Student] using TABDEP?

    Also, any function such as sp_helptext in SQL?

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Try db2look, you can use -t option to get DDL just for the table and its dependent objects

  5. #5
    Join Date
    May 2011
    Posts
    11
    I've discovered syntax is quite complex compared with SQL.

    I'm getting syntax error - An unexpected token "db2look -t" was found following "BEGIN-OF-STATEMENT""

    db2look -t mktg.student

    Goal - trying to figure out exact syntax to find all dependent objects related to student before I delete the object.

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Example:
    db2look -d <db name> -e -t <table name>

    You can redirect the output to a file

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    db2look is not an SQL statement, it is a DB2 system command. See the DB2 Command Reference manual for more details.

    Here is the PDF manual for DB2 9.7:
    http://public.dhe.ibm.com/ps/product...-db2n0e972.pdf
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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