| name | database-performance |
| description | Monitor IBM i database performance including index statistics, maintained temporary indexes (MTIs), database monitors, query supervisor thresholds, and materialized query tables. Use when user asks about: (1) index usage or unused indexes, (2) maintained temporary indexes and whether to create permanent indexes, (3) database monitor configuration, (4) query supervisor thresholds, (5) MQT statistics and refresh status, or (6) tables with high MTI overhead. |
IBM i Database Performance
Monitor database performance using index statistics, MTI analysis, database monitors, query supervisor thresholds, and materialized query table tracking.
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):
ibmi tools --tools "$SKILL_DIR/tools/" --toolset database_performance_default
ibmi tool get_index_statistics --tools "$SKILL_DIR/tools/"
ibmi tool get_mti_info --tools "$SKILL_DIR/tools/" --schema-name MYLIB --table-name ORDERS
ibmi sql "SELECT * FROM QSYS2.SYSINDEXSTAT WHERE TABLE_SCHEMA = 'MYLIB' ORDER BY QUERY_USE_COUNT DESC"
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
MTI Overhead
- QSYS2.SYSPARTITIONSTAT -- Partition-level MTI sizes to find tables with high overhead
Database Monitoring
- QSYS2.DATABASE_MONITOR_INFO -- Active and inactive database performance monitors
Query Governance
- QSYS2.QUERY_SUPERVISOR -- Query supervisor threshold rules and filters
Materialized Query Tables
- QSYS2.SYSMQTSTAT -- MQT refresh status, usage counts, sizes, and maintenance settings
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
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
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
Common Use Cases
- Index audit -- Review all indexes on a table to assess coverage
- Unused index cleanup -- Find and remove indexes not used by the optimizer
- MTI promotion -- Identify MTIs that should become permanent indexes
- MTI overhead -- Find tables where MTI maintenance consumes excessive resources
- Monitor review -- Check what database monitors are running and their output
- Threshold audit -- Review query supervisor rules for appropriateness
- MQT health -- Verify MQTs are being refreshed and used by the optimizer
Quick Examples
View index statistics for a library
ibmi tool get_index_statistics --tools "$SKILL_DIR/tools/" --schema-filter MYLIB
Find unused indexes
ibmi tool get_unused_indexes --tools "$SKILL_DIR/tools/" --schema-filter MYLIB --unused-days 90
Check MTIs on a specific table
ibmi tool get_mti_info --tools "$SKILL_DIR/tools/" --schema-name MYLIB --table-name ORDERS
Find tables with high MTI overhead
ibmi tool get_tables_with_high_mti --tools "$SKILL_DIR/tools/" --schema-filter MYLIB --min-mti-size 1048576
Review query supervisor thresholds
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:
| Tool | Description |
|---|
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 |
ibmi tool <tool_name> --tools "$SKILL_DIR/tools/"
ibmi tool <tool_name> --tools "$SKILL_DIR/tools/" --dry-run
ibmi tools show <tool_name> --tools "$SKILL_DIR/tools/"
Reference Documentation
