zOs/SQL/TUNEDDL

set current path oa1p;
set current sqlid = 'S100447';
drop   tablespace dA540769.pkgWeek;
drop   tablespace dA540769.aTune;
drop   tablespace dA540769.aTuneWee;
drop   tablespace dA540769.aTunePea;
commit;
CREATE tablespace aTune
  IN dA540769
  USING STOGROUP GSMS
  PRIQTY -1 SECQTY -1
  FREEPAGE 10 PCTFREE 5
  GBPCACHE CHANGED
  TRACKMOD YES
  not LOGGED
  BUFFERPOOL BP2
  LOCKSIZE ANY
  LOCKMAX SYSTEM
  CLOSE YES
  COMPRESS YES
  CCSID      EBCDIC
  DEFINE YES
  MAXROWS 255;
create table A540769.tTune
(
    tune char(20) not null,
    from date not null,
    to   date not null,
    desc char(100) not null
) in dA540769.aTune
;
create table A540769.tTunePgm
(
    tune char(20) not null,
    pgm char(8)
) in dA540769.aTune
;
CREATE tablespace aTuneWee
  IN dA540769
  USING STOGROUP GSMS
  PRIQTY -1 SECQTY -1
  FREEPAGE 10 PCTFREE 5
  GBPCACHE CHANGED
  TRACKMOD YES
  not LOGGED
  BUFFERPOOL BP2
  LOCKSIZE ANY
  LOCKMAX SYSTEM
  CLOSE YES
  COMPRESS YES
  CCSID      EBCDIC
  DEFINE YES
  MAXROWS 255;
create table A540769.tTuneWeek as
(
select
       date(trunc_timestamp(timestamp, 'ww')) timestamp,
       int(sum(OCCURRENCES ))   occ,
       sum(SQL_STMTS_ISSUED)   stmts,
       sum(CLASS7_CPU_TOTAL) c7cpu,
       sum(CLASS7_SU_CPU) c7su,
       sum(CLASS7_ELAPSED)   c7ela,
       substr(PCK_ID, 1, 8) pkg,
       min(substr(PCK_COLLECTION_ID, 1, 8)) collMin ,
       max(substr(PCK_COLLECTION_ID, 1, 8)) collMax ,
       count(distinct trunc_timestamp(timestamp, 'ddd')) dayCnt
    from rz2dd.tacct_program
    where   subsystem_id like 'DOF%'
        and timestamp >= trunc_timestamp('2012-03-01-00.00.00', 'ww')
   --   and  dayofweek_iso(timestamp) <= 5
   --   and  hour(timestamp) between 9 and 11
    group by
       trunc_timestamp(timestamp, 'ww'), PCK_id
) with no data
  in dA540769.aTuneWee
;
set current sqlid = 'S100447';
CREATE unique INDEX A5407690.ITuneWeek0
  ON A540769.TTuneWeek
   (pkg, timestamp)
  USING STOGROUP GSMS
  FREEPAGE 10 PCTFREE 10
  CLUSTER
  BUFFERPOOL BP1
  close yes
;
CREATE tablespace aTunePea
  IN dA540769
  USING STOGROUP GSMS
  PRIQTY -1 SECQTY -1
  FREEPAGE 10 PCTFREE 5
  GBPCACHE CHANGED
  TRACKMOD YES
  not LOGGED
  BUFFERPOOL BP2
  LOCKSIZE ANY
  LOCKMAX SYSTEM
  CLOSE YES
  COMPRESS YES
  CCSID      EBCDIC
  DEFINE YES
  MAXROWS 255;
create table A540769.tTunePeak as
(
select
       date(trunc_timestamp(timestamp, 'ww')) timestamp,
       int(sum(OCCURRENCES ))   occ,
       sum(SQL_STMTS_ISSUED)   stmts,
       sum(CLASS7_CPU_TOTAL) c7cpu,
       sum(CLASS7_SU_CPU) c7su,
       sum(CLASS7_ELAPSED)   c7ela,
       substr(PCK_ID, 1, 8) pkg,
       min(substr(PCK_COLLECTION_ID, 1, 8)) collMin ,
       max(substr(PCK_COLLECTION_ID, 1, 8)) collMax ,
       count(distinct trunc_timestamp(timestamp, 'hh')) hhCnt
    from rz2hh.tacct_program
    where   subsystem_id like 'DOF%'
        and timestamp >= trunc_timestamp('2012-03-01-00.00.00', 'ww')
        and  dayofweek_iso(timestamp) <= 5
        and  hour(timestamp) between 9 and 11
    group by
       trunc_timestamp(timestamp, 'ww'), PCK_id
) with no data
  in dA540769.aTunePea
;
set current sqlid = 'S100447';
CREATE unique INDEX A5407690.ITunePeak0
  ON A540769.tTunePeak
   (pkg, timestamp)
  USING STOGROUP GSMS
  FREEPAGE 10 PCTFREE 10
  CLUSTER
  BUFFERPOOL BP1
  close yes
;
commit
;