table or fragment arguments: Compress data and optimize storage (SQL administration API)
Use SQL administration API functions with table or fragment arguments to create compression dictionaries, to estimate compression ratios, to compress data in tables and table fragments, to consolidate free space (repack), to return free space to a dbspace (shrink), to uncompress data, and to delete compression dictionaries.
When you run SQL administration API compression and uncompression commands, you compress and uncompress row data and simple large objects in dbspaces. You can also specify whether to compress or uncompress only row data or only simple large objects in dbspaces.
Command arguments
The following table describes each argument.
| Argument | Description |
|---|---|
| blobs | Specifies that you want to compress or uncompress only simple large objects in dbspaces and not row data. |
| compress | Compresses all existing rows in-place, without
moving them (without repacking the table). This option automatically compresses row data and simple large objects in dbspaces. To compress only row data or only simple large objects in dbspaces, also use the rows or blobs element. If a compression dictionary for the target table or fragment does not exist, the compress operation also creates the dictionary. |
| create_dictionary | Builds a compression dictionary, which is a
library of frequently occurring patterns and the symbol numbers that
replace them in compressed rows. After a dictionary is created, any newly inserted or updated rows will be compressed if they are compressible. Existing rows are not compressed. |
| estimate_compression | Estimates both a new compression ratio and a current ratio. The current ratio is 0.0 percent if the table is not compressed. |
| parallel | Runs the compress, repack, update_ipa, or uncompress operation in parallel. A thread is started for each fragment of the table or fragment list and the operation is run in parallel across those fragments. |
| purge_dictionary | Deletes an inactive compression dictionary after you uncompress a table or fragment. |
| repack | Consolidates free space by moving data to the
front of the fragment or table. Because the repack operation moves rows while the fragment is online, other queries that access the fragment that are using an isolation level below Repeatable Read might occasionally find the same row twice or miss finding a row. To avoid this possibility, use the Repeatable Read isolation level for concurrent queries; or, instead of using the repack argument, use the repack_offline argument. |
| repack_offline | Consolidates free space by moving data to the front of the table or fragment, while holding an exclusive lock on the table or fragment. This operation prevents all other access to data until the operation is completed. |
| rows | Specifies that you want to compress or uncompress only row data and not simple large objects in dbspaces. |
| shrink | Returns free space at the end of a fragment or table to the dbspace, thus reducing the total size of the fragment or table. |
| uncompress | Deactivates compression for new INSERT and UPDATE
operations, uncompresses all compressed rows, and deactivates the
compression dictionary. This operation also allocates new pages for
a fragment and moves uncompressed rows that no longer fit on their
original pages to the new pages. Because this operation moves rows while the fragment is online, other queries that access the fragment that are using an isolation level below the Repeatable Read isolation level might occasionally find the same row twice or miss finding a row. To avoid this possibility, use the Repeatable Read isolation level for concurrent queries, or instead of using the uncompress argument, use the uncompress_offline argument. This option automatically uncompresses row data and simple large objects in dbspaces. To compress only row data or only simple large objects in dbspaces, also use the rows or blobs element. |
| uncompress_offline | Deactivates compression for new INSERT and UPDATE
operations, uncompresses all compressed rows, and deactivates the
compression dictionary, while holding an exclusive lock on the fragment.
This prevents all other access to the fragment data until the operation
is completed. This operation also allocates new pages for a fragment and moves uncompressed rows that no longer fit on their original pages to the new pages. |
| update_ipa | Removes outstanding in-place alter operations for the specified table or fragments. |
Command elements
| Element | Description | Key Considerations |
|---|---|---|
| database_name | The name of the database that contains the specified table. | Optional. If you do not specify a database, HCL OneDB™ uses the current database. If you enter a database name, you must use the same uppercase or lowercase letters that are in system catalog tables. |
| owner | The authorization identifier of the owner of the database that contains the specified table. | Optional. If you do not specify an owner, HCL OneDB uses the current owner. If you enter an owner name, you must use the same uppercase or lowercase letters that are in system catalog tables. |
| table_name | The name of the table that contains the data. | You must use the same uppercase or lowercase letters that are in system catalog tables. |
| Element | Description | Key Considerations |
|---|---|---|
| partition_number | A space-separated list of partition numbers that belong to the same table. |
Usage
HCL OneDB uses the compression dictionary to compress data.
After you run a compress command on a table or fragment, HCL OneDB automatically compresses any new rows that you add to the table or fragment. If the table or fragment contains more than 2000 rows when you run the compress command, a compression dictionary is built and all the rows are compressed. If the table or fragment contains fewer than 2000 rows when you run the compression command, the table or fragment is enabled for automatic compression. After 2000 rows are inserted, a compression dictionary is created and all rows after the initial 2000 rows are compressed. To compress the initial 2000 rows, run the compress command again.
If your data changes significantly, the compression dictionary might not be effective. In this situation, uncompress and then compress again.
If you do not have enough data to create a compression dictionary, the create dictionary command fails. If the creation of the dictionary fails, run the compress command to create a dictionary and compress when more rows are loaded into the table.
You can cancel a command with a compress or uncompress argument, for example, by typing CTRL-C in DB-Access. You can reissue commands with repack, repack_offline, uncompress, and uncompress_offline arguments after a prior interrupted command.
You cannot perform a compress, repack, repack_offline, shrink, uncompress, or uncompress_offline operation on a table or fragment while any of these operations is already occurring on the table or fragment.
- create_dictionary
- compress
- repack
- shrink
Compress, repack, repack_offline, uncompress, and uncompress_offline operations can consume large amounts of log files. Configure your logs to be larger if any workload that you expect to run, including but not limited to these compression operations, consumes log files faster than one every 30 seconds.
Compress, repack, and uncompress operations are logged, but run in small portions.
If you change the fragmentation strategy for a table after you perform a compression operation, the table loses its compression status and will need to be recompressed.
Dropping or disabling indexes before you complete a repack_offline or uncompress_offline operation can decrease the amount of time that it takes the server to complete the operation. Afterward, you can re-create or re-enable the indexes, preferably taking advantage of PDQ. Dropping or disabling the indexes and then creating or enabling them again can be faster than completing a repack_offline or uncompress_offline operation without doing this.
Do not drop a dbspace that Change Data Capture (CDC) API is using, if the dbspace ever contained compressed tables, because this might delete compression dictionaries that CDC still needs.
Repack
The compress operation normally creates a quantity of free space on individual data and remainder pages, but the space is not consolidated at the end of the table or fragment. Instead, the space can be used to hold newly inserted rows, with the table not growing any larger until this space is filled.
A compress operation, which only occurs online, compresses rows of a table in-place. The repack operation moves the rows. You can perform a repack operation online or offline. An online operation allows concurrent activity to occur on a table. However, this can result in phantom rows. (Phantom rows are rows that are initially modified or inserted during a transaction that is later rolled back.)
To avoid phantom rows, you might want to repack offline, when you can afford to keep other users from accessing a table or fragment. For example, you could perform a compress operation with concurrent activity during the day, and then perform a repack_offline operation at night, when no concurrent activity is expected on the table.
You cannot perform an offline operation with an online operation. For example, while you can perform a combined compress repack operation, you cannot perform a combined compress repack_offline operation. If you want to repack offline, you must do this in two steps:
- Perform a compress operation.
- Perform a repack_offline operation.
Similarly you cannot perform a repack_offline shrink operation.
If light appends (unbuffered, unlogged insert operations) occur in a table or fragment while a repack operation is occurring, the repack operation does not complete the consolidation of space at the end of a table or fragment. The repack operation does not complete because the new extents are added in the location where the repack operation already occurred, so space cannot be returned to the dbspace. To complete the repack process, you must run a second repack operation after light append activity completes. This second repack operation builds on the work of the first repack operation.
Shrink
The shrink operation is typically performed after a repack operation.
You can safely shrink the entire table without compromising the allocation strategy of the table. For example, if you have a fragmented table with one fragment for each day of the week and many fragments pre-allocated for future use, you can shrink the table without compromising this allocation strategy. If the table is empty, HCL OneDB shrinks the table to the initial extent size that was specified when the table was created.
- It shortens all extents except the first extent to as small a size as possible.
- If the table is entirely in the first extent (for example, because the table is an empty table), HCL OneDB does not shrink the first extent to a size that was smaller than the extent size that was specified when the table was created with the CREATE TABLE statement.
You can use the MODIFY EXTENT SIZE clause of the ALTER TABLE statement to reduce the current extent size. After you do this, you can rerun the shrink operation to shrink the first extent to the new extent size.
Uncompress
The uncompress operation has no effect on any table or fragment it is applied to that is not compressed.
After you uncompress a table or fragment, you can perform a purge_dictionary operation to delete the dictionary for that table or fragment.
Purge
Before you perform a purge_dictionary operation for tables and fragments, you must:
- Uncompress the tables and fragments.
When you uncompress a table or fragment, HCL OneDB marks the dictionary for the table or fragment as inactive.
- Be sure that Enterprise Replication functions do not need the compression dictionaries for older logs.
- Archive any dbspace that contains a table or fragment with a compression dictionary, even if you have uncompressed data in the table or fragment and the dictionary is no longer active.
You can also delete all compression dictionaries or all compression dictionaries that were created before and on a specified date. For information, see purge compression dictionary arguments: Remove compression dictionaries (SQL administration API).
Examples
The following command compresses, repacks, and shrinks both row data in a table that is named auto in the insurance database of which tjones is the owner and simple large objects in the dbspace.
EXECUTE FUNCTION task("table compress repack shrink","auto",
"insurance","tjones");The following command compresses only row data in a table named dental in parallel.
EXECUTE FUNCTION task("table compress rows parallel","dental");The following command uncompresses the fragment with the partition number 14680071.
EXECUTE FUNCTION task("fragment uncompress","14680071");The following command uncompresses only row data in the fragment with the partition number 14680071 in parallel.
EXECUTE FUNCTION task("fragment uncompress rows parallel","14680071");EXECUTE FUNCTION task("table estimate_compression","home",
"insurance","fgomez");The following command removes pending in-place alter operations on a table that is named auto in parallel.
EXECUTE FUNCTION task("table update_ipa parallel","auto");After you run the command, the database server displays an estimate of the compression ratio that can be achieved, along with the currently achieved compression ratio (if it exists). For information about the output of the command, see Output of the estimate compression operation (SQL administration API).