Loader switches

The following lists the loader switches useful for loading data into global or central tables.

Note the following conventions for using switches

  • Switches must be separated by spaces.
  • Switches may optionally be prefixed by a hyphen. For example “X” or “-X” may be used.
  • An argument value for a switch must appear immediately adjacent to the switch character.
  • Switches can appear in any order.
  • Any switch can appear multiple times.
  • Where two switches conflict or override, the later occurring switch will take precedence.

 

Switch Usage Examples
b

Force a rollback at the end of the load either unconditionally of if the number of rejected records exceeds a given threshold. The threshold is in decimal and must be 0 or greater. A zero threshold forces a rollback if any records are rejected. If no threshold is specified an unconditional rollback is forced at the end of the load.

Note that this switch will disable the commit interval switch (C) and the no-rollback switch (B).

Without this switch a rollback will occur if any record is rejected.

b

b0

b10
 B

Do not rollback at the end of the load, regardless of the number of rejects.

Note that this switch will disable the rollback switch (b).

Without this switch a rollback will occur if any record is rejected.

 B
c

Define the columns to be loaded. Columns must be listed with comma separators. Quotes are optional.

Without this switch all columns are assumed in the order they appear in the table.

cID,NAME,ADDRESS

c’ID,NAME,ADDRESS’

c”id,name,address”
 C

Defers a commit until the end of the file or sets an explicit commit interval. If used without a number, no commits will be issued until the whole file has been loaded. If a number is specified records will be committed at regular intervals of no less than that number of records.

Note that this switch will disable the rollback switch (b) and will implicitly set the no-rollback switch (B). Specifying a deferred commit or a commit interval will implicitly disable load rate governing for query optimisation.

Without this switch commits will occur in batchs of about 64MB of data.

C
C1000
d

Define the field delimiter character. Use an explicit character or a 2 digit hexadecimal character code. Quotes around the character are optional.

Without this switch a comma delimiter is assumed.

d,

d’ ’

dF5
D

Define the line/record delimiter. Use an explicit character or a 2 digit hexadecimal character code. Quotes around the character are optional.

Without this switch a new line (LF) delimiter is assumed.

D|

D’&’

D”|”

D0D

e

Define the character used to escape quotation marks within data. The escape character must be printable and is explicitly specified. Quotes around the character are optional.

Without this switch a quotation mark can be escaped by repeating it.

e\

e’\’

e"\"

 f

Filter records for loading by field content. The switch fN means load the record if the specified field N is not empty and the switch fN:V means load the record if the field contains a given value V; otherwise skip the record. Field numbers are in decimal and start at 1 for the left most field. The conditional value V is optional and quotes around the value are optional.

Repeat for each filter condition required.

Without this switch, no records are filtered by content.

f8

f8:021

f8:’021’

f12:”A”

f13 f8:021
F

Filter records for loading by field size constraint. The switch FN:S means load the record if the specified field N is a specific size S (=S) or no greater than size S (<S) or no less than size S (>S) and skip the record if not loaded. Field numbers are in decimal and start at 1 for the left most field. Field sizes are in characters and must be greater than 0.

Repeat for each filter condition required. Note that the switch may need to be enclosed in quote characters to avoid special characters being recognised by the operating system.

Without this switch, no records are filtered by size.

F8:=2

F9:<16

F10:>8

‘F9:<16’

‘F10:>8’
g

Strips non-digit characters from the specified field. The field number N starts at 1 for the left most field. Repeat for each field to have non-digit characters stripped.

Without this switch, a field is not stripped.

g8

g19 g7

h

Use host name or address S for database connections. This is only used where the host name does not explicitly appear within a database connection string. The host name may optionally appear within quotes.

Without this switch, the local host is assumed.

hBatman

h’Batman’

h”Batman”

h192.10.0.11

H

The specified field has fixed length. The switch HN:L means that field N has a fixed length L. The field number N starts at 1 for the left most field. The length L must be greater than 0.

Repeat for each field with a fixed length.

H8:1

H19:2 H20:8

i

Ignore one or more fields. The switch iN means ignore single field N and iN:M means ignore all fields between M and N (inclusive). Field numbers start at 1 for the left most field.

Repeat for each field or set of fields to be ignored.

Without this switch no fields are ignored.

i8

i19 i7

i8:9

I

Ignore records. The switch IN means ignore records that do not contain more than a N fields. The switch I'C' means ignore records that lead with C as the first non-whitespace character. N must be 1 or more. C must be a non-digit printable single character. Note that the character may need to be enclosed in quote marks to avoid special characters being recognised by the operating system.

Without this switch, no records are ignored.

I1

I19

I#

J

Adjoin a string to each loaded record. The string is treated as one or more virtual fields separated by the delimiter in use for loading. The symbols %N%, %O% and %S% can be used to represent the source record number, offset and size respectively.

The adjoin string must not exceed 64 characters.

Without this switch, no string is adjoined.

J’file.dat’

J’file.dat,%N%’

J”’file.dat’|%O%|%S%”

k

