No authorized routine...   

Tried to post my problem here kept getting Disallowed Content detected. So I've attached a full description please have a look

What am I doing wrong
Why does it work in Data Studio, half works in Control Centre and NOT work at all in the .NET subroutine.

Please help

Hello thanks for your initial reply. Let me update you on what i've done using Control Centre. I executed the

SELECT ROUTINESCHEMA, ROUTINENAME
FROM SYSCAT.ROUTINES
WHERE ROUTINENAME = 'SP_GETDBSIZE_INFO'

and the routine was present. I then did (using control centre)

SELECT ROUTINENAME,PARMNAME, ORDINAL ,TYPENAME, LENGTH, SCALE
FROM SYSCAT.ROUTINEPARMS
WHERE ROUTINENAME = 'SP_GETDBSIZE_INFO'
ORDER BY ORDINAL

and got the following result

ROUTINENAME PARMNAME ORDINAL TYPENAME LENGTH SCALE

---

---

---

---

--------

---

SPGET_DBSIZE_INFO SQLSTATE_OUT 1 CHARACTER 5 0
SPGET_DBSIZE_INFO SQLCODE_OUT 2 INTEGER 4 0

So I think it exists

When I executed CALL SPGET_DBSIZE_INFO I got the error no authorised routine.....

I went back to Data studio 2.2, executed the stored procedure sucessfully then opened the stored procedure to have a look inside. Here it is

CREATE PROCEDURE SPGET_DBSIZE_INFO ( OUT SQLSTATE_OUT CHAR(5),
OUT SQLCODE_OUT INTEGER )
DYNAMIC RESULT SETS 1

---

• • SQL Stored Procedure

-- SQLSTATE_OUT
-- SQLCODE_OUT

---

P1: BEGIN
-- Declare variables
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;

-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT *
FROM SYSTOOLS.STMG_DBSIZE_INFO;

-- Declare handler
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
FROM SYSIBM.SYSDUMMY1;

-- Cursor left open for client application
OPEN cursor1;
SET SQLSTATE_OUT = SQLSTATE;
SET SQLCODE_OUT = SQLCODE;
END P1

Now i created this stored procedure using the wizard for creating stored procedures in Data Studio.

Just for fun i decided to create another stored procedure, exactly the same as the first called SPGET_DBSIZE_INFO1, using the wizard, this time i only selected SQL Exception handling. It generated the following

CREATE PROCEDURE SPGET_DBSIZE_INFO1 ( )
DYNAMIC RESULT SETS 1

---

• • SQL Stored Procedure

    ---

P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT *
FROM SYSTOOLS.STMG_DBSIZE_INFO;

-- Cursor left open for client application
OPEN cursor1;
END P1

I went back to control centre and executed this new stored procedure SPGET_DBSIZE_INFO1 and IT WORKED...you can imagine my excitement, which quickly turned to puzzelment.
Why does SPGET_DBSIZE_INFO1 work and SPGET_DBSIZE_INFO not work in Control Centre, while both work in Data Studio. What specifcally do i need to type in Control Center to get my first stored procedure SPGET_DBSIZE_INFO to work

I've tried to give as much information as i know. If you have any answers please help

Simply put SDas, your a GENIUS...I did spget_dbsize_info(?,?) from in control centre and it worked, I proceded to try executing the same call from my .NET application and IT WORKED. Thank you very much

Declare handler
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
FROM SYSIBM.SYSDUMMY1;

I have NO CLUE what the above lines do. I just picked up DB2 the other day coz I'm trying to do a proof of concept that DB2 is worth a look....but that code is generated using Data Studio 2.2 when creating a stored procedure using wizard.

Thank you for your help.