Back to Details

database-performance

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

IBM i Database Performance

IBM i 数据库性能

Monitor database performance using index statistics, MTI analysis, database monitors, query supervisor thresholds, and materialized query table tracking.
使用索引统计信息、MTI 分析、数据库监控器、Query Supervisor 阈值和物化查询表跟踪来监控数据库性能。

Available Tools

可用工具

The 
ibmi
CLI is the primary tool for executing database performance queries. Set 
SKILL_DIR
to this skill's installed location (the directory containing this SKILL.md file):
bash
undefined
ibmi
CLI 是执行数据库性能查询的主要工具。将 
SKILL_DIR
设置为此 skill 的安装位置（包含此 SKILL.md 文件的目录）：
bash
undefined

SKILL_DIR = directory containing this SKILL.md

SKILL_DIR = directory containing this SKILL.md

Examples: ./skills/database-performance, ~/.claude/skills/database-performance

Examples: ./skills/database-performance, ~/.claude/skills/database-performance

List all database performance tools

List all database performance tools

ibmi tools --tools "$SKILL_DIR/tools/" --toolset database_performance_default
ibmi tools --tools "$SKILL_DIR/tools/" --toolset database_performance_default

Run a specific tool

Run a specific tool

ibmi tool get_index_statistics --tools "$SKILL_DIR/tools/"
ibmi tool get_index_statistics --tools "$SKILL_DIR/tools/"

Run with parameters

Run with parameters

ibmi tool get_mti_info --tools "$SKILL_DIR/tools/" --schema-name MYLIB --table-name ORDERS
ibmi tool get_mti_info --tools "$SKILL_DIR/tools/" --schema-name MYLIB --table-name ORDERS

Ad-hoc SQL for custom queries

Ad-hoc SQL for custom queries

ibmi sql "SELECT * FROM QSYS2.SYSINDEXSTAT WHERE TABLE_SCHEMA = 'MYLIB' ORDER BY QUERY_USE_COUNT DESC"
undefined
ibmi sql "SELECT * FROM QSYS2.SYSINDEXSTAT WHERE TABLE_SCHEMA = 'MYLIB' ORDER BY QUERY_USE_COUNT DESC"
undefined

Service Selection Guide

服务选择指南

Index Analysis

索引分析

  • QSYS2.SYSINDEXSTAT -- Index-level statistics: key columns, size, usage counts, build times
  • QSYS2.MTI_INFO() -- Maintained Temporary Indexes over a specific table
  • QSYS2.SYSINDEXSTAT — 索引级统计信息：键列、大小、使用次数和构建时间
  • QSYS2.MTI_INFO() — 特定表上的维护型临时索引

MTI Overhead

MTI 开销

  • QSYS2.SYSPARTITIONSTAT -- Partition-level MTI sizes to find tables with high overhead
  • QSYS2.SYSPARTITIONSTAT — 分区级 MTI 大小，用于查找开销较高的表

Database Monitoring

数据库监控

  • QSYS2.DATABASE_MONITOR_INFO -- Active and inactive database performance monitors
  • QSYS2.DATABASE_MONITOR_INFO — 活动和非活动的数据库性能监控器

Query Governance

查询治理

  • QSYS2.QUERY_SUPERVISOR -- Query supervisor threshold rules and filters
  • QSYS2.QUERY_SUPERVISOR — Query Supervisor 阈值规则和过滤器

Materialized Query Tables

物化查询表

  • QSYS2.SYSMQTSTAT -- MQT refresh status, usage counts, sizes, and maintenance settings
  • QSYS2.SYSMQTSTAT — MQT 刷新状态、使用次数、大小和维护设置

Key Capabilities

核心功能

Index Statistics & Optimization

索引统计与优化

  • Usage tracking -- Query use count and last query use for each index
  • Unused detection -- Find indexes never used by the optimizer (cleanup candidates)
  • Build history -- Last build time, type (rebuild vs. incremental), and degree of parallelism
  • Key analysis -- Number of key columns, column names, uniqueness, and sparseness
  • 使用情况跟踪 — 每个索引的查询使用次数和最后查询使用时间
  • 未使用检测 — 查找优化程序从未使用的索引（清理候选项）
  • 构建历史 — 最后构建时间、类型（重建或增量）以及并行度
  • 键分析 — 键列数量、列名称、唯一性和稀疏性

Maintained Temporary Index Analysis

维护型临时索引分析

  • Per-table MTIs -- List all MTIs on a specific table with usage counts and sizes
  • System-wide MTI overhead -- Find tables with the largest MTI footprints
  • Promotion candidates -- Identify frequently-used MTIs that should become permanent indexes
  • 按表分析 MTI — 列出特定表上的所有 MTI，以及使用次数和大小
  • 全系统 MTI 开销 — 查找 MTI 占用空间最大的表
  • 提升候选项 — 识别应转为永久索引的常用 MTI

Database Monitor Management

