Creating sysout for each stored procedure

mellonbill

Creating sysout for each stored procedure
MessageAll,

I've heard this discussed before. For testing stored procedures we want each stored procedure to have it's own sysout instead of all the sysouts going to the same DD. How is this accomplished?

Thanks,
Bill Johnson

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

mellonbill

Creating sysout for each stored procedure
(in response to Ava Collins)
MessageAll,

I've heard this discussed before. For testing stored procedures we want each stored procedure to have it's own sysout instead of all the sysouts going to the same DD. How is this accomplished?

Thanks,
Bill Johnson

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

*************************************************************************************
The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer.
*************************************************************************************


---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Bob Galeotti

Re: Creating sysout for each stored procedure
(in response to mellonbill)
MessageHello Bill,
I don't know that there's a solution to your liking, but maybe if you implement a new coding standard that mandates that the stored procedure name be placed as the first part of the SYSOUT line ( i.e. - <stor_proc_nam> ), then you will be able to identify the output from each stored procedure. Ofcourse that coding will follow the stored procedure into production. Maybe that's not a bad thing.

HTH

Bob Galeotti
SoftBase Systems, Inc.
----- Original Message -----
From: Bill Johnson
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Monday, December 08, 2003 10:55 PM
Subject: Creating sysout for each stored procedure


All,

I've heard this discussed before. For testing stored procedures we want each stored procedure to have it's own sysout instead of all the sysouts going to the same DD. How is this accomplished?

Thanks,
Bill Johnson
--------------------------------------------------------------------------------- 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Lee Hayden

Re: Creating sysout for each stored procedure
(in response to Bob Galeotti)
You can do this but you must use the CEEMOUT service.
You will have to set the runopts for the stored procedure i.e

RUN OPTIONS 'MSGFILE(MYPROG,FBA,121,0,NOENQ)'

Here are some code snips.

01 MESG-AREA.
05 MESG-LEN PIC S9(4) BINARY VALUE +120.
05 FILLER PIC X(1) VALUE SPACE.
05 MESG-PROG PIC X(8) VALUE "MYPROG".
05 MESG-TIME PIC X(14).
05 FILLER PIC X(1) VALUE SPACE.
05 MESG-TEXT PIC X(95) VALUE SPACES.
05 FILLER PIC X(1) VALUE SPACE.
01 MESG-DEST PIC S9(9) BINARY VALUE +2.
01 MESG-FEEDBACK PIC X(12).
.
.
.
STRING "RECEIVED PARM AUTHID = " AUTHID " "
DELIMITED BY SIZE INTO MESG-TEXT.
PERFORM MSG-OUT.
.
.
.
MSG-OUT.
MOVE FUNCTION CURRENT-DATE TO MESG-TIME,
CALL "CEEMOUT" USING
MESG-AREA
MESG-DEST
MESG-FEEDBACK.
MOVE SPACES TO MESG-TEXT.

