Scenario 5: Regular Data Load

In this scenario, we're going to use DMT to regularly export orders from several sales system and load them into a fulfillment system.

Let's say you have several sales system taking orders automatically and you need to transfer them regularly into a separate fulfillment system.

For this example we'll have 2 sales systems:

  • One based on a DB2 database from which we want to export orders hourly
  • One that produces order files daily in CSV format

The fulfillment system is based on an Oracle database.

Note: For our example, we chose an Oracle and a DB2 database and a CSV file format but this would work the exact same way with any other database or any other supported file format.

You can easily implement this scenario with DMT:

Scenario 5: Regular Data Load

Step 1: Create the Export Job (in the Export Tab)

  • Create an Export job to export orders from the DB2 database
    • Name: Export DB2 Orders
    • Database Connection: Connection to the DB2 database
    • Data File: Orders2.csv
    • Scheduling Frequency: Continually
    • Every: 01:00:00 (HH:mn:ss)
    • SQL Statement: Enter the SQL statement to export orders information

    • e.g.
      SELECT ORDER_ID, CLIENT_ID, PROD_ID, QTY FROM ORDERS WHERE PROCESSED = 'N'

    Note: We only need one export job since the other system already supplies order files containing the data.

    Once the export job is created, you can validate it to make sure it's error free and ready to run (right click on the Job then "Validate Job").

Step 2: Create the Load Group and Jobs (in the Load Tab)

  • Create a Load Group to load orders into the oracle database
    • Name: Load Orders
    • Group Type: Logical Group (i.e. do not select any other type)
    • Note: The group is only a container for the load jobs and has no particular functionality
  • In the Group, create a Load Job to load the DB2 orders
    • Name: Load DB2 orders
    • Linked to Ext.: Select "Export DB2 Orders" job to automatically link to the Legacy Orders2.csv data file produced by the Export job
    • Database Connection: Connection to the Oracle fulfillment database
    • Scheduling Frequency: Select After Export so the load will be triggered just after the export
    • Dest. Schema/Table: TEST / ORDERS
    • Load Mapping: Enter the mapping for each column from the data file to the destination table e.g.
      • ActiveDest. Column
      (in dest. database)      		Value
      (from src. data file)      		Comments
       YesIDORDER_ID
       YesCLIENTCLIENT_ID
       YesPRODUCTPRODUCT_ID
       YesQTYQTY
  • In the Group, create a Load Job to load the other orders (from the CSV file)
    • Name: Load CSV orders
    • Database Connection: Connection to the Oracle fulfillment database
    • Data File: ExternalOrders.csv
    • Pre-Condition: File ExternalOrders.csv is present
    • Scheduling Frequency: Continually
    • Every: 00:30:00 (HH:mn:ss)
    • Dest. Schema/Table: TEST / ORDERS
    • Load Mapping: Enter the mapping for each column from the data file to the destination table e.g.
      • ActiveDest. Column
      (in dest. database)      		Value
      (from src. data file)      		Comments
       YesIDORDER_NUM
       YesCLIENTCLIENT_NUM
       YesPRODUCTPRODUCT
       YesQTYQUANTITY

Note 1: Both jobs load data in the same destination columns, but the data file column names are slightly different between the 2 data files.

Note 2: You can enhance the process by deleting data files after they've been loaded (see advanced parameters).

Note 3: Because we don't know when the data file ExternalOrders.csv is delivered by the external system, we run the "Load CSV Orders" job every 30 minutes and use a run pre-condition to make sure the data file is present.

Note 4: Before you can enter load mappings for the load jobs, you need to look up the column definitions for the data file and/or the destination table.

Once the load group and jobs are created, you can validate them to make sure they are error free and ready to run (right click on the Group then "Validate Group").

Step 3: Start the Scheduler (in the Scheduling Console Tab)

Once the Export and Load Groups/Jobs are created, you can let the scheduler run them automatically. Just go to the scheduler Tab and click the Start button.

When the scheduler is started:

  • The Export job will run hourly at the specified time
  • The run of the "DB2 Orders" load job will be triggered automatically after each export job run
  • The "CSV Orders" load job will start every 30 min and will only run when the orders.csv is delivered

Note: It's always a good idea to run groups or jobs manually at least once before scheduling them.

 

How can DMT fit
your requirements?

 

    Just ask us!    

Usage Scenarios

Facebook Twitter YouTube