Topic
  • 2 replies
  • Latest Post - ‏2014-08-12T16:07:06Z by RickSwagerman
AngocA
AngocA
25 Posts

Pinned topic GET_DEPENDENCY procedure with modules

‏2014-08-05T22:28:03Z |

How can I use GET_DEPENDENCY procedure with an object in a module? Or is it a procedure just for non-module objects?

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.sqlpl.doc/doc/r0055162.html?lang=en

  • mor
    mor
    577 Posts

    Re: GET_DEPENDENCY procedure with modules

    ‏2014-08-06T11:11:32Z  

    According to the documentation:

    "Valid values are FUNCTION, INDEX, LOB, PACKAGE, PACKAGE BODY, PROCEDURE, SEQUENCE, TABLE, TRIGGER, and VIEW."

    suggesting that MODULE is not valid.

    But increasingly the documentation quality is deterioriating and incomplete, so you might want to ask IBM directly (via a PMR, or email to db2docs@ca.ibm.com )

    Apart from that, a module will result in one or more packages, and you might be able to use that information (dependency of a package) to indirectly get what you need.

  • RickSwagerman
    RickSwagerman
    109 Posts

    Re: GET_DEPENDENCY procedure with modules

    ‏2014-08-12T16:07:06Z  

    To answer the original question, you cannot use GET_DEPENDENCY for an object within a module. As you surmised, it is for non-module objects.

    While the documentation may not be completely clear about this, the built-in modules that are currently provided with DB2 are implemented to provide a level of compatibility with Oracle's built-in packages. So, the DBMS_UTILITY module includes a set of functions and procedures that are meant to mimic the syntax and semantics of the same routines in Oracle.

    The reference to valid values PACKAGE and PACKAGE BODY refer to the Oracle package for which the DB2 equivalent is a module. As I understand it (not an Oracle expert), Oracle does not track dependencies on objects within a package, it only tracks the dependency on the package. Hence the GET_DEPENDENCY procedure only allows for a schema name and a object name as arguments and the output will never refer to an object within a package (module).

    The SYSCAT.ROUTINEDEP catalog view can be queried to determine the objects that a procedure (whether in a module or not) depends on. If you are after determining what objects depend on a particular procedure (whether in a module or not), you would need query a set of the SYSCAT views with names ending in DEP (such as TRIGDEP) that could actually call a procedure.