DB2

1
DB2 Java for Dummies
Session G09

• John Mallonee
• Highmark Inc.

May 9, 2007 1110 a.m. 1210 p.m. Platform
cross-platform
2
Bullet Points

• Java Basics
• Java Development/Runtime Environment
• Java and DB2
• Security
• Development/Management Issues

3
Disclaimer

• Dummies (noun, plural) smart people who want to
  learn more about a technology with which they
  dont have much experience.
• (alt.) risk takers who want to step beyond their
  current boundaries and become more knowledgeable
  and productive

4
Bullet Points

• Java Basics
• Java Development/Runtime Environment
• Java and DB2
• Security
• Development/Management Issues

5
Java Basics

• What is Java?
• Quick example
• Java Terminology
• Compiling/Running Java programs
• Java Applications
• Java Editions

6
Java Basics What is Java?

• Java is an object-oriented programming language
  created by Sun Microsystems
• Java contains a JDK (Java Development Kit) which
  contains a compiler and a set of base classes
  (Java programs) which provide base-level
  functionality
• Java contains classes which provide a generic SQL
  interface to relational databases including DB2
  (JDBC Java Database Connectivity)
• Java is designed to be a write once, run
  everywhere (or most places) language
• Java is a pervasive language for creating open
  systems applications including web applications

7
Java Basics Quick Example
class

• public class MyClass
• private String name
• public static void main(String args)
• name args0
• for (int i0 ilt5 i)
• System.out.println(name name)

attribute
method
8
Java Basics Terminology

• Source program - .java file
• Compiled program - .class file executable
  code for target Java Virtual Machine (JVM)
• Java Virtual Machine (JVM)
• software written for a specific operating system
  which can execute .class files
• implementation of the JVM specification
• interprets Java Byte Code
• Java Runtime Environment (JRE) contains JVM,
  core classes, and other files
• Java Development Kit (JDK) components needed to
  develop in Java including a JVM and JRE

9
Java Basics Terminology

• package grouping of similar Java classes
• e.g. com.highmark.app.MyClass
• equates to folders - /com/highmark/app/MyClass.cla
  ss
• JAR compressed file of Java classes
• equivalent to mainframe library
• classpath Java classes referenced by Java
  compile or Java runtime
• equivalent to mainframe library concatenation
• APIs Application Program Interface
• interface to a Java class (specifies how to
  invoke)
• may be generic with specific implementation
  classes
• Java specification versions through Java
  Community Process (JCP)

10
Java Basics Compiling/Running

• Source code is created .java file
• Source is compiled to byte code .class file
  created using javac program (e.g. javac
  MyClass.java)
• Compiled code is stored as a .class file
  (optionally in a JAR file) within the JVM
• Compiled class is initiated on the JVM using the
  java command (e.g. java MyClass)

11
Java Basics Compiling/Running
javac
java
Source (.java)
Byte Code (.class)
Java Virtual Machine
archive
Java Archive (.jar)
12
Java Basics Java Editions

• Java SE Java Standard Edition (formerly J2SE)
• Standard Java classes to perform low-level
  operations in any environment
• Includes desktop applications (e.g. visual
  objects)
• Java EE Java Enterprise Edition (formerly J2EE)
• Additional Java classes to support
  enterprise-wide applications
• Java ME Java Micro Edition (formerly J2ME)
• Scaled down set of Java classes to support Java
  applications on limited-functionality devices
  (e.g. cell phones, PDAs, embedded devices)

13
Java Basics Java Enterprise Edition

• Java EE includes a broad list of components
• enterprise applications e.g. Enterprise
  JavaBeans (EJBs), Servlets, JavaServer Pages
  (JSPs), Java Message Service (JMS) and more
• specification for implementation of enterprise
  components including the deployment environment
  (application server)
• base level classes and a set of APIs that can be
  implemented (e.g. DataSource for DB2)

14
Java Basics Application Types

