Contents

Popular

Objective

To monitor a table in a database for insert and insert (replicate) the same data into another table in another database.

Prerequisites

  • Start Fiorano Enterprise Server (FES) and Fiorano Peer Server (FPS)
  • Login to eStudio application
  • Understand the basic menus and perspectives (panels) in eStudio application
  • Before configuring the database, add the JDBC driver of the database as a resource to the JDBC System lib. Please Refer to the Add Resources to a Service section to perform this.

Scenario

  1. Insert data into a table in Oracle database through Oracle Application Express.
  2. Configure a DB component which monitors the table in step 1 for 'insert' query.
  3. Configure another DB Component to receive data from the previous DB component and in turn insert it into another table in MS SQL Server database.

Components used

Setting up the Example

Insert data into Oracle Database

This section defines inserting data into Oracle DB using Oracle Application Express.

This data will be further monitored as defined in the next section - Configuring DB Component to monitor the data inserted into Oracle Database.

Perform the following actions to insert data in DB:

  1. Open Oracle Application Express in browser.
  2. Provide the workspace, username and password details and click Login.


    Figure 1: Logging into Oracle Application Express

  3. Navigate to SQL Workshop > ObjectBrowser.
  4. Select the table into which data is to be inserted. In this case, table EMPLOYEE with columns EMPID, EMPNAME and EMPDEPARTMENT.
  5. Select Data tab and click Insert Row.


    Figure 2: Insert Rows in Oracle DB

  6. Add new row details in EMPLOYEE table and click Create.


    Figure 3: Insert data in EMPLOYEE table.
Icon

Any number of rows can be added in the same manner even after launching the Event Process and all the newly inserted rows will be monitored.

Configuring DB Component to monitor the data inserted into Oracle Database

  1. Configure a DB component to monitor data that is inserted using Oracle Application Express.
  2. Add an Event Process with name 'DB_Replication_Eg'. Drag the DB component from DB category in the Service Palette to the Fiorano Orchestrator.


    Figure 4: Adding DB component to Fiorano Orchestrator

  3. Rename the DB component from 'DB1' to 'DB_MonitorDataInOracle' to differentiate it from the DB component that will be configured later.
  4. Double-click the DB component in the Orchestrator to open Configuration Property Sheet (CPS). Click the ellipsis button against Database Conifguration to open DbConfigurations panel.


    Figure 5: DB Component CPS

  5. Provide the following details in the DBConfigurations Panel and click OK.
    1. Database: Oracle (Choose the database to be connected to; 'Oracle' in this example)
    2. Driver: oracle.jdbc.driver.OracleDriver (Retain the default value)
    3. URL: jdbc:oracle:thin:@<MachineIP>:xe (machine IP in which the Oracle database is installed and 'xe' is the database name).
    4. User name: User name to connect to the database.
    5. Password: Password for the above user name.
    6. Named Configuration: OracleNamedConfig (Provide any value here to save the database configuration as Named Configuration. 'OracleDBNamedConfig' is the name used in this example).


      Figure 6: Database configuration in DbConfigurations Panel

  6. Click the Test button in Managed Connection Factory panel to see if the database has been configured correctly.


    Figure 7: Testing Database configuration in Managed Connection Factory Panel

  7. If the configuration is valid, it gets displayed in Test Connection Configuration pop-up.


    Figure 8: Testing Database Configuration

  8. Once the database has been configured properly, click Next in the Managed Connection Factory panel to navigate to Interaction Configurations panel.


    Figure 9: Moving to Interaction Configurations Panel

  9. Click SQL Configuration ellipsis button to configure 'Monitor Table' in SQL Configuration Wizard panel.


    Figure 10: SQL Configuration in Interaction Configurations Panel

  10. Click the Add button and select Monitor Table option from the menu.


    Figure 11: Selecting 'Monitor Table' in SQL Configuration Panel
  11. In SQL Creation Wizard, check Insert under Monitor Table for: and add a table that is to be monitored for Insert operation by clicking the ellipsis button against Monitor Table property.


    Figure 12: SQL Creation Wizard

  12. In Table Selection Dialog, click the Fetch Schemas button which fetches all the schemas in the database into Schema drop-down. Select a schema and then click Refresh Tables button to fetch/refresh the table list for the selected schema. Select the table from the list for which Monitoring has to be configured and click OK.


    Figure 13: Selecting the table in Table Selection Dialog Panel

  13. In SQL Configuration Wizard, navigate through all the pages by clicking Next and finally click Finish on the last page.


    Figure 14: Finishing Monitor Table configuration

  14. An Information dialog box is displayed in which the paths where SQL queries created for above operation are saved. Info: These can be used to delete triggers, stored procedures and copy tables created for monitoring data.


    Figure 15: Paths for cleanup SQL

  15. A stored procedure and a nested update query are created which will be used to monitor data. The Adapter Mode automatically changes to 'Scheduler' when a Monitor Table statement is configured. This means that the component will not have an input port and is launched in scheduling mode. Click Finish in SQL Configuration Wizard.


    Figure 16: Queries to monitor data in Scheduling mode

  16. Click Finish in Interaction Configurations panel to finish configuring DB_MonitorDataInOracle component.


    Figure 17: Finishing 'DB_MonitorDataInOracle' component configuration

