troubleshooting Question

DB2 Call procedure error "no authorized routine.... compatible arguments found"

Avatar of JDCam
JDCam asked on
DB2SQL
5 Comments1 Solution8693 ViewsLast Modified:
Experts,
I have a small trigger that sets some values and calls a procedure.
I can create the procedure with no errors, but I create the procedure  I get the following error:

[IBM][CLI Driver][DB2/NT64] SQL0440N  No authorized routine named "JDS_INSERT_INTO_ODRSTAT" of type "PROCEDURE" having compatible arguments was found.  LINE NUMBER=10.  SQLSTATE=42884

I don't understand why I get this error. Please help.


CREATE TRIGGER JDS_APPT_STATUS_INSERT
  AFTER UPDATE OF DELIVERY_APPT_MADE, DELIVER_BY ON TLORDER
  REFERENCING NEW AS N OLD AS O
  FOR EACH ROW
  MODE DB2SQL
BEGIN ATOMIC
  DECLARE vDATE TIMESTAMP;

  IF ((O.DELIVERY_APPT_MADE <> 'True ') AND (N.DELIVERY_APPT_MADE = 'True ')) THEN
    SET vDATE = N.DELIVER_BY;
    CALL JDS_INSERT_INTO_ODRSTAT (N.DETAIL_LINE_ID, N.DELIVER_BY, 'APTBOOK', 'Delivery Appt Made ' ,USER ,CURRENT TIMESTAMP);
  END IF;

  IF ((O.DELIVERY_APPT_MADE = 'True ') AND (N.DELIVERY_APPT_MADE = 'True ')) AND
     (O.DELIVER_BY <> N.DELIVER_BY)  THEN
     SET vDATE = N.DELIVER_BY;
     CALL JDS_INSERT_INTO_ODRSTAT (N.DETAIL_LINE_ID, N.DELIVER_BY, 'APTCHNG', 'Delivery Appt Changed ' ,USER ,CURRENT TIMESTAMP);
  END IF;
  END@


CREATE PROCEDURE JDS_INSERT_INTO_ODRSTAT(
  IN iORDER_ID INTEGER,
  IN iCHANGED TIMESTAMP,
  IN iSTATUS_CODE VARCHAR(10),
  IN iSTAT_COMMENT VARCHAR(80),
  IN iUPDATED_BY VARCHAR(128),
  IN iINS_DATE TIMESTAMP,
  OUT oID INTEGER
)
LANGUAGE SQL
BEGIN
      DECLARE vGEN_ODRSTAT_ID VARCHAR(128) DEFAULT 'GEN_ODRSTAT_ID';
  IF (iINS_DATE IS NULL) THEN
    SET iINS_DATE = CURRENT TIMESTAMP;
  END IF;
  DELETE FROM ODRSTAT
    WHERE
      ORDER_ID = iORDER_ID AND
      CHANGED = iCHANGED AND
      STATUS_CODE = iSTATUS_CODE;
  CALL GEN_ID(vGEN_ODRSTAT_ID, oID);
  INSERT INTO ODRSTAT (
    ID, ORDER_ID, CHANGED, STATUS_CODE, STAT_COMMENT, UPDATED_BY, INS_DATE
    )
      values (
      oID, iORDER_ID, iCHANGED, iSTATUS_CODE, iSTAT_COMMENT, iUPDATED_BY, iINS_DATE
      );
END@
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros