IBM i Database Performance

Available Tools

ibmi

SKILL_DIR

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

# List all database performance tools
ibmi tools --tools "$SKILL_DIR/tools/" --toolset database_performance_default

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

# Run with parameters
ibmi tool get_mti_info --tools "$SKILL_DIR/tools/" --schema-name MYLIB --table-name ORDERS

# Ad-hoc SQL for custom queries
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

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

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

tools/database-performance.yaml

get_index_statistics

get_unused_indexes

get_mti_info

get_database_monitor_info

get_query_supervisor_thresholds

get_mqt_statistics

get_tables_with_high_mti

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

• Database Performance Services Catalog -- Available SQL services
• Example SQL Patterns -- Working query examples
• IBM SYSINDEXSTAT -- View documentation
• IBM MTI_INFO -- Function documentation
• IBM DATABASE_MONITOR_INFO -- View documentation