work-management

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

IBM i Work Management & Job Monitoring

IBM i作业管理与作业监控

Query, monitor, and analyze jobs on IBM i using SQL table functions

QSYS2.JOB_INFO

and

QSYS2.ACTIVE_JOB_INFO

使用SQL表函数

QSYS2.JOB_INFO

和

QSYS2.ACTIVE_JOB_INFO

查询、监控和分析IBM i上的作业。

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.

ibmi-mcp-server

应已连接了两个可用工具：

```
describe_sql_object
```
```
execute_sql
```

使用

describe_sql_object

工具获取任何IBM i对象的信息，例如获取指定表或函数的列信息。如果需要编写自定义SQL语句，请务必使用此工具验证引用的SQL对象。

使用

execute_sql

工具在IBM i系统上运行SQL语句。该工具仅支持运行只读（

SELECT

）语句。

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

用途： 查找所有状态的作业（活跃、排队、已完成）

适用场景：

处于作业队列的作业（
```
*JOBQ
```
）
带有输出的已完成作业（
```
*OUTQ
```
）
按用户、提交者或子系统筛选的作业
作业配置与属性
替代：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

用途： 监控带有性能指标的活跃作业

适用场景：

实时性能（CPU、I/O、内存）
耗时统计跟踪（增量测量）
长时间运行SQL语句检测
资源消耗分析
锁争用监控
SQL活动与游标分析
替代：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

按状态 - 查找活跃、排队或已完成的作业
按用户 - 当前用户、特定用户或所有用户
按类型 - 交互式、批处理、系统、预启动等
按子系统 - QBATCH、QUSRWRK、QSYSWRK等
按提交者 - 当前会话、用户或工作站提交的作业
按名称 - 特定作业名称或通用模式

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

CPU使用率 - 当前CPU时间和时间间隔内的累计CPU时间
内存 - 临时存储、QTEMP使用量、峰值存储
I/O活动 - 总磁盘操作、异步和同步磁盘操作
锁分析 - 数据库与非数据库锁等待
响应时间 - 交互式作业响应指标

SQL Activity Analysis

SQL活动分析

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

活跃语句 - 当前正在执行的SQL及其执行时间
语句详情 - SQL文本、状态、对象信息
游标指标 - 打开、完全打开、伪打开/关闭计数
查询引擎 - CQE与SQE游标使用情况和存储
连接信息 - 客户端IP、主机、接口、端口

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

存储限制 - 最大临时存储和当前使用量
CPU限制 - 允许的最大处理时间
线程信息 - 活跃线程数和限制
队列状态 - 作业队列优先级和位置
工作负载组 - 工作负载管理组成员身份

Common Use Cases

常见用例

1. Current Job Monitoring

1. 当前作业监控

Get detailed information about your current connection's job

获取当前连接作业的详细信息

2. Performance Troubleshooting

2. 性能排查

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

识别跨子系统的顶级CPU消耗者
查找占用过多临时存储的作业
检测长时间运行的SQL语句
分析锁争用模式

3. Queue Management

3. 队列管理

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

查看作业队列中等待的作业及其优先级
跟踪作业队列状态（挂起、释放、已调度）
监控作业调度时间

4. User Activity Tracking

4. 用户活动跟踪

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

列出特定用户的所有作业
查找当前用户或工作站提交的作业
按用户跟踪活跃与排队作业

5. Storage Analysis

5. 存储分析

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

识别QTEMP使用量高的作业
监控临时存储趋势
跟踪峰值存储消耗

6. SQL Performance

6. SQL性能

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

查找活跃SQL语句及其执行时间
分析游标使用情况（CQE vs SQE）
监控SQL Server Mode连接
跟踪查询优化器活动

7. System Administration

7. 系统管理

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

获取系统作业信息（如用于IPL时间的SCPF）
监控QSQSRVR和主机服务器作业
跟踪预启动作业统计
分析工作负载组利用率

8. Elapsed Statistics

8. 耗时统计

Set baseline and measure performance deltas over time intervals

设置基准并测量时间间隔内的性能增量

9. Job Type Analysis

9. 作业类型分析

Filter by interactive, batch, prestart, or system jobs

按交互式、批处理、预启动或系统作业筛选

10. Lock Contention

10. 锁争用

Identify jobs experiencing database and non-database lock waits

