IBM i Work Management & Job Monitoring

Query, monitor, and analyze jobs on IBM i using SQL table functions QSYS2.JOB_INFO and QSYS2.ACTIVE_JOB_INFO.

Available Tools

The ibmi-mcp-server should already be connected with two available tools:

• describe_sql_object
• execute_sql

Use the describe_sql_object tool to get information on any IBM i Object, for example, to column information for a given table, or Function. If you need to write your own SQL statement, make sure to validate SQL objects being referenced with this tool.

Use the execute_sql tool to run SQL statements on the IBM i system. This tool only runs read only (SELECT) statements.

Service Selection Guide

QSYS2.JOB_INFO

Purpose: Find jobs across all states (active, queued, completed)

Use for:

• Jobs waiting on job queues (*JOBQ)
• Completed jobs with output (*OUTQ)
• Jobs by user, submitter, or subsystem
• Job configuration and attributes
• Replacing: WRKUSRJOB, WRKSBSJOB, WRKSBMJOB

QSYS2.ACTIVE_JOB_INFO

Purpose: Monitor active jobs with performance metrics

Use for:

• Real-time performance (CPU, I/O, memory)
• Elapsed statistics tracking (delta measurements)
• Long-running SQL statement detection
• Resource consumption analysis
• Lock contention monitoring
• SQL activity and cursor analysis
• Replacing: WRKACTJOB

Key Capabilities

Job Discovery & Filtering

• By Status – Find active, queued, or completed jobs
• By User – Current user, specific users, or all users
• By Type – Interactive, batch, system, prestart, etc.
• By Subsystem – QBATCH, QUSRWRK, QSYSWRK, etc.
• By Submitter – Jobs from current session, user, or workstation
• By Name – Specific job names or generic patterns

Performance Monitoring

• CPU Usage – Current CPU time and elapsed CPU during interval
• Memory – Temporary storage, QTEMP usage, peak storage
• I/O Activity – Total, async, and sync disk operations
• Lock Analysis – Database and non-database lock waits
• Response Time – Interactive job response metrics

SQL Activity Analysis

• Active Statements – Currently executing SQL with execution time
• Statement Details – SQL text, status, object information
• Cursor Metrics – Open, full open, pseudo open/closed counts
• Query Engine – CQE vs SQE cursor usage and storage
• Connection Info – Client IP, host, interface, port

Resource Tracking

• Storage Limits – Max temporary storage and current usage
• CPU Limits – Maximum processing time allowed
• Thread Info – Active thread count and limits
• Queue Status – Job queue priority and position
• Workload Groups – Workload management group membership

Common Use Cases

1. Current Job Monitoring

Get detailed information about your current connection’s job

2. Performance Troubleshooting

• Identify top CPU consumers across subsystems
• Find jobs using excessive temporary storage
• Detect long-running SQL statements
• Analyze lock contention patterns

3. Queue Management

• View jobs waiting on job queues with priorities
• Track job queue status (held, released, scheduled)
• Monitor job scheduling times

4. User Activity Tracking

• List all jobs for specific users
• Find jobs submitted by current user or workstation
• Track active vs queued jobs by user

5. Storage Analysis

• Identify jobs with high QTEMP usage
• Monitor temporary storage trends
• Track peak storage consumption

6. SQL Performance

• Find active SQL statements and execution time
• Analyze cursor usage (CQE vs SQE)
• Monitor SQL Server Mode connections
• Track query optimizer activity

7. System Administration

• Get system job information (like SCPF for IPL time)
• Monitor QSQSRVR and host server jobs
• Track prestart job statistics
• Analyze workload group utilization

8. Elapsed Statistics

Set baseline and measure performance deltas over time intervals

9. Job Type Analysis

Filter by interactive, batch, prestart, or system jobs

10. Lock Contention

Identify jobs experiencing database and non-database lock waits

Filter Parameters (Performance Critical)

