IBM E02HMLL-I Implementation Guide - Page 158

Table 14. Routing table example (continued) Routing value Destination application value 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" on page 148. 3. In the collaboration template design the logic to do the following: a. Retrieve the value in the attribute to be used for routing and store the value in a variable. b. Obtain a connection to the database. c. 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. d. 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: 146 IBM WebSphere Business Integration Server Express and Express Plus: System Implementation Guide