In migration project the common scenario is the sequence.
It might be Trigger commonly used in Oracle DB.
Identity column in Sql server for Sequence.
Identity/Sequence Objects In DB2
For more information on how Identity or Sequence works please refer
For Triggers:-
Lets see how to handle the above scenario in Netezza.
To generate Sequence in query use the below syntax.
SELECT
(SELECT Nvl(Max(prod_dw_id),0)FROM product_dim)
+ Row_number() OVER (partition BY 'A' ORDER BY 'A') AS sequance,
rest OF the required columns
FROM your_table.
(SELECT Nvl(Max(prod_dw_id),0)FROM product_dim)
+ Row_number() OVER (partition BY 'A' ORDER BY 'A') AS sequance,
rest OF the required columns
FROM your_table.
If one want to do both insert and update then first flag the records for insert 'I' or Update 'U'.
Based on the flag increment the sequence for Inset flagged records and skip for update once.
Ex:-
SELECT CASE
WHEN flag = 'I' THEN
(SELECT Nvl(Max(prod_dw_id), 0) FROM product_dim)
+ Row_number() OVER (partition BY 'A' ORDER BY 'A')
ELSE '-1'
END AS sequance,
rest OF the required columns
FROM (SELECT CASE
WHEN prod_dw_id IS NOT NULL THEN 'U'
ELSE 'I'
END AS flag,
rest OF the required columns
FROM stg_prod
LEFT OUTER JOIN roduct_dim
ON prod_id = dim.prod_id) a
WHEN flag = 'I' THEN
(SELECT Nvl(Max(prod_dw_id), 0) FROM product_dim)
+ Row_number() OVER (partition BY 'A' ORDER BY 'A')
ELSE '-1'
END AS sequance,
rest OF the required columns
FROM (SELECT CASE
WHEN prod_dw_id IS NOT NULL THEN 'U'
ELSE 'I'
END AS flag,
rest OF the required columns
FROM stg_prod
LEFT OUTER JOIN roduct_dim
ON prod_id = dim.prod_id) a
To handle the same in Informatica
Get the MAX(PROD_DW_ID) from the (product_dim)table using lookup and use sequence generator or use expression transformation to increment the sequence from the MAX(PROD_DW_ID).