数据库监控器管理

  • Monitor inventory -- List all database monitors with status, type, and filters
  • Output tracking -- Monitor file locations, row counts, and data sizes
  • Filter review -- Job, user, and SQL code filters applied to each monitor
  • 监控器清单 — 列出所有数据库监控器及其状态、类型和过滤器
  • 输出跟踪 — 监控器文件位置、行数和 data 大小
  • 过滤器审查 — 每个监控器所应用的作业、用户和 SQL 代码过滤器

Query Supervisor

Query Supervisor

  • Threshold rules -- All defined thresholds with types, values, and detection frequency
  • Scope filters -- Job names, users, and subsystems targeted by each threshold
  • 阈值规则 — 所有已定义阈值的类型、值和检测频率
  • 范围过滤器 — 每个阈值所针对的作业名、用户和子系统

Materialized Query Table Tracking

物化查询表跟踪

  • Refresh status -- Last refresh time and whether MQTs are enabled
  • Usage statistics -- Query use count and statistics count per MQT
  • Maintenance mode -- Immediate, deferred, or user-controlled refresh settings
  • 刷新状态 — 最后刷新时间以及 MQT 是否启用
  • 使用统计 — 每个 MQT 的查询使用次数和统计计数
  • 维护模式 — 立即、延迟或由用户控制的刷新设置

Common Use Cases

常见使用场景

  1. Index audit -- Review all indexes on a table to assess coverage
  2. Unused index cleanup -- Find and remove indexes not used by the optimizer
  3. MTI promotion -- Identify MTIs that should become permanent indexes
  4. MTI overhead -- Find tables where MTI maintenance consumes excessive resources
  5. Monitor review -- Check what database monitors are running and their output
  6. Threshold audit -- Review query supervisor rules for appropriateness
  7. MQT health -- Verify MQTs are being refreshed and used by the optimizer
  1. 索引审计 — 审查某个表上的所有索引以评估覆盖情况
  2. 未使用索引清理 — 查找并删除优化程序未使用的索引
  3. MTI 提升 — 识别应转为永久索引的 MTI
  4. MTI 开销 — 查找 MTI 维护消耗过多资源的表
  5. 监控器审查 — 检查正在运行哪些数据库监控器及其输出
  6. 阈值审计 — 审查 Query Supervisor 规则是否适当
  7. MQT 健康状态 — 验证 MQT 是否正常刷新并被优化程序使用

Quick Examples

快速示例

View index statistics for a library

查看某个库的索引统计信息

bash
ibmi tool get_index_statistics --tools "$SKILL_DIR/tools/" --schema-filter MYLIB
bash
ibmi tool get_index_statistics --tools "$SKILL_DIR/tools/" --schema-filter MYLIB

Find unused indexes

查找未使用的索引

bash
ibmi tool get_unused_indexes --tools "$SKILL_DIR/tools/" --schema-filter MYLIB --unused-days 90
bash
ibmi tool get_unused_indexes --tools "$SKILL_DIR/tools/" --schema-filter MYLIB --unused-days 90

Check MTIs on a specific table

检查特定表上的 MTI

bash
ibmi tool get_mti_info --tools "$SKILL_DIR/tools/" --schema-name MYLIB --table-name ORDERS
bash
ibmi tool get_mti_info --tools "$SKILL_DIR/tools/" --schema-name MYLIB --table-name ORDERS

Find tables with high MTI overhead

查找 MTI 开销较高的表

bash
ibmi tool get_tables_with_high_mti --tools "$SKILL_DIR/tools/" --schema-filter MYLIB --min-mti-size 1048576
bash
ibmi tool get_tables_with_high_mti --tools "$SKILL_DIR/tools/" --schema-filter MYLIB --min-mti-size 1048576

Review query supervisor thresholds

审查 Query Supervisor 阈值

bash
ibmi tool get_query_supervisor_thresholds --tools "$SKILL_DIR/tools/"
bash
ibmi tool get_query_supervisor_thresholds --tools "$SKILL_DIR/tools/"

Pre-built Tools

预构建工具

The 
tools/database-performance.yaml
file provides 7 ready-to-use tools:
ToolDescription
get_index_statistics
Index usage, key columns, size, and build history
get_unused_indexes
Indexes not used by the optimizer
get_mti_info
Maintained Temporary Indexes on a specific table
get_database_monitor_info
Active and inactive database monitors
get_query_supervisor_thresholds
Query supervisor rules and filters
get_mqt_statistics
Materialized Query Table refresh and usage stats
get_tables_with_high_mti
Tables with largest MTI overhead
bash
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
tools/database-performance.yaml
文件提供 7 个可直接使用的工具：
工具描述
get_index_statistics
索引使用情况、键列、大小和构建历史
get_unused_indexes
优化程序未使用的索引
get_mti_info
特定表上的维护型临时索引
get_database_monitor_info
活动和非活动的数据库监控器
get_query_supervisor_thresholds
Query Supervisor 规则和过滤器
get_mqt_statistics
物化查询表的刷新和使用统计
get_tables_with_high_mti
MTI 开销最大的表
bash
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

参考文档