Using an Error Bitmap
When running any mapping many things can go wrong and this usually results in several attempts to run a mapping with gradually improving source data. For instance, the first run some character conversion fails and the record is written to the error file. This is noticed and the source data is adjusted. When running the mapping again another error is detected and the record is again written to the error file. It is much more convenient to derive all the possible errors in one run, with an error bitmap.
The idea behind the error bitmap is that one number shows which errors have occurred for that particular row. By doing this only one run is required to see all the errors which have been checked for that particular mapping and record. The result of the error bit expression is stored in an extra audit attribute (ERROR_CODE, numeric)
The basic principle is as follows:
Possible error situation Bit position
For each possible error the expression checks if this is valid for the current records. If this is true the corresponding bit position (a multiplier of 2) will be selected. The eventual error code is the sum of these bit positions.
For example, if the errors 2 and 6 indeed occur, the error code will be 34 (2 + 32).
This concept/example can also be shown as (hence the bitmap):
00100010 (read from right to left)
This shows us that error situation 2 and error 6 have occurred for that particular record.
The implementation of the error bitmap concept is done by creating a (reusable) expression, procedure or code with inputsfor each possible error situation (0 or 1). Each situation is tested separately in the mapping expressions and the error bits are linked to the reusable error handling expression. Within this expression each bit is multiplied with the bit position thus resulting in an error bitmap. This process is displayed in the following two pictures (examples in Informatica Powercenter)
Inside the error handling expression.
After storing the error codes the corresponding error description can be combined from a reference table which contains the possible error codes. This reference table contains the bitmap values (numbers!) with the corresponding error description.
By joining the error record from the mapping with the error code as stored in the reference table (bitwise join) you can show what errors have been detected for the record. The query to be used is different for each database system, providing the RDBMS provides this option. I know that Oracle, DB2 and SQL Server have this option. An example is:
BITAND(reject.error_code,error_table.bit_mask_value) = error_table.bit_mask_value