dbload control file

With a dbload control file, you can define load operations in a text file instead of having to specify options on the command line.

You can also use control files to run multiple sequential loads, with different options, in one command instance. Each load is a different transaction. If a load fails, the command continues to run the other load operations in the file. The command displays messages to inform you of the success or failure of each load operation.

Options

badfile (bf): Specifies the name of the *.bad file, which contains any records that cannot be loaded. The default is table.schema.database.bad.
database: Specifies the name of the database that contains the table into which you are loading data.
datafile: Specifies the path name of the file that you want to load into the specified table, schema, and database. The datafile option must be the first line of the control file, followed by list of control file options in curly braces {}. You can specify more than one data file, each with its own set of options, in the control file. The datafile option corresponds to the DataObject external table option, which you can use for SQL.
logfile (lf): Specifies the name of the dbload log file, which contains messages and errors that occurred during load processing. The default is table.schema.database.log.
schema: Specifies the schema into which to load the data. Use this option only if the system supports multiple schemas in a database.
tablename: Specifies the name of the table into which to load the data.

The options in a control file are not case sensitive. For example, you can specify the database, DataBase, Database, or DATABASE option.

Command-line options take precedence over any equivalent options that you specify in a control file. With this precedence, you can override any control file options without changing the control file.

Syntax

The syntax for using a control file is as follows, where each sequence of the DATAFILE keyword and associated options is used for a different load:

DATAFILE <filename>
{
[<option name> <option value>]*
}

For example, the following control file options are used to load the data from the customer.dat file into the customer table:

DATAFILE /home/operation/data/customer.dat
{
    Database    dev
    schema      sales
    TableName   customer
}

If you save the control file contents as a text file, you can specify it by using the dbload command. The following sample command uses a text file that is named cust_control.txt:

dbload -cf /home/nz/sample/cust_control.txt 
Load session of table 'CUSTOMER' completed successfully

When you use the dbload command, you cannot specify both the -cf and -df options in the same command. You can load from a specified data file or load from a control file, but not both in one command.

The following control file options define two data sets to load. The options can vary for each data set.

DATAFILE /home/operation/data/customer.dat
{
    Database    dev
    Schema      sales
    TableName   customer
    Delimiter   '|'
    Logfile     operation.log
    Badfile     customer.bad
}

DATAFILE /home/imports/data/inventory.dat
{
    Database    dev
    Schema      ops
    TableName   inventory
    Delimiter   '#'
    Logfile     importload.log
    Badfile     inventory.bad
}

If the control file contents are saved as a text file named import_def.txt, it can be specified for the dbload command as follows:

dbload -cf /home/nz/sample/import_def.txt 
Load session of table 'CUSTOMER' completed successfully
Load session of table 'INVENTORY' completed successfully