Auditing IBM i Database Connections Using the History Log

How To

Summary

How to get a historical count of connections into an IBM i system and break out the user profile and client IP addresses, using the QSYS2.HISTORY_LOG_INFO table function.

Note: This document describes how to query the IBM i history log (QHST) to audit past connection activity. For a live view of currently active database connections, see QSYS2.ACTIVE_DB_CONNECTIONS (available on all currently supported releases).

Objective

Make it easier to detect the source of normal connections or connection leaks by querying IBM i history log messages generated whenever a client connects to the database host server or the DDM/DRDA server.

Environment

The QSYS2.HISTORY_LOG_INFO table function is available on all currently supported releases.

The SQL statements in this document are intended to be run in IBM Access Client Solutions (ACS) Run SQL Scripts. It is recommended to use a current release of ACS, as new features and SQL samples are added regularly.

Required ACS JDBC configuration

The ACS JDBC configuration must have Translate CCSID 65535 enabled. To verify or set this option:

In ACS, open Edit → JDBC Configurations…
Select the JDBC configuration in use and click Edit.
Select the Translation tab.
Ensure Translate CCSID 65535 is checked.

Connection type coverage

This technique covers two distinct connection mechanisms, each identified by a different history log message ID. Understanding which applies to your environment will help you choose the correct section below.

Connection type	History log message	Job names
ODBC, JDBC, .NET, OLE DB (Database Host Server)	CPIAD09	QZDASOINIT, QZDASSINIT
DDM / DRDA (Remote Data Manager)	CPI3E34	QRWTSRVR

Note: Green-screen (5250) sign-on, FTP, SSH, Telnet, and IBM i Access for Windows 5250 emulation connections do not generate CPIAD09 or CPI3E34 messages and are not captured by the queries in this document.

Steps

Open ACS Run SQL Scripts and work through the two sections below. You can run all statements in a single script or section by section, adapting them to your time range and environment as needed.

Performance note — busy systems: On high-throughput systems, querying a full day of history log data can produce millions of rows and consume significant QTEMP space. Start with a short window (for example, 1 hour) and expand as needed. The DDM/DRDA section below shows the date/time range syntax; apply the same pattern to the ODBC/JDBC queries.

ODBC / JDBC connections

CPIAD09 messages are written to the history log whenever a client connects via ODBC, JDBC, .NET, or OLE DB through the IBM i Database Host Server.

Step 1 — Load history log data into QTEMP

Create a working table in QTEMP from the last 24 hours of history log data. QTEMP is scoped to the current job — the table is automatically discarded when the ACS Run SQL Scripts session ends.

-- Load the last 24 hours of history log data
CREATE TABLE qtemp.qhst AS (
  SELECT *
  FROM   TABLE(qsys2.history_log_info(CURRENT TIMESTAMP - 1 DAY)) x
) WITH DATA;

-- Or restrict to a specific date/time range:
-- CREATE TABLE qtemp.qhst AS (
--   SELECT * FROM TABLE(qsys2.history_log_info('2026-09-16', '2026-09-16-17.00.00')) x
-- ) WITH DATA;

Step 2 — Total CPIAD09 connection count

A quick headline count and average per hour:

SELECT
    COUNT(*)        AS "Total",
    COUNT(*) / 24   AS "Per Hour (avg)"
FROM   qtemp.qhst
WHERE  message_id = 'CPIAD09';

Step 3 — Breakdown by server job type

SELECT
    COUNT(*) AS "Total",
    SUBSTRING(from_job, LOCATE('/', SUBSTRING(from_job, 8)) + 8) AS "Job"
FROM   qtemp.qhst
WHERE  message_id = 'CPIAD09'
GROUP BY
    SUBSTRING(from_job, LOCATE('/', SUBSTRING(from_job, 8)) + 8)
ORDER BY 1 DESC;

Step 4 — Breakdown by job type and user profile

SELECT
    COUNT(*) AS "Total",
    SUBSTRING(FROM_JOB, LOCATE('/', SUBSTRING(FROM_JOB, 8)) + 8) AS "Job",
    FROM_USER AS "Current User"
FROM   qtemp.qhst
WHERE  message_id = 'CPIAD09'
GROUP BY
    SUBSTRING(FROM_JOB, LOCATE('/', SUBSTRING(FROM_JOB, 8)) + 8),
    FROM_USER
