Home » SQL & PL/SQL » SQL & PL/SQL » Identifying parent data source in downstream application
Identifying parent data source in downstream application [message #273248] Tue, 09 October 2007 13:36 Go to next message
diku
Messages: 23
Registered: April 2007
Junior Member
Hi,

Before I even begin I know this would quite a bulky post and I apologise.

Oracle 10G R2

I have an awkward situation here(or so I believe)

I have an application which has input from two systems:
a DB2 and SQL Server probably.I don't know for sure but that's secondary. What's important is there are two ways/servers which can send me data.They do so periodically and as a rule they both MUST do so, which means, for every SQL Server input file there would be a DB2 input file too and vice-versa
And this has to be loaded onto my Oracle d/b.

Now I'm faced with a situation wherein some developer has loaded data from one of these and I have no way to find out.He could have missed out the SQL Server file or the DB2 file.The system does not record the input source. The problem now is I cant query the DB2 or SQL Servers.And I have to find out which set of record have been loaded and which of them is pending.

Now I don't know if this issue is really important but my lead asks me to develop a procedure/application to fix this kind of issues. Of course I suggested having an additional column or some kind of an identifier with the data source but he does not buy it.

I want to know if there is any way I can find out which set of data has been loaded and which one is pending.It does not help that the two systems could have around 90% of their records similar.
The only solution I can think of is loading any one set of files all over again and check for duplicate records.The problem is it'd take an eternity to load .

I have broken my head yesterday trying to think of a possible solution and strongly suspect this is just one more of those stupid assignments they think of when there is no work.

So is there any way I can find out which set of data has been loaded except the tedious and time consuming way?

Thanks for your patience.
DIKU

Re: Identifying parent data source in downstream application [message #273249 is a reply to message #273248] Tue, 09 October 2007 13:54 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I am assuming the file structure are the same between Sql server and DB2.

You can try creating an external table pointing to a filename say for example (a.txt). Copy the source file (one after the other) to a.txt. Try to do a query against the parent table with the external table which you have created. Get the count.

If the file structures are different then you have do it with two external tables. I can definitely tell you this is a lot simpler and better than reloading the same file and check for duplicates.

Hope that helps.

Regards

Raj

Re: Identifying parent data source in downstream application [message #273252 is a reply to message #273248] Tue, 09 October 2007 14:18 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
IMO, this is NOT an Oracle problem.
You have a flawed/broken procedure & you are hoping to use Oracle to correct problems resulting from faulty procedure.

In as much detail as you can provide, what happens to have Oracle populated with both DB2 & SQL Server data under ideal conditions?

In the abstract, can you create & deploy a LOGON trigger in Oracle?
Re: Identifying parent data source in downstream application [message #273256 is a reply to message #273252] Tue, 09 October 2007 14:44 Go to previous messageGo to next message
diku
Messages: 23
Registered: April 2007
Junior Member
Quote:

You have a flawed/broken procedure & you are hoping to use Oracle to correct problems resulting from faulty procedure
Yes Ana. You are pefectly right in your judgement.

This is a glowing fault in the design and I just happened to point out that such a situation could occur. It has been running fine for a couple of months but somehow everyone overlooked such a possibility. And they dont want to have a minimal code change because we are in production.And once up and running some people would never want to improvise their systems. My team just happens to be one such adamant one.
Quote:

In the abstract, can you create & deploy a LOGON trigger in Oracle?
BTW, I dont see how a LOGON trigger would be helpful.
Could you please guide me a bit further?

Under ideal conditions, no file should be missed. bothe SQl Server and DB2 inputs should reflect in the d/b even though there are duplicates. I guess they later purge out the duplicate records.

Quote:

Try to do a query against the parent table with the external table which you have created. Get the count.

I wish it was that simple. These inputs are from upstream and I dont have control over it. I cant even query them for Christ sake.

Still scratching my brains Embarassed

DIKU



Re: Identifying parent data source in downstream application [message #273259 is a reply to message #273248] Tue, 09 October 2007 14:50 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
In as much detail as you can provide, what happens to have Oracle populated with both DB2 & SQL Server data under ideal conditions?

In as much detail as you can provide, what happens to have Oracle populated with both DB2 & SQL Server data under ideal conditions?

In as much detail as you can provide, what happens to have Oracle populated with both DB2 & SQL Server data under ideal conditions?

I/we need details about the actual process to have ANY chance at providing a solution.
Re: Identifying parent data source in downstream application [message #273260 is a reply to message #273256] Tue, 09 October 2007 14:54 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
What do you mean by
Quote:

I cant even query them for Christ sake
Can you bit more clear on the above statement.
Re: Identifying parent data source in downstream application [message #273263 is a reply to message #273259] Tue, 09 October 2007 15:03 Go to previous messageGo to next message
diku
Messages: 23
Registered: April 2007
Junior Member
Quote:

In as much detail as you can provide, what happens to have Oracle populated with both DB2 & SQL Server data under ideal conditions?

I thought I had answered it but all I said in my last post was that no file is missed in an ideal condition.

What happens right now is upstream applications dump all files in a common network folder. There are two sets of files from two separate applications but there would always be both sets. That much is guranted. Now an Unix Cron job scans through the folder and soon as it finds the two sets of as-yet-unprocessed files ; it schedules a run of the individual load procedures. What complicates matters is there are two different load procedures; one for each file type. There is no telling which one would run first or if at all they run and LOAD data successfully

I am only able to view the final result set which is where Oracle comes to the picture.

Do let me know if this information is sufficient

[Updated on: Tue, 09 October 2007 15:24]

Report message to a moderator

Re: Identifying parent data source in downstream application [message #273267 is a reply to message #273260] Tue, 09 October 2007 15:20 Go to previous messageGo to next message
diku
Messages: 23
Registered: April 2007
Junior Member
Quote:

I cant even query them for Christ sake
The intelligent Laughing and so called secure data flow Laughing is such that as downstream users we wont even know who put those records in the first place, when or even how.

We don't know what tables they come from except that they have come. In short, we cant query those severs to find out what's happening. We only see data once it's given to us and have minimal idea of the origin.

I know this is confusing and outright stupid but that's how some wise guy devised it

DIKU

[Updated on: Tue, 09 October 2007 15:21]

Report message to a moderator

Re: Identifying parent data source in downstream application [message #273271 is a reply to message #273248] Tue, 09 October 2007 16:01 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Now an Unix Cron job scans through the folder and soon as it finds the two sets of as-yet-unprocessed files ; it schedules a run of the individual load procedures

This script has a bug or otherwise you would not have the case where only 1 side of the data is loaded.
If/when you find & fix the bug, then Oracle data will be complete & you will have noting to do.
These script can be enhanced to log to a flat file when the load scripts are launched.

You could create a LOGON trigger to insert a record when each of the "individual load procedures" start to load the data; including current timestamp.
Based upon timestamps you should be able to detect & report when only 1 of the 2 occurs.
Also the log file written above can be treated as an EXTERNAL table & you can compare timestamps in it with the timestamps INSERTed by the LOGON trigger.
Re: Identifying parent data source in downstream application [message #273277 is a reply to message #273271] Tue, 09 October 2007 16:24 Go to previous messageGo to next message
diku
Messages: 23
Registered: April 2007
Junior Member
Thanks for the insight Ana.
Let me sell this to them.

The stumbling block is they dont have one simple procedure to load. They have two different procs and they run independent of each other. May be we can change them to have one single proc call both of them in turn. And we can have a log file on the calling procedure.
I dont think they would appreciate this but let me go ahead.
Sometimes people fail to see reason and I fail to see why they do so Laughing

But I'd still like to have a final word:
Is this possible from Oracle side? I guess no; but maybe some one else might have an idea
Re: Identifying parent data source in downstream application [message #273279 is a reply to message #273248] Tue, 09 October 2007 16:33 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You could create a LOGON trigger to insert a record into an UPLOAD_AUDIT table when each of the "individual load procedures" start to load the data; including current timestamp.
Based upon timestamps you should be able to detect & report when only 1 of the 2 occurs.
>Is this possible from Oracle side?
See above!

If the data is NOT 100% identical when comparing DB2 data to SQL Server data, then programtically determine which data set is missing after it gets loaded.
Re: Identifying parent data source in downstream application [message #273280 is a reply to message #273279] Tue, 09 October 2007 16:50 Go to previous message
diku
Messages: 23
Registered: April 2007
Junior Member
Quote:

If the data is NOT 100% identical when comparing DB2 data to SQL Server data, then programtically determine which data set is missing after it gets loaded.


How can I do this? I'd only have the following information:

a)Any one file has been processed and data has been loaded
b)I cant do a compare of my Oracle data with that from DB2 or SQL Server
c)I wont even have the record count
d)My records could be similar

So as you point out I'd need that timestamp or an Update_Audit table either way.
Addressing it at the Unix level seems to be the logical and easier way to do it.And why should I break my haed anyway over an outright stupid design?

Let me have a go at this and I'd drop an update
Previous Topic: Help me - Data Fix
Next Topic: ORA-01722: invalid number
Goto Forum:
  


Current Time: Sat Dec 03 08:06:58 CST 2016

Total time taken to generate the page: 0.04637 seconds