Link to home
Start Free TrialLog in
Avatar of bees
bees

asked on

Calling procedures within another procedure of same schema where the schema is different from the connected user

Hi,

My database is DB2 UDB 8.1 Fix Pack 6a. I need to call procedures within another procedure. The number of called procedures are high as the database intensive processing is high and on huge volume.

All the procedures are in the same schema APPL01. The connection user is APPLUSER. The called procedure is inoked as "call < called procedure>;". On compiling the calling procedure I get errors like "Cannot access APPL01.<called procedure>". The compilation error is solved when the call is changed to "call APPL01.< called procedure>;".

The Schema name cannot be hard coded in Calling procedure as the Schema name can change on installations.

Please help. Is there any way to sort this problem?

Thanks in advance,
Bees
Avatar of ghp7000
ghp7000

grant execute on procedure to user or group
Avatar of bees

ASKER

The user APPLUSER has all the grants on the Called and Calling procedures as the procedures were created using Development Center with SQL Schema for Connection set as APPL01.
Avatar of bees

ASKER

One more point to add - the connection user is always APPLUSER for the Development Center project
still the same answer, regardless of which user you used from Development Center to create the SP, when it comes to deploy the SP to the database, you will have to grant execute of SP to a user or group. If you dont specify the schema name when you create the stored procedure , the schema id of the development center is used, so either
1) find out first what the schema name of the user will be in production and use that schema name in the stored procedure or (this means create the user first, log on to your development db with that username, then create the SP)
2) when you deploy the SP, make sure to grant execute on it to whoever is supposed to have those privs, if it includes schema users other than the one you used to create the SP.


Avatar of bees

ASKER

Thank you for answering.

But I tried again using the following procedures - Procedure1 and Procedure2. Procedure 1 calls Procedure2.
Step 1) Procedure 2 was first created in scheme APPL01 using APPLUSER. (using Dev Center)
Step 2) The following grant statement was run in CommandCenter
GRANT EXECUTE ON PROCEDURE PROCEDURE2 TO USER APPLUSER;
Step 3) Invoked Build on Procedure1 again in scheme APPL01 using APPLUSER.(using Dev Center)

Build fails for Procedure1. The error message is
[IBM][CLI Driver][DB2/NT] SQL0440N  No authorized routine named "PROCEDURE2" of type "PROCEDURE" having compatible arguments was found.  LINE NUMBER=6.  SQLSTATE=42884

if procedure 1 calls procedure 2, why are you building proc 2 first? Seems illogical. In any case, drop both procedures, make sure they are invalid by checking the valid column of the syscat.procedures table.
Build your procedures, since I dont know the code, I would assume that you dont have any set statements in them.
Since you are buidling both procedures with same schema name/user name, you dont have to grant anybody anything. It's only if you want other users to call and execute the procedures do  you need to grant them that privlidge.
Avatar of bees

ASKER

Code for Procedure 1:
===============
CREATE PROCEDURE APPL01.PROCEDURE1 (  )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
     call PROCEDURE2;
END P1                              

 Code for PROCEDURE2:
================
CREATE PROCEDURE APPL01.PROCEDURE2 (  )
    DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
        SELECT PROCSCHEMA, PROCNAME FROM SYSCAT.PROCEDURES;

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

 Build message for Procedure2:
====================
APPL01.PROCEDURE2 - Build started.
DROP SPECIFIC PROCEDURE APPL01.SQL050301202838840
APPL01.PROCEDURE2 - Drop stored procedure completed.
APPL01.PROCEDURE2 - Create stored procedure completed.
APPL01.PROCEDURE2 - Build successful.

 Build message for Procedure1:
=====================
APPL01.PROCEDURE1 - Build started.
DROP SPECIFIC PROCEDURE APPL01.SQL050301172048737
APPL01.PROCEDURE1 - Drop stored procedure completed.
Create stored procedure returns -440.
APPL01.PROCEDURE1: 6: [IBM][CLI Driver][DB2/NT] SQL0440N  No authorized routine named "PROCEDURE2" of type "PROCEDURE" having compatible arguments was found.  LINE NUMBER=6.  SQLSTATE=42884

APPL01.PROCEDURE1 - Build failed.
APPL01.PROCEDURE1 - Roll back completed successfully.

ASKER CERTIFIED SOLUTION
Avatar of ghp7000
ghp7000

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Please explain this C grade?

Thanks