Setting up Oracle Data Integrator (ODI) with Change Data Capture (CDC): DB Table to WebLogic Server JMS Queue

Introduction:

The following shows detailed steps on how to setup ODI with Change Data Capture on a database table so that all changes to a database table are loaded into a target. In this case the target is a JMS Queue hosted on a WebLogic Server 10.3 container.

Pre-requisites

• Download Oracle 10gR2 Database if it is not already installed
• Download ODI 10.1.3.5
• Install ODI 10.1.3.5
• Review ODI Documentation
• Download and install WebLogic Server 10.3
• Understanding of JMS Queues

 

Setup ODI Repositories:

The following information makes assumptions that this will be setup on a Windows machine however the same concepts would apply to a UNIX/LINUX environment.

• Create a Master Repository in an Oracle DB. In this case we will set this up under a user in our local Oracle 10gR2 Database. We will use the “system” user and connect to our local DB which was setup earlier (jdbc:oracle:thin:@localhost:1541:orcl). Goto Start –> All Programs -> <ORACLE_HOME> ->Oracle Data Integrator -> Repository Management -> Create Master Repository
• This will take a couple of minutes to create the necessary ODI information within the database and schema one provided.
• Startup the Topology Manager and create a Work repository. This is needed prior to starting up the Oracle Designer. Goto Start –> All Programs -> <ORACLE_HOME> -> Oracle Data Integrator -> Topology Manager.
• Create a connection to the repository by clicking on the “New” icon and fill in the data like below: (SUPERVISOR/SUNOPSIS are the default user/password and need to be in capitals)

• Goto the repositories tab (the 5^th one from the beginning) and create some named work repository. Right click and select “Insert new work repository”.

• In the Topology Manager, create a Physical Architecture for the Database Schema where you will be connecting to (the 1^st tab at the bottom). (NOTE: It is assumed that there are some tables in this schema already.) Select the technology (in this case “Oracle”), then right click and select “Insert Data Server”. Before clicking “ok” to save, one will have to create the Logical Layer as well (see later steps). Fill in the user/password to the schema one will be connecting to and fill in the JDBC information in the JDBC tab.

• Next right click this Data Server and select “Insert Physical Schema”. Select the schema one want and then on the “Context” tab is where one will select the Logical Schema. The Logical Schema will first need to be created in the next step before selecting it here and clicking “ok”. (You may get an error while saving this however first save it and then open it up so the Logical Schema can be created.)

• In the Topology Manager, create a Logical Architecture for the Database Schema where you will be connecting to (the 3^rd tab at the bottom). This will be needed for the Physical Architecture layer you created. Select the technology (in this case “Oracle”), then right click and select “Insert Logical Schema”.

 

Setup ODI for use with WebLogic JMS Queues:

The following outlines the pre-work necessary to setup ODI to work with WebLogic JMS Queues.

Create the JMS queues on WebLogic Server

The following outlines the steps necessary to create a basic JMS queue for this example. If one already has a JMS queue defined on a WebLogic Server, this section can be skipped.
WLS Domain Creation

• 1. Create a default WLS 10.3 Domain. Run <BEA_HOME>\wlserver10.3\common\bin\config.cmd
• 2. Select the defaults (7001 as the port, weblogic/weblogic) into some named directory.
• 3. Start the newly created domain by running startWebLogic.cmd in the directory where the Domain was created.
• 4. Login to the WLS console: http://localhhost:7001/console as weblogic/weblogic

Create a JMS Server:

• 1. Services -> Messaging -> JMS Server -> Select New
• 2. Create a name, select Persistent Store (File Store).
• 3. Target this to the AdminServer

Create a JMS Module:

• 1. Services -> Messaging -> JMS Module -> Select New
• 2. At the end, select add resources to it.

Create a JMS Resource (Queue in this case):

