Rebuild synopsis tables - REORG SYNOPSIS command

Without taking your database offline, you can recover data from a corrupted synopsis table with the REORG SYNOPSIS command. When applicable, you can also use the REORG SYNOPSIS command to reduce the table size for synopsis tables that use excessive storage space due to sparsity in data pages.

Restrictions

Note: This command is only available in Db2 version 11.5.9 and later.
Consider these restrictions before you run the REORG SYNOPSIS command:
  • During a rebuild process, the synopsis table is not available for read or write operations. When you rebuild a synopsis table, the corresponding base table is in exclusive-use mode, which blocks all access to the table.
  • The REORG SYNOPSIS command needs to be the first statement in a unit of work (UOW). If the command is not the first statement, then the error SQL0428N returns.

Command syntax

The syntax of the REORG SYNOPSIS command, reorg synopsis for table, requires the name of the base_table before the word rebuild:

reorg synopsis for table <base_table> rebuild

Example

In this example command, customer is the name for the base table:


reorg synopsis for table customer rebuild

When the REORG SYNOPSIS command is successful, the output shows this message:


DB20000I  The REORG command completed successfully.

Troubleshooting

Several situations will result in an error when running the REORG SYNOPSIS command, such as:
  • If the REORG SYNOPSIS command fails due to another Db2 error (such as running out of memory or disk space), the database returns SQL2223N. The state of the synopsis table then becomes unusable. When the synopsis table is in an unusable state, these conditions occur:
    • The synopsis table will not be included in any query plan. In this case, query performance can be negatively impacted.
    • Querying the synopsis table directly returns error SQL0668N, reason code 12.
    The synopsis table remains in an unusable state until the initial error is corrected and the REORG SYNOPSIS command runs successfully.
  • In the case that the REORG SYNOPSIS command runs against a table without a corresponding synopsis table, a SQL2224N error returns:
    $ db2 "create table t (c varchar(2000)) organize by column"
DB20000I  The SQL command completed successfully.

$ db2 reorg synopsis for table t rebuild
SQL2224N  The synopsis table for the specified table does not exist.
    Note: The corresponding synopsis table is not created if any of the following conditions are true:
    • Base table is not columnar
    • For columnar tables, if no columns in the base table qualify to have a corresponding synopsis column in the synopsis table. These conditions do not qualify a column to have a corresponding column:
      • Column length is greater than 1000 bytes
      • Datatype of column is Boolean