Index recovery
An index describes the order in which rows are read from a table. When indexes are recorded in the journal, the system can recover the index to avoid spending a significant amount of time rebuilding indexes during the IPL that follows an abnormal system end or while varying on an independent ASP after it was varied off abnormally.
When you journal tables, images of changes to the rows in the table are written to the journal. These row images are used to recover the table if the system, or independent ASP, ends abnormally. However, after an abnormal end, the system might find that indexes built over the table are not synchronized with the data in the table. If an access path and its data are not synchronized, the system must rebuild the index to ensure that the two are synchronized and usable.
When indexes are journaled, the system records images of the index in the journal to provide known synchronization points between the index and its data. By having that information in the journal, the system can recover both the data and the index, and ensure that the two are synchronized. In such cases, the lengthy time to rebuild the indexes can be avoided.
The IBM® i operating system provides several functions to assist with index recovery. All indexes on the system have a maintenance option that specifies when the index is maintained. SQL indexes are created with an attribute of *IMMED maintenance.
In the event of a power failure or abnormal server failure, indexes that are in the process of change might need to be rebuilt to make sure they agree with the data. All indexes on the server have a recovery option that specifies when the index should be rebuilt if necessary. All SQL indexes with an attribute of UNIQUE are created with a recovery attribute of *IPL, which means these indexes are rebuilt before the IBM i licensed program has been started. All other SQL indexes are created with the *AFTIPL recovery attribute, which means they are rebuilt after the operating system has been started or after the independent ASP has varied on. During an IPL or vary on of an independent ASP, you can see a display showing indexes that need to be rebuilt and their recovery options, and you can override these recovery options.
SQL indexes are not journaled automatically. You can use the Start Journal Access Path (STRJRNAP) command to journal any index created by SQL operations. The system save and restore functions allow you to save indexes when a table is saved by using ACCPTH(*YES) on the Save Object (SAVOBJ) or Save Library (SAVLIB) command. If you must restore a table, there is no need to rebuild the indexes. Any indexes not previously saved and restored are automatically and asynchronously rebuilt by the database.
Before journaling indexes, you must start journaling for the tables associated with the index. In addition, you must use the same journal for the index and its associated table.
Index journaling is designed to minimize additional output operations. For example, the system writes the journal data for the changed row and the changed index in the same output operation. However, you should seriously consider isolating your journal receivers in user ASPs when you start journaling your indexes. Placing journal receivers in their own user ASP provides the best journal management performance, while helping to protect them from a disk failure.