PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

Query to identify jobs which went to penalty box during its execution

Penalty Box 

A Penalty box is more of a workload management strategy than Viewpoint (although you configure workload management with Viewpoint portlets). The idea of a penalty box workload is to move queries, that are running longer than expected, to a lower priority workload allowing them to finish but get less resources in doing so. For instance, I have a tactical workload and the expectation would be that queries categorized here should finish quickly, one may add a TASM exception to say if they exceed a run time threshold, move them down to a lower priority. CPU milestones on the Teradata Appliance platforms behave this way as well.

Below is the query to identify the jobs  which were executed in penalty box for the day.

penaltycou

SEL
B.LOGDATE,
A.*,
B.USERNAME,
B.STARTTIME,
B.FIRSTRESPTIME ,
B.SessionID,
EXTRACT(SECOND FROM ((B.FIRSTRESPTIME – B.STARTTIME) HOUR(4) TO SECOND))+(60*EXTRACT(MINUTE FROM ((B.FIRSTRESPTIME – B.STARTTIME) HOUR(4) TO SECOND))) + (3600*EXTRACT(HOUR FROM ((B.FIRSTRESPTIME – B.STARTTIME) HOUR(4) TOSECOND))) AS RUNTIMESECONDS,– RUNTIMESECONDS
B.AMPCPUTIME (FORMAT 'ZZ,ZZZ,ZZ9.999') AS TOTALCPUTIME, –TOTALCPUTIME
MAXAMPCPUTIME * (HASHAMP()+1) AS CPUIMPACT,–CPUIMPACT
MAXAMPIO * (HASHAMP()+1) AS IOIMPACT, –IOIMPACT,
CASE WHEN TOTALCPUTIME < 1 OR (TOTALIOCOUNT/(HASHAMP()+1)) =0 THEN 0 ELSE MAXAMPIO/NULLIFZERO((TOTALIOCOUNT/(HASHAMP()+1))) END (DEC(8,2)) AS IOSKEW,–IOSKEW
CASE WHEN TOTALCPUTIME < 1 OR (TOTALCPUTIME/(HASHAMP()+1)) =0 THEN 0 ELSE MAXAMPCPUTIME/NULLIFZERO((TOTALCPUTIME/(HASHAMP()+1))) END (DEC(8,2)) AS CPUSKEW,–CPUSKEW
CASE WHEN TOTALCPUTIME < 1 OR TOTALIOCOUNT = 0 THEN 0 ELSE (TOTALCPUTIME *1000)/NULLIFZERO(TOTALIOCOUNT) END AS PJI,–PJI
CASE WHEN TOTALCPUTIME < 1 THEN 0 ELSE TOTALIOCOUNT/NULLIFZERO((TOTALCPUTIME *1000)) END AS UII,–UII
B.TOTALIOCOUNT,
B.SPOOLUSAGE,
B.STATEMENTTYPE,
B.QUERYTEXT
FROM
PDCRINFO.TDWMEXCEPTIONLOG_HST A INNER JOIN PDCRINFO.DBQLOGTBL_HST B ON A.QUERYID=B.QUERYID AND A.LOGDATE=B.LOGDATE AND A.USERNAME=B.USERNAME
WHERE A.LOGDATE BETWEEN '' AND ''
AND A.USERNAME = ''
AND A.NewWDName = 'WD-PenaltyBox'

Add a Comment

Your email address will not be published. Required fields are marked *