Optim™ supports the DB2® loader. Use the DB
Alias tab in the Load Request Editor to
define the specific parameters needed to perform the Load Process.
DB2 Remote Client
Loading
When Optim loads
data to DB2, the data files
are created and written to a network location accessible to both Optim and the DB2 server. The 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. Remote
client loading writes the data files to location outside the network
– for example, a local Optim Server
hard drive. Optim calls the DB2 client which reads the data
file and sends it to the DB2 server
for loading. Remote client loading cannot be used to load LOB data.
Since it may cause contention, use remote client loading only if there
is no network location available.
DB2 Load using
Named Pipe
In a Windows or
Unix environment, load performance may be significantly improved by
using a Named Pipe. When this option is used, the load process runs
in a single phase rather than a 2-stage process, resulting in reduced
elapsed time for load processing. As the row data is immediately loaded
after conversion to a loader format, the space requirements are also
significantly reduced, when compared to the 2-stage load process.
Optim creates the Named Pipe
and writes the Archive or Extract File data to the Named Pipe. The
loader utility reads the data from the Named Pipe and then submits
the data to the loader.
The DB2 Alias tab of the Load Request
Editor has the following elements:
Mode
- Insert
- Inserts the rows from the Source File into the destination
tables. If primary key values match, duplicate rows are inserted into
the appropriate Exception table if specified, or the table status
becomes Check Pending. Refer to the DB2 documentation
for additional information about Check Pending status.
- Replace
- Clears and replaces all of the existing rows in
the destination tables with the rows from the Source File. Selecting Replace enables
the Replace Options button, which opens this
dialog:

