java stored procedures getting the environment ready
Download
Skip this Video
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


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

PowerPoint Slideshow about ' Java Stored Procedures - Getting the Environment Ready' - jasper-daniel


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
java stored procedures getting the environment ready

Java Stored Procedures - Getting the Environment Ready

John LantzFederal Reserve BoardJune 11, 2008

agenda for today
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…
java why use it
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
java what is it
Java – what is it?
  • You have two options on how to deploy JDBC or SQLJ
java example jdbc
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();

}

java example sqlj
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;

}

}

}

java which to use
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…
tasks for z os system programmer
Tasks for z/OS System Programmer
  • Install JDBC/SQLJ Drivers
  • Set up WLM environment
  • Set up UNIX system services
  • Required DB2 changes
installing drivers
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)

wlm setup
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)
wlm setup continued
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)
wlm setup continued1
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)
unix stuff
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)
installing configuring db2 components
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)
installing configuring db2 components1
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
building a procedure
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…
building a procedure cont
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.
java define ddl for procedure
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
experiences
Experiences…
  • Still in the exploratory mode.
  • It’s working and available for the developers
  • Performance has been good so far
recommended resources
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
ad