Dear All,
for the last two days I am playing around with ODBC connector to load data
My requirement:If the rows are already existing then update else Insert.
Target DB:Sql Server
Requirement constraint: Cant add primary keys or index in target DB as it is being already used in front end,hence no keys or indexes can be added.
Issue:Using data stage I have checked the columns to be treated as key columns and used option Insert then update but to my surprise both options are not working and whenever I run the job at the second time all the rows get inserted again creating duplicate records
When I use the option Update then Insert, deadlock issue warnings are generated and rows are not getting properly inserted or updated.
Tried running job in sequential mode but no luck! Kindly share your valuable thoughts
ODBC Connector issue
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 22
- Joined: Thu Jul 05, 2012 5:09 am
- Location: Chennai
ODBC Connector issue
Thanks
Nibu Mathew Babu
Nibu Mathew Babu
Did you specify a Transaction size / commit frequency of "1" while running in sequential mode? If so, I am surprised that you saw deadlocks.
Does the original table have any keys or indices defined?
Does the original table have any keys or indices defined?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 22
- Joined: Thu Jul 05, 2012 5:09 am
- Location: Chennai
Hi ArndW ,
The deadlock issue got resolved as I put No Lock clause in the input query
As I said,Target Tables dont have any keys or idnices defined and we cant do it as well
Hi Kumar,
What i meant to say "Duplicates loaded " if I load the same data again for the second time I believe no rows should not get inserted or updated.correct?
The deadlock issue got resolved as I put No Lock clause in the input query
As I said,Target Tables dont have any keys or idnices defined and we cant do it as well
Hi Kumar,
What i meant to say "Duplicates loaded " if I load the same data again for the second time I believe no rows should not get inserted or updated.correct?
Thanks
Nibu Mathew Babu
Nibu Mathew Babu
If the target tables have no defined Primary key, then you cannot use the DataStage "Update" functionality. You would Need to program that in your Job. Depending upon data volume, you could read the exisiting data and do a CDC between the existing and new data.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
No. Properly configured, the "Update else Insert" will first try to see if the record already exists based on the key fields you've defined in the stage. If it already exists, it will update it. If it cannot be found the data will be inserted.nibumathewbabu wrote:What i meant to say "Duplicates loaded " if I load the same data again for the second time I believe no rows should not get inserted or updated.correct?
Meaning, if you load the same data a second time, all of the records should be updated. Now it may be hard to tell that happened with no actual data changes in the source but that is what should happen. Properly configured, as noted.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 22
- Joined: Thu Jul 05, 2012 5:09 am
- Location: Chennai
I believe the OP stated that the table has no keys defined, which means that the update alternative cannot work.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>