DB2 and batch testing

IBM DB2 on z/OS is the relational database under many of the world’s largest transactional systems. Banking general ledgers, insurance policy databases and airline reservation systems all run on DB2. Two performance testing scenarios apply: online query testing (measuring the response time of individual SQL queries under concurrent user load) and batch window testing (checking that long-running batch jobs complete within their allowed window). This page covers both with 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) from IBM’s DB2 client package. JMeter needs this JAR to connect to DB2. Ask MaxoPerf support whether the JMeter runner image already includes it.
• 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, know the batch schedule: when the window opens, when all jobs must complete, and the current batch duration baseline.

Understanding the two scenarios

Online query testing

CICS transactions, IMS programs and application servers issue online DB2 queries concurrently. You want to know how response time scales as the number of concurrent queries grows. Bottlenecks show up 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 the result 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 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 has to change.

Batch window tests do not usually drive thousands of concurrent VUs. You measure whether the batch program itself can finish 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 must 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; the JDBC Request sampler references it)
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 set 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 later 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 must sustain to finish within its window. A 4-hour hold validates a batch window that nominally takes 3 hours, which leaves headroom 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 throughput rate required to complete within the window.
• p95 latency: 95th percentile query response time. Latency that climbs linearly during a soak points to 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, then compare them with the latency percentiles you see in MaxoPerf. That tells you whether latency sits 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. Results on an empty test table do not predict production behavior with millions of rows.
• Parameterize queries with CSV data. Random access patterns match real workloads better than hitting the same row again and again (the buffer pool serves it after the first query).
• Set connection pool size equal to VU count. Too few connections add connection wait time that pollutes latency measurements.
• Check that RUNSTATS is current on test tables. Stale statistics make DB2 choose poor access paths, and the results then misrepresent 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 signal 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 for 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.