UNCOMPRESS - Advanced SQL Engine - Teradata Database

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

Syntax

UNCOMPRESS [ /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 uncompressed.

The table to be uncompressed 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 uncompress.

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 uncompress any data:

ESTIMATE

E

Estimates data block (DB) sizes that would exist after an uncompress operation is performed, and estimates the CPU usage (time per DB) that would be required. Does not uncompress any data.

Multiply the Table Size Factor shown in the output by the current table size to determine the estimated table size after an uncompress 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.

Usage Notes

The UNCOMPRESS command is part of the block-level compression (BLC) feature of Teradata.

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.

BLC can be enabled or disabled system-wide, or for various categories of tables. For Permanent data, these include subtables for primary, fallback, eligible LOB data, and join and hash indexes. Secondary indexes on primary rows and fallback copies of secondary indexes are never compressed.

Tables that have their BLOCKCOMPRESSION value set to ALWAYS in the CREATE TABLE or ALTER TABLE statement cannot be uncompressed using the UNCOMPRESS command.

The UNCOMPRESS command consumes storage space as it uncompresses data. To ensure sufficient space is available after an uncompress command completes, use the UncompressReservedSpace setting in DBS Control to specify the amount of space to reserve. The decompression operation stops when the available free space falls below the reserved amount. The table undergoing decompression at the time may remain in a partially compressed state. This will not affect the functioning of the table or database. To resume decompression, free up some storage space, or lower the value of UncompressReservedSpace then reissue the UNCOMPRESS command.

Ferret logs the beginning and end of UNCOMPRESS operations. If the database is reset during the operation, the decompression operation continues as part of the recovery process after the database restarts. However, decompression will continue and complete only on the table that was in the process of being decompressed 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 UNCOMPRESS 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.

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

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

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