-----Original Message-----
From: Bill Johnson [mailto:[login to unmask email]
Sent: Monday, December 08, 2003 10:55 PM
Subject: Creating sysout for each stored procedure


All,

I've heard this discussed before. For testing stored procedures we want each
stored procedure to have it's own sysout instead of all the sysouts going to
the same DD. How is this accomplished?

Thanks,
Bill Johnson
----------------------------------------------------------------------------
----- 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". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Bill Johnson

Re: Creating sysout for each stored procedure
(in response to Martin Packer)
I seem to recall an easier way that was discussed a while ago and I used
a couple of years ago. Wasn't it separate DD's in the DSNSPAS address
space for each Stored Procedure? Or something like that?

Thanks,
Bill Johnson

>>> [login to unmask email] 12/09/03 08:28AM >>>
You can do this but you must use the CEEMOUT service.
You will have to set the runopts for the stored procedure i.e

RUN OPTIONS 'MSGFILE(MYPROG,FBA,121,0,NOENQ)'

Here are some code snips.

01 MESG-AREA.
05 MESG-LEN PIC S9(4) BINARY VALUE +120.
05 FILLER PIC X(1) VALUE SPACE.
05 MESG-PROG PIC X(8) VALUE "MYPROG".
05 MESG-TIME PIC X(14).
05 FILLER PIC X(1) VALUE SPACE.
05 MESG-TEXT PIC X(95) VALUE SPACES.
05 FILLER PIC X(1) VALUE SPACE.
01 MESG-DEST PIC S9(9) BINARY VALUE +2.
01 MESG-FEEDBACK PIC X(12).
.
.
.
STRING "RECEIVED PARM AUTHID = " AUTHID " "
DELIMITED BY SIZE INTO MESG-TEXT.
PERFORM MSG-OUT.
.
.
.
MSG-OUT.
MOVE FUNCTION CURRENT-DATE TO MESG-TIME,
CALL "CEEMOUT" USING
MESG-AREA
MESG-DEST
MESG-FEEDBACK.
MOVE SPACES TO MESG-TEXT.

-----Original Message-----
From: Bill Johnson [mailto:[login to unmask email]
Sent: Monday, December 08, 2003 10:55 PM
Subject: Creating sysout for each stored procedure


All,

I've heard this discussed before. For testing stored procedures we want
each
stored procedure to have it's own sysout instead of all the sysouts
going to
the same DD. How is this accomplished?

Thanks,
Bill Johnson
----------------------------------------------------------------------------
----- 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". If you will be out of the office, send
the
SET DB2-L NO MAIL command to [login to unmask email] 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". If you will be out of the office,
send the SET DB2-L NO MAIL command to [login to unmask email] 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

------------------------------------------------------------------------------
CONFIDENTIALITY NOTICE: This 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 or exempt from disclosure
under applicable law. If the reader of this message is not the intended
recipient or the employee or agent responsible for delivering the message
to the intended recipient, you are hereby notified that you are strictly
prohibited from printing, storing, disseminating, distributing or copying
this communication. If you have received this communication in error,
please notify us immediately by replying to the message and deleting it
from your computer. Thank You, Antares Management Solutions.
=====

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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 Packer

Re: Creating sysout for each stored procedure
(in response to Lee Hayden)
Parenthetic comment: It's in production where you really want the
separation into different DDs - one per SP INSTANCE. If you can, try to
avoid writing much to the SPOOL as that'll cause SPs to ENQ. (We figured
this one out recently on the SP red book residency.)

Cheers, Martin

Martin Packer, MBCS Martin Packer/UK/IBM
020-8832-5167 in the UK (+44) (MOBX 273643, Internal 7-325167, Mobile
07802-245584)

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Agus Kwee

Re: Creating sysout for each stored procedure
(in response to Bill Johnson)
Bill,

The method that you mentioned is by using different 8 character
name following the OUTDD cobol compiler option when compiling
different cobol stored procedure program. You can use the unique
stored procedure program name for the name of the OUTDD.
You can then use separate DD's for each of those OUTDD names.

Regards,
Agus Kwee
Themis Training
http://www.themisinc.com

----- Original Message -----
From: William Johnson
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Tuesday, December 09, 2003 8:41 AM
Subject: Re: Creating sysout for each stored procedure


I seem to recall an easier way that was discussed a while ago and I used a couple of years ago. Wasn't it separate DD's in the DSNSPAS address space for each Stored Procedure? Or something like that?

Thanks,
Bill Johnson


---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Avram Friedman

Re: Creating sysout for each stored procedure
(in response to Agus Kwee)
Best solution for SYSOUT in production stored proceedures is DON'T EVEN CONSIDER IT.

1. The prinicipal use of stored procedures is to reduce network traffic. This implies the users are else where, most likely on a remote heterogeneous platform. They are not around to see the SYSOUT.

2. Creating a SYSOUT for every instance of every procedure is expensize in terms of cycles, JES resources like JOEs, elapsed time etc.

3. As already mentioned in some postings, Stored procedure address spaces usually have many TCBs. The same stored procedure name could be scheduled simutaniously under many TCBs resulting in ENQ delays and ABENDS. We try to address this with MSGFILE(ABNDMSG,FBA,121,0,ENQ)
which generates a new SYS000* dataset for every SYSOUT. But we have been far more sucessfull at eliminating the SYSOUTS.

The way we eliminate the SYSOUTS is we monitor our stored procedure address spaces for SYSOUTS and then we go after the development groups. If any one has a better ideas (more automated) I would be grateful.



Data centers learned the lesson about excessive use of console messages, debugging messages, extra reportting files etc years ago ... these lessons apply to stored procedures as well.

Martin Packer wrote:

> Parenthetic comment: It's in production where you really want the
> separation into different DDs - one per SP INSTANCE. If you can, try to
> avoid writing much to the SPOOL as that'll cause SPs to ENQ. (We figured
> this one out recently on the SP red book residency.)
>
> Cheers, Martin
>
> Martin Packer, MBCS Martin Packer/UK/IBM
> 020-8832-5167 in the UK (+44) (MOBX 273643, Internal 7-325167, Mobile
> 07802-245584)
>
> ---------------------------------------------------------------------------------
> 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

