Db2 stored procedure problem (Pl help)

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



joejosephkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
if you
call reportsampl  (current timestamp,current timestamp + 1 hour)

does it work?

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

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

0
joejosephkAuthor Commented:
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
ghp7000Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BigSchmuhCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.