Insert into an empty table returning sql code -803

ravi.munnangi · New User Joined: 26 May 2006 Posts: 4

Hi,

I have a job which first runs the query to delete from a table.

DELETE FROM table;

Then we insert rows into the same table.
But the job abends with sqlcode -803.

SQLCODE = -803, ERROR: AN INSERTED OR UPDATED VALUE IS INVALID
BECAUSE INDEX IN INDEX SPACE PSRT1BQ@ CONSTRAINS COLUMNS OF THE TABLE
SO NO TWO ROWS CAN CONTAIN DUPLICATE VALU

The job runs successfully after one or two restarts.
So the problem cannot be becuase of select query which picks data for inserting.

Can you please share your thoughts on what could be problem?

Thanks,
Ravi

Gurmeet · New User Joined: 22 Feb 2006 Posts: 46 Location: Pune

Ravi,

We get -803 when we try to insert a row which have some unique keys and those unique key values already exists on the table.

Suppose we have a employee table with unique key defined on emp_no and the contents of the tables are,

emp_no emp_name emp_addr
123 ravi xyz

Now, the following query will give a sql code of -803 as a emp_no 123 already exist.

Insert into emp_table values(123, 'gurmeet' ,'pqr');

~Gurmeet

gskulkarni · New User Joined: 01 Mar 2006 Posts: 70

Gurmeet,
Looking at hRavi's query it seems that he is aware of what you have explained. The problem is that he is DELETING the records from teh table and then tries to INSERT. So his question is, if teh DELETE was successful, there would be no records in the table so teh INSERT should be successsful. Instead, the INSERT is giving -803.

Ravi,
My first guess is, your DELETE query is not comitted. So check for it. Once the DELETE SQL code is successful, commit the update. Then try to INSERT. Let us know of teh results.

ravi.munnangi · New User Joined: 26 May 2006 Posts: 4

Hi,

The query flow is like this;

delete from JOB;

commit;

insert into JOB
select * from def where row not in JOB;

But the third query fails with -803.
I am working on a table which contains 300,000 rows.
The job runs to success if i repeat this process(may be more than once).

Can anyone give a clue on this?

Thanks,
Ravi

DavidatK · Posted: Sat Sep 02, 2006 1:23 am

Ravi,

Long time between posts!

I know exactly the problem you are having, I've been there before.

You cannot 'see' the updates you have made to the table in the same SQL execution as the first 'insert', and that's why the second SQL works. Now the first 'insert' was done in a prior SQL.

You can try using 'distinct' to eliminate the duplicate rows. Other techniques are also availiable.

Dave

ravi.munnangi · New User Joined: 26 May 2006 Posts: 4

Dave,

I do not understand what you are trying to say.
Can you give some example?

Thanks,
Ravi

rajeshbook · Posted: Wed Sep 06, 2006 5:03 pm

Ravi,

When you issue Insert query like below

insert into JOB
select * from def where row not in JOB;

First, The inner select query will be executed to select all the records that meets the cond in WHERE clause. However this may select the duplicate records from def table, and then, the Insert query will be executed to perform the mass inserting of records selected from def table, while doing this, the DB2 encounter the primary key viloation becuase of the duplicates from def table.

The thing is, the records inserted in to JOB table will not be available for WHERE cond checking in select query. This is because, the insert query will always execute after the select query completes it's execution and selected all the records that meets the WHERE cond.

Hope you are clear now!

Thanks
Rajasekar

ravi.munnangi · New User Joined: 26 May 2006 Posts: 4

Rajasekar,

I understand your explanation.
The where conditions in the inner select query are written so that duplicates will not be selected.

But i am happy to know from your explanation that all the rows from
inner select are first retrieved an then inserted to JOB table.

One possibility for -803:
If the table in inner select - def is updated when we are selecting
So the data keeps changing and hence there is possibility of duplicates selected.

Please let me know your opinion on this.

Thanks,
Ravi

rajeshbook · Posted: Wed Sep 06, 2006 7:35 pm

I am not sure what exactly you mean by saying " If the table in inner select - def is updated when we are selecting So the data keeps changing and hence there is possibility of duplicates selected ".

Do you mean select and update were happaning at a time against def table?, If that is the case, either the select or update query should fail, that is again depends on what isolation level you use( With UR,,etc ).

But still, At any cost, If your query designed in such a way by which duplicates will not be selected, We don't need to think about the data getting updated when select query running.

Please correct if i am wrong.

Thanks
Rajasekar

DavidatK · Posted: Wed Sep 06, 2006 10:15 pm

Ravi,

You can try this:

Muthukumar.PLindia · New User Joined: 03 Jul 2006 Posts: 51 Location: Chennai

HI dave,
will the inner query be executed first.. after the complete execution of inner query will the outer query (insert into) will run?
Could you please explain how it will execute?

Muthukumar.PLindia · New User Joined: 03 Jul 2006 Posts: 51 Location: Chennai

HI dave,
will the inner query be executed first.. after the complete execution of inner query will the outer query (insert into) will run?
Could you please explain how it will execute?

kgumraj · Posted: Sat Sep 09, 2006 3:23 pm

Hi Ravi,

We got the same error in one of my program, this is how we solved it

Check the variables in file aid or any other aiding tools you have in your shop