DB Alias Tab for DB2 for Linux, UNIX, and Windows

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.

Load Request Editor with DB alias for DB2 for Linux, UNIX

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:
Replace Options dialog
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 Tools > Edit Exception Table Map from the menu to display the Exception Table Mapping dialog.

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.

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.

Select Copy Option dialog
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.


Feedback