Integrating cloud applications with WebSphere Cast Iron Cloud Integration

Learn how WebSphere Cast Iron Cloud Integration enables you to synchronize accounts between a database (such as mySQL) and a cloud application (such as Oracle CRM on Demand), and how this integration between different business applications can be done within days and without coding.

Lars Besselmann-Hamandouche, WebSphere Client Technical Professional, IBM

Photo of Lars BesselmannLars Besselmann-Hamandouche has been a member of the WebSphere Client Technical Professional Team in Duesseldorf, Germany for more than 10 years, working with clients from all industries. He joined IBM as an IBM Systems Engineer in 1994, and became a WebSphere Technical Professional in 2000. He is an IBM and The Open Group Master Certified IT Specialist, and he holds a degree in mathematics from the University of Dortmund. His current focus is on application infrastructure and application integration. You can contact Lars at Lars.Besselmann@de.ibm.com.



13 April 2011

Also available in Chinese Russian Japanese

Introduction

Develop skills on this topic

This content is part of progressive knowledge paths for advancing your skills. See:

Cloud applications are getting increasingly popular, and as a result, many companies end up in a hybrid world of public clouds, private clouds, and on-premise applications. This flexibility in application platforms brings the challenge of integrating cloud solutions with your existing systems and applications. The article shows you how IBM® WebSphere® Cast Iron Cloud Integration (hereafter called WebSphere Cast Iron) can greatly simplify the integration between on-premise applications and a cloud applications. The example in this article uses a mySQL database, Oracle® CRM on Demand, and WebSphere Cast Iron Cloud Integration V6 (V5 works also).

About WebSphere Cast Iron Cloud Integration

WebSphere Cast Iron enables you to easily and rapidly integrate business applications hosted on public and/or private clouds with on-premise applications. WebSphere Cast Iron capabilities include:

Connectivity
WebSphere Cast Iron provides connectivity to different kinds of endpoints, including flat files, databases, messaging systems, and Web services, as well as applications such as SAP, Siebel, JD Edwards, and Lotus Domino.
Transformation
WebSphere Cast Iron supports protocol and data transformation.
Workflow
WebSphere Cast Iron lets you use different workflow elements such as if-activities and loops.
Management
WebSphere Cast Iron provides powerful management interfaces, such as the easy-to-use Web Management Console.

The workflow that defines the integration is called orchestration, and the different tasks within an orchestration are called activities. To enable rapid integration, WebSphere Cast Iron includes a development tool, the WebSphere Cast Iron Studio, which you can either run on your development machine, or access via a browser as part of the cloud offering. It lets you design and build your orchestration graphically via drag and drop, so that you can do configurations without coding. To speed up development and promote reuse, WebSphere Cast Iron provides an online repository of template integration projects (TIPs) for hundreds of different integration scenarios. After you create a project, you can deploy it in one of the following form factors, each of which provides the same functionality:

WebSphere DataPower Cast Iron Appliance XH40
A self-contained physical appliance.
WebSphere Cast Iron Hypervisor Edition
A virtual appliance that can be installed on your existing virtualization platform.
Cast Iron Cloud2
A multi-tenant cloud service to design, run, and manage all of your integrations in the cloud.

For more information on WebSphere Cast Iron, see Resources at the bottom of the article.

Integration scenario

You will use WebSphere Cast Iron to synchronize account data between two endpoints. The source of the data is an Oracle MySQL database, and the target is Oracle CRM on Demand. New accounts will be created within Oracle CRM on Demand, and existing accounts will be updated. The synchronization will be triggered by an HTTP request or by the WebSphere Cast Iron Scheduler. Endpoint configurations will be stored in configuration properties, enabling you to change them later on in the runtime environment via the Web management console and without a new deployment.

MySQL database

The account data is stored in the MySQL database mysqldb in the table Accounts. ACCOUNT_NAME and ACCOUNT_NUMBER are the primary keys and are of type varchar(). The country fields for billing and shipping contain a two-letter country code such as DE for Germany or US for the United States. The state fields for billing and shipping contain a two-letter state code such as 01 in Germany or CA in the United States. For more details about the database structure and a sample account, see Listing 2. SQL statement to create a sample account at the bottom of the article.

Template repository

