Archives of the TeradataForum

Message Posted: Fri, 11 Apr 2014 @ 16:19:22 GMT

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

Below query finds top bad queries.

When I run this query I get an error, "expected something between the 'DATE' keyword and the word 'QUALIFYRANK'.

What should I change?

Thank you,

     ---Query to list Top Bad Queries
     SELECT
        RANK(ImpactCPU) AS CPURank
        ,CAST(a.CollectTimeStamp AS DATE) AS logdate
        ,a.UserName
        ,a.ProcId
        ,a.QueryId
        /*,a.querytext*/
        ,a.expandacctstring
        ,a.appid
        ,a.clientid
        ,a.MaxAmpCPUTime * (HASHAMP()+1)  /*no. of Amps*/ AS ImpactCPU
        , a.AMPCPUTime AS SumCPU
        ,CAST(EXTRACT(HOUR
            FROM ((a.firststeptime - a.StartTime) HOUR(2) TO SECOND(2) ) ) * 3600
     + EXTRACT(MINUTE
            FROM ((a.firststeptime - a.StartTime) HOUR(2) TO SECOND(2) ) ) * 60 +
     EXTRACT(SECOND
            FROM ((a.firststeptime - a.StartTime) HOUR(2) TO SECOND(2) ) ) AS
     INTEGER)   AS ParseBlockTime
        ,((firstresptime-firststeptime) DAY(4) TO SECOND) AS QryRespTime
        , NumofActiveAmps
        , NumResultRows
        , a.TotalIOCount AS SumIO
        ,
     CASE WHEN AMPCPUTime < 1
     OR(AMPCPUTime /  (HASHAMP()+1)) =0 THEN 0
              ELSE MaxAmpCPUTime/(AMPCPUTime /  (HASHAMP()+1))
     END(DEC(8,2)) AS CPUSKW
        ,
     CASE WHEN AMPCPUTime < 1
     OR(TotalIOCount /  (HASHAMP()+1)) =0 THEN 0
              ELSE MaxAmpIO/(TotalIOCount /  (HASHAMP()+1))
     END(DEC(8,2)) AS IOSKW
        ,
     CASE WHEN AMPCPUTime < 1
     OR TotalIOCount = 0 THEN 0
     ELSE (a.AMPCPUTime *1000)/a.TotalIOCount END AS PJI
        ,
     CASE WHEN AMPCPUTime < 1
     OR AMPCPUTime = 0 THEN 0
     ELSE a.TotalIOCount/(a.AMPCPUTime *1000) END AS UII
        ,a.acctstring
        ,a.Starttime
        ,a.defaultdatabase
        ,a.SpoolUsage
        ,a.Statementtype
      FROM DBC.DBQLogTbl a
     WHERE logdate BETWEEN (DATE-90)  AND  DATE
     QUALIFYRANK(ImpactCPU) <= 70;

James Park
Database Specialist

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