java stored procedures getting the environment ready n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Java Stored Procedures - Getting the Environment Ready PowerPoint Presentation
Download Presentation
Java Stored Procedures - Getting the Environment Ready

Loading in 2 Seconds...

play fullscreen
1 / 24

Java Stored Procedures - Getting the Environment Ready - PowerPoint PPT Presentation


  • 87 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


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