zOs/REXX/XBREPA

$#@
$= 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
*******************************************************$*)
$<>
$<.lst
call sqlConnect dvbp
$forWith $@[
    tb = $TABLE
    if symbol('m.table.tb') \== 'VAR' then $@[
            m.table.tb.cols = catTbCols('BUA', tb)
            if word(m.table.tb.cols, 1) <> 'EN1PART' then
                call err 'tb' tb 'not en1part:' m.table.tb.cols
            m.table.tb = sql2One("select partitions" ,
              "from sysibm.sysTables t join sysibm.sysTableSpace s" ,
                "on t.dbName = s.dbName and t.tsName = s.name",
              "where t.creator = 'BUA' and t.name = '"tb"'")
            m.table.tb.part = m.table.tb
        $]
    if $NEW1 <> m.table.tb.part + 1 | $NEW2 <> m.table.tb.part + 2 then
         say 'tb' tb m.table.tb 'parts added to' m.table.tb.part,
             'mismatches' $TABLE $OLD $NEW1 $NEW2
         /*call err 'tb' tb m.table.tb 'parts added to' m.table.tb.part,
             'mismatches' $TABLE $OLD $NEW1 $NEW2 */
    m.table.tb.part = $NEW2
    $]
call sqlDisconnect
$<>
$>. fEdit()
$@=/genJob/
//XBREPAST JOB (CP00,KE50),'DB2 ADMIN',
//           TIME=1440,REGION=0M,CLASS=M1,SCHENV=DB2ALL,
//           MSGCLASS=T,NOTIFY=&SYSUID
//* add partitions ***************************************************
//ADDPART  EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99
//SYSTSIN    DD *
    DSN SYSTEM(DVBP)
    RUN PROGRAM(DSNTEP2) PARMS('ALIGN(LHS)') PLAN(DSNTEP2)
//SYSTSPRT   DD SYSOUT=*
//SYSPRINT   DD SYSOUT=*
//SYSUDUMP   DD SYSOUT=*
//SYSTERM    DD DUMMY
//SYSIN      DD *
set current sqlid = 'S100447';
$<>
$<.lst
$forWith $@=[
??ter table BUA.$TABLE
     add partition ending at($NEW1) ;
??ter table BUA.$TABLE
     add partition ending at($NEW2) ;
commit;
$]
//       IF ADDPART.RUN AND RC = 0 THEN
//* submit load jobs *************************************************
//SUBMIT   EXEC PGM=IEBGENER
//SYSPRINT   DD SYSOUT=*
//SYSUT2     DD SYSOUT=(,INTRDR)
//SYSUT1   DD DATA,DLM='}}'
$<>
$<.lst
$=jx=0
$forWith $@=/oneSplit/
$= jx =- $jx + 1
$= jn =- 'XBREPA'right($jx // 10, 2, 0)
//$jn  JOB (CP00,KE50),'XB REPA',
//           TIME=1440,REGION=0M,CLASS=M1,SCHENV=DB2ALL,
//           MSGCLASS=T,NOTIFY=&SYSUID
//* job $jx $jn repartition $TABLE **********************
//* from $OLD to $NEW1 and $NEW2
//* split $COLUMN x'$VALUELESS'
//LOAD$jx  EXEC PGM=DSNUTILB,PARM='DVBP,$jn.LOAD$jx'
//SYSPRINT   DD SYSOUT=*
//UTPRINT    DD SYSOUT=*
//SYSTEMPL   DD DSN=DVBP.DBAA.LISTDEF(TEMPL),DISP=SHR
//SYSIN    DD *
LISTDEF ON INCLUDE TABLE BUA.$TABLE PARTLEVEL $OLD
           INCLUDE TABLE BUA.$TABLE PARTLEVEL $NEW1
           INCLUDE TABLE BUA.$TABLE PARTLEVEL $NEW2
LISTDEF NN INCLUDE TABLE BUA.$TABLE PARTLEVEL $NEW1
           INCLUDE TABLE BUA.$TABLE PARTLEVEL $NEW2

COPY LIST ON COPYDDN(TCOPYD)
  FULL YES PARALLEL
  SHRLEVEL CHANGE
$@ tb  = $TABLE
$@ cls = m.table.tb.cols
$do cx=1 to 2 $@=[

EXEC SQL
  DECLARE CUR$cx CURSOR FOR
    SELECT ${NEW$cx} EN1PART
  $do wy=2 to words(cls) $@=[
         , $-[word(cls, wy)$]  $]
        FROM BUA.$TABLE
        WHERE EN1PART = $OLD
          AND $COLUMN
    $@ if cx == 1 then
            <  $VALUELESS
    $@ else
            >= $VALUELESS
ENDEXEC
$]

LOAD DATA
   LOG NO
   SORTDEVT DISK
   WORKDDN(TSYUTS,TSOUTS)
 INTO TABLE  BUA.$TABLE PART $NEW1
      INCURSOR CUR1 RESUME NO REPLACE
      COPYDDN TCOPYS STATISTICS INDEX ALL KEYCARD
 INTO TABLE  BUA.$TABLE PART $NEW2
      INCURSOR CUR2 RESUME NO REPLACE
      COPYDDN TCOPYS -- no duplicated  STATISTICS INDEX ALL KEYCARD

REORG TABLESPACE LIST NN
    LOG NO
    SORTDATA
    SORTKEYS
    SORTDEVT DISK
    COPYDDN(TCOPYD)
    SHRLEVEL CHANGE
    DRAIN_WAIT 40
      RETRY 20
      RETRY_DELAY 180
      MAXRO 40
      DRAIN WRITERS
      LONGLOG CONTINUE
      DELAY 1200
      TIMEOUT TERM
    UNLDDN TSRECD
    UNLOAD CONTINUE
    PUNCHDDN TPUNCH
    DISCARDDN TDISC
    STATISTICS
      INDEX ALL KEYCARD
      UPDATE ALL
$/oneSplit/
}}
//       ENDIF
$/genJob/
$#out                                              20160903 21:10:42
$#out                                              20160903 17:43:51
$#out                                              20160903 09:29:44
$#out                                              20160903 09:21:26
$#out                                              20160903 09:19:16
$#out                                              20160903 09:18:29
$#out                                              20160902 21:41:31
$#out                                              20160902 21:40:53
$#out                                              20160902 21:39:12
$#out                                              20160902 13:00:11
$#out                                              20160902 12:59:24
$#out                                              20160827 12:32:43
$#out                                              20160826 21:25:59
$#out                                              20160826 11:44:22
fatal error in WSH: tb XBDNT003HS001001 43 parts added to 45 mismatches XBDNT...
in wsh phase run
$#out                                              20161011 13:52:37