zOs/SQL/ALTERQTY

$#@
call sqlConnect dp4g
$<>
$<#[
 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 like 'DB2PDB%'
      group by dbName, tsName
 )
 select 'alter tablespace' || strip(dbName) || '.' || strip(tsName)
           || ' priQty -1 secQty -1;'
     where pri >  0 or sec > 0
     order by db, ts
     with ur
$]
call sqlSel
$| call fTabAuto
$#end
call sqlConnect dp4g
say "set sqlid sqlcode:" sqlUpdate(, "set current sqlid = 'S100447'")
$=dSp=200
$=dPa=1000
$=dTi=10
$=sleep=1
$;
$<#[
 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) 'tb,' cPa 'parts,' m.d.db'.'m.d.ts ,
                      ': sqlCode' sqlUpdate(, 'commit')
call sqlClose 66
$;
$#out                                              20160329 13:58:22
DB      TS       P S C
DB2PDB1 DMRPRAAA 1 1 1
DB2PDB1 DMRPRATR 1 1 1
DB2PDB1 DMRPRAUB 1 1 1
DB2PDB1 DMRPRAUC 1 1 1
DB2PDB1 DMRPRAUD 1 1 1
DB2PDB1 DMRPRAUF 1 1 1
DB2PDB1 DMRPRAUG 1 1 1
DB2PDB1 DMRPRAUM 1 1 1
DB2PDB1 DMRPRAUN 1 1 1
DB2PDB1 DMRPRAUS 1 0 1
DB2PDB1 DMRPRAUU 1 1 1
DB2PDB1 DMRPRTA2 1 1 1
DB2PDB1 DMRPRTB2 1 1 1
DB2PDB1 DMRPRTBD 1 1 1
DB2PDB1 DMRPRTBS 1 1 1
DB2PDB1 DMRPRTD2 1 1 1
DB2PDB1 DMRPRTDD 1 1 1
DB2PDB1 DMRPRTDS 1 1 1
DB2PDB1 DMRPRTF2 1 1 1
DB2PDB1 DMRPRTFS 1 1 1
DB2PDB1 DMRPRTP2 1 1 1
DB2PDB1 DMRPRTSF 1 1 1
DB2PDB1 DMRPRTSS 1 0 1
DB2PDB1 DMRPRTT2 1 1 1
DB2PDB1 DMRPRTTS 1 1 1
$#out                                              20120628 13:47:29
$#out                                              20120627 17:51:57