DB2 – Format of EXTERNAL NAME Argument in Stored Procedures

db2db2-10.5javastored-procedures

When a Java class cl with a method mth resides in a package pck, what should I put into the EXTERNAL NAME clause of CREATE PROCEDURE?

I tried

CREATE PROCEDURE mth (arg1 INTEGER) LANGUAGE JAVA EXTERNAL NAME 'myjar:pck/cl.mth'
CREATE PROCEDURE mth (arg1 INTEGER) LANGUAGE JAVA EXTERNAL NAME 'myjar:pck.cl.mth'
CREATE PROCEDURE mth (arg1 INTEGER) LANGUAGE JAVA EXTERNAL NAME 'myjar:cl.mth'

but either way, I get The user defined function or procedure "mth" was unable to map to a single Java method.. SQLCODE=-20204, SQLSTATE=46008, DRIVER=3.68.61

On Java side, the method is declared as follows:

public void send_mail( // with or w/o static, with or w/o synchronized
    final String P_TO,
    final String P_CC,
    final String P_BCC,
    final String P_FROM,
    final String P_SUBJECT,
    final String P_TEXT_MSG,
    final String P_HTML_MSG,
    final String P_ATTACH_NAME,
    final String P_ATTACH_MIME,
    final byte[] P_ATTACH_BLOB,
    final String P_SMTP_HOST,
    final int P_SMTP_PORT,
    final String P_USER_NAME,
    final String P_PASSWORD

On the DB2 side, the procedure is being attempted to create as follows:

CREATE PROCEDURE send_mail(
  IN P_TO          VARCHAR(4096),
  IN P_CC          VARCHAR(4096) DEFAULT NULL,
  IN P_BCC         VARCHAR(4096) DEFAULT NULL,
  IN P_FROM        VARCHAR(4096),
  IN P_SUBJECT     VARCHAR(32672),
  IN P_TEXT_MSG    VARCHAR(32672) DEFAULT NULL,
  IN P_HTML_MSG    VARCHAR(32672) DEFAULT NULL,
  IN P_ATTACH_NAME VARCHAR(255) DEFAULT NULL,
  IN P_ATTACH_MIME VARCHAR(100) DEFAULT NULL,
  IN P_ATTACH_BLOB BLOB DEFAULT NULL,
  IN P_SMTP_HOST   VARCHAR(255),
  IN P_SMTP_PORT   INTEGER DEFAULT 25,
  IN P_USER_NAME   VARCHAR(255) DEFAULT NULL,
  IN P_PASSWORD    VARCHAR(32672) DEFAULT NULL
)
LANGUAGE java
PARAMETER STYLE java
FENCED THREADSAFE
EXTERNAL NAME 'javadb2:db2.smtp.send_mail' -- with or w/o db2

I've checked and double-checked that every time I sqlj.db2_install_jar it appears under the function\username folder in the DB2 folder hierarchy.

Best Answer

The correct format is <JAR name>:<class name>.<method name>, <class name> is fully qualified, i.e. includes the package name. Since you are still getting this error, it's likely that your method signature does not comply with the convention you use. You don't specify the PARAMETER STYLE option, meaning DB2GENERAL is used by default, which is not recommended. Use PARAMETER STYLE JAVA and make sure the method signature is correct (public static void mth(int arg1) in your contrived example).

Note also that

For updating Java™ routines that are built into JAR files, you must issue a CALL SQLJ.REFRESH_CLASSES() statement to force the database manager to load the new classes. If you do not issue the CALL SQLJ.REFRESH_CLASSES() statement after you update Java routine classes, the database manager continues to use the previous versions of the classes. The database manager refreshes the classes when a COMMIT or ROLLBACK occurs. (source)

Related Question