Direct read / load

Shell Programming and Scripting
1

Guys,

I wrote a script to do the following -

  1. Read a file that is placed into a directory and depending upon the contents, load into one or multiple tables into Oracle.
  2. After the file is loaded into the table, depending upon the exit status of the process I will either delete the file or mark the record entry for that file as incomplete record in the table.

Initially, I was told that there would only be few files in a 15 minute time span and I cron-ed this script to run for every 5 mins and it was fast enough to load it in few minutes. Now, they are sending huge files and on couple occasions, the script ran longer than 10 minutes.

Now, Im running into an issue that I cant seem to grapple with on multiple levels.

  1. First, how do I find which records ran and which one did not ?
  2. How do I identify as to which records got completed and which are pending and which one did not even process ?
  3. how do I implement some checks and balances so that whatever is placed in the directory is loaded into the table.

I am sure a great many of you might have ran into this and want to learn as to what could be the best practice here. I am thinking of implementing a thread based approach but I never wrote thread programming in POSIX.

Please advise

2

I presume that you loading script has something like:-

for file in *
do
   sqlload connection $file to table options
done

If you have your data that is to be loaded in a directory /data then when you script runs, it could:-

  1. List (and remember) the files it is going to work with
  2. Move all those files into a sub-directory /data/loading
  3. Process the files listed above now in /data/loading moving them to /data/loaded or wherever afterwards.

In this way, you can have the script running multiple times but they will load separate data files. Of course, you still have to consider:-

  • What if there is a duplicate file name?
  • What if there is a duplicate target table name?
  • What to do with failures? (i.e. file remains in /data/loading)

You could also just slow dow the frequency of your cron job I suppose......

I hope that this logic might help.
Robin

3

rbatte1, thanks a bunch for taking time and writing back to me on this. Let me try my way around this here...

4

Let us know how you get on or if I have just caused more confusion.

All questions welcome and someone here should be able to help.

Regards,
Robin