How to make a batch script for rebuilding all indexes? (DBMR2924)

~ 0 min
2020-09-29 14:10

Enter follow SQL commands to check and rebuild all indexes. For example, there is a database including five indexes.

 

  1. 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

Average rating 0 (0 Votes)

You cannot comment on this entry

Tags