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. To do this, perform these steps:

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 a DB2 connection. The dropdown displays all the connections you have configured. To configure a new connection or edit any existing connections, click the Manage Connections link. It takes you to the Connections page in the Spectrum Management Console. For details on configuring new connections, see Connections. Note: Click the Refresh icon to update any modification, deletion or addition to your list of connections.
Table	Specify the table to which you want to write the data. Click the Browse button ([...]). The Select Table pop-up window is displayed, which shows a list of the tables and views. Choose the required table. Note: You can also use the search text box on the top of the pop-up window to narrow down your search. Click Select. The fields of the selected table are displayed in the Fields section on the right side of the page. These are the details displayed: Database Fields: This column lists the field names in the database. You cannot modify these field names. Stage Fields: This column lists the field names used in the data flow. You cannot modify these field names Types: This column lists the data type of each field.
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
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 the 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 Yes: 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. 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 Yes if the data contains Datalink columns that have the File Link Control attribute in them. Note: Default is No.
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.
CPU	Specify 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	Specify the number of parallel threads that the load utility can generate and sustain for writing data to table space containers.
Fast Parse	Indicates whether syntactical validation on column values must be left out, thus enhancing performance. If Yes, 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 set to Yes, 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.
Indexing Mode	Indicates the mode of index handling 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: The Load Copy option is specified (`logretain` or `userexit` is enabled). The table resides in a DMS table space. 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.
Exception Handling A DB2 database allows you to record the errors and exceptions encountered while running queries and procedures, and also handle those 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
Schema Name	The schema in which the exception tables are stored.
Table Name	The exception table into which the rows that encountered some error while loading are copied.
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 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.
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.