• 1. Select "new" and click "Queue". In this example the JNDI Name is “TestQueueJNDI” and the Queue name is “weblogic.jms.poc.TestQueue”. The Queue name specified here will be the same string used in ODI when defining the Physical Architecture. Please make a note of it.
• 2. Click "Create a new Subdeployment"
• 3. Target the JMS Server which was just created.

 

Update ODI to work with WLS JMS

The following outlines the pre-work necessary to setup ODI to work/connect to WLS JMS Queues.

• Within the WebLogic Server installation one will need to create a jar file which ODI will use for connecting to a WLS JMS Queue. Review the documentation
• Build the wlfullclient5.jar file for use with ODI by doing the following:
• First change the directory to the WL_HOME/server/lib directory
• Run the command: java -jar wljarbuilder.jar -profile wlfullclient5
• Copy the resulting wlfullclient5.jar file to the <ORACLE_HOME>\oracledi\drivers directory
• Modify the <ORACLE_HOME>\oracledi\bin\odiparams.bat file to use JDK 1.5 instead of the default of JDK 1.4. This is the only ODI file which needs to be modified to use JDK 1.5. Specifically in this file add the following line right before the existing “if” statement:

set ODI_JAVA_HOME=C:\bea10x\jrockit_150_11
if "%ODI_JAVA_HOME%" == "" set ODI_JAVA_HOME=%JAVA_HOME%

• If the ODI Designer is running, shut it down and restart it so the changes take effect. If not, then one will not be able to successfully test the JMS Connection from ODI to WLS.

 

Create JMS Architecture in ODI Topology Manager

After restarting ODI Designer, one can create and test the connections to WebLogic Server JMS Queues. The following outlines the steps required to setup JMS Queues in ODI.

• In the Topology Manager, create a Physical Architecture for the WLS JMS Queue where you will be connecting to (the 1^st tab at the bottom).
• Right click on JMS Queue in the Physical Architecture. Fill in the Definition and JNDI tabs to look like the following screenshots. Modify the host:port name and user/password to what is defined in the WebLogic Server Domain one is connecting to.

• Clicking “ok” to save. (There may be a message about the Logical Layer however that is created in the next step.)
• Create the Logical Layer for this JMS Queue.

• Make sure the Physical Schema for the JMS Data Server has the Logical Layer defined in the “Context” tab.

• Now one can test the connection to WLS JMS to make sure everything is setup correctly. Open up the Data Server defined for the WLS JMS Queue and click the “Test” button at the bottom.

 

ODI Designer:

• Startup the ODI Designer: Goto Start -> All Programs -> <ORACLE_HOME> -> Oracle Data Integrator -> Designer
• Create a new connection to this new Master Repository and Work Repository. Click the “New” icon next to the default “Login Name”
• Connect with the SUPERVISOR/SUNOPSIS (user/password and all capitals), supply the Oracle Database information where the master repository was created, and the name of the Work Repository which was just created.

• Back in the Oracle Designer we will now create a Model based on the Logical Schema we created in the Topology Manager.
• Create a Project under the Projects tab.
• Next import the Knowledge Modules. Right click the project, select Import, and then “Import Knowledge Modules”. Select the directory under <ORACLE_HOME>\oracledi\impexp, and then select each knowledge module individually. We will select:

· CKM SQL
· IKM SQL Incremental Update
· IKM SQL to JMS Append
· JKM Oracle 10g Consistent (LOGMINER)
· LKM SQL to SQL

 

Create a Model for the Database table

• Right click and select “Insert Model”.
• In the definition tab, select the technology and Logical Schema (created in the Topology Manager) and then click the “Reverse” button to get the tables for this schema into the model view. Click “ok”. NOTE: There are SQL statements to create the test table for the schema user in the APPENDIX section which one can use to test with.

