report of all PARENT/CHILD relationship of Native stored procedures

Sue howe

report of all PARENT/CHILD relationship of Native stored procedures

Hi All

my  manger asked me below

I would like you to produce a report of all PARENT/CHILD relationship of Native stored procedures, only for latest package version based on the DB2 catalog information.

Hint:

SYSPACKDEP stores BNAME for the child and DNAME for parent.

i have developed below query however  it is getting time out, i am sure it needs some fine tuning , could any one of you please let me know the better way i can re-write this query

 

SELECT
SUBSTR(A.DNAME, 1,25) AS PARENT_NSP,
SUBSTR(B.VERSION ,1,7) AS PARENT_VERSION,
SUBSTR(A.BNAME, 1,25) AS CHILD_NSP,
SUBSTR(B.VERSION ,1,7) AS CHILD_VERSION
FROM
SYSIBM.SYSPACKDEP A
INNER JOIN
SYSIBM.SYSROUTINES B
ON
A.BQUALIFIER=B.SCHEMA OR DCOLLID=B.SCHEMA AND
A.DNAME=B.NAME OR A.BNAME=B.NAME
WHERE
B.SCHEMA='AAAAAA' AND
B.ORIGIN='N' AND B.ACTIVE='Y' WITH UR; 

Nadir Doctor

report of all PARENT/CHILD relationship of Native stored procedures
(in response to Sue howe)
Hi Sue,

One helpful option could be to simplify your join criteria and using a
union all or temporary table to store each set of partial results so that
this is then available all together.

Nadir




On Sun, Jul 26, 2020 at 5:32 PM Sue howe <[login to unmask email]> wrote:

> Hi All
>
> my manger asked me below
>
> I would like you to produce a report of all PARENT/CHILD relationship of
> Native stored procedures, only for latest package version based on the DB2
> catalog information.
>
> Hint:
>
> SYSPACKDEP stores BNAME for the child and DNAME for parent.
>
> i have developed below query however it is getting time out, i am sure it
> needs some fine tuning , could any one of you please let me know the better
> way i can re-write this query
>
>
>
> SELECT
> SUBSTR(A.DNAME, 1,25) AS PARENT_NSP,
> SUBSTR(B.VERSION ,1,7) AS PARENT_VERSION,
> SUBSTR(A.BNAME, 1,25) AS CHILD_NSP,
> SUBSTR(B.VERSION ,1,7) AS CHILD_VERSION
> FROM
> SYSIBM.SYSPACKDEP A
> INNER JOIN
> SYSIBM.SYSROUTINES B
> ON
> A.BQUALIFIER=B.SCHEMA OR DCOLLID=B.SCHEMA AND
> A.DNAME=B.NAME OR A.BNAME=B.NAME
> WHERE
> B.SCHEMA='AAAAAA' AND
> B.ORIGIN='N' AND B.ACTIVE='Y' WITH UR;
>
> -----End Original Message-----
>

Peter Vanroose

Re: report of all PARENT/CHILD relationship of Native stored procedures
(in response to Sue howe)

Sue,

There is one minor but important issue with your query: there are parentheses missing, which could explain why the query takes much longer than expected (and would return much more rows than those wanted):

SELECT 
SUBSTR(A.DNAME, 1,25) AS PARENT_NSP,
SUBSTR(B.VERSION ,1,7) AS PARENT_VERSION,
SUBSTR(A.BNAME, 1,25) AS CHILD_NSP,
SUBSTR(B.VERSION ,1,7) AS CHILD_VERSION
FROM SYSIBM.SYSPACKDEP A INNER JOIN SYSIBM.SYSROUTINES B
ON (A.BQUALIFIER=B.SCHEMA OR DCOLLID=B.SCHEMA) AND
(A.DNAME=B.NAME OR A.BNAME=B.NAME)
WHERE B.SCHEMA='AAAAAA' AND B.ORIGIN='N' AND B.ACTIVE='Y'
WITH UR;

Two other ways to get exactly the same result (but in a syntactically different way), which avoids making the above mistake, is by either using UNION ALL (as Nadir Doctor suggested) or by using IN instead of OR:

 SELECT 
'table' AS rowtype,
SUBSTR(A.DNAME, 1,25) AS PARENT_NSP,
SUBSTR(B.VERSION ,1,7) AS PARENT_VERSION,
SUBSTR(A.BNAME, 1,25) AS CHILD_NSP,
SUBSTR(B.VERSION ,1,7) AS CHILD_VERSION
FROM SYSIBM.SYSPACKDEP A INNER JOIN SYSIBM.SYSROUTINES B
ON A.BQUALIFIER=B.SCHEMA AND A.BNAME=B.NAME
WHERE B.SCHEMA='AAAAAA' AND B.ORIGIN='N' AND B.ACTIVE='Y'
AND A.BTYPE IN ('T','V','M','A','S')
UNION ALL
SELECT
'package' AS rowtype,
SUBSTR(A.DNAME, 1,25) AS PARENT_NSP,
SUBSTR(B.VERSION ,1,7) AS PARENT_VERSION,
SUBSTR(A.BNAME, 1,25) AS CHILD_NSP,
SUBSTR(B.VERSION ,1,7) AS CHILD_VERSION
FROM SYSIBM.SYSPACKDEP A INNER JOIN SYSIBM.SYSROUTINES B
ON DCOLLID=B.SCHEMA AND A.DNAME=B.NAME
WHERE B.SCHEMA='AAAAAA' AND B.ORIGIN='N' AND B.ACTIVE='Y'
AND A.DTYPE IN ('N','F','T')
WITH UR;

