CREATE INDEX may fail with SQL0901N if log files are not available
db2scope 310002R9XW Visits (580)
When creating indexes, Db2 may need to retrieve log files from archive to complete the indexes creation.
If the requested log files are not found, CREATE INDEX will fail with the following error :
SQL0901N The SQL statement or command failed because of a database system
error. (Reason "Map error to -901".) SQLSTATE=5800
The following is an example that log files are requested:
The following is an example that a log file is requested but could not be found:
The following explains why log files might be needed when creating indexes. Concurrent read/write
access to the table might be permitted while an index is being created. This is also known as Online
Index Creation(OLIC). The conditions for OLIC to be enabled are specified in "CREATE INDEX
statement" - Notes section :
catch-up phase, log records are requested through log read API which may trigger log retrieval if the log
files are not already in active log path.
Note in pureScale environment, by default Db2 only allows index create to occur with read only access
to the table.The enhancements are made in V18.104.22.168 to allow concurrent write by setting the requisite
registry variable to DB2_
be requested for all members in the cluster.
If you have auto log pruning enab
want to set NUM_DB_BACKUPS and REC_HIS_RETENTN to a reasonable longer value to avoid aggressive
log deletion so that in case Db2 needs to access log files, log files can be retrieved.
Number of database backups to reta
Recovery history retention (day