Toad World® Forums

Toad for DB2 LUW v6.0 B0111 - Obejct Explorer\ Dependencies Tab Doesn't Display all Dependencies

This issue has been around for a while. Image 1 is the dependencies for a stored procedure. Images 2-3 are just a few of the sections of the procedure that show tables/views being used.

Image 1:

Dependencies_1.png

Image 2:

Dependencies_2.png

Image 3:

Dependencies_3.png

Hi, just checking in. I have not heard anything since submitting this “bug” regarding the dependencies issue.

Richard Chouanard, analytics consultant

DB2 Database Administrator

*Payment Operations Management/Float Datamart

*Ph: 480-724-2142

From: richard.j.chouanard_187 [mailto:bounce-richardjchouanard_187@toadworld.com]

Sent: Thursday, August 21, 2014 8:31 AM

To: toaddb2beta@toadworld.com

Subject: [Toad for IBM DB2 - Beta Discussion Forum] Toad for DB2 LUW v6.0 B0111 - Obejct Explorer\ Dependencies Tab Doesn’t Display all Dependencies

Toad for DB2 LUW v6.0 B0111 - Obejct Explorer\ Dependencies Tab Doesn’t Display
all Dependencies

Thread created by richard.j.chouanard_187

This issue has been around for a while. Image 1 is the dependencies for a stored procedure. Images 2-3 are just a few of the sections of the procedure that show tables/views being used.

Image 1:

Image 2:

Image 3:

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for IBM DB2 - Beta
notifications altogether.

Toad for IBM DB2 - Beta Discussion Forum

Flag
this post as spam/abuse.

I opened TDB-1168 to track resolution of this issue.

Thanks

Hi Richard,

I haven’t been able to reproduce this problem. What’s the DB2 version and platform? Can you send me the DDL for the procedure?

Adam

Hi Adam, the database is on a UNIX server running an AIX OS and the DB2 version is 10.1. Below is the procedure.

CREATE
OR REPLACE
PROCEDURE DMA01WK.SSRS_ACH_VOLUME_FROM_STORES ( )

SPECIFIC SSRS_ACH_VOLUME_FROM_STORES

DYNAMIC RESULT SETS
1

LANGUAGE SQL

MODIFIES SQL DATA

P1:
BEGIN

DECLARE V_START_DT
DATE;

DECLARE V_END_DT
DATE;

DECLARE V_START_TS
TIMESTAMP;

DECLARE V_PARAMETERS
VARCHAR(500);

SET V_START_TS

CURRENT
TIMESTAMP;

SET V_START_DT
= (SELECT
MIN(CYCLE_DT)
FROM DMA01.CALENDAR_5YR_VW
WHERE MTH

MONTH(CURRENT
DATE -
3 MONTHS)
AND YR

YEAR(CURRENT
DATE -
3 MONTHS));

SET V_END_DT
= (SELECT
MAX(CYCLE_DT)
FROM DMA01.CALENDAR_5YR_VW
WHERE MTH

MONTH(CURRENT
DATE -
1 MONTHS)
AND YR

YEAR(CURRENT
DATE -
1 MONTHS));

SET V_PARAMETERS
= V_START_DT||’&’||V_END_DT;

–get store au list from svt

DECLARE GLOBAL
TEMPORARY
TABLE SESSION.SVT

  ( BRANCH_AU

CHAR(6) )

ON
COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE;


INSERT
INTO SESSION.SVT

SELECT
DISTINCT

RIGHT(BRANCH_AU,6)
AS BRANCH_AU

FROM DMA01.DLY_SVT_VOLUME

WHERE BUSINESS_DT
BETWEEN V_START_DT
AND V_END_DT;


CREATE
INDEX SESSION.SVT_I01
ON SESSION.SVT ( BRANCH_AU );


CALL SYSPROC.ADMIN_CMD ( ‘RUNSTATS
ON TABLE SESSION.SVT AND DETAILED INDEXES ALL’);

DECLARE GLOBAL
TEMPORARY
TABLE SESSION.FINAL

  (WORK_MONTH

CHAR(7)

  ,STATE

CHAR(2)

  ,SOURCE

VARCHAR(10)

  ,ITEMS

BIGINT

  )

ON
COMMIT PRESERVE ROWS
NOT LOGGED
WITH REPLACE;

Hi Richard,

Now I see. This was a trick question :slight_smile: I guess should have noticed the SESSION schema.

I’m not sure this dependency exists in the catalog. It’s not in packagedep, and the object only exists during the session. Do you know a way of finding this dependency?

Adam