Stored Procedure Abends

William Moss

Stored Procedure Abends
I apologize up front about knowing little about stored procedures, but
here is my question.

We are trying to be more proactive about handling stopped stored
procedures. When a procedure has been stopped due to some kind of abend,
are there any messages written to WLM or the SPAS ? Also, is there
anything a programmer can do in the code of a stored procedure to
prevent it from being stopped when there is a problem ? Our environment
is DB2 V7, z/OS 01.04.

Thanks in advance,

Bill Moss

Aetna, Inc



-----------------------------------------
This e-mail may contain confidential or privileged information. If you
think you have received this e-mail in error, please advise the sender by
reply e-mail and then delete this e-mail immediately. Thank you. Aetna


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Martin Kenney

Re: Stored Procedure Abends
(in response to William Moss)
ZPARM parameter STORMXAB is the maximum number of failures a routine can
tolerate before it becomes stopped.





Marty Kenney

Sr. DB2 DBA

Railinc

(919) 651-5211



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Moss, William R
Sent: Wednesday, December 08, 2004 8:04 AM
To: [login to unmask email]
Subject: Stored Procedure Abends



I apologize up front about knowing little about stored procedures, but
here is my question.

We are trying to be more proactive about handling stopped stored
procedures. When a procedure has been stopped due to some kind of abend,
are there any messages written to WLM or the SPAS ? Also, is there
anything a programmer can do in the code of a stored procedure to
prevent it from being stopped when there is a problem ? Our environment
is DB2 V7, z/OS 01.04.

Thanks in advance,

Bill Moss

Aetna, Inc

_____

This e-mail may contain confidential or privileged information. If you
think you have received this e-mail in error, please advise the sender
by
reply e-mail and then delete this e-mail immediately. Thank you. Aetna

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Vaughan

Re: Stored Procedure Abends
(in response to Martin Kenney)
In addition to this, when (if) you finally do exceed the STORMXAB, a message "DSNX906I" is written the db2MSTR address space.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Kenney, Marty
Sent: Wednesday, December 08, 2004 7:15 AM
To: [login to unmask email]
Subject: Re: Stored Procedure Abends



ZPARM parameter STORMXAB is the maximum number of failures a routine can tolerate before it becomes stopped.





Marty Kenney

Sr. DB2 DBA

Railinc

(919) 651-5211



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Moss, William R
Sent: Wednesday, December 08, 2004 8:04 AM
To: [login to unmask email]
Subject: Stored Procedure Abends



I apologize up front about knowing little about stored procedures, but here is my question.

We are trying to be more proactive about handling stopped stored procedures. When a procedure has been stopped due to some kind of abend, are there any messages written to WLM or the SPAS ? Also, is there anything a programmer can do in the code of a stored procedure to prevent it from being stopped when there is a problem ? Our environment is DB2 V7, z/OS 01.04.

Thanks in advance,

Bill Moss

Aetna, Inc


_____


This e-mail may contain confidential or privileged information. If you
think you have received this e-mail in error, please advise the sender by
reply e-mail and then delete this e-mail immediately. Thank you. Aetna

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



-----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.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

William Proctor

Re: Stored Procedure Abends
(in response to Mike Vaughan)
You can add a parameter line to your create procedure definition that
will allow you to write your sysout to the WLM sysout. This should be
temporary for debugging in test area's only though. If it's a cobol
stored procedure then expeditor for DB2 works very well for debugging
the stored procedure.



Bill Proctor

Database Administrator (Adabas/DB2)

DB2 UDB V7.1 Database Administration for OS/390

Texas Guaranteed Student Loan Corp.

Austin, Texas

Phone: 512-219-4847





-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Moss, William R
Sent: Wednesday, December 08, 2004 7:04 AM
To: [login to unmask email]
Subject: Stored Procedure Abends



I apologize up front about knowing little about stored procedures, but
here is my question.

We are trying to be more proactive about handling stopped stored
procedures. When a procedure has been stopped due to some kind of abend,
are there any messages written to WLM or the SPAS ? Also, is there
anything a programmer can do in the code of a stored procedure to
prevent it from being stopped when there is a problem ? Our environment
is DB2 V7, z/OS 01.04.

