View previous topic :: View next topic
|
Author |
Message |
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 |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
|
|
|
|
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 |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
rajeshbook
New User
Joined: 21 Nov 2005 Posts: 6 Location: Chennai, India
|
|
|
|
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 |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
rajeshbook
New User
Joined: 21 Nov 2005 Posts: 6 Location: Chennai, India
|
|
|
|
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 |
|
Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
|
|
|
|
Ravi,
You can try this:
Code: |
INSERT
INTO JOB
(COL1,
COL2,
:
)
SELECT DISTINCT
COLA,
COLB,
:
FROM DEF
;
|
|
|
Back to top |
|
|
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? |
|
Back to top |
|
|
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? |
|
Back to top |
|
|
kgumraj
Active User
Joined: 01 May 2006 Posts: 151 Location: Hyderabad
|
|
|
|
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 |
|
Back to top |
|
|
|