Debugging Java UDFs Using Graphical System Debugger Using Run SQLScripts

To create a Java UDF that is enabled for debug and then use the graphical system debugger. 


Step 1: Ensure that IBM i ACS Run SQL Script is at a minimum level V6R1M0. 



Step 2: Create Java source code for a simple UDF with an infinite loop. You must set x = 1 at some point to escape the loop. If you want to debug the Java UDF program, use the following:

// You have to Compile the code using the following commands:
Note: Square.java is located in folder /home/Java
//
// 1. Start QShell (QSH)
// 2. Use the javac command as follows (the Java file name is case sensitive!)(-g is for debug):
// javac -g -d /qibm/userdata/os400/sqllib/function/ /home/Java/javaUDFs.java
//
// For optimal performance on the iSeries, issue the CRTJVAPGM
// command from the command line with optimize 40 (10 to debug):
//
// CRTJVAPGM CLSF('/qibm/userdata/os400/sqllib/function/javaUDFs.class') OPTIMIZE(10)
//
// To disable the JIT for debug:
// ADDENVVAR ENVVAR(QIBM_SQJAVA_PROPERTIES) VALUE('java.compiler=NONE') LEVEL(*SYS)

public class javaUDFs {
public static int square(int input) throws Exception {
for (int x = 0;x < 1;x = x)
Thread.sleep(1000);
int output = input * input;
return output;
}
}

Step 3: Use the following SQL to register the Java program as UDF:

CREATE FUNCTION qgpl.square(input int)
RETURNS INTEGER
EXTERNAL NAME 'javaUDFs.square'
LANGUAGE Java
PARAMETER STYLE Java
FENCED
NO SQL
RETURNS NULL ON NULL INPUT
SCRATCHPAD
DETERMINISTIC

Step 4: Use the following statement to call the Java program from the iSeries Navigator RunSQLScripts option:

select qgpl.square(4) from sysibm.sysdummy1

Step 5: Open an iSeries Navigator session.

Step 6: Connect to the system.

Step 7: Click on the plus sign by Databases.

Step 8: Right-click on the name of the system's connection.

Step 9: Click on Run SQL Scripts from the pull-down.

Step 10: From the next screen click Run.

Step 11: Select Debugger.



Step 12: Fill in the following screen, as shown:



Step 13: Call the Java UDF:



Step 14: Press the Play button to resume, and then the source will come up (you might need to specify the client side source). Add break points where needed, and resume. After the break points hit, you can edit the variable values.


The following is an example of a screen that is seen when the break point is hit:



Step 15: When you are done with debugging the program, the following SQL can be used to delete the UDF:

DROP FUNCTION qgpl.square(INT)