SET LOAD Command

2.10.1.1 SET LOAD Command

SET LOAD enables you to set options for loading data.

Syntax

SET LOAD default | [options...]

where

default means load method properties return to default values.

options represents the following:

```
BATCH_ROWS|BATCHROWS <number_of_rows>
```
Data loading is done in batches. Specifies the number of rows to include in each batch.
```
BATCHES_PER_COMMIT|BATCHESPERCOMMIT <batches_per_commit>
```
Commit after processing number_of_batches. If the number is equal to 0, commit happens at the end of the load. If the number is greater than or equal to 0, COMMIT ON is set.
```
CLEAN_NAMES [ TRANSFORM | TRANSFORM128 | QUOTE | QUOTE128 | UNIQUE ]
```
Identifies the rule for making table and column names compliant with database identifiers. Names are cleaned before they are mapped to provide consistency with previous releases. If both CLEAN_NAMES and MAP_COLUMN_NAMES are used, then clean names should be specified.

The standard identifiers are:
- No longer than 30 or 128 characters.
- Not a reserved word.
- Starts with a letter and contains only letters, digits, or one of _$#.
- Uppercase
- Names that do not comply must be quoted. Length rules always apply.
Note:
Data that is enclosed in quotes will have quotes in the header row removed before names are cleaned.

TRANSFORM (default)

Indicates that names are transformed as follows:
- Names are in uppercase.
- If the name starts and ends with the quote character, the quotes are removed.
- Names that are reserved words are appended with a dollar sign ($).
- Names that start with a number or special character is prefixed with an X.
- Spaces and hyphens are replaced with underscores (_). $ and # characters are retained.
- Special characters other than $ and # is replaced with the number sign (#).
- Names are truncated to 30 or 128 characters depending on database MAX_STRING_SIZE.
- After names are cleaned, non-unique names within the column set are appended with a unique sequence number. If truncation is required, the sequence number is maintained.
TRANSFORM (default)

Applies all transform rules. Names may be 128 characters.

QUOTE

Quote non-compliant names and shorten to 30 or 128 characters depending on database MAX_STRING_SIZE.

QUOTE128

Quote non-compliant names. Names may be 128 characters.

UNIQUE

Compatibility option with previous releases of load service. Names that are not unique within the column set are appended with a unique sequential number. Truncation is not provided.
```
COLUMN_SIZE|COLUMNSIZE|COLSIZE {ACTUAL|ROUND|ROUNDED|MAX|MAXIMUM}
```
Create table column size strategy.

ACTUAL uses the largest size found during the scan.

ROUND|ROUNDED uses a size a little larger than the largest size found during the scan.

MAX|MAXIMUM uses the database maximum size for the data type that was detected.
```
COMMIT {ON|OFF}
```
Enable or disable data commits.
```
DATE|DATE_FORMAT|DATEFORMAT format_mask 
```
The format of all DATE data type columns loaded. Specify no format_mask or DEFAULT to use database default.

For DATE columns, if format is not set and SCAN_ROWS = 0, the data is not scanned for a valid mask.

ERRORS {number_of_rows | UNLIMITED}|-1: Indicates the number of error rows allowed.

If this number is exceeded, the load will be terminated.

-1 and UNLIMITED indicate no error limit.

All rows in a batch may be in error if any row fails.
```
LOCALE { <language country> | DEFAULT | "" }
```
Specify locale language and optionally country.

DEFAULT|"" : Set to default locale.
```
MAP_COLUMN_NAMES|MAPCOLUMNNAMES|MAPNAMES { OFF| (<file-col-name>=<table-col-name>,...) }
```
Provide a mapping from the column names specified in the file to column names in the table.
```
METHOD INSERT
```
Method to use for data loads.
```
SCAN_ROWS|SCANROWS|SCAN <1-5000> 
```
Identify the number of rows to scan for create table generation. Default is 100 rows.
```
TIMESTAMP|TIMESTAMP_FORMAT|TIMESTAMPFORMAT
```
The format of all TIMESTAMP data type columns being loaded. Specify no format_mask or DEFAULT to use database default. For TIMESTAMP columns, if format is not set and SCAN_ROWS not equal to 0, the data is scanned for a valid mask.
```
TIMESTAMPTZ|TIMESTAMPTZ_FORMAT|TIMESTAMPTZFORMAT
```
The format of all TIMESTAMPTZ data type columns being loaded. Specify no format_mask or DEFAULT to use database default. For TIMESTAMPTZ columns, if format is not set and SCAN_ROWS not equal to 0, the data is scanned for a valid mask.
```
TRUNCATE {OFF|ON}
```
Truncate ON truncates the table before loading
```
UNKNOWN_COLUMNS_FAIL|UNKNOWNCOLUMNSFAIL|UNKNOWNFAIL {ON|OFF}
```
ON: Terminates the load if any columns in the file do not map to a column in the table.

OFF: Allows the load to proceed when columns in the file do not map to a column in the table.