Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Getting SQL Error code -811 for a single row

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Getting SQL Error code -811 for a single row
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: Re: Getting SQL Error code -811 for a single row
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    Post subject:
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    Post subject: Re: Getting SQL Error code -811 for a single row
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need help with below DB2 error ashek15 DB2 3 Sat Mar 18, 2017 5:56 am
No new posts Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts FINDREP Syntax Error seahawk789 DFSORT/ICETOOL 4 Thu Mar 09, 2017 10:11 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us