ajshedivy/work-management

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 CLI is the primary tool for executing work management queries. Set SKILL_DIR to this skill's installed location (the directory containing this SKILL.md file):

# SKILL_DIR = directory containing this SKILL.md
# Examples: ./skills/work-management, ~/.claude/skills/work-management

# List all work management tools
ibmi tools --tools "$SKILL_DIR/tools/" --toolset work_management_default

# Run a specific tool
ibmi tool list_active_jobs --tools "$SKILL_DIR/tools/"

# Ad-hoc SQL for custom queries
ibmi sql "SELECT * FROM TABLE(QSYS2.ACTIVE_JOB_INFO(JOB_NAME_FILTER => '*')) X"

Use ibmi describe <schema>.<object> to verify columns and parameters before writing custom SQL.

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

| Level | Returns | Authorization | Use Case | |-------|---------|---------------|----------| | NONE | Basic job info | None | Quick overview | | WORK | + Work management | None | Job config, queues | | QTEMP | + QTEMP_SIZE | *JOBCTL | Storage analysis | | FULL | All except QTEMP/HOST | Special auth for SQL cols | Complete performance data | | ALL | Complete | Special auth for SQL cols | Full SQL activity detail |

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

| CL Command | SQL Service | |------------|-------------| | WRKACTJOB | ACTIVE_JOB_INFO() | | WRKUSRJOB | JOB_INFO() + filter system jobs | | WRKSBSJOB | JOB_INFO(JOB_SUBSYSTEM_FILTER) | | WRKSBMJOB | JOB_INFO(JOB_SUBMITTER_FILTER) |

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;

Pre-built Tools

The tools/work-management.yaml file provides 12 ready-to-use tools:

| Tool | Description | |------|-------------| | list_active_jobs | Active jobs with CPU, status, and subsystem filter | | get_job_info | Detailed info for a specific qualified job name | | list_scheduled_jobs | Scheduled jobs with dates, times, and frequency | | list_subsystems | Active subsystems with aggregated job metrics | | list_job_queues | Job queues with status and job count breakdowns | | get_job_log_entries | Job log messages for a specific job | | find_long_running_sql | Active SQL statements sorted by elapsed time | | find_jobs_by_user | All jobs for a user across all statuses | | list_subsystem_info | Subsystem configuration with max/current active jobs and monitor job | | list_ended_jobs | Recently ended jobs with CPU time, end code, and completion detail | | list_job_descriptions | Job descriptions with user, job queue, routing, and logging settings | | list_autostart_jobs | Autostart job entries configured in subsystem descriptions |

An additional work_management_locks toolset provides get_object_lock_info, get_record_lock_info, and get_job_lock_info for contention analysis.

ibmi tool <tool_name> --tools "$SKILL_DIR/tools/"          # Execute
ibmi tool <tool_name> --tools "$SKILL_DIR/tools/" --dry-run # Preview SQL
ibmi tools show <tool_name> --tools "$SKILL_DIR/tools/"     # View details

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