Question on sqlcode -905 sqlstate 57014

Mera Nadu

Question on sqlcode -905 sqlstate 57014
Cobol stored procedure that MESH has informed me abended on a -905

Sqlcode -905 A resource limit as determined from the active resource limit
specification table has been exceeded.
Sqlstate 57014 Processing was canceled as requested.

I have sqlcode checking in the Cobol stored procedure which should trap any
error and write a row to a stats table.
However, it looks like the stored proc is not trapping the error in this case and
execution was cancelled (per the sqlstate).

I just wanted to make sure this is a normal behavior: sometimes control isn't
given back to a stored procedure and there's no way of trapping those
sqlcodes

I appreciate all your comments..

Thanks

Mera

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: Question on sqlcode -905 sqlstate 57014
(in response to Mera Nadu)
As far as I understand the workings of a -905, is that DB2 says "I don't trust you any more - you are using too much resources, so I will terminate you" - I don't think the application gets a chance to trap the -905, I think the thread itself is terminated by DB2

Phil Grainger
CA
Senior Principal Product Manager
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company registration number 1282495 with its registered office at the address set out above. VAT number 697904179.



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Mera Nadu
Sent: 18 December 2008 16:23
To: [login to unmask email]
Subject: [DB2-L] Question on sqlcode -905 sqlstate 57014

Cobol stored procedure that MESH has informed me abended on a -905

Sqlcode -905 A resource limit as determined from the active resource limit
specification table has been exceeded.
Sqlstate 57014 Processing was canceled as requested.

I have sqlcode checking in the Cobol stored procedure which should trap any
error and write a row to a stats table.
However, it looks like the stored proc is not trapping the error in this case and
execution was cancelled (per the sqlstate).

I just wanted to make sure this is a normal behavior: sometimes control isn't
given back to a stored procedure and there's no way of trapping those
sqlcodes

I appreciate all your comments..

Thanks

Mera

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mike Vaughan

Re: Question on sqlcode -905 sqlstate 57014
(in response to Phil Grainger)
I think this really just comes down to understanding where you are hitting the -905. I'll take a wild guess that you are hitting the -905 because the stored procedure execution exceeded the ASUTIME limit defined for the stored procedure. At that point this was not an SQLCode received on an SQL statement within the stored procedure itself, but an SQLCode reflecting the status of the call to the stored procedure overall (Keep in mind that the stored procedure could exceed the -905 without ever issuing an SQLCall). On the other hand, if you were executing a dynamic statement with the stored procedure call that happened to exceed your RLF limits then the -905 would be returned on that call (to the stored procedure). The thread itself shouldn't actually get "terminated" in either case and could continue issueing other SQL statements.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Grainger, Phil
Sent: Thursday, December 18, 2008 11:13 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Question on sqlcode -905 sqlstate 57014

As far as I understand the workings of a -905, is that DB2 says "I don't trust you any more - you are using too much resources, so I will terminate you" - I don't think the application gets a chance to trap the -905, I think the thread itself is terminated by DB2

Phil Grainger
CA
Senior Principal Product Manager
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company registration number 1282495 with its registered office at the address set out above. VAT number 697904179.



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Mera Nadu
Sent: 18 December 2008 16:23
To: [login to unmask email]
Subject: [DB2-L] Question on sqlcode -905 sqlstate 57014

Cobol stored procedure that MESH has informed me abended on a -905

Sqlcode -905 A resource limit as determined from the active resource limit specification table has been exceeded.
Sqlstate 57014 Processing was canceled as requested.

I have sqlcode checking in the Cobol stored procedure which should trap any error and write a row to a stats table.
However, it looks like the stored proc is not trapping the error in this case and execution was cancelled (per the sqlstate).

I just wanted to make sure this is a normal behavior: sometimes control isn't given back to a stored procedure and there's no way of trapping those sqlcodes

I appreciate all your comments..

Thanks

Mera

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA * ______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA * ______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms


-----Message Disclaimer-----

This e-mail message is intended only for the use of the individual or
entity to which it is addressed, and may contain information that is
privileged, confidential and exempt from disclosure under applicable law.
If you are not the intended recipient, any dissemination, distribution or
copying of this communication is strictly prohibited. If you have
received this communication in error, please notify us immediately by
reply email to [login to unmask email] and delete or destroy all copies of
the original message and attachments thereto. Email sent to or from the
Principal Financial Group or any of its member companies may be retained
as required by law or regulation.

