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