--
NOTICE: If received in error, please destroy and notify sender. Sender does not waive confidentiality or privilege, and use is prohibited.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Bill Johnson

Re: Creating sysout for each stored procedure
(in response to Avram Friedman)
I'm not in support of using this method in production but for testing
SP's it is invaluable. We used this method at another employer a couple
of years ago. When you have 6 programmers trying to debug their SP's and
all sending their displays to the same DD, splitting them into 6
separate DD's makes it so much easier.

Bill Johnson
Tech Services
OS/390, ZOS
Antares Management Solutions
23700 Commerce Park Road
Beachwood, OH 44122-5832
Phone:(216) 292-0400 ext.3478
FAX: (216) 292-1619
E-mail: [login to unmask email]

>>> "Avram Friedman" <[login to unmask email]> 12/09/03 09:29AM
>>>
Best solution for SYSOUT in production stored proceedures is DON'T EVEN
CONSIDER IT.

1. The prinicipal use of stored procedures is to reduce network traffic.
This implies the users are else where, most likely on a remote
heterogeneous platform. They are not around to see the SYSOUT.

2. Creating a SYSOUT for every instance of every procedure is expensize
in terms of cycles, JES resources like JOEs, elapsed time etc.

3. As already mentioned in some postings, Stored procedure address
spaces usually have many TCBs. The same stored procedure name could be
scheduled simutaniously under many TCBs resulting in ENQ delays and
ABENDS. We try to address this with MSGFILE(ABNDMSG,FBA,121,0,ENQ)
which generates a new SYS000* dataset for every SYSOUT. But we have
been far more sucessfull at eliminating the SYSOUTS.

The way we eliminate the SYSOUTS is we monitor our stored procedure
address spaces for SYSOUTS and then we go after the development groups.
If any one has a better ideas (more automated) I would be grateful.



Data centers learned the lesson about excessive use of console messages,
debugging messages, extra reportting files etc years ago ... these
lessons apply to stored procedures as well.

Martin Packer wrote:

> Parenthetic comment: It's in production where you really want the
> separation into different DDs - one per SP INSTANCE. If you can, try
to
> avoid writing much to the SPOOL as that'll cause SPs to ENQ. (We
figured
> this one out recently on the SP red book residency.)
>
> Cheers, Martin
>
> Martin Packer, MBCS Martin Packer/UK/IBM
> 020-8832-5167 in the UK (+44) (MOBX 273643, Internal 7-325167,
Mobile
> 07802-245584)
>
>
---------------------------------------------------------------------------------
> 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". If you will be out of the office,
send the SET DB2-L NO MAIL command to [login to unmask email] 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

--
NOTICE: If received in error, please destroy and notify sender. Sender
does not waive confidentiality or privilege, and use is prohibited.

---------------------------------------------------------------------------------
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". If you will be out of the office,
send the SET DB2-L NO MAIL command to [login to unmask email] 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

------------------------------------------------------------------------------
CONFIDENTIALITY NOTICE: This 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 or exempt from disclosure
under applicable law. If the reader of this message is not the intended
recipient or the employee or agent responsible for delivering the message
to the intended recipient, you are hereby notified that you are strictly
prohibited from printing, storing, disseminating, distributing or copying
this communication. If you have received this communication in error,
please notify us immediately by replying to the message and deleting it
from your computer. Thank You, Antares Management Solutions.
=====

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Tanya Haverchak

