zOs/WK/PLANLOAD

$#@             $*** load old plan_tables to new
$>.fEdit() $@[
$=subsys=DD0G
$=db=DB2PLAN
$=cr=CMNBATCH
$=ts=- strip(left($cr, 7))
$=tstTst= '2012-02-07-15.00.00' $** cut off timestamp
$=crNew = $cr
if 1 then $@[   $*** load _new tables with replace from noPrefix
    $=tstOp= <=
    $=suNew= _NEW
    $=suOld=- ''
    $=resum1=LOG NO RESUME NO REPLACE COPYDDN(TCOPYD)
    $=resum2=LOG NO RESUME YES
$] else if 0 then $@[  $*** load noPrefix tables with resume from _old
    $=tstOp = >        $*** shrLevel change ist verboten
    $=suNew=- ''
    $=suOld= _OLD
    $=resum1=LOG YES RESUME YES
    $=resum2=$resum1
$] else if 0 then $@[  $*** load $crNewx tables from  _old
    $=tstOp = >        $*** to $crNew
    $=suNew=- ''
    $=crNew = A540769
    $=suOld= _OLD
    $=resum1=LOG NO RESUME NO REPLACE COPYDDN(TCOPYD)
    $=resum2=LOG NO RESUME YES
    $]
call sqlConnect $subsys
$;
$<=[            $*** Achtung richtige TS einfüllen| ohne LOBs|
${ts}G
${ts}I
${ts}J
${ts}Q
${ts}X
$]
$@for ts1 $@/ts/
    $=ts1=- strip($ts1)
    $=c1=- right($ts1, 1)
$@=[
//Y4PLANL$c1 JOB (CP00,KE50),'DB2 REO',
//         MSGCLASS=T,TIME=1440,
//         NOTIFY=&SYSUID,REGION=0M
//************ load $db.$ts1 *******
//S1       EXEC PGM=DSNUTILB,PARM='$subsys,Y4PLANL$c1.PLANLO'
//SYSMAP   DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSERR   DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTEMPL  DD DSN=$subsys.DBAA.LISTDEF(TEMPL),DISP=SHR
//SYSIN    DD *
$]
$=cx=0
$=resume=$resum1
$;
$<=[ select creator, name, type
         from sysibm.sysTables
         where dbName='$db' and tsName = '$ts1'
$] call sqlSel
$| $@forWith tb $@/tb/
  if $TYPE \== T then do
      say 'skipping' $CREATOR'.'$NAME 'because type' $TYPE
      iterate
      end
  if right($NAME, length($suNew)) \== $suNew then
      call err 'table' $NAME 'suffix \==' $suNew
  $=fTb=- left($NAME, length($NAME)-length($suNew))$suOld
  $=laCo=- catTbLastCol($CREATOR, $fTb)
$@=[
-- $CREATOR $fTb     : $-[if($laCo=='', 'missing', $laCo)$]
--      --> $NAME type $TYPE
$]
  if $laCo == '' | abbrev($fTb, '??N_STATEMENT_CACHE_T') then
      iterate
  $=cx=- $cx+1
  if abbrev($fTb,'PLAN_TABLE') then
      $=tstPrd = BIND_TIME  $tstOp $tstTst
  else if abbrev($fTb,'DSN_STATEMENT_CACH') then
      $=tstPrd = EXPLAIN_TS $tstOp $tstTst
  else if abbrev($fTb,'F2PLAN') then
      $=tstPrd =- 'TIMESTAMP' $tstOp $*+
         "'"left(space(translate($tstTst, "   ", "'-."), 0), 16)"'"
  else
      $=tstPrd = EXPLAIN_TIME $tstOp $tstTst
$@=[
EXEC SQL
  DECLARE CUR$cx CURSOR FOR
    SELECT
$]
$;
call catColCom $CREATOR, $fTb, $CREATOR, $NAME
$| $@for li $@[
  $=cn =- if(abbrev($li, '--'), '--', word($li, words($li)))
  if wordPos($cn, 'FIRSTPAGE LASTPAGE') > 0 then $@=[
        $-[left($li,1)$] cast(cast($cn
              as char(4) ccsid unicode for bit data)
              as char(4) ccsid unicode for mixed data)
            $cn
  $] else if wordPos($cn, 'LOWKEY HIGHKEY LOWBOUND HIGHBOUND'),
                  > 0 then $@=[
        $-[left($li,1)$] cast($cn
              as varchar(40) ccsid unicode for bit data)
            $cn
  $] else if wordPos($cn, 'LPTHIKEY LPTLOKEY'),
                  > 0 then $@=[
        $-[left($li,1)$] cast(cast($cn
              as varchar(40) ccsid unicode for bit data)
              as varchar(40) ccsid unicode for mixed data)
            $cn
  $] else $@=[
        $li
  $]
$]
$;
$@=[
        FROM $cr.$fTb
        WHERE $tstPrd
$*+     fetch first 1000000 rows only
        WITH UR
ENDEXEC
LOAD DATA $resume
  SORTDEVT DISK
  WORKDDN(TSYUTS,TSOUTS)
  INCURSOR CUR$cx
  INTO TABLE  $crNew.$NAME
$=resume=$resum2
$]
$/tb/
if $cr = $crNew then $@=[
LISTDEF LSP INCLUDE TABLESPACE $db.$ts1 ALL PARTLEVEL
COPY LIST LSP COPYDDN(TCOPYD) SHRLEVEL REFERENCE
LISTDEF LSB INCLUDE TABLESPACE $db.$ts1 BASE
RUNSTATS TABLESPACE LIST LSB
    INDEX(ALL KEYCARD) UPDATE ALL
    SHRLEVEL CHANGE
LISTDEF LSL INCLUDE TABLESPACE $db.$ts1 LOB
RUNSTATS TABLESPACE LIST LSL
    INDEX(ALL        ) UPDATE ALL
    SHRLEVEL CHANGE
$]
$/ts/
$]
$#out                                              20120207 15:26:28