or

SELECT 
SUBSTR(A.DNAME, 1,25) AS PARENT_NSP,
SUBSTR(B.VERSION ,1,7) AS PARENT_VERSION,
SUBSTR(A.BNAME, 1,25) AS CHILD_NSP,
SUBSTR(B.VERSION ,1,7) AS CHILD_VERSION
FROM SYSIBM.SYSPACKDEP A INNER JOIN SYSIBM.SYSROUTINES B
ON B.SCHEMA IN (A.BQUALIFIER, A.DCOLLID) AND
B.NAME IN (A.DNAME, A.BNAME)
WHERE B.SCHEMA='AAAAAA' AND B.ORIGIN='N' AND B.ACTIVE='Y'
WITH UR;

The latter is less readable than the former: with the two separate WHERE clauses in the UNION ALL version, you more clearly see the two composing parts of the result set, viz. the table (or view or alias) dependencies versus the package (e.g. stored procedure) dependencies.
Also note the extra condition I've added on respectively BTYPE and DTYPE for those two parts.
And the extra column in the output, which indicates to which of the two parts the output row belongs to.

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://abis.be/


In Reply to Sue howe:

my  manger asked me below

I would like you to produce a report of all PARENT/CHILD relationship of Native stored procedures, only for latest package version based on the DB2 catalog information.

Hint:

SYSPACKDEP stores BNAME for the child and DNAME for parent.

i have developed below query however  it is getting time out, i am sure it needs some fine tuning , could any one of you please let me know the better way i can re-write this query

SELECT
SUBSTR(A.DNAME, 1,25) AS PARENT_NSP,
SUBSTR(B.VERSION ,1,7) AS PARENT_VERSION,
SUBSTR(A.BNAME, 1,25) AS CHILD_NSP,
SUBSTR(B.VERSION ,1,7) AS CHILD_VERSION
FROM
SYSIBM.SYSPACKDEP A
INNER JOIN
SYSIBM.SYSROUTINES B
ON
A.BQUALIFIER=B.SCHEMA OR DCOLLID=B.SCHEMA AND
A.DNAME=B.NAME OR A.BNAME=B.NAME
WHERE
B.SCHEMA='AAAAAA' AND
B.ORIGIN='N' AND B.ACTIVE='Y' WITH UR;

Bill Gallagher

[External] report of all PARENT/CHILD relationship of Native stored procedures
(in response to Sue howe)
Taking it one step further, because native stored procedures calls can go multiple levels deep, and they can be called by triggers, UDFs, other NSPs, etc., I wrote this table UDF last year. It starts with the routine that you pass as a parameter, and traverses up through all parents which call it, and recursively though the parents which call those, etc.

If you wanted to also go down through children (if any), then you would need to update accordingly:

--#SET TERMINATOR #

CREATE FUNCTION DBA.RTNE_DEPN (V_SCHEMA VARCHAR(128),
V_NAME VARCHAR(128))
RETURNS TABLE (LEVEL SMALLINT,
CHILD_SCHEMA VARCHAR(128),
CHILD_NAME VARCHAR(128),
CHILD_TYPE VARCHAR(9),
PARENT_SCHEMA VARCHAR(128),
PARENT_NAME VARCHAR(128),
PARENT_TYPE VARCHAR(9))

BEGIN ATOMIC

RETURN
WITH CHLDPRNT (CHILDSCHEMA, CHILDNAME, CHILDTYPE,
PARENTSCHEMA, PARENTNAME, PARENTTYPE) AS
(SELECT C.SCHEMA, C.NAME, C.ROUTINETYPE,
P.SCHEMA, P.NAME, P.ROUTINETYPE
FROM SYSIBM.SYSROUTINES C,
SYSIBM.SYSPACKDEP PD,
SYSIBM.SYSROUTINES P
WHERE C.ACTIVE = 'Y'
AND PD.BQUALIFIER = C.SCHEMA
AND PD.BNAME = C.SPECIFICNAME
AND PD.BTYPE IN ('F', 'O')
AND PD.DTYPE IN ('F', 'N')
AND P.SCHEMA = PD.DCOLLID
AND P.SPECIFICNAME = PD.DNAME
AND P.ACTIVE = 'Y'
UNION
SELECT C.SCHEMA, C.NAME, C.ROUTINETYPE,
PD.DCOLLID, PD.DNAME, PD.DTYPE
FROM SYSIBM.SYSROUTINES C,
SYSIBM.SYSPACKDEP PD
WHERE C.ACTIVE = 'Y'
AND PD.BQUALIFIER = C.SCHEMA
AND PD.BNAME = C.SPECIFICNAME
AND PD.BTYPE IN ('F', 'O')
AND PD.DTYPE IN ('T')),
RECUR (LEVEL, CHILDSCHEMA, CHILDNAME, CHILDTYPE,
PARENTSCHEMA, PARENTNAME, PARENTTYPE) AS
(SELECT 0, CHILDSCHEMA, CHILDNAME, CHILDTYPE,
PARENTSCHEMA, PARENTNAME, PARENTTYPE
FROM CHLDPRNT ROOT
WHERE ROOT.CHILDSCHEMA = V_SCHEMA
AND ROOT.CHILDNAME = V_NAME
UNION ALL
SELECT P.LEVEL+1, C.CHILDSCHEMA, C.CHILDNAME, C.CHILDTYPE,
C.PARENTSCHEMA, C.PARENTNAME, C.PARENTTYPE
FROM RECUR P, CHLDPRNT C
WHERE P.PARENTSCHEMA = C.CHILDSCHEMA
AND P.PARENTNAME = C.CHILDNAME)
SELECT LEVEL, CHILDSCHEMA, CHILDNAME,
CASE CHILDTYPE
WHEN 'F' THEN 'FUNCTION'
WHEN 'P' THEN 'PROCEDURE'
WHEN 'T' THEN 'TRIGGER'
END AS CHILDTYPE, PARENTSCHEMA, PARENTNAME,
CASE PARENTTYPE
WHEN 'F' THEN 'FUNCTION'
WHEN 'P' THEN 'PROCEDURE'
WHEN 'T' THEN 'TRIGGER'
END AS CHILDTYPE
FROM RECUR;

