CREATE FUNCTION and Algorithmic Compression of UDT Columns - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

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 normally 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.

See the following topics for rules that apply to creating UDFs to implement algorithmic compression and decompression of UDT data.

Compression UDF

The compression UDF must have the following signature.
  • There must be a single input parameter that can be any of the supported UDT data types.
  • The parameter data type of the compression function must match the data type of the UDT column exactly.
  • The UDT parameter data type of the compression function must match the return data type of the decompression function exactly.
  • The return data type must be VARBYTE(n).
  • The compression function VARBYTE(n) return length n must match the length of the decompression function VARBYTE(n) parameter exactly.

Decompression UDF

A decompression UDF must have the following signature:
  • Single input parameter whose data type must be VARBYTE(n).
  • The return data type of the decompression function must match the data type of the UDT column exactly.
  • The return data type of the decompression function must match the UDT parameter data type of the compression function exactly.
  • The decompression function VARBYTE(n) return length n must match the length of the compression function VARBYTE(n) parameter exactly.
  • The output of the decompress UDF must be one of the supported UDT data types.