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 specified, 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.
A Load Request contains the 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 in the Load Request
to transform data before loading. For details, see
the Common Elements Manual.
Data to be loaded must be contained in an Archive
or Extract File. You can use the Archive or Extract Process to create
the corresponding file, or use an existing Archive or Extract File.
See Extract Process, or see the Archive User Manual.
Load versus Insert
Optim™ can move data into a database
in two ways — using a Load Process or an Insert Process. Consider
the following when deciding which method to use:
- The volume of data and the speed of using the database
load utility may offset the advantages of the Insert Process.
- The data may contain referential integrity (RI)
cycles that exceed the capability of the Insert Process to insert
all the data successfully.
- The database load utility requires exclusive control
of the database and prevents user access during the Load Process.
The database is available to other users while the Insert Process
is performed.
- The database load utility either inserts new data
or replaces existing data. The Insert Process allows for Update/Insert
processing in one step.
Process File Names
The Load
Process generates the following types of files to support the database
utility load process:
- Data files — Data files contain the data
you want to load, prepared in the format appropriate for the DBMS
you are using. Optim generates
a data file for each table in the Archive or Extract File. Data files
are named the same as the Archive or Extract File, but contain sequentially
numbered file name extensions. For example, an Extract File named demo.xf that
contains three tables will generate three data files named: demo.001, demo.002,
and demo.003.
- Message files — Message files contain information
that the database load utility generates during the Load Process.
Typically, there is one message file for the entire Load Process.
The message file is named the same as the Archive or Extract File,
but contains the extension .msg. For example, if the Extract
File is named demo.xf, the message file is named demo.msg.
- For DB2®, an SQL
file is generated with one statement for each destination table
that contains the loader syntax to manually execute the loader. The
SQL file is named the same as the Archive or Extract file, but has
the extension .sql.
- For Oracle, Sybase ASE, SQL Server, and Informix®, a BAT file is
generated that contains the syntax 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 manually execute
the loader, the BAT file must be edited (in Notepad, for example)
to replace a string of eight question marks with specific password
information (except Informix).
Additionally, a Format file is generated for
each data file. A format file has the same name as the corresponding
data file, except that the file name extension is different. If there
are less than 500 tables to load, the format file name extension is
500 greater numerically than the data file name extension. (For example,
if there are three data files named demo.001, demo.002,
and demo.003, the corresponding format files are named demo.501, demo.502 and demo.503,
respectively.) If there are more than five hundred tables, a more
complex file extension generation algorithm is employed.
Note: If
your file server does not allow long file names of greater than 8
characters and the Archive or Extract File has a long name, the Load
Process will fail. The best solution is to avoid using long file names
for Archive or Extract Files. If needed, you can copy and rename a
file before you use it for a Load Process.
Run or Schedule
You can
process a Load Request immediately by clicking , or you can save and schedule
the request for processing at a later time by clicking . You must save the request before it is scheduled,
but it is not necessary to save the request before it is run.
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.
Note: You cannot start the database load utility from
within Optim using a previously
created set of SQL statements.
Naming Conventions
The
fully qualified name of a Load Request consists of: 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.
Chapter Contents
This
chapter explains how to create and maintain a Load Request, including
how to:
- Specify the Source File containing the data you
want to load.
- Specify the Control File to record information about
the process.
- Choose to run DBMS loaders in parallel or in sequence.
- Select or create a Table Map (and optional Column
Maps) to provide more control over the data you want to load.
- Specify default options for date aging to adjust
dates in specified columns.
- Specify notification options.
- Run, save, and schedule a Load Request.
- Review, save and print the Load Process Report.
Note: For details on using a particular DBMS loader, refer to
the documentation provided with your DBMS.