Proc append to DB2 - No error when loading duplicates eventhough we have constraints

[AravindPalanichamy]

Hi All,

Wondering someone would help explaining me that Y proc append doesnt throw an error when loading in to DB2 table where they have constraints. I tried to append a existing row in to DB2 table, it doesnt throw me unique constraint error rather it is throwing 1 rows loaded and 1 rows deleted, due to this my job doesnt get exit. 

proc append base = libref.tablename( BULKLOAD = YES
BL_METHOD=CLILOAD )
data = sastable force ;
run;

1 rows loaded.
0 rows skipped.
0 rows rejected.
1 rows deleted as duplicates.
1 rows committed.

[Patrick]

Looks like this is not a SAS but DB2 behaviour when using the CLI LOAD utility.

"The insertion of data is non-atomic because the load utility precludes atomicity. LOAD might not be able to successfully insert all the rows passed to it. For example, if a unique key constraint is violated by a row being inserted, LOAD will not insert this row but will continue loading the remaining rows."

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.apdv.cli.doc/doc/t00078... 

[AravindPalanichamy]

Thanks Patrick. Really helpful. Is there any way we can avoid this behavior, i need to use CLILoad for sure as the table is huge and taking longer time. 

Any other way using CLILoad but we can capture the ERROR in SAS by throwing an error like unique constraint violated. This will make us easy in case of production support without any error we may think that job is success, but actually job is supposed to fail by unique constraint. 

[Patrick]

It looks to me like SAS is doing nothing else than reporting what DB2 tells it that happens.

I don't know if there is a way to change the CLI Load Utility behaviour. It's a question you need to ask a DB2 DBA.

You could use the CLI Load Utility to first load the data into a DB2 staging table and then use a "normal" insert to load the staging table into your target table (DB2 to DB2).