Re: Creating sysout for each stored procedure
(in response to Bill Johnson)
Avram,
I agree with all your points. But I would like to know how do you handle
the issue of error diagnosis in production stored procedures.
In our shop, if sql call in DB2 stored procedure that is being called
from the "web app" , returns negative sql code, SQLCODE along with
SQLSTATE is displayed to a sysout for ease of problem determination.
So far it has presented no problems. How ever, very recently we switched
to WLM managed stored procedures. With these procedures, the displays in
WLM address space, cause that particular address space to ABEND with a
dump.
I would like to know how other shops dealing with this situation.


Tanya Haverchak
IBM Certified Solutions Expert
- DB2 V7 Database Administration OS/390
Antares Management Solutions
23700 Commerce Park Road
Beachwood, OH 44122-5832
Phone:(216) 292-0400 ext.4011
FAX: (216) 292-1619
E-mail: [login to unmask email]


>>> "Avram Friedman" <[login to unmask email]> 12/09/03 09:29AM
>>>
Best solution for SYSOUT in production stored proceedures is DON'T EVEN
CONSIDER IT.

1. The prinicipal use of stored procedures is to reduce network traffic.
This implies the users are else where, most likely on a remote
heterogeneous platform. They are not around to see the SYSOUT.

2. Creating a SYSOUT for every instance of every procedure is expensize
in terms of cycles, JES resources like JOEs, elapsed time etc.

3. As already mentioned in some postings, Stored procedure address
spaces usually have many TCBs. The same stored procedure name could be
scheduled simutaniously under many TCBs resulting in ENQ delays and
ABENDS. We try to address this with MSGFILE(ABNDMSG,FBA,121,0,ENQ)
which generates a new SYS000* dataset for every SYSOUT. But we have
been far more sucessfull at eliminating the SYSOUTS.

The way we eliminate the SYSOUTS is we monitor our stored procedure
address spaces for SYSOUTS and then we go after the development groups.
If any one has a better ideas (more automated) I would be grateful.



Data centers learned the lesson about excessive use of console messages,
debugging messages, extra reportting files etc years ago ... these
lessons apply to stored procedures as well.

Martin Packer wrote:

> Parenthetic comment: It's in production where you really want the
> separation into different DDs - one per SP INSTANCE. If you can, try
to
> avoid writing much to the SPOOL as that'll cause SPs to ENQ. (We
figured
> this one out recently on the SP red book residency.)
>
> Cheers, Martin
>
> Martin Packer, MBCS Martin Packer/UK/IBM
> 020-8832-5167 in the UK (+44) (MOBX 273643, Internal 7-325167,
Mobile
> 07802-245584)
>
>
---------------------------------------------------------------------------------
> 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". If you will be out of the office,
send the SET DB2-L NO MAIL command to [login to unmask email] 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

--
NOTICE: If received in error, please destroy and notify sender. Sender
does not waive confidentiality or privilege, and use is prohibited.

---------------------------------------------------------------------------------
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". If you will be out of the office,
send the SET DB2-L NO MAIL command to [login to unmask email] 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

------------------------------------------------------------------------------
CONFIDENTIALITY NOTICE: This 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 or exempt from disclosure
under applicable law. If the reader of this message is not the intended
recipient or the employee or agent responsible for delivering the message
to the intended recipient, you are hereby notified that you are strictly
prohibited from printing, storing, disseminating, distributing or copying
this communication. If you have received this communication in error,
please notify us immediately by replying to the message and deleting it
from your computer. Thank You, Antares Management Solutions.
=====

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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: Creating sysout for each stored procedure
(in response to Tanya Haverchak)
We use WLM managed stored procedures, and have Cobol programmers doing
DISPLAYs to sysout. We had some abends due to conflicts between Cobol's use
of SYSOUT and LE/370 trying to put out messages. Most of these were
eliminated when we explicitly coded
//SYSOUT DD SYSOUT=*
in the SP JCL.

