App Connect

Data copy pattern using DatabaseInput node in ACE.

By SINDHU BALAKRISHNAN posted Mon October 19, 2020 06:28 AM

  

Co-author: @JAMES JOSE

 In normal DatabaseInput node scenarios, an event table is updated with Events whenever there is a change in the Application data. DatabaseInput node picks up the event and the application data is processed based on the requirement. Sometimes it is required to process large number of existing records of a table like copying the data to another database where matching events are not present for the DatabaseInput node to pick and trigger the flow execution. In such scenarios following approach can be taken to create the events and process the records by using the DatabaseInput node flow.

Assume an Oracle database table is having more than 1 million records and it needs to be copied to a DB2 database and there is no matching event Store.

Existing EMPLOYEE table in the Oracle database is of the form:

                                     

Column name                Data type

--------------------------------------------

ID                                   VARCHAR  

FIRSTNAME                  VARCHAR  

LASTNAME                   VARCHAR  

DEPT                              VARCHAR  

 

There is a business requirement to merge the FIRSTNAME and LASTNAME columns of Oracle table to a single column named 'NAME' in the DB2 table and so the EMPLOYEE tabled at the DB2 Database is defined as:

 

Column name                Data type

-------------------------------------------

ID                                    VARCHAR  

NAME                            VARCHAR  

DEPT                              VARCHAR


To use DatabaseInput node for copying out the records, first create events based on the number of records in the Oracle database table.


Before running the flow, a simple database table needs to be created with following SQL command to store the Event IDs.

CREATE TABLE EVENTS (ID INTEGER)

 

The Event table records can be generated with a simple EventGenerator flow using a Compute Node as shown below. Compute node is configured with Datasource name (ORACLEDSN) to connect to the Oracle Database.



 ESQL Code in the compute Node.



In the above ESQL code, we first get the total number of records in the EMPLOYEE table and then based on this number event records get inserted into EVENTS table for the DatabaseInput node.  We create one event record for 1000 rows in the table ie 1000 events for 1 million record.

 

Now another flow is created with a DatabaseInput node and a compute node to process the records in the EMPLOYEE table. Two datasources are defined to connect to the Oracle and DB2 databases.


In the DatabaseInput node following ESQL statements are added to handle the event records and it is also configured with additional instances to get the events processed parallelly.

 

ReadEvents:

   SET NewEvents.Event[] = SELECT EVENTS.ID AS Key FROM Database.EVENTS;

 

EndEvent:

  DELETE FROM Database.EVENTS where EVENTS.ID=DispatchedEvent.Key;

 

 Compute Node is configured with ORACLEDSN as the Data source name. Following ESQL statements in the Compute node get the records from the oracle EMPLOYEE table and insert the same records to the DB2 table.


 During the processing of Event with ID '1', the flow copies the first 1000 records. For ID '2', records 1001 to 2000 will be processed. Events are getting processed parallelly by the number of threads configured as the additional instances of the DatabaseInput node.

 

Please note, the select query to retrieve records based on the ROWNUM may be different for different databases.  So, the ESQL needs to be modified accordingly. Following commands can be used in Oracle and DB2 to retrieve records based on ROWNUM value.

 

Oracle -> 'SELECT * from (select m.*, rownum r from EMPLOYEE m ) where r >= ?  and r <= ?'       

DB2 -> 'SELECT * FROM EMPLOYEE WHERE ROWNUM BETWEEN ? AND ?'

 

Once the execution of the flow is completed EMPLOYEE table in the DB2 database should be having all the records present in the EMPLOYEE table of Oracle database with FIRSTNAME and LASTNAME merged to NAME column.

Summary: 


This article explains how to copy data from one database  to another database with the help of DatabaseInput node. 



0 comments
5 views

Permalink