skills.rest
ajshedivy
ajshedivyCOMMUNITY
Explore all skills by this author →
GitHub

database-performance

Aggregate IBM i index usage, MTI overhead, and monitor configurations to identify database performance issues.

3|1|Updated Feb 3, 2026
Search Tags:#database-performance#ibmi#index-statistics#mti-info#database-monitor-info#query-supervisor#mqt-statistics
One-click install
npx skills add https://github.com/ajshedivy/ibmi-agent-skills --skill database-performance-ajshedivy
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: database-performance
Source: https://github.com/ajshedivy/ibmi-agent-skills/tree/main/skills/database-performance
Command: npx skills add https://github.com/ajshedivy/ibmi-agent-skills --skill database-performance-ajshedivy

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

IBM i database performance planning and troubleshooting can be time-consuming; this skill consolidates critical metrics from index usage, MTIs, monitors, and MQTs to help teams quickly identify bottlenecks and optimization opportunities.

Core Features & Use Cases

  • Index statistics & optimization: Inspect QSYS2.SYSINDEXSTAT for usage counts, last build times, and identify unused or high-impact indexes.
  • MTI overhead analysis: Analyze Maintained Temporary Index sizes to pinpoint overhead and determine MTI-to-index promotion opportunities.
  • Database monitor review: List and assess active/inactive monitors, statuses, filters, and output locations to ensure effective monitoring.
  • Query supervisor audits: Review threshold rules to validate performance guardrails and tune queries.
  • MQT statistics: Track Materialized Query Tables status, usage counts, sizes, and refresh timings to measure effectiveness.
  • Cross-library health checks: Run end-to-end checks across libraries to surface bottlenecks like MTI overhead or underused indexes.

Quick Start

Use ibmi tools to list index statistics and MTI information for your libraries to begin monitoring database health.

Frequently Asked Questions about database-performance

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I check IBM i database performance issues across my libraries?▼

Check IBM i database performance issues by aggregating index usage, MTI overhead, and monitor configurations. Querying catalog views like SYSINDEXSTAT and MTI_INFO surfaces bottlenecks, underused indexes, and optimization opportunities across libraries.

What is MTI overhead and how does it impact IBM i database performance?▼

MTI overhead refers to the performance impact of Maintained Temporary Indexes. Analyzing MTI_INFO sizes helps pinpoint overhead, identify MTI-to-index promotion opportunities, and resolve bottlenecks affecting query execution speed.

How do I find unused or high-impact indexes on IBM i?▼

Find unused or high-impact indexes on IBM i by inspecting the QSYS2.SYSINDEXSTAT catalog view. This reveals index usage counts and last build times to help teams identify indexes for removal or optimization.

Can I audit database monitor configurations and query supervisor thresholds on IBM i?▼

Audit database monitor configurations and query supervisor thresholds using DATABASE_MONITOR_INFO and QUERY_SUPERVISOR catalog views. This validates active monitor statuses, filters, output locations, and performance guardrails for tuning queries.

Does this database performance skill require any specific dependencies to run?▼

The database performance skill has no external dependencies. It relies solely on querying built-in IBM i catalog views via the ibmi CLI to generate actionable insights for DBAs evaluating database health.

How do I track Materialized Query Table effectiveness on IBM i?▼

Track Materialized Query Table effectiveness on IBM i by querying the SYSMQTSTAT catalog view. This monitors MQT status, usage counts, sizes, and refresh timings to measure their impact on database performance.

Related Skills

Explore More Data & Analytics Skills→
ajshedivyajshedivy
3

performance

Collect and analyze IBM i performance metrics via SQL services.

Community
Intermediate
Mentor-WizardMentor-Wizard
10

database-optimizer

Analyze slow queries and execution plans for PostgreSQL and MySQL.

Official
Advanced
ajshedivyajshedivy
3

system-health

Monitor IBM i CPU, memory, disk, ASP, and network metrics via QSYS2 views.

Community
Intermediate
shalevaminshalevamin

database-optimizer

Analyze execution plans and optimize PostgreSQL and MySQL database performance.

Community
Advanced