Db2 Reorg Table Example

Full table access except during the truncation phase of a reorg operation more control over the reorg process which runs asynchronously in the background and which can be paused resumed or stopped.

Db2 reorg table example. In the following example a mapping table and mapping table index are created. The following command can be used to view the reorganization flags for the tables and the indices of the tables. Reorg table db2 step 1 run reorgchk. Make sure you have enough space where the temporary table space resides to avoid unwanted failure and file system full issue.

Table b contains 2 million rows occupying 1 4 gb of data space and 3 gb of index space. In practise i just need to view the recent table reorg stats. Note that there are three values either dashes or asterisks. Q script to reorg a set of tables it is possible to create a script listing sequentially all the tables to be reorganized.

Some parts of this job use the exec sql utility to execute dynamic sql statements. Then a reorg tablespace job uses the mapping table and finally the mapping table is dropped. The owner of this script. An asterisk indicates that a table reorg is needed for that table based on the results of that specific query.

Running db2 udb eee version 7 1 on aix 4 3. Using a mapping table. The reorgchk update statistics report has two sections. Anyone have any reliable metrics for predicting the runtime of reorg.

For example you can pause an in progress reorg operation if a large number of update or delete operations are running against the table. Db2 reorg table employee index empid inplace start db2 reorg table employee inplace pause db2 reorg table homer employee inplace allow read access notruncate table resume the command to resume the reorg contains extra keywords to specify read access only and to skip the truncation step which share locks the table. The column at the far right is what tells us that a table reorg is actually needed. Dropping a column in table for example is one of the reorg pending operations.

Table a contains 18 million rows occupying 13 8 gb of data space and 2 2 gb of index space. Db2 reorg table schema name table name use temp tablespace. The first section is the table information and the second section is the indexes. Nevertheless there are certain operations in db2 which lead to the reorg pending state on the changed tables.

Reorg pending state indicates that we either must or should reorganize the data table because db2 will limit the access to the tables in that state. Fortunately the db2pd utility returns this information.