DSXchange

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

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?

If the duplicates are getting loaded only one (only during the second run), did you check for any whitespace or PADCHAR on the key field?

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?

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.

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?

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.

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.

I believe the issue is with some invisible characters in the source file,requested new file to check,thanks for all the inputs

I believe the OP stated that the table has no keys defined, which means that the update alternative cannot work.