title: DB2 java Stored Procedure call return error SQLCODE=-440, SQLSTATE=42884
body: <p>I am doing a simple stored procedure call to DB2. While it calls the stored procedure, it always returns this error: </p>
  
  <pre><code>DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=MEDIAN_RESULT_SET;PROCEDURE, DRIVER=3.66.46
  </code></pre>
  
  <p>========== Java code:</p>
  
  <pre><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-&gt;callproc after execute " + sql);
  stmt1.close();
  
  conn.close();
  </code></pre>
  
  <p>==============
  The db2 clp command line worked:</p>
  
  <pre><code>c:SP&gt;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
  </code></pre>
  
  <p>================
  The stored procedure definition:</p>
  
  <pre><code>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) &gt; 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) &lt; 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 &lt; (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 @
  </code></pre>