SQL Job fails only once in a week

[nitin4dba]

In my present org, they have a very strange problem going on.

A SQL Job (that pulls data from Oracle tables to SQL tables) works great from Sunday to Friday but fails only on a Saturday (the job time is 1:15am - 1:30am)

If the job is working all 6 days itself means: No problem with the steps or the schedule of that particular job (from Sunday to Friday).
Another problem could be: Not able to perform the job if there is some clash of network maintenance / database maintenance work going on.

Both Developers were able to remote access the database manually (using TOAD) or ping the database manually during the specified time period. As they are able to access the db, they have not thought about this to be a network / connectivity issue.

I have recommended them to go through the logs (history) of the executing job / failed job.

Any other possible thoughts that you can think of ?

Thanks in Advance,
Nitin.

[Bru Medishetty]

If there is no network related issues, I would be interested to investigate if there are any Maintenance Jobs going on the databases on either end (SQL an Oracle) during that time frame.

As you have mentioned, you will have to look at the Job history to find out more..

[guptaa2]

There could be deadlock of your job with some other maintainace job which may be using the same tables.

For deadlock issues you can put frequent commit statements after certain number of records say 100 or 500.

In our Mainframe DB2 envoirnment , we also use have to these deadlock scenarios and we had put frequent commit statements and these issues are not coming up now.

We also have a job that runs that gives a trace of all log history. You would also need to refer to SQL logs for more understanding.

This is what I can think of

Thanks and Regards
Ankur Gupta