• Batch/Command (executable)
• Java class with main method
• initiated via javac command (command line)
• requires server with a JRE installed
• Web (executable)
• Servlet Java server application which
  understands HTTP requests web-oriented
  transaction
• JSP JavaServer Page combination of HTML and
  Java code or tags compiles to create Servlet
• requires an Java Enterprise Edition application
  server or web server with servlet engine

15
Java Basics Other Java Classes

• Java Bean (non-executable)
• class that contains attributes/fields and get and
  set methods
• like COBOL copybook for data record
• Other Java classes (non-executable)
• Data Access Objects DAO (SQL)
• Business objects methods with business logic
• EJBs Session, Entity, Message beans
• others design patterns, special use, etc.

16
Java Basics Web Application

• Applications generally launched by a URL
• Enterprise Application maps URL to a servlet
• Servlet invokes business logic (other Java
  classes)
• Business logic invokes DAO class
• DAO class retrieves data from DB2 using database
  API (e.g. JDBC, SQLJ more later)
• Display information is stored in request or
  session
• Servlet redirects to JSP (servlet to build HTML)
• JSP has tags to display data within HTML

17
Java Basics Web Application
App Server
z/OS
JVM .class/.jar
Web Server
DB2
DB2 Connect
18
Java Basics Web Application
Servlet (.class)
JavaServer Page (.jsp)
browser request (URL)
HTML
DB2
Data Access Object (.class)
Business Object (.class)
19
Bullet Points

• Java Basics
• Java Development/Runtime Environment
• Java and DB2
• Security
• Development/Management Issues

20
Java Development

• Developers typically use a development tool (IDE)
• code highlighting, code assist, compiling, etc.
• source code stored in source control repository
• developers synchronize replicate code to
  desktop
• Applications (Java EE) are unit tested on a
  desktop application server
• Server configurations are replicated in developer
  environment
• Development tool simulates server runtime
  environment

21
Java Development (example)
Source Control
IDE
DB2
Local Test Server
DB2 Client
Developer Desktop
22
Java Runtime

• Application components deployed to application
  server
• follows Java EE specification
• manages database connections/drivers/pools
• Application runs within a JVM within the
  application server
• JVM has the following
• JDK version including base Java classes
• specific classpath (concatenation of
  folders/JARs)
• application dependent JARs
• system property settings
• application configuration files
• Configuration changes requires application
  server to be bounced (restarted)

23
Java Runtime Deployment
Enterprise App (.ear)
Deployment Processes
Source Control
Classes (.jar)
Source Control Server
Developer
Application Server
24
Java Runtime - Environment
DB2 Client
Common Classes (.jar)
Application (.ear)
Application (.ear)
DataSource
Configuration (.xml)
DataSource
Application logs
Application Server
25
Bullet Points

• Java Basics
• Java Development/Runtime Environment
• Java and DB2
• Security
• Development/Management Issues

26
DB2 Connection

• Basics
• Driver Types
• Static vs. Dynamic SQL
• Quick example
• Data Persistence
• Desktop vs. Server

27
DB2 Connection Basics

• Java applications connect to DB2 through the
  java.sql.Connection class
• DB2 driver (multiple types) implements the
  generic API of a Connection
• DataSource is configured in application server
  (more under Security)
• Java application uses one or more APIs to build
  SQL and retrieve/update data in DB2
• JDBC dynamic SQL
• SQLJ static SQL

28
DB2 Connection Basics
App (Java)
JDBC/ SQLJ
DB Driver (JAR)
DB2 Connect
DB2 Client
DB2
Application Server (Java EE)
Server
Mainframe
29
DB2 Connection Basics

• There are multiple ways to configure
  communication between open systems server and
  mainframe server
• e.g. DB2 Connect thru DB2 Client
• DB2 can also reside on an open system server (DB2
  UDB)
• Developer desktop may have a different path to
  the DB2 server (e.g. DB2 Client)
• Driver type can vary depending on the connection
  (see Driver Types later).

30
DB2 Connection Basics

