zOs/SQL/XBREPSPC

$#=
$>. fedit('::v')
$<=[
table              old  new1 new2 column      valueLess    $]
$<dsn.dbadm.texv(XB06101C)
$@ call sqlConnect dvbp
$*( ******************************************************

    count parts and needed space

    usage: adapt input above and enter wsh on commandline

    output: first line total, then figurs for each partition
        spc = db2 vsam space
        unl = estimate for unloads (using pageSave)
              multiply by 2 to be on the save side|

    input: first 2 words on each line (separated by spaces or tab chars)
           (same as for xbRepEmp or xbRepa)
        table:     tablename (without creator)
        old:       old partition number

    e.g.
table              old  new1 new2    ...
XBDPM027IT002001 102 110 111 EA1STRN ...

12.10.16 Walter
*******************************************************$*)
$@. csvWordRdr( , 'u')
$|
set current application compatibility 'V11R1';
set current path oa1p;
select substr(t.name, 1, 20) bt
      , smallint(p.partition) part
      , count(*) count
      , fqzfmtBin7(sum(1024.0
                * max(0, value(p.spaceF, 0)
                     , value(real(r.nActive) * s.pgSize, 0)))) spc
      , fqzfmtBin7(sum(1024.0
                * max(0, value(p.spaceF, 0)
                     , value(real(r.nActive) * s.pgSize, 0))
                * 100 / max(1, 100-value(p.pageSave, 0)))) unl

    from sysibm.sysTables t
      join sysibm.sysTablespace s
        on t.dbName = s.dbName and t.tsName = s.name
      join sysibm.sysTablePart p
        on t.dbName = p.dbName and t.tsName = p.tsname
      left join sysibm.sysTablespaceStats r
        on r.dbid = s.dbid and r.psid = s.psid
             and r.partition = p.partition
    where
      (1=0
$@ tbs = ''
$forWith $@=[
        or (t.name = '$TABLE' and p.partition = $OLD)
        $@ if wordPos($TABLE, tbs) < 1 then tbs = tbs $TABLE
    $]
      )
      and t.name in ( 'dummyDoesNotExits'
$do tx=1 to words(tbs) $@=[
                    , '$-[word(tbs, tx)$]'
    $]
                    )
    group by grouping sets ((t.name, p.partition), ())
    order by value(t.name, ''), p.partition
$| $@ call sqlStmts , , , , 's'
$#out                                              20161012 14:21:21
$#out                                              20161012 14:15:37
$#out                                              20161012 14:08:44
$#out                                              20161012 14:07:13
$#out                                              20161012 14:05:52
$#out                                              20161012 14:04:35
$#out