DB2 and batch testing

IBM DB2 on z/OS is the relational database backbone for many of the world’s largest transactional systems — banking general ledgers, insurance policy databases, and airline reservation systems all run on DB2. Two distinct performance testing scenarios apply: online query testing (measuring the response time of individual SQL queries under concurrent user load) and batch window testing (verifying that long-running batch jobs complete within their allowed window). This page covers both using JMeter’s built-in JDBC Request sampler, run through MaxoPerf.

Before you start

• You need the DB2 JDBC driver JAR (db2jcc4.jar or db2jcc.jar), available from IBM’s DB2 client package. This JAR is required by JMeter to connect to DB2. Confirm with MaxoPerf support whether it is already included in the JMeter runner image.
• You need a DB2 connection string, user ID, and password for the test schema. Use a dedicated test schema — never run load tests directly against production tables.
• Coordinate with your DBA team to confirm authorized query patterns, maximum connections, and the DB2 subsystem’s buffer pool and lock configuration for the test period.
• For batch window testing, understand the batch schedule: when does the window open, when must all jobs complete, and what is the current batch duration baseline.

Understanding the two scenarios

Online query testing

Online DB2 queries are issued concurrently by CICS transactions, IMS programs, or application servers. The performance question is: as the number of concurrent queries increases, how does response time scale? Bottlenecks appear as lock contention, buffer pool pressure, or CPU saturation on the DB2 specialty engine (zIIP).

JMeter’s JDBC sampler drives SQL queries concurrently and measures per-query response time. MaxoPerf reports this as throughput (queries per second) and latency percentiles.

Batch window testing

Batch processing on mainframes runs in scheduled windows — often overnight or between business hours. Month-end closes, statement generation, and interest calculations are common examples. The performance constraint is a hard deadline: batch must finish before the online window opens. If batch takes 14 hours and the window is 12 hours, something needs to change.

For batch window testing, you do not typically drive thousands of concurrent VUs. Instead, you measure whether the batch program itself can complete its work within the window. The JMeter test either triggers the batch (via a JCL submission endpoint, an MQ trigger, or a stored procedure) and polls for completion, or runs the batch SQL workload directly at the throughput it needs to sustain to finish on time.

Configuring JDBC for DB2

JDBC Connection Configuration

Add a JDBC Connection Configuration element to your Thread Group:

Field	DB2 value
Variable name	db2Connection (any name — referenced by the JDBC Request sampler)
Database URL	jdbc:db2://<hostname>:<port>/<database> — for DB2 on z/OS, the database name is the DB2 location name
JDBC Driver class	com.ibm.db2.jcc.DB2Driver
Username / Password	DB2 user ID and password for the test schema
Max number of connections	Set to match your VU count — each JMeter thread needs one connection
Connection validation	Enable validation with a simple SQL statement (e.g., SELECT 1 FROM SYSIBM.SYSDUMMY1)

Making the DB2 JAR available

The db2jcc4.jar must be on the JMeter classpath. Locally, place it in JMeter’s lib/ directory. For MaxoPerf:

• Preferred: Contact MaxoPerf support to add db2jcc4.jar to the JMeter runner image for your account.
• Alternative: Upload the JAR as a Test asset. You may need to configure the classpath reference in your JMX explicitly — confirm the mechanism with MaxoPerf support.

JDBC Request sampler

With the connection configured, add JDBC Request samplers to the Thread Group. Each sampler executes one SQL statement or calls one stored procedure:

• Variable name — db2Connection (must match the connection configuration).
• Query type — Select Statement, Update Statement, Callable Statement (for stored procedures), or Prepared Select Statement (for parameterized queries).
• SQL query — the SQL to execute. Use JMeter variables (e.g., ${accountId}) to parameterize with data from a CSV Data Set Config.
• Variable names — for SELECT queries, map result columns to JMeter variables for use in subsequent samplers.
• Result variable name — capture the full result set as a JMeter variable for assertion.

Example: parameterized account balance query

