dbload command options

You can use the dbload command to load data. You can accept the defaults or specify options by using the command line, the control file, or environment variables.

Syntax

The basic command syntax for dbload looks as follows:
dbload [-h|-rev] [options]

If you are using IBM® Db2® Warehouse, the way in which you run the command depends on where you obtained the Db2 support tools. For information about how to run the command, see the Db2 support tools overview.

Inputs

The precedence of the input values is as follows:
  • Command line.
  • Control file. Without a control file, you can do only one load at a time; the use of a control file allows multiple loads.
  • Environmental variables. You can use these only for the user, password, database, and host.
  • Built-in defaults.

Option names are not case sensitive. Every option has a standard name for use either on the command line or in the control file.

Many options include an argument, which you can enclose in either single or double quotation marks. The command ignores letter casing for the characters in option arguments. For example, -boolStyle YES_NO is equivalent to -boolStyle yes_no. You must use quotation marks for options that require a punctuation character as an argument and use an escape character if quotation marks are part of the argument.

Command options

The dbload command takes the following additional options:
Table 1. Options for the dbload command
Option Description
-host name Specifies the host name or IP address (overrides the value of the DB_HOST environment variable). If you do not specify a value, the command runs on the local host. If you set this option to any name other than localhost or any IP address other than the reserved one (127.0.0.1), the system sets the remotesource option to ODBC.
-excludeGeneratedColumns Skips generated columns, which are included by default.
-includeHiddenColumns Includes hidden columns, which are skipped by default.
-u user Specifies the database user name (overrides the value of the DB_USER environment variable).
-pw password Specifies the password of the user (overrides the value of the DB_PASSWORD environment variable).
-loginTimeout int-seconds Specifies the login timeout, in seconds. This option overrides the default value of 30 seconds.
-port number Specifies the port to use, which you can use to override the default. The default is 50000.
-securityLevel level Specifies the security level for the session. The option has four values:
preferredUnSecured
This value is the default value. Specify this value when you prefer an unsecured connection but you will accept a secured connection if the system requires one.
onlyUnSecured
Specify this value when you want an unsecured connection to the system. If the system requires a secured connection, the connection is rejected.
preferredSecured
Specify this option when you want a secured connection to the system but you will accept an unsecured connection if the system is configured to use only unsecured connections.
onlySecured
Specify this option when you want a secured connection to the system.

If you specify an invalid value for the -securityLevel option, the preferredUnSecured level is used by default.
-caCertFile path Specifies the path name of the root CA certificate file on the client system. This argument is used by clients that use peer authentication to verify the host system. The default value is NULL, which skips the peer authentication process.
-remoteSource <YES|GZIP> Enforces remote load using DB2 ODBC driver. Used to perform remote load to the local host. Possible values: YES, GZIP. GZIP packages data before sending it.
-db database Specifies the database to load (overrides the value of the DB_DATABASE environment variable).
-schema schema Specifies the schema in which to load the table for a system that supports multiple schemas in a database. If you do not specify the -schema option, the system uses the value of the DB_SCHEMA environment variable, if you set it. If the value of the DB_SCHEMA environment variable is not set, the system uses the default schema for the database.
-t table Specifies the table name. You can specify a fully qualified name.
Files:  
-cf filename Specifies the control file.
-df filename Specifies the data file to load. If you do not specify a path, the dbload reads data from stdin of the process. This option corresponds to the DataObject external table option, which you can use for SQL.
-lf filename Specifies the log file name. If the file exists, it is appended to.
-bf filename Specifies the bad or rejected rows file name. The file is overwritten if it exists.
-outputDir dir Specifies the output directory for the log and bad or rejected rows files. This option corresponds to the LogDir external table option, which you can use for SQL..
-partition <statement> Partitions external table into multiple files.
Counters:  
-skipRows <n> Skips (do not load) the first n input rows [default = 0 (none)]
-maxRows <n> Ends loading after processing n input rows [default = 0 (all)]
-maxErrors <n> Aborts after encountering n bad input rows [default = 1]
Row syntax:  
-delim <char> Acts as a separator between successive input fields. CHAR,HEX,DEC delimiters are supported.
An example follows: 
[-delim ‘x2c’ for HEX, -delim ‘44’ for DEC]

Default value is \\t.
-ignoreZero <bool>

Defines if binary zero bytes in input generate errors (NO) or are ignored (YES) or are accepted (KEEP):

NO, FALSE / YES, TRUE [default = error]
-quotedValue <type> Defines whether fields are unquoted (NO) or enclosed in optional [SINGLE|DOUBLE] quotes:

NO / YES, SINGLE, DOUBLE [default = NO; default for YES = SINGLE]
-requireQuotes Specifies that quotes are mandatory, except fro null Value. By default the quotes are optional.
-format <type> Specifies the record format:

TEXT, INTERNAL, FIXED

Default value is TEXT.
-decimalDelim <char> Defines decimal delimiter for float/double/numeric, time/timezone/timestamps. Possible values are '.'|','

Default value is '.'
Nulls  

-nullValue <token>

Case-insensitive alpha-numeric pattern or ''

