1 / 27

Java Enhancements in DB2 for z/OS and OS/390 version 7

Java Enhancements in DB2 for z/OS and OS/390 version 7. Issuer : Michael Ärlebrandt Issued : 2002-03-06. Java enhancements in V7. Implements support for the JDBC 2.0 standard required to support JDK 1.3 and products such as WebSphere version 4 JDBC 2.0 DataSource support

joel-mays
Download Presentation

Java Enhancements in DB2 for z/OS and OS/390 version 7

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 Enhancements in DB2 for z/OS and OS/390 version 7 Issuer : Michael Ärlebrandt Issued : 2002-03-06

  2. Java enhancements in V7 • Implements support for the JDBC 2.0 standard • required to support JDK 1.3 and products such as WebSphere version 4 • JDBC 2.0 DataSource support • JDBC 2.0 connection pooling • JDBC 2.0 Distributed transaction support • Support for userid/password usage on SQL CONNECT vi URL • Java Stored Procedures using interpreted Java (JVM)

  3. Java Stored Procedures in DB2 for z/OS and OS/390 Version 7

  4. Java Stored Procedures • SQLJ Part 1 specification • Can be JDBC, SQLJ, or both • Compiled Java on V5, V6 and V7 • Interpreted Java support in V7 • JAR files support in V7

  5. Java Stored Procedures Support • Compiled Java stored procedures • CREATE PROC … LANGUAGE COMPJAVA • Java bytecodes must be compiled with VisualAge Java bytecode binder (HPJ) • Requires JDK 1.1.8 • HPJ is not being enhanced from Java 1.1.xLanguage COMPJAVA will not be supported in next version of DB2 for z/OS. • Interpreted Java stored procedures • CREATE PROC … LANGUAGE JAVA • Java programs stored in JAR files in DB2 catalog • Exploits new IBM JVM technology

  6. Requirements for Java JVM SP • IBM Developer Kit for OS/390, Java(TM) 2 Technology Edition with Persistent Reusable Java Virtual Machines • Currently running in Volvo production environment • JDBC 2.0 driver delivered with DB2 v7 • Installed in Customer Test environment, VT01/03 • All stored procedures are “shared classes” • not reinitialised between invocations • Performance approximately equal to HPJ

  7. Java constructs JAR V012345.MY_JARFILE • JAR - Java Archive filecollection of classes (binary) • CLASS - collection of java objects and/or methods (abc) • METHOD - Java program (method1) • SIGNATURE - Parameter types (int,String[]) Class abc { static void method1 (int,String[]){ ….. }

  8. OS/390 System The Big Picture DRDA or JAVA or Native CLIENT Exec SQL CALL PROC x WLM DB2 WLM SP Address Space catalog entry USS JAR /u/classes/ Driver: Find Java package Load and Execute Java method Identify C1 Identify C2 Return parms C1 rows C2 rows SDK 1.3.1 Java Method JDBC Driver

  9. Tasks for OS/390 System Programmer //D2Y0WLM PROC RGN=0K,APPLENV=XXXXXXXX,DB2SSN=D2Y0,NUMTCB=7 ... //JAVAENV DD DISP=SHR,DSN=F1D2VT2.PROD.D2Y0.JAVAENV //JSPDEBUG DD SYSOUT=* ONLY USE THIS IN TEST • Set up WLM environment, address space JCL • Set up JAVAENV with “home” directories • Use JSPDEBUG DD in development, not in production • Keep NUMTCB low, suggest 7 or less • Provide .profile for users

  10. JAVAENV DD Card • Dataset containing RUNOPTS • Applies to entire WLMENV, not individual SPs • Must set JAVA_HOME • IBM JDK 1.3 with special enhancement • Must set DB2_HOME • JDBC/SQLJ Driver directory • Optionally set CLASSPATH • Directory for user classes not in JAR • all SP classes are shared • Limited to 245 characters

  11. JAVAENV DD Card Example Organization . . . : PS Record format . . . : VB Record length . . . : 1028 ENVAR("CLASSPATH=/home/v070674/classes", "JAVA_HOME=/usr/lpp/java/IBM/J1.3", "DB2_HOME=/usr/lpp/db2/db2710"), MSGFILE(JSPDEBUG,,,,ENQ)

  12. Install and Maintain JAR files • DB2 v7 built-in utility Stored Procedures • As per SQLJ specification • Invoked with CALL statement • INSTALL_JAR • Installs the Java Archive file into the DB2 catalog as a LOB from the OS/390 HFS • JAR file contains one or more Stored Procedures • REPLACE_JAR • REMOVE_JAR • new JAR authorisation • GRANT USAGE ON JAR

  13. Interpreted Java SP Example /* SQLJ Stored Procedure V070674.Sp003 */ package s3;import java.sql.*; // JDBC classesimport sqlj.runtime.*;import sqlj.runtime.ref.*;#sql iterator Sp003_Cursor1 ( String, String );public class Sp003 { public static void sp003 (String[] text, ResultSet[] rs ) throws SQLException, Exception { Sp003_Cursor1 cursor1 = null; #sql cursor1 = { SELECT ID,NAME FROM Q.STAFF ORDER BY 1 FETCH FIRST 10 ROWS ONLY }; rs[0] = cursor1.getResultSet(); text[0] = " SQLJ SP ended successful"; } }

  14. Java Stored Procedures Differences A Java SP differs from procedures in other languages • PARAMETER STYLE JAVA • Output parameters are single-element arrays • Because Java doesn’t allow modifying parms • Result sets are in method signature as outputs • Because “with return” is not universal • Mapping beetween datatypes

  15. Preparing SQLJ Stored Procedure • Translate the SQLJ program • sqlj Sp003.sqlj • Compile the generated java program • javac Sp003.java • Customize the serialized profile • db2profc -pgmname=SP003 Sp003_SJProfile0.ser • Create the JAR file • jar -cvf Sp003.jar s3/Sp003.class s3/Sp003_Cursor1.class s3/Sp003_SJProfile0.ser s3/Sp003_SJProfileKeys.class

  16. Bind the SQLJ packages //BINDSQLJ EXEC PGM=IKJEFT01,DYNAMNBR=20 //SYSTSIN DD * DSN SYSTEM(D2Y2) BIND PACKAGE (SQLJMIAR) MEMBER(SP0031) ISOLATION(UR) BIND PACKAGE (SQLJMIAR) MEMBER(SP0032) ISOLATION(CS) BIND PACKAGE (SQLJMIAR) MEMBER(SP0033) ISOLATION(RS) BIND PACKAGE (SQLJMIAR) MEMBER(SP0034) ISOLATION(RR) BIND PLAN(SQLJMIAR) PKLIST(SQLJMIAR.*,DSNJDBC.*) RUN PROGRAM(DSNTIAD) PLAN(DSNTIAD) END //SYSIN DD * GRANT EXECUTE ON PLAN SQLJMIAR TO PUBLIC ; GRANT EXECUTE ON PACKAGE SQLJMIAR.SP0032 TO PUBLIC ;

  17. Create Procedure CREATE PROCEDURE V070674.SP003 (CHAR(30) OUT) COLLID SQLJMIAR FENCED MODIFIES SQL DATA LANGUAGE JAVA EXTERNAL NAME 'V070674.JAR_SP003:s3.Sp003.sp003' DYNAMIC RESULT SETS 1 WLM ENVIRONMENT D2Y0WL03 PROGRAM TYPE SUB PARAMETER STYLE JAVA; GRANT EXECUTE ON PROCEDURE V070674.SP003 TO PUBLIC ; GRANT USAGE ON JAR V070674.JAR_SP003 TO PUBLIC ; NAME(jar:package.class.method(signature))

  18. Refresh WLM SP Address Space • Using CommandV WLM, APPLENV=D2Y0WL03,REFRESHAuthority to issue operator commands needed • Use WLM_REFRESH Stored Procedure

  19. Calling JSP application example // Create a CallableStatement CallableStatement proc1 ; proc1 = con.prepareCall("CALL V070674.SP003(?)") ; // Specify the parameter types proc1.registerOutParameter(1,java.sql.Types.CHAR); // run proc1 ResultSet rs = proc1.executeQuery(); String text = proc1.getString(1);

  20. DB2 Stored Procedure Builder

  21. Additional Documentation • Application Programing Guide and Reference FOR JAVA ™ Version 7 SC26-9932-01 • Redbook: DB2 Java Stored Procedures Learning by Example • DB2 for OS/390 Stored Procedures site:http://www-3.ibm.com/software/data/db2/os390/spb/exciting/ • M41 Java and Design for high performance by John Campbell • M34 JDBC, VAJ DLLs and Java Stored Procedures - An OS/390 Setup Cookbook by Peggy Rader • M27 Hot topics in stored procedures in DB2 by Peggy Abelite

  22. Some practical exampels

  23. WebSphere Studio Application Developer Versioner • Site Developer • App Developer Egenskaper • Inbyggd webbserver • Inbyggd applikationsserver (mini-WebSphere) • EJB-Wizards • Deployment till WebSphere • Debug • Integrerad miljö • workspace på filsystemet (Refresh from local ) • Rational Integration • Rose, ClearCase

  24. Client DB2 Connect Server OS/390 JDBC Settings Case 1 Java application running in client using local database • URL : jdbc:db2:mydb • Driver : COM.ibm.db2.jdbc.app.DB2Driver • Classpath: db2jdbc.zip

  25. DB2 Connect Server JDBC Settings Case 2 Client OS/390 vsegn133.it.volvo.se Java application running in client using OS/390 DB2 • URL : jdbc:db2://vsegn133.it.volvo.se:6789/D2VT • Driver : COM.ibm.db2.jdbc.net.DB2Driver • Classpath: db2jdbc.zip (Note! Use same version as DB2 Connect Server)

  26. Client OS/390 JDBC Settings Case 3 Java application running in OS/390 against DB2 • URL : jdbc:db2os390:SEVOL0D2D2VT01 • Driver : COM.ibm.db2os390.sqlj.jdbc.DB2SQLJDriver • Classpath: db2j2classes.zip

More Related