When to use database connection pools

This section describes some of the situations in which you might want to use database connection pools.

Performing routing

The logic of a business process may dictate that a business object must be routed to different destination applications depending on the value in one or more fields in the business object.

For instance, a site might store and process customer entities in different applications depending on the value in an attribute such as CustomerType. The collaboration template would need to retrieve and evaluate the value in that attribute and make a decision as to which destination application to send the business object to based on the value. Although this can be accomplished with control flow structures in Java, the pairings between the values and destination applications are then hard-coded in the collaboration template. If they need to be changed because of a change in procedure, or added to because a new value and application are introduced into the interface, then the collaboration template must be modified and re-compiled, it must be re-deployed, and so forth. A much more flexible implementation stores the pairings of values and destination applications in a database table. To implement this sort of approach, do the following:

  1. Create a database table which has one column to store the routing values and another column to store some information that associates the appropriate destination application with the routing value. Table 14 is an example of such a table.

    Table 14. Routing table example

    Routing value Destination application value
    Customer AppA
    Federal AppB
    Reseller AppC
    Academic AppD
  2. Create a database connection pool and database connection in System Manager as described in Creating database connection pools and database connections.
  3. In the collaboration template design the logic to do the following:
    1. Retrieve the value in the attribute to be used for routing and store the value in a variable.
    2. Obtain a connection to the database.
    3. Execute a SQL query that retrieves the value in the column that stores the destination application values where the value in the column that stores routing values is equal to the value stored in the variable in the collaboration template.
    4. Use a decision node to cause the collaboration logic to branch depending on the value returned from the database table. The different branches should lead to different service call nodes responsible for sending the business object out to the appropriate connector and thereby sending it out to the appropriate destination application.

Performing lookups in database tables

You might need to translate one value into another by looking up its equivalent in a table. Frequently you perform these operations by implementing lookup relationships, though using a lookup relationship does not always make sense. Lookup relationships are designed primarily for situations where each application involved in an interface needs its own way of representing a piece of data--a participant is created for each application and the lookup relationship itself connects all the participants much in the same way that the integration broker connects the applications. Sometimes you have a need to transform a value into one of several other values, but there is not the need to maintain a separate representation of that data for each application involved in the interface. In such a case you should create a table in the repository to store the associated values and then use a database connection and SQL select statements to retrieve the desired value.

Furthermore, the API provided for lookup relationships makes it very easy to abstract related data across applications, but does not make it easy to perform more complicated queries. The lookup relationship API is designed to take a piece of data and return the key value that the data shares with the other pieces of application data in the relationship, or to take a key value and return a particular piece of data associated with it. The lookup relationship API cannot return multiple column values, however, or execute stored procedures, which the APIs of the CwDBConnection class are able to do.

You can also satisfy this requirement with Java code rather than with a database query, by using control structures such as "if/else" and "switch/case" statements. Consider the following advantages and disadvantages of the different approaches and make the appropriate choice based on the situation:

Persisting information

Some customers want to make information about the operation of the business integration system persistent by storing that information in a database so that it can be referenced for problem resolution or historical analysis.

To satisfy this requirement, do the following:

  1. Create a database table with as many columns as necessary to store the desired data.
  2. Create a database connection pool and database connection in System Manager as described in Creating database connection pools and database connections.
  3. In the collaboration template design the logic to do obtain a connection to the database and execute a SQL query that inserts the desired data into the appropriate column.

Typically this requirement involves persisting information contained in the business object being processed by the collaboration (such as the primary key of the entity being processed), or information about the system itself (such as the successful processing of a business object request). As of release 4.2.0, you can persist business object data like primary key values by using the Business Object Probe feature; for more information, see the Collaboration Development Guide. As of release 4.2.0, you can persist some information about the system by using the Persistent Monitoring feature; for more information, see the System Administration Guide.

Copyright IBM Corp. 1997, 2004