Part 1 of this article series describes how to:
- Build an orchestration using WebSphere Cast Iron Studio (hereafter called Studio), which inserts records into an Oracle database table by polling an MQ queue.
- Configure an "MQ Poll Message" activity, a "Read XML" activity, and a "Database Insert Rows" activity.
- Deploy the project in the Integration Appliance using the Web Management Console (WMC).
The following software is required to follow the instructions in this article:
- IBM WebSphere Cast Iron Studio V220.127.116.11 or later
- Oracle 10g XE or later
- IBM WebSphere MQ 18.104.22.168 or later
- Microsoft® Windows™ V7 or later
Setting up Oracle, MQ setup, and XML schema
Oracle and MQ are two external systems to be integrated through Cast Iron. This section describes the external system parameters and the structure of the input data.
Oracle database setup
You need to set up the Oracle database. For this article, the following setup is maintained:
- Database Name: XE
- Database Port: 1521
- Database User: CIRON
Create the "EMPDTL" table in the Oracle database under the "CIRON" schema. The table script is shown below:
CREATE TABLE "CIRON"."EMPDTL" ( "NAME" VARCHAR2(20 BYTE), "ID" VARCHAR2(20 BYTE), "DEPT" VARCHAR2(20 BYTE), "DEPTCODE" NUMBER );
You need to set up MQ properly. For this article, the following setup is maintained:
- MQ Queue Manager: MB7QMGR
- MQ Listener Port: 2415
- MQ Local Queue: IN1
- Server Connection Channel: SYSTEM.AUTO.SVRCONN
- MQ Listener: MB7QMGR_LISTENER
Now start the MQ listener.
Input XML file
Cast Iron is used to load the EMPDTL table from an XML input file. The XML input file is shown in Listing 1.
Listing 1. XML input file
<CompanyDetails> <Employee> <Name>Ajit Mohra</Name> <Id>876548</Id> <Department>Middleware</Department> <DepartmentId>80765</DepartmentId> </Employee> <Employee> <Name>Rick Shastry</Name> <Id>323275</Id> <Department>SAP</Department> <DepartmentId>98453</DepartmentId> </Employee> </CompanyDetails>
Ensure that the input file is compliant with the following XML schema, CompanyDetails.xsd, as shown in Listing 2.
Listing 2. XML schema
<?xml version="1.0" encoding="UTF-8"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="Name" type="xs:string"/> <xs:element name="Id" type="xs:string"/> <xs:element name="Employee"> <xs:complexType> <xs:sequence> <xs:element ref="Name"/> <xs:element ref="Id"/> <xs:element ref="Department"/> <xs:element ref="DepartmentId"/> </xs:sequence> </xs:complexType> </xs:element> <xs:element name="DepartmentId" type="xs:int"/> <xs:element name="Department" type="xs:string"/> <xs:element name="CompanyDetails"> <xs:complexType> <xs:sequence> <xs:element ref="Employee" maxOccurs="unbounded"/> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>
Developing the Cast Iron orchestration
This section describes the procedure of creating a new Cast Iron project and a new orchestration in the project.
Creating the orchestration
- Open Cast Iron Studio. Create a new project by
clicking File > New > New Project as shown in
Figure 1. Name the new
project, such as
Figure 1. Creating a new project in Studio
- Next, create a new orchestration by clicking on the
Project tab in the upper-right corner.
Right-click on Orchestration > New Orchestration
as shown in Figure 2.
Figure 2. Creating a new orchestration in Studio
The new orchestration is created as shown in Figure 3.
Figure 3. Newly created orchestration
- Rename it as
databaseOperation. Right-click on Orchestration and then Rename, as shown in Figure 4.
Figure 4. Renaming the orchestration
The new name is entered as shown in Figure 5.
Figure 5. New orchestration after rename
The new orchestration is displayed as shown in Figure 6.
Figure 6. Graphical view of newly created orchestration
- Save the project by pressing Ctrl+s.
Creating the endpoints
Before configuring the orchestration, you need to create the endpoints to set up the connectivity for the external systems. Here, the source XML file comes from the MQ queue and the data is loaded into an Oracle database table. You need to create two endpoints – one for MQ and the other one for the database.
- You also need to create some configuration properties, which need to
be set on the endpoints. Select Project > Configuration
Properties as shown in Figure 7.
Figure 7. Setting the project specific configuration properties
- Once you click Configuration Properties, the
following window appears as shown in Figure 8.
Figure 8. Configuration Properties window
- Set the type for the configuration property. The type should be either
"String" or "Password". See the sequence of steps in Figure 9.
Figure 9. Steps of setting a configuration property
- Once all the properties are set, the configuration looks like Figure
Figure 10. Project specific configuration properties
- Create one MQ endpoint by right-clicking on
Endpoints, as shown in Figure 11.
Figure 11. Creating endpoints
- The newly created MQ endpoint is renamed as
MQEndpoint. Open this endpoint by double-clicking MQEndpoint as shown in Figure 12. The host name, port number, and appropriate server connection channel name are provided as shown in Figure 12.
Figure 12. Configuring MQEndpoint
- Now, instead of hard coding the values, you can just put the
configuration properties in the respective fields, such as Host Name,
Port, and so on. Click the "T" drop-down icon next to the fields to
get the configuration properties that you created previously.
MQEndpoint is configured properly as shown in Figure 13. The MQ
Listener is started and the project is saved.
Figure 13. Configuring MQEndpoint with the configuration property
- Similarly, one database endpoint, “DBEndpoint”, is created. The
configuration of DBEndpoint is shown in Figure 14.
Figure 14. Configuring DatabaseEndpoint
- The appropriate database type (Oracle), database name, server, port,
user name, and password are provided. The rest of the parameters are
left as they are. You can also provide the configuration parameters,
instead of hard coding them, as shown in Figure 15.
Figure 15. Configuring DatabaseEndpoint with the configuration property
- After providing all the parameters, press the Test
Connection button at the bottom of this endpoint to check
the database connectivity, as shown in Figure 16.
Figure 16. Testing the connectivity of DatabaseEndpoint
If the connectivity is established, then the following message is displayed as shown in Figure 17.
Figure 17. Success message of DatabaseEndpoint connectivity
The created endpoints are shown in Figure 18.
Figure 18. Database and MQ endpoints in Studio
In Part 1 of this series, you learned to create a Cast Iron orchestration, a database endpoint, and an MQ endpoint. A Cast Iron project should have at least one orchestration because it is the main component responsible for the execution of the sequence of "activities", which are necessary in an integration solution. The endpoints are used in the activities to connect to the external systems. Here, the integration solution can connect to the MQ and Oracle database through the endpoints you have just created.
In Part 2, you will learn how to configure the Cast Iron orchestration by polling an MQ queue and inserting records into a database table.
- WebSphere Cast Iron Information Center
- WebSphere Cast Iron Cloud Integration product page
- Integrating cloud applications with WebSphere Cast Iron Cloud Integration
- WebSphere Cast Iron Cloud Integration support
- Getting Started with IBM WebSphere Cast Iron Cloud Integration