dbunload command syntax
You run the dbunload command to unload 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 dbunload looks as
follows:
dbunload [-h|-rev] [options]
Note: Due to a limitation, regular users other than
root and db2inst are required to use the following
syntax:DBLOAD_STRINGS_DIR=/opt/ibm/migration_tools/dbload/
strings/opt/ibm/migration_tools/dbload/dbunload [-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 unload at a time; the use of a control file allows multiple unloads.
- 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 dbunload command takes the following additional options:
| 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. |
| -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:
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 unload using DB2 ODBC driver. Used to perform remote unload to the local host. Possible values: YES, GZIP. GZIP enables compressing data before sending it over the network. |
| -db database | Specifies the database to unload from (overrides the value of the DB_DATABASE environment variable). |
| -schema schema | Specifies the schema name. 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 file path where data will be unloaded. This option corresponds to the DataObject external table option, which you can use for SQL. |
| -dataFileOverwrite | Tries to overwrite a data file if it already exists |
| Row syntax: | |
| -delim <char> | Separator between successive input fields [default = \t] Note: When you set a field delimiter
to space, you must also change -dateDelim to be different than space, as it is
its default value.
|
| -includeHeader <bool> | Includes column names as headers. Default is 'false'. |
| -decimalDelim <char> | Defines decimal delimiter for float/double/numeric, time/timezone/timestamps. Possible values
are '.'|',' Default value is '.' |
| Nulls | |
|
-nullValue <token> |
The UTF-8 string of at most 4 bytes that is to be used to indicate a null value. The default is 'NULL'. |
| Strings: | |
| -escapeChar <char> | Process escapes in char/varchar fields (currently <char> must be '\') |
| -crInString | Accepts unescaped CR in char/varchar fields (LF becomes only end of row) |
| -encoding <type> | Defines encoding. The following values are allowed: LATIN9, UTF8, INTERNAL. Default is INTERNAL. |
|
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 '-'] |
|
Times: |
|
| -timeStyle <style> | Possible values: 24HOUR, 12HOUR. Default: 24HOUR |
| -timeDelim <char> | Defines time component separator [default = ':'] |
| -includeZeroSeconds <bool> | Specify 00 as the value for seconds when no value for seconds is available. Default is 'false'. |
|
Booleans: |
|
| -boolStyle <style> |
Possible values: 1_0, T_F, Y_N, TRUE_FALSE, YES_NO. Default value is 1_0. |
| Basic options | |
| -h|--help|-? | Displays command help. |
| -rev|-Rev|-V | Displays the version. |
| -v|-verbose | Displays additional information about the unload session. |
| -dumpOpts | After processing options, prints them and exists without unloading. |
| -gmt | Prints session times in GMT time zone. (By default times are in a local time zone.) |