Link to home
Start Free TrialLog in
Avatar of joejosephk
joejosephk

asked on

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



Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

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

does it work?

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

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

Avatar of joejosephk
joejosephk

ASKER

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
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
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.