ODBC Connector issue

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
nibumathewbabu
Premium Member
Premium Member
Posts: 22
Joined: Thu Jul 05, 2012 5:09 am
Location: Chennai

ODBC Connector issue

Post by nibumathewbabu »

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
Thanks
Nibu Mathew Babu
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

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?
kumar_s
Charter Member
Charter Member
Posts: 5245
Joined: Thu Jun 16, 2005 11:00 pm

Post by kumar_s »

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?
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
nibumathewbabu
Premium Member
Premium Member
Posts: 22
Joined: Thu Jul 05, 2012 5:09 am
Location: Chennai

Post by nibumathewbabu »

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?
Thanks
Nibu Mathew Babu
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
nibumathewbabu
Premium Member
Premium Member
Posts: 22
Joined: Thu Jul 05, 2012 5:09 am
Location: Chennai

Post by nibumathewbabu »

I believe the issue is with some invisible characters in the source file,requested new file to check,thanks for all the inputs
Thanks
Nibu Mathew Babu
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

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