• Double click the Model name you created and select the Journalizing tab. Make sure that the tab is filled out and looks like the following. Because you want to capture changes to the tables in a consistent fashion, you select the Consistent option and the JKM Oracle 10g Consistent (LOGMINER) knowledge module. This knowledge module, shown in the figure below, will capture new and changed data, using the LogMiner feature of Oracle Database 10g. Read the notes section for additional details on the user accessing the tables.

• Click “ok”.
• Next right-click the tables in turn, and choose Changed Data Capture ->Add to CDC. Then edit the model again to select the Journalized Tables tab. If there are multiple tables use the up and down arrow keys to place the tables in the correct order.
• Next add a subscriber to the journal by returning to the Designer application, right-clicking the Model, and choosing Changed Data Capture ->Subscriber->Subscribe. You add a new subscriber and execute the code locally to ensure that the code executes correctly. Once this step is complete, you have set up the changed-data capture process and you are ready to begin building your interfaces.
• Now one is ready to create the journal that captures changed data from these two tables. To do this, right-click the model again and choose Changed Data Capture ->Start Journal. Click OK to execute the code locally, and then start up the Operator application to check the progress of the operation. If all has gone well, you will be presented with a list of completed steps similar to the following. In order to start the Operator goto Start -> All Programs –> <ORACLE_HOME> -> Oracle Data Integrator -> Operator and login to the repository when presented with the same login screen.

• At this point if CDC is working properly the table should have a green icon instead of the yellow/orange icon. If this is not the case, then CDC is not properly setup.

 

Create a Model for JMS DataStore in ODI Designer

• Right click and select “Insert Model”.
• In the definition tab, select the technology and Logical Schema (created in the Topology Manager).

• Next within this Model, right click and select “Insert Datastore”.
• Create a Datastore making sure the Name and Resource name is the name of the JMS Queue which was created within WebLogic Server. Also note that the “Datastore Type” is set to “table”.

• Within this Datastore, click on the “Files” tab and make sure the format of how the JMS Text Message will look like. This means that the JMS Text Message sent to the queue can have all of the columns of data from the DB table separated by “,” in this case. The delimiter can be anything one chooses.

• Next define the columns of data which can be added to this JMS Text message. In this case we are adding all of the columns of data from the DB table into the resulting JMS Message. Right click on the Column name and select “Insert Column”. This will be used in the mapping part in the interface defined later on.

 

Create the Interface (DB to WLS JMS)

• Now we can create a new interface which will take the data from SUPPORT_CASE table in an Oracle Database when CDC happens and load it to a JMS Queue hosted on a WebLogic Server container.
• In the Project tab, right click the interface to create a new interface.
• Make sure the checkbox for “Staging Area Different from Target” is selected. Select the Logical Schema for the staging area. In this case it is the Oracle DB.

• Click on the Diagram tab and drag and drop the tables from the model for the target (“weblogic.jms.poc.testQueue” Datastore in this example) and the table for the source onto the respective locations on the Diagram Tab.
• Join and map any columns to the target column in the Diagram tab.
• Also on the diagram tab on the Interface, select the source, enable “Journalized Data Only”. Make sure the filter created has the correct subscriber name one wants. The resulting picture should look something like this:

• Click on the Flow tab to see how the data will be combined. Here one will see two boxes. One for the Staging Area (which is on the Database) and one on the Target Area (the WLS JMS Queue). Click on the Target Area and in the IKM dropdown box select “IKM SQL to JMS Append’. Click “apply” and there should be no errors.

• Next select the Controls tab to make sure the Control Knowledge Module was selected. This is used for handling constraint errors in the target table. Select the CKM SQL Knowledge Module, which will handle erroneous data for any ISO-92-compliant database. Click “OK”.
• The interface is now successfully created to load journalized data from the DB table into a WLS JMS Queue. Next the Package and Scenario will be created to automate the flow of this at runtime.

 

Create the ODI Packages and Execute:

Now create an Oracle Data Integrator package to carry out the following steps:

