zOs/SQL/LOGEXLCM

with n as
(
  select 'new' on
      , (select count(*)
           from oa1p.tqz064LockEscal o
           where t.timestamp       = o.timestamp
             and t.rz              = o.rz
             and t.dbSys           = o.dbSys
             and t.ssid            = o.ssid
             and t.EVENT_TYPE      = o.EVENT_TYPE
             and t.PLAN_NAME       = o.PLAN_NAME
             and t.PACKAGE_NAME    = o.PACKAGE_NAME
             and t.COLLECTION_ID   = o.COLLECTION_ID
             and t.CORRID_ID       = o.CORRID_ID
             and t.CONN_ID         = o.CONN_ID
             and t.RESOURCE        = o.RESOURCE
             and t.LOCK_STATE      = o.LOCK_STATE
             and t.STATEMENT       = o.STATEMENT
        ) othCnt
      , t.* from A540769.tqz064LockEscal t
  where rz = 'RZ2'
    and timestamp between '2014-10-05-04.00.00'
                      and '2014-10-09-00.00.00'
union all
  select 'old' on
      , (select count(*)
           from a540769.tqz064LockEscal o
           where t.timestamp       = o.timestamp
             and t.rz              = o.rz
             and t.dbSys           = o.dbSys
             and t.ssid            = o.ssid
             and t.EVENT_TYPE      = o.EVENT_TYPE
             and t.PLAN_NAME       = o.PLAN_NAME
             and t.PACKAGE_NAME    = o.PACKAGE_NAME
             and t.COLLECTION_ID   = o.COLLECTION_ID
             and t.CORRID_ID       = o.CORRID_ID
             and t.CONN_ID         = o.CONN_ID
             and t.RESOURCE        = o.RESOURCE
             and t.LOCK_STATE      = o.LOCK_STATE
             and t.STATEMENT       = o.STATEMENT
        ) othCnt
      , t.* from oa1p.tqz064LockEscal t
  where rz = 'RZ2'
    and timestamp between '2014-10-05-04.00.00'
                      and '2014-10-09-00.00.00'
)
select othCnt, min(on), max(on)
      , othCnt
      , timestamp
      , rz
      , dbSys
      , ssid
      , EVENT_TYPE
      , PLAN_NAME
      , PACKAGE_NAME
      , COLLECTION_ID
      , CORRID_ID
      , CONN_ID
      , RESOURCE
      , LOCK_STATE
      , STATEMENT
    from n
--  where othCnt <> 1
    group by   othCnt
             , timestamp
             , rz
             , dbSys
             , ssid
             , EVENT_TYPE
             , PLAN_NAME
             , PACKAGE_NAME
             , COLLECTION_ID
             , CORRID_ID
             , CONN_ID
             , RESOURCE
             , LOCK_STATE
             , STATEMENT
--  having min(on) = max(on)
    order by timestamp, rz, dbSys, event_type, min(on)
;
select count(*) from A540769.tqz064LockEscal ;
select *        from A540769.tqz064LockEscal
     where timestamp = '2014-09-29-06.00.51';
select *        from oa1p.tqz064LockEscal
     where timestamp = '2014-09-29-06.00.51';