Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 18 Sep 2013 @ 14:14:54 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

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;
        Call ThatSP;
     End if;

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 24 Jul 2020