Default value is NULL. Maximum 4 characters allowed.
-fillRecord Treats missing trailing input fields as null. Columns must be "nullable".
Strings:  
-escapeChar <char> Process escapes in char or varchar fields. There is no default value.
-ctrlChars Accepts control chars in char or varchar fields (must escape NUL, CR and LF)
-crInString Accepts unescaped CR in char or varchar fields (LF becomes only end of row)
-truncString Truncates any string value that exceeds its declared char or varchar storage
-encoding <type> Defines encoding. The following values are allowed: LATIN9, UTF8, INTERNAL. Default is INTERNAL.
-disableNfc Disables NFC.
-CCSID <value> Specifies Character Coded Set Identifier. Accepts any value between 0 and 65535 from the CCSID specification.

Dates:

  
-dateStyle <style> Defines date style. Possible values: Y[2]MD, DMY[2], MDY[2], MONDY[2], DMONY[2] Default value: YMD
-dateDelim <char> Defines date component separator [MONDY[2] = ' ' else '-']
-dateFormat <format> Defines date format, using string accepted by TIMESTAMP_FORMAT. Cannot be used with -dateStyle and -dateDelim.
-y2Base <year> Defines first year expressible, using two digit year (Y2) dateStyle.

Times:

  
-timeStyle <style> Possible values: 24HOUR, 12HOUR. Default: 24HOUR
-timeDelim <char> Defines time component separator [default = ':']
-timeFormat <format> Defines time format, using string accepted by TIMESTAMP_FORMAT. Cannot be used with -timeStyle and -timeDelim.
-timeRoundNanos Allows but rounds non-zero digits with resolution smaller than microsecond.
-timestampFormat <format> Defines timestamp format, using string accepted by TIMESTAMP_FORMAT. Cannot be used with -timeStyle, -timeDelim, -dateStyle and -dateDelim.

Booleans:

  
-boolStyle <style>

Possible values: 1_0, T_F, Y_N, TRUE_FALSE, YES_NO. Default value is 1_0.
Merge  
-merge operation Specifies that the contents of the data file are to be merged into the target table. The possible values of operation are as follows:
INSERT
Inserts new rows into the table.
UPDATE
Updates existing rows in the table.
INSERTUPDATE
Inserts new rows into the table and updates existing rows in the table.

You cannot choose which columns to update or insert. Also, there is no delete functionality.

To identify whether to insert or update a particular row, you must also specify a match condition by using the -mergeSchema option, the -mergeOn option, or both. If the match condition is met (the row exists in the table), the row is updated. If the match condition is not met (the row does not exist in the table), the row is inserted.
-mergeOn "condition" Specifies a match condition, a filter, or both for the -merge option, in the form of an SQL expression:
  • The match condition determines whether the value of a particular column in the data file matches the value of the same column in the table. A match condition has the following form: 
    src.column_name = table_name.column_name
    Qualify the name of the column with src for the data file and table_name for the table. Use the same column_name value for both the data file and table. An example follows:
    -mergeOn "src.col1 = T1.col1"

    If you do not specify a match condition by specifying the primary_key value, you must specify a match condition by using the -mergeOn option. If you use both the primary_key value and -mergeOn option to specify match conditions, those conditions are combined.

  • A filter identifies a subset of records in the data file, target table, or both. Specify one or more column names and operators. Qualify the name of a column with src for the data file and table_name for the table. Use the same column_name value for both the data file and table. An example follows:
    -mergeOn "src.col1 > 3"
-mergeSchema "column_name data_type [primary_key]" Specifies the schema of the target table and, optionally, a match condition for the -merge option. An example follows: 
-mergeschema "col1 int primary_key,col2 int,col3 int"
The following rules apply:
  • You must specify all the columns that are in the table, although the case of the names that you specify does not need to match the case of the names in the table.
  • The order in which you specify the columns and the order of the columns in the data file must match. However, the order of the columns in the table can be different.
  • The data type that you specify for a column must match the data type for the column in the table. Specify the double precision data type as double_precision.
  • The interval data type is not supported.

The primary_key value identifies a column whose value is used as a match condition. The match condition determines whether the value of a particular column in the data file matches the value of the same column in the table. For example, if you specify col1 int primary_key, the system searches for a match between the value of the col1 column in the data file and the value of the col1 column in the table. If you do not specify the primary_key value, you must specify a match condition by using the -mergeOn option. If you use both the primary_key value and -mergeOn option to specify match conditions, those conditions are combined.
External zones:  
-recLength <number> Specifies external record length. Accepts integers. The default value is 'unused'.
-recDelim <string> Specifies external record separator. Default is 'new-line'.
-nullIndicator Specifies if the record null-indicator bytes are present. By default is set to 'false'.
-layout "<zoneDefList>" Specifies the external column zone definition list. An example follows: 
c1 int decimal bytes 4, c2 numeric decimal ',' bytes 5, ...
Basic options  
-h|--help|-? Displays command help.
-rev|-Rev|-V Displays the version.
-v|-verbose Displays additional information about load session.
-dumpOpts After processing options, prints options and exists without loading data.
-gmt Prints session times in GMT time zone. (By default times are in a local time zone.)
-sts Allows backward compatibility with nzload. It is accepted in dbload but has no effect on the program.