?
Solved

Db2 stored procedure problem (Pl help)

Posted on 2004-11-12
5
Medium Priority
?
4,564 Views
Last Modified: 2012-08-14
When I run the first stored procedure I'm getting the error

ERROR [42884] [IBM][CLI Driver][DB2/NT] SQL0440N No authorized routine named "REPORTSAMPLE" of type "PROCEDURE" having compatible arguments was found. SQLSTATE=42884

<code>

SET CURRENT SCHEMA = IS @
SET PATH = IS @


CREATE PROCEDURE RG

(
  IN ReportID INT,
  IN StartDate TIMESTAMP,
  IN EndDate TIMESTAMP
)
      LANGUAGE SQL
      DYNAMIC RESULT SETS 1
BEGIN

DECLARE Statement VARCHAR(1000);
DECLARE      SpName VARCHAR(50);

SELECT "SQLStoredProcedure" INTO SpName
  FROM "Report"
 WHERE "ReportID" = ReportID;
 
SET Statement = 'CALL ' || SpName || '(?,?)';            
PREPARE sl FROM Statement;
EXECUTE sl USING StartDate, EndDate;

END @

<code>
SET CURRENT SCHEMA = IS @
SET PATH = IS @

CREATE PROCEDURE ReportSample

(
  IN StartDate TIMESTAMP,
  IN EndDate TIMESTAMP
)
      LANGUAGE SQL
      DYNAMIC RESULT SETS 1
BEGIN

DECLARE REPORT_SQL CURSOR WITH RETURN FOR
  SELECT "SiteProfile"."SiteName",
             "SiteProfile"."ProfileName",
             "SiteProfile"."ApplicationName"
      FROM "SiteProfile"
   WHERE "SiteProfile"."SiteProfileID" <> 0;


OPEN REPORT_SQL;

END @


</code>

Thanks
j



0
Comment
Question by:joejosephk
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 12567157
if you
call reportsampl  (current timestamp,current timestamp + 1 hour)

does it work?

are you compiling both procedures together or separatly...?

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 12567176
have you tried an execute immediate instead of the prepare & execute?
(no real reason why it shouldn't work with the 2 steps.)

0
 

Author Comment

by:joejosephk
ID: 12567254
Some one else develped this stored procedure.I'm debugging it.The procedures are already compiled.No error in compilation.

Report sample is working when it is executed alone.
Thanks
J
0
 
LVL 13

Accepted Solution

by:
ghp7000 earned 400 total points
ID: 12569105
1. SELECT "SELECT "SQLStoredProcedure" INTO SpName " INTO SpName-
does SQLStoredProcedure contain the schema name?

2. The procedure might have compiled, but is it valid?
select valid from syscat.procedures where procname in ('RG,'ReportSample')

3. Run the failing procedure from command line and obtain the reason code for sql0444n

4. Do you have microsoft compiler installed? If so, what version? What platform is the db running on?

5. try changing the statement in rg stored proc
DYNAMIC RESULT SETS 1 to
DYNAMIC RESULT SETS 1 READS SQL DATA

0
 
LVL 18

Expert Comment

by:BigSchmuh
ID: 12592282
2 points:
-Your procedure may be registered on another schema than your login one.
-Your 2 arguments MUST have the TimeStamp datatype

1/ Explicit naming
Using ghp7000 query
    SELECT * FROM syscat.procedures where procname in ('RG,'ReportSample')
Try to call your stored procedure using an explicit name containing its owner as a prefix:
    LoginUsedToCompileAndRegisterTheSP.ReportSample(...)

2/ TimeStamp datatype
If you are not sure of your datatype, use one of the TIMESTAMP functions to enclose your parameters.

Hope this helps.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Screencast - Getting to Know the Pipeline
Suggested Courses

807 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