Execute a read-only SQL SELECT query against the IBM Db2i database. Only SELECT statements are allowed for security. Results are limited by default to prevent large result sets.

List all schemas (libraries) in the IBM Db2i database. Optionally filter by name pattern using * as wildcard.

List all tables in a schema (library). Uses the system's default schema if not provided. Optionally filter by name pattern using * as wildcard.

Find tables by name or description text across libraries. Matches TABLE_NAME, SYSTEM_TABLE_NAME, and TABLE_TEXT. Use * as a wildcard. When a schema allowlist is configured, only those libraries are searched. Otherwise system libraries (Q* and SYS*) are skipped unless include_system is true.

Find columns by name or description text across libraries. Matches COLUMN_NAME, SYSTEM_COLUMN_NAME, and COLUMN_TEXT. Use * as a wildcard. When a schema allowlist is configured, only those libraries are searched. Otherwise system libraries (Q* and SYS*) are skipped unless include_system is true.

Get detailed column information for a specific table including data types, lengths, nullability, defaults, and CCSID. Uses the system's default schema if not provided.

List all views in a schema (library). Uses the system's default schema if not provided. Optionally filter by name pattern using * as wildcard.

List all indexes for a specific table including uniqueness and column information. Uses the system's default schema if not provided.

Get all constraints (primary keys, foreign keys, unique constraints) for a specific table. Uses the system's default schema if not provided.

List SQL procedures and functions in a library from QSYS2.SYSROUTINES, one row per specific routine, so overloads appear once each. Shows the type (procedure, scalar function, or table function), language, external program, SQL data access, result sets, and text. Use describe_routine for parameters and a call template. Uses the system's default schema if not provided.

Describe an SQL procedure or function from QSYS2.SYSPARMS: parameters in order with mode, data type, and default; the return value of a scalar function; and the result columns of a table function. An overloaded name returns every overload unless specific_name picks one. call_template is a statement with a ? marker per parameter. callable_with_execute_query says whether execute_query can run it: procedures (CALL) and functions that modify SQL data cannot, and note says why. Uses the system's default schema if not provided.

Check a SQL statement without running it. Parses it with QSYS2.PARSE_STATEMENT and checks that referenced tables, columns, and qualified routines exist in the catalog. Also reports read-only and schema-allowlist findings.

Return the SQL DDL that recreates a database object, using QSYS2.GENERATE_SQL. Does not run the generated statements. Requires IBM i 7.3 or later.

List views, indexes, triggers, and other objects that depend on a table, using SYSTOOLS.RELATED_OBJECTS. Requires IBM i 7.3 Technology Refresh 9, IBM i 7.4 Technology Refresh 3, or a later release.

List the physical data files in a library with their journal, journal library, journal images, omitted entries, and whether they have a primary key, using QSYS2.OBJECT_STATISTICS. needs_attention is true when a table is not journaled, or has no primary key and does not journal both images, which journal-based replication tools need. Requires IBM i 7.3 Technology Refresh 2 or later.

Find IBM i SQL services (views, table functions, procedures, and more in QSYS2 and SYSTOOLS) in the catalog QSYS2.SERVICES_INFO, with the release that added each one and an example query. Call it before writing SQL that uses an IBM i service, instead of guessing names and parameters. With no query and no category, it lists the categories with a count each. This tool only reads the catalog. Running an example with execute_query still needs the IBM i authority the service documents and, when QUERY_ALLOWED_SCHEMAS is set, the service's schema in that list. Table functions must be qualified, as in TABLE(QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'NONE')), with plain values as arguments.

List the indexes the query optimizer asked for on the tables of a library, from the IBM i index advisor (QSYS2.SYSIXADV). Rows with the same table, key columns, and index type are merged into one, with counts summed and reason codes described. Sorted by mti_used, then times_advised: advice where the optimizer kept building and reusing a maintained temporary index (MTI) is the strongest signal. key_columns are in CREATE INDEX order and can end in DESC. last_advised and since are in the system's local time. This tool only reads the advice; it does not create indexes.

Profile a table for ETL work: row count, deleted rows, size, and last change from QSYS2.SYSTABLESTAT, plus per-column distinct count, null count, and low and high values. By default column numbers come from stored statistics in QSYS2.SYSCOLUMNSTAT (low and high are the second-lowest and second-highest values), and columns without collected statistics report source "none". Set compute to true to scan the table for exact numbers on up to 20 columns. Masked columns keep their counts and return no values.

List business entities, row filters most queries need (such as leaving out deleted rows), table and column descriptions, and relations that the catalog does not declare as foreign keys. Filter by entity or table. Omit both to return every annotation loaded from MCP_CUSTOM_TOOLS. An unknown entity name falls back to names that partly match it, and when nothing matches, the result lists the loaded entity names.