External stored procedures for RDS for Db2

You can create external routines and register them with your RDS for Db2 databases as external stored procedures. Currently, RDS for Db2 only supports Java-based routines for external stored procedures.

Java-based external stored procedures

Java-based external stored procedures are external Java routines that you register with your RDS for Db2 database as external stored procedures.

Limitations for Java-based external stored procedures

Before you develop your external routine, consider the following limitations and restrictions.

To create your external routine, make sure to use the Java Development Kit (JDK) provided by Db2. For more information, see Java software support for Db2 database products.

Your Java program can create files only in the /tmp directory, and Amazon RDS doesn't support enabling executable or Set User ID (SUID) permissions on these files. Your Java program also can't use socket system calls or the following system calls:

For additional restrictions on external routines for Db2, see Restrictions on external routines in the IBM Db2 documentation.

Configuring Java-based external stored procedures

To configure an external stored procedure, create a .jar file with your external routine, install it on your RDS for Db2 database, and then register it as an external stored procedure.

Step 1: Enable external stored procedures

To enable external stored procedures, in a custom parameter group associated with your DB instance, set the parameter db2_alternate_authz_behaviour to one of the following values:

For more information about this setting, see GRANT (database authorities) statement in the IBM Db2 documentation.

You can create and modify a custom parameter group by using the AWS Management Console, the AWS CLI, or the Amazon RDS API.

Step 2: Install the .jar file with your external routine

After you create your Java routine, create the .jar file and then run db2 "call sqlj.install_jar('file:file_path',jar_ID)" to install it on your RDS for Db2 database.

The following example shows you how to create a Java routine and install it on an RDS for Db2 database. The example includes sample code for a simple routine that you can use to test the process. This example makes the following assumptions:

If you'd like to try out the process with the following sample code, copy it and then save it to a file named MYJAVASP.java.

import java.sql.*;
public class MYJAVASP
{
public static void my_JAVASP (String inparam) throws SQLException, Exception
{
try
{
// Obtain the calling context's connection details.
Connection myConn = DriverManager.getConnection("jdbc:default:connection");
String myQuery = "INSERT INTO TEST.TEST_TABLE VALUES (?, CURRENT DATE)";
PreparedStatement myStmt = myConn.prepareStatement(myQuery);
myStmt.setString(1, inparam);
myStmt.executeUpdate();
}
catch (SQLException sql_ex)
{
throw sql_ex;
}
catch (Exception ex)
{
throw ex;
}
}

The following command compiles the Java routine.

~/sqllib/java/jdk64/bin/javac MYJAVASP.java

The following command creates the .jar file.

~/sqllib/java/jdk64/bin/jar cvf MYJAVASP.jar MYJAVASP.class

The following commands connect to the database named MY_DB2_DATABASE and install the .jar file.

db2 "connect to MY_DB2_DATABASE user master_username using master_password" 

db2 "call sqlj.install_jar('file:/tmp/MYJAVASP.jar','MYJAVASP')"
db2 "call sqlj.refresh_classes()"

Step 3: Register the external stored procedure

After you install the .jar file on your RDS for Db2 database, register it as a stored procedure by running the db2 CREATE PROCEDURE or db2 REPLACE PROCEDURE command.

The following example shows you how to connect to the database and register the Java routine created in the previous step as a stored procedure.

db2 "connect to MY_DB2_DATABASE user master_username using master_password"

create procedure TESTSP.MYJAVASP (in input char(6))
specific myjavasp
dynamic result sets 0
deterministic
language java
parameter style java
no dbinfo
fenced
threadsafe
modifies sql data
program type sub
external name 'MYJAVASP!my_JAVASP';

Step 4: Validate the external stored procedure

Use the following steps to test the sample external stored procedure that was registered in the previous step.