END #

--#SET TERMINATOR ;

Bill Gallagher | Senior Systems Engineer, DBA

From: Sue howe <[login to unmask email]>
Sent: Sunday, July 26, 2020 6:32 PM
To: [login to unmask email]
Subject: [External] [DB2-L] - report of all PARENT/CHILD relationship of Native stored procedures

***External Sender - Please Exercise Caution***


Hi All

my manger asked me below

I would like you to produce a report of all PARENT/CHILD relationship of Native stored procedures, only for latest package version based on the DB2 catalog information.

Hint:

SYSPACKDEP stores BNAME for the child and DNAME for parent.

i have developed below query however it is getting time out, i am sure it needs some fine tuning , could any one of you please let me know the better way i can re-write this query



SELECT
SUBSTR(A.DNAME, 1,25) AS PARENT_NSP,
SUBSTR(B.VERSION ,1,7) AS PARENT_VERSION,
SUBSTR(A.BNAME, 1,25) AS CHILD_NSP,
SUBSTR(B.VERSION ,1,7) AS CHILD_VERSION
FROM
SYSIBM.SYSPACKDEP A
INNER JOIN
SYSIBM.SYSROUTINES B
ON
A.BQUALIFIER=B.SCHEMA OR DCOLLID=B.SCHEMA AND
A.DNAME=B.NAME OR A.BNAME=B.NAME
WHERE
B.SCHEMA='AAAAAA' AND
B.ORIGIN='N' AND B.ACTIVE='Y' WITH UR;

-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Sue howe

RE: [External] report of all PARENT/CHILD relationship of Native stored procedures
(in response to Bill Gallagher)

thank you very much Nadir,peter and Bill

i am not strong @ coding Sql's, your inputs showed some light , let me take a look and get back

and after Bill reply, i just realized native stored procedures calls can go multiple levels deep and they can be called by triggers, UDFs,

https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_drop.html

 

