db2 stored procedure incompatible arguments error

Question

I am new to stored procedures. Basically, I am trying to create a stored procedure and I am trying to call it from db2 command line interface to insert a record into a table.

The stored procedure is created successfully in my database. But, when I pass the arguments to call the stored procedure it is throwing the below error.

SQL0440N No authorized routine named "COM_PROC_INSERT_FAILURE_XMESSAGE_MAIN" of type "PROCEDURE" having compatible arguments was found. SQLSTATE=42884

I have created XMESSAGE table and the stored procedure is created based on the same data types from the XMESSAGE table.

The stored procedure which I have created is shown here:

CREATE PROCEDURE com_proc_insert_failure_XMESSAGE_main(
    OUT errmsg CHAR(5),
    IN XMESSAGE_ID BIGINT,
    IN XMESSAGETYPE_ID CHAR(32),
    IN ISPROCESSED INTEGER,
    IN FOREIGN_ID BIGINT,
    IN FIELD1 VARCHAR(254),
    IN FIELD2 VARCHAR(254),
    IN FIELD3 VARCHAR(4000),
    IN ORDER_ID VARCHAR(30)
)
LANGUAGE SQL
BEGIN

DECLARE SQLSTATE CHAR(5);

INSERT INTO XMESSAGE (XMESSAGE_ID, XMESSAGETYPE_ID, ISPROCESSED, FOREIGN_ID, FIELD1, FIELD2, FIELD3, ORDER_ID ) 
VALUES (XMESSAGE_ID, XMESSAGETYPE_ID, ISPROCESSED, FOREIGN_ID, FIELD1, FIELD2, FIELD3, ORDER_ID );

SET errmsg = SQLSTATE;

END@

I am calling the stored procedure from db2 command line interface as below

db2 "call com_proc_insert_failure_XMESSAGE_main(?, 123456789,'TEST_MESSAGE',1,1,'TESTMESSAGE','111','test','12345')"

I believe I am passing the parameters correctly. Can someone please let me know where the issue is happening? Thanks in advance!

mao mao · Accepted Answer · 2017-07-12T18:24:51

Check if the schema used at the time of the 'create procedure' matches the default schema at the time of the call. The syscat.procedures entry in column procschema should be the implicit auth-id at the time of the call, otherwise qualify the sproc name with that procschema when calling.

db2 stored procedure incompatible arguments error

1 Answers