zOs/SQL/PDBGENOP

set current path oa1p;
set current schema = RZ2HH;
select timestamp,
       fosFmte7(sum(select)) "select",
       fosFmte7(sum(fetch)) "fetch",
       fosFmte7(sum(open)) "open",
       fosFmte7(sum(insert)) "insert"
      ,fosFmte7(sum(update)) "update"
      ,fosFmte7(sum(delete)) "delete"
      ,fosFmte7(sum(commit)) "commit"
      ,fosFmte7(sum(rollback)) "rollBa"
    from       TACCT_GENERAL g
    where subsystem_id like 'DOF%'
         and timestamp > '2010-03-01-00.00.00'
    group by timestamp
    order by sum(insert) desc
    with ur
;
x
select trunc_timestamp(timestamp, 'HH'),
       fosFmte7(sum(select)) "select",
       fosFmte7(sum(fetch)) "fetch",
       fosFmte7(sum(open)) "open",
       fosFmte7(sum(insert)) "insert"
      ,fosFmte7(sum(update)) "update"
      ,fosFmte7(sum(delete)) "delete"
      ,fosFmte7(sum(commit)) "commit"
      ,fosFmte7(sum(rollback)) "rollBa"
    from       TACCT_GENERAL g
    where subsystem_id like 'DOF%'
         and timestamp > '2010-03-01-00.00.00'
    group by trunc_timestamp(timestamp, 'HH')
    order by sum(insert) desc
    with ur
;
set current schema = RZ2DD;
select date(timestamp),
       fosFmte7(sum(select)) "select",
       fosFmte7(sum(fetch)) "fetch",
       fosFmte7(sum(open)) "open",
       fosFmte7(sum(insert)) "insert"
      ,fosFmte7(sum(update)) "update"
      ,fosFmte7(sum(delete)) "delete"
      ,fosFmte7(sum(commit)) "commit"
      ,fosFmte7(sum(rollback)) "rollBa"
    from       TACCT_GENERAL g
    where subsystem_id like 'DOF%'
         and timestamp > '2010-03-01-00.00.00'
    group by date(timestamp)
    order by date(timestamp) desc
    with ur
;
x
select min(timestamp - class1_elapsed seconds) "von",
       time(max(timestamp)) "bis",
       fosFmte7(sum(class1_elapsed)) "ela sec",
       fosFmte7(sum(occurrences)) occ,
       oa1p.fosFmtE7(sum(select)) sel,
       oa1p.fosFmtE7(sum(insert)) ins,
       oa1p.fosFmtE7(sum(update)) upd,
       oa1p.fosFmtE7(sum(delete)) del,
       oa1p.fosFmtE7(sum(commit)) com,
       oa1p.fosFmtE7(sum(class2_elapsed)/sum(insert+update)) "ela/i+u" ,
       fosFmte7(sum(class2_cpu_total) / sum(insert+update)) "cpu/i+u",
       fosFmte7(sum(CLASS3_LOCK_LATCH)/sum(insert+update)) "LocLat/i+u",
       fosFmte7(sum(CLASS3_SYNC_IO)/sum(insert+update)) "syncIo/i+u",
       count(distinct subsystem_id) "#ssid", plan_name
    from g
    where timestamp > current timestamp - 9992 month
      --  and connect_ID like 'NI229%'
          and plan_name in ( 'NI5310', 'NI5340')
      --  and insert >= 1
      group by g1, g2, plan_name
      order by plan_Name, 1 desc
    with ur