We still had a few abends after that whenever there were multiple copies of
a program doing DISPLAYs of 4k blocks of data to SYSOUT at the same time
(NUMTCB=8, so it didn't take too many to cause the problem). After the
programmer removed this huge DISPLAY, we have had no further problems in
that area.
HTH,
Cathy

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Tanya Haverchak
Sent: Tuesday, December 09, 2003 11:12 AM
To: [login to unmask email]
Subject: Re: Creating sysout for each stored procedure


Avram,
I agree with all your points. But I would like to know how do you handle the
issue of error diagnosis in production stored procedures.
In our shop, if sql call in DB2 stored procedure that is being called from
the "web app" , returns negative sql code, SQLCODE along with SQLSTATE is
displayed to a sysout for ease of problem determination.
So far it has presented no problems. How ever, very recently we switched to
WLM managed stored procedures. With these procedures, the displays in WLM
address space, cause that particular address space to ABEND with a dump.
I would like to know how other shops dealing with this situation.


Tanya Haverchak
IBM Certified Solutions Expert
- DB2 V7 Database Administration OS/390
Antares Management Solutions
23700 Commerce Park Road
Beachwood, OH 44122-5832
Phone:(216) 292-0400 ext.4011
FAX: (216) 292-1619
E-mail: [login to unmask email]
<mailto:[login to unmask email]>


>>> "Avram Friedman" <[login to unmask email]> 12/09/03 09:29AM >>>
Best solution for SYSOUT in production stored proceedures is DON'T EVEN
CONSIDER IT.

1. The prinicipal use of stored procedures is to reduce network traffic.
This implies the users are else where, most likely on a remote heterogeneous
platform. They are not around to see the SYSOUT.

2. Creating a SYSOUT for every instance of every procedure is expensize in
terms of cycles, JES resources like JOEs, elapsed time etc.

3. As already mentioned in some postings, Stored procedure address spaces
usually have many TCBs. The same stored procedure name could be scheduled
simutaniously under many TCBs resulting in ENQ delays and ABENDS. We try to
address this with MSGFILE(ABNDMSG,FBA,121,0,ENQ)
which generates a new SYS000* dataset for every SYSOUT. But we have been
far more sucessfull at eliminating the SYSOUTS.

The way we eliminate the SYSOUTS is we monitor our stored procedure address
spaces for SYSOUTS and then we go after the development groups. If any one
has a better ideas (more automated) I would be grateful.



Data centers learned the lesson about excessive use of console messages,
debugging messages, extra reportting files etc years ago ... these lessons
apply to stored procedures as well.

Martin Packer wrote:

> Parenthetic comment: It's in production where you really want the
> separation into different DDs - one per SP INSTANCE. If you can, try to
> avoid writing much to the SPOOL as that'll cause SPs to ENQ. (We figured
> this one out recently on the SP red book residency.)
>
> Cheers, Martin
>
> Martin Packer, MBCS Martin Packer/UK/IBM
> 020-8832-5167 in the UK (+44) (MOBX 273643, Internal 7-325167, Mobile
> 07802-245584)
>
>
----------------------------------------------------------------------------
-----
> 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.
< http://www.idugdb2-l.org/archives/db2-l.html. > From that page select "Join
or Leave the list". If you will be out of the office, send the SET DB2-L NO
MAIL command to listserv@ www.idugdb2-l.org. < http://www.idugdb2-l.org. >
The IDUG List Admins can be reached at DB2-L-REQUEST@ www.idugdb2-l.org.
< http://www.idugdb2-l.org. > Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
< http://conferences.idug.org/index.cfm >

--
NOTICE: If received in error, please destroy and notify sender. Sender does
not waive confidentiality or privilege, and use is prohibited.

----------------------------------------------------------------------------
-----
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.
< http://www.idugdb2-l.org/archives/db2-l.html. > From that page select "Join
or Leave the list". If you will be out of the office, send the SET DB2-L NO
MAIL command to listserv@ www.idugdb2-l.org. < http://www.idugdb2-l.org. >
The IDUG List Admins can be reached at DB2-L-REQUEST@ www.idugdb2-l.org.
< http://www.idugdb2-l.org. > Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
< http://conferences.idug.org/index.cfm >



