Reorganizes a table within its current storage area with one command while the application continues to run.

Syntax

proutil db-name -C tablereorg [owner-name.] table-name 
   [ info ] |    
   [ [ resume | resume-numrecs n | resume-rowid n] 
   [ nosmartscan ]
   [ restrict [ EQ value ] | 
          [ LT | LE  high-value ] |
          [ GT | GE  low-value [ AND LT | LE high-value ] ] ]
   [ useindex index-name ] [ recs n ] 
   [ searchdepth n ] [ reusepercent n ] ]
   [ tenant tenant-name | group group-name |
          partition partition-name | composite initial ]

Parameters

db-name
Specifies the name of the database containing the table to be reorganized.
owner-name
Specifies the owner of the table containing the data you want to reorganize. You must specify an owner name unless the table name is unique within the database, or the table is owned by PUB. By default, PUB owns all tables created and used by the ABL.
table-name
Specifies the name of the table to be reorganized. The table must reside in a Type II storage area.
info
Reports the resume information of the specified table for a cancelled PROUTIL TABLEREORG operation, including:
  • The rowid of the last record processed
  • The number of records processed
  • The cluster of the Type II storage area where the newly reorganized records will be stored on resume
  • The index used
  • Which area the table was in when it was last reorganized
  • (When used with the -verbose option) the number of records found in the table. This helps you see how far along in the reorganization process the utility was when it was cancelled and how much more data remains to be processed.
resume

Requests that a cancelled table reorganization operation resume where it left off, and disables smart scanning. The operation uses the internally stored information reported by the info option. The reorganization resumes when the specified number of records have been skipped or when the saved rowid is encountered.

If there is no resume location stored for this table or if the table was moved to a different storage area since the last reorganization process was cancelled, an error is reported, and the table reorganization operation ends.

The stored resume information is updated when the associated transaction finishes processing the number of records specified by recs.

resume-numrecs

Explicitly requests that a cancelled table reorg operation resume after resume-numrecs records have been skipped when scanning the useindex index, regardless of the saved information.

You can use this option with the resume-rowid option. When you use resume-rowid and resume-numrecs together, the one reached first determines the resume point.

resume-rowid

Explicitly requests that a cancelled table reorg operation resume at the point where the specified rowid is located in the index.

This option can be used with the resume-numrecs option. When you use resume-rowid and resume-numrecs together, the one reached first determines the resume point.

nosmartscan

Disables smart scanning, like the resume option does, and reorganizes the table from the beginning.

PROUTIL TABLEREORG performs a smart scan by default, skipping reorganization of records that are already in order according to the chosen index. Smart scans optimize PROUTIL TABLEREORG for very large tables.

restrict

Optimizes table reorganization for large tables by reorganizing sections of a table instead of the entire table. Omitting restrict reorganizes the entire table.

The parameters that follow restrict specify how much of the table is scanned for reorganizing. The parameters consist of an operator and value pair that create a range index search based on the first component field of the index specified in useindex. The operator and value specify the starting position and possibly an ending position for scanning. The AND option, followed by an operator and value pair, indicates the (optional) ending position. If you omit the ending position, the table is reorganized from the start position to the end of the table, according to the specified index. Valid operators for the starting position are EQ, GT, GE, LT, and LE, whereas for the ending position, valid operators are LT and LE. If a data value for the restrict option requires embedded spaces, enclose the value in quotation marks.

EQ

Scans only portions of the table equal to the specified value. EQ is the only valid operator when the first component of the index used in the restrict clause is Boolean.

GT

Scans only portions of the table greater than the specified low-value.

GE

Scans only portions of the table greater than or equal to the specified low-value. The supplied value must be valid for the data type of the index field specified by the useindex parameter. Data values requiring embedded spaces must be enclosed in quotes.

low-value

The value supplied for the starting position. This value must be valid for the data type of the index field specified by the useindex parameter. Data values requiring embedded spaces must be enclosed in quotes.

LT

LT can be used with or without the AND option as described in the syntax above. The associated high-value determines the end point of the reorganization. When used with the AND option, the start of the reorganization is determined by the GT or GE option described above. When used without AND, all data up to the high-value is reorganized.

The value supplied must be valid for the data type of the index field specified by the useindex parameter. Data values requiring embedded spaces must be enclosed in quotes.

LE

LE can be used with or without the AND option as described in the syntax above. The associated high-value determines the end point of the reorganization. When used with the AND option, the start of the reorganization is determined by the GT or GE option described above. When used without AND, all data up to and including the high-value is reorganized.

The value supplied must be valid for the data type of the index field specified by the useindex parameter. Data values requiring embedded spaces must be enclosed in quotes.

AND

Optional parameter that allows the use of a high-value (see below) in conjunction with the GT or GE low-value.

high-value

The optional value supplied for the ending position. This value must be valid for the data type of the index field specified by the useindex parameter. Data values requiring embedded spaces must be enclosed in quotes.

useindex

Specifies an active index to be used for accessing records in the table for the table reorganization operation. This index must be an active non-word index. If not specified, the primary index is used.

PROUTIL TABLEREORG reorganizes the table according to the sort order of this index. To reduce logical scatter when the application requests data from the database, specify an index that is frequently used by the application. Specifying a non-unique index requires additional resources to perform the reorganization and is not expected to complete as quickly as when specifying a unique index.