Thanks in advance,

Bill Moss

Aetna, Inc

_____

This e-mail may contain confidential or privileged information. If you
think you have received this e-mail in error, please advise the sender
by
reply e-mail and then delete this e-mail immediately. Thank you. Aetna

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Cathy Taddei

Re: Stored Procedure Abends
(in response to William Proctor)
It's been a while, but as I recall I got message DSNX905I when a stored
procedure abended, and DSNX906I when the stored procedure is stopped in
DB2 due to excessive abends. I believe they appear on the console as
well as the xxxxMSTR jesmsglog. At any rate, I was able to automate
both messages.

HTH,
Cathy

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Vaughan, Mike
Sent: Wednesday, December 08, 2004 5:38 AM
To: [login to unmask email]
Subject: Re: Stored Procedure Abends


In addition to this, when (if) you finally do exceed the STORMXAB, a
message "DSNX906I" is written the db2MSTR address space.

-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Kenney, Marty
Sent: Wednesday, December 08, 2004 7:15 AM
To: [login to unmask email]
Subject: Re: Stored Procedure Abends



ZPARM parameter STORMXAB is the maximum number of failures a
routine can tolerate before it becomes stopped.





Marty Kenney

Sr. DB2 DBA

Railinc

(919) 651-5211



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Moss, William R
Sent: Wednesday, December 08, 2004 8:04 AM
To: [login to unmask email]
Subject: Stored Procedure Abends



I apologize up front about knowing little about stored
procedures, but here is my question.

We are trying to be more proactive about handling stopped stored
procedures. When a procedure has been stopped due to some kind of abend,
are there any messages written to WLM or the SPAS ? Also, is there
anything a programmer can do in the code of a stored procedure to
prevent it from being stopped when there is a problem ? Our environment
is DB2 V7, z/OS 01.04.

Thanks in advance,

Bill Moss

Aetna, Inc


________________________________


This e-mail may contain confidential or privileged information.
If you
think you have received this e-mail in error, please advise the
sender by
reply e-mail and then delete this e-mail immediately. Thank you.
Aetna


------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


-----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.
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

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

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.

======

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Suresh Sane

Re: Stored Procedure Abends
(in response to Cathy Taddei)
Bill,

Re. >>is there anything a programmer can do in the code of a stored
procedure to prevent it from being stopped when there is a problem ?"

Not really (besides writing code that never abends!). In V7 a zparm
(STORMXAB) controls how many times you can abend before being stopped. I
suggest a high number in test but low (1?) in prod.

In V8, this becomes an option on create/alter procedure .... STOP AFTER nn
FAILURES.

So either the sysprog or in V8 the dba can fix it but not the prorammer.

Thanks,
Suresh

>From: "Moss, William R" <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: Stored Procedure Abends
>Date: Wed, 8 Dec 2004 08:03:50 -0500
>
>I apologize up front about knowing little about stored procedures, but
>here is my question.
>
>We are trying to be more proactive about handling stopped stored
>procedures. When a procedure has been stopped due to some kind of abend,
>are there any messages written to WLM or the SPAS ? Also, is there
>anything a programmer can do in the code of a stored procedure to
>prevent it from being stopped when there is a problem ? Our environment
>is DB2 V7, z/OS 01.04.
>
>Thanks in advance,
>
>Bill Moss
>
>Aetna, Inc
>
>
>
>-----------------------------------------
>This e-mail may contain confidential or privileged information. If you
>think you have received this e-mail in error, please advise the sender by
>reply e-mail and then delete this e-mail immediately. Thank you. Aetna
>
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Kadir Guray Meric

Re: Stored Procedure Abends
(in response to Suresh Sane)
Hi Bill,

Can you show us how we can add a parameter line to create procedure definition?

Can you give an example, please?

Regards

kadir guray meric

