ajshedivy/database-performance
skills/database-performance/SKILL.md
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.
$ install --global
skillsauthnpx skillsauth add ajshedivy/ibmi-agent-skills database-performanceInstall this skill globally with one command. Works with Claude Code, Cursor, and Windsurf.
Security Scan Results
3 of 9 scanners reported clean
Some scanners were skipped, did not run, or reported a non-clean status. Review each row below.
3
Scanners Passed
9
Scanners in report
Clean
TrivyContainer and dependency vulnerability scanner
95%
Clean
SemgrepStatic code analysis for vulnerabilities
95%
Clean
mcp-scan (Snyk)Model Context Protocol security validation
95%
Skipped
Snyk (dep)Open source security scanning
50%
Skipped
Socket.devSupply chain security analysis
50%
Skipped
VirusTotalMulti-engine malware detection
50%
Skipped
CrowdStrikeAdvanced threat intelligence
50%
Skipped
OSV-ScannerOpen Source Vulnerability database check
50%
Skipped
OWASP Dep-Check
50%
Last scanned: Apr 25, 2026, 5:30 AM49.8s4 files scanned
SKILL.md
- 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):
# 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
- 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/" # 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
Related Skills
ajshedivy/work-management
tools
VerifiedTrustedCommunity
Query, monitor, and analyze jobs on IBM i using SQL table functions via the ibmi CLI. Use when user asks about: (1) finding jobs by status, user, subsystem, or type, (2) monitoring active job performance (CPU, I/O, memory), (3) detecting long-running SQL statements, (4) analyzing lock contention, (5) checking job queues, (6) scheduled jobs, (7) job logs, (8) replacing WRKACTJOB, WRKUSRJOB, WRKSBSJOB, WRKSBMJOB commands, or (9) any IBM i work management task.
2SKILL.mdUpdated Apr 2, 2026
ajshedivy/system-health
testing
VerifiedTrustedCommunity
Monitor IBM i system health including CPU, memory, disk, ASPs, system limits, and network status via SQL services. Use when user asks about: (1) CPU utilization or system status, (2) memory pool sizes or page faults, (3) disk capacity or ASP usage, (4) system limits approaching thresholds, (5) TCP/IP connections and network status, (6) system activity overview, (7) replacing WRKSYSSTS, WRKDSKSTS, WRKTCPSTS commands, or (8) any system health monitoring task.
2SKILL.mdUpdated Apr 2, 2026
ajshedivy/storage
development
VerifiedTrustedCommunity
Monitor and analyze IBM i storage resources including ASPs, disk units, temporary storage, user storage consumption, and NVMe devices via SQL services. Use when user asks about: (1) ASP capacity, usage, or health, (2) disk unit status or I/O performance, (3) temporary storage consumption by jobs, (4) storage used per user profile, (5) NVMe device health, (6) IASP vary operations, or (7) replacing WRKDSKSTS, WRKSYSSTS storage info, or WRKSTG commands.
2SKILL.mdUpdated Apr 2, 2026
ajshedivy/spool
testing
VerifiedTrustedCommunity
Manage and analyze spooled files, output queues, and printer configurations on IBM i via SQL services. Use when user asks about: (1) listing or searching output queues, (2) viewing spooled file entries by queue, user, or status, (3) reading spool file content, (4) identifying top spool consumers or old spool files, (5) printer file definitions, (6) spool storage analysis, (7) replacing WRKSPLF, WRKOUTQ, WRKOBJLCK commands, or (8) any spool file management task.
2SKILL.mdUpdated Apr 2, 2026