• Isolation Level
• Connection class API JDBC
• Java application can set for a transaction
• SET TRANSACTION ISOLATION LEVEL clause - SQLJ
• Configuration of a default isolation for
  DataSource in application server
• NOTE z/OS threads have different DB2 packages
  for each isolation level

31
DB2 Connection Driver Types

• Type 1 driver code maps directly to native code
  of platform (e.g. JDBC ODBC bridge for Windows)
• Type 2 driver made up of part native code, part
  Java
• Type 3 driver Java code uses generic database
  protocol middleware supports database
  connectivity
• Type 4 pure Java driver no middleware

32
DB2 Connection Static vs. Dynamic SQL

• Dynamic
• JDBC API part of Java SE
• Most pervasive
• SQL parsed and optimized at run time
• Statement vs. PreparedStatement (HINT never use
  Statement) to execute SQL
• CallableStatement API for stored procedures
• Table qualifier specified in SQL
• Static
• SQLJ API separate from Java SE
• Less adoption than JDBC
• SQL parsed and optimized at development time

33
DB2 Connection Static vs. Dynamic SQL
Statements

• Dynamic SQL Statement
• SELECT FROM TABLE WHERE COL1 123
• SELECT FROM TABLE WHERE COL1 789
• Different SQL statements!
• Dynamic SQL PreparedStatement
• SELECT FROM TABLE WHERE COL1 ?
• Same for all values of COL1!
• Better performance, helps manage SQL
• So, use PreparedStatement

34
DB2 Connection Quick Example
int myKey 123 Connection conn
MyDataSource.getConnection( ) String sql
SELECT COL_2, COL_3, COL_4, COL_5 FROM
TEST.TABLE WHERE COL1 ? PreparedStatement
stmt conn.prepareStatement(sql) stmt.setInt(myK
ey) ResultSet rs stmt.executeQuery( )
35
DB2 Connection Quick Example

