16.20 - PACK - Basic Teradata Query

Basic Teradata Query Reference

Basic Teradata Query
October 2018
Programming Reference


This control enables users to specify the maximum number of import data file records that can be sent within an SQL request's USING data buffer.


where the following is true:

Integer in the range of 0 to 999900.

0 is the default value. Zero means that use of a PACK factor has not been specified, and PACK statistics are not generated for the import.

Entering an invalid n value prompts the following error message:

*** Error: Pack factor value must be in the 0..999900 range.

Defines the maximum request buffer size (in bytes) that can be used for an import. The request buffer stores the SQL statement, plus packed records (USING data), plus metadata. The larger the value, the more records can be packed. However, a larger request buffer could affect performance. Thorough testing should be performed before using this option in a production environment.
The REQBUFLEN option takes one numeric argument (b) in the range of 1000000 to 7340032. The default value is 1000000. If the PACK value is greater than 1, the REQBUFLEN option is not defined, and an import's RECORDLENGTH option value is set to MAX1MB, then BTEQ will automatically set REQBUFLEN to 7340032, in order to handle the larger records.
This option can only be used after logging on to a database and will automatically reset upon logoff. It can be overridden by the REQBUFLEN option of the REPEAT command.

Usage Notes

The pack factor is reducible to prevent a USING data buffer overflow. BTEQ packs as many records into the request buffers as will fit. To prevent CLI buffer overflow errors, BTEQ considers both the upper limit, indicated by the pack factor, and CLI's maximum message size.

BTEQ enforces the database's limit for maximum packed records allowed in a iterated request. If the supplied pack factor exceeds the database maximum, BTEQ will display a warning message and will reset the pack factor to the allowed limit.

When a pack factor is used, statistics are created for the average and largest factor used as part of the summary messages for the request's result. If the REQBUFLEN option is defined, an extra statistical line will be added for the requested value and the actual bytes allocated for the request buffer.

A pack factor is established by using the PACK command or by using a PACK clause for the REPEAT command. See REPEAT.

Large pack factors should be used judiciously. A significant amount of memory is allocated for the associated underlying data pointers, which might require advance planning, particularly for z/OS BTEQ use. Also consider the underlying multi-data-parcel protocol limits imposed by the DBS as well as CLI, which can further constrain the usability of a large pack factor. The PACK factor setting is ignored if the database being used does not support iterated requests.