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.

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.

Summary: