Use the load process to prepare the contents of source
file (an archive or extract file) for a particular DBMS loader. If
wanted, you can also start the corresponding database load utility.
For each table in the source file, the load process
generates a data file in the correct format. If you are not using
named pipes, the Optim™ solution
also generates an SQL or batch execution file that contains the syntax
needed to start the database loader.
Specifications for the load process are embodied
in a load request. You can name and save the load request in the Optim directory, so that it is
available for various users and uses. An unnamed load request can
also be embedded in a restore request and you can edit it as part
of the restore request. The load request provides parameters that
are used to prepare data for a DBMS loader. The load request also
provides instructions needed to process the load. A table map is used
to map the destination for the data to load. Also, optional column
maps transform data before loading.
Load versus Insert
Your Optim solution can move data into
a database by loading or inserting. For details on using a particular
DBMS loader, see the documentation for your database management system.
Also, consider the following when deciding the best method:
- The volume of data and the speed
of load processing can offset the advantages of the insert process.
- Load bypasses referential integrity
(RI) cycles that might exceed the ability to insert all the data successfully.
- A load utility might lock a database
table during processing, while an insert does not lock tables.
- A load utility either inserts new
data or replaces existing data. An insert provides table-specific
update and insert processing in one step.
Process Files
The Optim solutions generate several
types of files to support the database utility load process. If
your file server does not support file names of more than eight characters,
the load process cannot process a source file with a long name. Do
not use long file names for source files or copy and rename a file
before you use it for a load process.
As you design or modify
a load request, you can use the resource estimator to calculate the
storage needed for these files. From the Load Request Editor,
click to calculate the amount of storage
needed for the conversion process and create a Resource Estimation Report. Generally, you might want to
run the resource estimator offline or during nonproduction times to
prevent possible resource contention issues.
- Data files Unless you are
using named pipes, the solution 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 solution generates three data
files named: demo.001, demo.002, and demo.003.
- Format files In addition,
a format or control file is generated for each data file. The name
of 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, demo.003,
the corresponding format files are named demo.501, demo.502,
and demo.503. A more complex algorithm is used
when more than 500 tables are processed.
- SQL files For some DBMSs, an SQL file with one statement
for each destination table is generated. This file provides the syntax
needed to run the loader manually. The SQL file name is the source
file name, with the extension .sql. For DB2 for i, the extension is .isql.
- BAT files For other DBMSs,
a BAT file provides the syntax needed to manually run the loader for
each table. A BAT file is generated for each DB alias referenced in
the table map. Each BAT file is in the directory with the corresponding
load file. For DBMSs other than Informix® or
DB2, you must edit the BAT file before running the loader manually.
Edit the file by using Notepad or other software to replace a string
of eight question marks with specific password information.
- Message files Typically,
there is one message file per process. The message file contains information
that the database load utility generates during the 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 A fallback file is 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 deleted when the load completes successfully.
Run online or automate
If you have DBMS authorization, you can run a load request
by clicking or you can schedule named load requests 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 command-line
process, you can supply overrides to tailor the process to circumstances
as they exist at run time.
A load request can
also be run as part of a restore request. Also, you can direct the
load process to generate the necessary files immediately, but defer
running the database load utility. If deferred, the loader must be
started manually.
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 - 8 characters).
- name
- Name assigned to the request (1 - 12 characters).
It is helpful to use a logical set
of naming conventions to identify the use for each load request and
to organize them for easy access. For example, TDMRefresh.AcctsRec.
Section contents
This
section explains how to create and maintain a load request, including
how to:
- Specify the source file that contains
the data you want to load.
- Specify the control file to record
information about the process.
- Select or create a table map (and
optional column maps) to provide more control over the data you want
to load.
- Specify default options to adjust
dates in specified columns.
- Specify notification options.
- Estimate the amount of storage needed to process
the load request.
- Run, save, and schedule a load request.
- Review, save, and print the load
process report.