Configuring DB Component to replicate data into MS SQL Server 2008 Database

Add another DB component to the Fiorano Orchestrator and rename the component from 'DB1' to 'DB_InsertDataInMSSQL'.

This DB component is used to replicate data from a table in Oracle to a table in MSSQL database.

Configure an Insert Query for table (it is EMPLOYEE in this example) in MSSQL with similar structure as the EMPLOYEE table in Oracle database.

Icon

To configure this component, please refer the steps in Configuring DB component section under DB Basics - Select and Insert Statements page with a slight change in Database Configuration as follows:
Provide "MSSQL database" in place of "Oracle" in the DBConfigurations Panel.

Configuring XSLT Component to transform XML between Oracle and MS SQL DB Instances

Xslt component is used to transform XML between Oracle and MSSQL DB instances which might have subtle differences in schemas.

To configure Xslt component, perform the following actions:

  1. Drag the Xslt component from Transformation category in the Service Palette to the Fiorano Orchestrator.
  2. Connect the components in the following manner:
    1. Output port of DB_MonitorDataInOracle to the input port of Xslt
    2. Output port of Xslt to the input port of DB_InsertDataInMSSQL.


      Figure 18: Connecting Xslt component to the two DB components

  3. Double-click Xslt1 component in the Orchestrator to open Configuration Property Sheet (CPS). Click the ellipsis button against Mappings to configure mappings.


    Figure 19: Configure Mappings

  4. Schemas of the OUT_PORT of DB_MonitorDataInOracle and IN_PORT of DB_InsertDataInMSSQL are displayed. Map them in the following manner, save and close the mappings editor.


    Figure 20: Define transformations

  5. Click Finish in the Component Configuration panel to finish Xslt configuration.


    Figure 21: Finishing Xslt component configuration

Connecting Display component

Connect the output port of DBInsertDatainMSSQL component to the input port of a Display (present in Util category in Service Palette) component.


Figure 22: Connecting Display to DB_InsertDataInMSSQL

Running the Example

To run the example, perform the following actions in the DB_Replication_Eg Event Process:

  1. Click Check Resource and Connectivity icon (or press ALT+SHIFT+C) to check resource and connectivity.
  2. Click Run Event Process icon (ALT+SHIFT+R) to run the event process. Display1 window gets opened.
  3. Insert a record into EMPLOYEE table following the steps in Insert Data into Oracle Database section.
  4. The output message in Display window will have UpdateCount as "1" confirming the successful monitoring and insertion of data.Info: The data inserted in Oracle database is monitored and same record is inserted in MS SQL Server database.


    Figure 23: Output message after inserting data in MS SQL Server Database

Reference

Please note that the options used in this example are minimum, which helps you to get an overview of the application. To explore the other options present in the DB, Xslt and Display, refer sections: DB, Xslt and Display respectively.

Adaptavist ThemeBuilder EngineAtlassian Confluence