| name | database-application |
| description | Analyze IBM i SQL application behavior including error logs, statement parsing, SQLCODE lookups, and system limits. Use when user asks about: (1) SQL errors in applications or error log analysis, (2) most frequent SQL errors, (3) parsing SQL statements to find referenced objects, (4) looking up SQLCODE meanings, (5) properly delimiting SQL identifiers, (6) system size limits for files or indexes, or (7) objects approaching maximum capacity. |
IBM i Database Application
Analyze SQL application behavior including error logs, statement parsing, SQLCODE reference, SQL naming utilities, and system limit monitoring.
Available Tools
The ibmi CLI is the primary tool for executing database application 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_application_default
ibmi tool list_sql_error_log --tools "$SKILL_DIR/tools/"
ibmi tool get_sqlcode_info --tools "$SKILL_DIR/tools/" --sqlcode -204
ibmi sql "SELECT * FROM QSYS2.SQL_ERROR_LOG ORDER BY LOGGED_TIME DESC FETCH FIRST 10 ROWS ONLY"
Service Selection Guide
SQL Error Analysis
- QSYS2.SQL_ERROR_LOG -- Logged SQL errors with SQLCODE, program, statement text, timestamps
- SYSTOOLS.SQLCODE_INFO() -- Message ID, text, and help for a given SQLCODE
SQL Statement Analysis
- QSYS2.PARSE_STATEMENT() -- Extract referenced tables, views, schemas from SQL text
- QSYS2.DELIMIT_NAME() -- Properly quote/delimit SQL identifiers
System Limits
- QSYS2.SYSLIMITS -- Objects approaching system-defined size or capacity limits
Key Capabilities
SQL Error Log Analysis
- Error listing -- Recent SQL errors with SQLCODE, SQLSTATE, program, job, and statement text
- Error summary -- Aggregate error counts by SQLCODE to find the most frequent issues
- Program filtering -- Focus on errors from a specific program or library
- Occurrence tracking -- See how many times an error has occurred and when it first appeared
SQL Statement Parsing
- Object extraction -- Parse a SQL statement to find all referenced tables, views, and schemas
- Usage types -- Determine how each object is used (SELECT, INSERT, UPDATE, etc.)
- Static analysis -- Analyze SQL embedded in programs without executing it
SQLCODE Reference
- Message lookup -- Get the message ID, text, and detailed help for any SQLCODE
- Diagnosis -- Understand what a SQL error means and how to resolve it
SQL Naming Utilities
- Identifier delimiting -- Determine if a name needs quoting for safe use in dynamic SQL
- Reserved word detection -- Identify names that conflict with SQL reserved words
System Limit Monitoring
- Limit inventory -- All objects with tracked limits (file size, index size, journal, etc.)
- Near-maximum detection -- Find objects approaching their system-defined maximums
- Proactive alerts -- Identify capacity issues before they cause failures
Common Use Cases
- Error investigation -- Find SQL errors occurring in production applications
- Error trends -- Identify the most frequent SQL errors across the system
- Impact analysis -- Parse SQL to find all tables referenced by a statement
- Error diagnosis -- Look up what an SQLCODE means and how to fix it
- Dynamic SQL safety -- Determine if identifiers need delimiting
- Capacity monitoring -- Find files or indexes approaching size limits
- Proactive maintenance -- Alert before objects hit system maximums
Quick Examples
View recent SQL errors
ibmi tool list_sql_error_log --tools "$SKILL_DIR/tools/"
Errors for a specific program
ibmi tool list_sql_error_log --tools "$SKILL_DIR/tools/" --program-name MYPGM --program-library MYLIB
Look up an SQLCODE
ibmi tool get_sqlcode_info --tools "$SKILL_DIR/tools/" --sqlcode -204
Parse a SQL statement
ibmi tool parse_sql_statement --tools "$SKILL_DIR/tools/" --sql-statement "SELECT * FROM MYLIB.ORDERS JOIN MYLIB.CUSTOMERS ON ORDERS.CUSTID = CUSTOMERS.ID"
Find objects near capacity limits
ibmi tool get_system_limits_near_max --tools "$SKILL_DIR/tools/" --threshold-pct 75
Pre-built Tools
The tools/database-application.yaml file provides 7 ready-to-use tools:
| Tool | Description |
|---|
list_sql_error_log | SQL error log entries with program and statement details |
get_sql_error_summary | Error counts grouped by SQLCODE |
parse_sql_statement | Extract referenced objects from SQL text |
get_sqlcode_info | Look up message text for a SQLCODE |
delimit_name | Check if a SQL identifier needs quoting |
get_system_limits | System limit tracking for objects and jobs |
get_system_limits_near_max | Objects approaching their capacity maximums |
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
