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

Getting SQL Error code -811 for a single row


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

New User


Joined: 09 Feb 2007
Posts: 26
Location: Chennai

PostPosted: Wed Mar 07, 2007 2:54 pm
Reply with quote

Hi,
I ran a job with a select query, The job abended with an error msg as follows
Quote:

ERROR SQL CODE = -811
***JOB ABNORMALLY TERMINATED***
CEE3250C The system or user abend U 248 R=00000000 was issued.


When I checked in the table, and ran the query separately, it results in only one row.
Could anyone help me on this issue?
Back to top
View user's profile Send private message
raak

Active User


Joined: 23 May 2006
Posts: 166
Location: chennai

PostPosted: Wed Mar 07, 2007 3:44 pm
Reply with quote

can u post the query here?????
Back to top
View user's profile Send private message
girias

New User


Joined: 09 Feb 2007
Posts: 26
Location: Chennai

PostPosted: Wed Mar 07, 2007 4:09 pm
Reply with quote

The Query is
Quote:

EXEC SQL
SELECT CMT_I, PO_SEQ_I
INTO :CM09T-CMT-I, :CM09T-PO-SEQ-I
FROM Table_Name
WHERE PO_SEQ_I = icon_razz.gifO-SEQ-HOST
END-EXEC
Back to top
View user's profile Send private message
raak

Active User


Joined: 23 May 2006
Posts: 166
Location: chennai

PostPosted: Wed Mar 07, 2007 4:40 pm
Reply with quote

r u sure of the value that ur host variable icon_razz.gif O-SEQ-HOST is getting?

I mean, when u ran the query outside, is the value u have given there same as the one getting populated for the host variable in the program???
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Wed Mar 07, 2007 4:41 pm
Reply with quote

Check for the value assigned to PO_SEQ_I column when you got error.
Some updates/deletes might have happend between the time you got the errror and you ran the query. The best solution to get rid of this kind would be by using Primary/Unikeys in the WHERE predicate.

In your case, If PO_SEQ_I is not a Primary/Unikey key. If it is then no way you can get -811. Hope this helps you. Thanks
Back to top
View user's profile Send private message
girias

New User


Joined: 09 Feb 2007
Posts: 26
Location: Chennai

PostPosted: Wed Mar 07, 2007 4:54 pm
Reply with quote

Thanks for ur quick response.

I agree with ur suggessions. But PO_SEQ_I is not a primary/Unikey.
I knew the value of that variable; but, how do I check the run-time value of the variable.
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Wed Mar 07, 2007 5:31 pm
Reply with quote

Put a display of that variable when you get -811.
Take the value and query the table by putting that value in WHERE predicate you will see more than one row.
Back to top
View user's profile Send private message
girias

New User


Joined: 09 Feb 2007
Posts: 26
Location: Chennai

PostPosted: Wed Mar 07, 2007 5:41 pm
Reply with quote

Ya, Its already there. The value is same as what I tried with running the query saparately.
Back to top
View user's profile Send private message
girias

New User


Joined: 09 Feb 2007
Posts: 26
Location: Chennai

PostPosted: Wed Mar 07, 2007 5:47 pm
Reply with quote

Ya, Thats what I tried and it is returning only one row only. I even checked the input file, the runtime value is correct, But I am still not getting why does it gives -811 in the runtime of the job.
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Thu Mar 08, 2007 11:00 am
Reply with quote

This sounds very wierd. No way this could be possible.
I suspect there should be some mismatch while running the query.

Can you paste the error message when you get -811 and also the query you used to get only one row.
Back to top
View user's profile Send private message
a1javeed

New User


Joined: 10 Dec 2005
Posts: 20
Location: KUWAIT

PostPosted: Thu Mar 08, 2007 4:52 pm
Reply with quote

Hi
If PO_SEQ_I is not primary key, are u inserting or updaing this column before u SELECT it in ur job?

Javeed
Back to top
View user's profile Send private message
girias

New User


Joined: 09 Feb 2007
Posts: 26
Location: Chennai

PostPosted: Thu Mar 08, 2007 4:59 pm
Reply with quote

Javeed,
ya, I am inserting row before select query. But I have a condition that if sql return code is -803, then it will update the row instead inserting.
Back to top
View user's profile Send private message
a1javeed

New User


Joined: 10 Dec 2005
Posts: 20
Location: KUWAIT

PostPosted: Thu Mar 08, 2007 6:35 pm
Reply with quote

girias,
As u wrote PO_SEQ_I is not a key So How canu get -803 ? {U can get -803 if u r trying to INSERT a row with the same key value! }
Just go thru ur code may be it is adding a new Row with the same value for the col PO_SEQ_I.



Javeed
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Thu Mar 08, 2007 6:44 pm
Reply with quote

May be few rows could be inserted for a given PO_SEQ_I value before you do select. It seems like when you get -811 those inserts are rolled back.

Since PO_SEQ_I is not a primary key there could be few rows inserted successfully in your program.

When you get -811, take a count(*) for that where predicate. You can find what is the count.
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 Error to read log with rexx CLIST & REXX 11
No new posts Error when install DB2 DB2 2
No new posts run rexx code with jcl CLIST & REXX 15
No new posts Compile rexx code with jcl CLIST & REXX 6
No new posts CLIST - Virtual storage allocation error CLIST & REXX 5
Search our Forums:

Back to Top