0XST_John_Gera 2700020XST Visits (1899)
There are two approaches to table reor
Classic table reorganization uses a shadow copy approach, building a full copy of the table that is being reorganized.
During an inplace or online table reorg operation, portions of a table are reorganized sequentially. Data is not copied to a temporary table space; instead, rows are moved within the existing table object to reestablish clustering, reclaim free space, and eliminate overflow rows.
Advantages of offline reorganization
Disadvantages of offline reorganization
Advantages of online reorganization
Disadvantages of online reorganization
To monitor an OFFLINE REORG you can use one of the following methods:
1. GET SNAPSHOT FOR TABLES command
2. Starting DB2 V9.5 FP5 and DB2 V9.7 FP1 you can you the db2pd -reorg command
3. List History Reorg:
4. Select from the SNAPTAB_REORG administrative view or the SNAP_GET_TAB_REORG table function and check the REORG_TYPE
To Monitor an INPLACE REORG, you can use:
1. Select from the SNAPTAB_REORG administrative view o
2. ADMIN_GET_TAB_INFO table function and look at the INPL
3. List History Reorg:
-- --- ------------------ ---- --- ------------ ------------ --------------
MaryKKassey 2700055CKK Visits (1519)
How to run REORG table command from a CLI application against a DB2 database server?
REORG command is supported by command line processor, you cannot use the REORG command directly from a CLI application. In order to run REORG from a CLI application, you will have to use ADMIN_CMD stored procedure.
CALL SYSPROC.ADMIN_CMD ('REORG TABLE test')
The same REORG command when issued from the command line executes successfully.
db2 "reorg table test
DB20000I The REORG command completed succ
dlang 060001V98P Visits (1673)
Does runstats seem to be running longer than usual? There could be a few explinations for this. First you should consider if there is a significant amount of data than before, this could just be normal. If you are in a situation where you will be working with a larger volume of data, you may consider different sampling options with runstats. When collecting detailed statistics collection can consume considerable CPU and memory for large tables, and may take longer than planned. The various sampling options can provide accurate statistics with less resource usage.
However, there may be a chance that the runstats is taking longer simply because the data in the database is highly fragmented. If a reorganization of the data has not been done in a long time, and the runstats utility seems to take longer and longer, this may be an indication of highly fragmented data. If you are unsure that a reorg would be of benefit you can run the reorgchk tool. You may likely find that after a reorg of your key tables, the runstats performance should improve.
I/O is one of the most expensive operations that happens. Once a table is reorg-ed, the number of small, scattered i/o should go down, and larger, sequential i/o can happen, which would drastically improve IO. Often the number of trips made to storage will be a big factor in the throughput. With fewer, trips, getting large chunks of data, the throughput will go up. When making a lot of small i/o requests (and bouncing around the disk) we have all the overhead with making and processing the storage request and you can expect this type of frequent activity can consume alot of time. If there are frequent DML activities like inserts/deletes, fragmentation is going to happen, and that will effect the I/O effectiveness of the runstats if reorg maintenance isn't done.