WLM Problem with DB2 stored procedures in V7.

Dan Lamas

WLM Problem with DB2 stored procedures in V7.
Hello everyone,

We are in the middle of testing our upgrade to v7 in our lpar (mirror of
prod) os390 / v210. We are using JAVA to access DB2 and cannot get our WLM
SP's to work. We have WLM SP's in our v6 test environment and they are
working fine.

I have been trying to read every manual to find out if I missed something.

Here is the error we are receiving:

2002-09-25 15:58:49.710 - ERROR - <StoredProcedure_Impl> Stored Procedure
ILS_TET.ILCP813S is probably not running
2002-09-25 15:58:49.710 - ERROR - <StoredProcedure_Impl> Error executing
procedure com.duke.persistence.StoredProcedure_Impl Named: ILS_TET.ILCP813S
with 6 [login to unmask email]
index: 1 Value RV, Type IN, Out Type 1
[login to unmask email] index: 2 Value
910363, Type IN, Out Type 1
[login to unmask email] index: 3 Value
910363, Type IN, Out Type 1
[login to unmask email] index: 4 Value
null, Type OUT, Out Type 4
[login to unmask email] index: 5 Value
null, Type OUT, Out Type 4
[login to unmask email] index: 6 Value
null, Type OUT, Out Type 1
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0471N Invocation
of routine "ILS_TET .ILCP813S " failed due to reason "00E7900C".
SQLSTATE=55023

2002-09-25 15:58:49.710 - WARNING - <DatabaseSession>
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0471N Invocation
of routine "ILS_TET .ILCP813S " failed due to reason "00E7900C".
SQLSTATE=55023

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0471N Invocation
of routine "ILS_TET .ILCP813S " failed due to reason "00E7900C".
SQLSTATE=55023

The error messages and codes manual states that the user-written routine is
not defined or is currently not available for DB2 use in the active WLM
policy .

I have check and re-checked. In WLM the application environment is
available. The JCL proc has been created. A STC RACF id has been
established. RRS is active. I have re-compiled the program using DSNRLI.
Dropped the procedure and re-created it specifying the WLM Environment.
And still I get nothing!!

Please advise.


Thanks,

Dan Lamas
Duke Energy



[login to unmask email]

Re: WLM Problem with DB2 stored procedures in V7.
(in response to Dan Lamas)
I am sure that you looked up the error code

00E7900C
Explanation: SQL CALL statement or user-defined function invocation
could
not be processed. The WLM application environment name specified for the
user-written routine is not defined or is currently not available for DB2
use in the active WLM policy.
System Action: The SQL statement is rejected with SQLCODE -471 and this
reason code.
Programmer Response: Use the -ALTER PROCEDURE or -ALTER FUNCTION command
to update the WLM ENVIRONMENT to specify an application environment that
is defined and active in the active WLM policy, or update the WLM policy
to contain a valid application environment name.
If the WLM policy definition is updated, use the MVS WLM VARY command to
activate this new definition.

But if not here it is right out of the V7 book.
John C. Lendman
DBA
[login to unmask email]
(561) 694-5085
Beeper FPL 7413



"Dan Lamas"
<[login to unmask email] To: [login to unmask email]
GY.COM> cc:
Sent by: "DB2 Subject: WLM Problem with DB2 stored procedures in V7.
Data Base
Discussion List"
<[login to unmask email]
LASSOC.COM>


09/26/02 10:29 AM
Please respond to
"DB2 Data Base
Discussion List"






Hello everyone,

We are in the middle of testing our upgrade to v7 in our lpar (mirror of
prod) os390 / v210. We are using JAVA to access DB2 and cannot get our WLM
SP's to work. We have WLM SP's in our v6 test environment and they are
working fine.

I have been trying to read every manual to find out if I missed something.

Here is the error we are receiving:

2002-09-25 15:58:49.710 - ERROR - <StoredProcedure_Impl> Stored Procedure
ILS_TET.ILCP813S is probably not running
2002-09-25 15:58:49.710 - ERROR - <StoredProcedure_Impl> Error executing
procedure com.duke.persistence.StoredProcedure_Impl Named: ILS_TET.ILCP813S
with 6 [login to unmask email]
index: 1 Value RV, Type IN, Out Type 1
[login to unmask email] index: 2 Value
910363, Type IN, Out Type 1
[login to unmask email] index: 3 Value
910363, Type IN, Out Type 1
[login to unmask email] index: 4 Value
null, Type OUT, Out Type 4
[login to unmask email] index: 5 Value
null, Type OUT, Out Type 4
[login to unmask email] index: 6 Value
null, Type OUT, Out Type 1
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0471N Invocation
of routine "ILS_TET .ILCP813S " failed due to reason "00E7900C".
SQLSTATE=55023