Always use UDTF filter parameters (not WHERE clause) for optimal performance:

JOB_INFO Filters

• JOB_STATUS_FILTER – *ALL, *ACTIVE, *JOBQ, *OUTQ
• JOB_TYPE_FILTER – *ALL, *BATCH, *INTERACT
• JOB_SUBSYSTEM_FILTER – Subsystem name or *ALL
• JOB_USER_FILTER – User name, *ALL, *USER, or USER special register
• JOB_SUBMITTER_FILTER – *ALL, *JOB, *USER, *WRKSTN
• JOB_NAME_FILTER – Job name or *ALL

ACTIVE_JOB_INFO Filters

• RESET_STATISTICS – YES/NO (establish measurement baseline)
• SUBSYSTEM_LIST_FILTER – Comma-separated subsystems (max 25)
• JOB_NAME_FILTER – *, *ALL, *CURRENT, *SBS, *SYS, or job name
• CURRENT_USER_LIST_FILTER – Comma-separated users (max 10)
• DETAILED_INFO – NONE, WORK, QTEMP, FULL, ALL

DETAILED_INFO Levels

SQL Column Auth: QIBM_DB_SQLADM or QIBM_DB_SYSMON function usage identifiers

Essential Columns

JOB_INFO

Job identity, status, type, subsystem, queue info, timing, completion status, configuration, regional settings, logging, output queues

ACTIVE_JOB_INFO

Job identity, status, function, CPU usage, memory, I/O counts, elapsed statistics, SQL statement details, cursor metrics, lock waits, client connection info, QTEMP size

CL Command Migration

Best Practices

1. Use UDTF filters – Always filter with parameters, not WHERE clause
2. Start minimal – Use DETAILED_INFO => ‘NONE’, add detail as needed
3. Filter subsystems – Specify subsystems to reduce rows scanned
4. Measure deltas – Use RESET_STATISTICS for elapsed metrics
5. Exclude system jobs – Filter JOB_TYPE <> ‘SYS’ for user jobs
6. Limit results – Use LIMIT clause on large systems
7. Combine services – Join with JOBLOG_INFO, PRESTART_JOB_INFO, etc.
8. Check active SQL – Filter SQL_STATEMENT_STATUS = ‘ACTIVE’ for running queries

Quick Examples

Get current job info

SELECT * FROM TABLE(QSYS2.ACTIVE_JOB_INFO(JOB_NAME_FILTER => '*')) X;

Find top CPU consumers

SELECT JOB_NAME, AUTHORIZATION_NAME, CPU_TIME
  FROM TABLE(QSYS2.ACTIVE_JOB_INFO(SUBSYSTEM_LIST_FILTER => 'QUSRWRK,QBATCH'))
  ORDER BY CPU_TIME DESC LIMIT 10;

Find jobs on job queue

SELECT * FROM TABLE(QSYS2.JOB_INFO(JOB_STATUS_FILTER => '*JOBQ')) X;

Find long-running SQL statements

SELECT JOB_NAME, AUTHORIZATION_NAME,
       TIMESTAMPDIFF(2, CAST(CURRENT_TIMESTAMP - SQL_STATEMENT_START_TIMESTAMP AS CHAR(22))) AS SECONDS,
       SQL_STATEMENT_TEXT
  FROM TABLE(QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL'))
  WHERE SQL_STATEMENT_STATUS = 'ACTIVE'
  ORDER BY SECONDS DESC;

Find jobs for a specific user

SELECT * FROM TABLE(QSYS2.JOB_INFO(JOB_USER_FILTER => 'USERNAME')) X;

Reference Documentation

• JOB_INFO Reference – Complete parameters and columns
• ACTIVE_JOB_INFO Reference – Complete parameters and columns
• Example SQL Patterns – Working query examples
• IBM ACTIVE_JOB_INFO – Enhancement history
• IBM JOB_INFO – Enhancement history