recs
Specifies the number of records to process per transaction. If you omit this parameter, the table reorganization proceses 100 records per transaction.
tenant tenant-name
For databases enabled for multi-tenancy, specifies that the partition of the table owned by tenant-name is to be reorganized. If tenant is specified, and tenant-name is not valid, or the table is not a multi-tenant table TABLEREORG exits with an error.
group group-name
For databases enabled for multi-tenancy, specifies that the partition of the table owned by group-name is to be reorganized. If group is specified, and group-name is not valid, or the table is not a multi-tenant table, TABLEREORG exits with an error.
partition partition-name
For databases enabled for table partitioning, specifies that the partition named partition-name is to be reorganized. If partition is specified, and partition-name is not a valid partition, or the table is not a partitioned table, TABLEREORG exits with an error.
composite initial

For databases enabled for table partitioning, specifies that the initial composite partition is to be reorganized.

searchdepth
Specifies the percentage of the record free chain to search for free blocks to be used during the table reorganization. The minimum is 0, the maximum is 100, and the default is 100.
reusepercent
Specifies the percentage of the free blocks within a cluster before being considered for use by table reorganization. The minimum is 0, the maximum is 100, and the default is 100.

You may specify the following general database parameters such as -verbose, -lkwtmo, and -rusage to alter the information reported.

By default (without the -verbose parameter) TABLEREORG reports status after processing recs records and the associated transaction has committed.

When you use -silent with the -verbose parameter, TABLEREORG reports completion statistics plus the number of records found in the table.

Notes

  • PROUTIL TABLEREORG is not allowed on system tables. If you use TABLEREORG on auditing tables, such as _aud-audit-data and _aud-audit-data-value, or CDC tables, such as _Cdc-Change-Tracking, OpenEdge returns the error Table Reorg operation not allowed on system tables.
  • As the table is reorganized, the rowid of the table’s records changes. This may affect applications with browse or result list data.

  • The smart scanning mechanism and resume options cannot be completely accurate for non-unique indexes due to the sorting order of the row IDs within each non-unique entry.

    During smart scans, according to the index, the first non-unique entry in a series of non-unique entries may not be the next entry in the current record block indicating that a reorganization start point has been found.

    When you use resume, resume-numrecs, or resume-rowid for the non-unique index, the order of duplicated keys can change between runs and therefore it does not guarantee reorganization of all the duplicated key records.

  • If you omit a resume option, a cancelled table reorganization restarts from the first record referenced in the associated index.
  • If a record lock conflict occurs during table reorganization processing, the startup parameter -lkwtmo (lock wait timeout) applies. When a record lock timeout occurs, current changes in process commit, and the record lock is retried. If another timeout occurs, the operation skips that record to prevent deadlock with concurrent operations on the same table that use different record ordering. For PROUTIL TABLEREORG, -lkwtmo defaults to 10 seconds, but -lkwtmo specified on the PROUTIL TABLEREORG command line overrides this default.
  • The table reorganization operation reports "Records found" and "Records processed."
  • Make sure that you specify the right data type for the operator in the restrict option clause. The value must have a valid data type for the first component field. When you use the AND option, the only valid operators for the start position are GT and GE.
  • This example reorganizes the mtabl1 table:
    PROUTIL testdb -C TABLEREORG pub.mtabl1 TENANT 

    Default info
Table reorg of mtabl1 resume rowid: 84359, rec count: 1000 cluster: 81920 using index: idx_fint1_prim_u area: 10 (20045)
Table reorganization operation info completed successfully. (20043)
  • This example reorganizes a specified section of the abl1 table:
    PROUTIL testdb -C TABLEREORG pub.abl1 nosmartscan restrict EQ 220430855876990168 useindex idx_2comp_win64 -verbose -rusage 

    Records found: 25 (20200)
Time to count 25 records: 0.000 (20197)
Blocks on record free chain: 242 (20196)
Time to scan 242 blocks on the record free chain: 0.008 (20195)
Commit total - trans: 1, recs 24, last rowid: 9508, partition: 0 trid: 908 (20064)

Resource usage: CPI user 0.002974 system 0.007870
Resource usage: DISK reads: 1720 KB at 33 MB/sec, writes: 28 KB at 1 MB/sec (17262)
Elapsed time: 0.051 (20198)
Total records processed: 25 (20060)
Table reorganization operation info completed successfully. (20043)
  • This example reorganizes a specified section of the pub.abl1 table. The GT and LT operators restrict the section to a specified range of fields in the index:
    PROUTIL testdb -C TABLEREORG pub.abl1 restrict GT 2935660178596213317 and LT 2204308585876990168 useindex idx_2comp_uint64 -verbose -rusage 

    Records found: 25 (20200)
Time to count 25 records: 0.000 (20197)
Blocks on record free chain: 242 (20196)
Time to scan 242 blocks on the record free chain: 0.008 (20195)
Commit total - trans: 1, recs 24, last rowid: 9508, partition: 0 trid: 908 (20064)

Start point identified - Split record and record size < max space per block. 
rowid: %j, rec size: %i bytes per block: %i rtc: %i
Resource usage: CPI user 0.002974 system 0.007870
Resource usage: DISK reads: 1720 KB at 33 MB/sec, writes: 28 KB at 1 MB/sec (17262)
Elapsed time: 0.051 (20198)
Total records processed: 25 (20060)
Table reorganization operation info completed successfully. (20043)