Page 1 of 1

create procedure (SQL) failed

PostPosted: Mon 06 Jul 2009, 00:35
by alexg
I am having problem creating procedures. Here's an example:
CREATE PROCEDURE sum ( IN p_a INTEGER, IN p_b INTEGER, OUT p_s INTEGER)
LANGUAGE SQL
BEGIN
SET p_s = p_a + p_b;
END

The error:
SQL0969N There is no message text corresponding to SQL error "-20071" in the message file on this workstation. The error was returned from module "DSNXIVRO" with original tokens "SQLDBX.SUM". SQLSTATE=53099

Re: create procedure (SQL) failed

PostPosted: Mon 06 Jul 2009, 03:50
by sysprog
You would get better answers if you post this question to DB2 forum, like here:
http://www.ibm.com/developerworks/forum ... orumID=293

Re: create procedure (SQL) failed

PostPosted: Thu 09 Jul 2009, 02:01
by alexg
It seems that this failure has to do with availability of WLM ENVIRONMENT.
I tried to use DB8GENV1 and was able to create procedure, but know if fails to execute.
It seems that sysadmin has to enable WLM ENVIRONMENT by executing below command
MVS WLM VARY APPLENV= applenvname, RESUME
I have no idea from where and how to run it

Re: create procedure (SQL) failed

PostPosted: Thu 09 Jul 2009, 03:31
by sysprog
Please use following available WLM application environment.
Here is the available application environment in Workload Manager of our system:
Code: Select all
D WLM,APPLENV=*                                       
IWM029I  23.24.07  WLM DISPLAY 370                     
  APPLICATION ENVIRONMENT NAME     STATE     STATE DATA
  ...
  DB8GENVA                         AVAILABLE           
  DB8GENVC                         AVAILABLE           
  DB8GENVJ                         AVAILABLE           
  DB8GENV1                         AVAILABLE           
  DB8GENV2                         AVAILABLE           
  DB8GENV3                         AVAILABLE           
  DB8GRFSH                         AVAILABLE

Re: create procedure (SQL) failed

PostPosted: Sat 24 Oct 2009, 04:43
by lkmfdba
There are lot of WLM APPLENVs that are in STOPPED STATUS.
Please start them.

Is it possible to deploy a STORPROC from DB2 connect . I think it requires RRS/MVS.

Re: create procedure (SQL) failed

PostPosted: Mon 26 Oct 2009, 02:50
by lkmfdba
Lot of the WLM addr spcs are in STOPPED STATUS without which STORPROC cant run.
It seems that users do not have access to do a VARY WLM,APPLENV=xxx
Can all the WLM address spaces be STARTED please?

Re: create procedure (SQL) failed

PostPosted: Sun 15 Nov 2009, 01:11
by lkmfdba
Stored procedure address spaces are very useful and it seems like we dont have access to issue VARY over APPLENVs
It would be great if the SYSPROG can issue a VARY command from the SDSF to start all the WLM APPLENV address spaces that are in stopped status.

COMMAND INPUT ===> /D WLM,APPLENV=* SCROLL ===> CSR
RESPONSE=ADCD
IWM029I 16.47.17 WLM DISPLAY 560
APPLICATION ENVIRONMENT NAME STATE STATE DATA
BBOC001 AVAILABLE
BBODMGR AVAILABLE
DB6GENVA AVAILABLE
DB6GENV1 AVAILABLE
DB6GRFSH AVAILABLE
DB7GENVC AVAILABLE
DB7GENVJ AVAILABLE
DB7GENV1 AVAILABLE
DB7GENV2 AVAILABLE
DB7GENV7 AVAILABLE
DB7GRFSH AVAILABLE
DB8GENVA STOPPED
DB8GENVC STOPPED
DB8GENVJ STOPPED
DB8GENV1 STOPPED
DB8GENV2 STOPPED
DB8GENV3 STOPPED
DB8GRFSH STOPPED

/VARY WLM,APPLENV=

Re: create procedure (SQL) failed

PostPosted: Tue 17 Nov 2009, 04:41
by lkmfdba
If it is possible kindly give access to execute VARY command so that I can RESUME/QUIESCE the Address spaces myself and maintain DB2 sps.
Thanks