Use the DB Alias tab to define the parameters needed for the DB2® for Linux, UNIX,
and Windows loader. This
tab is also used to define DB aliases used to process data in IBM® DB2 for
i or Teradata that is accessed with IBM InfoSphere® Federation Server.
Note, however, that the recommended method for Teradata is to process
directly without the use of Federation Server, as described for the
Teradata tab.
Load Strategy
To prepare data for loading
to DB2 for Linux, UNIX,
and Windows, the Optim™ solution writes formatted data files
to a network location. The location must be accessible to both the
solution and the database server. The database loader reads the files
from the network location and loads them to DB2. If a common network
location is not available, you can use DB2 remote
client loading. For remote loading, the data files are written to
a location outside the network – for example, a hard disk drive
on the Optim server or workstation.
The solution calls the DB2 client,
which reads the data file and sends the data to the DB2 server for loading. Because remote client
loading can cause contention issues, use it only if a network location
is not available. Also, remote client loading cannot be used to load
LOBs.
Your Optim solution
supports loading data into a DB2 for
i database by using the DB2 CLIENT
IMPORT command. A DB alias for DB2 for Linux, UNIX, and Windows is
required before you create the load request.

Mode
- Insert
- DB2 for i and Teradata
- Insert rows from the source file into empty destination tables.
If destination tables contain data, the loader returns an error. This
error occurs only if a primary key column is defined and this process
attempts to insert the same value into a table row.
- DB2 for Linux, UNIX,
and Windows
- Add
the loaded data to the table without changing existing data. If primary
key values match, the source row is inserted into an exception table,
if any is specified, or the table status becomes check pending.
- Replace
- DB2 for Linux, UNIX,
and Windows and Teradata
- Clear all data in the destination tables and replace with rows
from the source file. Use Replace Options to
manage statistics and recoverability of a load:
- Do not collect Statistics
- The solution generates STATISTICS
NO in the SQL script and other statistics options are unavailable.
- Collect default Statistics
- The solution generates STATISTICS USE PROFILE in the
SQL script. The DBMS profile for the table determines the statistics
collected during the load.
- Collect these Statistics
- The solution generates STATISTICS YES in the SQL script.
You can choose:
- Tables
- Table statistics.
- With Distribution
- Table distribution statistics.
- Indexes
- Index statistics.
- Detailed
- Detailed index statistics.
- Mark as NonRecoverable
- Tables are not left in an unusable state if the load process fails. The
solution generates NONRECOVERABLE in the SQL. The load transaction
cannot be recovered in a subsequent rollforward operation. See your
loader documentation for details.
File Type
- ASCII (DB2 for Linux, UNIX,
and Windows)
- Generate an ASCII file of fixed-length rows to import
data into other instances. This file type can be loaded to a database
that is enabled with DPF (Data Partitioning Feature).
- ASCII Delimited (DB2 for Linux, UNIX, and Windows)
- Generate an ASCII delimited file type, which
is a variable-length format with a delimiter character separating
each column value. If you select this file type, select a delimiter
that does not exist in the column data. This file type can be loaded
to a database that is enabled with DPF (Data Partitioning Feature).
When character columns are processed, the solution pre-scans the data
and issues an error message if a delimiter is present in the text.
If the data includes free-form text, such as a description field,
it may be difficult to choose a delimiter that does not appear in
the data. You can select a control character, that is less likely
to be present in the data.
- Delimiter
- Eliminating a scan for delimiters in character
data columns can improve load performance. To eliminate a scan, select
one of the following. The presence of the selected delimiter in the
source data causes a conversion error during the load process.
X'01' (No Pre-scan)
X'02' (No Pre-scan)
X'03' (No Pre-scan)
X'04' (No Pre-scan)
X'05' (No Pre-scan)
Scan while generating the loader
file. Select any of the following values from the Delimiter list:( ) * / ; ? | < > =
X'FA'
X'FB'
X'FC'
X'FD'
X'FE'
X'FF'
- IXF (DB2 for Linux, UNIX,
and Windows)
- The preferred import file type for expedient processing. DB2 for Linux, UNIX,
and Windows has an IXF record
length (row) limitation of 32K. This file type cannot be used to load
data to a database that uses DPF (Data Partitioning Feature).
- DB2 for i IXF
- Load data into a DB2 for
i instance.
- Teradata ASCII
- Generate an ASCII file of fixed-length rows.
If the data for a column is shorter than the maximum width of that
column, the data is padded with trailing blanks. This means more bytes
are sent over the network to the Teradata server, but a delimiter
is not required.
- Teradata Delimited
- For a Teradata loader, use to import data into
a Teradata instance. If you select this file type, select a valid
delimiter. This file type is valid for Teradata Fast Load and Multi
Load.
Options
- Perform Load
- DB2 for Linux, UNIX,
and Windows
- Run the loader immediately, after IXF file conversion is complete.
If you clear this check box or select an ASCII file type, the solution
prepares the data in the appropriate format. The solution then creates
the SQL to run the loader, but does not initiate the loader. To run
the loader, you can copy the syntax from the SQL file.
- Use Named Pipe
In a Windows or UNIX environment, load performance
might be improved by using a named pipe. When this option is used,
the load process runs in a single phase rather than a two-stage process,
resulting in reduced elapsed time for load processing. As the row
data is loaded immediately after conversion to a loader format, space
requirements are also reduced significantly, as compared to the two-stage
load process. The solution creates the named pipe and writes the
file to it. The load utility reads the data from the named pipe and
submits the data to the loader.
- DB2 for i
- Run the DB2 CLIENT IMPORT
command immediately, after IXF file conversion is complete. If you clear this check box, the load process prepares
the data in the appropriate format and creates the BAT file to run
the loader. To run the loader, edit the BAT file to include password
information and run the BAT file.
- Teradata
- Run the loader immediately, after file conversion is complete. If you clear this check box, the load process prepares
the data in the appropriate format and creates the BAT file to run
the loader. To run the loader, edit the BAT file to include password
information and run the BAT file.
- Use Named Pipe
In a Windows or UNIX environment, load performance
might be improved by using a named pipe. When this option is used,
the load process runs in a single phase rather than a two-stage process,
resulting in reduced elapsed time for load processing. As the row
data is loaded immediately after conversion to a loader format, space
requirements are also reduced significantly, as compared to the two-stage
load process. The solution creates the named pipe and writes the
file to it. The load utility reads the data from the named pipe and
submits the data to the loader.
For Teradata, the Optim solution writes the data to the named
pipe and the Teradata access module reads and copies the data, which
is loaded. Optionally, you can write the data to be loaded to a fallback
file, so that you can restart the load with Teradata utilities. The
solution writes data to the fallback file, based on the save interval
you set. After the load process completes, the fallback file is automatically
deleted. The fallback file extension is .fbf. The file is in the %TEMP%
or %WINDIR%\temp directory.
- Delete files if Successful
- Fast Load Confirmation is
not available.
- DB2 for Linux, UNIX,
and Windows and Teradata
- Delete the data files after the loader completes successfully.
This option is unavailable if you selected Use Named Pipe.
- DB2 for i
- Delete files after the DB2 CLIENT
IMPORT command runs.
- Delete files if Failure
- Fast Load Confirmation is
not available.
- DB2 for Linux, UNIX,
and Windows and Teradata
- Delete the data files if the loader does not complete successfully.
This option is unavailable if you selected Use Named Pipe.
- DB2 for i
- Delete files if the DB2 CLIENT
IMPORT process does not complete successfully.
- Load When Source is Empty (DB2 for Linux, UNIX, and Windows, DB2 for i, and Teradata)
- Complete the load if a table to be loaded contains no rows, deleting
any rows from the target. If not selected, empty source tables are
skipped.
- Load from remote client (DB2 for Linux, UNIX, and Windows only)
- Load by using the remote client option. If a common network
location is not available, you can use DB2 remote
client loading. For remote loading, the data files are written to
a location outside the network – for example, a hard disk drive
on the Optim server or workstation.
The solution calls the DB2 client,
which reads the data file and sends the data to the DB2 server for loading. Because remote client
loading can cause contention issues, use it only if a network location
is not available. Also, remote client loading cannot be used to load
LOBs.If you select this check box,
you must supply a path to the location of the data files. The solution
generates a LOAD command with the CLIENT keyword in the SQL.
- Load With Identity Override (DB2 for Linux, UNIX, and Windows only)
- Perform the load if an identity column defined as
GENERATED ALWAYS is present in the table to be loaded with user-supplied
values. It instructs the load utility to accept non-NULL data for
such a column (contrary to the normal rules for these types of identity
columns). When this option is used, any rows with no data or NULL
data for the identity column will be skipped.
Exception Table Options (DB2 for Linux, UNIX, and Windows)
An exception
table records each source row that violates unique index or primary
key rules. The exception table includes a copy of the row with a timestamp
and a description of the violation.
Use one or both of the
following options to create an exception table for each destination
table as part of DB2 load processing.
- Load
- Create exception tables for rows that violate unique
index or primary key rules.
- Constraints
- Create exception tables for rows that violate referential
integrity or table check constraints.
The solution ensures
that names of exception tables do not match names of destination tables
specified in the load request. You can modify the exception table
names, but ensure that the names do not match names of existing database
tables. Leave the exception table name blank to discard duplicate
rows. Existing exception tables are dropped before starting the load
utility. Dropping the tables ensures that newly created exception
tables contain only the information for the current database load.
Before tables are dropped, a dialog on which you can change the creator
ID to create different exception tables, opens.
Click from the menu to display the Exception Table
Mapping dialog.
If any constraints
are violated, discarded rows are placed in the exception table and
the database table is placed in check pending status. In addition,
the solution issues the DB2 SET
CONSTRAINTS statement for each table. You must use the DBMS
utilities to resolve any problems for database tables that have pending
status.
Save Interval (DB2 for Linux, UNIX, and Windows and Teradata)
The
interval for which you want the loader to commit changes to the database
or write data to the fallback file.
- To commit changes after a number of rows are processed,
enter the number of rows up to 999999999.
- To commit changes after all data is loaded into
a table, enter zero (0) or leave blank. This method ensures that all
data or no data is loaded.
Warning Limit (DB2 for Linux, UNIX, and Windows and Teradata)
The maximum number of rows, up to 999999999, that
the loader can discard during the load process.
- Enter 1 to end the process if a single row is discarded.
- Enter zero (0) or blank to continues processing
regardless of the number of rows discarded.
Copy Options (DB2 for Linux, UNIX, and Windows)
Use Copy Options to
instruct the loader to make an image copy of the source data during
the load process. To make an image copy, ensure that the LOGRETAIN and USEREXIT options
are set to ON in the loader. The image copy
is used to provide forward recovery. If LOGRETAIN and USEREXIT are
set to OFF, the database does not provide forward
recovery, and the copy image option is ignored.