WebSphere Cast Iron provides a template integration project repository which enables you to reuse existing assets, as well as share your assets with the community. Take a look at the available orchestration templates to see if there is an appropriate one. The description below shows how to find a template using WebSphere Cast Iron Studio. If you have access to the Cast Iron Cloud2 service, log into Cloud2, click Create to access the template repository, and follow the instructions below starting with Step 4. Differences are noted in parentheses ().

  1. Start WebSphere Cast Iron Studio.
  2. On the entry panel, click the button next to Find solutions (or select Solutions => Search for TIPs) to access the template repository. Enter your credentials to connect to the repository.
  3. Click on Filter to search by endpoint.
  4. Select as target endpoint Oracle CRM on Demand and click Query (click Search for Cloud2). You will get back a list of available template integration projects:
    Figure 1. Extract of the list of templates for Oracle CRM on Demand
    Extract of the list of templates for Oracle CRM on Demand
  5. As you can see, there is no template integration project available for synchronizing accounts from a MySQL database to Oracle CRM on Demand. But you can use a template integration project that is similar. Click on the template integration project T0120. It provides an account synchronization between Oracle EBS and Oracle CRM on Demand. As you may know, Oracle EBS is a database-based system, so the scenario is close to the use case here.
  6. Download the project T0120: click Download and specify on request where to store the project. (For Cloud2, click Create a project based on this template to start Studio in a browser window).
  7. You get a warning that the project was created with an older version of Studio. Click Yes and WebSphere Cast Iron Studio migrates the project automatically to V6.

Now that you have created a project based on the template, you need to adjust it.

Template wizard

The Template Integration Project Configuration wizard starts automatically and guides you through the steps to customize a project. You will be able to configure your endpoint configurations and adjust the account mapping.

  1. The Introduction panel provides the description of the template and the configuration steps:
    Figure 2. Introduction panel for template T0120
    Introduction panel for template T0120
    Read the instructions and then click Next.
  2. On the panel Oracle EBS 11i DB Credentials, adjust the configuration properties to fit to your MySQL setup:
    1. Click into the field Oracle_DatabaseName and rename it to MySQL_DatabaseName. Click into the value field and adjust it to your environment.
    2. Do the same for the other four configuration properties (the default port for MySQL is 3306). Then click Next.
  3. On the panel Connect to Oracle EBS 11i DB, define the MySQL database:
    1. Change the Database Type to MySQL.
    2. Click on the sign right of the field Port to replace the static port value 3306 with the configuration property MySQL_Port. Do the same for the Password field.
    3. Check if you have to change additional settings. Your final settings should look similar to Figure 3:
      Figure 3. MySQL database configuration panel
      MySQL database configuration panel
      Validate the settings: click Test connection on the bottom of the page. During the test, WebSphere Cast Iron Studio will contact the database and check the credentials. You should get a message You've successfully connected to the endpoint.
    4. You have finished reconfiguring the database endpoint. Click Next.
  4. On the panel Oracle CRM on Demand Credentials, change the CRM credentials. Oracle has a number of different CRM OD pods, each indicated by three letters (here apa). You can find your pod name by looking at your Oracle CRM on Demand login URL. Adjust the properties here.
    1. Change the value of the configuration property OracleCRMLoginHost to fit to your pod (In the template, it is set to ausomxapa.crmondemand.com ). Leave the port as 443.
    2. Change the values for User and Password, and then click Next.
  5. On the panel Connect to Oracle CRM on Demand, configure additional details if required. Validate the settings: click Test connection, then click Next:
    Figure 4. Oracle CRM on Demand configuration panel
    Oracle CRM on Demand configuration panel
  6. On the panel Configure CRM on Demand Account Web service, you can configure the account Web service provided by Oracle CRM on Demand, including details such as name of the service, port, login mechanism, and location. Modify the value in the Location field from https://secure-ausomxapa.crmondemand.com/Services/Integration to fit your Oracle pod name. You have finished configuring the second endpoint -- click Next.
  7. On the panel Map account, look at the data mapping between an EBS account and an Oracle CRM on Demand account. As you can see the map contains a lot of links. Blue squares on a link represent one or more functions used for data transformation. Fortunately, the mapping has already been done and you just have to adjust it:
    Figure 5. Account Mapping
    Account Mapping
    1. In the map, the element ExternalSystemID is set with the content of the element CUST_ACCOUNT_ID. To define ExternalSystemID in another way, adjust the mapping. For example, to define ExternalSystemID as a concatenation of the two elements ACCOUNT_NUMBER and ACCOUNT_NAME, follow these steps:
      1. Delete the existing mapping to the field ExternalSystemID.
      2. Click on function symbol left of the Back button to show the functions palette.
      3. Drag and drop the function Concatenate in the middle of the mapping.
      4. Create a link from ACCOUNT_NUMBER to the function Concatenate.
      5. Create a link from ACCOUNT_NAME to the function Concatenate.
      6. Create a link from the function Concatenate to ExternalSystemID.
      7. Right-click on the function and select Apply Function Graph to finalize the mapping.
    2. The map transforms a two-letter country code to the long version using a lookup table named CountryLookup. Scroll down on the functions palette to find the definition of CountryLookup. Right-click on Edit and you will find out that the lookup table only contains a mapping for the country code US. To map other country codes such as DE for Germany, enhance the lookup table with your country mapping.
    3. To verify the mapping, click Test and provide the test data to be mapped.
    4. Finally click OK and then Next to continue the wizard.
    5. You are now on the panel Verify orchestration. If your MySQL database has the same table structure as the EBS database and accepts the SQL query, you are done now with the configuration and can test the orchestration as part of the wizard within Studio. But in the sample here, the table structure is different and you have to adjust the SQL query. Do this outside of the wizard because it was not designed for that step. Therefore click Next to continue the wizard.
  8. On the next panel you can publish the orchestration after successfully testing in a runtime environment. But publishing does not make any sense at the moment, so click Finish.
  9. Now you can provide a rating. Click Close. You can restart the wizard later to provide a rating.
  10. Save the project with an appropriate name.

