Step 1 - Defining the database transport

A database transport represents the connection to a database server. For the sample trigger, the data will be propagated to a relational database. The output format of the data will be a database insert. This section describes how to define a transport that will provide delivery of data to a relational database. The transport must be available before the trigger is created. For a comprehensive set of tasks when using the transport feature, see to Enterprise connectivity.

Supported transport protocols

Nodes support specific transport protocols that are turned on using a license activation key. The transport that is created in this section is based on the IBM DB2 database product. You can also setup a transport to take advantage the other supported relational database products.

Assumptions

Information in this chapter assumes the following:

  • The Workbench was previously started and you have logged on.
  • There is a database product installed and configured on the network.
  • Your database administrator created a database and populated the database with tables. The administrator gave you the name of one or more databases.
  • Your database administrator provided you with the IP address of the computer where the database resides.
  • You have the user ID and password needed to log on to the database.
  • You have some understanding of relational databases and SQL.

Procedures

You can create a transport that will be able to generate a value for a newly inserted row in a database table.

  1. From the Workbench left pane, expand the node that where you want to define the database transport.
  2. Expand Enterprise, right-click the Transports icon to display its pop-up menu.

  3. Select New.
    The Transport default window appears.
  4. In the Name parameter, enter MyDatabaseTransport. This will be the unique name for the transport. You will not be able to type invalid characters. For example, spaces are not allowed.
  5. Next to Type, select the down-arrow, and then select DB.

    The Transport window changes to accommodate the definition for a DB2 database transport.

The Transport window has several tabs. Each tab and its parameters will be described separately.

Specifying connection parameters

The Parameters tab provides parameters that let you add communication parameters so that the transport is able to connect to the database.

  1. In the DB Name parameter, enter DWDB. This is the name of the database to use. The database must exist and be populated with tables.
  2. Use the DB Type down arrow to display a list of installed databases.

  3. In the Host parameter, enter the IP address of the computer where the database server is installed.
  4. In the Port parameter, accept the default value of 50000.
  5. In the User and Password parameters, enter the user ID and password given to you by the database administrator. These are the user ID and password to access the database.

The bottom of the Parameters tab provides these optional features.

Select the Extended Attributes button to display the Extended Attributes window. Each database has published a list of properties that can be specified to alter the connection settings when connecting to the database server. You can use the Extended Attributes window to specify these settings. Typically, you should only modify these values if required by your database administrator.
A connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. The Connection Pool Size value is the maximum number of new connections to create and store in the pool. The default value is 1.
By default, a transport is down until it processes a transaction. Select the Load transport at initialization check box to have the transport connect to the host as soon as the node starts up (or immediately after leaving store and forward). Thus, when the first transaction comes in, processing is faster because the transaction does not have to initialize the transport (the connection is already established).

Adding timeout values

The Timeout tab provides values that affect connection times.


Accept the default value for each Timeout parameter. The following describes those default values:

Connection (seconds)

The Connection timeout parameter defaults to 10 seconds and specifies the length of time the system will try to connect to a target computer (where the associated database server is running). If the connection is not made in the specified time period, an error message is sent to the exception log.

This connection is tested when you select Validate.

Execution (seconds)

The Execution timeout parameter defaults to 5 seconds and specifies the amount of time the Transaction Server should wait (once the connection is made) for a transaction to complete. The time value should be the outer limit for how long you expect a typical transaction to take using a transport.

Inactivity (seconds)

The Inactivity timeout parameter specifies the maximum number of seconds of inactivity the Transaction Server will wait before disconnecting from the database.

Turning on store and forward

The Store & Forward tab provides values for store and forward queues.

Accept the default value for each Store & Forward parameter. The following describes those default values:

Store & Forward

Select this check box to turn on store and forward for this transport. The Store and Forward queue for this transport will be based on the TTL (time to live), Max Storage, and On overflow parameters.

TTL (sec)

TTL means time-to-live in seconds which is how long to retain messages that are in the store and forward queue.

Max Storage (MB)

The maximum size of the store and forward queue for this transport. The only limit is the amount of disk space on the node.

On overflow

the options are:

  • Discard new message - Do not accept any new messages once the store and forward queue reaches its maximum size.
  • Delete oldest message - Have the oldest messages discarded in a first in/first out (FIFO) manner when the store and forward queue becomes full.
Process queue before leaving S/F

When the Process queue before leaving S/F check box is selected, and the connection is re-established, data is delivered in the order in which it entered the store and forward queue. When the Process queue before leaving S/F check box is not selected, as soon as the connection is re-established, new data is not added to the store and forward queue. Instead, is sent to the database server.

Turning on the map log

This tab provides a Mapping Log check box that turns on transaction logging for the transport. The content of every outbound transaction for the transport will be recorded in a log file.


Select the Mapping Log check box, and then accept the default values for the remaining parameters. Their descriptions follow:

Log Size (MB)

The value is the maximum size of the log file for this transport. Once the log file reaches that size, a new file is created and named as Name_1.log.

Message Size (byte)

The value controls the amount of the transaction recorded in the log file for this transport. For this example, accept the default to specify that the transaction message be recorded.

Number of Log Files

The value is the number of log files that can be maintained for this transport. Log files are maintained as numbered logs (1 to 10).

Copy rolled log to staging

When selected, a copy of each log file is made and written to the Staging Browser area when the log file is full and recording rolls over to the next log file.

Validating and saving the transport

Now that you have filled in the appropriate value for each parameter, you must validate the connection, and then save your work.

  1. Select Validate.
    The a connection to the database is made.
  2. A message will tell you whether or not the validation was successful. Select OK.

  3. If no errors are received, select Save.

The new transport is saved to the node and added to the Transports tab.

The Transports tab provides information about transport processing such as whether a transport is operational or in a store and forward mode.

The next step is to define the transport map and define the input that will be used to build the payload at run time. Go to Step 2 - Defining the database insert transport map.