Skip to content

CMNDB2DR

Db2 Object Dependency Report

The Db2 Object Dependency report is a batch report that analyzes stored procedures and user defined functions for dependencies that will interfere with the automatic DROP that is issued before a CREATE is executed at promote, demote, install, or backout.

Run this report for each package that contains CREATE PROCEDURE or CREATE FUNCTION statements for stored procedures and user defined functions to find potential problems with the automatic DROP that is issued before each CREATE SQL is processed.

Program CMNDB2DR reads specified members in a PDS library of Db2 object definitions. It parses the contents of each member looking for any of:

CREATE PROCEDURE
CREATE FUNCTION

It extracts the name of the object to be created and reports on any other objects which are dependent on this object.

Program CMNDB2DR can be run in batch using member CMNDB2DR delivered in the CMNZMF CNTL library.

The program can also be initiated online from the Db2 Object Dependency Report panel CMNDB2OD, which is accessed from:

  • The Define or Generate ChangeMan Batch Reports panel in global administration at =A.G.R, option 5 Db2

  • The Define or Generate ChangeMan Batch Reports panel in application administration at =A.A.R, option 5 Db2

  • The Submit ChangeMan Batch Reports panel at =6, option 2 Db2.

CMNDB2OD               Db2 Object Dependency Report 
Command ===> __________________________________________________________________

    Package . . . . . . . . . ACTP000070 
    Target Db2 subsystem . .  C105


Job Statement Information: 

    //USER015N JOB (SM-1IKF-SM),'DB2 OBJECT',
    //        CLASS=A,MSGCLASS=X,NOTIFY=USER015
    //*
    //*

This table describes the fields on the Db2 Object Dependency Report panel.

Field Description
Package id Type the ID of the package that you want to analyze. The package is scanned for Db2 components which may contain user defined function or stored procedure definitions. The batch job will analyze the catalog tables in the target Db2 subsystem to see if any objects exist that are dependent on the components in this package.
Target Db2 subsystem Type the identifier of the target Db2 subsystem.
Job Statement Information Specify a valid JOB statement for that batch job that will be submitted when you press Enter.

...

Keyword Options

This table describes the keyword options that control the behavior or program CMNDB2DR.

Keyword Description
TEST= Program trace facility.
Valid values: YES/NO
Default: NO
AUTHID= The userid or qualifier to be used when no explicit schema is provided.
NOTFOUNDRC= Return code set if the requested SQL component (MBR=) is not found in the library concatenation at ddname SQLIN.
Valid values: 0 to 99
Default: 8 Comment: Set to 0 for demotion and backout functions.
INOPRC= Return code set if the automatic DROP will work but will cause dependent objects to be made inoperative.
Valid values: 0 to 99
Default: 8
FAILRC= Valid values: 0 to 99
Default: 8
Return code set if the automatic DROP will fail.
DB2ID= Db2 subsystem ID to which SQL should be presented. The DB2ID keyword control statement must precede one or more MBR keyword control statements.
MBR= Name of SQL component to be processed in the Db2 subsystem specified in the preceding DB2ID keyword control statement.

A new DB2ID control statement makes program CMNDB2DR disconnect from the current subsystem and connect to the new one. The number of MBR control statements that follow a DB2ID control statement is not limited. Report output (excerpts):

ChangeMan(R) ZMF       CMNDB2DR - 8.1.0 DB2 object dependency report 

CMNDB2DR       Processing begins at 15:35:29 on 02/04/2016
------------------------------------------------------------------------------

CMNDB2DR SYSIN: TEST=NO 
CMNDB2DR SYSIN: AUTHID=JPRESTO 
CMNDB2DR SYSIN: NOTFOUNDRC=4 
CMNDB2DR SYSIN: INOPRC=8 
CMNDB2DR SYSIN: FAILRC=8 
CMNDB2DR SYSIN: DB2ID=S10G 

-----------------------------------------------------------------------------

CMNDR014I        Now connected to DB2 subsystem : C105 

------------------------------------------------------------------------------ 

CMNDB2DR SYSIN:  MBR=SQLNAT01 

CMNDR024I        Member contains procedure : JPRESTO.SQLNAT01 
CMNDR025I        No dependencies found for this object 


------------------------------------------------------------------------------
CMNDB2DR         Processing completed at 15:35:30 on 02/04/2016 MAX RC = 00

Return Codes and Messages

Return Code Description
00 Success
04 Warnings Issued
08 DROP errors
12 Parameter errors
16 Other unrecoverable errors

...