Check field content for record rejection. The switch kN means check field N for being non empty and the switch kN:V means check field N contains a specified value V.  If the check fails the record is rejected. Field number N is in decimal and starts at 1 for the left most field. Quotes around the value are optional.

Repeat for each check condition required.

Without this switch, the field content is not checked.

k8

k8:021

k8:’021’

k12:”A”

k13 k8:021

K

Check records for rejection by field size constraint. The switch KN:S means accept the record if the specified field N is a specific size S (=S) or no greater than size S (<S) or no less than size S (>S); otherwise reject it. Field numbers are in decimal and start at 1 for the left most field. Field sizes are in characters and must be greater than 0.

Repeat for each check condition required. Note that the switch may need to be enclosed in quote characters to avoid special characters being recognised by the operating system.

Without this switch, the field size is not checked.

K8:=2

K9:<16

K10:>8

‘K9:<16’

‘K10:>8’

l

Convert new line and carriage return characters within unquoted fields to \n and \r respectively.

Without this switch, new line and carriage return characters are left as is.

l

L

Convert new line and carriage return characters within quoted fields to \n and \r respectively.

Without this switch, new line and carriage return characters are left as is.

L

m

Limit the total records loaded from the file. The limit is a decimal number and must be greater than 0.

Without this switch, there is no limit

m1

m1000

 n

Define the character sequence that represents a null value (other than consecutive delimiters). The value that represents null may be optionally quoted. If wish to treat consecutive double quotes as null then use n’””’.

Without this switch, only consecutive delimiters are treated as null.

nNULL

n’\\N’

n”’’”

n’””’

N

Specify columns that are forced to be empty strings for empty fields. Columns must be listed with comma separators. Quotes are optional.

Without this switch, empty fields are interpreted as nulls.

Nname,address

N’name,address’

N”NAME,ADDRESS”

 o

Specify the character encoding used in the source file. Use any encoding name available on the host. Typically this one of:

US-ASCII

UTF-8

UTF-16

UTF-16BE

UTF-16LE

Quotes around the encoding name are optional.

Without this switch the system default encoding is assumed.

oUS-ASCII

o’UTF-8’

o”UTF-16”
O

Omitted trailing fields are appended where the number of fields in the source record is less than the specific number.

Without this switch, no fields are appended.

O9

O16
p

Use a specified port number for database connections. This is only used where the port number does not explicitly appear within a database connection string.

Without this switch the default port number is used.

p5432

 q

Define the quote character used. Use explicit an character or a 2 digit hexadecimal character code. Quotes around the character are optional.

Without this switch the double quote character (") is assumed.

q\’

qF6

Q

Treat quotation marks as data or remove them. The switch Q means treat them as data. The switch QC means remove quotation character C wherever it appears. Use an explicit character or a 2 digit hexadecimal character code. Quotes around the character are optional.

Without this switch, quotation characters are interpreted as quotation markers.

Q

Q\”

QF6

r

Reject and remove records with invalid syntax or without the specified number of fields. The required number of fields is specified in decimal and must be greater than 0.

Without this switch, the loader will attempt to load every record regardless of the number of fields it contains.

r9

r16

R

Remove carriage return characters that appear outside of quoted fields.

Without this switch, carriage return characters are left as is.

R

s

Skip leading records unconditionally. The number of records skipped is specified as a decimal number greater than 0.

Without this switch no records are skipped.

s1

s10

u

Use a switch file S for switches. The switch file name may optional appear within quotation marks.

uload.opt

u’data/load.opt’

U

Specifies the username to be used for connection authentication. The username can optionally be enclosed within quotation marks

UScott

U’Scott’

U”Scott”

w

Trim trailing whitespace from unquoted values for the specified field. The field number starts at 1 for the left most field. Repeat for each field to be trimmed. Note that values enclosed within quotes will not be trimmed.

Without this switch, fields are not trimmed.

w1

w19

W

Insert one or more space characters into a field. The switch WN:P,Q,... means insert a space before character position P, Q etc. The field number N starts at 1 for the left most field. The character position starts at 1 for the first character.

Repeat for each field requiring a space insertion.

Without this switch, spaces are not inserted.

W8:4

W8:4,8

W19:2 W20:8,16

x

Exit if the number of rejected records exceeds a given limit. The limit is specified as a decimal number greater than 0.

Without this switch, the loader will exit on the first rejected record.

x1

x100

X

Do not exit regardless of the number of rejects.

Without this switch, the loader will exit on the first rejected record.

X

y

Yank the contents of a field into one or more fields using a regular expression.

Without this switch, the field is left as is.

y8:”.*(\d).*”

z

Remove or replace unprintable characters in unquoted data. If a character is specified, all unprintable control characters will be converted to the specified character; otherwise they will be removed. The character may optionally appear in quotes.

Without this switch, unprintable characters are left as is.

z

z?

z’ ’

z”\”

Z

Remove or replace unprintable characters in quoted data. If character C is specified, all unprintable control characters will be converted to the character C; otherwise they will be removed. The character may optionally appear in quotes.

Without this switch, unprintable characters are left as is.

Z

Z!

Z’ ’

Z”\”

Have more questions? Submit a request

Comments

Article is closed for comments.

Powered by Zendesk