The DB2 Loader allows you to load data to any DB2 database configured in the Enterprise Data Integration platform. You need to set up the DB2 Utility on the same machine where you are running the Spectrum server.
- Install the DB2 runtime client with Administrator set up.
- Configure the loader utility as described in the table below.
- 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:
|
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.
|
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.
|
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.
|
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 cancelled. To cancel 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