Multivalue Compression of UDT Columns | VantageCloud Lake - Using Compression Methods with CREATE FUNCTION - Teradata Vantage

CREATE FUNCTION and Multivalue Compression of UDT Columns

CREATE FUNCTION and Algorithmic Compression of UDT Columns

You must specify either FOR COMPRESS or FOR DECOMPRESS, respectively, when you create an SQL function definition for an external UDF that is to be used to either compress or decompress BYTE, VARBYTE, CHARACTER, VARCHAR, GRAPHIC, Period, distinct UDT (including ARRAY/VARRAY), BLOB, CLOB, XML, Geospatial, distinct BLOB-based UDT, distinct CLOB-based UDT, or distinct XML-based UDT column values algorithmically. If you do not specify these options in the function definition, it cannot be used to algorithmically compress or decompress column values.

You can use algorithmic compression on distinct and structured UDT types.

The compression and decompression routines you code can be either external scalar UDFs or embedded services scalar UDFs.

Embedded services UDFs that are used for algorithmic compression are for Internal UDT types such as Period, ARRAY, VARRAY, and Geospatial. They cannot be used for structured UDTs.

External UDFs are typically used for distinct UDTs.

Accessing or writing an algorithmically-compressed column implicitly encapsulates the column reference with the appropriate routine.

See Compressing Column Values Using Only Multivalue Compression for additional information about the TD_LZ_COMPRESS and TD_LZ_DECOMPRESS UDFs.

REPLACE FUNCTION and Algorithmic Compression

You can only submit a REPLACE FUNCTION request if the compression-related function is not used by any column across all databases and users for compressing and decompressing its data. This restriction is necessary because column data is already compressed using a certain algorithm and cannot be decompressed if the algorithm is changed.

The only exception to this rule is that a REPLACE FUNCTION request involving algorithmic compression is valid if it does not change the existing function specification in the data dictionary and its execution mode is PROTECTED.

For example, consider the following DDL requests where the objects are created in the order indicated.

CREATE FUNCTION scsu_comp …;
CREATE FUNCTION scsu_decomp …;
CREATE TABLE t1 (
  col_1 INTEGER,
  col_2 CHARACTER(10) COMPRESS ('abc', 'efg')
                      COMPRESS USING scsu_comp
                      DECOMPRESS USING scsu_decomp);

Column col_2 in table t1 references the UDFs scsu_comp for algorithmic compression and scsu_decomp for algorithmic decompression.

After table t1 has been created, a REPLACE FUNCTION request on either scsu_comp scsu_decomp returns an error to the requestor. This assumes that the exception conditions noted earlier are not satisfied.