Auxiliary warning status

A base table space or LOB table space in auxiliary warning (AUXW) status remains available for processing by SQL even though it contains invalid LOBs.

Db2 can access all rows of a base table space that is in AUXW status. SQL can update the invalid LOB column and delete base table rows, but it cannot retrieve the value of the LOB column. If Db2 attempts to access an invalid LOB column, a -904 SQL code is returned. The AUXW status remains on the base table space even when SQL deletes or updates the last invalid LOB column.

The following situations are examples of when AUXW status is set:

  • The CHECK DATA utility is run with the AUXERROR INVALIDATE option, and at least one LOB column has an invalidated LOB.
  • CHECK DATA is run with the AUXERROR REPORT option and encounters only invalid LOB columns and no other LOB column errors. In this case, the base table space is set to AUXW status.
  • A base table space and its LOB table spaces are recovered to the current point in time in the same RECOVER utility invocation, and both the base table space and the LOB table spaces are defined with the NOT LOGGED attribute. In this case, if updates were made to the LOB table spaces after the recoverable point, AUXW status is set on the LOB table spaces.
  • An invalid LOB column is found by the RECOVER utility after the following series of events:
    1. The LOB table space was defined with the NOT LOGGED attribute.
    2. The LOB table space was recovered.
    3. The LOB was updated since the last image copy.

To reset AUXW status, take the actions that are described in the following table.

Table 1. Resetting auxiliary warning status
Status Abbreviation Object affected Corrective action
Auxiliary warning AUXW Base table space
  1. Update or delete invalid LOBs and XML objects by using SQL.
  2. If an orphan LOB exists or a version mismatch exists between the base table and the auxiliary index, use the REPAIR utility to delete the LOB from the LOB table space.
  3. Run the CHECK DATA utility to verify the validity of LOBs and XML objects and reset AUXW status.
Auxiliary warning AUXW LOB table space
  1. Update or delete invalid LOBs and XML objects by using SQL.
  2. If an orphan LOB exists or a version mismatch exists between the base table and the auxiliary index, use the REPAIR utility to delete the LOB from the LOB table space.
  3. Run the CHECK LOB utility to verify the validity of LOBs and reset AUXW status.