create procedure (SQL) failed

[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

[sysprog]

You would get better answers if you post this question to DB2 forum, like here:
http://www.ibm.com/developerworks/forum ... orumID=293

[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

[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

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

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

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

[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