zOs/SQL/MF15UNL

with r (l, p, v, b) as
(  -- r = rekursiv Partitionen berechnen,
   --     p = Partition, v=von inklusiv, b=bis exklusiv
  select  0, 0, date('01.01.1800'), date('01.01.1900')
    from sysibm.sysdummy1
  union all select l+1, -1,
      (select min(MF150013) from oa1t.tmf150a1 where MF150013 >= r.b),b
    from r where p >= 0 and v < current date - 10 month and l < 999
  union all select l+1, year(v)*24+month(v)*2 +10-48002, v,
      v + (16-day(v)) days
    from r where p = -1 and day(v) <= 15 and l < 999
  union all select l+1, year(v)*24+month(v)*2 +11-48002, v,
      v - (day(v)-1) days + 1 month
    from r where p = -1 and day(v) > 15 and l < 999
)
, p (p, v, b) as
(
  select strip(char(p)), v, b
    from r
    where p > 0 and b < current date - 10 month
)
, t (p, v, b, n, t) as
( -- für jede Partition utility ctl: copy, load, copy
            select p.*, 1, 'COPY TABLESPACE MF01A1T.MF150H DSNUM ' || p
                                                                from p
  union all select p.*, 2, '    COPYDDN(TCOPYD) SHRLEVEL REFERENCE'
                                                                from p
  union all select p.*,10, 'EXEC SQL'                           from p
  union all select p.*,11, '  DECLARE CUR' || p ||' CURSOR FOR' from p
  union all select p.*,12, '    SELECT *'                       from p
  union all select p.*,13, '      FROM OA1T.TMF150A1'           from p
  union all select p.*,14, '      WHERE MF150013 >= '''
                        || char(v) || ''''                      from p
  union all select p.*,15, '        and MF150013 <  '''
                        || char(b) || ''''                      from p
  union all select p.*,16, '      order by MF150013 asc, MF150067 asc'
                                                                from p
  union all select p.*,17, 'ENDEXEC'                            from p
  union all select p.*,21, 'LOAD DATA INCURSOR CUR' || p        from p
  union all select p.*,22, '  LOG NO  RESUME YES '              from p
  union all select p.*,23, '  SORTDEVT DISK SORTNUM 50'         from p
  union all select p.*,24, '  WORKDDN(TSYUTS,TSOUTS)'           from p
  union all select p.*,25, '  INTO TABLE  OA1T.TMF150H1'        from p
  union all select p.*,26, '    IGNOREFIELDS YES PART ' || p    from p
  union all select p.*,31, 'COPY TABLESPACE MF01A1T.MF150H DSNUM ' || p
                                                                from p
  union all select p.*,32, '    COPYDDN(TCOPYD) SHRLEVEL REFERENCE'
                                                                from p
)
select char(t, 80) from t order by v, n
;;    ????????????
??????????????????
with d (l, d) as
(  select 0, min(MF150013)+2 month from oa1t.tmf150a1
   union all select l+1, d + 1 day from d where l < 5
)
select d, (select count(*) from oa1t.tmf150a1 where MF150013=d) a
        , (select count(*) from oa1t.tmf150h1 where MF150013=d) h
    from d
;;
( select 0count(*), MF150013
    from oa1t.tmf150a1
    where MF150013 <
        (select min(MF150013) + 5 days from oa1t.tmf150a1)
    group by MF150013
    with ur
;;
select count(*), MF150013
    from oa1t.tmf150a1
    where MF150013 < '18.02.2010'
    group by MF150013
    with ur
;;
select min(MF150013), max(MF150013)
    from oa1t.tmf150a1 ;
select min(MF150013), max(MF150013)
    from oa1t.tmf150H1 ;
;;