?
Solved

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

Posted on 2005-03-01
10
Medium Priority
?
555 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:bees
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
10 Comments
 
LVL 13

Expert Comment

by:ghp7000
ID: 13428923
grant execute on procedure to user or group
0
 

Author Comment

by:bees
ID: 13428997
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.
0
 

Author Comment

by:bees
ID: 13429013
One more point to add - the connection user is always APPLUSER for the Development Center project
0
Major Serverless Shift

Comparison of major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform

 
LVL 13

Expert Comment

by:ghp7000
ID: 13429637
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.


0
 

Author Comment

by:bees
ID: 13429831
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

0
 
LVL 13

Expert Comment

by:ghp7000
ID: 13433546
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.
0
 

Author Comment

by:bees
ID: 13433863
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.

0
 
LVL 13

Accepted Solution

by:
ghp7000 earned 250 total points
ID: 13434977
0
 
LVL 20

Expert Comment

by:Venabili
ID: 14188144
Please explain this C grade?

Thanks
0

Featured Post

PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question