Java stored procedures getting the environment ready
Download
1 / 24

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


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