Use the load process to transform the contents of an archive
or extract File into a format that is appropriate for a particular
DBMS loader and then, if desired, start the corresponding database
load utility.
The load process generates a data file in the correct
format for each table in the archive or extract file and an SQL file
or a BAT file (Batch Execution), depending on the DBMS, that contains
the syntax necessary to start the database loader.
Specifications for the load process are embodied
in a load request, which may be named and saved in the Optim™ directory, so that it is available for a variety of
users and uses. A load request may also be embedded in a restore request,
in which case it is not named and is not accessible except by editing
or using the restore request. The load request provides parameters
used to prepare data for a DBMS loader and the instructions required
to process the load. Specify a table map in the load request to map
the destination for the data to load. Use optional column maps to
transform data before loading. For more details, refer to the Common
Elements Manual .
Load versus insert
Archive
can move data into a database using a load process or an insert process. For
details on using a particular DBMS loader, please refer to the documentation
provided with your database management system and consider the following
items when deciding the best method:
- The volume of data and the speed of load processing may
offset the advantages of the insert process.
- Referential integrity (RI) cycles may exceed the
capability of the insert process to insert all the data successfully.
- A load utility prevents user access during processing.
The database is available to other users during insert processing.
- The load utility either inserts new data or replaces
existing data. The insert process provides update/insert processing
in one step.
Process file names
The load
process generates several types of files to support the database utility
load process:
- Data files — Archive generates a file for each
table in the source file, with the data prepared in a format appropriate
for the DBMS. Data files are given the name of the source file with
sequentially numbered file name extensions. For example, for an extract
file named demo.xf that contains data from three
tables, the load process will generate three data files named: demo.001, demo.002,
and demo.003.
- Format files — Additionally, a format file is generated
for each data file. A format file matches the name of the corresponding
data file with a distinguishing extension. If there are fewer than
500 tables to load, 500 is added to the data file name extension.
(For example, for data files named demo.001, demo.002,
and demo.003, the corresponding format files are
named demo.501, demo.502 and demo.503.)
A more complex algorithm is used when more than five hundred tables
are processed.
- SQL files — For DB2®, an
SQL file with one statement for each destination table is generated.
This file provides the syntax needed to execute the loader manually.
The SQL file name is the source file name, with the extension .sql.
- BAT files — For Oracle, Sybase ASE, SQL Server,
and Informix®, a BAT file
provides the syntax needed to manually execute the loader for each
table. A BAT file is generated for each DB alias specified in the
table map. Each BAT file resides in the directory with the corresponding
converted load file. If you chose to execute the loader manually,
the BAT file must be edited (in Notepad, for example) to replace a
string of eight question marks with specific password information
(except Informix).
- Message files — Message files contain information
that the database load utility generates during the load process.
Typically, there is one message file per load process. The message
file name matches the source file name, with the extension .msg.
For example, for an extract file named demo.xf,
the message file is named demo.msg.
- Fallback Files — For Teradata, an optional data file that can
be created when the Teradata Named Pipe is used. Data is written to
the fallback file to support a restart of the load, if needed. The
fallback file has an extension of .fbf and is automatically deleted
when the load completes successfully.
Note: If
your file server does not allow for file names greater than 8 characters,
the load process will fail to process a source file with a long name.
Avoid using long file names for archive or extract files or copy and
rename a file before you use it for a Load Process.
Run online or automate
You can process a load request immediately (by clicking ).
You can also run a load request from the command line, whether manually,
in batch, or from an external application. When running a process
from the command line, you can supply overrides for various load request
parameters and settings to tailor the process to circumstances as
they exist at runtime. (See Command Line Interface — Processing Utilities for more information.) Named
load requests can also be scheduled for automated processing by clicking .
Note: You
must have authority from the SYSADM or DBA to run or schedule the
load process.
You can also direct the load
process to generate the necessary files immediately, but defer running
the database load utility. If you choose not to start the database
load utility as part of the load request, the loader must be started
manually.
A load request can also
be run as part of a restore request. See Restore.
Naming conventions
The
fully qualified name of a Load request is in the form identifier.name.
- identifier
- Identifier that serves as the prefix for the request
name (1 to 8 characters).
- name
- Name assigned to the request (1 to 12 characters).
When you create load requests, it
is helpful to use a logical set of naming conventions to identify
the use for each and to organize them for easy access.