How to findout the SP and UDF hierarchical order? for example one SP may call another SP and one UDF may call another UDF, so I would like to know the order.

Thanks

I guess you could find this information if you explain the corresponding packages.

Check out syscat.routinedep and syscat.packagedep.

Andy

thanks for the replies,

but still how can I find out which SP is calling which SP?

Thanks

Try something like this:

select pd.bschema,pd.bname from syscat.routines as r inner join syscat.routinedep as rd on (r.routineschema = rd.routineschema and r.routinename = rd.routinename) inner join syscat.packagedep as pd on (rd.bschema = pd.pkgschema and rd.bname = pd.pkgname) where r.routineschema = 'MySchema' and r.routinename = 'MyRoutine' and rd.btype = 'K' and pd.btype = 'F'

Read the SQL Reference manual and you should be able to expand on this.

Andy

This sql is not giving the correct result

Can you try this query...

SELECT A.PROCNAME,B.BNAME
from SYSCAT.PROCEDURES A, SYSCAT.PACKAGEDEP B
WHERE SUBSTR(A.IMPLEMENTATION,1,8) = B.PKGNAME
AND BSCHEMA = 'TBLPY00'
AND BTYPE = 'F'
ORDER BY 1,2

NOTE : This query works only if the first 8 characters of IMPLEMENTATION represents the package name.

This SQL is giving UDF name being used in SP, what about SP calls another SP?

Thanks