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=table&order=table&schema=schema_name&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=table
- Required parameter that indicates the table in the database (the
database named in the @database_name parameter)
to read from and write to.
- &order=table
- 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=table 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.
- &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".