Slide 1: Java Stored Procedures Getting the Environment Ready
John Lantz Federal Reserve Board June 11, 2008
Slide 2: Agenda for today
What is Java, background information Java and DB2 Implementing Java on the mainframe Building a procedure via Development Center Tips, resources, etc…
Slide 3: Java – why use it?
A platform independent object oriented programming language Richer programming language then SQL Procedure Language Compile it once and deploy it anywhere
Slide 4: Java – what is it?
You have two options on how to deploy JDBC or SQLJ
JDBC
Dynamic SQL Easier to write Errors detected at RUN time
SQLJ
Static SQL Faster run time Stable access paths Errors detected at BIND time Easy monitoring
Slide 5: Java – example (JDBC)
/** * JDBC Stored Procedure M1JPL01.test_java_jdbc */ package PKG80503035400970; import java.sql.*; // JDBC classes
public class Test_java_jdbc { public static void test_java_jdbc ( ResultSet[] rs1 ) throws SQLException, Exception { // Get connection to the database Connection con = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement stmt = null; boolean bFlag; String sql; sql = "SELECT SCHEMA, NAME FROM SYSIBM.SYSROUTINES"; stmt = con.prepareStatement( sql ); bFlag = stmt.execute(); rs1[0] = stmt.getResultSet(); }
Slide 6: Java – example (SQLJ)
/** * SQLJ Stored Procedure M1JPL01.test_java_sqlj */ package PKG80503035149340; import java.sql.*; // JDBC classes import sqlj.runtime.*; import sqlj.runtime.ref.*; #sql context SPContext; #sql iterator Test_java_sqlj_Cursor1 ( String, String ); public class Test_java_sqlj { public static void test_java_sqlj ( ResultSet[] rs1 ) throws SQLException, Exception { Test_java_sqlj_Cursor1 cursor1 = null; SPContext ctx = null; try { ctx = new SPContext( "jdbc:default:connection", false ); #sql [ctx] cursor1 = { SELECT SCHEMA, NAME FROM SYSIBM.SYSROUTINES }; rs1[0] = cursor1.getResultSet(); } catch (SQLException e) { // Close open resources try { if (cursor1 != null) cursor1.close() } catch (SQLException e2) { /* ignore */ }; throw e; } } }
Slide 7: Java – Which to use?
One reason why you use stored procedures is to have code that is efficient as possible This is accomplished by static SQL – thus avoiding overhead of prepare/etc… at execution time Using JDBC – kind of defeats the purpose of a stored procedure…
Slide 8: Tasks for z/OS System Programmer
Install JDBC/SQLJ Drivers Set up WLM environment Set up UNIX system services Required DB2 changes
Slide 9: Installing drivers
Part of DB2 base code: FMID JDB8812 Jobs DSNDDEF2 and DSNISMKD DSNDDEF2 - creates necessary DDDEF for DB2 universal drivers DSNISMKD - allocates the HFS structures (both jobs are smp/e stuff)
Slide 10: WLM setup…
Set up RRS (Resource Recovery Service) DESCSTAT=YES in DSNZPARM (necessary for SQLJ support) Set NUMTCB=5 for java WLM. Initially set to 1, not to exceed 8. Make sure //JAVAENV DD statement is correct WLM environment should be dedicated to Java stored procedures (don’t mix COBOL and SQL)
Slide 11: WLM setup… (continued)
Don’t confuse the WLM environments. In our environment… DSNUWLM1 and DSNUJAV1 DSN…WLM1 is used in the build process (issues the refresh command) DSN…JAV1 is where the procedures execute (where the procedure executes)
Slide 12: WLM setup… (continued)
Sample DD statements within the Java WLM address space. The JAVAENV specifies the LE run-time options for Java routines
//JAVAENV DD DSN=SYS2.DB2.DEVU.DSNUJAV1.JSPENV //JAVAERR DD PATH='/usr/lpp/dsnu/jsppuser/JAVAERR.TXT', PATHOPTS=(ORDWR,OCREAT,OAPPEND), PATHMODE=(SIRUSR,SIWUSR,SIRGRP,SIWGRP,SIROTH,SIWOTH) //JAVAOUT DD PATH='/usr/lpp/dsnu/jsppuser/JAVAOUT.TXT', PATHOPTS=(ORDWR,OCREAT,OAPPEND), PATHMODE=(SIRUSR,SIWUSR,SIRGRP,SIWGRP,SIROTH,SIWOTH)
Slide 13: UNIX stuff…
Set up UNIX environment LIBPATH, CLASSPATH, PATH, etc… (remember some UNIX is case sensitive) DB2_HOME (where JDBC driver is installed) JAVA_HOME Per IBM, we must install Java 31-bit version software on the z/OS 1.8 system and the JAVA_HOME must point to the 31 bit Java version. (remember some UNIX is case sensitive)
Slide 14: Installing / configuring DB2 components
DSNTIJSG – creates various stored procedures (grant execute to PUBLIC) SQLJ.DB2_INSTALL_JAR, SQLJ.DB2_REPLACE_JAR SQLJ.DB2_REMOVE_JAR SQLJ.DB2_UPDATEJARINFO DSNJDBC and DSNJAR collection’s perform GRANT CREATE ON COLLECTION… as necessary DSNTIJMS – creates additional indexes and catalog objects (may have been run in V7…, check)
Slide 15: Installing / configuring DB2 components
To use Development Center, must have SELECT on the following tables…
SYSIBM.SYSDUMMY1 SYSIBM.SYSROUTINES SYSIBM.SYSPARMS SYSIBM.SYSJARCONTENTS SYSIBM.SYSJAROBJECTS SYSIBM.SYSJAVAOPTSDSNTIJSG
Individual users do not need their own profiles set up within UNIX
Slide 16: Building a procedure
Things the programmer needs to know before building a procedure via the Development Center - Proper Java WLM environment - Proper collection to use, and the necessary GRANT CREATE ON COLLECTION…
Slide 20: Building a procedure (cont)
Do not check “Build using DSNTJSPP” on the z/OS Options panel. This is for the Legacy Driver type 2, not the Universal Driver type 2.
Slide 22: Java – Define DDL for procedure
This gets created for you….
CREATE PROCEDURE M1JPL01.TEST_JAVA_SQLJ ( IN INP_PARM VARCHAR(128) CCSID EBCDIC ) RESULT SETS 1 EXTERNAL NAME M1JPL01.SQL80503035149340:PKG80503035149340.Test_java_sqlj.test_java_sqlj' LANGUAGE JAVA PARAMETER STYLE JAVA NOT DETERMINISTIC NULL CALL FENCED MODIFIES SQL DATA NO DBINFO COLLID DSNJDBC WLM ENVIRONMENT DSNUJAV1 PROGRAM TYPE SUB COMMIT ON RETURN NO ;
External name refers to java classname.methodname
Slide 23: Experiences…
Still in the exploratory mode. It’s working and available for the developers Performance has been good so far
Slide 24: Recommended resources
Information Center http://publib.boulder.ibm.com/infocenter/dzichelp/v2r DB2 for z/OS and OS/390: Ready for Java SG24-6435-00 DB2 for z/OS Stored Procedures : Through the CALL and Beyond SG24-7083-00 Application Programming Guide and Reference for Java SC18-7414-05
