Simultaneous Update
Posted: Wed Feb 23, 2005 2:34 pm
We are building a data warehouse.
We have Slowly Changing Dimension jobs that write to three (two if Type 1 SCD) sequential files:
Type II SCD:
If record has Type 1 Change
- write to 'Type 1 Update' file (to update all records by Natural Key)
If record has Type 2 Change
- write to 'Type 2 Update' file (to expire prior version by Surrogate Key)
- write to 'Insert' file (to insert new version with new Surrogate Key)
If record has Type 1 AND Type 2 Change
- write to 'Type 1 Update' file (to update all records by Natural Key)
- write to 'Type 2 Update' file (to expire prior version by Surrogate Key)
- write to 'Insert' file (to insert new version with new Surrogate Key)
From here we start a separate UPSERT job which collects the output links from all three files into one Oracle OCI stage. Each of the input links are set with Transaction Isolation of "Serializable' and Transaction Size of 10,000 rows.
looks like this:
File:TYPE1UPDATE--------------
\
File:TYPE2UPDATE------------------- OCI
/
File:INSERTNEW----------------
The SQL:
TYPE1UPDATE
UPDATE <dimension>
SET <all type 1 attributes>
WHERE <natural key> = :<natural key column>
TYPE2UPDATE
UPDATE <dimension>
SET expiration_dt = <some date>
WHERE <surrogate key> = :<surrogate key column>
So, it is possible that the same row could be updated by the Type 1 link and Type 2 link.
The issue:
We are randomly getting the following error
ORA-08177 can't serialze access for this transaction
Are we doing this correctly? Any advice?
Thanks in advance,
Rick
We have Slowly Changing Dimension jobs that write to three (two if Type 1 SCD) sequential files:
Type II SCD:
If record has Type 1 Change
- write to 'Type 1 Update' file (to update all records by Natural Key)
If record has Type 2 Change
- write to 'Type 2 Update' file (to expire prior version by Surrogate Key)
- write to 'Insert' file (to insert new version with new Surrogate Key)
If record has Type 1 AND Type 2 Change
- write to 'Type 1 Update' file (to update all records by Natural Key)
- write to 'Type 2 Update' file (to expire prior version by Surrogate Key)
- write to 'Insert' file (to insert new version with new Surrogate Key)
From here we start a separate UPSERT job which collects the output links from all three files into one Oracle OCI stage. Each of the input links are set with Transaction Isolation of "Serializable' and Transaction Size of 10,000 rows.
looks like this:
File:TYPE1UPDATE--------------
\
File:TYPE2UPDATE------------------- OCI
/
File:INSERTNEW----------------
The SQL:
TYPE1UPDATE
UPDATE <dimension>
SET <all type 1 attributes>
WHERE <natural key> = :<natural key column>
TYPE2UPDATE
UPDATE <dimension>
SET expiration_dt = <some date>
WHERE <surrogate key> = :<surrogate key column>
So, it is possible that the same row could be updated by the Type 1 link and Type 2 link.
The issue:
We are randomly getting the following error
ORA-08177 can't serialze access for this transaction
Are we doing this correctly? Any advice?
Thanks in advance,
Rick