DB Alias Tab – DB2, Load Request Editor

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.
Load Request Editor with DB Alias - DB2 as the active tab. The elements of the DB Alias - DB2 tab are described as follows.

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:
Replace Options 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 Tools > Edit Exception Table Map 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.

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.

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.

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. 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.