Use a load service to transform the contents of a source
file into a format that is appropriate for a particular DBMS loader
and then, if specified, start the corresponding database load utility.
The load service generates a data file in the correct
format for each table in the source 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 service 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 service to map the destination
for the data to load. Use optional column maps in the load service
to transform data before loading.
Data to be loaded must be contained in a source file.
Load versus insert
Optim™ can move data into a database
by using a load or an insert service. 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 service.
- The data may contain referential integrity (RI)
cycles that exceed the capability of the insert service to insert
all the data successfully.
- The database load utility requires exclusive control
of the database and prevents user access during the load service.
The database is available to other users while the insert service
is performed.
- The database load utility either inserts new data
or replaces existing data. The insert service allows for update/insert
processing in one step.
Output file names
A load service
generates the following types of files to support the load process
of the database utility:
- 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 source file. Data files are named the same as the source file,
but contain sequentially numbered file name extensions. For example,
a source 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 service. Typically, there is one message
file for the entire load service. The message file is named the same
as the source file, but contains the extension .msg. For example,
if the source file is named demo.xf, the message file is named demo.msg.
- SQL file
- 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 file
- 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.
Note: If your file server does not allow long file
names of greater than 8 characters and the source file has a long
name, the load service will fail. The best solution is to avoid using
long file names for source files. If needed, you can copy and rename
a file before you use it for a load service.
Table maps
Table maps match
source tables to destination tables. Individual tables can be excluded,
and tables with different names can be mapped. You can use an existing
table map or define the table map along with the other specifications
for the load service.
When the columns in the
destination table match the columns in the source table, Optim automatically inserts the data. When
the columns do not match, the unmapped data is not inserted unless
column maps are specified.
Column maps
Column maps
match source columns to destination columns that have different column
names, eliminate columns from the process, and most importantly, allow
the specification of values to be used to populate the destination
columns.
Values that can be used to populate a
destination column include special registers, the NULL value, literals,
constants, expressions, and exit routines.