识别遇到数据库与非数据库锁等待的作业

Filter Parameters (Performance Critical)

筛选参数（性能关键）

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

始终使用UDTF筛选参数（而非WHERE子句）以获得最佳性能：

JOB_INFO Filters

JOB_INFO筛选器

```
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
```

```
JOB_STATUS_FILTER
```
-
```
*ALL
```
、
```
*ACTIVE
```
、
```
*JOBQ
```
、
```
*OUTQ
```
```
JOB_TYPE_FILTER
```
-
```
*ALL
```
、
```
*BATCH
```
、
```
*INTERACT
```
```
JOB_SUBSYSTEM_FILTER
```
- 子系统名称或
```
*ALL
```
```
JOB_USER_FILTER
```
- 用户名、
```
*ALL
```
、
```
*USER
```
或USER特殊寄存器

JOB_SUBMITTER_FILTER

*ALL

、

*JOB

、

*USER

、

*WRKSTN

```
JOB_NAME_FILTER
```
- 作业名称或
```
*ALL
```

ACTIVE_JOB_INFO Filters

ACTIVE_JOB_INFO筛选器

```
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
```

```
RESET_STATISTICS
```
-
```
YES
```
/
```
NO
```
（建立测量基准）
```
SUBSYSTEM_LIST_FILTER
```
- 逗号分隔的子系统（最多25个）
```
JOB_NAME_FILTER
```
-
```
*
```
、
```
*ALL
```
、
```
*CURRENT
```
、
```
*SBS
```
、
```
*SYS
```
或作业名称
```
CURRENT_USER_LIST_FILTER
```
- 逗号分隔的用户（最多10个）
```
DETAILED_INFO
```
-
```
NONE
```
、
```
WORK
```
、
```
QTEMP
```
、
```
FULL
```
、
```
ALL
```

DETAILED_INFO Levels

DETAILED_INFO级别

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

级别	返回内容	权限	用例
`NONE`	基础作业信息	无	快速概览
`WORK`	+作业管理信息	无	作业配置、队列
`QTEMP`	+QTEMP_SIZE	*JOBCTL	存储分析
`FULL`	除QTEMP/HOST外的所有信息	SQL列需特殊权限	完整性能数据
`ALL`	完整信息	SQL列需特殊权限	完整SQL活动详情

SQL列权限： 需要QIBM_DB_SQLADM或QIBM_DB_SYSMON功能使用标识符

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

作业标识、状态、功能、CPU使用率、内存、I/O计数、耗时统计、SQL语句详情、游标指标、锁等待、客户端连接信息、QTEMP大小

CL Command Migration

CL命令迁移

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)

CL命令	SQL服务
WRKACTJOB	ACTIVE_JOB_INFO()
WRKUSRJOB	JOB_INFO() + 过滤系统作业
WRKSBSJOB	JOB_INFO(JOB_SUBSYSTEM_FILTER)
WRKSBMJOB	JOB_INFO(JOB_SUBMITTER_FILTER)

Best Practices

最佳实践

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

使用UDTF筛选器 - 始终使用参数筛选，而非WHERE子句
从最简开始 - 使用DETAILED_INFO => 'NONE'，根据需要添加详情
筛选子系统 - 指定子系统以减少扫描的行数
测量增量 - 使用RESET_STATISTICS获取耗时指标
排除系统作业 - 过滤JOB_TYPE <> 'SYS'以获取用户作业
限制结果 - 在大型系统上使用LIMIT子句
组合服务 - 与JOBLOG_INFO、PRESTART_JOB_INFO等关联使用
检查活跃SQL - 过滤SQL_STATEMENT_STATUS = 'ACTIVE'以查找运行中的查询

Quick Examples

快速示例

Get current job info

获取当前作业信息

sql

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

sql

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

Find top CPU consumers

查找顶级CPU消耗者

sql

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;

sql

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

查找作业队列中的作业

sql

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

sql

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

Find long-running SQL statements

查找长时间运行的SQL语句

sql

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;

sql

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

查找特定用户的作业

sql

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

sql

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

JOB_INFO参考文档 - 完整参数与列说明
ACTIVE_JOB_INFO参考文档 - 完整参数与列说明
SQL示例模板 - 可用查询示例
IBM ACTIVE_JOB_INFO - 增强历史
IBM JOB_INFO - 增强历史