How to make a batch script for rebuilding all indexes? (DBMR2924)
Enter follow SQL commands to check and rebuild all indexes. For example, there is a database including five indexes.
- Check “CREATE_TIME” of original indexes.
dmSQL> SELECT CREATE_TIME, TRIM(TABLE_OWNER) || '.' || TRIM(TABLE_NAME) as INDEX_NAME from SYSINDEX;
dmSQL> SELECT CREATE_TIME, TRIM(TABLE_OWNER) || '.' || TRIM(TABLE_NAME) as INDEX_NAME from SYSINDEX; CREATE_TIME INDEX_NAME
=================== ===================
2020/01/31 14:55:02 SYSADM.T1
2020/01/31 14:55:13 SYSADM.T2
2020/01/31 14:56:23 SYSADM.T3
2020/01/31 14:57:34 SYSADM.T4
2020/01/31 14:58:45 SYSADM.T5
2. Get all of the rebuild indexes command.
dmSQL> SELECT 'REBUILD INDEX ' ||RTRIM(INDEX_NAME) || ' FOR ' || RTRIM(TABLE_OWNER) || '.' || RTRIM(TABLE_NAME) || ';' FROM SYSINDEX ORDER BY TABLE_OWNER,TABLE_NAME;
REBUILD INDEX IDX1 FOR SYSADM.T1;
REBUILD INDEX IDX2 FOR SYSADM.T2;
REBUILD INDEX IDX3 FOR SYSADM.T3;
REBUILD INDEX IDX4 FOR SYSADM.T4;
REBUILD INDEX IDX5 FOR SYSADM.T5;
3. Copy these rebuild commands into a script file, and run it.
dmSQL> run c:\rebuild.scp;
4. Execute step1 again. All indexes of “CREATE_TIME” updated.
dmSQL> SELECT CREATE_TIME, TRIM(TABLE_OWNER) || '.' || TRIM(TABLE_NAME) as INDEX_NAME from SYSINDEX;
CREATE_TIME INDEX_NAME
=================== ===================
2020/01/31 16:14:40 SYSADM.T1
2020/01/31 16:14:40 SYSADM.T2
2020/01/31 16:14:40 SYSADM.T3
2020/01/31 16:14:40 SYSADM.T4
2020/01/31 16:14:40 SYSADM.T5
Version: 5.2.2
Product: Normal/Bundle
Platform: Windows/Linux