When we run a session, the integration service may create a reject file
for each target instance in the mapping to store the target reject
record. With the help of the Session Log and Reject File we can identify the cause of data rejection in the session.
Eliminating the cause of rejection will lead to rejection free loads in the subsequent session runs. If the Informatica Writer or the Target Database
rejects data due to any valid reason the integration service logs the
rejected records into the reject file. Every time we run the session the
integration service appends the rejected records to the reject file.
For reading the bad file the best method is to copy the contents of the bad file and saving the same as a CSV (Comma Sepatared Value) file. Opening the csv file will give an excel sheet type look and feel. The firstmost column in the reject file is the Row Indicator , that determines whether the row was destined for insert, update, delete or reject. It is basically a flag that determines the Update Strategy for the data row. When the Commit Type of the session is configured as User-defined the row indicator indicates whether the transaction was rolled back due to a non-fatal error, or if the committed transaction was in a failed target connection group.
If our source file is like this:
7,John,5000.375,76.12345,,BrickLand Road Singapore,Malasia
The data in the reject/Bad file looks like:
0,D,7,D,John,D,5000.375,O,,N,BrickLand Road Singapore,T
Now let us understand What are the extra characters meant for?
If a row indicator is 3, an update strategy expression marked the row for reject.
Now comes the Column Data values followed by their Column Indicators, that determines the data quality of the corresponding Column.
The reject file and session log contain information that helps you determine the cause of the reject. You can correct reject files and load them to relational targets using the Informatica reject loader utility. The reject loader also creates another reject file for the data that the writer or target reject during the reject loading.
Complete the following tasks to load reject data into the target:
Locate the reject file.
Correct bad data.
Run the reject loader utility.
NOTE:
1. You cannot load rejected data into a flat file target
2. If you enable row error logging in the session properties, the Integration Service does not create a
reject file. It writes the reject rows to the row error tables or file.
Hope this helps.
Working with Informatica Bad Files or Reject Files
By default the Integration service creates the reject files or bad files in the $PMBadFileDir process variable directory. It writes the entire reject record row in the bad file although the problem may be in any one of the Columns. The reject files have a default naming convention like [target_instance_name].bad . If we open the reject file in an editor we will see comma separated values having some tags/ indicator and some data values. We will see two types of Indicators in the reject file. One is the Row Indicator and the other is the Column Indicator .For reading the bad file the best method is to copy the contents of the bad file and saving the same as a CSV (Comma Sepatared Value) file. Opening the csv file will give an excel sheet type look and feel. The firstmost column in the reject file is the Row Indicator , that determines whether the row was destined for insert, update, delete or reject. It is basically a flag that determines the Update Strategy for the data row. When the Commit Type of the session is configured as User-defined the row indicator indicates whether the transaction was rolled back due to a non-fatal error, or if the committed transaction was in a failed target connection group.
If our source file is like this:
7,John,5000.375,76.12345,,BrickLand Road Singapore,Malasia
The data in the reject/Bad file looks like:
0,D,7,D,John,D,5000.375,O,,N,BrickLand Road Singapore,T
Now let us understand What are the extra characters meant for?
List of Values of Row Indicators:
Row Indicator | Indicator Significance | Rejected By |
0 | Insert | Writer or target |
1 | Update | Writer or target |
2 | Delete | Writer or target |
3 | Reject | Writer |
4 | Rolled-back insert | Writer |
5 | Rolled-back update | Writer |
6 | Rolled-back delete | Writer |
7 | Committed insert | Writer |
8 | Committed update | Writer |
9 | Committed delete | Writer |
Now comes the Column Data values followed by their Column Indicators, that determines the data quality of the corresponding Column.
List of Values of Column Indicators:
Column Indicator | Type of data | Writer Treats As |
D | Valid data or Good Data. | Writer passes it to the target database. The target accepts it unless a database error occurs, such as finding a duplicate key while inserting. |
O | Overflowed Numeric Data. | Numeric data exceeded the specified precision or scale for the column. Bad data, if you configured the mapping target to reject overflow or truncated data. |
N | Null Value. | The column contains a null value. Good data. Writer passes it to the target, which rejects it if the target database does not accept null values. |
T | Truncated String Data. | String data exceeded a specified precision for the column, so the Integration Service truncated it. Bad data, if you configured the mapping target to reject overflow or truncated data. |
The reject file and session log contain information that helps you determine the cause of the reject. You can correct reject files and load them to relational targets using the Informatica reject loader utility. The reject loader also creates another reject file for the data that the writer or target reject during the reject loading.
Complete the following tasks to load reject data into the target:
Locate the reject file.
Correct bad data.
Run the reject loader utility.
NOTE:
1. You cannot load rejected data into a flat file target
2. If you enable row error logging in the session properties, the Integration Service does not create a
reject file. It writes the reject rows to the row error tables or file.
Hope this helps.
interesting piece of information, I had come to know about your web-page from my friend pramod, jaipur,i have read atleast eight posts of yours by now, and let me tell you, your blog gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a million once again, Regards, informatica online training
ReplyDeleteThanks Dinesh and Pramod for following my blog.
DeleteNaresh