Caching authorization IDs for plans

You can set the size of the plan authorization cache by using the BIND PLAN subcommand. The default cache size is specified by the AUTHCACH subsystem parameter, with an initial default setting of 3072 bytes.

The AUTHCACH subsystem parameter or CACHESIZE option enables you to specify the size of the cache to acquire for the plan. Db2 uses this cache for caching the authorization IDs of those users that are running a plan. Db2 uses the CACHESIZE value to determine the amount of storage to acquire for the authorization cache. Db2 acquires storage from the EDM storage pool.

The size of the cache that you specify depends on the number of individual authorization IDs that are actively using the plan. The plan authorization cache includes 32 bytes of overhead information. In addition, each entry in the plan authorization cache is up to 132 bytes. Therefore, the minimum cache size of 256 bytes is enough for only one authorization ID entry. The maximum size of 4096 bytes is enough for 30 entries. The default AUTHCACH size of 3072 can contain 23 entries.

If you run the plan infrequently, or if authority to run the plan is granted to PUBLIC, you might want to turn off caching for the plan so that Db2 does not use unnecessary storage. To do this, specify a value of 0 for the CACHESIZE option.

Any plan that you run repeatedly is a good candidate for tuning by modifying the plan authorization cache size. Also, if you have a plan that a large number of users run concurrently, you might want to use a larger plan authorization cache size.