PACK Command | Basic Teradata Query - PACK (P) - Basic Teradata Query

Basic Teradata® Query Reference - 20.00
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
Lake
VMware
Product
Basic Teradata Query
Release Number
20.00
Published
October 2023
Language
English (United States)
Last Update
2024-06-14
dita:mapPath
gxl1691484661681.ditamap
dita:ditavalPath
obe1474387269547.ditaval
dita:id
psp1479308573013
Product Category
Teradata Tools and Utilities

Purpose

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.

Syntax



Shorthand Syntax (Interactive Mode Only)
Command Shorthand
PACK P
Option Shorthand
REQBUFLEN RBL

where the following is true:

n
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.
REQBUFLEN
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. For more information, see REPEAT (RP).

Large pack factors needs to 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 database 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.

When the PACK command is used without specifying any arguments, BTEQ sets PACK to its initial default.

The DEFAULTS command does not affect PACK setting.

The PACK command is valid in an SQL macro.