4

I am doing a simple stored procedure call to DB2. While it calls the stored procedure, it always returns this error:

DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=MEDIAN_RESULT_SET;PROCEDURE, DRIVER=3.66.46

========== Java code:

String JDBC_DRIVER = "com.ibm.db2.jcc.DB2Driver";
// STEP 2: Register JDBC driver
Class.forName(JDBC_DRIVER);

// STEP 3: Open a connection
System.out.println("Connecting to database..."); 
conn = DriverManager.getConnection(DB_URL, USER, PASS);

// to execute the stored procedure.
System.out.println("CALL median_result_set(?)");
String sql = "CALL median_result_set(?)";
CallableStatement stmt1 = conn.prepareCall(sql);
stmt1.registerOutParameter(1, Types.DOUBLE);

stmt1.execute();
System.out.println("jdbcadapter->callproc after execute " + sql);
stmt1.close();

conn.close();

============== The db2 clp command line worked:

c:SP>db2 call median_result_set(?)
 Value of output parameters 
 --------------------------
 Parameter Name  : MEDIANSALARY 
 Parameter Value : +7.68582000000000E+004

Result set 1
--------------
NAME      JOB   SALARY
--------- ----- ---------
Marenghi  Mgr    77506.75
O'Brien   Sales  78006.00

================ The stored procedure definition:

CREATE PROCEDURE median_result_set
-- Declare medianSalary as OUT so it can be used to return values
(OUT medianSalary DOUBLE)
RESULT SETS 2
LANGUAGE SQL
BEGIN

   DECLARE v_numRecords INT DEFAULT 1;
   DECLARE v_counter INT DEFAULT 0;

   DECLARE c1 CURSOR FOR
      SELECT salary FROM staff
       ORDER BY CAST(salary AS DOUBLE);

  -- use WITH RETURN in DECLARE CURSOR to return a result set
  DECLARE c2 CURSOR WITH RETURN FOR
   SELECT name, job, salary
   FROM staff 
   WHERE CAST(salary AS DOUBLE) > medianSalary
   ORDER BY salary;

  -- use WITH RETURN in DECLARE CURSOR to return another result set
 DECLARE c3 CURSOR WITH RETURN FOR
    SELECT name, job, salary
    FROM staff
    WHERE CAST(salary AS DOUBLE) < medianSalary
    ORDER BY SALARY DESC;

 DECLARE CONTINUE HANDLER FOR NOT FOUND
   SET medianSalary = 6666; 

 -- initialize OUT parameter
 SET medianSalary = 0;

 SELECT COUNT(*) INTO v_numRecords FROM STAFF;

 OPEN c1;

   WHILE v_counter < (v_numRecords / 2 + 1) DO
     FETCH c1 INTO medianSalary;
     SET v_counter = v_counter + 1;
  END WHILE;
  CLOSE c1;

  -- return 1st result set, do not CLOSE cursor
  OPEN c2;

  -- return 2nd result set, do not CLOSE cursor
  OPEN c3;
END @
4
  • Thanks Bryan, Any idea about the cause? Commented Aug 21, 2013 at 21:32
  • 2
    Do you connect with the same user ID in the CLP and your Java application? What does this return: select routineschema from syscat.routines where routinename = 'MEDIAN_RESULT_SET'? Is this what you expect? Commented Aug 22, 2013 at 1:12
  • That is a good command, and it worked and showed me the schema name. I hope I knew this command at that time. Commented Aug 22, 2013 at 17:40
  • @huican Hey huican. Did you figure out how to solve this issue? When I ran mustaccio's command, it returned my schema name instead of the user's name. Commented Nov 18, 2016 at 19:07

3 Answers 3

10

Basically "SQLCODE=-440, SQLSTATE=42884" means that stored procedure can not be found.

I saw a very common cause is the argument doesn't match.

For my case, I noticed that in java code, I have to put the schema name in front of the stored procedure name, e.g, instead of median_result_set(?), I should do SCHEMANAME.median_result_set(?)

The SCHEMANAME for this SP can be found with some DB admin tools.

The reason why I don't need to specify the schema name from the command line: it seems that when I call SP from CLP command line with the same user when I created that SP, there is no need to the schema name (because internally they match up). Of course, it is always right if you specify the schema at the command line. I observed DB2 internally uses user name as schema name. E.g, if "ADMINISTRATOR" created a SP, the string "ADMINISTRATOR" is its schema name, as long as I see on Windows.

Sign up to request clarification or add additional context in comments.

1 Comment

I recently received an error when trying to use the ASCII function in a UDF; using SYSFUN.ASCII resolved this, thanks!
0

You cannot find it because it actually doesn't exist... Try to call MEDIAN_RESULT_SET instead or to create the procedure as "median_result_set"...

Just in case someone is having the same issue ;)

Comments

-3

The error # means 42884 No routine was found with the specified name and compatible arguments. Check on url: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.messages.doc%2Fdoc%2Frdb2stt.html

and search for 42884 error code. Hope you can solve your query by yourself.

1 Comment

The question is that why the route was not found. 1) I have the standalone working. 2) In java code, I connected to a good DB, and 3) I used the same name as I used in standalone. I am using DB2 Express-C, I am wondering whether it is not enabled there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.