Nothing in this message is intended to constitute an Electronic signature
for purposes of the Uniform Electronic Transactions Act (UETA) or the
Electronic Signatures in Global and National Commerce Act ("E-Sign")
unless a specific statement to the contrary is included in this message.

While this communication may be used to promote or market a transaction
or an idea that is discussed in the publication, it is intended to provide
general information about the subject matter covered and is provided with
the understanding that The Principal is not rendering legal, accounting,
or tax advice. It is not a marketed opinion and may not be used to avoid
penalties under the Internal Revenue Code. You should consult with
appropriate counsel or other advisors on all matters pertaining to legal,
tax, or accounting obligations and requirements.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Deepak Goyal

Re: Question on sqlcode -905 sqlstate 57014
(in response to Mike Vaughan)
This thread was cancelled because of the resource limit set at your subsystem
level. There is some SQL in your COBOL program using lot of resources (I would
say CPU time) that was not allowed as per your resource limit table (use "-dis
rlimit" in db2 command line to list this table). You cann't trap this error as in
this case SQL execution has not been started yet.

I would say if you get this kind of error please do consult with your DBA ASAP.
Your DBA can help you in fine tune your SQL.

Thanks

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Ted MacNEIL

Re: Question on sqlcode -905 sqlstate 57014
(in response to Deepak Goyal)
I'm a firm believer in not using the governor!
The first thing a user does when the transaction fails is re-issue it.
Where does that save resources?
Of course, there is the issue of looping, but where is the trade-off?

------Original Message------
From: Deepak Goyal
Sender: DB2 Data Base Discussion List
To: DB2 List
ReplyTo: DB2 List
Sent: Dec 18, 2008 17:04
Subject: Re: [DB2-L] Question on sqlcode -905 sqlstate 57014

This thread was cancelled because of the resource limit set at your subsystem
level. There is some SQL in your COBOL program using lot of resources (I would
say CPU time) that was not allowed as per your resource limit table (use "-dis
rlimit" in db2 command line to list this table). You cann't trap this error as in
this case SQL execution has not been started yet.

I would say if you get this kind of error please do consult with your DBA ASAP.
Your DBA can help you in fine tune your SQL.

Thanks

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms


-
Too busy driving to stop for gas!

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Deepak Goyal

Re: Question on sqlcode -905 sqlstate 57014
(in response to Ted MacNEIL)
I disagree with you, Ted. There are 2 approach to use governor. I like using
predictive approach. In this scenario DB2 will decide the cost before the query
will start and if it exceeds the limit specified in rlimit table it would not execute
that one. This could be helpful in so many situations and this way we can
identity the bad SQL also as developer will approach us to fine tune their SQL.
And ofcourse in this case we are saving the resources as our query hasn't
started yet.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Ted MacNEIL

Re: Question on sqlcode -905 sqlstate 57014
(in response to Deepak Goyal)
>I disagree with you, Ted. There are 2 approach to use governor.


And, I disagree.
Re-dispatching queries after cancelation is a waste.
-
Too busy driving to stop for gas!

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Deepak Goyal

Re: Question on sqlcode -905 sqlstate 57014
(in response to Ted MacNEIL)
But in this case query hasn't started yet. DB2 has cancelled the query during
preparation time only when it determined the cost. So there is no point of
cancelling the query here.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Cathy Taddei

Re: Question on sqlcode -905 sqlstate 57014
(in response to Deepak Goyal)
I have found the reactive governor useful for stopping developers from creating bad SQL before it hits production. They can submit their bad queries all day long and fight with each other for whatever resources we choose to give them. Eventually they will tire of the failures and fix their queries, or ask a DBA for help.

I had high hopes for the predictive governor, but until CA's RC Edit can properly handle SQL code +495 (or until we chuck CA and get QMF), it is useless in development. I opened an enhancement request to CA 3 years ago, and it went into that black hole known as the wishlist.

Cathy Taddei

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Deepak Goyal
Sent: Friday, December 19, 2008 12:58 PM
To: [login to unmask email]
Subject: Re: Question on sqlcode -905 sqlstate 57014

I disagree with you, Ted. There are 2 approach to use governor. I like using
predictive approach. In this scenario DB2 will decide the cost before the query
will start and if it exceeds the limit specified in rlimit table it would not execute
that one. This could be helpful in so many situations and this way we can
identity the bad SQL also as developer will approach us to fine tune their SQL.
And ofcourse in this case we are saving the resources as our query hasn't
started yet.

------------------------------------------------------------------------------

This email is confidential and may be legally privileged.

It is intended solely for the addressee. Access to this email by anyone else, unless expressly approved by the sender or an authorized addressee, is unauthorized.

If you are not the intended recipient, any disclosure, copying, distribution or any action omitted or taken in reliance on it, is prohibited and may be unlawful. If you believe that you have received this email in error, please contact the sender, delete this e-mail and destroy all copies.

=====

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Myron Miller

Re: Question on sqlcode -905 sqlstate 57014
(in response to Cathy Taddei)
I would disagree with you Deepak. I've found too many times, the estimated cost is totally unrepresentative of the actual run times, on both ways. I tried it and it was just totally unreliable and cancelled too many queries that should have run fine and not ones that I had to cancel unless the estimated times were essentially so large as to be worthless.

At runtime, it was much better. Granted it wasted some resources as sometimes the same query was rerun. But generally we found that it was better. Batch and SPs were easy. Dynamic was a bit harder but we found that people generally contacted us quickly when they got this type of abend.

Just my opinon, though.

Myron




________________________________
From: Deepak Goyal <[login to unmask email]>
To: [login to unmask email]
Sent: Friday, December 19, 2008 3:58:01 PM
Subject: Re: [DB2-L] Question on sqlcode -905 sqlstate 57014

I disagree with you, Ted. There are 2 approach to use governor. I like using
predictive approach. In this scenario DB2 will decide the cost before the query
will start and if it exceeds the limit specified in rlimit table it would not execute
that one. This could be helpful in so many situations and this way we can
identity the bad SQL also as developer will approach us to fine tune their SQL.
And ofcourse in this case we are saving the resources as our query hasn't
started yet.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Deepak Goyal

Re: Question on sqlcode -905 sqlstate 57014
(in response to Myron Miller)
I agree with you Myron but DB2 works on statistics. There might be a chance
that your statistics in the catalog is obsolete and hence your query run time is
far better than what DB2 figured out during preparetion time. See we have to
remember that DB2 always works on statistics, it cann't think or manipulate
like a human mind, if we will provide the good statistics to DB2, it will work as
it is supposed to work. I have faced the same situations before couple of
times but I cann't blame DB2 for that. But I have seen the situations where it
was quite useful. It's all a number game.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Myron Miller

Re: Question on sqlcode -905 sqlstate 57014
(in response to Deepak Goyal)
I've provided DB2 many times as good of stats as possible. First thing I do when looking at a query is to ensure that the stats for that query are as accurate as humanely possible. I've worked very closely with Pat Bossman on query tuning for too long to not ensure that stats are correct. That is the one thing that Pat has driven into my hard head. First thing, get good stats. Then get better stats, then get the best stats that DB2 can calculate. Then look at the query and see where more stats can help and provide them.

Yet, I can't count the the many cases where the estimate has no relationship whatsoever to the actual run. Most of the time, the difference is the estimate is lower than the actual run times.

But remember it's only an estimate based upon the Optimizer's guesstimates. I'll grant V9's historgram stats provide more information. In the past, I've used DB2PLI8 and the distribution stats in V8 for some time besides the stats advisor tools to get the stats.

Myron




________________________________
From: Deepak Goyal <[login to unmask email]>
To: [login to unmask email]
Sent: Saturday, December 20, 2008 1:01:30 PM
Subject: Re: [DB2-L] Question on sqlcode -905 sqlstate 57014

I agree with you Myron but DB2 works on statistics. There might be a chance
that your statistics in the catalog is obsolete and hence your query run time is
far better than what DB2 figured out during preparetion time. See we have to
remember that DB2 always works on statistics, it cann't think or manipulate
like a human mind, if we will provide the good statistics to DB2, it will work as
it is supposed to work. I have faced the same situations before couple of
times but I cann't blame DB2 for that. But I have seen the situations where it
was quite useful. It's all a number game.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms