Archives of the TeradataForum

Message Posted: Fri, 04 Jan 2013 @ 21:25:13 GMT

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

David is correct, even for the DBQL Step Information. As stated in the manual:

DBQLStepTbl is populated if you specify the WITH STEPINFO option. When the query completes, the system logs one row for each query step, including parallel steps.

I have seen instances, however, in which you can use the PMPC to glean information on queries while they are in flight. Below is an example.

-dave.clark

     List all sessions with an active AMP request
     --------------------------------------------

     SEL HostId (TITLE 'Host',FORMAT 'ZZZ9'),
         UserName (TITLE 'Active Users'),
         SessionNo (TITLE 'Session'),
         LogonPENo (TITLE 'IFP')
     FROM TABLE (SYSLIB.MonitorSession(-1,'*',0)) AS t1
     WHERE AmpState='ACTIVE'
     ORDER BY 1,2;


     Host  Active Users                  Session     IFP
     ----  ------------------------  -----------  ------
        1  DBC                            128988   16383



     Plug the Host, Session and PE numbers below....
     -----------------------------------------------


     List active SQL requests
     ------------------------
     SELECT SessionNo (TITLE 'Session'),
            Substring (SQLTxt FROM 1 FOR 100) (TITLE 'Active Query')
     FROM TABLE (syslib.MonitorSQLText(1, 128988, 16383)) AS t2
     ORDER BY 1;


         Session Active Query
     ----------- ----------------------------------------------------
          128988 sel count(*) from dbc.tvm a, dbc.tvm b, dbc.tvm c;


     Identify the current step
     -------------------------
     SELECT *
     FROM
     TABLE(syslib.MonitorSQLCurrentStep(1, 128988, 16383)) as t1;


     HostId    SessionNo  NumOfSteps  CurLvl1StepNo  CurLvl2StepNo
     ------  -----------  ----------  -------------  -------------
          1       128988           7              4              4



     Identify step detail
     --------------------
     .SET WIDTH 300
     .SET FOLDLINE
     .SET SIDETITLES ON
     SELECT t2.StepNum (FORMAT 'ZZZ9'),
            t2.EstRowCount (FORMAT 'ZZZ,ZZZ,ZZZ,ZZ9'),
            t2.ActRowCount  (FORMAT 'ZZZ,ZZZ,ZZZ,ZZ9'),
            t2.EstElapsedTime (FORMAT 'ZZZ,ZZZ,ZZ9'),
            t2.ActElapsedTime  (FORMAT 'ZZZ,ZZZ,ZZ9'),
            SUBSTRING(t2.SQLStep FROM 1 FOR 100) (TITLE 'SQLStep')
     FROM TABLE (syslib.MonitorSQLSteps(1, 128988, 16383)) AS t2;


            StepNum     1
        EstRowCount                0
        ActRowCount                2
     EstElapsedTime                0
     ActElapsedTime                0
            SQLStep  First, lock DBC.TVM for access.
            StepNum     2
        EstRowCount            5,760
        ActRowCount            5,970
     EstElapsedTime                1
     ActElapsedTime                0
            SQLStep  Next, we do an All-AMPs RETRIEVE step from DBC.TVM by way of
     an all-rows scan into Spool 2751, which is duplicated on all AMPs.
            StepNum     3
        EstRowCount        8,294,400
        ActRowCount        8,910,225
     EstElapsedTime               13
     ActElapsedTime                2
            SQLStep  We do an All-AMPs JOIN step from Spool 2751 by way of an
     all-rows scan, which is joined to table TVM. Spool 2751 and table TVM a
            StepNum     4
        EstRowCount   23,887,872,000
        ActRowCount                1
     EstElapsedTime           40,384
     ActElapsedTime                0
            SQLStep  We do an All-AMPs JOIN step from Spool 2751 (Last Use) by
     way of an all-rows scan, which is joined to Spool 2753. Spool 2751 and
            StepNum     5
        EstRowCount                1
        ActRowCount                1
     EstElapsedTime           19,082
     ActElapsedTime                1
            SQLStep  We do a SUM step to aggregate from Spool 2750 (Last Use) by
     way of an all-rows scan. Aggregate Intermediate Results are computed
            StepNum     6
        EstRowCount                1
        ActRowCount                1
     EstElapsedTime                0
     ActElapsedTime                1
            SQLStep  We do an All-AMPs RETRIEVE step from Spool 2754 (Last Use)
     by way of an all-rows scan into Spool 2748, which is built locally on
            StepNum     7
        EstRowCount                0
        ActRowCount                1
     EstElapsedTime                0
     ActElapsedTime                1
            SQLStep  We send out an END TRANSACTION step to all AMPs involved in
     processing the request.

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