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.