The primary and fallback subtables of a table can have independent block-level compression attributes.
You can use the BlockCompression query band with tables whose effective BLOCKCOMPRESSION attribute is MANUAL. In that case, the query band indicates if the data being loaded into an empty primary or fallback table (or both) should be compressed at the data block level.
The specifications apply to permanent or global temporary table data, but do not apply to Spool data or Permanent Journal data.
The valid BlockCompression values apply, as noted, to primary data tables, fallback data tables, and work tables.
BlockCompression indicates if the data being loaded into an empty primary or fallback table (or both) should be compressed at the data block level. See Adding, Modifying, or Dropping Block-Level Compression.
| Value | Description |
|---|---|
| ALL | Compress all new primary and fallback table data at the data block level. This option is equivalent to specifying YES. |
| FALLBACK | Compress new fallback data, but do not compress primary table data. |
| FALLBACKANDCLOBS | Compress fallback data, including fallback and primary LOBs that are eligible for compression. |
|
These options are equivalent and specify not to compress new primary and fallback table data at the data block level. |
| ONLYCLOBS | Compress only new LOB data that is eligible for compression. |
| WITHOUTCLOBS | Compress all data, except for LOB data. |
| YES | Compress all new primary and fallback table data at the data block level. This option is equivalent to specifying ALL. |
You can also control block-level compression using the FERRET utility and the compression fields of the DBS Control record. For more information about these DBS Control parameters, see Teradata Vantage™ - Database Utilities, B035-1102.
- CompressPermPrimaryDBs
- CompressPermFallbackDBs
- CompressPermPrimaryCLOBDBs
- CompressPermFallbackCLOBDBs
- CompressGlobalTempPrimaryDBs
- CompressGlobalTempFallbackDBs
- CompressGlobalTempPrimaryCLOBDBs
- CompressGlobalTempFallbackCLOBDBs
If you use both, the BlockCompression query band setting overrides the DBS Control setting for any affected subtables unless the DBS Control setting is NEVER.
The following table summarizes the effects on various data of the BlockCompression query band settings.
| Value | Primary Table | Fallback Table | Primary Subtable Compressible LOBs | Fallback Subtable Compressible LOBs |
|---|---|---|---|---|
| None | Default | Default | Default | Default |
| ALL | Compressed | Compressed | Compressed | Compressed |
| FALLBACK | Not compressed | Compressed | Not compressed | Compressed |
|
Not compressed | Not compressed | Not compressed | Not compressed |
| ONLYCLOBS | Not compressed | Not compressed | Compressed | Compressed |
| WITHOUTCLOBS | Compressed | Compressed | Not compressed | Not compressed |
| YES | Compressed | Compressed | Compressed | Compressed |