January 28, 2013 at 12:22 pm
I am creating a package to read data from our production iSeries (AS/400) and copying it into our new data warehouse. I am reading millions of rows at a time, and a couple of them will be updated while I am doing the read. The SSIS package is picking these records up twice, once before, and once after the update. I have tried changing the isolation level, but no luck.
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/db2/rbafzmstisol.htm
Looking at footnote P2, it appears that this may be by design. I have looked for a READPAST hint or something similar, but I haven't found anything.
Has anyone else dealt with this issue? Is there a way to solve it short of reading all the records into a pre-staging table prior to loading the staging table?
September 11, 2014 at 1:51 am
I'm facing the same problem, which Isolation levels did you try? I have two options: ReadCommited and Snapshot. Currently it is on ReadCommited, before I'm changing it to Snapshot, I like to know if you already tested that?
Thanks
September 15, 2014 at 10:02 am
Sorry, I never found a way to handle this through the select statement. I just dropped the records into a staging table and then filtered out the duplicates.
September 15, 2014 at 10:29 am
f.welten (9/11/2014)
I'm facing the same problem, which Isolation levels did you try? I have two options: ReadCommited and Snapshot. Currently it is on ReadCommited, before I'm changing it to Snapshot, I like to know if you already tested that?Thanks
Quick thought, snapshot should work fine, just make certain that tempdb is properly configured if you have large number of updates.
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply