IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Insert into an empty table returning sql code -803


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
ravi.munnangi

New User


Joined: 26 May 2006
Posts: 4

PostPosted: Fri May 26, 2006 6:05 pm
Reply with quote

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
View user's profile Send private message
Gurmeet

New User


Joined: 22 Feb 2006
Posts: 46
Location: Pune

PostPosted: Fri May 26, 2006 8:21 pm
Reply with quote

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
View user's profile Send private message
gskulkarni

New User


Joined: 01 Mar 2006
Posts: 70

PostPosted: Mon May 29, 2006 9:10 am
Reply with quote

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
View user's profile Send private message
ravi.munnangi

New User


Joined: 26 May 2006
Posts: 4

PostPosted: Sat Sep 02, 2006 12:35 am
Reply with quote

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
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Sat Sep 02, 2006 1:23 am
Reply with quote

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
View user's profile Send private message
ravi.munnangi

New User


Joined: 26 May 2006
Posts: 4

PostPosted: Sat Sep 02, 2006 2:29 pm
Reply with quote

Dave,

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

Thanks,
Ravi
Back to top
View user's profile Send private message
rajeshbook

New User


Joined: 21 Nov 2005
Posts: 6
Location: Chennai, India

PostPosted: Wed Sep 06, 2006 5:03 pm
Reply with quote

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
View user's profile Send private message
ravi.munnangi

New User


Joined: 26 May 2006
Posts: 4

PostPosted: Wed Sep 06, 2006 6:14 pm
Reply with quote

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
View user's profile Send private message
rajeshbook

New User


Joined: 21 Nov 2005
Posts: 6
Location: Chennai, India

PostPosted: Wed Sep 06, 2006 7:35 pm
Reply with quote

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
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Wed Sep 06, 2006 10:15 pm
Reply with quote

Ravi,

You can try this:

Code:

INSERT
  INTO JOB
       (COL1,
        COL2,
        :
        )
SELECT DISTINCT
       COLA,
       COLB,
       :
  FROM DEF
;
Back to top
View user's profile Send private message
Muthukumar.PLindia

New User


Joined: 03 Jul 2006
Posts: 51
Location: Chennai

PostPosted: Sat Sep 09, 2006 1:44 pm
Reply with quote

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
View user's profile Send private message
Muthukumar.PLindia

New User


Joined: 03 Jul 2006
Posts: 51
Location: Chennai

PostPosted: Sat Sep 09, 2006 1:44 pm
Reply with quote

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
View user's profile Send private message
kgumraj

Active User


Joined: 01 May 2006
Posts: 151
Location: Hyderabad

PostPosted: Sat Sep 09, 2006 3:23 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Reading Empty Dataset DFSORT/ICETOOL 5
No new posts Email attachment from mainframe is empty PC Guides & IT News 9
No new posts Unload and Load ISPF Table TSO/ISPF 4
No new posts ISPF Table to add a new column TSO/ISPF 1
No new posts Inserting into table while open selec... DB2 1
Search our Forums:

Back to Top