;
X
set current path oa1p;
set current schema = RZ2DD;
select date(timestamp),
       fosFmte7(sum(occurrences)) occ,
       oa1p.fosFmtE7(sum(select)) sel,
       oa1p.fosFmtE7(sum(insert)) ins,
       oa1p.fosFmtE7(sum(update)) upd,
       oa1p.fosFmtE7(sum(delete)) del,
       oa1p.fosFmtE7(sum(class2_elapsed)/sum(insert+update)) "ela/i+u" ,
       fosFmte7(sum(class2_cpu_total) / sum(insert+update)) "cpu/i+u",
       fosFmte7(sum(CLASS3_LOCK_LATCH)/sum(insert+update)) "LocLat/i+u",
       fosFmte7(sum(CLASS3_SYNC_IO)/sum(insert+update)) "syncIo/i+u"
    from RZ2dd.TACCT_GENERAL g
    where timestamp > current timestamp - 1 month
      --  and connect_ID like 'NI229%'
          and plan_name = 'NI5340'
      --  and insert >= 1
    group by timestamp
    ORDER BY Timestamp desc
    with ur
;
X
select plan_name,
       fosFmte7(sum(occurrences)) occ,
       oa1p.fosFmtE7(sum(select)) sel,
       oa1p.fosFmtE7(sum(insert)) ins,
       oa1p.fosFmtE7(sum(update)) upd,
       oa1p.fosFmtE7(sum(delete)) del,
       min(timestamp), max(timestamp)
    from       TACCT_GENERAL g
    where timestamp > '2010-03-21-00.30.00.000'
      and timestamp < '2010-03-21-02.30.00.000'
      --  and connect_ID like 'NI229%'
      --  and plan_name = 'NI5340'
      --  and insert >= 1
    group by plan_name
    order by sum(select+insert+update+delete) desc
    with ur
;
X
select sum(select) sel , sum(insert) ins, sum(update) upd,
       sum(class2_elapsed) / sum(insert) "ela/ins" ,
       sum(class2_cpu_total) / sum(insert) "cpu/ins",
       sum(LOCK_LATCH_SUSP),
       sum(CLASS3_LOCK_LATCH)/sum(insert) "LocLat/ins",
       sum(SYNC_IO_SUSP),
       sum(CLASS3_SYNC_IO)/sum(insert) "syncIo/ins",
       sum(occurrences)  occ,
       timestamp
    from RZ2hh.TACCT_GENERAL g
    where timestamp > current timestamp - 2 DAY
      --  and connect_ID like 'NI229%'
          and plan_name = 'NI5340'
      --  and insert >= 1
    ORDER BY Timestamp desc
    with ur
;
X
select subsystem_id, count(*), min(timestamp), max(timestamp)
    from RR2HH.TACCT_GENERAL g
 -- where corrName like 'NI%'
    group by subsystem_id
    order by subsystem_id
    with ur
;
xelect class2_elapsed,
    smallInt(1000* class2_cpu_total     /class2_elapsed) cpu,
    smallInt(1000* class3_global_cont  /class2_elapsed) "gloCon",
    smallInt(1000* class3_DB_IO        /class2_elapsed) "db io",
    smallInt(1000* class3_LOG_WRT_IO   /class2_elapsed) "logIO",
    smallInt(1000* (class2_cpu_total
                 + class3_global_cont
                 + class3_DB_IO
                 + class3_LOG_WRT_IO ) /class2_elapsed) "sum",
        g.*
    from RR2HH.TACCT_GENERAL g
    where -- timestamp > current timestamp - 120 DAY
              corrname like 'NI229%'
 --        or corrname like 'NI22922%'
   --     and plan_name like 'DSNUT%'
   --     and insert >= 1
    ORDER BY CLASS1_ELAPSED desc
;
X
select class2_elapsed / insert , class2_su_cpu / insert,
        g.*
    from RZ2XX.TACCT_GENERAL g
--  where timestamp > current timestamp - 1 month
    where timestamp IN ('2010-02-02-01.05.50.881483'
                       ,'2010-01-30-06.38.00.929757')
          and plan_name = 'BE5020'
          and  insert > 500000 -- class2_elapsed >= 1000
    order by 1 desc, class2_elapsed desc
;
select -- class2_elapsed / insert , class2_su_cpu / insert,
        g.*
    from RZ2XX.TACCT_GENERAL g
    where timestamp > current timestamp - 5 DAY
          and plan_name = 'RB5000'
    ORDER BY Timestamp asc
;
X