17.00 - Example: SHOW TABLE for Algorithmic Compression - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

Assume that you have defined algorithmic compression on a set of table columns. The SHOW TABLE output for such a table returns the names of the UDFs that contain the compression and decompression algorithms.

Assume table t1 is created with the following definition.

     CREATE TABLE t1 (
       col_1 INTEGER, 
       col_2 CHARACTER(10) COMPRESS ALGCOMPRESS scsu_comp
                           ALGDECOMPRESS scsu_decomp ('abc', 'efg'));

You then submit a SHOW TABLE request on t1. Vantage returns the following SQL create text for this request.

CREATE SET TABLE  user_name.t1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
col_1 INTEGER,
col_2 CHARACTER(10) 
CHARACTER SET LATIN NOT CASESPECIFIC   
COMPRESS
ALGCOMPRESS scsu_comp
ALGDECOMPRESS scsu_decomp ('abc','efg'))
PRIMARY INDEX ( col_1 );

Assume you create table t2 with the following definition.

     CREATE TABLE t2 (
       col_1 INTEGER, 
       col_2 CHARACTER(10));

You then submit the following ALTER TABLE request on t2 to modify its definition to include algorithmic compression on col_2.

     ALTER TABLE t2
     ADD col_2 CHARACTER(10) COMPRESS ALGCOMPRESS scsu_comp 
                             ALGDECOMPRESS scsu_decomp ('abc', 'efg');

A SHOW TABLE request for table t2 returns the following SQL create text.

     CREATE SET TABLE  user_name.t2, NO FALLBACK,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
          col_1 INTEGER,
          col_2 CHARACTER(10) 
             CHARACTER SET LATIN NOT CASESPECIFIC   
             COMPRESS
             ALGCOMPRESS scsu_comp
             ALGDECOMPRESS scsu_decomp ('abc       ','efg       '))
          PRIMARY INDEX ( col_1 );