Transferring data from one system to another

Posted on 2014-07-22
Last Modified: 2014-08-22
I have recently been thrown into a situation where I'm dealing with financial data (batch processing environment) that is transferred on a daily basis from a Mainframe COBOL system to (after some intermediate steps including ETL) an Oracle system.  As I am not in the IT department, all I really have direct access to is the dollars the Mainframe systems people say they transferred and the dollars the Oracle systems people say they received.  Those dollars may include multiple batches.  As ridiculous as this may seem, whenever there is a problem (for example, there is a multi-day delay in receiving a particular day's work in Oracle), the way the Oracle people are going about trying to find the data (that came into Oracle late)  is by searching Oracle batch reports for a batch or group of batches whose dollars add up to (or closely add up to) the amount the Mainframe people say they transferred. The batches (batch numbers) I am referring to are on the Oracle side only.  They are not batch numbers that are transferred by the Mainframe and captured by Oracle.  So they are of little if any use in cross-referencing between the two systems.

My question is:    What identifying data should be being transferred by the mainframe to Oracle to enable the Oracle IT department to identify which mainframe data was attempted to be transferred, what data actually got into Oracle and what data didn't get in?   The entire problem is currently the inability to match data that was sent with data that was received (and also what data was attempted to be sent but failed).  Thank you.
Question by:dbfromnewjersey
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 125 total points
ID: 40211873
There is a broad question.. Overall you are transferring data between two systems.. but how the transmission takes place?? this does really matter to answer your question better.

Is it a file based mechanism... I don't know much of mainframes.. but oracle has all its data in only tables...!!

so if any thing is not there in table means, it has got stuck somewhere in transmission(failed or missed)

To overcome this type of problem probably you need to get the batch numbers from oracle itself, overall it should be like this

1. Mainframe System will ask for oracle to send out the batch number(oracle sequence may be, for suppose), oracle will generate batch number and will update it status as pending
2. Once you receive batch number from oracle , then use it as an acknowledgement and start sending data
3. Once Batch is Sent, Oracle will automatically see the batch number which has been sent and updates corresponding flag as received in Oracle

The steps which were posted above may seem to over burden the process a little bit, but it will help out in such cases where in you have dilemma/difference between systems.
LVL 38

Assisted Solution

by:Gerwin Jansen, EE MVE
Gerwin Jansen, EE MVE earned 125 total points
ID: 40211919
I would say transaction ID's - for each financial data 'record' to be transferred, a unique ID from the COBOL system should be transferred into the Oracle system, as a (source) reference.
LVL 29

Accepted Solution

MikeOM_DBA earned 125 total points
ID: 40214949
As a minimum, you need to control the batches with following numbers:

- Batch number
- Timestamp
- transaction (row) sequence# and/or identification
- Record (row) count per batch
- Sum total of amounts (DB/CR) per batch

At each step (stage) transferred, received, loaded, etc... you should verify the above numbers and reject batch if there is a difference.

Good luck!
LVL 48

Assisted Solution

PortletPaul earned 125 total points
ID: 40215634
There is also a need to understand the method of file handling (I'd be pretty confident this is file based, most M/F interfaces to non-M/F system work that way).

The initial file is probably placed in a fixed location. Where is that?
(nb: What happens if that location isn't available? there should be procedures for this)

Will the M/F produce a file even if there are no transactions to process?
What is the cycle? daily? is the file produced at the same time of day for each cycle?

What is the naming convention of the files? (this can be very important)

What is the file "format"? (e.g. csv?)

What does the recipient system do with the file? Does it get "moved"? to where?
(nb: What happens if that location isn't available? there should be procedures for this)

If the file gets "moved" is it moved according to status?
(e.g. to a "done" folder? to "reject" folder?)

How long are files retained in these locations?
What are the backup regimes?
Are the files archived?

Operations staff should have all of this documented. Where is that documentation?
(although getting operations staff to share documents can be a rare event)

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
join actual table rows based on the column 25 42
plsql job on oracle 18 106
how to use l_instance in the host command 7 34
update based on the value on another table 8 38
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question