Archives of the TeradataForum
Message Posted: Wed, 18 Sep 2013 @ 14:14:54 GMT
| Subj: | | Re: Global temporary space usage |
| |
| From: | | Rewalt, Daniel |
David Clough wrote:
| | Does anyone know how to derive a list of Nested Procedures for a given Stored Procedure, by any chance ? | |
| | Why I'm asking is because our dba's are asking for the order in which Stored Procedures should be compiled, particularly when they are part
of a nested set. | |
You may be able to find that information in DBQL. This query should provide the "bones":
select querytext, min(starttime)
from dbc.qrylog
where sessionid in (sel sessionid
from dbc.qrylog
where querytext = 'CALL MyDb.MyMasterSp ();') and statementtype = 'CALL'
group by 1
order by 2 desc
You can use the min(starttime) to determine which SP to compile first.
Three caveats:
1 - You need to be capturing enough of the querytext in the dbqlogtbl to identify the execution of the "master" SP, or else you will
have to join to qrysql.
2 - The session could call other SPs. That would mess up the results. You may want to execute a controlled run of the "master" SP
on a dev/test system and pull the results from there.
3 - You may get inaccurate results if the SP has conditions in which it calls one SP or another; i.e., one or more of the SPs in the
chain has code like this:
If ThisCondition = 'Y' then
Call ThisSP;
Else
Call ThatSP;
End if;
| 
