...

Performance of environments using DB2 Connect Enterprise Edition

by user

on
Category:

movies and tv

3

views

Report

Comments

Transcript

Performance of environments using DB2 Connect Enterprise Edition
Performance of environments using DB2 Connect Enterprise Edition
February 2008
Performance of environments using
DB2 Connect Enterprise Edition
1
Performance of environments using DB2 Connect Enterprise Edition
Table of Contents
Objective ................................................................................................................................. 3
Executive summary................................................................................................................. 3
Summary................................................................................................................................. 4
Hardware equipment and software environment..................................................................... 6
Host hardware and software ............................................................................................... 6
Network setup ................................................................................................................. 6
Storage server setup....................................................................................................... 6
z/VM Guest Setup........................................................................................................... 7
Client hardware............................................................................................................... 7
Software.............................................................................................................................. 7
Test environment .................................................................................................................... 8
Trade 6................................................................................................................................ 9
Trade 6 topology overview ................................................................................................ 10
IRWW................................................................................................................................ 10
Setup..................................................................................................................................... 11
DB2 Connect - connection pooling and concentrator function .......................................... 12
DB2 Connect parameters ............................................................................................. 12
DB2 for z/OS (DSNZPARM parameter) ........................................................................ 13
Enable the DB2 Connect connection concentrator function.......................................... 14
Workload management policy for z/OS............................................................................. 15
WebSphere Application Server setup ............................................................................... 15
Connection monitoring and CPU utilization charts explained................................................ 16
Monitor active connections................................................................................................ 16
CPU utilization charts explained ....................................................................................... 17
Results .................................................................................................................................. 18
Considerations for all scenarios ........................................................................................ 18
Parameters common for all test cases .............................................................................. 18
Trade 6 results .................................................................................................................. 18
Scaling DB2 Connect memory .......................................................................................... 19
Scaling DB2 Connect virtual CPUs ................................................................................... 20
Scaling DB2 Connect parameter MAXAGENTS ............................................................... 21
Compare DB2 Connect to non-DB2 Connect.................................................................... 23
IRWW results ........................................................................................................................ 24
Scaling DB2 Connect memory .......................................................................................... 25
Scaling DB2 Connect CPUs.............................................................................................. 26
Scaling DB2 Connect MAXAGENTS ................................................................................ 27
Comparison of SQLJ versus JDBC................................................................................... 29
Compare DB2 Connect to non-DB2 Connect.................................................................... 30
Appendix A. Detailed set up examples.................................................................................. 31
DB2 Connect configuration commands for Trade 6 .......................................................... 31
Buffer pools used with the Trade 6 database on z/OS...................................................... 33
DB2 Connect configuration commands for IRWW ............................................................ 33
Buffer pools used with the IRWW database on z/OS........................................................ 34
Appendix B. Other Sources of Information............................................................................ 34
2
Performance of environments using DB2 Connect Enterprise Edition
1
Objective
DB2 Connect™ Enterprise Edition V9.1 for Linux® on zSeries® (DB2 Connect)
connects LAN-based systems and their applications to the company's mainframe
host DB2® databases. Additionally, DB2 Connect can be used in a three tier
environment and can act as a gateway concentrating large numbers of SQL
connections from various clients to a fewer, well-defined number of connections
to the DB2 database on z/OS®.
The focus of this report was on the connection concentrator functions of DB2
Connect. This feature enables a predictable, controlled load on the database on
z/OS from a DB2 Connect server and may reduce mainframe resource usage as
fewer DB2 threads may be defined. The capacity of active connections from the
DB2 Connect gateway to the z/OS DB2 database was dependent on the
resources available on the DB2 Connect gateway. The scaling behavior from
DB2 Connect for the number of CPUs, memory, and various connection
concentration levels were examined.
DB2 Connect was hosted on Linux for System z™, which was implemented
together with the WebSphere® Application Server environment as z/VM® guests.
This setup provided an integrated solution with DB2 Connect providing gateway
and connection concentration for a three tier environment with the Trade 6
workload and gateway and connection concentration in a two tier environment
with the IBM Relational Warehouse Workload (IRWW). The two application
workloads, Trade 6 and IRWW, with their different architectures were tested to
examine how application implementation characteristics might affect settings for
optimal performance.
The objectives of this project were to analyze the performance of DB2 Connect
environments. The information in this paper should not be used to develop
capacity sizing rules-of-thumb for customer environments. For sizing and
capacity planning, contact our specialists from the IBM System z sizing team.
Executive summary
This paper shows the results from our performance measurements with
environments using DB2 Connect Enterprise Edition. For our workload, a DB2
Connect system with 1 GB of main memory and up to 2 virtual CPUs was
suitable for all cases. The use of the connection concentrator function of DB2
Connect was a suitable tool to control the load on the DB2 database, but it
requires specific prerequisites on DB2 and WebSphere Application Server
1
This paper is intended to provide information regarding performance of environments using DB2 Connect
Enterprise Edition. It discusses findings based on configurations that were created and tested under
laboratory conditions. These findings may not be realized in all customer environments, and
implementation in such environments may require additional steps, configurations, and performance
analysis. The information herein is provided “AS IS“ with no warranties, express or implied. This
information does not constitute a specification or form part of the warranty for any IBM products.
3
Performance of environments using DB2 Connect Enterprise Edition
settings as well as the characteristics of the workload itself. The impact of DB2
Connect on the throughput in the three tier environment was very small. It was
higher in the two tier environment. Additionally, we identified the Workload
Manager (WLM) settings on z/OS as an important parameter for database
performance.
Summary
This chapter provides a short summary of our test results. Our test results and
recommendations are specific to our environment. Parameters useful in our
environment might be useful in other environments, but are dependent on
application usage and system configuration. You will need to determine what
works best for your environment. For our detailed test results information, see
Results.
The following are our summary results:
General
ƒ We identified the WLM settings on z/OS as an important parameter for
database performance.
ƒ Limiting the number of connections to DB2 on z/OS can be used to limit
the load created from this source on DB2. This protects the database
against load peaks from the workload and improves mainframe
resource utilization.
ƒ The DB2 Connect server may sustain continuous 100% CPU utilization
with improving throughput even while its connections to z/OS DB2 grow
as much as 12X.
Connection concentration
We identified several prerequisites for the mode where connections were
concentrated on the DB2 Connect system.
ƒ On the DB2 Connect system, the parameter, MAX_CONNECTIONS,
must be at least 1 greater than MAXAGENTS.
ƒ On the z/OS system the DSNZPARM parameter, MAXDBATS, must be
equal to or greater than MAXAGENTS.
ƒ On the WebSphere Application Server level, the parameter,
resultSetHoldability, must be set to 2.
ƒ The application must have only a small amount of long running
transactions (ideally none) and all cursors must be closed after commit.
Trade workload
ƒ A DB2 Connect guest with 512 MB of main memory started swapping
slightly with 400 Trade users. Our maximum number of Trade users of
4
Performance of environments using DB2 Connect Enterprise Edition
700 was well supported with a DB2 Connect system with 1 GB of
memory. An additional 1 GB of main memory provided no advantage. It
seems that about 512 MB of main memory is needed for each 400 users.
We did all further tests with 1 GB of main memory for the DB2 Connect
system.
ƒ The DB2 Connect system with a single CPU was overloaded with 400
concurrent users with this workload. It is recommended that DB2
Connect be run with a minimum of two CPUs when a high volume of
concurrent active clients are likely.
ƒ When controlling the load on the DB2 on z/OS created from 600 Trade
users by scaling the numbers of active connections via DB2 Connect
MAXAGENTS from 50 to 600, a factor of 12X, the load on the DB2 on
z/OS only varied by a factor of 2x. This shows that DB2 Connect was a
suitable tool to control the load on the DB2 database.
ƒ When the Trade 6 transactions are routed though DB2 Connect,
throughput degrades up to 8%. This may be considered a small impact
to achieve predictable, efficient utilization of mainframe DB2 resources
while handling widely varying client access volumes.
IRWW workload
ƒ DB2 Connect memory size does not affect IRWW throughput in these
scenarios. A memory size of 1 GB was selected for the remainder of the
IRWW test cases.
ƒ The IRWW workload throughput is not affected by the number of CPUs.
A single CPU for the DB2 Connect system would be sufficient for this
type of workload, but for a better comparison with the Trade 6 results,
we decided to use two CPUs on the DB2 Connect system for further
runs.
ƒ For the think time values used, a MAXAGENTS value of 100 was
sufficient for this workload. This demonstrates that limiting connections
to DB2 on z/OS can be done without a degradation of the throughput,
up to a certain load level.
ƒ We selected the SQLJ workload driver because, in our environment, it
produces slightly higher throughput at lower CPU utilization than the
JDBC driver.
ƒ The IRWW workload creates a significant lower load on the system than
the Trade workload. DB2 Connect has here a higher impact on
throughput than in the Trade 6 workload case. Bypassing DB2 Connect
resulted in an improvement between 6% and 30%. It seems that
WebSphere balances the request rate in a way that the pressure on
DB2 Connect is lower.
5
Performance of environments using DB2 Connect Enterprise Edition
Hardware equipment and software environment
This chapter provides details on the hardware and software used in our testing.
Topics include:
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
Host and client hardware used
Host and client software used
Network setup
Storage server setup
z/VM guest setup
The test environment
Workload description
Host hardware and software
The following section lists the hardware, software, network, and configurations
we used for the environment on System z.
Hardware
Two LPARs on a 16-way IBM System z9®, type 2094-S18, equipped with:
Table 1. System memory and CPUs
System/Guest
Memory
Dedicated CPUs
z/VM
16384 MB (4096 MB expanded)
5 or 6
z/OS
8192 MB
4-7
Additionally, the z/VM LPAR used an OSA-Express2 gigabit Ethernet card (OSA
code level 0805)
Network setup
Our network setup consisted of the following:
ƒ The clients were connected to the z/VM LPAR via Fiber Gigabit Ethernet
Interface
ƒ The z/VM guests used virtual guest LANs
ƒ The z/VM LPAR and the z/OS LPAR were connected via a
HiperSockets™ connection
Storage server setup
Enterprise Storage Server® (ESS) 2105 800
ƒ IBM 3390 disk model 3
ƒ Physical DDMs with 15,000 RPMs
6
Performance of environments using DB2 Connect Enterprise Edition
z/VM Guest Setup
Table 2 shows the z/VM guest configuration we used for our tests. We used this
configuration for our tests using the Trade 6 workload. For tests using the IRWW
workload the WebSphere Application Server guests were not required.
Table 2. Test system configuration: WebSphere Application Server
System/Guest
Memory
DB2 Connect
512 MB (1024 MB) (2048 MB)
# of Virtual
CPUs
1 (2)
WebSphere Application Server (1) 2048 MB
2
WebSphere Application Server (2) 2048 MB
2
WebSphere Application Server (3) 2048 MB
2
WebSphere Application Server (4) 2048 MB
2
Client hardware
ƒ 1 x330 PC with 2 processors, 1.26 GHz
Software
Table 3. Host and client software used
Product
Version/Level
WebSphere Application Server
6.1.0.3, Build Number: cf30646.29
DB2 Connect Enterprise Edition for Linux 9.1
on zSeries
Red Hat Enterprise Linux
RHEL 4 ES (client)
WebSphere Studio Workload Simulator
iwl-0-03309L
SUSE Linux Enterprise Server
SLES9 SP3, kernel level 2.65-7.244s390x (all z/VM guests)
DB2 for z/OS
9.1
z/OS
1.8
z/VM
5.2
7
Performance of environments using DB2 Connect Enterprise Edition
Test environment
Figure 1 shows the setup of the Trade 6 DB2 Connect environment
Figure 1. Trade 6 - DB2 Connect test environment
Figure 2 shows the setup of the IRWW DB2 Connect environment.
Figure 2. IRWW-DB2 Connect test environment
8
Performance of environments using DB2 Connect Enterprise Edition
The test environment consisted of an IBM System z server and an IBM System
x™ server. The System z contained a z/VM LPAR with five guests and a z/OS
LPAR. The network was split as follows:
ƒ The System x and the z/VM LPAR on the System z were connected via a
1 GB Ethernet connection. The System x contained the Trade 6 or IRWW
workload generator, which generated the workload.
ƒ The network on z/VM was implemented as a guest LAN type of
HiperSockets, which was used by all guests.
ƒ The z/OS LPAR was connected to the z/VM LPAR through a
HiperSockets connection and contained the DB2 for z/OS server.
Workload description
Trade 6
The IBM Trade Performance Benchmark Sample for WebSphere Application
Server (otherwise known as Trade 6) is the fourth generation of the WebSphere
end-to-end benchmark and performance sample application. The Trade
benchmark is designed and developed to cover the significantly expanding
programming model and performance technologies associated with WebSphere
Application Server. This application provides a real-world workload, enabling
performance research and verification tests of the Java™ 2 Platform, Enterprise
Edition (J2EE) 1.4 implementation in WebSphere Application Server, including
key performance components and features.
Overall, the Trade application is primarily used for performance research on a
wide range of software components and platforms. The latest revision of Trade
builds off of Trade 3, by moving from the J2EE 1.3 programming model to the
J2EE 1.4 model that is supported by WebSphere Application Server V6.0. Trade
6 adds DistributedMap based data caching in addition to the command bean
caching that is used in Trade 3. Otherwise, the implementation and workflow of
the Trade application remains unchanged.
Trade's new design enables performance research on J2EE 1.4 including the new
Enterprise JavaBeans™ (EJB™) 2.1 component architecture, message-driven
beans, transactions (1-phase, 2-phase commit) and Web services (SOAP, WSDL,
JAX-RPC, enterprise Web services). Trade 6 also drives key WebSphere
Application Server performance components such as dynamic caching,
WebSphere Edge Server, and EJB caching.
9
Performance of environments using DB2 Connect Enterprise Edition
Figure 3 shows the J2EE components that make up the Trade application.
Trade 6 topology overview
Trade provides two server implementations of the emulated Trade brokerage
services.
ƒ EJB - Database access uses EJB 2.1 technology to drive transactional
trading operations.
ƒ Direct - Uses database and messaging access through direct JDBC
and JMS code.
The J2EE programming model provides managed, object-based EJB components.
The EJB container provides declarative services for these components such as
persistence, transactions, and security. The J2EE programming model also
supports low-level APIs such as JDBC and JMS. These APIs provide direct
access to resource managers such as database and message servers.
For this test environment we used only the Direct mode.
For the tests described in this paper the Trade 6 database was populated with
4000 user IDs and 2000 stock quotes.
IRWW
IRWW is an OLTP workload that consists of seven transactions. Each transaction
consists of one to many SQL statements, each performing a distinct business
function in a predefined mix.
10
Performance of environments using DB2 Connect Enterprise Edition
The seven transaction types, a brief description of each, and the percentage of
the transaction mix follows:
Neworder
Performs various SELECTS, FETCHES, UPDATES, and INSERTS in support of
the receipt of new customer orders and runs as 22% of the total transaction mix.
Order Status
Performs various SELECTS and FETCHES in support of providing the status of
an order and runs as 24% of the total transaction mix.
Payment
Performs SELECTS, FETCHES, UPDATES, and INSERTS in support of
received customer payments and runs at 22% of the total transaction mix.
Price Change
Performs an UPDATE in support of changing the price of an item and runs as
1% of the total transaction mix.
Price Quote
Performs various SELECTS in support of providing the price of a set of items
and runs as 25% of the total transaction mix.
Stock Level
Performs a JOIN and various SELECTS in support of providing the current stock
level of an item and runs at 4% of the mix.
Delivery
Performs various SELECTS, UPDATES, and DELETES in support of the
delivery of a group of orders and runs as 2% of the total transaction mix.
The IRWW database that we built contained 8 inventory stock warehouses and
10 sales districts
Setup
This chapter includes the following setup topics:
ƒ DB2 Connect connection pooling and concentrator function
ƒ Workload management policy for z/OS
ƒ WebSphere Application Server setup
11
Performance of environments using DB2 Connect Enterprise Edition
DB2 Connect - connection pooling and concentrator function
The following is a short description of the DB2 Connect parameters that are
configurable and relate to connection pooling and concentration.
The parameters described in this section are configuration parameters and are
specified using the following command:
db2 update dbm cfg using <parameter> <value>
Connection Pooling
Connection Pooling is a simple technique that allows reuse of an established
connection infrastructure for subsequent connections and handles connection
volume and helps to reduce the overhead of database connections. It avoids the
overhead of opening and closing connections by holding the connections active
in a pool. You can configure the pool size using the NUM_POOLAGENTS
configuration parameter.
Connection Concentrator
The Connection Concentrator enables a fewer number of threads in z/OS DB2
and thus lower associated hardware usage by DB2 for z/OS to service the client
requests created from that DB2 Connect server (see also
http://www.ibm.com/developerworks/db2/library/techarticle/dm0503katsnelson/index.html ). A higher number of clients are connected to the
DB2 Connect server, which is really connected to the database, rather than the
clients connected directly to z/OS DB2. It introduces the concept of agents as
follows:
ƒ Logical Agent (LA) - handles user context
ƒ Coordinating Agent (CA) - owns DB2 connections and processes
Any time an application user connects, DB2 Connect assigns a logical agent. A
coordinating agent is needed to pass SQL to z/OS DB2, so one is assigned as
soon as a transaction is initiated. The coordinating agent is disassociated from
the logical agent and is returned to the pool when the transaction is finished.
The term logical agent was created to try to explain the decoupling of the
application state from agents that service the work when connection
concentration is enabled. A logical agent is just a concept. There is no agent
involved. A logical agent represents the state information associated with the
application connection, which is then tied to the agent processing the transaction.
DB2 Connect parameters
MAXAGENTS - The maximum number of DB2 Connect clients that can
concurrently execute work against DB2 for z/OS is defined by the MAXAGENTS
value. This value should be less than or equal to the MAXDBATS value on z/OS.
12
Performance of environments using DB2 Connect Enterprise Edition
Coordinating Agents - The configuration parameter MAX_COORDAGENTS
limits the number of coordinator agents. A co-coordinator agent (COORD agent)
is the agent that handles the connections to the database. The setting of
MAXAGENTS controls the total number of COORD + sub-agents (and a few
other types of agents) that can actually be spawned by the DB2 Connect instance.
However, because a pure DB2 Connect instance will not have any sub-agents,
MAX_COORDAGENTS and MAXAGENTS are the same thing. In other words,
even if MAXAGENTS is set higher than MAX_COORDAGENTS, in a pure DB2
Connect server environment, the number of agents used will never exceed the
MAX_COORDAGENTS.
For each run we set MAX_COORDAGENTS to be the same as MAXAGENTS.
MAX_CONNECTIONS - The configuration parameter, MAX_CONNECTIONS,
controls the number of connections allowed to the DB2 Connect server from the
client side. When MAX_CONNECTIONS is larger than MAXAGENTS, the
system runs in connection concentrator mode.
NUM_POOLAGENTS - The configuration parameter, NUM_POOLAGENTS,
indicates the number of agents that, when not assigned work, will be kept active
(pooled).
For each run we set NUM_POOLAGENTS to be the same as MAXAGENTS.
NUM_INITAGENTS - The configuration parameter, NUM_INITAGENTS,
indicates the number of idle agents spawned at db2start. These agents are like
any agent and can become COORD, sub-agents, etc. as required.
Because NUM_INITAGENTS just primes the agent pool with idle agents, its
value should not exceed NUM_POOLAGENTS.
The DB2 command db2 get dbm cfg shows the default settings for DB2 after
installation of DB2 Connect for the above parameters. An example is shown
below.
Max number of existing agents
(MAXAGENTS) = 200
Agent pool size
(NUM_POOLAGENTS) =
100(calculated)
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) =
MAXAGENTS
Max no. of concurrent coordinating agents (MAXCAGENTS)
= MAX_COORDAGENTS
Max number of client connections
(MAX_CONNECTIONS)
= MAX_COORDAGENTS
DB2 for z/OS (DSNZPARM parameter)
On our DB2 for z/OS system we used the DSNZPARM parameter MAXDBATS.
13
Performance of environments using DB2 Connect Enterprise Edition
MAXDBATS - The maximum number of database access agents from the DB2
connection pool. You can do a display command on DB2 for z/OS to see how
many connections are concurrently active.
The z/OS for DB2 command to display this information is:
-dis ddf detail
See Monitor active connections for more details on this output.
From the results of this command, you can decide if MAXDBATS and/or
MAXAGENTS need to be increased. MAXDBATS should be equal to or greater
than MAXAGENTS. For instance, if MAXAGENTS is 100 and MAXDBATS is
200 and you see 100 active connections from the display command, you will
know the DB2 Connect connection concentration pool has limited the number
of connections, while the DB2 connection pool can service 100 more
connections from other sources.
Enable the DB2 Connect connection concentrator function
This section describes the requirements to enable the DB2 Connect connection
concentrator on the DB2 Connect system.
On the DB2 Connect system, the following must be done:
ƒ MAX_CONNECTIONS must be at least 1 greater than MAXAGENTS
(Number of logical agents greater than the number of coordinating
agents).
ƒ MAXDBATS on the z/OS system must be equal to or greater than
MAXAGENTS.
ƒ MAX_COORDAGENTS controls the number of inbound connections
active at any time. (Equals the number of logical agents.) Relation:
MAXAGENTS < MAX_CONNECTIONS < 64,000
On the WebSphere Application Server level, the following must be done:
ƒ Set the parameter resultSetHoldability=2. For more information see
WebSphere Application Server setup.
On the Application level, the following must be done:
DB2 Connect can dispatch agents to another client connection only after a
commit and when all SQL cursors are closed. This requires that the application:
ƒ has only a small amount of long running transactions (ideally none)
ƒ closes all cursors after commit
14
Performance of environments using DB2 Connect Enterprise Edition
Workload management policy for z/OS
It is important to set the proper workload management policy for DB2's
Distributed Data Facility (DDF). The default service classification for DDF is
discretionary. This service class is the second lowest available and means that
once the system becomes busy, all distributed DDF work will run at a very low
priority.
The following service classes were defined for DDF and to the DB2 address
space started tasks:
SYSSTC
Built in service class. Used for DB91IRLM. High priority service class. Only
'SYSTEM' service class is higher.
DB2ADDRS
Service class for DB91MSTR, DB91DBM1, and DB91DIST. Uses importance=1,
velocity=80. Slightly lower than the IRLM address space.
DDFWORK
Service class for DDF. Uses importance=2, velocity=80. Slightly lower priority
than the DB2 address spaces.
For information about Workload Management and defining goals through the
service definition see z/OS MVS™ Planning: Workload Management.
For general information about DB2 for z/OS see DB2 for z/OS Performance
Monitoring and Tuning Guide.
WebSphere Application Server setup
The data source definition in WebSphere for the Trade 6 database needs the
following changes to the connection pool:
ƒ Create a new attribute in the connection pool properties as
resultSetHoldability and set the value to 2.
This setting controls the cursor behavior when committing a transaction. The
possible values are:
ƒ 1 = Hold cursors at commit
ƒ 2 = Close cursors at commit
15
Performance of environments using DB2 Connect Enterprise Edition
Connection monitoring and CPU utilization charts explained
This chapter contains information on how we monitored our active connections
as well as a sample CPU utilization chart.
Monitor active connections
The following information describes how we monitored the connections.
WebSphere - In the administrative console, we clicked Monitoring and Tuning >
Performance Viewer > Current activity. We then checked the following fields:
ƒ Connection pools - The connection pool summary lists all data source
connections that are defined in the application server and shows their
usage over time.
ƒ Thread Pools - The thread pool summary shows the usage of all thread
pools in the application server over time.
DB2 Connect - On the DB2 Connect server guest we ran a script that counted
the number of database agent processes running every 30 seconds during the test.
z/OS - The z/OS for DB2 command to display the active connection is -dis
ddf detail. An example of the output produced by this command is shown
below.
Note:
Bold is added for emphasis only.
DSNL080I -DB91 DSNLTDDF DISPLAY DDF REPORT FOLLOWS:
DSNL081I STATUS=STARTD
DSNL082I LOCATION
LUNAME
GENERICLU
DSNL083I DB91ZOS
USIBMT6.DB91ZOS
-NONE
DSNL084I TCPPORT=446
SECPORT=0
RESPORT=447
IPNAME=-NONE
DSNL085I IPADDR=::9.12.22.95
DSNL086I SQL
DOMAIN=lndia3.pdl.pok.ibm.com
DSNL086I RESYNC DOMAIN=lndia3.pdl.pok.ibm.com
DSNL090I DT=I CONDBAT= 10000 MDBAT= 1000
DSNL092I ADBAT= 198 QUEDBAT= 0 INADBAT= 0 CONQUED= 0
DSNL093I DSCDBAT=
85 INACONN= 320
DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE
The following fields are associated with DDF threads and connections:
condbat
Maximum number of inbound connections as determined by the "MAX
REMOTE CONNECTED" value in the DSNTIPE installation panel. This value
must be greater or equal to the total number of connections expected to be open
at the same time, especially as the value of mdbat.
16
Performance of environments using DB2 Connect Enterprise Edition
mdbat
Maximum number of database access threads as determined by the "MAX
REMOTE ACTIVE" value in the DSNTIPE installation panel. This effectively
determines the maximum number of active slots. That is, the maximum number
of concurrent active database access threads that could potentially be executing
SQL.
adbat
Current number of active database access threads. This value increases as new
database access threads get created or become active. The value decreases as
database access threads terminate or become inactive. When this value reaches
or exceeds the mdbat value that is indicated in the DSNL090I message, new or
inactive database access threads (DBATs), or new or inactive connections must
be queued, which leads to an increasing response time. When this value exceeds
the condbat value, new connection requests are rejected.
CPU utilization charts explained
In the charts in this report, the CPU utilization values are always normalized in a
way that 100% means one CPU is fully utilized.
Figure 4 is an example of a CPU utilization chart for a z/OS LPAR and a z/VM
LPAR in relation to z/VM guest CPU utilization
Figure 4. Sample CPU utilization chart
The CPU utilization charts show the utilization for each LPAR (z/VM or z/OS)
by a line with triangle symbols. The details of how the z/VM guests use the CPUs
are shown with stacked bars, which should sum up to the z/VM utilization
without the CP related CPU part, so it is always a little lower than the z/VM
17
Performance of environments using DB2 Connect Enterprise Edition
LPAR utilization. If the CPU utilization from a guest in the legend is not visible,
that means it is too low to display.
Results
This chapter provides our detailed test results and conclusions.
Considerations for all scenarios
You can relate for all workloads one workload user with an active network
connection path from the client via WebSphere, DB2 Connect, to the DB2 on
z/OS. The exception is, when DB2 Connect runs in the connection
concentration mode, in that case the number of connections between DB2
Connect and DB2 are fewer than the number of clients connected to DB2
Connect which means users which do not get an connection to the database are
queued within DB2 Connect.
Parameters common for all test cases
The DB2 Connect configuration parameters were set as follows, unless otherwise
specified:
ƒ NUM_POOLAGENTS = MAX_COORDAGENTS = MAXCAGENTS
ƒ MAXCONNECTIONS = 1000
The DB2 for z/OS DSNZPARM parameter MAXDBATS was set to 1000.
Note:
All guest CPUs described in the following tables are virtual CPUs. The CPUs
assigned to the z/VM and z/OS LPARs are physical CPUs.
Trade 6 results
The following configuration was used for all Trade 6 measurements.
Table 4. Trade 6 measurements configuration
WebSphere WebSphere WebSphere
WebSphere
Application
Application
Application
Application
Server 1 z/VM Server 2 z/VM Server 3 z/VM Server 4 Guest
Guest
Guest
Guest
Memory CPUs Memory CPUs Memory CPUs Memory CPUs
2 GB
2
2 GB
2
2 GB
2
2 GB
2
LPARs
z/VM
z/OS
CPUs
CPUs
5
6 or 7
The WebSphere Application Server configuration parameters for the Trade 6
data source connection pool were configured with 10/100 (min/max) connection
entries and the Web Connection Pool Thread size was set to 50/200 (min/max).
18
Performance of environments using DB2 Connect Enterprise Edition
In all tests, the Trade 6 brokerage services were configured to use the "direct"
method and the number of user IDs was set to 4000.
The number of stock quotes was set to 2000.
The Trade 6 workload driver (WebSphere Studio Workload Simulator) was
configured for a think time of 250 milliseconds and distributed all transaction
requests in a round robin fashion to each of the WebSphere Application Servers.
For example, if 100 users were specified, each WebSphere server was assigned
25 users.
Scaling DB2 Connect memory
In these tests, the DB2 Connect z/VM guest used two virtual CPUs and 512 MB,
1 GB, and 2 GB of memory, depending on the run. The Trade 6 workload driver
users were scaled from 50 to 600. The z/VM LPAR was assigned five CPUs and
the z/OS LPAR was assigned six CPUs. The parameter, MAXAGENTS, was set
high enough that the number of active connections was not limited.
Figure 5 shows throughput when scaling DB2 Connect z/VM guest memory from
512 MB to 2 GB and Trade users from 400 to 600.
Figure 5. Trade 6 memory scaling throughput
19
Performance of environments using DB2 Connect Enterprise Edition
Observations
At 400 users, the throughput for the three memory configuration scenarios was
almost identical. For the 1 GB and 2 GB memory scenarios, the throughput is
also almost identical. Using more than 400 Trade users in the 512 MB scenario
causes significant swapping on Linux.
Conclusion
We did not exceed 400 users at 512 MB of main memory on the DB2 Connect
system. Although a relatively small amount of swapping occurs at 400 users,
swapping increases above 400 users so we did not want go above that number of
users.
It is recommended that if MAXAGENTS is set to 400 or greater, DB2 Connect
should be run with 1 GB of memory for active connections when running
applications similar to Trade 6. There is nearly no advantage to 2 GB of main
memory for our test scenarios. Therefore, we used 1 GB of main memory on the
DB2 Connect system for all further runs.
Scaling DB2 Connect virtual CPUs
In these tests, one or two virtual CPUs were defined for the DB2 Connect z/VM
guest and 1 GB of memory was used. The number of Trade 6 users was scaled
from 50 to 600. The z/VM LPAR was assigned five CPUs and the z/OS LPAR
was assigned six CPUs. The parameter, MAXAGENTS, was set high enough that
the number of active connections was not limited.
Figure 6 shows throughput for DB2 Connect z/VM guests with one and two
virtual CPUs, scaling Trade users from 400 to 600.
Figure 6. Trade 6 workload - CPU scaling throughput
20
Performance of environments using DB2 Connect Enterprise Edition
Figure 7 shows CPU utilization for DB2 Connect z/VM guests with one and two
virtual CPUs when the Trade users were scaled from 400 - 600.
Figure 7. Trade 6 CPU scaling
Observations
With our workload, a DB2 Connect server with one virtual CPU was fully utilized
quickly, which limited the throughput. Throughput in the one CPU scenario
reaches its peak at 400 users and decreases beyond that. When DB2 Connect has
more than one CPU, throughput improves over the one CPU case by a factor of
1.35 with 600 users.
Conclusion
DB2 Connect with a single virtual CPU is mostly overloaded with this high
number of users and this workload. It works more effectively in a multi-CPU
environment. It is recommended that DB2 Connect be run with a minimum of
two virtual CPUs to achieve additional throughput.
Scaling DB2 Connect parameter MAXAGENTS
In this test, one virtual CPU was defined for the DB2 Connect z/VM guest and 1
GB memory was used. A constant load of 600 Trade 6 users was used. The
parameter, MAXAGENTS, was scaled from 50 to 600 to scale the number of
active connections between DB2 Connect and z/OS DB2. User connection
requests which did not get one of the active connections were queued within
DB2 Connect. We used only one virtual CPU for the DB2 Connect because this
is a scenario which creates a more moderate load.
The z/VM LPAR was assigned five CPUs and the z/OS LPAR was assigned six
CPUs.
21
Performance of environments using DB2 Connect Enterprise Edition
Figure 8 shows throughput for a DB2 Connect z/VM guest with one virtual CPU,
scaling MAXGENTS from 50 to 600.
Figure 8. Trade 6 MAXAGENTS scaling throughput
Figure 9 shows CPU utilization for DB2 Connect z/VM guests with one and two
virtual CPUs, scaling MAXAGENTS from 50 to 600
22
Performance of environments using DB2 Connect Enterprise Edition
Figure 9. Trade 6 workload - MAXAGENTS scaling
Observations
The DB2 Connect CPU utilization is near 100% in all cases. Between 50 - 200
MAXAGENTS, throughput is flat. With a MAXAGENTS value of 300 or more,
the throughput increases according to the increase in active connections and
then flattens out when it reaches the case where the connections are no longer
concentrated. The CPU load on z/OS behaves in the same manner.
Throughput at 400 MAXAGENTS is 65% higher than throughput at 200
MAXAGENTS, even though DB2 Connect is running at almost 100% of
available CPU.
Conclusion
The DB2 Connect connection concentration is a suitable feature to control the
load on the database on z/OS. Of course, when it limits the number of
connections, this prevents a further increase of the throughput, once the capacity
of a single connection is exceeded. In this case a MAXAGENTS value of 300 will
limit the CPU load on DB2 to a maximum of half of the 6 CPUs from that source.
Compare DB2 Connect to non-DB2 Connect
In these tests, the Trade 6 workload driver sent all transaction requests directly
to DB2 on z/OS, bypassing DB2 Connect. This set of results was compared to the
scenario of DB2 Connect in a z/VM guest (Figure 6) using two virtual CPUs and
1 GB memory. The number of Trade 6 users was scaled from 500 to 700. The
parameter, MAXAGENTS, was set high enough that the number of active
connections was not limited. The z/VM LPAR was assigned five CPUs and for
this test we assigned seven CPUs to the z/OS LPAR to support 700 Trade users.
23
Performance of environments using DB2 Connect Enterprise Edition
Figure 10 shows throughput using DB2 Connect and not using DB2 Connect
and scaling users from 500 to 700.
Figure 10. Trade 6 workload - DB2 Connect versus non-DB2 Connect
Observations
Throughput increases for the bypass case, which is as expected. The impact of
DB2 Connect is 6% to 8%, even with 700 users.
Conclusion
When the Trade 6 transactions are routed though DB2 Connect, throughput
degrades up to 8%. This may be considered a very small impact to obtain
predictable thread resource utilization on z/OS DB2.
IRWW results
The IRWW workload driver was the SQLJ version, unless otherwise specified.
The number of IRWW workload threads was set to 500, unless otherwise
specified.
24
Performance of environments using DB2 Connect Enterprise Edition
Scaling DB2 Connect memory
In these tests, the DB2 Connect z/VM guest used two virtual CPUs and 512 MB,
1 GB, and 2 GB of memory, depending on the run. The number of user threads
was held constant at 500. The IRWW workload driver's think time value was
varied from 2000 milliseconds to 5000 milliseconds to vary the workload. The
z/VM LPAR was assigned six CPUs and the z/OS LPAR was assigned four CPUs.
The parameter, MAXAGENTS, was set high enough that the number of active
connections was not limited.
Figure 11 shows throughput for scaling memory in a DB2 Connect z/VM guest
from 512 MB to 2 GB and scaling think times from 2000 to 5000 milliseconds.
Figure 11. IRWW memory scaling throughput
Observations
At a think time of 2000 milliseconds there is a slight throughput advantage for
512 MBs. At higher think times there is little throughput difference between the
various memory sizes.
Conclusion
DB2 Connect memory size does not affect IRWW throughput in these scenarios.
A memory size of 1 GB was selected for the remainder of the IRWW test cases.
25
Performance of environments using DB2 Connect Enterprise Edition
Scaling DB2 Connect CPUs
In these tests, the number of virtual CPUs defined for the DB2 Connect z/VM
guest was varied from one to two. The memory size was 1 GB. The think time
value for the IRWW workload driver was varied from 2000 milliseconds to 5000
milliseconds and the number of user threads was held constant at 500. The
z/VM LPAR was assigned six CPUs and the z/OS LPAR was assigned four CPUs.
The parameter, MAXAGENTS, was set high enough that the number of active
connections was not limited.
Figure 12 shows throughput for DB2 Connect z/VM guests with one and two
virtual CPUs and scaling think times from 2000 to 5000 milliseconds.
Figure 12. IRWW CPU scaling throughput
26
Performance of environments using DB2 Connect Enterprise Edition
Figure 13 shows CPU utilization for DB2 Connect z/VM guests with one and two
virtual CPUs and scaling think times from 2000 to 5000 milliseconds.
Figure 13. IRWW workload - CPU scaling
Observations
There is a slight throughput advantage in the one CPU case for think times of
2000 milliseconds and 2500 milliseconds. At higher think times there is no
difference in throughput. CPU utilization on the DB2 Connect server for the two
CPU case is slightly higher than the one CPU case. The overall CPU load on the
systems is very low (be aware that 100% means one CPU is fully utilized).
Conclusion
The IRWW workload throughput is not affected by the number of virtual CPUs.
A single CPU for the DB2 Connect system would be sufficient for this type of
workload. For a better comparison of the DB2 Connect behavior under these two
very different workloads, we decided to use two CPUs on the DB2 Connect
system for further runs.
Scaling DB2 Connect MAXAGENTS
In these tests, we measured the affects of connection concentration at DB2
Connect. Two virtual CPUs were defined to the DB2 Connect z/VM guest and 1
GB of memory was used. The number of users was held constant at 500. The
z/VM LPAR was assigned six CPUs and the z/OS LPAR was assigned four CPUs.
The MAXAGENTS were varied from 50 to 500 and think time values of 2500
and 5000 milliseconds were used.
Figure 14 shows throughput for a DB2 Connect z/VM guest with think times of
2500 and 5000 and scaling MAXAGENTS from 50 to 500.
27
Performance of environments using DB2 Connect Enterprise Edition
Figure 14. IRWW MAXAGENTS scaling throughput
Figure 15 shows CPU utilization for DB2 Connect z/VM guest with think times
of 2500 to 5000 milliseconds and scaling MAXAGENTS from 50 to 500.
Figure 15. IRWW workload - MAXAGENTS scaling
Observations
Maximum throughput is reached at 100 MAXAGENTS in both the 2500 and
5000 milliseconds think time cases. More MAXAGENTS are not required for this
28
Performance of environments using DB2 Connect Enterprise Edition
workload. In the 2500 milliseconds case there is a significant increase in
throughput at 100 MAXAGENTS, compared to 50 users.
Conclusion
For the IRWW workload, a MAXAGENTS value of 100 is sufficient for this
workload, even though the number of users is 500. This demonstrates that
limiting the connections to DB2 on z/OS can be done without degrading of the
throughput, up to a certain load level. It can also be used to limit the load
created from this source on DB2 to a certain level, protecting the database
against load peaks from that source.
Comparison of SQLJ versus JDBC
IRWW provides two methods for driving the workload. One uses SQLJ and the
other JDBC. These tests compare the two methods. Two virtual CPUs were
defined for the DB2 Connect z/VM guest and 1 GB memory was used. The think
time values used for the IRWW workload driver were 2000 milliseconds and
5000 milliseconds and the number of user threads was held constant at 500. The
z/VM LPAR was assigned six CPUs and the z/OS LPAR was assigned four CPUs.
The parameter, MAXAGENTS, was set high enough that the number of active
connections was not limited.
Figure 16 shows throughput for a DB2 Connect z/VM guest using SQLJ and
JDBC with think times of 2000 and 5000 milliseconds.
Figure 16. IRWW workload transactions per second - JDBC versus SQLJ
29
Performance of environments using DB2 Connect Enterprise Edition
Figure 17 shows CPU utilization for a DB2 Connect z/VM guest, z/VM LPAR,
and z/OS LPAR using SQLJ and JDBC with think times of 2000 and 5000
milliseconds.
Figure 17. IRWW workload % CPU - JDBC versus SQLJ
Observations
There is little difference between the IRWW JDBC and SQLJ workload drivers.
Conclusion
We selected the SQLJ workload driver because, in our environment, it produces
slightly higher throughput at lower CPU utilization.
Compare DB2 Connect to non-DB2 Connect
In this test, the IRWW workload driver user sent all transaction requests directly
to DB2 on z/OS, bypassing DB2 Connect. This test was run with 500 users and
think times of 2000 and 2500 milliseconds. We also ran 100 users with a think
time of 100 milliseconds. Two virtual CPUs were defined for the DB2 Connect
z/VM guest and 1 GB of memory was used. The z/VM LPAR was assigned six
CPUs and the z/OS LPAR was assigned four CPUs. The parameter,
MAXAGENTS, was set high enough that the number of active connections was
not limited.
Figure 18 compares the DB2 Connect runs to the non-DB2 Connect runs.
30
Performance of environments using DB2 Connect Enterprise Edition
Figure 18. IRWW workload - DB2 Connect versus non-DB2 Connect
Observations
At a think time of 100 milliseconds and the lower number or users, bypassing
DB2 Connect resulted in 30% higher throughput. As the think time and number
of users increase, the difference in throughput decreases.
Conclusion
In the IRWW workload case, it appears that DB2 Connect has a higher impact
on throughput than in the Trade 6 workload case, especially with a lower
number of users. Bypassing DB2 Connect resulted in 6% to 30% higher
throughput. The impact of DB2 Connect is higher – the shorter the think time.
This is expected because here the request rate is higher and DB2 Connect has
more activity to manage.
Appendix A. Detailed set up examples
This appendix contains detailed examples of configurations and sample scripts
we used in our test runs.
DB2 Connect configuration commands for Trade 6
The following commands were used to define the Trade 6 database on the DB2
Connect guest.
db2set DB2COMM=tcpip
db2 catalog tcpip node trade6db remote 10.10.50.110
server 446
31
Performance of environments using DB2 Connect Enterprise Edition
db2 catalog dcs database trade6db as DB91ZOS
db2 catalog database trade6db as trade6db at node
trade6db authentication DCS
You can issue the following DB2 command to generate the output shown below.
db2 connect to trade6db user IBMUSER using password
Database Connection Information
Database server
SQL authorization ID
Local database alias
= DB2 OS/390 9.1.5
= IBMUSER
= TRADE6DB
You can issue the following DB2 command to generate the output shown below.
db2 bind /home/db2inst2/sqllib/bnd/@ddcsmvs.lst grant
public blocking all V
sqlerror continue
Note:
The V symbol indicates that the text continues on the next line. These lines
should be entered on one line, not broken into multiple lines.
LINE
MESSAGES FOR ddcsmvs.lst
------ ------------------------------------------------------------------SQL0061W The binder is in progress.
LINE
MESSAGES FOR db2clist.bnd
------ ------------------------------------------------------------------SQL0038W The bind option SQLERROR CONTINUE has
been
activated since it is required when
binding this DB2-supplied
list file to DB2/MVS, SQL/DS, or
OS/400.
SQL0038W The bind option SQLERROR CONTINUE has
been
activated since it is required when
binding this DB2-supplied
list file to DB2/MVS, SQL/DS, or
OS/400.
LINE
MESSAGES FOR db2clpcs.bnd
------ ------------------------------------------------------------------3521
SQL0199N The use of the reserved word "UNION"
following ""
is not valid. Expected tokens may
include: ", )".
SQLSTATE=42601
32
Performance of environments using DB2 Connect Enterprise Edition
LINE
MESSAGES FOR db2clprr.bnd
------ ------------------------------------------------------------------3521
SQL0199N The use of the reserved word "UNION"
following ""
is not valid. Expected tokens may
include: ", )".
SQLSTATE=42601
LINE
MESSAGES FOR db2clpur.bnd
------ ------------------------------------------------------------------3521
SQL0199N The use of the reserved word "UNION"
following ""
is not valid. Expected tokens may
include: ", )".
SQLSTATE=42601
LINE
MESSAGES FOR db2clprs.bnd
------ ------------------------------------------------------------------3521
SQL0199N The use of the reserved word "UNION"
following ""
is not valid. Expected tokens may
include: ", )".
SQLSTATE=42601
LINE
MESSAGES FOR ddcsmvs.lst
------ ------------------------------------------------------------------SQL0091N Binding was ended with "0" errors and
"6" warnings.
Buffer pools used with the Trade 6 database on z/OS
Table 5. Buffer pools used with Trade 6 database on z/OS
Buffer Pool ID
9
10
Size
60000
60000
DB2 Connect configuration commands for IRWW
db2 catalog tcpip node irwwdb remote 10.10.50.110
server 446
db2 catalog dcs database irwwdb as DB91ZOS
db2 catalog database irwwdb as irwwdb at node irwwdb
authentication dcs
33
Performance of environments using DB2 Connect Enterprise Edition
Buffer pools used with the IRWW database on z/OS
Table 6. Buffer pools used with IRWW database on z/OS
Buffer Pool ID
1
2
3
4
5
6
7
8
Size
60000
60000
10000
7000
10000
20000
20000
60000
Appendix B. Other Sources of Information
ƒ For information on WebSphere Application Server see:
http://www.ibm.com/software/info1/websphere/index.jsp?tab=products/
apptransaction
ƒ For information on Linux on System z see:
www.ibm.com/servers/eserver/zseries/os/linux/
ƒ For information on z/VM see:
www.vm.ibm.com
ƒ For information on IBM open source projects see:
www.ibm.com/developerworks/opensource/index.html
ƒ For information on DB2 9 see:
http://www.ibm.com/software/data/db2/udb/support/manualsv9.html
ƒ For information on DB2 Connect see:
– Quick Beginnings for DB2 Connect, GC10-4243 at:
ftp://ftp.software.ibm.com/ps/products/db2/info/vr9/pdf/letter/en_
US/db2c6e90.pdf
– DB2 Connect User's Guide, SC10-4249 at:
ftp://ftp.software.ibm.com/ps/products/db2/info/vr9/pdf/letter/en_
US/db2c0e90.pdf
– An introduction to IBM DB2 Connect: It's more than meets the eye:
The basics of DB2 Connect at
http://www.ibm.com/developerworks/db2/library/techarticle/dm0503katsnelson/index.html
ƒ For information on Workload Management see:
– z/OS MVS Planning: Workload Management
ƒ For information on DB2 for z/OS see:
– DB2 for z/OS Performance Monitoring and Tuning Guide
34
Performance of environments using DB2 Connect Enterprise Edition
© Copyright IBM Corporation 2008
IBM Corporation
New Orchard Rd.
Armonk, NY 10504
U.S.A.
Produced in the United States of America
2/08
All Rights Reserved
IBM, IBM logo, DB2, DB2 Connect, Enterprise Storage Server, HiperSockets, MVS, System x, System z,
System z9, WebSphere, z/OS, z/VM and zSeries are trademarks or registered trademarks of
International Business Machines Corporation of the United States, other countries or both.
The following are trademarks or registered trademarks of other companies
Java and all Java-based trademarks and logos are trademarks of Sun Microsystems, Inc. in the United
States, other countries or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Intel, Intel logo, Intel Inside, Intel Inside logo, Intel Centrino, Intel Centrino logo, Celeron, Intel Xeon,
Intel SpeedStep, Itanium, and Pentium are trademarks or registered trademarks of Intel Corporation or
its subsidiaries in the United States and other countries.
Linux is a registered trademark of Linus Torvalds in the United States and other countries.
SUSE is a registered trademark of Novell, Inc., in the United States and other countries.
Other company, product and service names may be trademarks or service marks of others.
Information concerning non-IBM products was obtained from the suppliers of their products or their
published announcements. Questions on the capabilities of the non-IBM products should be
addressed with the suppliers.
IBM hardware products are manufactured from new parts, or new and serviceable used parts.
Regardless, our warranty terms apply.
IBM may not offer the products, services or features discussed in this document in other countries, and
the information may be subject to change without notice. Consult your local IBM business contact for
information on the product or services available in your area.
THE INFORMATION CONTAINED IN THIS DOCUMENTATION IS PROVIDED FOR INFORMATIONAL
PURPOSES ONLY. WHILE EFFORTS WERE MADE TO VERIFY THE COMPLETENESS AND ACCURACY
OF THE INFORMATION CONTAINED IN THIS DOCUMENTATION, IT IS PROVIDED "AS IS" WITHOUT
WARRANTY OF ANY KIND, EXPRESS OR IMPLIED. IN ADDITION, THIS INFORMATION IS BASED ON
IBM'S CURRENT PRODUCT PLANS AND STRATEGY, WHICH ARE SUBJECT TO CHANGE BY IBM
WITHOUT NOTICE. IBM SHALL NOT BE RESPONSIBLE FOR ANY DAMAGES ARISING OUT OF THE
USE OF, OR OTHERWISE RELATED TO, THIS DOCUMENTATION OR ANY OTHER DOCUMENTATION.
NOTHING CONTAINED IN THIS DOCUMENTATION IS INTENDED TO, NOR SHALL HAVE THE
EFFECT OF, CREATING ANY WARRANTIES OR REPRESENTATIONS FROM IBM (OR ITS SUPPLIERS
OR LICENSORS), OR ALTERING THE TERMS AND CONDITIONS OF THE APPLICABLE LICENSE
AGREEMENT GOVERNING THE USE OF IBM SOFTWARE
ZSW03032-USEN-00
35
Fly UP