Data loading

pts.
Tags:
DataCenter
The Mainframe systems put a file out each night containing data that needs to be downloaded to Oracle Daily INITIAL LOAD The Input file (from the mainframe) is read and all records are put into as "shadow" file which is kept out on the server. This "shadow" file is read and each Row is inserted into the database. SUBSEQUENT LOADS The Input file (from the mainframe) is read in and each record is compared with the "shadow" file from yesterday's run. IF the Row is not on the shadow file THEN it is placed in an "INSERT" file IF the Row is on the shadow file THEN each field is compared IF there is any difference in any of the fields This Row is placed in an "UPDATE" file IF there are no differences Ignore this Row IF the Row is on shadow file - but NOT on the input file THEN this Row is put in the "DELETE" file Now, we read these 3 files in (INSERT, UPDATE, DELETE) and take them against the actual Database. We first the "INSERT" file. A select statement is done to verify that the Row is not already on the database. IF it is already on the database THEN the proceed to the update section of the program ELSE They INSERT the Row. We read the "UPDATE" file A select statement is done to verify that the Row is still on the database. IF it is NOT on the Database, They go back to the Insert Routine IF it is on the Database They compare each field to see IF there are any differences IF there are differences An UPDATE statement is performed setting all fields that changed IF there are no differences Ignore this Row. We Read the "DELETE" file A select statement is done to verify that the Row is still there. IF it is still on the Database, DELETE the Row IF it is not on the database Ignore the Row. Let me know the best methodology within oracle 9i With Regards, Prashant Shah

Answer Wiki

Thanks. We'll let you know when a new response is added.

Your are doing three times the same job. First of all I think you don’t need the delete, insert, update files. Simply compare the new file downloaded from main-frame with the “shadow”, if it is new INSERT in the table cheking the SQLSTAE for duplicate key (maybe you need to create a primary key or unique key by the fields that assures you unicity), if the row exists, UPDATE whitout comparing fields because this duty takes time, in case you get a non existing row (SQLSTATE 02000) then INSERT the row.

For deleted rows simply DELETE it verifying SQLSTATE (I know that you ignore the row) send a message.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • nicholasnic
    what is a primary key?

    20 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following