This gist shows a glimpse into the realm of what's possible when using SQL and IBM i Services to monitor and manage interactive users.

Interactive Users and usage.sql

--
-- description: What are ALL my interactive users doing right now? (summary)
--              ====
--
select j.authorization_name as user_name,  u.text_description as description,
       count(*) as job_count
  from qsys2.user_info as u
       join table (
           qsys2.active_job_info(
             subsystem_list_filter => 'QINTER', detailed_info => 'ALL')
         ) as j
         on u.authorization_name = j.authorization_name and
           j.job_type = ('INT')
   group by j.authorization_name, u.text_description
   order by 3 desc;
stop;
--
--
-- description: What functions are ALL my interactive users using right now? (summary)
--                   =========
--
select j.authorization_name as user_name, function, count(*) as function_count
  from table (
      qsys2.active_job_info(subsystem_list_filter => 'QINTER', detailed_info => 'ALL')
    ) as j
  group by j.authorization_name, function
  order by 1, 3 desc; ;
stop;
--
-- description: What functions are ALL my interactive users using right now? (detail)
--                   =========
--
select j.authorization_name as user_name, job_name, u.text_description as description,
       job_entered_system_time as job_start, function, limit_capabilities,
       client_applname, regexp_replace(sql_statement_text, '  ', '') as sql_stmt,
       cpu_time, j.*
  from qsys2.user_info as u
       join table (
           qsys2.active_job_info(
             subsystem_list_filter => 'QINTER', detailed_info => 'ALL')
         ) as j
         on u.authorization_name = j.authorization_name and
           j.job_type = ('INT')
  order by job_start;
stop;
--
-- description: What locks are my interactive users holding right now?
--                   =====
--
select j.authorization_name as user_name, job_name, u.text_description as description, jl.*
  from qsys2.user_info as u
       join table (
           qsys2.active_job_info(
             subsystem_list_filter => 'QINTER', detailed_info => 'ALL')
         ) j
         on u.authorization_name = j.authorization_name, lateral (
         select *
           from table (
               qsys2.job_lock_info(job_name)
             )
       ) jl
  where j.job_type = ('INT') and lock_category not like 'INTERNAL%'
  order by lock_state;
stop;
--
-- description: What files do my interactive users have open right now?
--                   =====
--
select j.authorization_name as user_name, job_name, u.text_description as description, op.*
  from qsys2.user_info as u
       join table (
           qsys2.active_job_info(
             subsystem_list_filter => 'QINTER', detailed_info => 'ALL')
         ) j
         on u.authorization_name = j.authorization_name, lateral (
         select *
           from table (
               qsys2.open_files(job_name)
             )
       ) op
  where j.job_type = ('INT');

Thank you, great tool. Carlos

Thank you Carlos!

Thank you!!! See you in New Orleans. Best regards Carlos On Tuesday, May 10, 2022, 04:13:58 PM CDT, Scott Forstie ***@***.***> wrote: @forstie commented on this gist. Thank you Carlos! — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.Message ID: <forstie/Interactive Users and ***@***.***>