Get query information from DBQLogTBL in Teradata

Sometime user or developer might want to know why a query takes longer time. To investigate the issue, you can use the following sql to check if there was any delay, Query execution time, CPU time, Parsing time, CPU Skew, PJI, Spool Usage, Error, query text etc to investigate the matter.

Select

dbql.LogDate

,dbql.QueryId

,dbql.SessionID

,dbql.StartTime

,dbql.delaytime

,dbql.lockdelay

,dbql.FirstRespTime

,((dbql.FirstRespTime - dbql.StartTime) hour(4) to second) (Named ElapsedTimeInDB)

,cast(

extract(hour from ElapsedTimeInDB) * 60 * 60 +

extract(minute from ElapsedTimeInDB) * 60 +

extract(second from ElapsedTimeInDB)

as decimal(10,1)) (Named ElapsedSecondsInDB)

,dbql.LastRespTime

,((dbql.LastRespTime - dbql.StartTime) hour(4) to second) (Named ElapsedTimeTotal)

,cast(

extract(hour from ElapsedTimeTotal) * 60 * 60 +

extract(minute from ElapsedTimeTotal) * 60 +

extract(second from ElapsedTimeTotal)

as decimal(10,1)) (Named ElapsedSecondsTotal)

,(dbql.AmpCPUTime+dbql.ParserCPUTime) as CPU

,QUANTILE(10, CPU) as QCPU

,COALESCE(CPU/NULLIFZERO(ElapsedSecondsInDB),0) as CPUI

,case

when CPU between 0.0 and 3.9 then 3

when CPU between 3.0 and 10.0 then 10

else 11

end as CPUSpan

,dbql.ParserCPUTime

,COALESCE(100-(NULLIFZERO(dbql.Ampcputime/NULLIFZERO(dbql.NumOfActiveAMPs))/(NULLIFZERO(dbql.Maxampcputime))*100),0) AS CPUSkew

,COALESCE(NULLIFZERO(dbql.AMPCPUTime) / NULLIFZERO(dbql.NumOfActiveAMPS),0) AS CPUSkewImpact

,(dbql.AMPCPUTime *1000)/NULLIFZERO(dbql.TotalIOCount) AS PJI

,COALESCE(dbql.TotalIOCount/(NULLIFZERO(dbql.AMPCPUTime) * 1000),0) AS UII

,dbql.MaxAMPCPUTime*dbql.NumOfActiveAMPs ImpactCPU

,dbql.spoolusage/1024/1024 spoolusage_MB

,dbql.ERRORCODE

,dbql.errortext

,dbql.WDName

,dbql.FinalWDName

,dbql.QueryBand

,dbql.UserName

,dbql.QueryText

From PDCRINFO.DBQLogTbl_Hst as dbql

Where

dbql.LogDate = Date '2018-09-06'

and dbql.username='A83844'

-- and dbql.sessionid=74119319

-- and dbql.queryid=307121259674786486

order by starttime

;

If the data is not moved to PDCRINFO.DBQLogTbl_Hst table from PDCRINFO.DBQLogTbl_DBC table then replace PDCRINFO.DBQLogTbl_Hst with PDCRINFO.DBQLogTbl_DBC in the above query to get the information.