zOs/SQL/ALTERQTJ

//A540769W JOB (CP00,KE50),'DB2 REO',                                   00010000
//         MSGCLASS=T,TIME=1440,                                        00020000
//         NOTIFY=&SYSUID,REGION=0M                                     00030000
//*MAIN CLASS=LOG0                                                      00040000
//S1       EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99,                      00020001
//            PARM='%WSH'
//SYSPROC   DD DSN=A540769.WK.REXX,DISP=SHR
//SYSPRINT  DD SYSOUT=*
//SYSUDUMP  DD SYSOUT=*
//SYSTSPRT  DD SYSOUT=*
//OUT       DD SYSOUT=*
//SYSTSIN   DD DUMMY
//WSH       DD *
$#@
call sqlConnect dbba
say "set sqlid sqlcode:" sqlUpdate(, "set current sqlid = 'S100447'")
$=dSp=200
$=dPa=1000
$=dTi=10
$=sleep=5
$;
$<#[
 with s as
 (
 SELECT strip(DBNAME) db, strip(tsName) ts,
         sum(case when pqty = -1 then 0 else 1 end) pri,
         sum(case when sqty = -1 then 0 else 1 end) sec,
         count(*) cnt
      from SYSIBM.SYSTABLEPART
      where dbname not like 'DSNDB%' and dbName not like 'WKDB%'
      group by dbName, tsName
 )
 select * from s
     where pri >  0 or sec > 0
     order by db, ts
     with ur
$]
call sqlQuery(55)
cPa = 0
lSp = 0
do cSp=1 while sqlFetch(55, d)
    cPa = cPa + m.d.cnt
 /* say m.d.db m.d.ts m.d.pri m.d.sec m.d.cnt */
    s1 = 'alter tablespace' m.d.db'.'m.d.ts
    if m.d.pri <> 0 then
        call sqlUpdate , s1 'priqty -1', '* say'
    if m.d.sec <> 0 then
        call sqlUpdate , s1 'secqty -1', '* say'
    if cSp >= lSp | cPa >= lPa | time('e') >= lTi then do
        say time() 'commit' cSp 'tb,' cPa 'parts,' m.d.db'.'m.d.ts ,
                      ': sqlCode' sqlUpdate(, 'commit')
        call sleep $sleep, 0
        lSp = cSp + $dSp
        lPa = cPa + $dPa
        lTi = time('e') + $dTi
        end
    end
say time() 'end commit' (cSp-1) 'tb,' cPa 'parts,' m.d.db'.'m.d.ts ,
                      ': sqlCode' sqlUpdate(, 'commit')
call sqlClose 55
$;
$<#[
 with s as
 (
 SELECT strip(ixCreator) cr, strip(ixName) ix,
         sum(case when pqty = -1 then 0 else 1 end) pri,
         sum(case when sqty = -1 then 0 else 1 end) sec,
         count(*) cnt
      from SYSIBM.SYSIndexPART ip
         join sysibm.sysIndexes ix
           on ip.ixcreator = ix.creator and ip.ixName = ix.name
      where ix.dbname not like 'DSNDB%' and  ix.dbName not like 'WKDB%'
      group by ixCreator, ixName
 )
 select * from s
     where pri > 0 or sec > 0
     order by cr, ix
     with ur
$]
call sqlQuery(66)
cPa = 0
lSp = 0
do cSp=1 while sqlFetch(66, d)
    cPa = cPa + m.d.cnt
 /*  say m.d.cr m.d.ix m.d.pri m.d.sec m.d.cnt */
    s1 = 'alter index' m.d.cr'."'m.d.ix'"'
    if m.d.pri <> 0 then
        call sqlUpdate , s1 'priqty -1', '* say'
    if m.d.sec <> 0 then
        call sqlUpdate , s1 'secqty -1', '* say'
    if cSp >= lSp | cPa >= lPa | time('e') >= lTi then do
        say time() 'commit' cSp 'ix,' cPa 'parts,' m.d.cr'.'m.d.ix ,
                      ': sqlCode' sqlUpdate(, 'commit')
        call sleep $sleep, 0
        lSp = cSp + $dSp
        lPa = cPa + $dPa
        lTi = time('e') + $dTi
        end
    end
say time() 'end commit' (cSp-1) 'ix,' cPa 'parts,' m.d.db'.'m.d.ts ,
                      ': sqlCode' sqlUpdate(, 'commit')
call sqlClose 66
$;
$#out                                              20120628 17:30:19
$#out                                              20120628 13:47:29
$#out                                              20120627 17:51:57