On DB2 for z/OS version 10, an errant utility left MANY indexspaces in a status of "RW,RBDP" within a specific database. I can successfully use the REBUILD INDEXSPACE command to fix them one-by-one, but there are A LOT of them. So, I was hoping for some kind of wild-card or *ALL option, but that doesn't yet work for me.
Is there a way to do the equivalent of the following?
REBUILD INDEXSPACE (MYDB.*)
Thanks in advance!
You can't do an entire database at a time, but you could do some queries along with a LISTDEF to get a similar result.
First, find the indexes in question:
SELECT ' INCLUDE INDEX ' || RTRIM(CREATOR) || '.' || RTRIM(NAME)
FROM SYSIBM.SYSINDEXES
WHERE DBNAME = 'MYDB'
That will give you the list of indexes related to that database. Then, you can take the results as part of a larger LISTDEF. Here's some example JCL (honestly, I'm not sure how much of this is specific to my shop, so there may be some changes required):
//*****************************************************
//* RUN REBUILD INDEX UTILITY
//*****************************************************
//IXRBREST EXEC PGM=IEFBR14 DUMMY STEP FOR RESTART
//IXRBUTIL EXEC DB2UPROC,SYSTEM=DB2T,COND=(4,LT)
//STEPLIB DD DSN=DB2.PROD.SDSNLOAD,DISP=SHR
//DB2UPROC.SYSIN DD *
LISTDEF INDEXES
<insert generated list here>
REBUILD INDEX LIST INDEXES
SORTKEYS SORTDEVT SYSDA SHRLEVEL CHANGE
STATISTICS REPORT YES UPDATE ALL
MAXRO 240 LONGLOG CONTINUE DELAY 900 TIMEOUT TERM
DRAIN_WAIT 50 RETRY 6 RETRY_DELAY 30
That should get you the indexes that need to rebuild. If there are some that need to be rebuilt, and some that are fine, you could add SCOPE PENDING to the REBUILD INDEX utility, and it will only rebuild those in a pending state.