----------------------------------------------------------------------------
--
CONFIDENTIALITY NOTICE: This 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 or exempt from disclosure
under applicable law. If the reader of this message is not the intended
recipient or the employee or agent responsible for delivering the message
to the intended recipient, you are hereby notified that you are strictly
prohibited from printing, storing, disseminating, distributing or copying
this communication. If you have received this communication in error,
please notify us immediately by replying to the message and deleting it
from your computer. Thank You, Antares Management Solutions.
===
==


----------------------------------------------------------------------------
----- 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". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Avram Friedman

Re: Creating sysout for each stored procedure
(in response to Cathy Taddei)
In production most of our SQL errors are resource unavailable. For
these errors there is usually a message in the MSTR job log.

Our DBA's also use BMC APPTUNE to identify unusual SQL Codes. Some of
them prefer CA Detector (which we no longer have activly installed)

I like to think that many potencial errors are addressed in our test
environment. (Every one is entitled to at least one fantasy).

We have been toying with the idea of setting up atleast one WLM for
staging / isolation in which the developers would have somewhat more
freedom to turn on various debugging options. So far the idea has meet
with a lot of resistance because it is viewed as contrary to our tight
change control procedures.

Our shop may be somewhat exceptional when it comes to our use of stored
procedures. We run a 8 member DB2 SYSPLEX accross 4 LPARS. 95% of our
work originates from non OS/390 platforms and most of it schedules
stored proceedures (A mixture of C and SQL on the OS/390 side). I would
guess we easily do 2 to 3 million stored procedure calls a day. Our
biggest ongoing problem is not SQL related but assoicated with how DB2
manages procedures that are RESIDENT(YES) When ever DB2 schedules a
procedure in a WLM environment with diffrent RUNOPTS it causes all the
resident procedures to become invalidated and reloaded at there next
reference. I guess this is another reason why we don't like the idea of
special RUNOPTS by application.

Tanya Haverchak wrote:

> Avram,I agree with all your points. But I would like to know how do
> you handle the issue of error diagnosis in production stored
> procedures.In our shop, if sql call in DB2 stored procedure that is
> being called from the "web app" , returns negative sql code, SQLCODE
> along with SQLSTATE is displayed to a sysout for ease of problem
> determination.So far it has presented no problems. How ever, very
> recently we switched to WLM managed stored procedures. With these
> procedures, the displays in WLM address space, cause that particular
> address space to ABEND with a dump.I would like to know how other
> shops dealing with this situation. Tanya Haverchak
> IBM Certified Solutions Expert
> - DB2 V7 Database Administration OS/390
> Antares Management Solutions
> 23700 Commerce Park Road
> Beachwood, OH 44122-5832
> Phone:(216) 292-0400 ext.4011
> FAX: (216) 292-1619
> E-mail: [login to unmask email]
>
>
> >>> "Avram Friedman" <[login to unmask email]> 12/09/03
> 09:29AM >>>
> Best solution for SYSOUT in production stored proceedures is DON'T
> EVEN CONSIDER IT.
>
> 1. The prinicipal use of stored procedures is to reduce network
> traffic. This implies the users are else where, most likely on a
> remote heterogeneous platform. They are not around to see the SYSOUT.
>
> 2. Creating a SYSOUT for every instance of every procedure is
> expensize in terms of cycles, JES resources like JOEs, elapsed time
> etc.
>
> 3. As already mentioned in some postings, Stored procedure address
> spaces usually have many TCBs. The same stored procedure name could
> be scheduled simutaniously under many TCBs resulting in ENQ delays and
> ABENDS. We try to address this with MSGFILE(ABNDMSG,FBA,121,0,ENQ)
> which generates a new SYS000* dataset for every SYSOUT. But we have
> been far more sucessfull at eliminating the SYSOUTS.
>
> The way we eliminate the SYSOUTS is we monitor our stored procedure
> address spaces for SYSOUTS and then we go after the development
> groups. If any one has a better ideas (more automated) I would be
> grateful.
>
>
>
> Data centers learned the lesson about excessive use of console
> messages, debugging messages, extra reportting files etc years ago ...
> these lessons apply to stored procedures as well.
>
> Martin Packer wrote:
>
> > Parenthetic comment: It's in production where you really want the
> > separation into different DDs - one per SP INSTANCE. If you can, try
> to
> > avoid writing much to the SPOOL as that'll cause SPs to ENQ. (We
> figured
> > this one out recently on the SP red book residency.)
> >
> > Cheers, Martin
> >
> > Martin Packer, MBCS Martin Packer/UK/IBM
> > 020-8832-5167 in the UK (+44) (MOBX 273643, Internal 7-325167,
> Mobile
> > 07802-245584)
> >
> >
> ---------------------------------------------------------------------------------
>
> > 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". If you will be out of the
> office, send the SET DB2-L NO MAIL command to
> [login to unmask email] 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
>
> --
> NOTICE: If received in error, please destroy and notify sender.
> Sender does not waive confidentiality or privilege, and use is
> prohibited.
>
> -----------
> ---------------------------------------------------------------------
> 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". If you will be out of the
> office, send the SET DB2-L NO MAIL command to
> [login to unmask email] 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
>
> ------------------------------------------------------------------------------
>
> CONFIDENTIALITY NOTICE: This 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 or exempt from disclosure
>
> under applicable law. If the reader of this message is not the
> intended
> recipient or the employee or agent responsible for delivering the
> message
> to the intended recipient, you are hereby notified that you are
> strictly
> prohibited from printing, storing, disseminating, distributing or
> copying
> this communication. If you have received this communication in error,
> please notify us immediately by replying to the message and deleting
> it
> from your computer. Thank You, Antares Management Solutions.
> ============
> =================
> -----------------
> ---------------------------------------------------------------
> 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". If you will be out of the
> office, send the SET DB2-L NO MAIL command to
> [login to unmask email] 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

