Capturing Records Rejected due to Load Option

bi_fujitsu · Post by **bi_fujitsu** » Wed Apr 25, 2007 12:41 am

Hi,

I am loading records into my 26 staging table using Load /Append option. The source for all the staging tables is their respective datasets and my taget is Oracle. For some of the staging tables all the records don't get inserted into the target table where as the Performance statistics shows that the all the reocrds got loaded and also their are no errors in the log. But when i go to the folder where the sql loader logs gets generated it tells me the exact reason why the records got rejected. Since i am using the load/append option i can't have the reject link.

I want that if for any table a bad sql loader log file is generated it should send a warining mail to the ETL Team. For all these 26 tables the log files gets generated in to folder ds01 & ds02 as i have a two node file. But by just looking at the names of these log files i can't make out which log files belongs to which Staging Table untill i open it. Is there any way that i can capture the corresponding names of the log files and generate a warning mail.

Thanks

DSguru2B · Post by **DSguru2B** » Wed Apr 25, 2007 7:09 am

You can probably script it. After every table load, have an after job subroutine fire off a shell that cats the file and greps for the appropriate word that identifies the table. Look for particular warnings and if it exists, send out an email from the script itself.

csrazdan · Post by **csrazdan** » Wed Apr 25, 2007 2:22 pm

First of all Performance statistics on a link does not a whole lot, because it only indicated how may rows passed through that link. It does not mean that may records were loaded to the database.

You should set APT_ORA_WRITE_FILES parameter with the directory you want SQL Loader to created CTL, LOG and BAD files.

While I was writing this post a I got a crazy thought... see if it make sense... Create a directory on "SQLLoadFiles" on your file system. Under this create a subdirectory same as table name say XYZ. For the job which is loading to XYZ table, add APT_ORA_WRITE_FILES parameter to this job and set value as /SQLLoadFiles/XYZ. Run the job and if the records are rejected it will create a bad file in /SQLLoadFiles/XYZ directory. Create subdirectories under /SQLLoadFiles for your other tables.

You and also create a load strategy based on number of record count before and after the load to identify a problem.

Hope it helps...