Database transports allow you to move data from a database to a
pipeline. To use the database transport, you enter a database transport, using
an Universal Resource Identifer (URI).
Note: The database transport is not supported on IBM DB2® on z/OS®.
The
format for the database transport is:
"type://username:password@database/?table=tablename&order=table&timeout=seconds&concurrency=N"
Note: Because some of the characters used in this transport
are special characters for command line environments (specifically the ? and &
characters), if you specify this transport on a command line, you must enclose
the entire transport in quotes. For example: pipeline -d -t "db2://up@db/?table=tn_source&order=table"
- type://
- Required parameter that indicates the transport method is a database and
specifies the database type.
- Valid values are:
- oci:// for Oracle databases
- mssql:// for Microsoft® SQL databases
- db2:// for DB2 databases
- username:password@database_name
- This parameter is really several parameters: username:password and @database_name.
- username:password: Parameter
that specifies the user login name and corresponding password for the named
database. If your system is not configured to use trusted database or operating
system authentication, you must enter these parameters with the database name
parameter.
- @database_name: Required parameter that specifies
the database name of the database that you are transporting data from. If
your system is configured to use trusted database authentication, you only
need to enter the database name parameter.
- Examples by database:
- Oracle databases configured to use trusted authentication: :@relres_sid (No
need to enter the username:password parameters.)
- Oracle databases not configured to use trusted authentication: rr:passw0rd@relres_sid
- Microsoft SQL databases using trusted connections: @relres_dsn (No
need to enter the username:password parameters.)
- Microsoft SQL databases not configured to use trusted connections: rr:passw0rd@relres
- DB2 databases configured to use trusted authentication: @relres
(No need to enter the username:password parameters.)
- DB2 databases not configured to use trusted authentication: rr:passw0rd@relres
- /?table=tablename
- Required parameter that indicates the table in the database (the database
named in the @database_name parameter) to read from and
write to.
- &order=tablename
- Optional parameter to indicate the order to retrieve and process the data
from the table named.
- This option can be used when there are no prioritization requirements
on the data that needs to be loaded. On some database systems, you can improve
performance when the order in which records are pulled from the table is the
natural order selected by the database. Using this option permits the database
to return the records in the most efficient way, which is often the order
in which the records were inserted.
- For tables to be compatible with the database transport, they need to
conform to the following column layout:
Table 1. Mapping the order of columns in database tables
as part of the database transport URIColumn |
Type |
Description/Value |
Index |
LOAD_SEQ |
INTEGER, NOT NULL |
Indicates the priority of the message. Valid values
are 0, 1, 2, and 3:- 0 indicates critical priority
- 1 indicates high priority
- 2 indicates normal priority
- 3 indicates low priority
|
Non-unique on LOAD_SEQ, only if not specifying the &order=tablename parameter |
OWNER |
VARCHAR 256 |
The pipeline sets this column to a unique identifier
during processing to prevent other pipelines from duplicating the work. |
Non-unique |
LOCK_TIME |
INTEGER 64-BIT |
Set this to indicate the time (in epoch milliseconds)
that the record was locked. |
(None) |
MSG |
BLOB or VARCHAR (if maximum XML size, then this is always
smaller than VARCHAR size) |
Incoming UMF message. |
(None) |
- &schema=schema_name
- This optional DB2 parameter enables you to specify the name of a custom
DB2 schema.
Note: The DB2 custom schema feature is not
compatible with the reports generator in the Visualizer and the Configuration
Console. If you specify a custom DB2 schema, the Visualizer and Configuration
Console reports will not work.
- &timeout=n
- This optional parameter indicates the number of seconds the pipeline waits
for a response from the database before timing out.
- If you do not specify this parameter, the transport tries three times
to get a response before timing out.
- &concurrency=n
- This optional parameter enables you to specify the number of incoming
records (a positive integer greater than or equal to 0) that the database
transport pulls at one time for pipeline processing. The higher the number,
the more records are pulled for processing.
- For example, a concurrency of 0 indicates do not process records. A concurrency
of 1 indicates process records one-at-a-time. If you do not specify this
parameter, the default number of incoming records pulled at one time is 5,
unless you specify otherwise.
- You should coordinate this setting with the number of pipeline process
threads spawned by the concurrency setting in the pipeline configuration file
or the DEFAULT_CONCURRENCY system parameter group in the Configuration Console.
The pipeline concurrency setting determines the number of simultaneous pipeline
processing threads that begin when a pipeline is started. If your system is
set to spawn multiple pipeline processing threads for each pipeline started,
you might want to increase this transport concurrency setting so that the
pipeline threads are not waiting for records to process.
Examples of database transports
db2://CCadmin:passw0rd@Customers/?table=table2
Using
this database transport example, the system reads incoming records from the
DB2 database named "Customers" and the table named "table2", where
the login name is "CCadmin" and the corresponding password is "passw0rd".