• Check the SUPPORT_CASE journalized data to see if new or changed data records have been added.
• If journalized data is detected, extend the journal window.
• Execute the interface to read from the journalized data and load the target data store which in this case will be a WebLogic Server JMS Queue.
• Purge the journal window.
• Start this package again. (This will be in a loop ready for new journal changes.) Creating this package and then deploying it as an Oracle Data Integrator scenario effectively creates a real-time, continuously running ETL process.

Create the Package and Scenario for setting up CDC

The purpose of this is to automate setting up CDC within one’s environment.

• 1. To create this package, navigate to the Projects tab in the Designer application, locate the folder containing the interfaces you defined earlier, find the Packages entry, right-click it, and select Insert Package. Give the package a name and then navigate to the Diagram tab in the package details dialog box.
• 2. The next step in this package will be to drag the Model just created onto the canvas. Within the General Tab of this step, select the Drop down under “Type” and select “Journalizing Mode”.
• 3. Select “Stop” under Journalizing to stop this if it was already running so it can be successfully rerun.

• 4. The next step in this package will be to drag the Model just created onto the canvas again. Within the General Tab of this step, select the Drop down under “Type” and select “Journalizing Mode”.
• 5. Under the “Journalizing” section of the General tab, select “Start” and “Add Subscribers”. Add the “SUBA” subscriber to the list.

• 6. Connect the first step to this step with a green “ok” arrow and a red “ko” arrow.
• 7. Right click on this package to create a Scenario.
• 8. This scenario can be executed either from the tool by right clicking and selecting “Execute” or by running this from the command line.
• 9. Execute this Scenario so that Journaling is initially started. The next section will explain how to create and setup the main CDC process in a loop so that changes are continuously consumed.

 

Create the Package and Scenario for the Main CDC process

• 1. To create this package, navigate to the Projects tab in the Designer application, locate the folder containing the interfaces you defined earlier, find the Packages entry, right-click it, and select Insert Package. Give the package a name and then navigate to the Diagram tab in the package details dialog box.
• 2. Using the toolbox on the right, go to the Event Detection folder and add the OdiWaitForLogData tool to the package canvas, as shown in the figure below. This tool will monitor the journalized data on a regular basis. In this case the step name has been changed to “Waiting for Changes”.
• 3. In this step update the following parameters:
• a. Logical Schema: Change this to the Logical Schema being used in your interface
• b. Subscriber: The subscriber name
• c. CDC Set: Change this to your Model Name and the Logical Schema name. It is in the form of: <%=odiRef.getObjectName("L","model_code","logical_schema", "D")%>

Example: <%=odiRef.getObjectName("L","ORACLE_SPOZ2_MODEL","Oracle 10gR2 Logical Schema SPOZ2", "D")%>

• 4. The next step in this package will be to drag the Model just created containing the Database table onto the canvas. Within the General Tab of this step, select the Drop down under “Type” and select “Journalizing Mode”.
• 5. Under the “Consumption” section of the General tab, select “Extend Window” and “Lock Subscribers”. Add the “SUBA” subscriber to the list.

• 6. Connect the first step to this step with a green “ok” arrow.
• 7. Next Drag the Interface just created for Populating the WLS JMS Queue after the previous step.

• 8. Connect the second step to this step for the interface with a green “ok” arrow.
• 9. The next step in this package will be to drag the Model again onto the canvas after the interface. Within the General Tab of this step, select the Drop down under “Type” and select “Journalizing Mode”.
• 10. Under the “Consumption” section of the General tab, select “Purge Journal” and “Unlock Subscribers”. Add the “SUBA” subscriber to the list.

• 11. Connect the third step from the interface to this step with a green “ok” arrow.
• 12. Right click on this package to create a Scenario.

