What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

-- Download Significance of Overflow option? as PDF --


“Overflow” is an option used for error handling.

You cannot expect quality(valid) data everytime, sometimes we might get bad rows also, hence an error would occur while loading the data. To handle such kind of rows “Use overflow file” is the best option.

EX:-  Suppose you have a unique constraint on target table, you need to load only distinct records, but your’e getting duplicate records from source, you want to reject these duplicate records into a file.

Now, you can capture those erros by selectng “Use overflow file” in target table options tab. Errors will be logged or written to a file in a specified location.

For File name, you can specify like this,

Filename : C:\Reject_records.txt

There are two options avialbe in File Format  Write_data and Write_sql

  • If you select write_data, data will be written in normal format along with the error number & opcode
  • If you select write_sql, data will be written as SQl insert statements in your specified location
overflow01

The beauty of this option is, job doesn’t terminate amidst execution when an error occurs. Error will be handled by this option and all the bad rows were captured and logged into a file. Monitor window displays “job execution is completed successfully” , and error log displays the error cause & bad rows.

overflow11

you can see rej_rec log also, the bad rows were written into the overflow file.

Reject_records

If there is no error then the overflow file will be empty at the specified location

What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

3 comments on “Significance of Overflow option?

  1. But what if we require the job to alert the Operations team and have an overflow file? From what I have seen, this is not possible, unless there is something I missed.

    • The blog post that I made was to demonstrate how to use Overflow file option. For the most part we use third party scheduling tools which in turn sends failure or success notifications to operations team. However, there are alternative approaches to alert operations perhaps job fails.

      Cheers,
      Sudheer

Leave a Reply

Your email address will not be published. Required fields are marked *


*