also my manager asked me  to Develop a process to free older/inactive version(s) of Native Stored procedures( after Bill's reply looks like it is not just straight forward to drop a NSP as we have to check for dependencies right bill?

-

Sue

 

 

Bill Gallagher

[External] report of all PARENT/CHILD relationship of Native stored procedures
(in response to Sue howe)
Sue,

I think (but am not 100% positive without testing) that you should be able to drop inactive versions NSPs regardless of dependencies, because the dependencies only exist to the currently active version of the NSP.

If that’s not correct, then that greatly complicates the process for cleanup of old/inactive versions.

Bill Gallagher | Senior Systems Engineer, DBA

From: Sue howe <[login to unmask email]>
Sent: Monday, July 27, 2020 9:36 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [External] report of all PARENT/CHILD relationship of Native stored procedures

***External Sender - Please Exercise Caution***


thank you very much Nadir,peter and Bill

i am not strong @ coding Sql's, your inputs showed some light , let me take a look and get back

and after Bill reply, i just realized native stored procedures calls can go multiple levels deep and they can be called by triggers, UDFs,

https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/sqlref/src/tpc/db2z_sql_drop.html



also my manager asked me to Develop a process to free older/inactive version(s) of Native Stored procedures( after Bill's reply looks like it is not just straight forward to drop a NSP as we have to check for dependencies right bill?

-

Sue





-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Sue howe

RE: [External] report of all PARENT/CHILD relationship of Native stored procedures
(in response to Bill Gallagher)

Bill –

thank you very much for your timely response

I am trying to understand  the query

to be honest I am not too confident with sql’s, Could you help me where I need change as per your note “If you wanted to also go down through children (if any), then you would need to update accordingly”

 

-

Regards

Sue

Sue howe

RE: [External] report of all PARENT/CHILD relationship of Native stored procedures
(in response to Sue howe)

Hello Bill- if you don't mind could you reply to my latest comment ?

 

 

--

Sue

Bill Gallagher

[External] report of all PARENT/CHILD relationship of Native stored procedures
(in response to Sue howe)
Hi Sue,

I’ve tagged your message with the hope of getting to it soon, but honestly, I don’t have the free cycles to even look at it now. That was the first (and only) recursive SQL I’ve written, and it took a while for me to wrap my head around what it was doing when I wrote it. That was over a year ago. It would take time for me just to refamiliarize myself with it, and then figure out what needs to be changed. And I just don’t have the cycles to do that right now.

Sorry.

Bill Gallagher | Senior Systems Engineer, DBA

From: Sue howe <[login to unmask email]>
Sent: Thursday, July 30, 2020 9:47 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [External] report of all PARENT/CHILD relationship of Native stored procedures

***External Sender - Please Exercise Caution***


Hello Bill- if you don't mind could you reply to my latest comment ?





--

Sue

-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Sue howe

RE: [External] report of all PARENT/CHILD relationship of Native stored procedures
(in response to Bill Gallagher)

Sure Bill , i understand , thanks for rely 

general question:

 Is it worth or any space saving will happen of we drop any inactive native stored procedures ?, that too my manager wants to keep total 4 versions ( 1 active version of NSP+ 3 latest inactive versions of NSP) and drop the rest

 

Will there be any $ savings or benefit to CPU/zIIP/space if we drop inactive native stored procedures(because dropping NSP is not so straight forward as we know native stored procedures calls can go multiple levels deep calling another NSP’s)

 

--

Sue

Bill Gallagher

[External] report of all PARENT/CHILD relationship of Native stored procedures
(in response to Sue howe)
Sue,

I wouldn’t think you would see any noticeable space or CPU savings by dropping the inactive routines. It’s just deleting rows in your catalog and freeing the corresponding DB2 packages in the directory. Unless you’re talking about hundreds of thousands of routines.

The biggest benefit of cleanup is just the uncluttering of your DB2 catalog and directory, getting rid of the old stuff that’s no longer needed. Generally, that’s a good thing, especially as you move forward with installing new releases or function levels of DB2. Old stuff is usually what causes the most headaches at migration time, especially when objects or functionality is being deprecated.

Bill Gallagher | Senior Systems Engineer, DBA

From: Sue howe <[login to unmask email]>
Sent: Thursday, July 30, 2020 10:24 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [External] report of all PARENT/CHILD relationship of Native stored procedures

***External Sender - Please Exercise Caution***


Sure Bill , i understand , thanks for rely

general question:

Is it worth or any space saving will happen of we drop any inactive native stored procedures ?, that too my manager wants to keep total 4 versions ( 1 active version of NSP+ 3 latest inactive versions of NSP) and drop the rest



Will there be any $ savings or benefit to CPU/zIIP/space if we drop inactive native stored procedures(because dropping NSP is not so straight forward as we know native stored procedures calls can go multiple levels deep calling another NSP’s)



--

Sue

-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Sue howe

RE: [External] report of all PARENT/CHILD relationship of Native stored procedures
(in response to Bill Gallagher)
thank you Bill for sharing ideas and shedding some light... appreciate your kind help
i am the process of referring docs, but want to ask this
my manager kept this new requirement in front of me
he want to just keep total 4 versions ( 1 active version of NSP+ 3 latest inactive versions of NSP) and drop the rest( example below)
Active NSP XYZ created July/30 (don't drop)
Inactive NSP XYZ created July/ 29(don't drop)
Inactive NSP XYZ created July/ 28(don't drop)
Inactive NSP XYZ created July/ 27(don't drop)
Inactive NSP XYZ created July/ 26(drop)
Inactive NSP XYZ created July/ 25(drop
how we can achieve this ?(above dates are example only, in my shop inactive versions exist year back aswell)
any thoughts ?
--
Sue

Bill Gallagher

[External] report of all PARENT/CHILD relationship of Native stored procedures
(in response to Sue howe)
Sue,

You can probably use the RANK function to rank the inactive versions of a particular NSP in descending order by timestamp, and drop the ones with a rank value of 4 or greater.

Here’s some info with examples on the RANK function:

https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/apsg/src/tpc/db2z_rankrows.html

Bill Gallagher | Senior Systems Engineer, DBA

From: Sue howe <[login to unmask email]>
Sent: Thursday, July 30, 2020 10:56 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [External] report of all PARENT/CHILD relationship of Native stored procedures

***External Sender - Please Exercise Caution***

thank you Bill for sharing ideas and shedding some light... appreciate your kind help
i am the process of referring docs, but want to ask this
my manager kept this new requirement in front of me
he want to just keep total 4 versions ( 1 active version of NSP+ 3 latest inactive versions of NSP) and drop the rest( example below)
Active NSP XYZ created July/30 (don't drop)
Inactive NSP XYZ created July/ 29(don't drop)
Inactive NSP XYZ created July/ 28(don't drop)
Inactive NSP XYZ created July/ 27(don't drop)
Inactive NSP XYZ created July/ 26(drop)
Inactive NSP XYZ created July/ 25(drop)
how we can achieve this ?(above dates are example only, in my shop inactive versions exist year back aswell)
any thoughts ?
--
Sue

-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Sue howe

RE: [External] report of all PARENT/CHILD relationship of Native stored procedures
(in response to Sue howe)

Sure Bill that helps, i will work that 

btw i did some ground work,

-in our shop we only have NSP calling another NSP, in this scenario 

-i could see the same underling packages called by inactive and active NSP, so if i drop inactive NSP, the packages will be come invalid?

-and inactive NSP and active NSP are calling same other NSP's, so if i drop inactive NSP what will happen to called NSP's ?

 

--

Sue

 

 

 

Bill Gallagher

[External] report of all PARENT/CHILD relationship of Native stored procedures
(in response to Sue howe)
Sue,

Each NSP will have a version number associated with it. The package associated with each version will also have that same version number associated with it.

If you drop a version of an NSP, I believe the associated package version gets freed as well.

If you drop a “parent” NSP (one that calls another NSP), nothing happens to the “child” (i.e. called) NSP. They are independent of each other, there is no cascading drops.

Bill Gallagher | Senior Systems Engineer, DBA

From: Sue howe <[login to unmask email]>
Sent: Thursday, July 30, 2020 2:55 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: [External] report of all PARENT/CHILD relationship of Native stored procedures

***External Sender - Please Exercise Caution***


Sure Bill that helps, i will work that

btw i did some ground work,

-in our shop we only have NSP calling another NSP, in this scenario

-i could see the same underling packages called by inactive and active NSP, so if i drop inactive NSP, the packages will be come invalid?

-and inactive NSP and active NSP are calling same other NSP's, so if i drop inactive NSP what will happen to called NSP's ?



--

Sue







-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Peter Vanroose

Re: report of all PARENT/CHILD relationship of Native stored procedures
(in response to Sue howe)

Here is Bill's query (a little rewritten for performance reasons, e.g. avoiding infinite loop on cyclic call dependencies).
The original version started from a single procedure (parameters V_SCHEMA and V_NAME) and recurses into its (grand...)parents;
I have removed that procedure name dependency (now in comments), so this query will show all recursive dependencies of all procedures.
Note: level=0 means: direct call. level=100 most likely means: cyclic call, or self-referencing procedure.

WITH CHLDPRNT (CHILDSCHEMA, CHILDNAME, CHILDTYPE, PARENTSCHEMA, PARENTNAME, PARENTTYPE) AS
(SELECT C.SCHEMA, C.NAME, C.ROUTINETYPE, P.SCHEMA, P.NAME, P.ROUTINETYPE
FROM SYSIBM.SYSROUTINES C
JOIN SYSIBM.SYSPACKDEP PD ON PD.BQUALIFIER = C.SCHEMA AND PD.BNAME = C.SPECIFICNAME
JOIN SYSIBM.SYSROUTINES P ON P.SCHEMA = PD.DCOLLID AND P.SPECIFICNAME = PD.DNAME
WHERE C.ACTIVE = 'Y'
AND P.ACTIVE = 'Y'
AND PD.BTYPE IN ('F', 'O')
AND PD.DTYPE IN ('F', 'N')
-- AND C.SCHEMA = V_SCHEMA
-- AND C.NAME = V_NAME
UNION ALL
SELECT C.SCHEMA, C.NAME, C.ROUTINETYPE, PD.DCOLLID, PD.DNAME, PD.DTYPE
FROM SYSIBM.SYSROUTINES C
JOIN SYSIBM.SYSPACKDEP PD ON PD.BQUALIFIER = C.SCHEMA AND PD.BNAME = C.SPECIFICNAME
WHERE C.ACTIVE = 'Y'
AND PD.BTYPE IN ('F', 'O')
AND PD.DTYPE = 'T'
-- AND C.SCHEMA = V_SCHEMA
-- AND C.NAME = V_NAME
),
RECUR (LEVEL, CHILDSCHEMA, CHILDNAME, CHILDTYPE, PARENTSCHEMA, PARENTNAME, PARENTTYPE) AS
(SELECT 0, CHILDSCHEMA, CHILDNAME, CHILDTYPE, PARENTSCHEMA, PARENTNAME, PARENTTYPE
FROM CHLDPRNT ROOT
UNION ALL
SELECT LEVEL+1, C.CHILDSCHEMA, C.CHILDNAME, C.CHILDTYPE, C.PARENTSCHEMA, C.PARENTNAME, C.PARENTTYPE
FROM RECUR P JOIN CHLDPRNT C
ON P.PARENTSCHEMA = C.CHILDSCHEMA AND P.PARENTNAME = C.CHILDNAME
WHERE LEVEL < 100
)
SELECT LEVEL, CHILDSCHEMA, CHILDNAME,
CASE CHILDTYPE
WHEN 'F' THEN 'Function'
WHEN 'P' THEN 'Procedure'
WHEN 'T' THEN 'Trigger'
END AS CHILD_TYPE, PARENTSCHEMA, PARENTNAME,
CASE PARENTTYPE
WHEN 'F' THEN 'Function'
WHEN 'P' THEN 'Procedure'
WHEN 'T' THEN 'Trigger'
END AS PARENT_TYPE
FROM RECUR
;

The output of this query is at the same time a child-(grand)parent list and a parent-(grand)child list, of course!

To recur into "children" SPs of a single procedure (V_SCHEMA,V_NAME) instead, the following recursive query could be used:

WITH CHLDPRNT (CHILDSCHEMA, CHILDNAME, CHILDTYPE, PARENTSCHEMA, PARENTNAME, PARENTTYPE) AS
(SELECT C.SCHEMA, C.NAME, C.ROUTINETYPE, P.SCHEMA, P.NAME, P.ROUTINETYPE
FROM SYSIBM.SYSROUTINES C
JOIN SYSIBM.SYSPACKDEP PD ON PD.BQUALIFIER = C.SCHEMA AND PD.BNAME = C.SPECIFICNAME
JOIN SYSIBM.SYSROUTINES P ON P.SCHEMA = PD.DCOLLID AND P.SPECIFICNAME = PD.DNAME
WHERE C.ACTIVE = 'Y'
AND P.ACTIVE = 'Y'
AND PD.BTYPE IN ('F', 'O')
AND PD.DTYPE IN ('F', 'N')
AND P.SCHEMA = V_SCHEMA -- e.g. 'PROD'
AND P.NAME = V_NAME -- e.g. 'PROC_NAME'
),
RECUR (LEVEL, CHILDSCHEMA, CHILDNAME, CHILDTYPE, PARENTSCHEMA, PARENTNAME, PARENTTYPE) AS
(SELECT 0, CHILDSCHEMA, CHILDNAME, CHILDTYPE, PARENTSCHEMA, PARENTNAME, PARENTTYPE
FROM CHLDPRNT ROOT
UNION ALL
SELECT LEVEL+1, C.CHILDSCHEMA, C.CHILDNAME, C.CHILDTYPE, C.PARENTSCHEMA, C.PARENTNAME, C.PARENTTYPE
FROM RECUR C JOIN CHLDPRNT P
ON P.PARENTSCHEMA = C.CHILDSCHEMA AND P.PARENTNAME = C.CHILDNAME
WHERE LEVEL < 100
)
SELECT LEVEL, CHILDSCHEMA, CHILDNAME,
CASE CHILDTYPE
WHEN 'F' THEN 'Function'
WHEN 'P' THEN 'Procedure'
WHEN 'T' THEN 'Trigger'
END AS CHILD_TYPE, PARENTSCHEMA, PARENTNAME,
CASE PARENTTYPE
WHEN 'F' THEN 'Function'
WHEN 'P' THEN 'Procedure'
WHEN 'T' THEN 'Trigger'
END AS PARENT_TYPE
FROM RECUR
;


In Reply to Sue Howe:

Bill –

thank you very much for your timely response

I am trying to understand  the query

to be honest I am not too confident with sql’s, Could you help me where I need change as per your note “If you wanted to also go down through children (if any), then you would need to update accordingly”


--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://abis.be/

Sue howe

Re: report of all PARENT/CHILD relationship of Native stored procedures
(in response to Peter Vanroose)

Thank you Bill for info, you are good at helping.

Peter- wow !! thank you for taking time and coding query , really helps to some one like me not too confident at coding good logic

 

really appreciate dear Db2 SME's

 

--

Sue

 

 

Sue L howe

Re: report of all PARENT/CHILD relationship of Native stored procedures
(in response to Peter Vanroose)

Hey Peter- i am trying to find reason of SQLCODE = -104 from past couple of days but i didn't succeeded, could you check and help any thing typo in sql ?

 

CREATE FUNCTION Test.test1(V_SCHEMA VARCHAR(128),      

V_NAME VARCHAR(128))                                             

RETURNS TABLE (LEVEL SMALLINT,                                   

CHILD_SCHEMA VARCHAR(128),                                       

CHILD_NAME VARCHAR(128),                                         

CHILD_TYPE VARCHAR(9),                                           

PARENT_SCHEMA VARCHAR(128),                                      

PARENT_NAME VARCHAR(128),                                        

PARENT_TYPE VARCHAR(9))                                           

LANGUAGE SQL                                                     

BEGIN ATOMIC                                                     

RETURN                                                           

WITH CHLDPRNT (CHILDSCHEMA, CHILDNAME, CHILDTYPE,                

PARENTSCHEMA, PARENTNAME, PARENTTYPE) AS                         

(SELECT C.SCHEMA, C.NAME, C.ROUTINETYPE, P.SCHEMA, P.NAME,       

 P.ROUTINETYPE                                                   

   FROM      SYSIBM.SYSROUTINES C                                 

        JOIN SYSIBM.SYSPACKDEP PD ON                            

PD.BQUALIFIER = C.SCHEMA AND PD.BNAME = C.SPECIFICNAME          

        JOIN SYSIBM.SYSROUTINES P                               

ON P.SCHEMA = PD.DCOLLID AND P.SPECIFICNAME = PD.DNAME          

  WHERE C.ACTIVE = 'Y'                                          

    AND P.ACTIVE = 'Y'                                           

AND C.SCHEMA='test' AND P.SCHEMA='test'                   

    AND PD.BTYPE IN ('F', 'O')                                  

    AND PD.DTYPE IN ('F', 'N')                                  

--  AND C.SCHEMA = V_SCHEMA                                      

--  AND C.NAME = V_NAME                                         

UNION ALL                                                       

 SELECT C.SCHEMA, C.NAME, C.ROUTINETYPE, PD.DCOLLID,            

PD.DNAME, PD.DTYPE                                               

   FROM      SYSIBM.SYSROUTINES C                               

        JOIN SYSIBM.SYSPACKDEP PD ON PD.BQUALIFIER = C.SCHEMA   

AND PD.BNAME = C.SPECIFICNAME                                   

  WHERE C.ACTIVE = 'Y' AND C.SCHEMA='test'                   

    AND PD.BTYPE IN ('F', 'O')                                  

    AND PD.DTYPE = 'T'                                          

--  AND C.SCHEMA = V_SCHEMA                                     

--  AND C.NAME = V_NAME                                          

),                                                              

RECUR (LEVEL, CHILDSCHEMA, CHILDNAME, CHILDTYPE,                

PARENTSCHEMA, PARENTNAME, PARENTTYPE) AS                        

(SELECT 0, CHILDSCHEMA, CHILDNAME, CHILDTYPE,                   

PARENTSCHEMA, PARENTNAME, PARENTTYPE                            

   FROM CHLDPRNT ROOT                                           

UNION ALL                                                       

 SELECT LEVEL+1, C.CHILDSCHEMA, C.CHILDNAME,                    

C.CHILDTYPE, C.PARENTSCHEMA, C.PARENTNAME, C.PARENTTYPE         

   FROM RECUR P JOIN CHLDPRNT C                                 

        ON P.PARENTSCHEMA = C.CHILDSCHEMA AND                    

P.PARENTNAME = C.CHILDNAME                                      

  WHERE LEVEL < 100                                             

)                                                               

SELECT LEVEL, CHILDSCHEMA, CHILDNAME,                     

       CASE CHILDTYPE                                    

            WHEN 'F' THEN 'FUNCTION'                     

            WHEN 'P' THEN 'PROCEDURE'                    

            WHEN 'T' THEN 'TRIGGER'                      

       END AS CHILD_TYPE, PARENTSCHEMA, PARENTNAME,      

       CASE PARENTTYPE                                   

            WHEN 'F' THEN 'FUNCTION'                     

            WHEN 'P' THEN 'PROCEDURE'                    

            WHEN 'T' THEN 'TRIGGER'                      

       END AS PARENT_TYPE                                

FROM RECUR;                                              

END#                                                     

 

---------+---------+---------+---------+---------+---------+---------+---------+

DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "<END-OF-STATEMENT>". SOME     

         SYMBOLS THAT MIGHT BE LEGAL ARE: . <IDENTIFIER> JOIN INNER LEFT RIGHT 

         FULL CROSS , HAVING GROUP                                             

DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                               

DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR                   

DSNT416I SQLERRD    = 3 0  0  -1  4614  502 SQL DIAGNOSTIC INFORMATION         

DSNT416I SQLERRD    = X'00000003'  X'00000000'  X'00000000'  X'FFFFFFFF'       

         X'00001206'  X'000001F6' SQL DIAGNOSTIC INFORMATION                   

Peter Vanroose

Re: report of all PARENT/CHILD relationship of Native stored procedures
(in response to Sue L howe)

I believe you should just remove the ";" after "FROM RECUR".

Your function definition is "ATOMIC" so it can just have a single statement (the RETURN), so no syntactic need for ";"

Well, maybe the ";" is picked up by the client, in case you forgot to set the statement terminator to "#". Then Db2 does not even see it and actually complains about a missing "END FUNCTION".

In both cases, removing the ";" (and the "#") should solve the problem

In Reply to Sue L howe:

Hey Peter- i am trying to find reason of SQLCODE = -104 from past couple of days but i didn't succeeded, could you check and help any thing typo in sql ?

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://abis.be/

Peter Vanroose

Re: report of all PARENT/CHILD relationship of Native stored procedures
(in response to Sue L howe)

Also, for your use of the query inside a function with two parameters V_SCHEMA and V_NAME, you should remove comment signs "--" four times, to activate filtering on the parameter values.

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://abis.be/

Peter Vanroose

Re: report of all PARENT/CHILD relationship of Native stored procedures
(in response to Peter Vanroose)

Sue:

Correction, I was wrong: the ";" should be there since the body of a table function is SQL PL.

So you should instead just change the terminator setting of your SQL client (e.g. SPUFI) to "#" (without the quotes).

Below is a SPUFI-friendly version of the modified function definition (now using the two parameters, and removed the hardcoded schema name test):

--#SET TERMINATOR #
CREATE FUNCTION proc_dependencies(V_SCHEMA VARCHAR(128),
V_NAME VARCHAR(128))
RETURNS TABLE (LEVEL SMALLINT,
CHILD_SCHEMA VARCHAR(128),
CHILD_NAME VARCHAR(128),
CHILD_TYPE VARCHAR(9),
PARENT_SCHEMA VARCHAR(128),
PARENT_NAME VARCHAR(128),
PARENT_TYPE VARCHAR(9))
LANGUAGE SQL
BEGIN ATOMIC
RETURN
WITH CHLDPRNT (CHILDSCHEMA, CHILDNAME, CHILDTYPE,
PARENTSCHEMA, PARENTNAME, PARENTTYPE) AS
(SELECT C.SCHEMA, C.NAME, C.ROUTINETYPE,
P.SCHEMA, P.NAME, P.ROUTINETYPE
FROM SYSIBM.SYSROUTINES C
JOIN SYSIBM.SYSPACKDEP PD
ON PD.BQUALIFIER = C.SCHEMA
AND PD.BNAME = C.SPECIFICNAME
JOIN SYSIBM.SYSROUTINES P
ON P.SCHEMA = PD.DCOLLID
AND P.SPECIFICNAME = PD.DNAME
WHERE C.ACTIVE = 'Y'
AND P.ACTIVE = 'Y'
AND PD.BTYPE IN ('F', 'O')
AND PD.DTYPE IN ('F', 'N')
AND C.SCHEMA = V_SCHEMA
AND C.NAME = V_NAME
UNION ALL
SELECT C.SCHEMA, C.NAME, C.ROUTINETYPE,
PD.DCOLLID, PD.DNAME, PD.DTYPE
FROM SYSIBM.SYSROUTINES C
JOIN SYSIBM.SYSPACKDEP PD
ON PD.BQUALIFIER = C.SCHEMA
AND PD.BNAME = C.SPECIFICNAME
WHERE C.ACTIVE = 'Y'
AND PD.BTYPE IN ('F', 'O')
AND PD.DTYPE = 'T'
AND C.SCHEMA = V_SCHEMA
AND C.NAME = V_NAME
),
RECUR (LEVEL, CHILDSCHEMA, CHILDNAME, CHILDTYPE,
PARENTSCHEMA, PARENTNAME, PARENTTYPE) AS
(SELECT 0, CHILDSCHEMA, CHILDNAME, CHILDTYPE,
PARENTSCHEMA, PARENTNAME, PARENTTYPE
FROM CHLDPRNT ROOT
UNION ALL
SELECT LEVEL+1, C.CHILDSCHEMA, C.CHILDNAME,
C.CHILDTYPE, C.PARENTSCHEMA,
C.PARENTNAME, C.PARENTTYPE
FROM RECUR P JOIN CHLDPRNT C
ON P.PARENTSCHEMA = C.CHILDSCHEMA
AND P.PARENTNAME = C.CHILDNAME
WHERE LEVEL < 100
)
SELECT LEVEL, CHILDSCHEMA, CHILDNAME,
CASE CHILDTYPE
WHEN 'F' THEN 'FUNCTION'
WHEN 'P' THEN 'PROCEDURE'
WHEN 'T' THEN 'TRIGGER'
END AS CHILD_TYPE,
PARENTSCHEMA, PARENTNAME,
CASE PARENTTYPE
WHEN 'F' THEN 'FUNCTION'
WHEN 'P' THEN 'PROCEDURE'
WHEN 'T' THEN 'TRIGGER'
END AS PARENT_TYPE
FROM RECUR;
END
#

 

Hey Peter- i am trying to find reason of SQLCODE = -104 from past couple of days but i didn't succeeded, could you check and help any thing typo in sql ?

P.S. I haven't checked the correct functioning of that function yet -- it creates fine on my system so no syntax errors anymore ;-)

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://abis.be/

Peter Schwarcz

Re: report of all PARENT/CHILD relationship of Native stored procedures
(in response to Peter Vanroose)

Hi Sue, 

Here is a working version of your code that I ran through spufi on a DB2 V12 sub-system.

DROP FUNCTION PETER.TABLIST ;

CREATE FUNCTION PETER.TABLIST (V_SCHEMA VARCHAR (128)
,V_NAME VARCHAR(128))
RETURNS TABLE (LEVEL SMALLINT
,CHILD_SCHEMA VARCHAR(128)
,CHILD_NAME VARCHAR(128)
,CHILD_TYPE VARCHAR(9)
,PARENT_SCHEMA VARCHAR(128)
,PARENT_NAME VARCHAR(128)
,PARENT_TYPE VARCHAR(9))
LANGUAGE SQL
SPECIFIC FN_TABLIST
NOT DETERMINISTIC
READS SQL DATA
RETURN

WITH CHLDPRNT (CHILDSCHEMA, CHILDNAME, CHILDTYPE,
PARENTSCHEMA, PARENTNAME, PARENTTYPE) AS
(SELECT C.SCHEMA
, C.NAME
, C.ROUTINETYPE
, P.SCHEMA
, P.NAME
, P.ROUTINETYPE
FROM SYSIBM.SYSROUTINES C
JOIN SYSIBM.SYSPACKDEP PD
ON PD.BQUALIFIER = C.SCHEMA
AND PD.BNAME = C.SPECIFICNAME
JOIN SYSIBM.SYSROUTINES P
ON P.SCHEMA = PD.DCOLLID
AND P.SPECIFICNAME = PD.DNAME
WHERE C.ACTIVE = 'Y'
AND P.ACTIVE = 'Y'
AND C.SCHEMA = V_SCHEMA
AND P.SCHEMA = V_SCHEMA
AND PD.BTYPE IN ('F', 'O')
AND PD.DTYPE IN ('F', 'N')
AND C.SCHEMA = V_SCHEMA
AND C.NAME = V_NAME

UNION ALL

SELECT C.SCHEMA, C.NAME, C.ROUTINETYPE, PD.DCOLLID,
PD.DNAME, PD.DTYPE
FROM SYSIBM.SYSROUTINES C
JOIN SYSIBM.SYSPACKDEP PD
ON PD.BQUALIFIER = C.SCHEMA
AND PD.BNAME = C.SPECIFICNAME
WHERE C.ACTIVE = 'Y'
AND C.SCHEMA = V_SCHEMA
AND PD.BTYPE IN ('F', 'O')
AND PD.DTYPE = 'T'
AND C.SCHEMA = V_SCHEMA
AND C.NAME = V_NAME
) ,

RECUR (LVL, CHILDSCHEMA, CHILDNAME, CHILDTYPE,
PARENTSCHEMA, PARENTNAME, PARENTTYPE) AS
(SELECT 0, CHILDSCHEMA, CHILDNAME, CHILDTYPE,
PARENTSCHEMA, PARENTNAME, PARENTTYPE
FROM CHLDPRNT ROOT
UNION ALL
SELECT LVL+1, C.CHILDSCHEMA, C.CHILDNAME,
C.CHILDTYPE, C.PARENTSCHEMA, C.PARENTNAME, C.PARENTTYPE
FROM RECUR P
JOIN CHLDPRNT C
ON P.PARENTSCHEMA = C.CHILDSCHEMA
AND P.PARENTNAME = C.CHILDNAME
WHERE LVL < 100 )

SELECT LVL, CHILDSCHEMA, CHILDNAME,
CASE CHILDTYPE
WHEN 'F' THEN 'FUNCTION'
WHEN 'P' THEN 'PROCEDURE'
WHEN 'T' THEN 'TRIGGER'
END AS CHILD_TYPE,
PARENTSCHEMA,
PARENTNAME,
CASE PARENTTYPE
WHEN 'F' THEN 'FUNCTION'
WHEN 'P' THEN 'PROCEDURE'
WHEN 'T' THEN 'TRIGGER'
END AS PARENT_TYPE
FROM RECUR
;

SELECT * FROM TABLE ( PETER.TABLIST( 'SYSIBM', 'SYSTABLES' ) ) AS A
;