Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 18 Sep 2013 @ 15:32:23 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Global temporary space usage
From:   Dieter Noeth

Daniel Rewalt wrote:

  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

Hey, that's a nice idea :-)

  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.  

This will be no problem, when a SP is CALLed the "RequestNum" will not change for all statements submitted within this CALL, only the InternalRequestNumber increases, so this works event if multiple SPs are called:

     FROM dbc.qrylog
     WHERE (sessionid, RequestNum)
     IN (SEL sessionid, RequestNum
           FROM dbc.qrylog
           WHERE querytext LIKE '%CALL nestedproc%'
           AND statementtype = 'CALL')
     AND statementtype = 'CALL'
     AND RequestNum <> InternalRequestNum -- to exclude the callin SP GROUP BY 1 ORDER BY 2 DESC


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