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":"BU058","label":"IBM Infrastructure w\/TPS"},"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":"LOB57","label":"Power"}}]
Was this topic helpful?
Document Information
Modified date:
09 November 2023
UID
ibm16212238