Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Ignore SQLSTATE '23505' Duplicate Primary Key

SVERHEYDEN
6 - Meteoroid

Need help from the community here!

 

I need to pipe in ~900 fixed width zipped text files into a pre-existing DB2 BLU table with a 6 column primary key. The problem is the size of the data I'm trying to pipe in as well as duplicate keys across the files. I've created a workflow that picks up batches of files (don't want to do all at once due to connection stability risk as well as space on my harddrive)

 

I don't want to do an 'update insert if new' output because my table in DB2 BLU will contain tens of billions of lines so I'm doing an append records output. An update insert if new will cause massive delays and performance issues for very sporadic duplicate key lines. 

 

Doing a left join back into the target table is also a major performance drain so I'd like to avoid that at all costs

 

Is there a pre-sql statement that I could use to ignore duplicate keys using DB2 SQL? Essentially I want to just skip any records that are considered duplicates and keep the process going.

 

Thanks in advance!

1 REPLY 1
danilang
19 - Altair
19 - Altair

Hi @SVERHEYDEN 

 

This is more of a DB2 SQL question than an Alteryx one.  The key is enforced at the database level and if the error causes a rollback at that point, there's not much you do with your work flow in it's current state.  There may be some option you can set in the database.  I've found a reference to "Exception Tables" that may be able to help you   

 

Here's some other things you can try though.

 

1. Are the duplicate keys caused by conflicts in the various input files only?  i.e. if you clean all the duplicates out of the input files, will the insert work or will there still be conflicts between the cleaned data and the existing keys in the database?

 

2. Do you have owner access to the database?  If yes, you can write your data to a temp table and then run a stored procedure that merges this data with the existing data.

 

3. Write the records one at a time through a batch macro.  This way each insert will be isolated and not affect the others.  This will be very slow since the connection will have to be made on a record by record basis and should only be considered if it's a one time process that can chug away until done.

 

Check the various DB2 SQL forums for more ideas.

 

Dan 

Labels