Monitoring the Progress of Repack Threads

A new onstat command, onstat -g pak, can display information about each repack thread and the progress of their repack operations:

informix: onstat -g pak

IBM Informix Extended Parallel Server Version 8.51.FN141 
-- On-Line -- Up 1 days 16:44:03 -- 94208 Kbytes

Data Repack Queue:
partition                rows    pages  approx  elapsed
number   status          packed  freed  % done  time      table name
0xb0002  queued          0       0      0       00:00:00  jc:jctab
0x30002  complete        89876   0      100     00:15:54  jc:jctab
0x50002  in prog (pack)  928     0      6       00:03:32  jc:jctab
0x70002  failed  (116)   22344   0      21      00:32:07  jc:jctab
0x90002  stopped (DDL)   4536    0      92      00:06:45  jc:jctab

5 total (1 pending, 1 in progress, 1 stopped, 1 complete, 1 failed)


Data Repack Threads:
 tid     tcb              rstcb            partnum
 78      3a700480         3a4472c8         0x50002

 1 active, 10 maximum (REPACK_THREAD_MAX)

Possible values for the status column are the following:

• queued - The request has been queued but has not been picked up yet by a repack thread
• in prog - The request is being processed
• complete - The request was processed successfully
• stopped - The request was interrupted
• failed - The processing of the request failed

Associated with the in prog status is one of the following actions:

• (open) - The partition is being opened
• (lock) - The partition (or a row) is being locked
• (d-scan) - Looking for data to pack
• (f-scan) - Looking for free space for the data
• (pack) - With data and free space for it, now doing the actual move
• (begwork) - Beginning a transaction
• (comwork) - Committing a transaction
• (critenter) - Trying to enter a critical section
• (shrink) - Empty space at the end of the partition is being freed back to the chunk
• (close) - Cleaning up after a successful repack
• (fail) - In the middle of handling a failure

Associated with the stopped status is one of the following reasons:

• (LTX) - The operation has been temporarily stopped because of a long transaction, but will resume momentarily
• (DDL) - A DDL operation on this partition was forced through using the Force DDL Execution feature
• (SHUTDN) - The server was shut down to quiescent mode, or is being shut down to off-line mode
• (CFG) - REPACK_THREAD_MAX was set to 0
• (TABLOG) - The table type was changed before processing could begin on the request
• (REVERT) - The server is being reverted
• (LAP) - A light append occurred while repacking the partition, which prevented us from freeing pages at the end of the table

Associated with the failed status is the error code behind the failure, as in the following example:

• 0x70002 failed (116) 22344 0 21 00:32:07 jc:jctab
• 0xa0002 failed (131) 7623 0 56 00:16:12 jc:jctab

Failures should happen very rarely.

The onstat -g pak command displays a slightly different output format if the RASHELP environment variable is set. The "approx % done" column is replaced by a "method" column, which indicates whether the data is being repacked by page or by row:

informix> export RASHELP=1
informix: onstat -g pak

IBM Informix Extended Parallel Server 
Version 8.51.FN141 -- On-Line -- Up 1 days 16:44:03 -- 94208 Kbytes

Data Repack Queue:
partition                    rows        pages           elapsed
number     status            packed      freed   method  time      table name
0xb0002    queued            0           0               00:00:00  jc:jctab1
0x30002    complete          89876       0       row     00:15:54  jc:jctab2
0x50002    in prog (pack)    928         0       page    00:03:32  jc:rawtab
0x70002    failed  (116)     22344       0       row     00:32:07  jc:jctab4
0x90002    in prog (d-scan)  4536        0       row     00:06:45  jc:jctab5

5 total (1 pending, 2 in progress, 0 stopped, 1 complete, 1 failed)


Data Repack Threads:
 tid     tcb              rstcb            partnum
 78      3a700480         3a4472c8         0x50002
 106     3a7088f0         3a480310         0x90002

 2 active, 2 maximum (REPACK_THREAD_MAX)

For more information about command-line options of the onstat utility, see Appendix A. Syntax Summaries of Selected Utilities.