Use the DB Alias tab in the Load
Request Editor to define the parameters needed to perform
the Load Process for the DB2® loader.
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
- Insert
source rows into the destination tables. If primary key values match,
a source row is inserted into the appropriate Exception table, if
any is specified, or the table status becomes Check Pending. Refer
to the IBM® DB2 documentation for additional information
about Check Pending status.
- Replace
- Clear and replace all rows in the destination tables
with 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
You can use an exception table
to record each row that violates unique index or primary key rules.
Each exception table includes a copy of the row with a timestamp column
and a description column containing 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.
Archive 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. However,
use care to ensure that the names do not match names of 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 from the menu 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, Archive 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.
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. Refer to the Installation and Configuration Guide for complete information.
- 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 IBM 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.
- 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 Archive File containing no rows will be skipped in the load
phase. Using load with an empty source table deletes rows from the
target table, resulting in a clean test environment.
- 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.