15.00 - LZCOMP_L - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

LZCOMP_L

Purpose  

Compresses the specified Latin character data using the Lempel-Ziv algorithm.

Syntax  

where:

 

Syntax element…

Specifies…

TD_SYSFNLIB

the name of the database where the function is located.

Latin_string

a Latin character string or string expression.

Note: This function takes no arguments when used as part of the COMPRESS USING or DECOMPRESS USING phrases. For more information about the COMPRESS/DECOMPRESS phrase, see SQL Data Types and Literals.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Argument Type and Rules

Expressions passed to this function must have a data type of VARCHAR(n) CHARACTER SET LATIN, where the maximum supported size (n) is 64000. You can also pass arguments with data types that can be converted to VARCHAR(64000) CHARACTER SET LATIN using the implicit data type conversion rules that apply to UDFs. For example, LZCOMP_L(CHAR) is allowed because it can be implicitly converted to LZCOMP_L(VARCHAR).

Note: The UDF implicit type conversion rules are more restrictive than the implicit type conversion rules normally used by Teradata Database. If an argument cannot be converted to VARCHAR following the UDF implicit conversion rules, it must be explicitly cast.

For details, see “Compatible Types” in SQL External Routine Programming.

The input to this function must be Latin character data.

If you specify NULL as input, the function returns NULL.

Result Type

The result data type is VARBYTE(64000).

Usage Notes

Uncompressed character data in Teradata Database requires one byte per character when storing Latin character data. LZCOMP_L takes Latin character input, compresses it using the Lempel-Ziv algorithm, and returns the compressed result.

See http://zlib.net for information about the compression algorithm used by LZCOMP_L.

LZCOMP_L provides good compression results for long Latin character strings, but might not be as effective for short strings. It can also provide good results for medium strings that have many repeating characters.

For a detailed comparison between the Teradata-supplied compression functions and guidelines for choosing a compression function, see Database Administration.

Although you can call the function directly, LZCOMP_L is normally used with algorithmic compression (ALC) to compress table columns. If LZCOMP_L is used with ALC, nulls are also compressed if those columns are nullable.

For more information about ALC, see “COMPRESS and DECOMPRESS Phrases” in SQL Data Types and Literals.

Uncompressing Data Compressed with LZCOMP_L

To uncompress Latin data that was compressed using LZCOMP_L, use the LZDECOMP_L function. See “LZDECOMP_L” on page 542.

Example

In this example, the Latin values in the Description column are compressed using the LZCOMP_L function with ALC. The LZDECOMP_L function uncompresses the previously compressed values.

   CREATE MULTISET TABLE Pendants
      (ItemNo INTEGER,
       Gem CHAR(10) UPPERCASE CHARACTER SET LATIN,
       Description VARCHAR(1000) CHARACTER SET LATIN
          COMPRESS USING TD_SYSFNLIB.LZCOMP_L
          DECOMPRESS USING TD_SYSFNLIB.LZDECOMP_L);

Example

Given the following table definition:

   CREATE TABLE Pendants
      (ItemNo INTEGER,
       Description VARCHAR(100) CHARACTER SET LATIN);

The following query returns the compressed values of the Description column.

   SELECT TD_SYSFNLIB.LZCOMP_L(Pendants.Description);