Now take a look at the project and the orchestration to see what you have configured within the wizard.

The Orchestration

First, look at the project. In the right upper corner on the Project tab, you can see the list of project assets such as orchestrations, endpoints, transformations, and XML schemas. Take a look at the list of endpoints. As you replaced the EBS database with the MySQL database, rename the database endpoint from OracleEBS11iDB to MySQLDB or some other meaningful name by right-clicking on the endpoint.

Now double-click to open the orchestration SyncAccounts (if not already open) and look at the orchestration. The final orchestration is shown in Figure 11 at the bottom of the article. Here a short description of the main steps of the orchestration:

  1. Trigger and database access: There are two ways to trigger the orchestration:
    • The Receive Request activity expects an HTTP request with a timestamp in the body.
    • The Schedule Job activity triggers the job on a timely basis.
    After the event occurrs, the Execute Query activity is called. This activity sends a query to the database and returns none, one or more account datasets depending on the query. Comment: In the figure below the name of the activity has already been renamed. You will do so in the next section.
    Figure 6. Trigger and database access
    Triggers for the orchestration
  2. Log in to Oracle CRM on Demand: The orchestration checks if there are any updates and if so, logs in to Oracle CRM on Demand. The For each loop provides an easy way to work with the account datasets one by one:
    Figure 7. Login to Oracle CRM on Demand
    Login to Oracle CRM on Demand
  3. Update accounts in Oracle CRM on Demand: Within the For each loop, the activity Map Account maps the EBS dataset to the Oracle CRM on Demand dataset. This is the mapping you changed within the template integration project wizard in Figure 5 above. Then the account within Oracle CRM on Demand is updated using the Web service activity Upsert Account, which takes one account dataset and does either an insert or an update on the account:
    Figure 8. Mapping and Upsert Account
    Mapping and Upsert Account
  4. Log out from Oracle CRM on Demand: After all accounts have been upserted, the orchestration logs out from Oracle CRM on Demand.

Now you should have a better understanding what the orchestration does. Before you can test the orchestration, you have to adjust the database query.

Modifying the SQL query

