Volvo Information Technology Java Enhancements in DB 2
Volvo Information Technology Java Enhancements in DB 2 for z/OS and OS/390 version 7 2540 OS/390 UNIX Issuer : Michael Ärlebrandt Issued : 2002 -03 -06 Win 2000 Volvo Information Technology Dept. 2540 Slide: 1
Volvo Information Technology Java enhancements in V 7 u Implements support for the JDBC 2. 0 standard ® ® required to support JDK 1. 3 and products such as Web. Sphere version 4 JDBC 2. 0 Data. Source support JDBC 2. 0 connection pooling JDBC 2. 0 Distributed transaction support u Support for userid/password usage on SQL CONNECT vi URL u Java Stored Procedures using interpreted Java (JVM) Volvo Information Technology Dept. 2540 Slide: 2
Volvo Information Technology Java Stored Procedures in DB 2 for z/OS and OS/390 Version 7 Volvo Information Technology Dept. 2540 Slide: 3
Volvo Information Technology Java Stored Procedures u SQLJ Part 1 specification u Can be JDBC, SQLJ, or both u Compiled Java on V 5, V 6 and V 7 u Interpreted Java support in V 7 u JAR files support in V 7 Volvo Information Technology Dept. 2540 Slide: 4
Volvo Information Technology Java Stored Procedures Support u Compiled Java stored procedures CREATE PROC … LANGUAGE COMPJAVA ® Java bytecodes must be compiled with Visual. Age Java bytecode binder (HPJ) ® Requires JDK 1. 1. 8 ® HPJ is not being enhanced from Java 1. 1. x Language COMPJAVA will not be supported in next version of DB 2 for z/OS. ® u Interpreted Java stored procedures CREATE PROC … LANGUAGE JAVA ® Java programs stored in JAR files in DB 2 catalog ® Exploits new IBM JVM technology ® Volvo Information Technology Dept. 2540 Slide: 5
Volvo Information Technology Requirements for Java JVM SP u IBM Developer Kit for OS/390, Java(TM) 2 Technology Edition with Persistent Reusable Java Virtual Machines ® Currently running in Volvo production environment u JDBC 2. 0 driver delivered with DB 2 v 7 ® Installed in Customer Test environment, VT 01/03 u All stored procedures are “shared classes” ® not reinitialised between invocations u Performance approximately equal to HPJ Volvo Information Technology Dept. 2540 Slide: 6
Volvo Information Technology Java constructs JAR V 012345. MY_JARFILE Class abc { static void method 1 (int, String[]){ …. . } Volvo Information Technology Dept. 2540 Slide: 7 u JAR - Java Archive file collection of classes (binary) u CLASS - collection of java objects and/or methods (abc) u METHOD - Java program (method 1) u SIGNATURE - Parameter types (int, String[])
Volvo Information Technology The Big Picture OS/390 System Exec SQL CALL PROC x WLM DB 2 catalog entry DRDA or JAVA or Native CLIENT JAR Identify C 1 Identify C 2 Return parms C 1 rows C 2 rows Volvo Information Technology Dept. 2540 Slide: 8 WLM SP USS Address Space /u/classes/ Driver: SDK 1. 3. 1 Java Method JDBC Driver Find Java package Load and Execute Java method
Volvo Information Technology Tasks for OS/390 System Programmer u Set up WLM environment, address space JCL //D 2 Y 0 WLM PROC RGN=0 K, APPLENV=XXXX, DB 2 SSN=D 2 Y 0, NUMTCB=7. . . //JAVAENV DD DISP=SHR, DSN=F 1 D 2 VT 2. PROD. D 2 Y 0. JAVAENV //JSPDEBUG DD SYSOUT=* ONLY USE THIS IN TEST u Set up JAVAENV with “home” directories u Use JSPDEBUG DD in development, not in production u Keep NUMTCB low, suggest 7 or less u Provide. profile for users Volvo Information Technology Dept. 2540 Slide: 9
Volvo Information Technology JAVAENV DD Card u Dataset containing RUNOPTS ® Applies to entire WLMENV, not individual SPs u Must set JAVA_HOME ® IBM JDK 1. 3 with special enhancement u Must set DB 2_HOME ® JDBC/SQLJ Driver directory u Optionally set CLASSPATH Directory for user classes not in JAR ® all SP classes are shared ® u Limited to 245 characters Volvo Information Technology Dept. 2540 Slide: 10
Volvo Information Technology JAVAENV DD Card Example Organization. . . : Record format. . . : Record length. . . : PS VB 1028 ENVAR("CLASSPATH=/home/v 070674/classes", "JAVA_HOME=/usr/lpp/java/IBM/J 1. 3", "DB 2_HOME=/usr/lpp/db 2710"), MSGFILE(JSPDEBUG, , ENQ) Volvo Information Technology Dept. 2540 Slide: 11
Volvo Information Technology Install and Maintain JAR files u DB 2 v 7 built-in utility Stored Procedures u As per SQLJ specification u Invoked with CALL statement INSTALL_JAR • Installs the Java Archive file into the DB 2 catalog as a LOB from the OS/390 HFS • JAR file contains one or more Stored Procedures ® REPLACE_JAR ® REMOVE_JAR ® u new JAR authorisation ® GRANT USAGE ON JAR Volvo Information Technology Dept. 2540 Slide: 12
Volvo Information Technology Interpreted Java SP Example /* SQLJ Stored Procedure V 070674. Sp 003 */ package s 3; import java. sql. *; import sqlj. runtime. ref. *; // JDBC classes #sql iterator Sp 003_Cursor 1 ( String, String ); public class Sp 003 { public static void sp 003 (String[] text, Result. Set[] rs ) throws SQLException, Exception { Sp 003_Cursor 1 cursor 1 = null; #sql cursor 1 = { SELECT ID, NAME FROM Q. STAFF ORDER BY 1 FETCH FIRST 10 ROWS ONLY }; rs[0] = cursor 1. get. Result. Set(); text[0] = " SQLJ SP ended successful"; }} Volvo Information Technology Dept. 2540 Slide: 13
Volvo Information Technology Java Stored Procedures Differences A Java SP differs from procedures in other languages u PARAMETER STYLE JAVA u Output parameters are single-element arrays ® Because Java doesn’t allow modifying parms u Result sets are in method signature as outputs ® Because “with return” is not universal u Mapping beetween datatypes Volvo Information Technology Dept. 2540 Slide: 14
Volvo Information Technology Preparing SQLJ Stored Procedure u Translate the SQLJ program ® sqlj Sp 003. sqlj u Compile the generated java program ® javac Sp 003. java u Customize the serialized profile ® db 2 profc -pgmname=SP 003 Sp 003_SJProfile 0. ser u Create the JAR file ® jar -cvf Sp 003. jar s 3/Sp 003. class s 3/Sp 003_Cursor 1. class s 3/Sp 003_SJProfile 0. ser s 3/Sp 003_SJProfile. Keys. class Volvo Information Technology Dept. 2540 Slide: 15
Volvo Information Technology Bind the SQLJ packages //BINDSQLJ EXEC PGM=IKJEFT 01, DYNAMNBR=20 //SYSTSIN DD * DSN SYSTEM(D 2 Y 2) BIND PACKAGE (SQLJMIAR) MEMBER(SP 0031) ISOLATION(UR) BIND PACKAGE (SQLJMIAR) MEMBER(SP 0032) ISOLATION(CS) BIND PACKAGE (SQLJMIAR) MEMBER(SP 0033) ISOLATION(RS) BIND PACKAGE (SQLJMIAR) MEMBER(SP 0034) 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. SP 0032 TO PUBLIC ; Volvo Information Technology Dept. 2540 Slide: 16
Volvo Information Technology Create Procedure CREATE PROCEDURE V 070674. SP 003 (CHAR(30) OUT) COLLID SQLJMIAR FENCED MODIFIES SQL DATA NAME(jar: package. class. method(signature)) LANGUAGE JAVA EXTERNAL NAME 'V 070674. JAR_SP 003: s 3. Sp 003. sp 003' DYNAMIC RESULT SETS 1 WLM ENVIRONMENT D 2 Y 0 WL 03 PROGRAM TYPE SUB PARAMETER STYLE JAVA; GRANT EXECUTE ON PROCEDURE V 070674. SP 003 TO PUBLIC ; GRANT USAGE ON JAR V 070674. JAR_SP 003 TO PUBLIC ; Volvo Information Technology Dept. 2540 Slide: 17
Volvo Information Technology Refresh WLM SP Address Space u Using Command V WLM, APPLENV=D 2 Y 0 WL 03, REFRESH Authority to issue operator commands needed u Use WLM_REFRESH Stored Procedure Volvo Information Technology Dept. 2540 Slide: 18
Volvo Information Technology Calling JSP application example // Create a Callable. Statement proc 1 ; proc 1 = con. prepare. Call("CALL V 070674. SP 003(? )") ; // Specify the parameter types proc 1. register. Out. Parameter(1, java. sql. Types. CHAR); // run proc 1 Result. Set rs = proc 1. execute. Query(); String text = proc 1. get. String(1); Volvo Information Technology Dept. 2540 Slide: 19
Volvo Information Technology Dept. 2540 Slide: 20
Volvo Information Technology DB 2 Stored Procedure Builder Volvo Information Technology Dept. 2540 Slide: 21
Volvo Information Technology Additional Documentation u Application Programing Guide and Reference FOR JAVA ™ Version 7 SC 26 -9932 -01 u Redbook: DB 2 Java Stored Procedures Learning by Example u DB 2 for OS/390 Stored Procedures site: http: //www-3. ibm. com/software/data/db 2/os 390/spb/exciting/ u M 41 Java and Design for high performance by John Campbell u M 34 JDBC, VAJ DLLs and Java Stored Procedures - An OS/390 Setup Cookbook by Peggy Rader u M 27 Hot topics in stored procedures in DB 2 by Peggy Abelite Volvo Information Technology Dept. 2540 Slide: 22
Volvo Information Technology Some practical exampels Volvo Information Technology Dept. 2540 Slide: 23
Volvo Information Technology Web. Sphere Studio Application Developer Versioner u Site Developer u App Developer Egenskaper u Inbyggd webbserver u Inbyggd applikationsserver (mini-Web. Sphere) u EJB-Wizards u Deployment till Web. Sphere u Debug u Integrerad miljö workspace på filsystemet (Refresh from local ) u Rational Integration ® Rose, Clear. Case ® Volvo Information Technology Dept. 2540 Slide: 24
Volvo Information Technology JDBC Settings Case 1 Client DB 2 Connect Server OS/390 Java application running in client using local database u URL : jdbc: db 2: mydb u Driver : COM. ibm. db 2. jdbc. app. DB 2 Driver u Classpath: db 2 jdbc. zip Volvo Information Technology Dept. 2540 Slide: 25
Volvo Information Technology JDBC Settings Case 2 Client DB 2 Connect Server OS/390 vsegn 133. it. volvo. se Java application running in client using OS/390 DB 2 u URL : jdbc: db 2: //vsegn 133. it. volvo. se: 6789/D 2 VT u Driver : COM. ibm. db 2. jdbc. net. DB 2 Driver u Classpath: db 2 jdbc. zip (Note! Use same version as DB 2 Connect Server) Volvo Information Technology Dept. 2540 Slide: 26
Volvo Information Technology JDBC Settings Case 3 Client OS/390 Java application running in OS/390 against DB 2 u URL : jdbc: db 2 os 390: SEVOL 0 D 2 D 2 VT 01 u Driver : COM. ibm. db 2 os 390. sqlj. jdbc. DB 2 SQLJDriver u Classpath: db 2 j 2 classes. zip Volvo Information Technology Dept. 2540 Slide: 27
- Slides: 27