RECORDLENGTH - Basic Teradata Query - 16.20

Basic Teradata Query Reference

prodname
Basic Teradata Query
vrm_release
16.20
category
Programming Reference
featnum
B035-2414-108K

Purpose

This control enables users to specify the maximum record (row) length that can be processed within the current session for imports, exports and stdout hex-dump output.

Syntax



where the following is true:

MAX64
Limits record sizes to 64,768 bytes. For RECORD/INDICATOR mode (including DATA/INDICDATA exports and imports), 2-byte record lengths will be used.
MAX1MB
Limits record sizes to 1,024,000 bytes. For RECORD/INDICATOR mode (including DATA/INDICDATA exports and imports), 4-byte record lengths will be used.
DEFAULTS
Resets the RECORDLENGTH command settings back to their original default values:
  • Import/Export: MAX64
  • Stdout: MAX1MB

Usage Notes

The command affects imports, exports, and responses printed to stdout. Therefore, BTEQ defines two settings for the RECORDLENGTH command. One is specific to imports and exports, which has a default value of MAX64. The other is specific to stdout, which has a default value of MAX1MB. Use the SHOW CONTROLS RECORDLENGTH command to display the current RECORDLENGTH command settings.

Maximum record sizes may vary depending upon the SQL statement being used and the format of the request/response.

Rules
  • A DATA/INDICDATA export file must contain record lengths of the same size. It must not contain 2-byte record lengths (MAX64) mixed with 4-byte record lengths (MAX1MB) .
  • When importing records, the current RECORDLENGTH setting for the import must match that of the data file.
  • If connected to an older database, which does not provide 1MB Row Support, then the MAX1MB value is not allowed.
  • Once an import or export is active, the RECORDLENGTH command cannot be used. IMPORT RESET or EXPORT RESET must first be issued to terminate the import or export, respectively.
  • The SET DEFAULTS command can also be used to reset the RECORDLENGTH command settings to their default values, but only if an import or export is not active.
  • The RECORDLENGTH command setting for imports and exports can be temporarily overridden by using the RECORDLENGTH option from the IMPORT and EXPORT commands.

Expected Behavior

The following table shows how BTEQ behaves, covering all combinations of the RECORDLENGTH command and the RECORDLENGTH option for the IMPORT and EXPORT commands.

RECORDLENGTH Command Setting Import/Export RECORDLENGTH Option Stdout Behavior Import/Export Behavior
MAX64 Not used 64KB 64KB
MAX64 MAX64 64KB 64KB
MAX64 MAX1MB 64KB 1MB
MAX1MB Not used 1MB 1MB
MAX1MB MAX64 1MB 64KB
MAX1MB MAX1MB 1MB 1MB
DEFAULTS Not used 1MB 64KB
DEFAULTS MAX64 1MB 64KB
DEFAULTS MAX1MB 1MB 1MB

Example 1 – RECORDLENGTH

Eight-digit offsets are employed for stdout hex-dump output when RECORDLENGTH=MAX1MB:

.RECORDLENGTH=MAX1MB
.RECORDMODE ON
SELECT 'abcdefghijklmnopqrstuvwxyz';

*** Record#1. Dump Of Data:
 00000000  1A00 6162 6364 6566  6768 696A 6B6C 6D6E  *..abcdefghijklmn*
 00000010  6F70 7172 7374 7576  7778 797A            *opqrstuvwxyz*

Example 2 – RECORDLENGTH

Although the RECORDLENGTH command is used, individual imports and exports can override the RECORDLENGTH command settings. Below, the first export allows 1MB rows, but the second export only allows 64KB rows.

.SET RECORDLENGTH=MAX1MB
.SHOW CONTROLS RECORDLENGTH

/* Large rows allowed */
.EXPORT DATA FILE=datafile1
SELECT * FROM large_table;
.EXPORT RESET

/* Large rows not allowed */
.EXPORT DATA RECORDLENGTH=MAX64 FILE=datafile2
SELECT * FROM small_table;
.EXPORT RESET

Example 3 – RECORDLENGTH

When exporting and importing with the same data file, the format type and RECORDLENGTH values must match.

/* Export large rows with INDICDATA format */
.SET RECORDLENGTH=MAX1MB
.EXPORT INDICDATA FILE=onefile
SELECT * FROM large_table1;
.EXPORT RESET

/* Import the same rows into a different table */
.IMPORT INDICDATA RECORDLENGTH=MAX1MB FILE=onefile
.REPEAT *
USING (col1 CHAR(30000), col2 CHAR(30000),
       col3 CHAR(30000), col4 CHAR(30000),
       col5 CHAR(30000), col6 CHAR(30000))
INSERT INTO large_table2 values (:col1, :col2, :col3,
                                 :col4, :col5, :col6);
.IMPORT RESET