<!-- JDBC Request sampler — account balance lookup -->
<JDBCRequest guiclass="TestBeanGUI" testclass="JDBCRequest" testname="Account balance lookup" enabled="true">
  <stringProp name="dataSource">db2Connection</stringProp>
  <stringProp name="queryType">Prepared Select Statement</stringProp>
  <stringProp name="query">
    SELECT ACCOUNT_BALANCE, LAST_UPDATE_DATE
    FROM TEST_SCHEMA.ACCOUNTS
    WHERE ACCOUNT_ID = ?
  </stringProp>
  <stringProp name="queryArguments">${accountId}</stringProp>
  <stringProp name="queryArgumentsTypes">INTEGER</stringProp>
  <stringProp name="variableNames">balance,lastUpdate</stringProp>
</JDBCRequest>

Upload a CSV file with accountId values as a Test asset, and use a CSV Data Set Config element to feed each VU a unique account ID.

Running an online query test in MaxoPerf

Structure the test for online query scenarios:

execution:
  - executor: jmeter
    concurrency: 100
    ramp-up: 3m
    hold-for: 20m
    scenario: db2-online-query

scenarios:
  db2-online-query:
    script: db2-online-query.jmx

100 concurrent VUs represents 100 simultaneous DB2 connections. Confirm with your DBA that this is within the subsystem’s MAXDBAT (maximum database access threads) setting.

Running a batch soak test in MaxoPerf

For batch window testing, use a soak-style run with modest concurrency but long duration:

execution:
  - executor: jmeter
    concurrency: 10
    ramp-up: 1m
    hold-for: 4h
    scenario: db2-batch-soak

scenarios:
  db2-batch-soak:
    script: db2-batch-soak.jmx

The JMX for the batch soak drives the batch SQL workload (bulk selects, updates, and inserts) at the throughput the batch program needs to sustain to finish within its window. A 4-hour hold validates a batch window that nominally takes 3 hours — leaving headroom margin in the results.

Reading the results

After a DB2 load run, MaxoPerf shows:

• Throughput — SQL statements per second. For online testing, compare against your baseline (derived from DB2 accounting records). For batch testing, compare against the required throughput rate to complete within the window.
• p95 latency — 95th percentile query response time. Latency that climbs linearly during a soak indicates resource saturation (buffer pool, lock contention, or zIIP thread exhaustion).
• Error rate — DB2 SQL errors (SQLCODE non-zero) appear as JMeter sampler failures if you add a Response Assertion on the JDBC result variable. Common errors under load: -911 (deadlock/timeout), -904 (resource unavailable), -805 (package not found).

Correlate with DB2 accounting

DB2 SMF 101 accounting records capture per-connection CPU time, elapsed time, lock wait time, and buffer pool I/O for every SQL statement. Ask your DBA to run DB2 accounting reports (via OMEGAMON, Db2 Performance Expert, or the free DB2PM tool) during the MaxoPerf run and correlate them with the latency percentiles you see in MaxoPerf. This identifies whether latency is in the SQL plan (CPU-bound) or in I/O and lock waits.

Do / don’t

Do:

• Use a dedicated test schema with representative data volume — performance results on an empty test table are not predictive of production behavior with millions of rows.
• Parameterize queries using CSV data — random access patterns better represent real workloads than repeatedly hitting the same row (which would be served from buffer pool after the first query).
• Set connection pool size equal to VU count — under-provisioning causes connection wait time that pollutes latency measurements.
• Check RUNSTATS is current on test tables — stale statistics cause DB2 to choose poor access paths, which misrepresents production performance.

Don’t:

• Run DDL (CREATE, ALTER, DROP) statements in a load test — DDL acquires table-level locks that block all concurrent queries.
• Open connections and hold them idle for long periods during the test — this exhausts MAXDBAT for other DB2 applications running on the same subsystem.
• Ignore SQLCODE -911 (deadlock/timeout) errors — they indicate lock contention that is likely to appear in production under peak load.
• Test against production data without explicit DBA sign-off — regulatory environments (banking, insurance) may prohibit this even for read-only queries.

Where to go next

• JMeter plugins for mainframe — how the JDBC sampler fits alongside the RTE plugin and JMS sampler.
• Daily mainframe scenarios — month-end batch, end-of-day settlement, and insurance claims processing scenarios.
• Soak / endurance test — general soak test guidance applicable to long batch windows.
• MQ and messaging testing — MQ-triggered batch jobs that feed into DB2 processing pipelines.
• Mainframe do and don’t — authorization, DBA coordination, and MIPS cost guidance.