Java Stored Procedures in DB2 for z/OS and OS/390 version 7
Download
Report
Transcript Java Stored Procedures in DB2 for z/OS and OS/390 version 7
Volvo Information Technology
Java Enhancements in DB2 for
z/OS and OS/390 version 7
2540
OS/390
UNIX
Issuer : Michael Ärlebrandt
Issued : 2002-03-06
Win2000
Volvo Information Technology
Dept. 2540
Slide: 1
Volvo Information Technology
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)
Volvo Information Technology
Dept. 2540
Slide: 2
Volvo Information Technology
Java Stored Procedures in DB2 for z/OS and
OS/390 Version 7
Volvo Information Technology
Dept. 2540
Slide: 3
Volvo Information Technology
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
Volvo Information Technology
Dept. 2540
Slide: 4
Volvo Information Technology
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.x
Language 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
Volvo Information Technology
Dept. 2540
Slide: 5
Volvo Information Technology
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
Volvo Information Technology
Dept. 2540
Slide: 6
Volvo Information Technology
Java constructs
JAR V012345.MY_JARFILE
Class abc {
static void
method1
(int,String[]){
….. }
Volvo Information Technology
Dept. 2540
Slide: 7
JAR - Java Archive file
collection of classes (binary)
CLASS - collection of java
objects and/or methods (abc)
METHOD - Java program
(method1)
SIGNATURE - Parameter
types (int,String[])
Volvo Information Technology
The Big Picture
OS/390 System
Exec SQL
CALL
PROC x
WLM
DB2
WLM SP
catalog entry
DRDA
or
JAVA
or
Native
CLIENT
USS
JAR
/u/classes/
Identify C1
Identify C2
Return parms
C1 rows
C2 rows
Volvo Information Technology
Dept. 2540
Slide: 8
Address Space
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
Set up WLM environment, address space JCL
//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 JAVAENV with “home” directories
Use JSPDEBUG DD in development, not in production
Keep NUMTCB low, suggest 7 or less
Provide .profile for users
Volvo Information Technology
Dept. 2540
Slide: 9
Volvo Information Technology
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
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/v070674/classes",
"JAVA_HOME=/usr/lpp/java/IBM/J1.3",
"DB2_HOME=/usr/lpp/db2/db2710"),
MSGFILE(JSPDEBUG,,,,ENQ)
Volvo Information Technology
Dept. 2540
Slide: 11
Volvo Information Technology
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
Volvo Information Technology
Dept. 2540
Slide: 12
Volvo Information Technology
Interpreted Java SP Example
/* SQLJ Stored Procedure V070674.Sp003 */
package s3;
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
// JDBC classes
#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";
}}
Volvo Information Technology
Dept. 2540
Slide: 13
Volvo Information Technology
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
Volvo Information Technology
Dept. 2540
Slide: 14
Volvo Information Technology
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
Volvo Information Technology
Dept. 2540
Slide: 15
Volvo Information Technology
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 ;
Volvo Information Technology
Dept. 2540
Slide: 16
Volvo Information Technology
Create Procedure
CREATE PROCEDURE V070674.SP003
(CHAR(30) OUT)
COLLID SQLJMIAR
FENCED
MODIFIES SQL DATA
NAME(jar:package.class.method(signature))
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 ;
Volvo Information Technology
Dept. 2540
Slide: 17
Volvo Information Technology
Refresh WLM SP Address Space
Using Command
V WLM,
APPLENV=D2Y0WL03,
REFRESH
Authority to issue
operator commands
needed
Use WLM_REFRESH
Stored Procedure
Volvo Information Technology
Dept. 2540
Slide: 18
Volvo Information Technology
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);
Volvo Information Technology
Dept. 2540
Slide: 19
Volvo Information Technology
Volvo Information Technology
Dept. 2540
Slide: 20
Volvo Information Technology
DB2 Stored Procedure Builder
Volvo Information Technology
Dept. 2540
Slide: 21
Volvo Information Technology
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
Volvo Information Technology
Dept. 2540
Slide: 22
Volvo Information Technology
Some practical exampels
Volvo Information Technology
Dept. 2540
Slide: 23
Volvo Information Technology
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
Volvo Information Technology
Dept. 2540
Slide: 24
Volvo Information Technology
JDBC Settings Case 1
Client
DB2 Connect Server
OS/390
Data
Data
Laptop computer
IBM Compatible
Processor
Java application running in client using local database
URL
:
jdbc:db2:mydb
Driver
:
COM.ibm.db2.jdbc.app.DB2Driver
Classpath:
db2jdbc.zip
Volvo Information Technology
Dept. 2540
Slide: 25
Volvo Information Technology
JDBC Settings Case 2
Client
DB2 Connect Server
OS/390
Data
Data
Laptop computer
IBM Compatible
vsegn133.it.volvo.se
Processor
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)
Volvo Information Technology
Dept. 2540
Slide: 26
Volvo Information Technology
JDBC Settings Case 3
Client
OS/390
Data
Data
Laptop computer
Processor
Java application running in OS/390 against DB2
URL
:
jdbc:db2os390:SEVOL0D2D2VT01
Driver
:
COM.ibm.db2os390.sqlj.jdbc.DB2SQLJDriver
Classpath:
db2j2classes.zip
Volvo Information Technology
Dept. 2540
Slide: 27