17.10 - COMPRESS - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Configuration
Publication ID
B035-1102-171K
Language
English (United States)

Compresses specified types of data in specified databases and tables, or shows an estimate of what results would be if tables were compressed. If data of one of the specified types is subsequently added to the specified table or database, it will also be compressed.

Compression for a table uses the algorithm and level specified by the BLOCKCOMPRESSIONALGORITHM and BLOCKCOMPRESSIONLEVEL options that were specified in the CREATE TABLE or ALTER TABLE statement. If either of these options has not been specified for the table, or if the value was specified as DEFAULT, the data is compressed using the algorithm and level specified by the CompressionAlgorithm and CompressionLevel settings in DBS Control.
BLOCKCOMPRESSIONLEVEL is effective only for the ZLIB compression algorithm.

Compression uses the algorithm specified by the CompressionAlgorithm setting in DBS Control.

Syntax

COMPRESS [ /Y ] table_unique [
  PRIMARY |
  FALLBACK |
  FALLBACKANDCLOBS |
  { WITHOUT | ONLY } CLOBS
] 
[ ESTIMATE | E ]

Syntax Elements

/Y
Prevents Ferret from displaying a confirmation message.
table_unique
Unique identifier of the table for which data blocks will be compressed.
The table to be compressed can be uniquely identified in either of two ways:
  • Specify the name of the database to which the table belongs and the table name, separated by a period. The names individually or together must be delimited. The following formats are valid:
    • "database_name.table_name"
    • 'database_name.table_name'
    • "database_name"."table_name"
    • 'database_name'.'table_name'
    • "database_name.*"
    • 'database_name.*'
    • "database_name"."*"
    • 'database_name'.'*'

    The asterisk denotes all tables in the given database.

  • Specify the unique numeric identifier of the table, which consists of two numeric values separated by a space. The table identifier consists of the first two numeric values returned by the TABLEID command. These two numbers are common to all subtables that comprise the table.
    The format of the input numbers depends on the current radix setting, which is displayed by the RADIX command.

The following options specify the type of data to compress:

PRIMARY
All primary base table data and primary LOB data that is eligible for compression within the specified database or table.
FALLBACK
All fallback base table data and fallback LOB data that is eligible for compression within the specified database or table.
FALLBACKANDCLOBS
All fallback base table data, fallback LOB data that is eligible for compression, and primary LOB data eligible for compression within the specified database or table.
WITHOUT CLOBS
All data except LOB data that is eligible for compression within the specified database or table.
ONLY CLOBS
Only the primary and fallback LOB data that is eligible for compression within the specified table. Other table DBs are not affected.

The following option does not compress any data:

ESTIMATE
E
Estimates data block (DB) sizes that would exist after a compress operation is performed, and estimates the CPU usage (time per DB) that would be required. Does not compress any data.
Multiply the Table Size Factor shown in the output by the current table size to determine the estimated table size after a compress operation.
Estimates are most accurate when the table DB size is at or near the size defined by DATABLOCKSIZE (optionally specified when the table was created or altered), or defined by the PermDBSize setting in DBS Control if no DATABLOCKSIZE was specified for the table.

The Ferret INQUIRE (or INQ) and ABORT commands can be used to check on the progress or halt this operation during command execution.

The command progress percentage reported by the INQUIRE command can change unexpectedly if the table being compressed is being modified concurrently by another process.

Usage Notes

The COMPRESS command is part of the block-level Compression (BLC) feature of Vantage.

BLC enables data compression at the data block (DB) level of the Teradata file system. Compression reduces the amount of storage required for a given amount of data. The BlockLevelCompression field of DBS Control enables and disables BLC.

Tables that have their BLOCKCOMPRESSION value set to NEVER in the CREATE TABLE or ALTER TABLE statement cannot be compressed using the COMPRESS command.

BLC is subject to the compression-related DBS Control settings. Some data blocks in a compressed table may not be compressed for the following reasons:
  • The block consists of fewer sectors than the number specified by MinDBSectsToCompress.
  • The block size after compression would not be reduced by the percentage specified by MinPercentCompReduction.
  • The DBS Control compression setting for the specified table type is set to NEVER compress.

After a data block has been compressed using a particular algorithm, the data retains that compression, even if the algorithm is subsequently changed. New data added to the table is compressed using the new algorithm. If previously compressed data is updated, which requires uncompressing and recompressing the data, the currently set algorithm is used for the recompression.

Consequently, a single subtable can exhibit a mixture of uncompressed data blocks and data blocks compressed using different compression algorithms, depending on the settings at the time the data was added to the table. To make the data consistent with respect to compression, use the Ferret COMPRESS command to compress all the data using the currently set algorithm, or use the Ferret UNCOMPRESS command to uncompress all the table data.

The PermDBSize and JournalDBSize field settings in DBS Control apply to the uncompressed size of these types of DBs.

Ferret logs the beginning and end of COMPRESS operations. If the database is reset during the operation, the compression operation continues as part of the recovery process after the database restarts. However, compression will continue and complete only on the table that was in the process of being compressed at the time of the reset. The rest of the database will remain in the state it was prior to the reset.

Only one instance of the COMPRESS command is allowed to run at any time on the system.

If you receive an error when you issue the COMPRESS or UNCOMPRESS command, and you are certain there is no other instance of either command currently running, use the RESETBLCTSKCNT command to reset the count of running COMPRESS and UNCOMPRESS tasks to zero.

Related Information

For more information on… See…
DBS Control compression settings DBS Control (dbscontrol).
Query Banding Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184.
RADIX command RADIX.
RESETBLCTSKCNT command RESETBLCTSKCNT.
TABLEID command TABLEID.
UNCOMPRESS command UNCOMPRESS.