Designing EDM storage space for performance
The environmental descriptor manager (EDM) pools contain skeleton application plans and packages, database descriptors, and cached dynamic SQL statements. You can design them to reduce the number of I/O operations and reduce processing times.
About this task
You can design your EDM storage pools to avoid allocation I/O (a significant part of the total number of I/Os for a transaction), reduce the time that is required to check whether users who attempt to execute a plan are authorized to do so, and reduce the time that is required to prepare statements with the statement cache pool.
When pages are needed from the EDM storage pools, any
pages that are available are allocated first. If the available pages
do not provide enough space to satisfy the request, pages are stolen
from
an inactive SKCT, SKPT, DBD, or dynamic SQL skeleton. If enough space
is still not available, an SQL error code is sent to the application
program.
EDM storage pools that are too small cause the following problems.
- Increased I/O activity in DSNDB01.SCT02, DSNDB01.SPT01, DSNDB01.DBD01, DSNDB01.SYSDBDXA, and DSNDB01.SYSSPUXA
- Increased response times, because of loading the SKCTs, SKPTs, and DBDs
- Increased processing and response times time for full prepares of dynamic SQL statements when the EDM statement cache is too small.
Procedure
To ensure the best performance from EDM pools:
| Design... | To contain... |
|---|---|
| EDM DBD pool | Database descriptors |
| EDM statement pool | The cached dynamic SQL statements |
| EDM skeleton pool | Skeleton copies of plans (SKCTs) and packages (SKPTs) |