- Do not collect Statistics
- Select this button if you do not want to collect statistics for
this load process. Optim generates
STATISTICS NO in the SQL script. If you select this button, the other
statistics options are unavailable.
- Collect default Statistics
- Select this button to have Optim generate
STATISTICS USE PROFILE in the SQL script. The profile defined for
this table determines the statistics collected during the load. For
details refer to the documentation for the loader.
- Collect these Statistics
- Select this button to choose the statistics to be collected. Optim generates STATISTICS YES
in the SQL script. You can choose:
- Tables
- Select this check box to collect table statistics.
- With Distribution
- Select this check box to collect table distribution statistics.
- Indexes
- Select this check box to collect index statistics.
- Detailed
- Select this check box to collect detailed index statistics.
- Mark as NonRecoverable
- Select this check box to prevent tables from being left in an
unusable state if the Load process fails. Optim generates the NONRECOVERABLE keyword
in the SQL. NONRECOVERABLE specifies that a load transaction is to
be marked as non-recoverable, and that it will not be possible to
recover it by a subsequent rollforward operation. Refer to your loader
documentation for details.
Exception Table Options
An exception table contains
copies of rows that violate unique index or primary key rules. Each
exception table includes a timestamp column and a description column
that contains the DB2 description
of the violation.
Select one or both of the following
options to create an exception table for each destination table as
part of database load utility processing.
- Load
- Select this check box to create exception tables
to store rows that violate unique index or primary key rules.
- Constraints
- Select this check box to create exception tables
to store rows that violate referential integrity or table check constraints.
Optim ensures
that the names of exception tables do not match names of destination
tables specified in the Load Request. You can modify the exception
table names, but make sure that the names do not match the names of
any existing database tables.
If the Exception Table name is
left blank, duplicate rows are discarded. The Load Process drops existing
exception tables before starting the database load utility to ensure
that newly created exception tables contain only the information for
the current database load.
Note: A confirmation dialog opens before
exception tables are dropped. Change the Creator ID to create different
exception tables.
Click to
display the Exception Table Mapping dialog.
After the loader begins processing,
if any data or referential integrity constraints are violated, the
discarded rows are placed in the exception table and the database
table is placed in Check Pending status. In addition, Optim issues the DB2 SET
CONSTRAINTS statement for each table.
Note: You must use
the DBMS utilities to resolve any problems for database tables that
have pending status. For complete details, refer to your DBMS documentation.
File Type
- ASCII
- For DB2 Linux, UNIX or Windows, use to import data into
other DBRM instances or EEE sites.
- ASCII Delimited
- For DB2 Linux, UNIX or Windows, use to import data into
other DBRM instances or EEE sites. If you select this file type, select
a valid delimiter.
- iSeries® IXF
- For iSeries, use to
import data into an iSeries instance.
- IXF
- For DB2 Linux, UNIX or Windows, use as the preferred
import file type for expedient processing.
Note: DB2 has an IXF record length (row) limitation
of 32K.
- Teradata ASCII
- For a Teradata loader, use to import data into a Teradata instance.
See DB Alias Tab - Teradata, Load Request Editor.
- Teradata Delimited
- For a Teradata loader, use to import data into a Teradata instance.
See DB Alias Tab - Teradata, Load Request Editor.
Delimiter
- Delimiter
- You can exclude the delimiter from being scanned in character
data columns by Optim while
generating the loader file. This can improve load performance. To
exclude delimiters from scanning, select any of the following values
from the Delimiter drop-down list:
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)
Additional delimiters are supported
which are scanned in character data columns by Optim while generating the loader file. Select
any of the following values from the Delimiter drop-down list. Optim scans these values while
generating the loader file:X'FA'
X'FB'
X'FC'
X'FD'
X'FE'
X'FF'
Save Interval
Specify
whether you want the loader to commit changes to the database after
processing a specified number of rows or after each table is processed.
- To commit changes based on a number of rows, enter
the number of rows up to a maximum of 999999999.
- To commit changes after all data is loaded into
a table, specify zero (0) or leave blank. This method ensures that
all data or no data is loaded.
Warning Limit
Specify
the number of rows, up to a maximum of 999999999, that the loader
can discard during the Load Process. The process stops when the specified
number of rows is discarded.
- To end the process if a single row is discarded,
specify 1 as the maximum.
- To set no limit to the number of rows that can be
discarded, specify zero (0) or leave blank.
Copy Options
Click Copy Options to
select options to instruct the loader to make an image copy of the
input data during the Load Process. These options are specified on
the Select Copy Option dialog.
Note: To make an image copy, ensure
that the LOGRETAIN and USEREXIT options
are set to ON in the loader. You should request
an image copy if either LOGRETAIN or USEREXIT is specified in the
loader. The image copy is used to provide forward recovery.
- Do not copy
- Select this option to choose not to make an image
copy.
- If you do not make an image copy, the tablespace
where the table resides is placed in a backup pending state. A tablespace
backup or a full database backup is required to clear the pending
status.
- If LOGRETAIN and USEREXIT are
set to OFF in the loader, the database does
not provide forward recovery, and the copy image option is ignored.
- If you restore the database to resolve pending states,
you must use the Configuration tool to apply
maintenance for Optim Directory
access and DB Alias access. See the Installation and Configuration
Guide for details.
- Copy image to directory
- Select this option to create an image copy. The
load utility generates the file names for each table based on the
date and time of the load.
Note: You can specify a directory path
to store the image copy. If the directory does not exist, you can
create one by responding to the prompts.
- Path Name
- Directory path to store the image copy. Click the
browse button to select from your system directories.
- Copy image using ADSM
- Select this option to create an image copy using
ADSM. (For complete information on using ADSM, refer to the DB2 documentation.)
- I/O Sessions
- Specify the number of I/O sessions to be used with
ADSM.
The values you specify in the Copy
Options dialog are profiled. Therefore, if you always use
the same specifications, it is not necessary to set copy options for
each Load Request. Click OK to retain your
specifications. Click Cancel to close the dialog,
ignore changes, and return to the DB Alias tab
of the Load Request Editor.
Options
- Perform Load
- Select this check box to run the loader immediately
after file conversion processing is complete.
Note: You can run the
loader immediately only if you select IXF file type.
If you clear this check box, or select ASCII file types,
the Load Process prepares the data in the appropriate format and 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. Refer to your DB2 documentation.
- Use Named Pipe
- This option is only available when the file type is Teradata ASCII,
Teradata ACSII Delimited, or IXF. See DB Alias Tab - Teradata, Load Request Editor for information on loading to Teradata.
- Delete files if Successful
- Select this check box to delete the data files after
the loader completes successfully. If you select this check box, the
Fast Load Confirmation feature is unavailable. This option is unavailable
if you selected Use Named Pipe.
Note: Fast Load
Confirmation reduces processing time when you run a Load Request that
has run before. See
Run a Load Request for
information.
- Delete files if Failure
- Select to delete the data files if the loader does
not complete successfully. If you select this check box, the Fast
Load Confirmation feature is unavailable. This option is unavailable
if you selected Use Named Pipe.This option
is unavailable if you selected Use Named Pipe.
- Load When Source Is Empty
- Select this check box to perform the load if a table to be loaded
contains no rows. If you do not select this check box, any tables
in the Extract File containing no rows will be skipped in the load
phase.
To create a clean test environment:
- For an existing target table, use
- Load When Source Is Empty
- an empty source table
- Replace mode
- For a newly created table, use
- Load When Source Is Empty
- an empty source table,
- Insert mode
- Load from remote client
- Select this check box to load using the DB2 LUW remote client option. The data files
are written to a location outside the DBMS server location, read from
the DB2 LUW loader client, and
sent to the DBMS server. If you select this check box, you must supply
a path to the location of the data files. Optim generates a LOAD command with the CLIENT
keyword in the SQL.
Directory Paths
Specify directory paths to the
location for loader files. The files must be stored on a drive that
can be accessed by DB2 and the
client as well as by workstation and network server.
Note: If DB2 is running on a remote server
and is started before Windows connects
to the network drives, you might receive a DB2 error message in the Load Process Report
that indicates the path for the file or device is not valid. The solution
is to restart DB2.
- Work path for data files:
- Specify workstation directory path to the location for temporary
loader files. The files must be stored on a drive that can be accessed
by DB2 and the client as well
as by workstation and network server.
- Server path for data files:
- Specify network server directory paths to the location for temporary
loader files. The files must be stored on a drive that can be accessed
by DB2 and the client as well
as by workstation and network server.
- Server path for temporary files:
- Path for temporary loader files. Optim generates
TEMPFILES PATH clause in the SQL. This is unavailable if Load from
remote client is selected.