-----Original Message-----
From: Proctor, William [mailto:[login to unmask email]
Sent: Wednesday, December 08, 2004 4:20 PM
To: [login to unmask email]
Subject: Re: Stored Procedure Abends



You can add a parameter line to your create procedure definition that will allow you to write your sysout to the WLM sysout. This should be temporary for debugging in test area's only though. If it's a cobol stored procedure then expeditor for DB2 works very well for debugging the stored procedure.



Bill Proctor

Database Administrator (Adabas/DB2)

DB2 UDB V7.1 Database Administration for OS/390

Texas Guaranteed Student Loan Corp.

Austin, Texas

Phone: 512-219-4847

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm






















*************************************************************************************


Bu e-posta ve muhtemel eklerinde verilen bilgiler kiþiye özel ve gizli olup, yalnýzca mesajda belirlenen alýcý ile ilgilidir.Size yanlýþlýkla ulaþmýþsa lütfen göndericiye bilgi veriniz, mesajý siliniz ve içeriðini baþka bir kiþiye açýklamayýnýz, herhangi bir ortama kopyalamayýnýz. Bu mesaj aksi sözleþme ile belirtilmedikçe herhangi bir finansal iþlem teklifi, alýmý, satýmý veya herhangi bir havalenin teyidi gibi bankacýlýk iþlemi yapýlmasý amacýný taþýmamaktadýr.Verilen tüm bilgilerin doðruluðu ve bütünlüðünün garantisi verilmemekte olup, önceden bildirilmeksizin deðiþtirilebilecektir.Bu mesajýn içeriði Bankamýzýn resmi görüþlerini yansýtmayabileceðinden Akbank T.A.Þ. hiçbir hukuki sorumluluðu kabul etmez.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Vaughan

Re: Stored Procedure Abends
(in response to Kadir Guray Meric)
Suresh, can you expand just a little on the suggestion to set the STORMAXB to something low in a production environment? I often hear this recommendation but have never really understood the reasoning behind it. If I have a high-volume production system, why would I want to shut the entire application down just because of a single abend? I usually look at this in comparison to a CICS transaction -- just because a small percentage of users (possibly 1?) happens to hit the right set of conditions to cause an abend, why would I shut down the much larger percentage who are not having a problem? From my standpoint it would seem to be a better approach to bump this value up as high as it can go in a production environment (I actually wish I could turn it off) and put automation in place to notify people of the abends based on the DSNX905I/DSNX906I messages.

Mike.
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Suresh Sane
Sent: Thursday, December 09, 2004 10:39 AM
To: [login to unmask email]
Subject: Re: Stored Procedure Abends


Bill,

Re. >>is there anything a programmer can do in the code of a stored
procedure to prevent it from being stopped when there is a problem ?"

Not really (besides writing code that never abends!). In V7 a zparm
(STORMXAB) controls how many times you can abend before being stopped. I
suggest a high number in test but low (1?) in prod.

In V8, this becomes an option on create/alter procedure .... STOP AFTER nn
FAILURES.

So either the sysprog or in V8 the dba can fix it but not the prorammer.

Thanks,
Suresh

>From: "Moss, William R" <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: Stored Procedure Abends
>Date: Wed, 8 Dec 2004 08:03:50 -0500
>
>I apologize up front about knowing little about stored procedures, but
>here is my question.
>
>We are trying to be more proactive about handling stopped stored
>procedures. When a procedure has been stopped due to some kind of abend,
>are there any messages written to WLM or the SPAS ? Also, is there
>anything a programmer can do in the code of a stored procedure to
>prevent it from being stopped when there is a problem ? Our environment
>is DB2 V7, z/OS 01.04.
>
>Thanks in advance,
>
>Bill Moss
>
>Aetna, Inc
>
>
>
>-----------------------------------------
>This e-mail may contain confidential or privileged information. If you
>think you have received this e-mail in error, please advise the sender by
>reply e-mail and then delete this e-mail immediately. Thank you. Aetna
>
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


-----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.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Suresh Sane

Re: Stored Procedure Abends
(in response to Mike Vaughan)
Mike,

My reasoning is as follows:

There are 2 types of abends:

The first is the kind that you are refering to. The SP is complex and has
many logic paths. In some obscure instance, it runs into a problem perhaps
on a -911 or some such problem that may cure itself a few seconds later. In
this case, a high number (with automation to report after the fact what went
wrong) works well.

The second is a type of error likely form an install. For example you roll
out some client app whose parm list now conflicts with the SP. Similarly
when called by a trigger. Here the problem is more fundamental - it is NOT
going to cure itself. In this case why create 10,000 dumps (I assume you do
create one, right?) when all they are doing is telling you no more info and
chewing up resources.

A third case exists in test where you expect to (and are perhaps planning
to) abend. That is different altogether.

Focusing on prod - what is the profile of your SPs? Complex? Do you have a
good Lib Mgmt procedure so that accidental mismatch is not possible? Could
one abend be the tip of the iceberg (signifying some major flaw) or is it a
transitory error? Do you have good automation in place to detect and
process error messages? What about dumps? Does your software have the
smarts to capture just one and throw away the rest?

Looks like you have a handle on it and could tolerate stormxab to be much
larger. But my recommendation (identical to what IBM has been making) is, in
my opinion, a good starting position for many shops.

Finally - till V7 you are stuck with 1 zparm. With V8, each SP could have a
different value ("stop after nn failures") of your choosing.

Hope this helps and thanks for asking.

Suresh

>From: "Vaughan, Mike" <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: Stored Procedure Abends
>Date: Fri, 10 Dec 2004 09:33:59 -0600
>
> Suresh, can you expand just a little on the suggestion to set the
>STORMAXB to something low in a production environment? I often hear this
>recommendation but have never really understood the reasoning behind it.
>If I have a high-volume production system, why would I want to shut the
>entire application down just because of a single abend? I usually look at
>this in comparison to a CICS transaction -- just because a small percentage
>of users (possibly 1?) happens to hit the right set of conditions to cause
>an abend, why would I shut down the much larger percentage who are not
>having a problem? From my standpoint it would seem to be a better approach
>to bump this value up as high as it can go in a production environment (I
>actually wish I could turn it off) and put automation in place to notify
>people of the abends based on the DSNX905I/DSNX906I messages.
>
>Mike.
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
>Behalf Of Suresh Sane
>Sent: Thursday, December 09, 2004 10:39 AM
>To: [login to unmask email]
>Subject: Re: Stored Procedure Abends
>
>
>Bill,
>
>Re. >>is there anything a programmer can do in the code of a stored
>procedure to prevent it from being stopped when there is a problem ?"
>
>Not really (besides writing code that never abends!). In V7 a zparm
>(STORMXAB) controls how many times you can abend before being stopped. I
>suggest a high number in test but low (1?) in prod.
>
>In V8, this becomes an option on create/alter procedure .... STOP AFTER nn
>FAILURES.
>
>So either the sysprog or in V8 the dba can fix it but not the prorammer.
>
>Thanks,
>Suresh
>
> >From: "Moss, William R" <[login to unmask email]>
> >Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
> >To: [login to unmask email]
> >Subject: Stored Procedure Abends
> >Date: Wed, 8 Dec 2004 08:03:50 -0500
> >
> >I apologize up front about knowing little about stored procedures, but
> >here is my question.
> >
> >We are trying to be more proactive about handling stopped stored
> >procedures. When a procedure has been stopped due to some kind of abend,
> >are there any messages written to WLM or the SPAS ? Also, is there
> >anything a programmer can do in the code of a stored procedure to
> >prevent it from being stopped when there is a problem ? Our environment
> >is DB2 V7, z/OS 01.04.
> >
> >Thanks in advance,
> >
> >Bill Moss
> >
> >Aetna, Inc
> >
> >
> >
> >-----------------------------------------
> >This e-mail may contain confidential or privileged information. If you
> >think you have received this e-mail in error, please advise the sender by
> >reply e-mail and then delete this e-mail immediately. Thank you. Aetna
> >
> >
> >---------------------------------------------------------------------------------
> >Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
>home
> >page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
>select
> >"Join or Leave the list". The IDUG DB2-L FAQ is at
> >http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> >[login to unmask email] Find out the latest on IDUG conferences
>at
> >http://conferences.idug.org/index.cfm
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm
>
>
>-----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.
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". The IDUG DB2-L FAQ is at
>http://www.idugdb2-l.org. The IDUG List Admins can be reached at
>[login to unmask email] Find out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm