Have you faced a situation where a Informatica mapping fails because the Sequence generator is inserting duplicate surrogate keys into the dimension?
The obvious way to fix it is to set the next_value in sequence generator in the Informatica mapping. Value of next_value > Max(surrogate_key_value).
What if we do not have access to Informatica mapping ? One way is to get into the informatica repository db and change it directly on the tables.
Step 1 - Connect to the Informatica repository schema
Step 2 - Find the Widget ID of the sequence generator
SELECT * FROM REP_WIDGET_INST where instance_name like '%<Sequence generator Name>%' and WIDGET_TYPE_NAME = 'Sequence' ;
Step 3 - Set the Next_value using the widget ID
UPDATE OPB_WIDGET_ATTR SET ATTR_VALUE = ATTR_VALUE + 100000000 WHERE ATTR_ID = 4 AND WIDGET_TYPE = 7 AND widget_id = <Widget_ID>;
COMMIT;
The obvious way to fix it is to set the next_value in sequence generator in the Informatica mapping. Value of next_value > Max(surrogate_key_value).
What if we do not have access to Informatica mapping ? One way is to get into the informatica repository db and change it directly on the tables.
Step 1 - Connect to the Informatica repository schema
Step 2 - Find the Widget ID of the sequence generator
SELECT * FROM REP_WIDGET_INST where instance_name like '%<Sequence generator Name>%' and WIDGET_TYPE_NAME = 'Sequence' ;
Step 3 - Set the Next_value using the widget ID
UPDATE OPB_WIDGET_ATTR SET ATTR_VALUE = ATTR_VALUE + 100000000 WHERE ATTR_ID = 4 AND WIDGET_TYPE = 7 AND widget_id = <Widget_ID>;
COMMIT;