2002-09-25 15:58:49.710 - WARNING - <DatabaseSession>
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0471N Invocation
of routine "ILS_TET .ILCP813S " failed due to reason "00E7900C".
SQLSTATE=55023

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0471N Invocation
of routine "ILS_TET .ILCP813S " failed due to reason "00E7900C".
SQLSTATE=55023

The error messages and codes manual states that the user-written routine is
not defined or is currently not available for DB2 use in the active WLM
policy .

I have check and re-checked. In WLM the application environment is
available. The JCL proc has been created. A STC RACF id has been
established. RRS is active. I have re-compiled the program using DSNRLI.
Dropped the procedure and re-created it specifying the WLM Environment.
And still I get nothing!!

Please advise.


Thanks,

Dan Lamas
Duke Energy



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Simon George

Re: WLM Problem with DB2 stored procedures in V7.
(in response to John_Lendman@FPL.COM)
Only thing I can think of is that the WLM environment has been stopped for some reason. Check by issuing a

D WLM,APPLENV=*

command and see if its started. If its not start it with:

VARY WLM,APPLENV=<wlmenv>,RESUME

Regards Simon

-----Original Message-----
From: Dan Lamas [mailto:[login to unmask email]
Sent: 26 September 2002 15:30
To: [login to unmask email]
Subject: WLM Problem with DB2 stored procedures in V7.


Hello everyone,

We are in the middle of testing our upgrade to v7 in our lpar (mirror of
prod) os390 / v210. We are using JAVA to access DB2 and cannot get our WLM
SP's to work. We have WLM SP's in our v6 test environment and they are
working fine.

I have been trying to read every manual to find out if I missed something.

Here is the error we are receiving:

2002-09-25 15:58:49.710 - ERROR - <StoredProcedure_Impl> Stored Procedure
ILS_TET.ILCP813S is probably not running
2002-09-25 15:58:49.710 - ERROR - <StoredProcedure_Impl> Error executing
procedure com.duke.persistence.StoredProcedure_Impl Named: ILS_TET.ILCP813S
with 6 [login to unmask email]
index: 1 Value RV, Type IN, Out Type 1
[login to unmask email] index: 2 Value
910363, Type IN, Out Type 1
[login to unmask email] index: 3 Value
910363, Type IN, Out Type 1
[login to unmask email] index: 4 Value
null, Type OUT, Out Type 4
[login to unmask email] index: 5 Value
null, Type OUT, Out Type 4
[login to unmask email] index: 6 Value
null, Type OUT, Out Type 1
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0471N Invocation
of routine "ILS_TET .ILCP813S " failed due to reason "00E7900C".
SQLSTATE=55023

2002-09-25 15:58:49.710 - WARNING - <DatabaseSession>
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0471N Invocation
of routine "ILS_TET .ILCP813S " failed due to reason "00E7900C".
SQLSTATE=55023

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0471N Invocation
of routine "ILS_TET .ILCP813S " failed due to reason "00E7900C".
SQLSTATE=55023

The error messages and codes manual states that the user-written routine is
not defined or is currently not available for DB2 use in the active WLM
policy .

I have check and re-checked. In WLM the application environment is
available. The JCL proc has been created. A STC RACF id has been
established. RRS is active. I have re-compiled the program using DSNRLI.
Dropped the procedure and re-created it specifying the WLM Environment.
And still I get nothing!!

Please advise.


Thanks,

Dan Lamas
Duke Energy




Visit our website at http://www.ubswarburg.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mail transmission cannot be guaranteed to be secure or error-free
as information could be intercepted, corrupted, lost, destroyed,
arrive late or incomplete, or contain viruses. The sender therefore
does not accept liability for any errors or omissions in the contents
of this message which arise as a result of e-mail transmission. If
verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities or
related financial instruments.



Gregg Sawyer

Re: WLM Problem with DB2 stored procedures in V7.
(in response to Simon George)
In practical terms, the WLM_ENVIRONMENT value for that SP in
SYSIBM.SYSROUTINES should match a value in your WLM policy for that
environment.

Supplementally, you also need a started task for the specific WLM
environment (third EXEC PARM value for DSNX9WLM).

Gregg Sawyer





"John C. Lendman" <[login to unmask email]>@LISTSERV.YLASSOC.COM> on
09/26/2002 11:08:17 AM

Please respond to DB2 Data Base Discussion List
<[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Re: WLM Problem with DB2 stored procedures in V7.


I am sure that you looked up the error code

00E7900C
Explanation: SQL CALL statement or user-defined function invocation
could
not be processed. The WLM application environment name specified for the
user-written routine is not defined or is currently not available for DB2
use in the active WLM policy.
System Action: The SQL statement is rejected with SQLCODE -471 and this
reason code.
Programmer Response: Use the -ALTER PROCEDURE or -ALTER FUNCTION command
to update the WLM ENVIRONMENT to specify an application environment that
is defined and active in the active WLM policy, or update the WLM policy
to contain a valid application environment name.
If the WLM policy definition is updated, use the MVS WLM VARY command to
activate this new definition.

But if not here it is right out of the V7 book.
John C. Lendman
DBA
[login to unmask email]
(561) 694-5085
Beeper FPL 7413



"Dan Lamas"
<[login to unmask email] To:
[login to unmask email]
GY.COM> cc:
Sent by: "DB2 Subject: WLM Problem with
DB2 stored procedures in V7.
Data Base
Discussion List"
<[login to unmask email]
LASSOC.COM>


09/26/02 10:29 AM
Please respond to
"DB2 Data Base
Discussion List"






Hello everyone,

We are in the middle of testing our upgrade to v7 in our lpar (mirror of
prod) os390 / v210. We are using JAVA to access DB2 and cannot get our WLM
SP's to work. We have WLM SP's in our v6 test environment and they are
working fine.

I have been trying to read every manual to find out if I missed something.

Here is the error we are receiving:

2002-09-25 15:58:49.710 - ERROR - <StoredProcedure_Impl> Stored Procedure
ILS_TET.ILCP813S is probably not running
2002-09-25 15:58:49.710 - ERROR - <StoredProcedure_Impl> Error executing
procedure com.duke.persistence.StoredProcedure_Impl Named: ILS_TET.ILCP813S
with 6 [login to unmask email]
index: 1 Value RV, Type IN, Out Type 1
[login to unmask email] index: 2 Value
910363, Type IN, Out Type 1
[login to unmask email] index: 3 Value
910363, Type IN, Out Type 1
[login to unmask email] index: 4 Value
null, Type OUT, Out Type 4
[login to unmask email] index: 5 Value
null, Type OUT, Out Type 4
[login to unmask email] index: 6 Value
null, Type OUT, Out Type 1
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0471N Invocation
of routine "ILS_TET .ILCP813S " failed due to reason "00E7900C".
SQLSTATE=55023

2002-09-25 15:58:49.710 - WARNING - <DatabaseSession>
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0471N Invocation
of routine "ILS_TET .ILCP813S " failed due to reason "00E7900C".
SQLSTATE=55023

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0471N Invocation
of routine "ILS_TET .ILCP813S " failed due to reason "00E7900C".
SQLSTATE=55023

The error messages and codes manual states that the user-written routine is
not defined or is currently not available for DB2 use in the active WLM
policy .

I have check and re-checked. In WLM the application environment is
available. The JCL proc has been created. A STC RACF id has been
established. RRS is active. I have re-compiled the program using DSNRLI.
Dropped the procedure and re-created it specifying the WLM Environment.
And still I get nothing!!

Please advise.


Thanks,

Dan Lamas
Duke Energy



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Dan Lamas

Re: WLM Problem with DB2 stored procedures in V7.
(in response to Gregg Sawyer)
1. Is there anything that our RACF group needs to do to make this work.
Besides just setting up a STC user id?

2.
Here is my proc for WLM.

//*************************************************************
//* JCL FOR RUNNING THE WLM-ESTABLISHED STORED PROCEDURES
//* ADDRESS SPACE
//* RGN -- THE MVS REGION SIZE FOR THE ADDRESS SPACE.
//* DB2SSN -- THE DB2 SUBSYSTEM NAME.
//* NUMTCB -- THE NUMBER OF TCBS USED TO PROCESS
//* END USER REQUESTS.
//* APPLENV -- THE MVS WLM APPLICATION ENVIRONMENT
//* SUPPORTED BY THIS JCL PROCEDURE.
//*
//*************************************************************
//DB2AWLM PROC RGN=32M,APPLENV=LINKPROD,DB2SSN=DB2A,NUMTCB=16
//IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT,
// PARM='&DB2SSN,&NUMTCB,&APPLENV'
//STEPLIB DD DISP=SHR,DSN=SYS1.DB2A.V710.RUNLIB.LOAD
// DD DISP=SHR,DSN=SYS1.CEE.SCEERUN
// DD DISP=SHR,DSN=SYS1.DB2A.V710.SDSNLOAD
// DD DISP=SHR,DSN=SYS1.DB2A.V710.SDSNLOD2
// DD DISP=SHR,DSN=TEGAS.ILP.PLOADLIB

Here is an excerpt from sysroutines.

PARM
SCHEMA OWNER NAME SPECIFICNAME COUNT
LANGUAGE
-------- -------- ------------------ ------------------ ------
--------
ILS_TET #ILPUPDT ILCP813S ILCP813S 6 COBOL


SQL
PARAMETER DATA WLM
STYLE FENCED ACCESS DBINFO STAYRESIDENT ASUTIME ENVIRONMENT
--------- ------ ------ ------ ------------ -----------
--------------
G Y M N Y 517410 LINKPROD

WLM
ENV COMMIT
FOR PROGRAM EXTERNAL ON RESULT
NESTED TYPE SECURITY RETURN SETS LOBCOLUMNS CREATEDTS
------ ------- -------- ------ ------ ---------- ------------------
N M D N 10 0 2002-09-25-15.52.4









Gregg Sawyer
<[login to unmask email] To: [login to unmask email]
RIZON.COM> cc:
Sent by: DB2 Data Subject: Re: WLM Problem with DB2 stored
Base Discussion procedures in V7.
List
<[login to unmask email]
ASSOC.COM>


09/26/2002 11:09
AM
Please respond to
DB2 Data Base
Discussion List






In practical terms, the WLM_ENVIRONMENT value for that SP in
SYSIBM.SYSROUTINES should match a value in your WLM policy for that
environment.

Supplementally, you also need a started task for the specific WLM
environment (third EXEC PARM value for DSNX9WLM).

Gregg Sawyer



Gregg Sawyer

Re: WLM Problem with DB2 stored procedures in V7.
(in response to Dan Lamas)
Dan,

1) You do need to invoke the SP with an ID that has, or connects to a RACF
ID that has, EXECUTE auth. on the SP; however, I generally see SQLCODE -440
(a classic example of DB2 obfuscation) if this is not set up correctly, so
I doubt it is the direct cause of the problem you reported originally.

2) Looks good as far as it goes (assuming that SYSROUTINES listing is from
subsystem DB2A), but you also need to check your WLM policy; I'm not
familiar with this process directly, so I'll just point you to Chapter
5.13.2 of DB2 UDB for OS/390 V6 Administration Guide.

