zOs/SQL/LOGEXTIM

with s as
(
    select
          case when timestamp between  '2015-07-05-09.23.00'
                                  and  '2015-07-05-09.29.00' then 1
               when timestamp between  '2015-06-28-09.12.00'
                                  and  '2015-06-28-09.19.00' then 2
               when timestamp between  '2015-06-21-09.00.00'
                                  and  '2015-06-21-09.04.00' then 3
               else 0  end g
       , t.*
    from oa1p.tqz060timeout t
    where rz = 'RZ2' and dbSys = 'DBOF'
                and timestamp between  '2015-06-21-09.00.00'
                                  and  '2015-06-21-09.04.00'
)
select count(*), holder_corr_id
    , min(timestamp), max(timestamp)
    , min(name), max(name)
    from s
    group by holder_corr_id
    order by 1 desc
;X;
with s as
(
    select trunc_timestamp(timestamp, 'dd') tst
       ,  case when timestamp between  '2015-07-05-09.23.00'
                                  and  '2015-07-05-09.29.00' then 1
               when timestamp between  '2015-06-28-09.12.00'
                                  and  '2015-06-28-09.19.00' then 2
               when timestamp between  '2015-06-21-09.00.00'
                                  and  '2015-06-21-09.04.00' then 3
               else 0  end g
       , t.*
    from oa1p.tqz060timeout t
    where rz = 'RZ2' and dbSys = 'DBOF'
   --   and name like '%NZ01A1P%'
    --  and name like '%NZ01A1P%A200A%'
    --  and (victim_plan like 'WI5611%'
    --    or holder_plan like 'WI5611%')
        and  timestamp between  '2015-06-21-00.00.00'
                          and  '2015-07-06-00.00.00'
)
select g -- date(min(timestamp)) --- , date(max(timestamp))
    ,  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(*)
    , min(timestamp), max(timestamp)
    , min(name), max(name)
 -- , min(plan_name), max(plan_name)
 -- , min(package_name), max(package_name)
    , min(event_type), max(event_type)
    , min(loadDate), max(loadDate) tst
    from s
    group by tst, g
    order by tst desc, g
;x;
select int(count(*)) total
    , int(sum(case when event_type = 'D' then 1 else 0 end)) deadlo
    , int(sum(case when event_type = 'T' then 1 else 0 end))timeout
    , substr(type, 1, 10) type
    , left(name, 16)
    from oa1p.tqz060timeout t
    where rz = 'RZ2' and dbSys = 'DBOF'
        and name like '%VV%'
        and timestamp >= '2015-05-01-00.00.00'
    group by type, left(name, 16)
    order by 1  desc
;x;
with s as
( select case when loadDate <  '14.11.2014' then 1 else 0 end t1
       ,      trunc_timestamp(timestamp, 'iw') tst
       , t.*
    from oa1p.tqz060timeout t
    where rz = 'RZ2' and dbSys = 'DBOF'
        and name like '%VV%'
      --and name like '%VDPS230%'
        and timestamp >= '2014-11-01-00.00.00'
)
select date(tst)
    ,  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
    group by tst
    order by 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 = 'RZ2' 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