Creating Java routines from the command line

Procedures and functions that reference a Java™ class are created in a similar way to external routines with other implementations. Steps for creating a Java routine include the formulation of the CREATE statement, coding, compilation (translation), and deployment of the Java class to the database server.

1. Code the routine logic in Java.
   • A routine parameter signature must be implemented using one of the supported parameter styles. It is strongly recommended that parameter style JAVA be used for all Java routines. For more on parameter signatures and parameter implementations see:
     • Parameters in Java routines
     • Parameter style JAVA procedures
     • Parameter style JAVA Java functions and methods
   • Declare variables in the same manner as is done for Java database applications. Be careful to correctly use data types that map to SQL data types.
     For more on data type mapping between SQL and Java data types, see the following topics:

     • Data types that map to database data types in Java applications
   • Include routine logic. Routine logic can consist of any code supported in the Java programming language. It can also include the execution of SQL statements in the same manner as is done in Java database applications.
     For more on executing SQL statements in Java code see:

     • JDBC interfaces for executing SQL
     • SQL statement execution in SQLJ applications
   • If the routine is a procedure and you might want to return a result set to the caller of the routine, you do not require any parameters for the result set. For more on returning result sets from Java routines:
     • Returning result sets from JDBC procedures
     • Returning result sets from SQLJ procedures
   • Set a routine return value at the end of the routine.
2. Build your code to produce a Java class file or JAR file containing a collection of Java class files.
   For information on how to build Java routine code, see:

   • Building JDBC routines
   • Building SQL routines
3. Copy the class file to the database server or install the JAR file to the database server.
   For information on how to do this, see:

   • Deploying Java routine class files to database servers
   • JAR file administration on the database server

   It is recommended that you store class files associated with database routines in the function directory. To find out more about the function directory, see information related to the EXTERNAL clause in one of the following statements: CREATE PROCEDURE or CREATE FUNCTION.

   You can copy the library to another directory on the server if you wish, but to successfully invoke the routine you must note the fully qualified path name of your library as you will require it for the next step.

4. Execute either dynamically or statically the appropriate CREATE statement for the routine type: CREATE PROCEDURE or CREATE FUNCTION.
   • Specify the LANGUAGE clause with: JAVA
   • Specify the PARAMETER STYLE clause with the name of the supported parameter style that was implemented in the routine code. It is strongly recommended that PARAMETER STYLE JAVA be used unless the features you require are only supported when PARAMETER STYLE DB2GENERAL is used.
   • Specify the EXTERNAL clause with the name of the JAR file or Java class to be associated with the routine using one of the following values:
     • the fully qualified path name of the Java class file
     • the relative path name of the routine Java class file relative to the function directory.
     • the JAR file ID of the JAR file on the database server that contains the Java class

     By default the database manager searches for the library in the function directory unless a JAR file ID and class, fully qualified path name, or relative path name for it is specified in the EXTERNAL clause.

   • Specify DYNAMIC RESULT SETS with a numeric value if your routine is a procedure and it will return one or more result sets to the caller.
   • Specify any other non-default clause values in the CREATE statement to be used to characterize the routine.