1 / 24

Java Stored Procedures - Getting the Environment Ready

Java Stored Procedures - Getting the Environment Ready. John Lantz Federal Reserve Board June 11, 2008. 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….

jasper-daniel
Download Presentation

Java Stored Procedures - Getting the Environment Ready

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Java Stored Procedures - Getting the Environment Ready John LantzFederal Reserve BoardJune 11, 2008

  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…

  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

  4. Java – what is it? • You have two options on how to deploy JDBC or SQLJ

  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(); }

  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; } } }

  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…

  8. Tasks for z/OS System Programmer • Install JDBC/SQLJ Drivers • Set up WLM environment • Set up UNIX system services • Required DB2 changes

  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)

  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)

  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)

  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)

  13. UNIX stuff… • Set up UNIX environmentLIBPATH, CLASSPATH, PATH, etc…(remember some UNIX is case sensitive) • DB2_HOME (where JDBC driver is installed) • JAVA_HOMEPer 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)

  14. Installing / configuring DB2 components • DSNTIJSG – creates various stored procedures (grant execute to PUBLIC)SQLJ.DB2_INSTALL_JAR,SQLJ.DB2_REPLACE_JARSQLJ.DB2_REMOVE_JARSQLJ.DB2_UPDATEJARINFO • DSNJDBC and DSNJAR collection’sperform GRANT CREATE ON COLLECTION… as necessary • DSNTIJMS – creates additional indexes and catalog objects (may have been run in V7…, check)

  15. Installing / configuring DB2 components • To use Development Center, must have SELECT on the following tables…SYSIBM.SYSDUMMY1SYSIBM.SYSROUTINES SYSIBM.SYSPARMSSYSIBM.SYSJARCONTENTSSYSIBM.SYSJAROBJECTSSYSIBM.SYSJAVAOPTSDSNTIJSG • Individual users do not need their own profiles set up within UNIX

  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…

  17. 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.

  18. 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 DSNJDBCWLM ENVIRONMENT DSNUJAV1 PROGRAM TYPE SUB COMMIT ON RETURN NO ; • External name refers to java classname.methodname

  19. Experiences… • Still in the exploratory mode. • It’s working and available for the developers • Performance has been good so far

  20. Recommended resources • Information Centerhttp://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp • DB2 for z/OS and OS/390: Ready for JavaSG24-6435-00 • DB2 for z/OS Stored Procedures :Through the CALL and Beyond SG24-7083-00 • Application Programming Guide and Reference for JavaSC18-7414-05

More Related