DBQLogTbl

2009-09-15

SQL①　SQL発行件数の分布　ユーザー別

sel

cast(StartTime as char(10)) AS TheDate,

substr( cast( StartTime as char(19) ), 12,3)||

(CASE WHEN substr( cast(StartTime as char(16)), 15,2) between '00' and '29' THEN '00' ELSE '30' END)||':00' AS TheTime,

CASE GROUPING(UserName) WHEN 1 THEN '--ALLUSER--' ELSE Trim(UserName) END AS UserName,

count(distinct QueryText) AS QueryCount

from DBC.DBQLogTbl

WHERE cast(StartTime as char(10)) = '2009-09-15'

GROUP BY GROUPING SETS ((TheDate,TheTime,UserName),(TheDate,TheTime))

order by 1,2,3

;

SQL②　SQL発行件数の分布　全ユーザー

※結果を「10.SQL発行件数の分布」シートの表②に貼り付ける。

sel

cast(StartTime as char(10)) AS TheDate,

substr( cast( StartTime as char(19) ), 12,3)||

(CASE WHEN substr( cast(StartTime as char(16)), 15,2) between '00' and '29' THEN '00' ELSE '30' END)||':00' AS TheTime,

'--ALLUSER--' AS UserName,

count(distinct QueryText) AS QueryCount

from DBC.DBQLogTbl

WHERE cast(StartTime as char(10)) = '2009-09-15'

GROUP BY 1,2,3

order by 1,2,3

;

SQL③　処理時間の長いクエリー

基準時間 > 1200

※結果を「11.処理時間の長いクエリー」シートに貼り付ける。

sel

A.UserName,

A.ExeYMD,

A.StartTime,

A.EndTime,

A.ExeTime,

A.SessionID,

A.QueryID,

T3.C_JOB_ID,

( substr( cast(T3.J_END as char(19)), 12, 2)*3600 + substr(cast( T3.J_END as char(19)), 15, 2)*60 + substr( cast(T3.J_END as char(19)), 18, 2) )

-

( substr( cast(T3.J_ST as char(19)), 12, 2)*3600 + substr(cast( T3.J_ST as char(19)), 15, 2)*60 + substr( cast( T3.J_ST as char(19)), 18, 2) )

AS SHELL_ExeTime

from

(

SELECT

Trim(T1.UserName) UserName,

substr( cast( T1.StartTime as char(19) ), 1,10) AS ExeYMD,

substr( cast( T1.StartTime as char(19) ), 12,8) AS StartTime,

substr( cast( T1.LastRespTime as char(19) ), 12,8) AS EndTime,

(

substr( cast(T1.LastRespTime as char(19)), 12, 2)*3600 + substr(cast( T1.LastRespTime as char(19)), 15, 2)*60 + substr( cast(T1.LastRespTime as char(19)), 18, 2)

)

-

(

substr( cast(T1.StartTime as char(19)), 12, 2)*3600 + substr(cast( T1.StartTime as char(19)), 15, 2)*60 + substr( cast( T1.StartTime as char(19)), 18, 2)

) AS ExeTime,

T1.SessionID,

T1.QueryID,

Cast(T1.TotalCPUTime AS INTEGER) AS TotalCPUTime

FROM DBC.DBQLogTbl T1

LEFT OUTER JOIN DBC.DBQLSqlTbl T2

ON (T1.QueryID=T2.QueryID)

WHERE CAST(T1.StartTime AS CHAR(10))='2009-09-15' AND

ExeTime> 1200

GROUP BY 1,2,3,4,5,6,7,8

) A

LEFT OUTER JOIN DWHRUN.BCH_PGM_LOG T3

ON (

A.ExeYMD = substr( cast( T3.J_ST as char(19) ), 1, 10)

AND

A.StartTime

BETWEEN substr( cast( T3.J_ST as char(19) ), 12, 9) AND substr( cast( T3.J_END as char(19) ), 12, 9)

AND

( substr( cast(T3.J_END as char(19)), 12, 2)*3600 + substr(cast( T3.J_END as char(19)), 15, 2)*60 + substr( cast(T3.J_END as char(19)), 18, 2) )

-

( substr( cast(T3.J_ST as char(19)), 12, 2)*3600 + substr(cast( T3.J_ST as char(19)), 15, 2)*60 + substr( cast( T3.J_ST as char(19)), 18, 2) ) > 900

)

where SHELL_ExeTime-ExeTime between 0 and 100

group by UserName,ExeYMD,StartTime,EndTime,ExeTime,SessionID,QueryID,TotalCPUTime,C_JOB_ID,SHELL_ExeTime

ORDER BY StartTime, SHELL_ExeTime desc

;

Tweet