- Do not copy
- Do not make an image copy.
- If you do not make an image copy, the table space
for the table is placed in a backup pending state. A table space backup
or a full database backup is required to clear the pending status.
- If you restore the database to resolve pending states,
you must use the configuration tool to apply maintenance for Optim directory and DB alias access.
- Copy image to directory
- Create an image copy. The load utility generates the file names
for each table based on the date and time of the load. Use Path
Name to provide a directory path for the image copy.
- Path Name
- Directory path for the image copy. Use browse to select from system
directories.
- Copy image using ADSM
- Create an image copy by using ADSM.
- I/O Sessions
- The number of I/O sessions to be used with ADSM.
The Copy Options settings
are profiled when you click OK, making it unnecessary
to set copy options for each load request. Use Cancel to
close the dialog, ignore changes, and return to the DB
Alias tab of the Load Request Editor.
Directory Paths (DB2 for Linux, UNIX, and Windows)
Directory paths to the location
of loader files. The files must be stored on a drive that can be accessed
by the database server and the client. The files must also be accessible
to the workstation and network server. If DB2 is running on a remote server and is started
before Windows connects to
the network drives, the process report might indicate an invalid path.
Restart DB2 to correct this
issue.
- Work path for data files:
- The workstation directory path to the data files.
- Server path for data files:
- The network server directory path to the data files.
- Server path for temporary files:
- Path to temporary loader files. The solution generates a TEMPFILES
PATH clause in the SQL. Server path for temporary files is
unavailable if Load from remote client is selected.