• while (rs.next( ))
• String col2 rs.getString(COL_2")
• Date col3 rs.getDate(COL_3)
• Timestamp col4 rs.getTimestamp(COL_4)
• int col5 rs.getInt(COL_5)

36
DB2 Connection Data Persistence

• There are multiple ways to persist data to and
  retrieve data from the database in Java
• SQL issued by Java
• Data Access Object (DAO)
• SQL is coded by developer
• can use JDBC or SQLJ calls
• Hibernate open source project
• EJB entity beans
• EJB 2.0 vs. EJB 3.0
• Mainframe transactions accessed by Java
• Stored Procedures
• other APIs

37
DB2 Connection Desktop vs. Server

• DB2 drivers DB2 client on developer desktop
• DB2 connection different path than from server
• Security developer requires DB privileges (more
  under Security)
• Server configuration IDE, plug-in, etc.
• configured by developer on desktop
• configured by DBA or server administrator on
  server

38
Bullet Points

• Java Basics
• Java Development/Runtime Environment
• Java and DB2
• Security
• Development/Management Issues

39
Security

• Application vs. database
• Dynamic vs. Static SQL
• Runtime vs. developer
• Web applications

40
Security Application vs. Database

• Java applications typically authenticate to DB2
  using an application-level id
• Users authenticate to application instead of to
  DB2
• Application id is stored in a DataSource or a
  property file (encrypted, secured)
• DB2 privileges are assigned to application id for
  dynamic SQL
• Single application id helps with SQL caching

41
Security Application vs. Database
User1
Id AppUser
User2
App Security
DataSource
Application
DB2
User3
User Directory
Id file
Id password (one location)
42
Security Dynamic vs. Static SQL

• JDBC dynamic SQL
• privileges assigned to application id
• privileges assigned at table level
• SQLJ static SQL
• package created at development time
• privileges assigned at package level to
  application id

43
Security Server vs. Developer

• Developers generally require individual
  privileges for testing of dynamic SQL
• Alternately, secondary authid can be used
• Password for application id is unknown to
  developers (as it should be)
• Developer configures DataSource in local test
  server or local file with personal id and
  password
• Personal id and password sent to DB2

44
Bullet Points

• Java Basics
• Java Development/Runtime Environment
• Java and DB2
• Security
• Development/Management Issues

45
Development/Management Issues

• Performance
• Logging/tracing
• Environment
• Security
• Support

46
Development/Management Issues Performance

• Dynamic SQL
• developers dont always know DB2
• SQL cache use PreparedStatement
• Distributed thread priority on z/OS can be lower
  than native z/OS processes
• Managing/monitoring dynamic SQL
• Generated SQL EJB entity beans, Hibernate
  developers dont always directly control SQL

47
Development/Management Issues Logging/tracing

• Logging/tracing from open server to z/OS
• more levels of logging/tracing
• log4j common debug log method for Java
• JDBC trace in DB2 Client
• Dynamic SQL challenges
• SQL not known until run time
• Dont use Statement use PreparedStatement
• PreparedStatement host variable values not
  displayable in log (Hint implement a
  DebugStatement)

48
Development/Management Issues Environment

• Various servers Java, DB2 Connect, z/OS
• Version levels JDK, DB2 (Client, database),
  driver classes, Java EE, open source
• DB2 connection/settings
• Differences between desktop and server
• Releasing DB2 resources by application close
  connections, statements
• Restarting application server (e.g. changed
  configuration, changed JAR files)

49
Development/Management Issues Security

• Dynamic SQL vs. Static SQL (see Security)
• Multi-tiered security security privileges
  managed at multiple levels (e.g. web layer,
  database layer)
• Developer access vs. server runtime (application
  id)
• DataSource configuration vs. id/password file
• DataSource proliferation of DataSources managed
  by server admins, not Security
• File API to obtain id and password can expose
  to developers

50
Development/Management Issues Support

• Mainframe developers learning Java
• Java developers not knowing about DB2 (can you
  say dynamic SQL?)
• Mainframe DBAs supporting Java developers
• Multi-tiered infrastructure support
• How can I learn?

51
Development/Management How can I learn?

• Sun site
• JDK download
• Examples
• API documentation
• NetBeans or other IDE www.netbeans.org
• IBM site
• DB2 Express or Cloudscape
• Examples
• DataSource

52
Summary

• There is a lot of new terminology to learn
• The runtime environment is very different from
  the mainframe
• There are more components to connect a Java
  application to DB2 on the mainframe
• Database authentication is different for dynamic
  vs. static applications
• There are issues with managing a Java and DB2
  environment, but hopefully

53
Youve learned something!
54
References

• Sun Java website http//java.sun.com
• DB2 Isolation Levels in Java - http//publib.bould
  er.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topi
  c/com.ibm.db2.doc/db2prodhome.htm (search
  isolation level java)
• Dynamic SQL - http//www.redbooks.ibm.com/redbooks
  /pdfs/sg246418.pdf
• SQLJ - http//publib.boulder.ibm.com/infocenter/db
  2help/index.jsp?topic/com.ibm.db2.udb.doc/ad/t000
  7588.htm

55
References

• DB2 Universal Java Driver - http//www-128.ibm.com
  /developerworks/db2/library/techarticle/dm-0512kok
  kat/
• DB2 Explain Tables - http//www.os2ports.com/docs/
  DB2/db2d0/db2d0219.htmHDRAPPEXP (Appendix K)
• DB2 Express - http//www-128.ibm.com/developerwork
  s/kickstart/database.html
• Cloudscape - http//www-128.ibm.com/developerworks
  /db2/zones/cloudscape/

56
References

• Java and DB2 - http//www-128.ibm.com/developerwor
  ks/db2/zones/java/bigpicture.html
• DB2 Connect - http//www-128.ibm.com/developerwork
  s/db2/library/techarticle/dm-0503katsnelson/

57
John Mallonee
Session G09 DB2 Java for Dummies

• Highmark Inc.
• john.mallonee_at_highmark.com