zOs/SQL/VVLOCKPR

  select count(*) cnt, date(timestamp)
       , 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 month
    group by date(timestamp), type
    order by 2 desc, 3
;
  select count(*) cnt, date(timestamp)
  --   , sum(case when timestamp > current Timestamp - 1 month
  ---          then 1 else 0 end) cMo
  --   , substr(resource, 1, 27) n17
       , min(resource) rMin, max(resource) rMax
    from oa1p.tqz064LockEscal t
    where rz = 'RZ2' and dbSys = 'DBOF'
        and (resource like '%VV%' or resource like '%VDPS%'
            or plan_name like '%VDPS%' or package_name like '%VDPS%'
            or plan_name like '%serv%' or package_name like '%serv%'
            or plan_name like '%VV%' or package_name like '%VV%')
        and timestamp > current timestamp - 1 month
    group by date(timestamp)
    order by 2 desc
;x;
)
with g as
(
  select count(*) cnt
       , sum(case when timestamp > current Timestamp - 1 month
               then 1 else 0 end) cMo
       , substr(resource, 1, 27) n17
       , min(resource) rMin, max(resource) rMax
    from oa1p.tqz064LockEscal t
    where rz = 'RZ2' and dbSys = 'DBOF'
        and (resource like '%VV%' or resource like '%VDPS%'
            or plan_name like '%VDPS%' or package_name like '%VDPS%'
            or plan_name like '%serv%' or package_name like '%serv%'
            or plan_name like '%VV%' or package_name like '%VV%')
        and timestamp > current timestamp - 1 year
    group by substr(resource, 1, 27)
    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