How To
Summary
How to get a count of connections into the IBM i and break out the USRPRF and Client IP addresses
Objective
Make it easier to detect the source of normal connections or connection leaks.
Environment
The HISTORY_LOG_INFO table function was introduced with OS400 v720 Tech Refresh 7 and OS400 v730 Tech Refresh 3 in late 2017.
This technique will use the IBM Access Client Solutions "Run SQL Scripts" functionality. It is recommended to use a current version of IBM ACS since that product has new features and SQL samples added to it on a regular basis.
The ACS JDBC Configuration must have "Translate CCSID 65535" checked. To verify the setting, go to Edit -> JDBC Configurations... Edit the JDBC Configuration in use; select the "Translation" tab and ensure "Translate CCSID 65535" is checked.:
Steps
Open IBM ACS Run SQL Scripts and paste in the following SQL statements:
-- adapted from material by Eric Barsness
-- First, a table to hold the connection data must be created:
CREATE TABLE qtemp.qhst AS
(SELECT *
FROM TABLE (
qsys2.history_log_info(CURRENT TIMESTAMP - 1 DAY)
) x)
WITH DATA;
-- CPIAD09 messages are generated when users connect to the following jobs:
-- QZRCSRVS
-- QZDASOINIT QZDASSINIT
-- QPWFSERVSO QPWFSERVSS
-- QZSCSRVS
-- To see the total CPIAD09s per hour:
SELECT COUNT(*) AS "Total", COUNT(*) / 24 AS "Per Hour"
FROM qtemp.qhst
WHERE message_id = 'CPIAD09';
-- which jobs specifically?:
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;
-- add the USRPRF info:
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;
-- Connections by IP address and Job and User :
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 allocate QRWTSRVR jobs and CPI3E34 messages are issued to QHST log.
-- So we could create a new table for just the DDM/DRDA connections.
-- get number of unique DDM/DRDA connections for the last 6 days :
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('2023-10-25', '2023-10-30-09.45.00')
) x
WHERE message_id = 'CPI3E34')
WITH DATA;
-- Once you've built the qhstDDM table, query it. For example:
-- simple count of unique jobs (Prestart jobs are re-used 200x by default) :
SELECT COUNT(DISTINCT FROM_JOB) AS "numofuniquejobs"
FROM qtemp.qhstDDM;
-- More detailed output for DDM/DRDA connections:
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;
DROP TABLE qtemp.qhstDDM;Adapt the statements to your needs.
Related Information
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"}}]
Was this topic helpful?
Document Information
Modified date:
19 November 2025
UID
ibm16212238