Where exactly is a DB2 plan stored?

The title of this posting is a question I received awhile ago. As I promised earlier on this blog, I will periodically post the answers I have given to e-mailed questions. So, here goes:

A DB2 "plan" is stored in the DB2 Directory and information about the plan is stored in the DB2 Catalog.

The DB2 Directory table that contains actual plans is SYSIBM.SCT02 (and SYSIBM.SPT01 contains actual packages). The plan is stored as an internal structure called a cursor table; packages are stored as package tables. As DB2 runs application programs, it loads the cursor tables for plans and package tables for packages from the DB2 Directory tables into the EDM Pool. This is where the "access path" code that determines how to get the actual DB2 data resides.

There is also metadata about plans and packages that you might find useful. This information includes data about the state, privileges, isolation level, release specification, and so. The DB2 Catalog contains information about plans in the following tables:

• SYSIBM.SYSDBRM
• SYSIBM.SYSPLAN
• SYSIBM.SYSPLANAUTH
• SYSIBM.SYSPLANDEP
• SYSIBM.SYSSTMT
  

And, the DB2 Catalog contains information about packages in the following tables:

• SYSIBM.SYSPACKAGE
• SYSIBM.SYSPACKAUTH
• SYSIBM.SYSPACKDEP
• SYSIBM.SYSPACKLIST
• SYSIBM.SYSPACKSTMT
• SYSIBM.SYSPKSYSTEM
• SYSIBM.SYSPLSYSTEM