Reset Sequence Generator Initial value through Informatica Repository DB schema

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;

ETL: How to select one row for a set of columns, in Informatica ?

There was a Informatica design problem i had to solve, as part of the solution i had find a way to select one row for a set of columns.
Here's the scenario

Col1 Col2 Col3 Col4
A      1       a1     a11
A      1       a1     a11
A      2       a1     a11
A      2       a1     a11
B      2       b2     b22

I have to select one record for a set of values in Col1 and Col2.

Solution - Use an Aggregator transformation, Select Col1 and Col2 as group by column. An aggregator transformation will select one record for the selected group by columns, the last record by design.

The output of the Aggregator transformation will be like this.


Col1 Col2 Col3 Col4
A      1       a1     a11
A      2       a1     a11
B      2       b2     b22

Note - We cannot achieve this by using a group by function in DB, thus making this feature of Aggregator unique.