DB2 Loader

The DB2 Loader allows you to load data to any DB2 database configured in the Spectrum Data Integration platform. You need to set up the DB2 Utility on the same machine where you are running the Spectrum server.

Perform these steps:
  1. Install the DB2 runtime client with Administrator set up.
  2. Configure the loader utility as described in the table below.
  3. Start the Spectrum server.
    Note: In case the Spectrum server was already running when you started the configuration, you will need to restart the server for the configuration to take effect.
Option Name Description

Connection

Select the database connection you want to use. Your choices vary depending on what connections are defined in the Connection Manager of Spectrum Management Console. If you need to make a new database connection, or modify or delete an existing database connection, click Manage Connections.

If you are adding or modifying a database connection, complete these fields:
Connection name
Enter a name for the connection. The name can be anything you choose.
Database driver
Select the appropriate database type.
Connection options
Specify the host, port, instance, user name, and password to use to connect to the database.
Table/View After selecting a connection, specify the table or view to write to. Click the browse button ([...]) to go to the table or view that you want to use, or click Create Table to create a new table in the database.
Database/Alias

This is a variable that catalogues the DB2 server and database.

To catalog the DB2 server

Use the DB2 command line processor on spectrum server machine and enter the command: 

CATALOG TCPIP NODE <nodename> REMOTE <hostname> SERVER <port>
where:
nodename: name of connection

hostname: TCP/IP name of the DB2 server machine

port: server port

To catalog the database

Use the command: 

CATALOG DATABASE <databasename> AS <local_database_alias> AT NODE <nodename>
where:
databasename: Name of the database on the DB2 server

local_database_alias: Local Name given to the database while connectiong from the server machine

nodename: Name used in the previous CATALOG TCP/IP command
Stage fields This column lists the field names used in the dataflow. You cannot modify these field names.
Types This column lists the data type of each field.

Runtime Tab

Option Name Description
Load method Indicates the mode of writing data into a DB2 table.
Insert
Inserts the loaded data into the table, while the existing table data remains unchanged.
Replace
Inserts the loaded data into the table after deleting all existing data from it.

The table schema and index definitions remain unchanged.

Restart
Restarts the data load, in case the previous load attempt was interrupted.
Non-recoverable Indicates if this load transaction is non-recoverable.

If you select this option, the load transaction is marked as non-recoverable. Table spaces are not put into the Backup Pending state after the load, nor is a copy of the loaded data made during the load. Hence, a non-recoverable transaction cannot be recovered in the event of a data load failure, even if a rollforward is attempted later.

If you select this option, you cannot recover from the transaction even if you use the DB2 rollforward utility because the utility skips such a non-recoverable transaction, and the table is marked as "invalid". In addition, subsequent transactions against the table are also ignored by rollforward.

To restore a table that contains non-recoverable transactions, you must use either a tablespace-level backup or a full backup taken at a commit point following the non-recoverable load.

Note: Do not select this option if the data contains Datalink columns that have the File Link Control attribute present in them.
CPU The number of parallel threads that the load utility can generate and sustain for loading, parsing and formatting the records, while building table objects in each database partition.
Disk The number of parallel threads that the load utility can generate and sustain for writing data to table space containers.
Indexing Mode Indicates the mode of handling of indexes by the load utility.
Autoselect
The load utility decides whether to apply Rebuild or Incremental mode, based on the amount of data and the depth of the index tree.
Rebuild
All indexes are rebuilt.
Incremental
New data is added to the existing indexes.

This mode can be applied only if the index object is valid and accessible at the start of a load operation.

Note: Incremental indexing is not supported if ALL of these conditions hold true:
  1. The Load Copy option is specified (logretain or userexit is enabled).
  2. The table resides in a DMS table space.
  3. The index object resides in a table space that is shared by other table objects belonging to the table being loaded.
To bypass this limitation, place indexes in separate table spaces.
Deferred
The load utility does not attempt creating an index. Existing indexes are marked for refresh.
Note: Index construction requires more time in Deferred mode than in Rebuild mode. Hence, while performing multiple load operations, allow the last load operation to rebuild all indexes instead of rebuilding indexes at the first access by a non-load operation.
Note: This mode is supported only for tables with non-unique indexes.
Fast Parse Indicates whether syntactical validation on column values must be left out, thus enhancing performance.

If checked, any syntactical errors in the data are ignored in favor of optimized performance.

For example, if a String value 12wxvg56 is encountered in a field mapped to an integer column in an ASCII file, the load utility should normally generate a syntax error. But if Fast Parse is selected, the syntax error is ignored, and a random number is loaded into the integer field.

Note: Ensure you use this option only with correct and clean data.
Schema Name The schema in which the exception tables are stored.
Table Name The exception table into which those rows are copied in which some error is encountered while loading.
Log file folder The path of the directory in which the log files are to be stored.

A log file contains a list of the database load transactions run by a DB Loader stage in one load session.

Click the ellipses button (...) to specify the desired directory for log files.
Bad file folder The path of the directory on the DB2 server in which the bad files are to be stored.

A bad file contains a list of the records that a DB Loader stage fails to load into the database.

Click the ellipses button (...) to specify the desired directory for bad files.
Maximum errors allowed The maximum number of errors allowed before a load operation is aborted.

To abort a load operation as soon as the first error is encountered, set the value of this field to 0.

Note: A maximum of 32767 errors are allowed.
Parallelism
A DB2 database can be divided into multiple partitions by cloning the environment onto different physical nodes.

Separate database requests for data fetch and update are automatically divided amongst the different partitions and run in parallel for optimized performance.

Exception Handling
A DB2 database allows you to record the errors and exceptions encountered while running queries and procedures, and also handle them appropriately.

For this, a DB2 database provides specific exception tables and schema which store the source as well as the log traces of each database exception.

To use the Exception table, ensure:
  • DB2 runtime client is version 10.5 or above
  • Service pack version is 7 or above