Working with load services

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:

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.



Feedback