zOs/SQL/LOGEXESC

  set current application compatibility 'V11R1';
  select rz, dbSys, loadDate, count(*)
      from oa1p.tqz064LockEscal
      where loadDate > current date - 3 days
      group by rollup(loadDate, rz, dbSys)
      order by rz, dbSys, loadDate desc
  ;X;
with s as
( select
         date(trunc_timestamp(timestamp, 'mon')) tst
       , e.*
    from oa1p.tqz064lockEscal e
    where rz = 'RZ2' and dbSys = 'DBOF'
        and resource like '%DG01%'
        and timestamp >= current timestamp - 2 year
)
select tst
    ,  count(*) count
    ,  sum(case when resource like '%127%' then 1 else 0 end) c127
    , min(resource), max(resource)
    , 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;
select
      count(*)
    , left(resource, 16)
--  , trunc_timestamp(timestamp, 'mon')
    from oa1p.TQZ064LOCKESCAL
    where rz = 'RZ2' and dbSys = 'DVBP'
        and timestamp > '2014-12-01-00.00.00'
--  group by trunc_timestamp(timestamp, 'mon')
    group by left(resource, 16)
    order by 1 desc
;x;
with s as
( select case when loadDate <  '14.11.2014' then 1 else 0 end t1
       , date(trunc_timestamp(timestamp, 'iw')) tst
       , e.*
    from oa1p.tqz064lockEscal e
    where rz = 'RZ2' and dbSys = 'DBOF'
        and resource like '%VV%'
        and timestamp >= '2014-07-01-00.00.00'
)
select tst
    ,  count(*) count
    , min(resource), max(resource)
    , 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 = '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