SQL0440

A SQL0444 error indicates that the procedure with compatible arguments could not be found. This error does not indicate a serious problem, and it usually occurs at run time. There are two common causes:

• CREATE PROCEDURE statement was not issued to define a procedure at the current server. Therefore DB2 is unable to locate the procedure.

  Solution: Issue the missing CREATE PROCEDURE statement.

• The procedure is invoked with an incorrect number of agruments. That is, you may have provided an incorrect number of input or output parameters, or you may have provided an improper data type in one of the parameters. Therefore, DB2 could not find a procedure with the matching arguments to invoke.

  Solution: Check the CREATE PROCEDURE statement and make sure you are passing the correct number of input and output parameters with proper data types.

Here is an example of the second situation where an incorrect number of parameters are passed to the procedure during runtime. This example uses the sample stored procedures OUT_LANGUAGE and ALL_DATA_TYPES that are shipped with the DB2 product. You may locate the source code in /instance_home/sqllib/samples/java/jdbc/SpServer.java, and the corresponding CREATE PROCEDURE statements in /instance_home/sqllib/samples/java/jdbc/SpCreate.db2 on UNIX platforms, where instance_home is your instance home directory. On Windows, the default location of the files are in C:\Program Files\IBM\SQLLIB\samples\java\jdbc\SpServer.java and C:\Program Files\IBM\SQLLIB\samples\java\jdbc\SpCreate.db2.
Listing 41. SQL0440 example: Calling the OUT_LANGUAGE() and ALL_DATA_TYPEs procedure on Windows

D:\>db2 call out_language()
SQL0440N  No authorized routine named "OUT_LANGUAGE" of type "PROCEDURE"
having compatible arguments was found.  SQLSTATE=42884

D:\>db2 call all_data_types (32000, 2147483000, 21478483000, 100000, 2500000)
SQL0440N  No authorized routine named "ALL_DATA_TYPES" of type "PROCEDURE"
having compatible arguments was found.  SQLSTATE=42884

Let's take a look at why SQL0440 is returned. First look at the CREATE PROCEDURE statement to confirm what parameters each of the stored procedures expects.
Listing 42. SQL0440 example: CREATE PROCEDURE statements for OUT_LANGUAGE and ALL_DATA_TYPES procedures

CREATE PROCEDURE OUT_LANGUAGE (OUT LANGUAGE CHAR(8))
SPECIFIC JDBC_OUT_LANGUAGE
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'SpServer.outLanguage'

CREATE PROCEDURE ALL_DATA_TYPES (
  INOUT small SMALLINT, 
  INOUT intIn INTEGER,
  INOUT bigIn BIGINT,
  INOUT realIn REAL, 
  INOUT doubleIn DOUBLE,
  OUT charOut CHAR(1),
  OUT charsOut CHAR(15),
  OUT varcharOut VARCHAR(12),
  OUT dateOut DATE,
  OUT timeOut TIME)
SPECIFIC JDBC_ALL_DAT_TYPES
DYNAMIC RESULT SETS 0
NOT DETERMINISTIC
LANGUAGE JAVA 
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'SpServer.allDataTypes'

From the CREATE PROCEDURE statements, you can see that:

1. The OUT_LANGUAGE procedure expects one output parameter of type char.
2. The ALL_DATA_TYPES procedure expects 5 input/output parameters, and 5 output parameters, where each of the argument types expected is shown above.

For input parameters, provide a proper value with the correct data type. For output parameters, use a '?' for each output parameter. For input/output parameters, since they are used for both input and output purposes, provide the proper value as if you are just passing a value for an input parameter. Therefore, you should call each of the procedures like below:
Listing 43. SQL0440 example: Calling the OUT_LANGUAGE and ALL_DATA_TYPES procedures properly

D:\>db2 call out_language(?)

  Value of output parameters
  --------------------------
  Parameter Name  : LANGUAGE
  Parameter Value : JAVA

  Return Status = 0

D:\>db2 call all_data_types (32000, 2147483000, 21478483000, 100000, 
2500000, ?, ?, ?, ?, ?)

  Value of output parameters
  --------------------------
  Parameter Name  : SMALL
  Parameter Value : 16000

  Parameter Name  : INTIN
  Parameter Value : 1073741500

  Parameter Name  : BIGIN
  Parameter Value : 10739241500

  Parameter Name  : REALIN
  Parameter Value : +5.00000E+004

  Parameter Name  : DOUBLEIN
  Parameter Value : +1.25000000000000E+006

  Parameter Name  : CHAROUT
  Parameter Value : S

  Parameter Name  : CHARSOUT
  Parameter Value : SCOUTTEN

  Parameter Name  : VARCHAROUT
  Parameter Value : MARILYN

  Parameter Name  : DATEOUT
  Parameter Value : 09/27/2005

  Parameter Name  : TIMEOUT
  Parameter Value : 11:30:16

  Return Status = 0

For more information about how to call procedures from the Command Line Processor (CLP), see this URL: http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/t0007055.htm.