The EBS database has a database structure different from the one used for the MySQL database. You will now replace the original query with one that fits your database, and then adjust the mapping to the new result set:

  1. In the orchestration, click on the activity Execute Query -- Get Modified EBS Accounts. At the lower left, a section named Checklist describes all steps required to configure the activity.
  2. Click on the step Map outputs and take a look at the map. It defines how the data returned by the query activity will be transformed before it will be stored in the variable EBS11iAccounts. Don't change anything here right now -- you will come back to the output map again soon.
  3. Click Summary and change the activity name to Get MySQL Accounts.
  4. Click Configure => Enter Query to change the query.
    1. The query for EBS is complex and returns accounts depending on a timestamp. For initial testing with your mySQL database, use an easy query such as SELECT * from accounts. Click View columns if you don't know exactly what your database looks like.
    2. Change the parameter Max Rows from 50 to 1, which is sufficient for testing.
    3. Click Validate query to validate the query against the database. In addition, it will update the result set structure returned by the query.
  5. Skip the step Map inputs as you do not have any input variable in the query.
  6. Click Map outputs. As you can see, the left side of the mapping has been updated to reflect the modified result set. Only the still valid mapping links have been kept. Add some mapping links:
    1. Create a link from the element row on the left to the element row on the right, so that Studio will create a mapping for all elements with the same or similar name for source and target.
    2. Create a link from the element BILLINGPOSTALCODE to BILLING_PC. Do the same for the shipping postal code.
    3. If you work with accounts from countries other than US, you have some additional stuff to do. Oracle CRM on Demand accepts only values for a state field for specific countries such as the U.S. To avoid problems for non-U.S. countries, remove the mapping from BILLINGSTATE to BILLING_STATE. To not lose the state information for U.S. accounts, define a custom function that returns a valid state value based on the country.
      1. Click on the tab Functions and create a custom function with name MapStates and return type string. As parameters enter InputState and InputCountry, both of type string. As function content enter: if (InputCountry == "US") {return InputState} return ""; This will return an empty string for non-US countries. Click Compile to validate the function, then click Finish.
      2. Drag and drop the function MapStates to the middle of the output map.
      3. Create a link from BILLINGSTATE to the function MapStates.
      4. Create a link from BILLINGCOUNTRY to the function MapStates.
      5. The order of linking the sources to the function is important for the mapping, so double-click on the function MapStates to verify the field/parameter mapping.
      6. Create a link from the function MapStates to BILLING_STATE.
      7. Right-click on the custom function and select Apply Function Graph.
      8. Repeat Steps 2-7 also for the mapping from SHIPPINGSTATE to SHIPPING_STATE.
    4. Compare your final mapping with Figure 9:
      Figure 9. Query output mapping
      Query output mapping
  7. Now that you have done all required adjustments to the orchestration check, if it is still valid: use the green check mark, or select Orchestration => Validate. Then save the orchestration.

Testing the orchestration within WebSphere Cast Iron Studio

As shown in Figure 6 above, there are two ways to trigger the orchestration:

  • The Receive Request activity expects an HTTP request with a timestamp in the body. The URL to use is defined via the configuration property HTTPURL and has the value AccountsSync (see Project => Configuration Properties).
  • The Schedule Job activity triggers the job on a timely basis, here once daily. To test the orchestration via the Scheduler, click the Schedule Job activity and change the configuration to Once every 5 seconds.

To test the orchestration in WebSphere Cast Iron Studio, follow these steps:

  1. Check in the Oracle CRM on Demand portal which accounts already exist.
  2. Click in WebSphere Cast Iron Studio on the Verify tab and start the orchestration by clicking on the green arrow (or you can select Orchestration => Start Orchestration). In Studio, the orchestration will stop after one execution.
  3. Trigger the orchestration:
    1. If you use the Scheduler, then the orchestration is triggered automatically after some time.
    2. To send an HTTP request, use the HTTP Post Utility shipped with WebSphere Cast Iron Studio (select Tools => HTTP Post Utility). Enter as URL http://yourhost/AccountsSync and as body a timestamp such as 2011-01-13 14:00:00. Then click Submit to send the request.
  4. In WebSphere Cast Iron Studio, look at the execution flow of the orchestration. Expand the sections for the different activities and investigate details by clicking on the variables. Compare your execution flow with the one below:
    Figure 10. Orchestration execution flow
    Orchestration execution flow
    For each of the orchestration activities, you can see the input and output values for all variables before and after the mapping. This level of detail makes it is easy to analyze problems such as mapping issues, and to check if the orchestration works as expected.
  5. Finally, switch to the Oracle CRM on Demand page and check if an update occurred.

After successfully testing the orchestration in WebSphere Cast Iron Studio, you can deploy it to the runtime.

Deploying the orchestration to your runtime environment

Your runtime environment can be one of the following form factors:

  • WebSphere DataPower Cast Iron Appliance XH40
  • WebSphere Cast Iron Hypervisor Edition
  • Cast Iron Cloud2

If you are using Cast Iron Cloud2 and have your MySQL database installed behind a firewall, then use the Cast Iron Secure Connector to provide a secure connection between your database network and the Cast Iron cloud.

Code listings