ORDER BY 1 DESC;

Step 5 — Breakdown by IP address, job type, and user profile

The client IP address is embedded in the MESSAGE_TOKENS field of CPIAD09 starting at position 65. This offset reflects the fixed-length token layout of the CPIAD09 message; if results look incorrect, verify the offset by inspecting a raw row: SELECT message_tokens FROM qtemp.qhst WHERE message_id = 'CPIAD09' FETCH FIRST 1 ROW ONLY. IPv6 addresses are longer than IPv4 but are handled correctly by the open-ended SUBSTRING.

SELECT
    COUNT(*) AS "Count",
    SUBSTRING(TRIM(message_tokens), 65) AS "IP Address",
    SUBSTRING(from_job, LOCATE('/', SUBSTRING(from_job, 8)) + 8) AS "Job",
    from_user AS "Current User"
FROM   qtemp.qhst
WHERE  message_id = 'CPIAD09'
GROUP BY
    SUBSTRING(TRIM(message_tokens), 65),
    SUBSTRING(from_job, LOCATE('/', SUBSTRING(from_job, 8)) + 8),
    from_user
ORDER BY 1 DESC;

DDM / DRDA connections

DDM/DRDA connections allocate QRWTSRVR prestart jobs and write CPI3E34 messages to the history log. These are handled separately from ODBC/JDBC connections above.

Note that prestart jobs are reused by default (up to 200 times), so a count of unique jobs is a useful lower-bound estimate of actual connection events.

Step 1 — Load DDM/DRDA history data into QTEMP

-- Load the last 6 days, filtering for CPI3E34 messages only
CREATE TABLE qtemp.qhstDDM AS (
  SELECT *
  FROM   TABLE(qsys2.history_log_info(CURRENT TIMESTAMP - 6 DAYS)) x
  WHERE  message_id = 'CPI3E34'
) WITH DATA;

-- Or a specific date/time range:
-- CREATE TABLE qtemp.qhstDDM AS (
--   SELECT * FROM TABLE(
--     qsys2.history_log_info('2026-09-13', '2026-09-16-09.45.00')
--   ) x WHERE message_id = 'CPI3E34'
-- ) WITH DATA;

Step 2 — Count unique DDM/DRDA jobs

-- Prestart jobs are reused ~200x by default; unique job count is a lower bound
SELECT COUNT(DISTINCT FROM_JOB) AS "numofuniquejobs"
FROM   qtemp.qhstDDM;

Step 3 — Detailed DDM/DRDA breakdown by IP address, job, and user

The client IP address is embedded in MESSAGE_TOKENS for CPI3E34 starting at position 72 (a different offset than CPIAD09). Verify the offset for your environment with: SELECT message_tokens FROM qtemp.qhstDDM FETCH FIRST 1 ROW ONLY.

SELECT
    COUNT(*) AS "Count",
    SUBSTRING(TRIM(message_tokens), 72) AS "IP Address",
    SUBSTRING(from_job, LOCATE('/', SUBSTRING(from_job, 8)) + 8) AS "Job",
    from_user AS "Current User"
FROM   qtemp.qhstDDM
WHERE  message_id = 'CPI3E34'
GROUP BY
    SUBSTRING(TRIM(message_tokens), 72),
    SUBSTRING(from_job, LOCATE('/', SUBSTRING(from_job, 8)) + 8),
    from_user
ORDER BY 1 DESC;

Additional Information

Other host server jobs that post CPIAD09

The following host server jobs also post a CPIAD09 message to the history log and will appear in the query results above. They are not database connections but may still be useful for auditing overall client activity:

QZRCSRVS (Remote Command)
QPWFSERVSO / QPWFSERVSS (File Host Server)
QZSCSRVS (Central Server)
QZBSCNNSRV (Host Connection Server)

Related Information

Creating a report for Users that connect via ODBC/JDBC using Audit Value *JOBDTA

HISTORY_LOG_INFO table function

QSYS2.ACTIVE_DB_CONNECTIONS view — IBM Documentation (real-time active connecti…

IBM i Access Client Solutions

Document Location

Worldwide

[{"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CHZAA2","label":"Data Access"}],"ARM Case Number":"TS003617731","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)","Line of Business":{"code":"LOB68","label":"Power HW"}}]

Tips