• 13. After creating this Scenario, open up the package just created.
• 14. In this package, drag and drop the scenario to the last step in the list after “Purge and Unlock”.
• 15. Connect the fourth step from the “Purge and UnLock” step to this final step (the scenario) with a green “ok” arrow.
• 16. Click on the named scenario which was just added to the package and make sure to select “Asynchronous” in the parameters listed under the General tab.

• 17. Click “ok” to save and close the package.
• 18. Right click the scenario created in step 12 and click “Regenerate”.

• 19. The reason for doing steps 13 through 17 is to avoid any issues around log files growing too quickly and too large in the work repository. Plus one would not be able to clean the log file here.
• 20. This scenario can be executed either from the tool by right clicking and selecting “Execute” or by running this from the command line.

 

Testing the Main CDC Scenario:

• 1. Make sure the Journal has been started by executing the scenario you created. Example: “SETTING_UP_CDC_SCEN”.
• 2. Make sure the main CDC scenario is executing by right clicking the named Scenario (for example “SUPPORT_CASE_TO_WLS_JMS_Q_SCEN”) and then starting the “Operator” utility. Here you should see something like this (notice the green icon that does not have a checkmark):

• 3. Goto the Model section, right click on the table with CDC setup on it and select “Data”.
• 4. In the window which comes up, change some data and hit enter. Click “ok” so the changes take effect. (Notice Changes were made to the name to add “7777” to it).

• 5. Go back to the Operator utility, click Refresh and you’ll notice that the Package ran and the interface to populate the JMS Queue executed. Double click on the “Pop. WLS JMS Queue” and select the “Execution” tab. There you will notice that the “Number of Inserts” shows a “1” meaning that one JMS message was sent to the target WLS JMS Queue.

• 6. Also notice that the scenario was stopped and restarted (by adding the scenario to the package to start again) as to avoid any log issues in the ODI Work Repository.
• 7. Login into the WebLogic Server Console, click on “Services”, “Messaging”, “JMS Modules”, the name of the JMS Module created, the name of the Queue created, click the Monitoring tab, select the box next to the Queue name and then click “Show Messages”.

• 8. Click on the Message ID to view the contents of the message in the JMS Queue.

• 9. Notice the change which was made to the “name” and that each field from the DB table is separated by a “,” (as was previously setup in the “Files” tab of the data store defined in the Model).
• 10. This scenario will continue to run (by design in the Package created) until one chooses to stop it.

 

APPENDIX:

SQL statements to create the sample SUPPORT_CASE and SUPPORT_CASE_TRG tables.

drop table support_case;
create table support_case
(
customerid varchar2(20),
name varchar2(50),
email varchar2(30),
address varchar2(50),
city varchar2(40),
state varchar2(20),
zip varchar2(15),
country varchar2(30),
phone varchar2(20),
orderid varchar2(20),
constraint supportcase_pk primary key(customerid)
);
INSERT INTO support_case VALUES('1','Scott King','sking@oracle.com', '200 Main Street', 'New York', 'NY', '10022', 'USA', '212-555-1212', '200');
INSERT INTO support_case VALUES('2','Steven Pozarycki', 'steve@oracle.com', '22 Pinckney Street', 'Morris Plains', 'NJ', '07950', 'USA', '617-863-4444', '500');
INSERT INTO support_case VALUES('3', 'Murali M', 'murali@oracle.com', '180 Patriots Road', 'Boston', 'MA', '02116', 'USA', '617-285-2222', '300');
INSERT INTO support_case VALUES('4','Lloyd Doe, 'lloyd@oracle.com', '56 Cambridge Street', 'Boston', 'MA', '02116', 'USA', '617-452-3333', '400');
commit;

drop table support_case_trg;
create table support_case_trg
(
customerid varchar2(20),
name varchar2(50),
email varchar2(30),
address varchar2(50),
city varchar2(40),
state varchar2(20),
zip varchar2(15),
country varchar2(30),
phone varchar2(20),
orderid varchar2(20),
constraint supportcase_trg_pk primary key(customerid)
);
commit;