You are previewing DB2 for z/OS and WebSphere Integration for Enterprise Java Applications.
O'Reilly logo
DB2 for z/OS and WebSphere Integration for Enterprise Java Applications

Book Description

IBM DB2® for z/OS® is a high-performance database management system (DBMS) with a strong reputation in traditional high-volume transaction workloads that are based on relational technology. IBM WebSphere® Application Server is web application server software that runs on most platforms with a web server and is used to deploy, integrate, execute, and manage Java Platform, Enterprise Edition applications. In this IBM® Redbooks® publication, we describe the application architecture evolution focusing on the value of having DB2 for z/OS as the data server and IBM z/OS® as the platform for traditional and for modern applications.

This book provides background technical information about DB2 and WebSphere features and demonstrates their applicability presenting a scenario about configuring WebSphere Version 8.5 on z/OS and type 2 and type 4 connectivity (including the XA transaction support) for accessing a DB2 for z/OS database server taking into account high-availability requirements.

We also provide considerations about developing applications, monitoring performance, and documenting issues.

DB2 database administrators, WebSphere specialists, and Java application developers will appreciate the holistic approach of this document.

Please note that the additional material referenced in the text is not available from IBM.

Table of Contents

  1. Front cover
  2. Figures
  3. Tables
  4. Examples
  5. Notices
    1. Trademarks
  6. Preface
    1. Authors
    2. Now you can become a published author, too!
    3. Comments welcome
    4. Stay connected to IBM Redbooks
  7. Chapter 1. Application development with DB2 for z/OS
    1. 1.1 Mainframe and DB2 for z/OS
    2. 1.2 The System z platform
      1. 1.2.1 Using System z technology to reduce complexity
      2. 1.2.2 Business integration and resiliency
      3. 1.2.3 Managing the System z platform to meet business goals
      4. 1.2.4 Security
    3. 1.3 Programming languages
      1. 1.3.1 Language Environment
      2. 1.3.2 Java
      3. 1.3.3 Business application languages
    4. 1.4 Integrated application and database on z/OS
      1. 1.4.1 Data consolidation on the System z platform
      2. 1.4.2 Data consolidation and integration of the applications on z/OS
    5. 1.5 The synergy between z/OS and DB2 for z/OS
      1. 1.5.1 How DB2 for z/OS uses the System z platform
  8. Chapter 2. Accessing DB2 for z/OS from WebSphere applications
    1. 2.1 Application server infrastructure
    2. 2.2 Related products
      1. 2.2.1 WebSphere Application Server Community Edition
      2. 2.2.2 WebSphere eXtreme Scale
      3. 2.2.3 Rational Application Developer for WebSphere Software V8.5
    3. 2.3 Core concepts of WebSphere Application Server
      1. 2.3.1 Applications
      2. 2.3.2 Containers
      3. 2.3.3 Application servers
      4. 2.3.4 Profiles
      5. 2.3.5 Nodes, node agents, and node groups
      6. 2.3.6 Cells
      7. 2.3.7 Deployment manager
    4. 2.4 Server configurations
    5. 2.5 Clusters and high availability
      1. 2.5.1 Vertical cluster
      2. 2.5.2 Horizontal cluster
      3. 2.5.3 Mixed cluster
      4. 2.5.4 Mixed-node versions in a cluster
      5. 2.5.5 Dynamic cluster
      6. 2.5.6 Cluster workload management
    6. 2.6 Database access from WebSphere Application Server
      1. 2.6.1 JDBC driver types
      2. 2.6.2 Concept of JDBC providers
      3. 2.6.3 Concept of data sources
      4. 2.6.4 WebSphere Application Server connection pooling
      5. 2.6.5 WebSphere connection pooling combined with sysplex workload balancing for JDBC type 4 connectivity
      6. 2.6.6 WebSphere Application Server prepared statement cache
      7. 2.6.7 Trusted context support in WebSphere Application Server
      8. 2.6.8 Transaction Isolation Level support in WebSphere Application Server
      9. 2.6.9 Transactions in WebSphere Application Server
    7. 2.7 WebSphere Application Server - DB2 high availability configuration options
      1. 2.7.1 WebSphere Application Server - DB2 for z/OS recommended high availability configuration when using JDBC type 4 connectivity
      2. 2.7.2 WebSphere Application Server - DB2 z/OS recommended high availability configuration when using JDBC type 2 connectivity
  9. Chapter 3. DB2 configuration options for Java client applications
    1. 3.1 The DB2 configuration
      1. 3.1.1 Configuring the TCP/IP network
      2. 3.1.2 Configuring the DB2 subsystems
    2. 3.2 IBM Data Server Drivers and Clients
      1. 3.2.1 Connectivity options for IBM Data Server Driver for JDBC and SQLJ
      2. 3.2.2 Limited block fetch extended to the JCC type 2 drivers
    3. 3.3 High availability configuration options
      1. 3.3.1 How to make your client application sysplex aware
      2. 3.3.2 The difference between connections and transports
      3. 3.3.3 What JCC client properties need to be changed
  10. Chapter 4. DB2 infrastructure setup
    1. 4.1 z/OS related setup
      1. 4.1.1 Parallel Sysplex
      2. 4.1.2 Automatic Restart Manager policy
      3. 4.1.3 WLM configuration
      4. 4.1.4 Resource Recovery Services
      5. 4.1.5 z/OS resource planning
      6. 4.1.6 External storage configuration
      7. 4.1.7 UNIX System Services file system configuration
      8. 4.1.8 Monitoring infrastructure
      9. 4.1.9 WebSphere Application Server and DB2 security
    2. 4.2 Monitoring strategy
    3. 4.3 DB2 for z/OS configuration
      1. 4.3.1 DB2 connectivity installation parameters
      2. 4.3.2 Enabling DB2 dynamic statement cache
      3. 4.3.3 Locking and accounting setup
      4. 4.3.4 Buffer pool configuration
      5. 4.3.5 DB2 for z/OS Distributed Data Facility
      6. 4.3.6 High Performance DBATs
      7. 4.3.7 IBM Data Server Driver for JDBC and SQLJ
      8. 4.3.8 JDBC type 2 DLL and the SDSNLOD2 library
      9. 4.3.9 Bind JDBC packages
      10. 4.3.10 UNIX System Services command line processor configuration
      11. 4.3.11 Using the TestJDBC Java sample
      12. 4.3.12 DB2 security considerations
      13. 4.3.13 Trusted context
      14. 4.3.14 Trusted context application scenarios
      15. 4.3.15 DayTrader-EE6 application using JDBC connections
      16. 4.3.16 Data Web Service servlet with trusted context AUTHID switch
      17. 4.3.17 Using DB2 profiles
      18. 4.3.18 Using profiles to optimize and monitor threads and connections
      19. 4.3.19 Configure thread monitoring for the DayTrader-EE6 application
      20. 4.3.20 Using profiles to keep track of DRDA client levels
      21. 4.3.21 Using profiles to disable idle thread timeout at application level
      22. 4.3.22 Using profiles for remote connection monitoring
      23. 4.3.23 SYSPROC.ADMIN_DS_LIST stored procedure
      24. 4.3.24 DB2 real time statistics
      25. 4.3.25 Using RTS to obtain COPY, REORG and RUNSTATS recommendations
    4. 4.4 Tivoli OMEGAMON XE for DB2 Performance Expert for z/OS
      1. 4.4.1 Extract, transform, and load DB2 accounting FILE and statistics information
      2. 4.4.2 Extract, transform and load DB2 accounting SAVE information
      3. 4.4.3 Querying the performance database tables
      4. 4.4.4 Additional information
    5. 4.5 DB2 database and application design considerations
  11. Chapter 5. WebSphere Application Server infrastructure setup
    1. 5.1 Configuring WebSphere Application Server Network Deployment on z/OS
    2. 5.2 Configuring WebSphere Application Server for JDBC type 4 XA access
      1. 5.2.1 Defining a DB2 JDBC XA provider
      2. 5.2.2 Defining environment variables at the location of the IBM Data Server Driver for JDBC and SQLJ classes for JDBC type 4 connectivity
      3. 5.2.3 Defining a JDBC type 4 XA data source
    3. 5.3 Configuring WebSphere Application Server for JDBC type 2 access
      1. 5.3.1 Defining a DB2 JDBC provider
      2. 5.3.2 Defining environment variables to the location of the IBM Data Server Driver for JDBC and SQLJ classes for JDBC type 2 connectivity
      3. 5.3.3 Defining a JDBC type 2 data source
      4. 5.3.4 Configuring a subsystem ID on the data source
    4. 5.4 Configuring WebSphere Application Server for sysplex workload balancing
    5. 5.5 Configuring client information in WebSphere Application Server
      1. 5.5.1 Setting client information on a data source
      2. 5.5.2 Setting client information by using extended data source properties
      3. 5.5.3 Setting DB2 client information in a WebSphere Java application
    6. 5.6 Configuring the prepared statement cache in WebSphere Application Server
    7. 5.7 Configuring the J2C authentication alias
    8. 5.8 Configuring connection pool sizes on data sources in WebSphere Application Server
    9. 5.9 Enabling trusted context for applications that are deployed in WebSphere Application Server
    10. 5.10 Configuring the JCC properties file in WebSphere Application Server
    11. 5.11 Configuring data source properties (webSphereDefaultlsolationLevel, currentPackagePath, pkList, and keepDynamic)
      1. 5.11.1 websphereDefaultIsolationLevel
      2. 5.11.2 currentPackagePath
      3. 5.11.3 pkList
      4. 5.11.4 keepDynamic
  12. Chapter 6. Developing Java applications with DB2 for z/OS
    1. 6.1 Drivers for Java applications
    2. 6.2 Dynamic SQL
    3. 6.3 Static SQL
    4. 6.4 PureQuery optimization
    5. 6.5 DB2 support for Java stand-alone applications
      1. 6.5.1 Alternatives for setting the JDBC driver parameters
      2. 6.5.2 Java batch considerations with DB2
      3. 6.5.3 Portability
      4. 6.5.4 Sample Java SE stand-alone application with JPA and DB2
    6. 6.6 JDBC applications in managed environments
      1. 6.6.1 Data source connection tests on z/OS
    7. 6.7 Coding practices for a good DB2 dynamic statement cache hit ratio
      1. 6.7.1 Eligible SQL statements for caching
      2. 6.7.2 SQL comments considerations
      3. 6.7.3 Conditions for prepared statement reuse
      4. 6.7.4 Literal replacement
    8. 6.8 Locking
      1. 6.8.1 Isolation level
      2. 6.8.2 Lock avoidance
      3. 6.8.3 Optimistic locking
  13. Chapter 7. Java Platform, Enterprise Edition with WebSphere Application Server and DB2
    1. 7.1 Java Platform, Enterprise Edition with WebSphere Application Server and DB2
    2. 7.2 Implementation version of JPA inside WebSphere Application Server
      1. 7.2.1 The goals of the Java Persistence API
      2. 7.2.2 OpenJPA and JDBC interaction
      3. 7.2.3 Agile JPA development with a WebSphere Application Server embeddable EJB container and DB2
      4. 7.2.4 Use of alternative JPA persistence providers
      5. 7.2.5 Usage of Non-JTA data sources
      6. 7.2.6 Data source resource definition in applications
      7. 7.2.7 Definition of the IBM DB2 Driver in WebSphere Application Server V8.5 Liberty Profile
      8. 7.2.8 LOB streaming
      9. 7.2.9 XML JPA column mapping
    3. 7.3 Preferred practices of Java Platform, Enterprise Edition and DB2
      1. 7.3.1 Using resource references
      2. 7.3.2 Providing a JDBC driver in your application libraries
      3. 7.3.3 Resetting the database for each test run
      4. 7.3.4 Optimizing generated SQL from persistence frameworks
    4. 7.4 Known issues with OpenJPA 2.2 and DB2
  14. Chapter 8. Monitoring WebSphere Application Server applications
    1. 8.1 Performance monitoring
      1. 8.1.1 Continuous monitoring
      2. 8.1.2 Detailed monitoring
    2. 8.2 Correlating performance data from different sources
      1. 8.2.1 Using client information strings for correlating data
      2. 8.2.2 Using client information strings to classify work in WLM and RMF reporting
      3. 8.2.3 Other techniques to segregate/correlate work
    3. 8.3 Monitoring from WebSphere Application Server
      1. 8.3.1 Using SMF 120 records
      2. 8.3.2 WebSphere Application Server Performance Monitoring Infrastructure
    4. 8.4 Monitoring from the DB2 side
      1. 8.4.1 Which information to gather
      2. 8.4.2 Creating DB2 accounting records at a transaction boundary
      3. 8.4.3 DB2 rollup accounting
      4. 8.4.4 Analyzing DB2 statistics data
      5. 8.4.5 Analyzing DB2 accounting data
      6. 8.4.6 Monitoring threads and connections by using profiles
    5. 8.5 Using the performance database
      1. 8.5.1 Querying aggregated JDBC type 2 accounting information
      2. 8.5.2 Querying aggregated JDBC type 4 accounting information
      3. 8.5.3 Using RTS to identify DB2 tables that are involved in DML operations
    6. 8.6 Monitoring from the z/OS side with RMF
      1. 8.6.1 Workload activity when using a type 4 connection
      2. 8.6.2 Workload activity when using a type 2 connection
  15. Chapter 9. Error handling and problem determination
    1. 9.1 Error handling
      1. 9.1.1 Basic error message
      2. 9.1.2 SQLCA formatting
      3. 9.1.3 Multiple SQL error handling
    2. 9.2 Correlating DB2 and WebSphere Application Server information
    3. 9.3 Common tools for problem determination
      1. 9.3.1 Application log
      2. 9.3.2 IBM Data Server Driver for JDBC and SQLJ trace
      3. 9.3.3 DB2 commands
    4. 9.4 Typical problem scenario: Deadlock
      1. 9.4.1 Analyzing the Servant log and DB2 MSTR job log
      2. 9.4.2 Analyzing the deadlock trace record
      3. 9.4.3 Identifying the application and SQL statements
      4. 9.4.4 Getting more information from the record trace
  16. Appendix A. DB2 administrative task scheduler
    1. A.1 Implementation
    2. A.2 Administrative scheduler operation
    3. A.3 Using ADMT for DB2STOP, DB2START, and statistics monitoring
    4. A.4 Additional information
  17. Appendix B. Configuration and workload
    1. B.1 Configurations
    2. B.2 The DayTrader application workload
    3. B.3 Using the DayTrader application
  18. Appendix C. Setting up a WebSphere Application Server test environment on IBM Data Studio
    1. C.1 Installing WebSphere Application Server Developer Tools into IBM Data Studio
  19. Appendix D. IBM OMEGAMON XE for DB2 performance database
    1. D.1 Introduction
    2. D.2 Creating the performance database
    3. D.3 Extracting, transforming, and loading accounting and statistics data
    4. D.4 Sample query for application profiling
    5. D.5 Using the UDF for application profiling
    6. D.6 Additional information
  20. Appendix E. SMF 120 records subtypes 1, 3, 7, and 8
    1. E.1 Server activity record: Subtype 1
    2. E.2 Server interval record: Subtype 3
    3. E.3 WebContainer activity record: Subtype 7
    4. E.4 WebContainer activity record: Subtype 8
  21. Appendix F. Sample IBM Data Server Driver for JDBC and SQLJ trace
  22. Appendix G. External user-defined functions
    1. G.1 UDF GRACFGRP
    2. G.2 UDF BIGINT
  23. Appendix H. ClientInfo dynamic web project
    1. H.1 The ClientInfo dynamic web project
    2. H.2 Accessing the ClientInfo.war file from your workstation
    3. H.3 Installing the ClientInfo web application
    4. H.4 Starting the ClientInfo web application
    5. H.5 Testing the ClientInfo web application
    6. H.6 Testing the ClientInfoJDBC30API servlet
    7. H.7 Testing the ClientInfoJDBC40 servlet
    8. H.8 Testing the ClientInfoWSAPI servlet
    9. H.9 Testing the ClientInfoWLM servlet
  24. Appendix I. Additional material
    1. Locating the web material
    2. Using the web material
  25. Related publications
    1. IBM Redbooks
    2. Other publications
    3. Online resources
    4. Help from IBM
  26. Back cover