zOs/SQL/LOGEXTO

set current application compatibility 'V11R1';
select rz, dbSys, loadDate, count(*)
    from oa1p.tqz060timeout
    where loadDate > current date - 10 days
    group by rollup(loadDate, rz, dbSys)
    order by rz, dbSys, loadDate desc
;X;
select ' ' || reason_code
      , sum(case when event_type = 'D' then 1 else 0 end) d
      , sum(case when event_type = 'T' then 1 else 0 end) t
      , sum(case when event_type = ''  then 1 else 0 end) sp
      , sum(case when event_type not in ('D', 'T', '')
              then 1 else 0 end) oth
      , count(*) cnt
    from oa1p.tqz060Timeout t
    group by reason_code
;x;
select count(*), event_type, reason_code
    from oa1p.tqz060Timeout t
    group by event_type, reason_code
;x;
    where rz = 'RZ2' and dbSys = 'DBOF'
           and victim_plan like 'DSNUTI%'
    --  and name like '%SYSUTI%'
    order by timestamp desc
    fetch first 100 rows only
;x;
with g as
(
  select count(*) cnt
       , date(min(timestamp))  d
       , sum(case when timestamp > current Timestamp - 1 month
               then 1 else 0 end) cMo
       , substr(name, 1, 15) n17
       , min(name) rMin, max(name) rMax
    from oa1p.tqz060Timeout t
    where rz = 'RZ2' and dbSys = 'DBOF'
        and (
               holder_plan like 'RV5650%'
            or victim_plan like 'RV5650%'
     --     or plan_name like '%serv%' or package_name like '%serv%'
            or name like 'RV01A1P .A200A%'
            )
        and timestamp > current timestamp - 1 year
    group by substr(name, 1, 15), trunc_timestamp(timestamp, 'mon')
    order by 2, 3
)
, t as
(
  select sum(cnt) cnt, sum(cMo) cMo, n17
      from g
      group by n17
)
select g.*
    from g join t on g.n17 = t.n17
    order by cMo desc, cnt desc, n17
;x;L
with g as
(
  select count(*) cnt
       , sum(case when timestamp > current Timestamp - 1 month
               then 1 else 0 end) cMo
       , substr(name, 1, 17) n17
       , type
       , min(name) rMin, max(name) rMax
    from oa1p.tqz060timeout t
    where rz = 'RZ2' and dbSys = 'DBOF'
        and name like '%VV%'
        and timestamp > current timestamp - 1 year
    group by substr(name, 1, 17), type
    order by 2, 3
)
, s as
(
  select sum(cnt) cnt, sum(cMo) cMo, n17
      from g
      group by n17
)
, t as
(
  select cnt, cMo
       , row_number() over(order by cnt desc) rCn
       , row_number() over(order by cMo desc) rMo
       , n17
      from s
)
select g.*, rCn, rMo
    from g join t on g.n17 = t.n17
    order by cMo desc, cnt desc, n17, type
;x;L
select sum(case when event_type = 'D' then 1 else 0 end) deadlock
    ,  sum(case when event_type = 'T' then 1 else 0 end) timeout
    , min(name), max(name)
    , min(plan_name), max(plan_name)
    , min(package_name), max(package_name)
    , min(event_type), max(event_type)
    , count(*)
    , min(loadDate), tst
    from s
    where rz = 'RR2' and dbSys = 'DBOF'
        and name like '%VV%'
        and timestamp > current timestamp - 1 year
    group by t1, tst
    order by t1 desc, tst desc
;x;
with s as
( select case when loadDate <= '03.10.2014' then 0 else 1 end t1
       , trunc_timestamp(timestamp, 'dd') tst
       , t.*
    from oa1p.tqz060timeout t
)
select sum(case when event_type = 'D' then 1 else 0 end) deadlock
    ,  sum(case when event_type = 'T' then 1 else 0 end) timeout
    , min(name), max(name)
    , min(plan_name), max(plan_name)
    , min(package_name), max(package_name)
    , min(event_type), max(event_type)
    , count(*)
    , min(loadDate), tst
    from s
    where rz = 'RR2' and dbSys = 'DBOF'
        and name like '%VV%'
        and timestamp > current timestamp - 1 year
    group by t1, tst
    order by t1 desc, tst desc
;x;
with s as
( select case when loadDate <= '03.10.2014' then 0 else 1 end t1
       , trunc_timestamp(timestamp, 'dd') tst
       , t.*
    from oa1p.tqz064lockEscal t
    where rz = 'RR2' and dbSys = 'DBOF'
        and resource like '%VV%'
)
select

      count(*)
    , min(loadDate), tst
    , min(resource), max(resource)
    , min(plan_name), max(plan_name)
    , min(package_name), max(package_name)
    , min(event_type), max(event_type)
    from s
    group by t1, tst
    order by t1 desc, tst desc
;x;
select sum(case when event_type = 'D' then 1 else 0 end) deadlock
    ,  sum(case when event_type = 'T' then 1 else 0 end) timeout
    , count(*)
    , trunc_timestamp(timestamp, 'mon')
    from oa1p.tqz060timeout
    where rz = 'RZ2' and dbSys = 'DBOF'
        and name like '%WB%'
        and timestamp > current timestamp - 1 year
    group by trunc_timestamp(timestamp, 'mon')
    order by 4 desc
;x;
select *
    from oa1p.tqz064LockEscal
    where rz = 'RZ2' and dbSys = 'DBOF'
        and resource like '%WB%'
        and timestamp > currentDA540769mp - 1 month
    order by timestamp desc
;x;
select *
    from oa1p.tqz060timeout
    where rz = 'RZ2' and dbSys = 'DBOF'
        and name like '%WB%'
        and timestamp > current timestamp - 1 month
    order by timestamp desc