Perhaps more knowledgeable listers will offer more advice.

Gregg Sawyer





Dan Lamas <[login to unmask email]>@LISTSERV.YLASSOC.COM> on 09/26/2002
02:11:09 PM

Please respond to DB2 Data Base Discussion List
<[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Re: WLM Problem with DB2 stored procedures in V7.


1. Is there anything that our RACF group needs to do to make this work.
Besides just setting up a STC user id?

2.
Here is my proc for WLM.

//*************************************************************
//* JCL FOR RUNNING THE WLM-ESTABLISHED STORED PROCEDURES
//* ADDRESS SPACE
//* RGN -- THE MVS REGION SIZE FOR THE ADDRESS SPACE.
//* DB2SSN -- THE DB2 SUBSYSTEM NAME.
//* NUMTCB -- THE NUMBER OF TCBS USED TO PROCESS
//* END USER REQUESTS.
//* APPLENV -- THE MVS WLM APPLICATION ENVIRONMENT
//* SUPPORTED BY THIS JCL PROCEDURE.
//*
//*************************************************************
//DB2AWLM PROC RGN=32M,APPLENV=LINKPROD,DB2SSN=DB2A,NUMTCB=16
//IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT,
// PARM='&DB2SSN,&NUMTCB,&APPLENV'
//STEPLIB DD DISP=SHR,DSN=SYS1.DB2A.V710.RUNLIB.LOAD
// DD DISP=SHR,DSN=SYS1.CEE.SCEERUN
// DD DISP=SHR,DSN=SYS1.DB2A.V710.SDSNLOAD
// DD DISP=SHR,DSN=SYS1.DB2A.V710.SDSNLOD2
// DD DISP=SHR,DSN=TEGAS.ILP.PLOADLIB

Here is an excerpt from sysroutines.

PARM
SCHEMA OWNER NAME SPECIFICNAME COUNT
LANGUAGE
-------- -------- ------------------ ------------------ ------
--------
ILS_TET #ILPUPDT ILCP813S ILCP813S 6 COBOL


SQL
PARAMETER DATA WLM
STYLE FENCED ACCESS DBINFO STAYRESIDENT ASUTIME ENVIRONMENT
--------- ------ ------ ------ ------------ -----------
--------------
G Y M N Y 517410 LINKPROD

WLM
ENV COMMIT
FOR PROGRAM EXTERNAL ON RESULT
NESTED TYPE SECURITY RETURN SETS LOBCOLUMNS CREATEDTS
------ ------- -------- ------ ------ ---------- ------------------
N M D N 10 0 2002-09-25-15.52.4









Gregg Sawyer
<[login to unmask email] To:
[login to unmask email]
RIZON.COM> cc:
Sent by: DB2 Data Subject: Re: WLM Problem
with DB2 stored
Base Discussion procedures in V7.
List
<[login to unmask email]
ASSOC.COM>


09/26/2002 11:09
AM
Please respond to
DB2 Data Base
Discussion List






In practical terms, the WLM_ENVIRONMENT value for that SP in
SYSIBM.SYSROUTINES should match a value in your WLM policy for that
environment.

Supplementally, you also need a started task for the specific WLM
environment (third EXEC PARM value for DSNX9WLM).

Gregg Sawyer



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Dan Lamas

Re: WLM Problem with DB2 stored procedures in V7.
(in response to Gregg Sawyer)
Thanks to everyone who helped on this topic. The sysprogs looked closer at
the WLM policies for the Applenv. There was a typo in the subsystem type
field that was causing the problem. After they fixed the typo everything
started working.

Thanks again!

Dan



Phil Jackson

WLM
(in response to Dan Lamas)
List,

We are soon going to be moving some cobol stored procedures to our
production environment on OS/390. These were designed primarily for use by
ASP pages
running on Win2000 server. We have a production Batch cobol loadlib and a
production CICS cobol load lib. Both of these are defined to our production
WLM
environment. My question is "What is the most common/best practice?" Is it
typical to define special libaries for stored procs that the WLM will
monitor? Or is it
ok ot throw them in with the batch or cics programs? Any benefits either
way?

thanks,

Phil Jackson

James Campbell

Re: WLM
(in response to Phil Jackson)
The "killer" as far as common libraries is what happens if your
sp/udf wants to dynamically call a common subroutine that accesses
DB2? In a batch environment it has to be linked to DSNELI (well,
usually), in CICS DSNCLI and in the WLM SPAS DSNRLI.

James Campbell

On 17 Oct 2002 at 9:24, Jackson, Phil wrote:

> List,
>
> We are soon going to be moving some cobol stored procedures to our
> production environment on OS/390. These were designed primarily for use by
> ASP pages
> running on Win2000 server. We have a production Batch cobol loadlib and a
> production CICS cobol load lib. Both of these are defined to our production
> WLM
> environment. My question is "What is the most common/best practice?" Is it
> typical to define special libaries for stored procs that the WLM will
> monitor? Or is it
> ok ot throw them in with the batch or cics programs? Any benefits either
> way?
>
> thanks,
>
> Phil Jackson
>



Kirk Hampton

Re: WLM
(in response to James Campbell)
We have exactly the situation James has described, and for this reason we
have a third production loadlib for stored procedures alone, and we have
the
same "common" routine linked three different ways (batch, CICS, and SP).





James Campbell <[login to unmask email]>@LISTSERV.YLASSOC.COM> on
10/17/2002 04:42:59 PM

Please respond to DB2 Data Base Discussion List
<[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Re: WLM


The "killer" as far as common libraries is what happens if your
sp/udf wants to dynamically call a common subroutine that accesses
DB2? In a batch environment it has to be linked to DSNELI (well,
usually), in CICS DSNCLI and in the WLM SPAS DSNRLI.

James Campbell

On 17 Oct 2002 at 9:24, Jackson, Phil wrote:

> List,
>
> We are soon going to be moving some cobol stored procedures to our
> production environment on OS/390. These were designed primarily for use
by
> ASP pages
> running on Win2000 server. We have a production Batch cobol loadlib and
a
> production CICS cobol load lib. Both of these are defined to our
production
> WLM
> environment. My question is "What is the most common/best practice?" Is
it
> typical to define special libaries for stored procs that the WLM will
> monitor? Or is it
> ok ot throw them in with the batch or cics programs? Any benefits either
> way?
>
> thanks,
>
> Phil Jackson
>



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can





**********************************************************************************
Confidentiality Notice: This email message, including any attachments,
contains or may contain confidential information intended only for the
addressee. If you are not an intended recipient of this message, be
advised that any reading, dissemination, forwarding, printing, copying
or other use of this message or its attachments is strictly prohibited. If
you have received this message in error, please notify the sender
immediately by reply message and delete this email message and any
attachments from your system.
**********************************************************************************



Chuck Kosin

Re: Problem with stored procedure
(in response to Kirk Hampton)
Please let me know if this problem has been solved. We are getting the
same thing. An occasional DSNX962I.

20.26.25 STC06832 IEA995I SYMPTOM DUMP
OUTPUT
USER COMPLETION CODE=4039 REASON
CODE=00000000
TIME=20.26.12 SEQ=44440 CPU=0000
ASID=0093
PSW AT TIME OF ERROR 078D1000 9C6D750E ILC 2 INTC
0D
ACTIVE LOAD MODULE ADDRESS=1C5B8870
OFFSET=0011

NAME=CEEPLPKA
DATA AT PSW 1C6D7508 - 00181610 0A0D58D0
D00498EC
GR 0: 84000000 1:
84000FC7
2: 1DB27E10 3:
00000000
4: 1DB27E10 5:
00000010
6: 1DB27BD8 7:
1DB28797
8: 1C6555F5 9:
1C6545F6
A: 1DB27BD8 B:
1C6D7438
C: 00067A00 D:
1DB29EC8
E: 9C654E40 F:
00000000
END OF SYMPTOM
DUMP
20.26.25 STC06832 +DSNX962I DSNX9SER THE LE/370 CEEPIPI SERVICE RETURNED
AN UNEXPECTED RETURN CODE '00000008'X FOR FUNCTION TERM SSN= DB2E
DB2ESPAS ASID= 0093
WLM_ENV=
DB2ESPAS ASID= 0093 WLM_ENV=

On Fri, 28 Jun 2002 13:04:16 +0200, Calleja Vilar, Juan
<[login to unmask email]> wrote:

>Hello, list. We are having problems with a stored procedure. Occasionally
>the execution of this SP is not successful. In those cases the following
>message is written to the SPAS joblog:
>
>+DSNX962I DSNX9SER THE LE/370 CEEPIPI SERVICE RETURNED AN UNEXPECTED
> RETURN CODE '00000008'X FOR FUNCTION TERM SSN= DS2P PROC=
> DS2PSPAS ASID= 0070 WLM_ENV=
>
>Any idea on this topic will be highly appreciated.
>
>Thanks in advance.
>
>Juan Calleja Vilar
>IBERDROLA, S.A.
>



Marcel Harleman

Re: Problem with stored procedure
(in response to Chuck Kosin)
Hi Chuck,

it seems to me you are trying to abend a stored procedure (because of
some SQLcode?). I think it's better to issue a returncode and return
back to the calling program so the abend-handling can take place from
the coordinators point of view. If you want to rollback changes before
you return then issue a rollback, a returncode and return. I don't
think you will see these messages again then.

Marcel.

>Please let me know if this problem has been solved. We are getting the
>same thing. An occasional DSNX962I.
>
>20.26.25 STC06832 IEA995I SYMPTOM DUMP
>OUTPUT
> USER COMPLETION CODE=4039 REASON
>CODE=00000000
> TIME=20.26.12 SEQ=44440 CPU=0000
>ASID=0093
> PSW AT TIME OF ERROR 078D1000 9C6D750E ILC 2 INTC
>0D
> ACTIVE LOAD MODULE ADDRESS=1C5B8870
>OFFSET=0011
>
>NAME=CEEPLPKA
> DATA AT PSW 1C6D7508 - 00181610 0A0D58D0
>D00498EC
> GR 0: 84000000 1:
>84000FC7
> 2: 1DB27E10 3:
>00000000
> 4: 1DB27E10 5:
>00000010
> 6: 1DB27BD8 7:
>1DB28797
> 8: 1C6555F5 9:
>1C6545F6
> A: 1DB27BD8 B:
>1C6D7438
> C: 00067A00 D:
>1DB29EC8
> E: 9C654E40 F:
>00000000
> END OF SYMPTOM
>DUMP
>20.26.25 STC06832 +DSNX962I DSNX9SER THE LE/370 CEEPIPI SERVICE RETURNED
>AN UNEXPECTED RETURN CODE '00000008'X FOR FUNCTION TERM SSN= DB2E
> DB2ESPAS ASID= 0093
>WLM_ENV=
>DB2ESPAS ASID= 0093 WLM_ENV=
>
>On Fri, 28 Jun 2002 13:04:16 +0200, Calleja Vilar, Juan
><[login to unmask email]> wrote:
>
>>Hello, list. We are having problems with a stored procedure. Occasionally
>>the execution of this SP is not successful. In those cases the following
>>message is written to the SPAS joblog:
>>
>>+DSNX962I DSNX9SER THE LE/370 CEEPIPI SERVICE RETURNED AN UNEXPECTED
>> RETURN CODE '00000008'X FOR FUNCTION TERM SSN= DS2P PROC=
>> DS2PSPAS ASID= 0070 WLM_ENV=
>>
>>Any idea on this topic will be highly appreciated.
>>
>>Thanks in advance.
>>
>>Juan Calleja Vilar
>>IBERDROLA, S.A.
>>
>
>
>



Mark Anzmann

Re: Problem with stored procedure
(in response to Marcel Harleman)
Are you by chance logging any information to the address (displays in the program/SP)?

We had what I recall was a similar return code when 2 executions of the SP tried to write output to the address space at the same time.

We just removed the displays and have not had the problem since.

Mark

Mark Anzmann
Tech Services Manager
DHRIS Program - Team Accenture
Phone: 410-238-1213
email: [login to unmask email]

>>> [login to unmask email] 12/11/02 09:41PM >>>
Please let me know if this problem has been solved. We are getting the
same thing. An occasional DSNX962I.

20.26.25 STC06832 IEA995I SYMPTOM DUMP
OUTPUT
USER COMPLETION CODE=4039 REASON
CODE=00000000
TIME=20.26.12 SEQ=44440 CPU=0000
ASID=0093
PSW AT TIME OF ERROR 078D1000 9C6D750E ILC 2 INTC
0D
ACTIVE LOAD MODULE ADDRESS=1C5B8870
OFFSET=0011

NAME=CEEPLPKA
DATA AT PSW 1C6D7508 - 00181610 0A0D58D0
D00498EC
GR 0: 84000000 1:
84000FC7
2: 1DB27E10 3:
00000000
4: 1DB27E10 5:
00000010
6: 1DB27BD8 7:
1DB28797
8: 1C6555F5 9:
1C6545F6
A: 1DB27BD8 B:
1C6D7438
C: 00067A00 D:
1DB29EC8
E: 9C654E40 F:
00000000
END OF SYMPTOM
DUMP
20.26.25 STC06832 +DSNX962I DSNX9SER THE LE/370 CEEPIPI SERVICE RETURNED
AN UNEXPECTED RETURN CODE '00000008'X FOR FUNCTION TERM SSN= DB2E
DB2ESPAS ASID= 0093
WLM_ENV=
DB2ESPAS ASID= 0093 WLM_ENV=

On Fri, 28 Jun 2002 13:04:16 +0200, Calleja Vilar, Juan
<[login to unmask email]> wrote:

>Hello, list. We are having problems with a stored procedure. Occasionally
>the execution of this SP is not successful. In those cases the following
>message is written to the SPAS joblog:
>
>+DSNX962I DSNX9SER THE LE/370 CEEPIPI SERVICE RETURNED AN UNEXPECTED
> RETURN CODE '00000008'X FOR FUNCTION TERM SSN= DS2P PROC=
> DS2PSPAS ASID= 0070 WLM_ENV=
>
>Any idea on this topic will be highly appreciated.
>
>Thanks in advance.
>
>Juan Calleja Vilar
>IBERDROLA, S.A.
>






Kirk Hampton

Re: Problem with stored procedure
(in response to Mark Anzmann)
Also, make sure that no one has added a CALL from your main program to a
sub-module that may be compiled with older, non-supported language such as
COBOL II.

Kirk Hampton
DB2 OS/390 Sysprog
IBM Certified Solutions Expert - DB2 V7 Database Administration OS/390
TXU Business Services
Dallas, Texas





Mark Anzmann <[login to unmask email]>@LISTSERV.YLASSOC.COM> on 12/12/2002
06:55:30 AM

Please respond to DB2 Data Base Discussion List
<[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Re: Problem with stored procedure


Are you by chance logging any information to the address (displays in the
program/SP)?

We had what I recall was a similar return code when 2 executions of the SP
tried to write output to the address space at the same time.

We just removed the displays and have not had the problem since.

Mark

Mark Anzmann
Tech Services Manager
DHRIS Program - Team Accenture
Phone: 410-238-1213
email: [login to unmask email]

>>> [login to unmask email] 12/11/02 09:41PM >>>
Please let me know if this problem has been solved. We are getting the
same thing. An occasional DSNX962I.

20.26.25 STC06832 IEA995I SYMPTOM DUMP
OUTPUT
USER COMPLETION CODE=4039 REASON
CODE=00000000
TIME=20.26.12 SEQ=44440 CPU=0000
ASID=0093
PSW AT TIME OF ERROR 078D1000 9C6D750E ILC 2 INTC
0D
ACTIVE LOAD MODULE ADDRESS=1C5B8870
OFFSET=0011

NAME=CEEPLPKA
DATA AT PSW 1C6D7508 - 00181610 0A0D58D0
D00498EC
GR 0: 84000000 1:
84000FC7
2: 1DB27E10 3:
00000000
4: 1DB27E10 5:
00000010
6: 1DB27BD8 7:
1DB28797
8: 1C6555F5 9:
1C6545F6
A: 1DB27BD8 B:
1C6D7438
C: 00067A00 D:
1DB29EC8
E: 9C654E40 F:
00000000
END OF SYMPTOM
DUMP
20.26.25 STC06832 +DSNX962I DSNX9SER THE LE/370 CEEPIPI SERVICE RETURNED
AN UNEXPECTED RETURN CODE '00000008'X FOR FUNCTION TERM SSN= DB2E
DB2ESPAS ASID= 0093
WLM_ENV=
DB2ESPAS ASID= 0093 WLM_ENV=

On Fri, 28 Jun 2002 13:04:16 +0200, Calleja Vilar, Juan
<[login to unmask email]> wrote:

>Hello, list. We are having problems with a stored procedure. Occasionally
>the execution of this SP is not successful. In those cases the following
>message is written to the SPAS joblog:
>
>+DSNX962I DSNX9SER THE LE/370 CEEPIPI SERVICE RETURNED AN UNEXPECTED
> RETURN CODE '00000008'X FOR FUNCTION TERM SSN= DS2P PROC=
> DS2PSPAS ASID= 0070 WLM_ENV=
>
>Any idea on this topic will be highly appreciated.
>
>Thanks in advance.
>
>Juan Calleja Vilar
>IBERDROLA, S.A.
>



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can





**********************************************************************************
Confidentiality Notice: This email message, including any attachments,
contains or may contain confidential information intended only for the
addressee. If you are not an intended recipient of this message, be
advised that any reading, dissemination, forwarding, printing, copying
or other use of this message or its attachments is strictly prohibited.
If you have received this message in error, please notify the sender
immediately by reply message and delete this email message and any
attachments from your system.
**********************************************************************************



Fred Waterman

WLM Store Procedures
(in response to Kirk Hampton)
I'm looking for recommendations.
Do we set up the TCB value at one per address space or say ten and create an
additional address spaces when that value is exceeded?
The only down side to multiple TCB's is if there is one in a loop, you would
loose more than just the one when canceled.

Thanks ahead of time..



Richard Humphris

Re: Problem with stored procedure
(in response to Fred Waterman)
We too had a problem when two stored procedures were doing display(s) to the
SYSOUT file simultaneously. We solved the problem by creating our own LE
loadlib for use by stored procedures where we changed the default LE run
time options.

The (LE) Language Environment Customization manual shows the default CEEXOPT
macro which has a line like:
...
MSGFILE=((SYSOUT,FBA,121,0,NOENQ),OVR),
...

We changed this to:
...
MSGFILE=((SYSOUT,FBA,121,0,ENQ),OVR),
...

and this solved the multiple display problem in the ssidSPAS address space
by ensuring that writes/put(s) are single threaded before the next one is
attempted. Although normally this is not required, if multiple processes
share the same SYSOUT file then you need to use ENQ processing. Thus, the
ssidSPAS address space which can run multiple and/or duplicate stored
procedures concurrently needs to use ENQ processing with the SYSOUT file.

Rich Humphris
CNA



Richard Humphris

Re: Problem with stored procedure
(in response to Richard Humphris)
I found an alternate way of handling the SYSOUT file w/LE and stored
procedures... note: I copied the following from an IBM web site on stored
procedures
(at http://www-3.ibm.com/software/data/db2/os390/spb/exciting/spFAQs.html):
...
...
4. When writing debug statements, how do I use the ENQ option to avoid 02A
abends?
Q. I'm trying to debug my stored procedures and have print statements in
it, but when I run more than one task in the stored procedures address
space, I see 02A abends.
A. LE/370 has added support to serialize writes to the JES2 queue in order
to solve this. Apply LE APAR PQ14532
and change runopts in SYSPROCEDURES or CREATE PROCEDURE to include the
string 'MSGFILE(SYSOUT,FBA,121,0, ENQ)'.
...
...


Rich Humphris
CNA