--
NOTICE: If received in error, please destroy and notify sender. Sender
does not waive confidentiality or privilege, and use is prohibited.


---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Dave Nance

Re: Creating sysout for each stored procedure
(in response to Avram Friedman)
Tanya,
How about: writing the offending SQLCODE to an error table of some sort, send back as part of the output from the proc, pass a set SQLSTATE. Those are a few options available to you among many others you will soon be receiving.

Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 12/9/03 2:11:34 PM >>>

Avram,
I agree with all your points. But I would like to know how do you handle the issue of error diagnosis in production stored procedures.
In our shop, if sql call in DB2 stored procedure that is being called from the "web app" , returns negative sql code, SQLCODE along with SQLSTATE is displayed to a sysout for ease of problem determination.
So far it has presented no problems. How ever, very recently we switched to WLM managed stored procedures. With these procedures, the displays in WLM address space, cause that particular address space to ABEND with a dump.
I would like to know how other shops dealing with this situation.


Tanya Haverchak
IBM Certified Solutions Expert
- DB2 V7 Database Administration OS/390
Antares Management Solutions
23700 Commerce Park Road
Beachwood, OH 44122-5832
Phone:(216) 292-0400 ext.4011
FAX: (216) 292-1619
E-mail: [login to unmask email]


>>> "Avram Friedman" <[login to unmask email]> 12/09/03 09:29AM >>>
Best solution for SYSOUT in production stored proceedures is DON'T EVEN CONSIDER IT.

1. The prinicipal use of stored procedures is to reduce network traffic. This implies the users are else where, most likely on a remote heterogeneous platform. They are not around to see the SYSOUT.

2. Creating a SYSOUT for every instance of every procedure is expensize in terms of cycles, JES resources like JOEs, elapsed time etc.

3. As already mentioned in some postings, Stored procedure address spaces usually have many TCBs. The same stored procedure name could be scheduled simutaniously under many TCBs resulting in ENQ delays and ABENDS. We try to address this with MSGFILE(ABNDMSG,FBA,121,0,ENQ)
which generates a new SYS000* dataset for every SYSOUT. But we have been far more sucessfull at eliminating the SYSOUTS.

The way we eliminate the SYSOUTS is we monitor our stored procedure address spaces for SYSOUTS and then we go after the development groups. If any one has a better ideas (more automated) I would be grateful.



