DB2 -803 Error When Trying to Insert a Non-existent Record in Table

The user is getting this error while trying to add a new record to the database:

com.jcorporate.expresso.core.db.DBException: Unable to add record to database for record with key 'CSQ119/C/CSQ119.1/SBF1041406' in database 'default':Unable to execute statement: INSERT INTO CP_INDUSR (IND_LAST_UPDATE, IND_SUB_CAT_ID, IND_CAT_ID, IND_LAST_UPDATE_USER, IND_TYPE, IND_USER_TYPE, IND_USER_NAME, IND_USER_ID) VALUES ('20160302', 'CSQ119.1', 'CSQ119', 'SB1041001', 'C', 'U', 'Palwasha Ashraf', 'SBF1041406')(StatisticsDBObject, db/context 'default'):DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=1;CARDPRO.CP_INDUSR, DRIVER=3.61.65 (INSERT INTO CP_INDUSR (IND_LAST_UPDATE, IND_SUB_CAT_ID, IND_CAT_ID, IND_LAST_UPDATE_USER, IND_TYPE, IND_USER_TYPE, IND_USER_NAME, IND_USER_ID) VALUES ('20160302', 'CSQ119.1', 'CSQ119', 'SB1041001', 'C', 'U', 'Palwasha Ashraf', 'SBF1041406')):DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=1;CARDPRO.CP_INDUSR, DRIVER=3.61.65

But when I asked the user to execute this command:

db2 "select * from CP_INDUSR where IND_CAT_ID='CSQ119' AND IND_TYPE='C' AND IND_SUB_CAT_ID='CSQ119.1' AND IND_USER_ID='SBF1041406'"

the result returned 0.

So I'm wondering how can this happen as apparently the record does not exist in the table but it hit -803 error while trying to perform the insert.

Appreciate your expertise in this issue and thanks in advance.

1 Answer

What could cause a SQL0803 when running an insert:

• inserting a row that already exists => you only searched for exactly that row try to select ONLY the primary key fields

  db2 "select * from CP_INDUSR where IND_CAT_ID='CSQ119' AND IND_SUB_CAT_ID='CSQ119.1' AND IND_USER_ID='SBF1041406'" 

• multiple rows get inserted with the same pk values in the same transaction - => again you wrote this is not what you have done

• inserting a row where another unique constraint (besides the primary key) get violated => so check if other unique indexes exist for that table