The purpose of this post is to explain the data integration between SQL and JMS Queue or Topic. ODI is capable of integration between the two technologies through easy configurable steps and extensible Integration Knowledge Modules. You can leverage ODI to send text or binary message from SQL to JMS.
We will cover only the basic steps involved in the integration purpose and this tutorial assumes that everything is installed on windows and the database is Oracle and JMS provider is JBOSS. In this post we will use Oracle default schema "SCOTT" and publish the employee name from EMP table to JMS queue/topic without any order.
Note: I cant put the tags <> in this blog so replacing tags by paranthesis ( )
Pre-Requisite and Software Version
1) Oracle Data Integrator 10g - Version 10.1.3.6.4 or 10.1.3.6.5.0
2) JBOSS Messaging Middleware - Version 4.2.3 GA
3) ODI Master and Work Repository already installed
First Step - JBOSS Server topic/queue configuration1) Copy the JBOSS version 4.2.3 GA version to your local window desktop ( JBOSS_INSTALL)
2) Configure the JMS Topic/Queue name in the jboss-jms configuration file. Navigate to "JBOSS_INSTALL\ server\all\deploy-hasingleton\jms" directory and your JMS Queue in the configuration file - "jbossmq-destinations-service.xml"
3) Add the following entry for topic and queue respectively in the above configuration file:
-- Add the queue - odiQueue to the configuration file
(mbean code="org.jboss.mq.server.jmx.Queue" name="jboss.mq.destination:service=Queue,name=odiQueue") (depends optional-attribute-name="DestinationManager")jboss.mq:service=DestinationManager(/depends) (/mbean)
(mbean code="org.jboss.mq.server.jmx.Topic" name="jboss.mq.destination:service=Topic,name=odiTopic") (depends optional-attribute-name="DestinationManager")jboss.mq:service=DestinationManager(/depends) (/mbean)
Save the configuration file and then be prepare to start the server
4) Run the JBOSS Server using the command prompt and bind your hostname to the JBOSS server(Start > Run > cmd)
(JBOSS_INSTALL)/bin>run -c all -b (jboss_host_or_ip_address)
eg. If my hostname which has JBOSS installed is "hbinjola-pc" then use following
/bin>run -c all -b hbinjola-pc
Ok so now the JBOSS part is finished and we are ready to move to second step for ODI Configuration
Second Step - ODI Topology Configuration
In the second step we will configure the ODI Physical Architecture, Conext, Logical Architecture and Physical Schema.
Data Server and Physical Schema Configuration for JMS Queue
Open the topology manager in ODI and see the physical architecture window. If you are having queue or topic use the Technology JMS Queue or JMS Topic respectively for configuring the data server.
All steps are similar for Topic and Queue so let me focus for queue connection :
# Right click on the JMS Queue and click on Insert Data Server and enter only below config
Definition Tab :
Name :JMSQ_LOCALHOST
JNDI Tab :
JNDI Authentication:
JNDI User: Leave Blank
Password: Leave Blank
JNDI Protocol:
JNDI Driver: org.jnp.interfaces.NamingContextFactory
JNDI URL: jnp://
JNDI Resource: ConnectionFactory
Click on Apply and Test using Local Agent. A popup must appear saying "Successfull Connection"
Physical Schema Configuration
For JMS the physical schema is the JMS topic or queue which we actually define in the model as datastore. The configuration for datastore is covered later in this post
# Straight away go to Context Tab and click on add button above and map the Global Context to Logical Schema - "JMSQ_LOCALHOST" and click on Ok.
Data Server and Physical Schema Configuration for Oracle Technology
For Oracle configuration is pretty easy which you may have done several times. Just writing some high level steps
- Insert Data Server under Oracle Technology in physical architecture
- Provide the data server name, instance name, db schema name, password and then enter JDBC connection for Oracle Driver
- Test the Connection and then start entering schema on which you want to work
- For this testing we are using SCOTT schema and kept the work schema as ODI_STG(create this user in oracle)
- For each schema create a logical schema under the context tab. For our case we create ORCL_SCOTT and the mapping we use GLOBAL context
Ok so this finishes your Topology Configuration and we can start our journey to Oracle Designer Window
Third Step - ODI Model Configuration in Designer
1) Create a model for Oracle and name it as MOD_ORCL_SCOTT. Provide technology as Oracle and Logical Schema as ORCL_SCOTT and import the EMP table using context Global
Now the important thing is creating a model for JMS Queue.
2) Create a model and only work under the definition tab by entering the following details
Name: MODEL JMS QUEUE
Code: MODEL_JMS_QUEUE
Technology: JMS queue
Logical Schema: JMSQ_LOCALHOST
3) Now right click on MODEL JMS QUEUE and click on "INSERT DataStore" and enter following details. It is just like a file model with resource name as the queue name which is registered with JBOSS.
Definition Tab:
Name : ODI JMSQ
Alias: JMSQ
DataStore Type : Table
OLAP Type: Undefined
Resource Name: queue/odiQueue
Files Tab (Put anything as it does not matter you are not reverse engg)
File Format: Fixed
Heading: 0
Record Seperator: MS-DOS
Field Seperator: Space
Text Delimiter: Leave Blank
Decimal Separator: Leave Blank
Columns:
Add a column manually and give following details as this is the length of the text string in the JMS Queue
Order : 1
Name: DATA
Physical Length: 1000
Logical Length: 1000
Fourth Step - ODI Interface Design:
1) Prior to working on ODI Interface we need to import the KM required for integration purpose. ODI provides a predefined KM which takes care of integration between SQL and JMS i.e. "IKM SQL to JMS Append". Navigate to KM under the project and right click and import from (ODI_INSTALL)
2) Create a new interface and enter following details in definition tab :
Definition Tab:
Name: INT_SQLTOJMSQ_PUSH
Optimization Code : Global
Click on checkbox "Staging area different from target" as JMS is our target does not have any DB to create temporary objects
Select "ORCL_SCOTT" in the list of options
Diagram Tab:
- Drag and drop the EMP table from model - MOD_ORCL_SCOTT to source area
- Drag and drop the ODI_JMSQ from JMS Model to Target Area
- Map the DATA columns as : EMP.EMP_NAME
Flow Tab:
- On the source/staging/target we need to select a KM for integration purpose
- Select the KM as "IKM SQL to JMS Append" available in the list and leave all option as default
Control Tab:
- Select the Check KM as CKM Oracle and enter max error allowed as 0% leaving other option as default
Fifth Step - ODI Execution :
1) Execute the interface by context as "Global", Agent as "Local Agent" and log level as "5"
2) The employee name must be pushed into the JMS queue name and ready to be consumed by the consumer program in java. You will not be able to view data in the JMS datastore as it is not supported. You need to run a java program to consume messages from JMS queue. I am not a java expert hence not focusing on post integration steps as how to consume the message.