Data centers learned the lesson about excessive use of console messages, debugging messages, extra reportting files etc years ago ... these lessons apply to stored procedures as well.

Martin Packer wrote:

> Parenthetic comment: It's in production where you really want the
> separation into different DDs - one per SP INSTANCE. If you can, try to
> avoid writing much to the SPOOL as that'll cause SPs to ENQ. (We figured
> this one out recently on the SP red book residency.)
>
> Cheers, Martin
>
> Martin Packer, MBCS Martin Packer/UK/IBM
> 020-8832-5167 in the UK (+44) (MOBX 273643, Internal 7-325167, Mobile
> 07802-245584)
>
> ---------------------------------------------------------------------------------
> 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

--
NOTICE: If received in error, please destroy and notify sender. Sender does not waive confidentiality or privilege, and use is prohibited.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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


------------------------------------------------------------------------------
CONFIDENTIALITY NOTICE: This 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 or exempt from disclosure
under applicable law. If the reader of this message is not the intended
recipient or the employee or agent responsible for delivering the message
to the intended recipient, you are hereby notified that you are strictly
prohibited from printing, storing, disseminating, distributing or copying
this communication. If you have received this communication in error,
please notify us immediately by replying to the message and deleting it
from your computer. Thank You, Antares Management Solutions.
======

--------------------------------------------------------------------------------- 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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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 message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Ray Price

Re: Creating sysout for each stored procedure
(in response to Dave Nance)
We also have stored procedures that put error diagnosis data to SYSOUT. We
had problems if more than one tried to write to SYSOUT at the same time. We
found that the ENQ option in the SP definition worked for us, something
like:



RUN OPTIONS 'TRAP(ON),TERMT,MSGFILE(SYSOUT,FBA,121,0,ENQ)'



Seems to work fine for us. We use WLM.





Regards,



Ray Price

Dresdner Kleinwort Wasserstein

London



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Tanya Haverchak
Sent: 09 December 2003 19:12
To: [login to unmask email]
Subject: Re: Creating sysout for each stored procedure



Avram,

I agree with all your points. But I would like to know how do you handle the
issue of error diagnosis in production stored procedures.

In our shop, if sql call in DB2 stored procedure that is being called from
the "web app" , returns negative sql code, SQLCODE along with SQLSTATE is
displayed to a sysout for ease of problem determination.

So far it has presented no problems. How ever, very recently we switched to
WLM managed stored procedures. With these procedures, the displays in WLM
address space, cause that particular address space to ABEND with a dump.

I would like to know how other shops dealing with this situation.





Tanya Haverchak
IBM Certified Solutions Expert
- DB2 V7 Database Administration OS/390
Antares Management Solutions
23700 Commerce Park Road
Beachwood, OH 44122-5832
Phone:(216) 292-0400 ext.4011
FAX: (216) 292-1619
E-mail: [login to unmask email]
<mailto:[login to unmask email]>



----------------------------------------------------------------------------
--
CONFIDENTIALITY NOTICE: This 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 or exempt from disclosure
under applicable law. If the reader of this message is not the intended
recipient or the employee or agent responsible for delivering the message
to the intended recipient, you are hereby notified that you are strictly
prohibited from printing, storing, disseminating, distributing or copying
this communication. If you have received this communication in error,
please notify us immediately by replying to the message and deleting it
from your computer. Thank You, Antares Management Solutions.
===
==


----------------------------------------------------------------------------
----- 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". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] 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


--------------------------------------------------------------------------------
The information contained herein is confidential and is intended solely for the
addressee. Access by any other party is unauthorised without the express
written permission of the sender. If you are not the intended recipient, please
contact the sender either via the company switchboard on +44 (0)20 7623 8000, or
via e-mail return. If you have received this e-mail in error or wish to read our
e-mail disclaimer statement and monitoring policy, please refer to
http://www.drkw.com/disc/email/ or contact the sender.
--------------------------------------------------------------------------------


---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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

Ava Collins

Re: Creating sysout for each stored procedure
(in response to Ray Price)
In application the calling applications logs information about the parameters and the resulting messages which include the SQL code and SQLstate.

We also use CA's Detector.

Jacquie

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] 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