IM InfoSphere Identity Insight, Version 8.0

Database transports

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 URI
Column 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".



Feedback

Last updated: 2011