• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQLexception-duplicate records

 
Harry Steinke
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am creating an application which reads data from an external source and places it into a DB2 table on the users workstation for further processing. Depending on the selection criteria used the data read could contain the some of same records that are already in the database which results in an SQL exception (-803) because the primary key already exists.
I would like to either 1) overwrite the existing record 2) skip the insert if the record is a duplicate or 3) handle the exception in the code and continuing processing.
Does anyone have any ideas how to do any of these or some other suggestion? I don't want to do a select to see if the record exists before the insert because I could be adding >10K records at a time and this would increase the processing time.

Thanks
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not being a DB2 user, not sure if this is an option or not, but you may want to check. Oracle recently added an "upsert" feature so it would insert new rows and update existing rows. Couldn't hurt to troll the DB2 docs to see if it gives you the same option.
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Another option would be to use batch inserts. The array of return codes tells you which inserts succeeded or failed.
 
Harry Steinke
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried to add the records with a batch update but I kept running out of memory as there are too many objects.
I ended up adding some code to add the record if it does not already exist.
Thanks for your help
 
Harry Steinke
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried to add the records with a batch update but I kept running out of memory as there are too many objects.
I ended up adding some code to add the record if it does not already exist.
Thanks for your help
 
Babji Reddy
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Batchupdate is an apt suggestion.
(1) Driver is responsible to return the int[] array with the status code whether that particular row update (or insert) was executed correctly.
(2) Assuming DB2 driver does support this,
Processing too many records is always an issue (time/memory) for select/update operations. You have to do it in steps. Means process part of the total records, issue batch update, and process next part(s).
The size of step is what you have to determine based on your data statistics.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic