Back to Details

database-plan-cache

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

IBM i Database Plan Cache

IBM i 数据库计划缓存

Explore and manage the SQL plan cache including service discovery, snapshot analysis, event monitor tracking, and management procedure references.
探索和管理 SQL 计划缓存，包括服务发现、快照分析、事件监视器跟踪以及管理过程引用。

Available Tools

可用工具

The 
ibmi
CLI is the primary tool for executing plan cache queries. Set 
SKILL_DIR
to this skill's installed location (the directory containing this SKILL.md file):
bash
undefined
ibmi
CLI 是执行计划缓存查询的主要工具。将 
SKILL_DIR
设置为此 skill 的安装位置（包含此 SKILL.md 文件的目录）：
bash
undefined

SKILL_DIR = directory containing this SKILL.md

SKILL_DIR = directory containing this SKILL.md

Examples: ./skills/database-plan-cache, ~/.claude/skills/database-plan-cache

Examples: ./skills/database-plan-cache, ~/.claude/skills/database-plan-cache

List all plan cache tools

List all plan cache tools

ibmi tools --tools "$SKILL_DIR/tools/" --toolset database_plan_cache_default
ibmi tools --tools "$SKILL_DIR/tools/" --toolset database_plan_cache_default

Run a specific tool

Run a specific tool

ibmi tool list_plan_cache_services --tools "$SKILL_DIR/tools/"
ibmi tool list_plan_cache_services --tools "$SKILL_DIR/tools/"

Run with parameters

Run with parameters

ibmi tool get_plan_cache_procedure_details --tools "$SKILL_DIR/tools/" --procedure-name DUMP_PLAN_CACHE
ibmi tool get_plan_cache_procedure_details --tools "$SKILL_DIR/tools/" --procedure-name DUMP_PLAN_CACHE

Ad-hoc SQL for plan cache analysis

Ad-hoc SQL for plan cache analysis

ibmi sql "SELECT * FROM QSYS2.SERVICES_INFO WHERE SERVICE_CATEGORY = 'DATABASE-PLAN CACHE'"

Plan cache CALL operations require a connection that supports write operations (the default `dev` system, not a `READ_ONLY` system).
ibmi sql "SELECT * FROM QSYS2.SERVICES_INFO WHERE SERVICE_CATEGORY = 'DATABASE-PLAN CACHE'"

计划缓存 CALL 操作需要支持写操作的连接（默认的 `dev` 系统，而不是 `READ_ONLY` 系统）。

Service Selection Guide

服务选择指南

Service Discovery

服务发现

  • QSYS2.SERVICES_INFO -- List all plan cache services with schema, type, and release info
  • QSYS2.SYSPARMS / SYSROUTINES -- Procedure parameter details for plan cache procedures
  • QSYS2.SERVICES_INFO -- 列出所有计划缓存服务及其 schema、类型和发行版本信息
  • QSYS2.SYSPARMS / SYSROUTINES -- 计划缓存过程的参数详细信息

Snapshot Management

快照管理

  • QSYS2.DUMP_PLAN_CACHE (PROCEDURE) -- Snapshot entire plan cache to a file
  • QSYS2.DUMP_PLAN_CACHE_TOPN (PROCEDURE) -- Snapshot top N most expensive queries
  • QSYS2.DUMP_PLAN_CACHE_PROPERTIES (PROCEDURE) -- Snapshot plan cache configuration
  • QSYS2.DUMP_PLAN_CACHE（PROCEDURE）-- 将整个计划缓存导出为快照文件
  • QSYS2.DUMP_PLAN_CACHE_TOPN（PROCEDURE）-- 将开销最高的 N 个查询导出为快照
  • QSYS2.DUMP_PLAN_CACHE_PROPERTIES（PROCEDURE）-- 导出计划缓存配置快照

Event Monitors

事件监视器

  • QSYS2.START_PLAN_CACHE_EVENT_MONITOR (PROCEDURE) -- Begin monitoring plan cache events
  • QSYS2.END_PLAN_CACHE_EVENT_MONITOR (PROCEDURE) -- Stop a specific event monitor
  • QSYS2.END_ALL_PLAN_CACHE_EVENT_MONITORS (PROCEDURE) -- Stop all event monitors
  • QSYS2.START_PLAN_CACHE_EVENT_MONITOR（PROCEDURE）-- 开始监视计划缓存事件
  • QSYS2.END_PLAN_CACHE_EVENT_MONITOR（PROCEDURE）-- 停止特定事件监视器
  • QSYS2.END_ALL_PLAN_CACHE_EVENT_MONITORS（PROCEDURE）-- 停止所有事件监视器

Plan Cache Control

计划缓存控制

  • QSYS2.CHANGE_PLAN_CACHE_SIZE (PROCEDURE) -- Resize plan cache memory
  • QSYS2.CLEAR_PLAN_CACHE (PROCEDURE) -- Remove all cached plans
  • QSYS2.CHANGE_PLAN_CACHE_SIZE（PROCEDURE）-- 调整计划缓存内存大小
  • QSYS2.CLEAR_PLAN_CACHE（PROCEDURE）-- 清除所有已缓存的计划

Snapshot Import/Export

快照导入/导出

  • QSYS2.IMPORT_PC_SNAPSHOT (PROCEDURE) -- Import a previously exported snapshot
  • QSYS2.IMPORT_PC_EVENT_MONITOR (PROCEDURE) -- Import event monitor data
  • QSYS2.REMOVE_PC_SNAPSHOT (PROCEDURE) -- Remove an imported snapshot
  • QSYS2.REMOVE_PC_EVENT_MONITOR (PROCEDURE) -- Remove imported event monitor data
  • QSYS2.IMPORT_PC_SNAPSHOT（PROCEDURE）-- 导入之前导出的快照
  • QSYS2.IMPORT_PC_EVENT_MONITOR（PROCEDURE）-- 导入事件监视器数据
  • QSYS2.REMOVE_PC_SNAPSHOT（PROCEDURE）-- 删除已导入的快照
  • QSYS2.REMOVE_PC_EVENT_MONITOR（PROCEDURE）-- 删除已导入的事件监视器数据

Key Capabilities

核心能力

Service Discovery

服务发现

  • Service catalog -- List all 14 plan cache services with their types and minimum releases
  • Parameter reference -- Get exact parameter names, types, and modes for any procedure
  • SQL syntax generation -- Ready-to-use CALL statements for all common operations
  • 服务目录 -- 列出全部 14 个计划缓存服务及其类型和最低发行版本
  • 参数参考 -- 获取任何过程的准确参数名称、类型和模式
  • SQL 语法生成 -- 为所有常见操作生成可直接使用的 CALL 语句

Snapshot Analysis

快照分析

  • Snapshot inventory -- Find existing plan cache snapshot files across all libraries
  • Snapshot metadata -- Get size, row count, and creation date for a specific snapshot
  • Snapshot querying -- Once snapshots exist, query them with ad-hoc SQL
  • 快照清单 -- 查找所有 library 中现有的计划缓存快照文件
  • 快照元数据 -- 获取特定快照的大小、行数和创建日期
  • 快照查询 -- 快照存在后，可使用即席 SQL 对其进行查询

Plan Cache Sizing

计划缓存大小调整

  • QSQSRVR monitoring -- Track memory usage of SQL server jobs that manage the plan cache
  • Resource consumption -- CPU, disk I/O, and temporary storage per server job
  • QSQSRVR 监控 -- 跟踪管理计划缓存的 SQL 服务器作业内存使用情况
  • 资源消耗 -- 每个服务器作业的 CPU、磁盘 I/O 和临时存储空间使用情况

Event Monitor Tracking

事件监视器跟踪

  • Active monitors -- List running database monitors including plan cache event monitors
  • Monitor output -- Track output file locations, row counts, and data sizes
  • 活动监视器 -- 列出正在运行的数据库监视器，包括计划缓存事件监视器
  • 监视器输出 -- 跟踪输出文件位置、行数和 数据大小

Common Use Cases

常见使用场景

  1. Service discovery -- Find available plan cache management procedures
  2. Snapshot review -- Find and examine existing plan cache snapshot files
  3. Top query analysis -- Dump top N queries by time, then analyze the snapshot
  4. Plan cache sizing -- Monitor QSQSRVR job memory to assess plan cache pressure
  5. Event monitoring -- Set up and track plan cache event monitors
  6. Procedure reference -- Get exact parameter syntax for plan cache CALL statements
  7. Cross-system comparison -- Import snapshots from another system for comparison
  1. 服务发现 -- 查找可用的计划缓存管理过程
  2. 快照审查 -- 查找并检查现有的计划缓存快照文件
  3. Top 查询分析 -- 按耗时导出 Top N 查询，然后分析快照
  4. 计划缓存大小调整 -- 监控 QSQSRVR 作业内存，以评估计划缓存压力
  5. 事件监视 -- 设置并跟踪计划缓存事件监视器
  6. 过程参考 -- 获取计划缓存 CALL 语句的确切参数语法
  7. 跨系统比较 -- 导入其他系统的快照进行比较

Quick Examples

快速示例

List all plan cache services

列出所有计划缓存服务

bash
ibmi tool list_plan_cache_services --tools "$SKILL_DIR/tools/"
bash
ibmi tool list_plan_cache_services --tools "$SKILL_DIR/tools/"

Get procedure parameters

获取过程参数

bash
ibmi tool get_plan_cache_procedure_details --tools "$SKILL_DIR/tools/" --procedure-name DUMP_PLAN_CACHE_TOPN
bash
ibmi tool get_plan_cache_procedure_details --tools "$SKILL_DIR/tools/" --procedure-name DUMP_PLAN_CACHE_TOPN

Find existing snapshots

查找现有快照

bash
ibmi tool list_plan_cache_snapshots --tools "$SKILL_DIR/tools/"
bash
ibmi tool list_plan_cache_snapshots --tools "$SKILL_DIR/tools/"

Get management SQL syntax

获取管理 SQL 语法

bash
ibmi tool get_plan_cache_management_sql --tools "$SKILL_DIR/tools/"
bash
ibmi tool get_plan_cache_management_sql --tools "$SKILL_DIR/tools/"

Dump top 50 queries (via ad-hoc SQL)

导出耗时最长的 50 个查询（通过即席 SQL）

sql
CALL QSYS2.DUMP_PLAN_CACHE_TOPN(
  FILESCHEMA => 'MYLIB',
  FILENAME => 'PCTOP50',
  TOPN => 50,
  CATEGORY => 'TOTAL_TIME');
sql
CALL QSYS2.DUMP_PLAN_CACHE_TOPN(
  FILESCHEMA => 'MYLIB',
  FILENAME => 'PCTOP50',
  TOPN => 50,
  CATEGORY => 'TOTAL_TIME');

Query a snapshot (via ad-hoc SQL)

查询快照（通过即席 SQL）

sql
SELECT QQJOB, QQUSER, QQUCNT, QQETIM, QQSTIM
  FROM MYLIB.PCTOP50
  ORDER BY QQETIM DESC
  FETCH FIRST 20 ROWS ONLY;
sql
SELECT QQJOB, QQUSER, QQUCNT, QQETIM, QQSTIM
  FROM MYLIB.PCTOP50
  ORDER BY QQETIM DESC
  FETCH FIRST 20 ROWS ONLY;

Pre-built Tools

预置工具

The 
tools/database-plan-cache.yaml
file provides 7 ready-to-use tools:
ToolDescription
list_plan_cache_services
All plan cache services from SERVICES_INFO
get_plan_cache_procedure_details
Parameter info for a specific procedure
list_plan_cache_snapshots
Find existing snapshot files across libraries
get_plan_cache_snapshot_info
Metadata for a specific snapshot file
list_plan_cache_event_monitors
Active and inactive database monitors
get_plan_cache_size_info
QSQSRVR job memory and resource usage
get_plan_cache_management_sql
Ready-to-use CALL statements for all operations
bash
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
tools/database-plan-cache.yaml
文件提供了 7 个可直接使用的工具：
工具描述
list_plan_cache_services
从 SERVICES_INFO 获取所有计划缓存服务
get_plan_cache_procedure_details
获取特定过程的参数信息
list_plan_cache_snapshots
查找所有 library 中现有的快照文件
get_plan_cache_snapshot_info
获取特定快照文件的元数据
list_plan_cache_event_monitors
获取活动和非活动的数据库监视器
get_plan_cache_size_info
获取 QSQSRVR 作业内存和资源使用情况
get_plan_cache_management_sql
获取所有操作可直接使用的 CALL 语句
bash
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

参考文档