zOs/REXX/XBREPASE

$#@
$= lst =. jBuf()
$<>
$>.lst
$<=[
table              old  new1 new2 column      valueLess    $]
$<dsn.dbadm.texv(xb0902C)
$** XB.RZ2.P0.OVRFLD.DB2.SPLTKY.D030916.NEW => C, ERR => D
$*** $<WOK.XB.F216914.SPLIT.LIST.TXT
$@. csvWordRdr( , 'u')
$<>
$*( ******************************************************

    reparttitioning for overloaded elar partitions

    generates a job, that adds the new partitions
         and then submits jobs that copy the data
         from each old to two new partitions

    Input: 6 words on each line (separated by spaces or tab chars)
        table:     tablename (without creator)
        old:       old partition number
        new1:      first new partition number
        new2:      second new partition number
        column:    splitCondition: column <  valueLess ==> new1
        valueLess: splitCondition: column >= valueLess ==> new2

    e.g.
table              old  new1 new2 column      valueLess
XBDPM027IT002001 102 110 111 EA1STRN x'2016070722302476158640401A7440F100000006'

20. 8.16 Walter
*******************************************************$*)
$<>
$>. fEdit('::v')
$<.lst
$@=[
declare global temporary table session.l
( seq int, tb varchar(30)
, old int, new1 int, new2 int
) ccsid unicode not logged
--with no data
on commit drop table
;
$= sq=0
$forWith $@=[
   $= sq =- $sq + 1
   insert into session.l values
     ($sq, '$TABLE', $OLD, $NEW1, $NEW2);
   $]
with t as
(
  select l.seq, t.creator, t.name, l.old, l.new1, l.new2
         , t.dbName, t.tsName
         , s.dbId, s.psid
    from session.l l
      join sysibm.sysTables t
        on t.creator = 'BUA' and t.name = l.tb
      join sysibm.sysTableSpace s
        on t.dbName = s.dbName and t.tsName = s.name
)
, r as
(
  select t.*
        , o.totalRows - n1.totalRows - n2.totalRows diffRows
        , o.totalRows  o0Rows
        , n1.totalRows n1Rows
        , n2.totalRows n2Rows
        , n1.loadRLastTime n1Load
        , n2.loadRLastTime n2Load
        , n1.reorgLastTime n1reorg
        , n2.reorgLastTime n2reorg
    from t
      left join sysibm.sysTableSpaceStats o
        on t.dbid = o.dbid and t.psid=o.psid
          and o.partition = t.old
      left join sysibm.sysTableSpaceStats n1
        on t.dbid = n1.dbid and t.psid=n1.psid
          and n1.partition = t.new1
      left join sysibm.sysTableSpaceStats n2
        on t.dbid = n2.dbid and t.psid=n2.psid
          and n2.partition = t.new2
)
select * from r
$]
$|
call sqlConnect dvbp
call sqlStmts
call sqlDisconnect
$#out                                              20160903 22:44:46
$#out                                              20160903 21:26:16
$#out                                              20160903 21:12:59
$#out                                              20160903 21:01:37
$#out                                              20160903 21:01:14
$#out                                              20160903 20:56:50
$#out                                              20160903 20:54:46
$#out                                              20160903 20:53:57