PROGRAM TYPE SUB for ALTER PROCEDURE

ramesh bala

PROGRAM TYPE SUB for ALTER PROCEDURE
Hello listers. No good deed goes unpunished. My sysprog and I pushed ahead
a Stored procedure definition change in production (after due testing in
development) that corrupted our production database. The change was to
alter the stored procedure from PROGRAM TYPE MAIN to SUB. This ostensibly
reduced I/O on the Loadlib.

The stored procedures to me it seems operated with NULL parameters or
invalid parameters from prev invocation and corrupted our database. Does
anybody have a similar exciting experience to share ?
Ramesh Bala

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

Re: PROGRAM TYPE SUB for ALTER PROCEDURE
(in response to ramesh bala)
Ramesh, we experienced a problem when we were directed to alter PROGRAM
TYPE MAIN to SUB in order to improve performance. Some of the stored
procedures worked others didn't. It turned out that programmers that had
initialized working storage as part of their logic were unaffected. The
problem was with the programs that were depending upon PROGRAM TYPE MAIN to
do initialization for them. There were other problems with using SUB which
caused workload manager problems for us, but this was during our long and
painful experience with version 6. IBM did fix the workload manager
problem.

Bill Shipley
IBM Certified Solutions Expert -
DB2 UDB V7.1 Database Administration for OS/390
The Vanguard Group, Inc.
Malvern, PA



Ramesh Bala
<[login to unmask email]> To: [login to unmask email]
Sent by: DB2 Data cc:
Base Discussion Subject: PROGRAM TYPE SUB for ALTER PROCEDURE
List
<[login to unmask email]
ORG>


12/07/2004 12:34
PM
Please respond to
DB2 Database
Discussion list
at IDUG








Hello listers. No good deed goes unpunished. My sysprog and I pushed ahead
a Stored procedure definition change in production (after due testing in
development) that corrupted our production database. The change was to
alter the stored procedure from PROGRAM TYPE MAIN to SUB. This ostensibly
reduced I/O on the Loadlib.

The stored procedures to me it seems operated with NULL parameters or
invalid parameters from prev invocation and corrupted our database. Does
anybody have a similar exciting experience to share ?
Ramesh Bala

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

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

Patrick Steurs

Re: PROGRAM TYPE SUB for ALTER PROCEDURE
(in response to William Shipley)
HI,

We altered our SP from program type MAIN to SUB and resident NO to YES.
We had the same problems. We recommended our programmers to use the
following clause "program-id. progname INITIAL" in their cobol-programs
/ SP and routines to solve the problem of re-using working-storage of
other threads.

greetings

Patrick Steurs
DBA at National bank of Belgium


-----Original Message-----
From: Bill Shipley [mailto:[login to unmask email]
Sent: dinsdag 7 december 2004 21:03
To: [login to unmask email]
Subject: Re: PROGRAM TYPE SUB for ALTER PROCEDURE

Ramesh, we experienced a problem when we were directed to alter PROGRAM
TYPE MAIN to SUB in order to improve performance. Some of the stored
procedures worked others didn't. It turned out that programmers that
had
initialized working storage as part of their logic were unaffected. The
problem was with the programs that were depending upon PROGRAM TYPE MAIN
to
do initialization for them. There were other problems with using SUB
which
caused workload manager problems for us, but this was during our long
and
painful experience with version 6. IBM did fix the workload manager
problem.

Bill Shipley
IBM Certified Solutions Expert -
DB2 UDB V7.1 Database Administration for OS/390
The Vanguard Group, Inc.
Malvern, PA



Ramesh Bala
<[login to unmask email]> To:
[login to unmask email]
Sent by: DB2 Data cc:
Base Discussion Subject: PROGRAM TYPE
SUB for ALTER PROCEDURE
List
<[login to unmask email]
ORG>


12/07/2004 12:34
PM
Please respond to
DB2 Database
Discussion list
at IDUG








Hello listers. No good deed goes unpunished. My sysprog and I pushed
ahead
a Stored procedure definition change in production (after due testing in
development) that corrupted our production database. The change was to
alter the stored procedure from PROGRAM TYPE MAIN to SUB. This
ostensibly
reduced I/O on the Loadlib.

The stored procedures to me it seems operated with NULL parameters or
invalid parameters from prev invocation and corrupted our database. Does
anybody have a similar exciting experience to share ?
Ramesh Bala

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

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

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

ramesh bala

Re: PROGRAM TYPE SUB for ALTER PROCEDURE
(in response to Patrick Steurs)
Thanks to everybody who responded. I am requesting this so I can save my
fellow unsuspecting DBA's some pain and suffering (and data corruption).

Can IBM please update the documentation for the ALTER PROCEDURE command
to reflect the fact that program TYPE SUB should be used with caution and
only after ensuring that stored procedures are initializing the Working
storage correctly?

Thanks !
Ramesh Bala

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

Karthikeyan Vinayagam

Re: PROGRAM TYPE SUB for ALTER PROCEDURE
(in response to ramesh bala)
Hi Ramesh,

from the original mail, it sounded like the procedures are stayresident
(yes) and non-reentrant. If that is not the case, please ignore this.

Otherwise, you may want to consider making those programs re-entrant.

initialising variables, at its best, would only *enable* those programs to
become serially reusable not 'concurrently' reusable. It would prevent
problems like the one you described only if: wlm address spaces are single
tcb or there are no concurrent callers in a given service class.
These things could change in a heartbeat and you know what happens next.

Regards,Karthik


On Fri, 10 Dec 2004 14:12:26 -0600, Ramesh Bala <[login to unmask email]> wrote:

>Thanks to everybody who responded. I am requesting this so I can save my
>fellow unsuspecting DBA's some pain and suffering (and data corruption).
>
>Can IBM please update the documentation for the ALTER PROCEDURE command
>to reflect the fact that program TYPE SUB should be used with caution and
>only after ensuring that stored procedures are initializing the Working
>storage correctly?
>
>Thanks !
>Ramesh Bala
>
>---------------------------------------------------------------------------
------
>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 DB2-L-
[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