Listing 1. SQL statement to create account table
CREATE TABLE IF NOT EXISTS 'mysqldb'.'Accounts' (
	ACCOUNT_NAME 		varchar(80) NOT NULL,
	ACCOUNT_TYPE 		varchar(40),
	PARENTID 		varchar(18),
	BILLINGSTREET 		varchar(255),
	BILLINGCITY 		varchar(40),
	BILLINGSTATE 		varchar(20),
	BILLINGPOSTALCODE 	varchar(20),
	BILLINGCOUNTRY 		varchar(40),
	SHIPPINGSTREET 		varchar(255),
	SHIPPINGCITY 		varchar(40),
	SHIPPINGSTATE 		varchar(20),
	SHIPPINGPOSTALCODE 	varchar(20),
	SHIPPINGCOUNTRY 	varchar(40),
	PHONE 			varchar(40),
	FAX 			varchar(40),
	ACCOUNTNUMBER 		varchar(40) NOT NULL,
	WEBSITE 		varchar(255),
	SIC 			varchar(20),
	INDUSTRY 		varchar(80),
	ANNUALREVENUE 		integer,
	NUMBEROFEMPLOYEES 	integer,
	OWNERSHIP 		varchar(40),
	TICKERSYMBOL 		varchar(20),
	DESCRIPTION 		varchar(4000),
	RATING 			varchar(40),
	SITE 			varchar(80),
PRIMARY KEY ('ACCOUNT_NAME', 'ACCOUNTNUMBER') )
Listing 2. SQL statement to create a sample account
INSERT INTO 'mysqldb'.'accounts' (
ACCOUNT_NAME,
BILLINGSTREET, BILLINGCITY, BILLINGSTATE, BILLINGPOSTALCODE, BILLINGCOUNTRY,
SHIPPINGSTREET, SHIPPINGCITY, SHIPPINGSTATE, SHIPPINGPOSTALCODE, SHIPPINGCOUNTRY,
PHONE, FAX, 
ACCOUNTNUMBER, WEBSITE)
VALUES (
'IBM Deutschland GmbH',
'Karl-Arold-Platz 1a', 'Duesseldorf', '05', '40474', 'DE',
'Karl-Arold-Platz 1a', 'Duesseldorf', '05', '40474', 'DE',
'+49-211-476-0', '+49-211-476-0', 
'LBH00007', 'www.ibm.com');
Figure 11. Orchestration SyncAccountsw
Orchestration SyncAccounts

Click to see larger image

Figure 11. Orchestration SyncAccountsw

Orchestration SyncAccounts

Conclusion

This article showed you how to use WebSphere Cast Iron Cloud Integration to integrate a MySQL database with Oracle CRM on Demand. Predefined endpoints as well as hundreds of different templates for integration scenarios enable you to integrate systems without any coding and without delving into the interface definitions of the different endpoints. The article demonstrated how WebSphere Cast Iron Cloud Integration can empower you to integrate on-premise applications and cloud applications in a matter of days and without coding.

Resources

  • WebSphere Cast Iron resources
  • WebSphere resources
    • developerWorks WebSphere developer resources
      Technical information and resources for developers who use WebSphere products. developerWorks WebSphere provides product downloads, how-to information, support resources, and a free technical library of more than 2000 technical articles, tutorials, best practices, IBM Redbooks, and online product manuals.
    • developerWorks WebSphere application connectivity developer resources
      How-to articles, downloads, tutorials, education, product info, and other resources to help you build WebSphere application connectivity and business integration solutions.
    • developerWorks WebSphere SOA and Web services developer resources
      How-to articles, downloads, tutorials, education, product info, and other resources to help you design and build WebSphere SOA and Web services solutions.
    • Most popular WebSphere trial downloads
      No-charge trial downloads for key WebSphere products.
    • WebSphere forums
      Product-specific forums where you can get answers to your technical questions and share your expertise with other WebSphere users.
    • WebSphere on-demand demos
      Download and watch these self-running demos, and learn how WebSphere products and technologies can help your company respond to the rapidly changing and increasingly complex business environment.
    • developerWorks WebSphere weekly newsletter
      The developerWorks newsletter gives you the latest articles and information only on those topics that interest you. In addition to WebSphere, you can select from Java, Linux, Open source, Rational, SOA, Web services, and other topics. Subscribe now and design your custom mailing.
    • WebSphere-related books from IBM Press
      Convenient online ordering through Barnes & Noble.
    • WebSphere-related events
      Conferences, trade shows, Webcasts, and other events around the world of interest to WebSphere developers.
  • developerWorks resources
    • Trial downloads for IBM software products
      No-charge trial downloads for selected IBM® DB2®, Lotus®, Rational®, Tivoli®, and WebSphere® products.
    • developerWorks blogs
      Join a conversation with developerWorks users and authors, and IBM editors and developers.
    • developerWorks Webcasts
      Free technical sessions by IBM experts that can accelerate your learning curve and help you succeed in your most difficult software projects. Sessions range from one-hour Webcasts to half-day and full-day live sessions in cities worldwide.
    • developerWorks podcasts
      Listen to interesting and offbeat interviews and discussions with software innovators.
    • IBM Education Assistant
      A collection of multimedia educational modules that will help you better understand IBM software products and use them more effectively to meet your business requirements.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into WebSphere on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere
ArticleID=645679
ArticleTitle=Integrating cloud applications with WebSphere Cast Iron Cloud Integration
publish-date=04132011