Orders fulfilment: PL/SQL to DataStage EE translation.

[rkashyap]

We need help in rewriting/translating an Order fulfilment job from PL/SQL to DataStage.

Data Sources: Non-partitioned Oracle tables, along with their relevant columns are listed below

Code: Select all

ORDERS table: ORDER_NUM(PK1), ITEM_NUM(PK2), CUST_NUM, QTY_ORDERED
INVENTORY table: WHSE_NUM(PK1), ITEM_NUM(PK2), QTY_ONHAND

Existing Logic: (Simplified version, implemented in an Oracle package) Orders are fulfilled from different warehouses as per logic given below:
1. Read one row at a time from "Orders table".
2. Read "Inventory table" to select warehouses stocking ordered-item. Calculate "warehouse rank"(Rank depends on business rules and is used to prioritize between warehouses).
3. Update "Inventory table" to decrement QTY_ONHAND for the ordered-item at the warehouse.
4. Write to an output "Fulfillment file", following columns ... ORDER_NUM, ITEM_NUM and WHSE_NUM (which will be fulfilling the order).

Repeat steps 1 thru 4, till all the order have been processed.

Current state: Over the years, volume of orders has increased and Oracle server has become overloaded, consequently the above mentioned job is taking longer to run. We are planning to translate existing logic from Oracle to DataStage to take advantage of parallelism and newer/faster hardware.

Issue:In Oracle package, it is possible to perform selects and subsequent updates to "Inventory" and "Orders" table serially, thereby not double-dipping into same inventory held at a warehouse. However we are not sure, how to do this for an DataStage EE job.
Please advise.

[ArndW]

Just a quick thought - do your lookups to the inventory table as "sparse" lookups and your updates to the inventory table with an array/commit size of 1. That will ensure consistency within the job.

[rkashyap]

Thanks for your suggestion.

Code: Select all

       (Oracle)INVENTORY 
                   |
                   V
(Oracle)ORDERS--->LKP--->XFM--->INVENTORY (Oracle)
                (Sparse)  |
                          +---->FULFILLED_ORDERS (Seq)

Unfortunately latency between sparse lookup and subsequent update on inventory table is resulting in double dipping

[chulett]

updates to the inventory table with an array/commit size of 1.

Sounds like you may have missed that part.

[rkashyap]

Craig,
Thanks. Array/commit size is set to 1 ... I suspect that this issue may be due to presence of transformer stage between Lookup and (target) Oracle connector. I will test this theory next week.