15.10 - Example: Using the BLOCKCOMPRESSION Reserved Query Band - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

These examples show how to use the BLOCKCOMPRESSION reserved query band to set the block-level compression characteristics for various tables, subtables, or both for a session or transaction.

Setting the BlockCompression query band value to either all or yes tells Teradata Database to compress all new subtables loaded for the session or transaction.

These examples set the query band for the current session.

The following requests have the identical effect.

     SET QUERY_BAND = 'BLOCKCOMPRESSION=YES;' FOR SESSION;
     SET QUERY_BAND = 'BLOCKCOMPRESSION=ALL;' FOR SESSION;

Setting the BlockCompression query band value to fallback tells Teradata Database to compress all new fallback subtables, including the fallback copy of LOB subtables, loaded for the session or transaction.

This example sets the query band for the current session.

     SET QUERY_BAND = 'BLOCKCOMPRESSION=FALLBACK;' FOR SESSION;

Setting the BlockCompression query band value to either no or none tells Teradata Database not to compress any new subtables loaded for the session or transaction.

These examples set the query band for the current session.

The following requests have the identical effect.

     SET QUERY_BAND = 'BLOCKCOMPRESSION=NO;' FOR SESSION;
     SET QUERY_BAND = 'BLOCKCOMPRESSION=NONE;' FOR SESSION;

Setting the BlockCompression query band value to ONLYCLOBS tells Teradata Database to compress all new primary and fallback CLOB subtables for the session or transaction. Data loaded into all other new subtables during the session or transaction is not compressed.

This example sets the query band for the current transaction.

     BEGIN TRANSACTION
       SET QUERY_BAND = 'BLOCKCOMPRESSION=ONLYCLOBS;' FOR TRANSACTION;
           …
     END TRANSACTION

The ellipsis represents the SQL request set that causes the data load to occur.

Setting the BLOCKCOMPRESSION query band value to WITHOUTCLOBS tells Teradata Database to compress all new primary and fallback subtables loaded for the session or transaction except for CLOB subtables, whose data is not compressed.

This example sets the query band for the current session.

     SET QUERY_BAND = 'BLOCKCOMPRESSION=WITHOUTCLOBS;' FOR SESSION;