DB2DataWriter
We assume that you have already learned what is described in:
If you want to find the right Writer for your purposes, see Writers Comparison.
Short Summary
DB2DataWriter loads data into DB2 database.
| Component | Data output | Input ports | Output ports | Transformation | Transf. required | Java | CTL |
|---|---|---|---|---|---|---|---|
| DB2DataWriter | database | 0-1 | 0-1 | no | no | no | no |
Abstract
DB2DataWriter loads data into database using DB2 database client. It can read data through the input port or from an input file. If the input port is not connected to any other component, data must be contained in an input file that should be specified in the component. If you connect some other component to the optional output port, it can serve to log the information about errors. DB2 database client must be installed and configured on localhost. Server and database must be cataloged as well.
Icon
Ports
| Port type | Number | Required | Description | Metadata |
|---|---|---|---|---|
| Input | 0 | 1) | Records to be loaded into the database | Any |
| Output | 0 | no | For information about incorrect records | Error Metadata for DB2DataWriter2) |
Legend:
1): If no file containing data for loading (Loader input file) is specified, input port must be connected.
2): Error Metadata cannot use Autofilling Functions.
Table 46.1. Error Metadata for DB2DataWriter
| Field number | Field name | Data type | Description |
|---|---|---|---|
| 0 | <any_name1> | integer | Number of incorrect record (records are numbered starting from 1) |
| 1 | <any_name2> | integer | number of incorrect field (for delimited records), fields are numbered starting from 1 | offset of incorrect field (for fixed-length records) |
| 2 | <any_name3> | string | Error message |
DB2DataWriter Attributes
| Attribute | Req | Description | Possible values |
|---|---|---|---|
| Basic | |||
| File metadata | Metadata of external file. Must only be delimited. Each
column except the last one is followed by an identical, one
char delimiter. The last delimiter following the last column
is \n. Delimiter must not be a part of any
field value. | ||
| Database | yes | Name of the database into which the records should be loaded. | |
| Database table | yes | Name of the database table into which the records should be loaded. | |
| User name | yes | Database user. | |
| Password | yes | Password for database user. | |
| Load mode | Mode of the action performed when loading data. See Load mode for more information. | insert (default) | replace | restart | terminate | |
| Field mapping | 1) | Sequence of individual mappings
($CloverField:=DBField) separated by
semicolon, colon, or pipe. See Mapping of Clover Fields to DB Fields for more
information. | |
| Clover fields | 1) | Sequence of Clover fields separated by semicolon, colon, or pipe. See Mapping of Clover Fields to DB Fields for more information. | |
| DB fields | 1) | Sequence of DB fields separated by semicolon, colon, or pipe. See Mapping of Clover Fields to DB Fields for more information. | |
| Advanced | |||
| Loader input file | 2) | Name of input file to be loaded, including path. See Loader input file for more information. | |
| Parameters | All parameters that can be used as parameters by
load method. These values are contained in
a sequence of pairs of the following form:
key=value, or key only
(if the key value is the boolean
true) separated from each other by
semicolon, colon, or pipe. If the value of any parameter
contains the delimiter as its part, such value must be double
quoted. | ||
| Rejected records URL (on server) | Name of the file, including path, on DB2 server where rejected records will be saved. Must be located in the directory owned by database user. | ||
| Batch file URL | URL of the file where the connect,
load and disconnect
commands for db2 load utility are stored. Normally the batch
file is automatically generated, stored in current directory
and deleted after the load finishes. If the Batch
file URL is specified, component tries to use it as
is (generates it only if it does not exist or if its length is
0) and does not delete it after the load finishes. (It is
reasonable to use this attribute in connection with the
Loader input file attribute, because
batch file contains the name of temporary data file which is
generated at random, if not provided explicitly). Path must
not contain white spaces. | ||
| DB2 command interpreter | Interpreter that should execute script with DB2
commands (connect, load,
disconnect). Its form must be the
following: interpreterName [parameters] ${}
[parameters]. This expression must be replaced by the name of this
script file. | ||
| Use pipe transfer | By default, data from input port is written to a
temporary file and then it is read by the component. If set to
true, on Unix data records received through
the input port are sent to pipe instead of a temporary
file. | false (default) | true | |
| Column delimiter | The first one char field delimiter from File
metadata or the metadata on the input edge (if
File metadata is not specified).
Character used as a delimiter for each column in data file.
Delimiter must not be contained as a part of a field value.
The same delimiter can be set by specifying the value of the
coldel parameter in the
Parameters attribute. If Column
delimiter is set, coldel in
Parameters is ignored. | ||
| Number of skipped records | Number of records to be skipped. By default, no records are skipped. This attribute is applied only if data is received through the input port. Otherwise, it is ignored. | 0 (default) | 1-N | |
| Max number of records | Maximum number of records to be loaded into database.
The same can be set by specifying the value of the
rowcount parameter in the
Parameters attribute. If
rowcount is set in
Parameters, the Max number of
records attribute is ignored. | all (default) | 0-N | |
| Max error count | Maximum number of records after which the load stops.
If some number is set explicitely and when it is reached,
process can continue in RESTART mode. In REPLACE mode, process
continues from the beginning. The same number can be specified
with the help of warningcount in the
Parameters attribute. If
warningcount is specified, Max
error count is ignored. | all (default) | 0-N | |
| Max warning count | Maximum number of printed error messages and/or warnings. | 999 (default) | 0-N | |
| Fail on warnings |
By default, the component fails on errors. Switching the
attribute to true, you can make the component fail on warnings.
Background:
when an underlying
bulk-loader utility finishes with a warning,
it is just logged
to the console. This behavior is sometimes undesirable as warnings from
underlying bulk-loaders may seriously impact further processing. For example,
'Unable to extend table space' may result in not loading all data records to a database;
hence not completing the expected task successfully.
| false (default) | true | |
Legend:
1) See Mapping of Clover Fields to DB Fields for more information about their relation.
2) If input port is not connected, Loader input file must be specified and contain data. See Loader input file for more information.
Advanced Description
Mapping of Clover Fields to DB Fields
If a Field mapping is defined, the value of each Clover field specified in this attribute is inserted to such DB field to whose name this Clover field is assigned in the Field mapping attribute.
Both Clover Fields and DB Fields are Defined
If both Clover fields and DB fields are defined (but Field mapping is not), the value of each Clover field specified in the Clover fields attribute is inserted to such DB field which lies on the same position in the DB fields attribute.
Number of Clover fields and DB fields in both of these attributes must equal to each other. The number of either part must equal to the number of DB fields that are not defined in any other way (by specifying clover fields prefixed by dollar sign, db functions, or constants in the query).
Pattern of Clover fields:
CloverFieldA;...;CloverFieldMPattern of DB fields:
DBFieldA;...;DBFieldMOnly Clover Fields are Defined
If only the Clover fields attribute is defined (but Field mapping and/or DB fields are not), the value of each Clover field specified in the Clover fields attribute is inserted to such DB field whose position in DB table is equal.
Number of Clover fields specified in the Clover fields attribute must equal to the number of DB fields in DB table that are not defined in any other way (by specifying clover fields prefixed by dollar sign, db functions, or constants in the query).
Pattern of Clover fields:
CloverFieldA;...;CloverFieldMMapping is Performed Automatically
If neither Field mapping, Clover fields, nor DB fields are defined, the whole mapping is performed automatically. The value of each Clover field of Metadata is inserted into the same position in DB table.
Number of all Clover fields must equal to the number of DB fields in DB table that are not defined in any other way (by specifying clover fields prefixed by dollar sign, db functions, or constants in the query).
Load mode
insertLoaded data is added to the database table without deleting or changing existing table content.
replaceAll data existing in the database table is deleted and new loaded data is inserted to the table. Neither the table definition nor the index definition are changed.
restartPreviously interrupted load operation is restarted. The load operation automatically continues from the last consistency point in the load, build, or delete phase.
terminatePreviously interrupted load operation is terminated and rolled back to the moment when it started even if consistency points had been passed.
Loader input file
Loader input file is the name of input file
with data to be loaded, including its path. Normally this file is a
temporary storage for data to be passed to dbload utility unless
named pipe is used instead. Remember that DB2
client must be installed and configured on localhost (see
DB2 client setup overview).
Server and
database must be cataloged as well.
If it is not set, a loader file is created in Clover or OS temporary directory (on Windows) or
named pipeis used instead of temporary file (on Unix). The file is deleted after the load finishes.If it is set, specified file is created. It is not deleted after data is loaded and it is overwritten on each graph run.
If input port is not connected, the file must exist, must be specified and must contain data that should be loaded into database. It is not deleted nor overwritten.