title: DB2 Stored Procedure Returning SQLCODE=-440, SQLSTATE=42884
body: <p>I am currently using RazorSQL to test queries with IBM DB2. Right now, I am stuck on an issue with creating procedures</p>
  
  <p>I have the following procedure. I create this procedure as an admin user called <code>db2inst1</code>:</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE CANCEL_ACTIVITY (IN application_handle INTEGER)
    LANGUAGE SQL
  BEGIN
    DECLARE UOW_ID INTEGER;
    DECLARE ACTIVITY_ID  INTEGER;
  
    FOR v AS cur1 CURSOR FOR
      SELECT UOW_ID, ACTIVITY_ID FROM TABLE(SYSPROC.MON_GET_ACTIVITY(application_handle, -1))
    DO
      CALL WLM_CANCEL_ACTIVITY(application_handle, v.uow_id, v.activity_id);
    END FOR ;
  END
  </code></pre>
  
  <p>Now, I log onto my regular user called <code>applicationtest</code> which is NOT an admin. If I run the following:</p>
  
  <pre><code>CALL CANCEL_ACTIVITY(12345)
  </code></pre>
  
  <p>I get the following error:</p>
  
  <pre><code>DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=CANCEL_ACTIVITY;PROCEDURE, DRIVER=4.16.53
  </code></pre>
  
  <p>According to <a href="http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/codes/src/tpc/n440.html" rel="nofollow noreferrer">IBM</a>, it's caused by:</p>
  
  <blockquote>
    <p>NO AUTHORIZED routine-type BY THE NAME routine-name HAVING COMPATIBLE ARGUMENTS WAS FOUND</p>
  </blockquote>
  
  <p>Looking at this <a href="https://stackoverflow.com/questions/18367803/db2-java-stored-procedure-call-return-error-sqlcode-440-sqlstate-42884">question</a>, I ran this debug code to check if the users are the same, and it does not appear to be the same, because the results are:</p>
  
  <pre><code>  | ROUTINESCHEMA
  1 | db2inst1
  2 | DB2INST1
  </code></pre>
  
  <p>Logically, <code>ROUTINESCHEMA</code> should have my regular user <code>applicationtest</code> in it, right?</p>
  
  <p>I even ran the following:</p>
  
  <pre><code>GRANT EXECUTE ON PROCEDURE CANCEL_ACTIVITY TO PUBLIC;
  </code></pre>
  
  <p>To see if it would help, but still, same error.</p>
  
  <p>I also tried to create the procedure using <code>applicationtest</code>, but as expected, I don't have the privileges required to do so. </p>
  
  <p>EDIT:</p>
  
  <p>I just specified DB2INST1.CANCEL_PROCEDURE(12345), and now I am getting the following error:</p>
  
  <pre><code>2016-11-18 11:27:34.983 -0800 [ERROR|01c56|] :: Java::ComIbmDb2JccAm::SqlSyntaxErrorException : DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=APPLICATIONTEST;EXECUTE;DB2INST1.CANCEL_ACTIVITY, DRIVER=4.16.53
  </code></pre>
  
  <p>I guess that user <code>applicationtest</code> doesn’t have permission to execute that procedure?</p>
  
  <p>I have two questions:</p>
  
  <ol>
  <li><p>Is it possible to not have to call DB2INST1 before calling the procedure? So I can just call it like this <code>CALL CANCEL_PROCEDURE(..)</code> instead of <code>CALL DB2INST1.CANCEL_ACTIVITY</code>. I feel like this would remove a lot of the ambiguity..</p></li>
  <li><p>How do I grant <code>applicationtest</code> the necessary privilege to call the procedure? </p></li>
  </ol>