OraFAQ Forum: General » New to Oracle -- desperately seeking advice

Home » Other » General » New to Oracle -- desperately seeking advice

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

New to Oracle -- desperately seeking advice [message #270719]

Thu, 27 September 2007 18:47

magicofbeliving
Messages: 2
Registered: September 2007
Location: Northeast

Junior Member

My company is just starting to move from a legacy mainframe COBOL system to Oracle. A question that continues to come up is how do we know that a data file with 100 records sent from the mainframe is received inbound with 100 records to Oracle? In the past we have had issues where our network has hiccuped and 'lost' either entire files or just parts of the file.

Has anyone else dealt with an issue like this?

How did you go about file verification?

Is there any best practice we should be following?

Any guidance is greatly appreciated! Embarassed

Report message to a moderator

Re: New to Oracle -- desperately seeking advice [message #270720 is a reply to message #270719]

Thu, 27 September 2007 19:17

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

There are numerous techniques.

Here's one:
Number your files. In the normal process on the Oracle box, store that number in a table in your Oracle system along with the date-time of the start and end of the load and the number of records processed. But this standard behaviour, in your script comes after the verification that the number of the file that is being processed is 'one more' than the maximum that exists in the table of processed files.

If you want to, you can send a small or zero length file back to the COBOL box with the same first part of the file name but with a different extension to signify that the file has been processed successfully. For example, the data file would be A0001234.DAT and the acknowledgment would be A0001234.ACK. If you wished, when the Oracle process finds that a file is missing it could send a file like A0001233.RET to indicate that the COBOL system should resend file number 1233.

The COBOL box, upon the receipt of the ACK file could remove its copy of the file (or those that are 10 or 20 lower in the series), and upon the receipt of a RET file could either send its copy of the file again or re-extract the data and transmit it.

David

Report message to a moderator

Re: New to Oracle -- desperately seeking advice [message #270743 is a reply to message #270720]

Thu, 27 September 2007 22:59

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

David's suggestion of file numbering to ensure that all expected files are received is a good one.

Two other issues are:
- How to stop Oracle from reading the file until COBOL has finished sending it.
- How to make sure the file transfer did not hiccup.

The most common techniques I have seen for these are:

- Get COBOL to send a zero-byte ("indicator") file only AFTER it has finished successfully sending the main file. Then program Oracle to IGNORE the data file until it sees an indicator file that is NEWER than the data file; this means the data file is complete and ready to read.

- Get COBOL to include a trailer record in the data file that contains a rowcount and a checksum (tricky with EBCDIC vs ASCII)of the detail records. The receiving process (Oracle) counts and hashes the detail records and validates against the trailer record.

Ross Leishman

Report message to a moderator

Re: New to Oracle -- desperately seeking advice [message #270748 is a reply to message #270719]

Thu, 27 September 2007 23:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>- How to stop Oracle from reading the file until COBOL has finished sending it.
Another approach is to write the file into a "working" directory & after the has been closed rename the file into the folder where Oracle can find/access it.

Report message to a moderator

Re: New to Oracle -- desperately seeking advice [message #270809 is a reply to message #270743]

Fri, 28 September 2007 01:28

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

As Ana said, write the file to a working directory and in the next step of the job places the file in the transfer directory.

All data files should have both a header and a trailer record indicating things like file type, date of creation, number of records (by category and total).

You need to decide which system is the controlling system and that is the only system that sends the file acknowledgements and retransmission requests.

David

Report message to a moderator

Re: New to Oracle -- desperately seeking advice [message #271205 is a reply to message #270719]

Sun, 30 September 2007 16:39

magicofbeliving
Messages: 2
Registered: September 2007
Location: Northeast

Junior Member

Thanks for all the good suggestions. I have requested that each file being sent, send a second file indicating date of creation and number of records. The issue I am having is that management believes (wrongly) that there will be no changes needed to the legacy systems and they are poo-pooing my fears of data loss.
I like the idea of having Oracle send a transmission back to the mainframe -- right now it looks like everything will be FTP'd -- but that is still going to require mainframe development, something management is trying to avoid.

Anyone else out there have any ideas -- or have you dealt with this situation before and how did you get around it?

Report message to a moderator

Re: New to Oracle -- desperately seeking advice [message #271533 is a reply to message #270719]

Mon, 01 October 2007 22:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I can't say I've been impacted by network gremlins similar to yours.

If the problem you are trying to solve involves random & intermittent network disruptions, then copy the collection of files into two different target directories & compare checksums.

IMO, you are attempting to solve a problem which does not exist on a TCP/IP based network. The File Transfer Protocol (FTP) guarentees successful delivery or an error is raised.

Hope This Helps.

Report message to a moderator

Re: New to Oracle -- desperately seeking advice [message #271788 is a reply to message #271533]

Tue, 02 October 2007 23:00

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

I would avoid sending anything back to the COBOL machine.

Sending something back implies that they will do something with it. The fact is, YOU want the data, THEY are giving it to you grudgingly. Asking THEM to do anything more than give you the data you want is wishful thinking.

In my experience, it is best to keep as much of the hand-shaking with the receiving system as possible.

I've also seen implementations like @anacedent described - where the sending party renames the file after it is sent. That's a great technique (preferable even!) if you can get the source system to use it. Sadly many will restrict themselves to send-only operations as they have a semi-automated wrapper around FTP with limited capabilities.

Ross Leishman

Report message to a moderator

Previous Topic:	table size
Next Topic:	Shared Pool

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 28 06:59:22 CDT 2023