Caching authorization IDs for packages

Db2 authorization can cache roles or primary authorization IDs for handling packages. Db2 checks and caches a role if it is in effect and authorized. If a role is not in effect or authorized, Db2 checks and caches the primary authorization ID.

About this task

Caching roles or authorization IDs for packages can provide benefits for handling the following objects at run time:

  • Stored procedures
  • Remotely bound packages
  • Local packages in a package list in which the plan owner does not have execute authority on the package at bind time, but does at run time
  • Local packages that are not explicitly listed in a package list, but are implicitly listed by collection-id.*, *.*, or *.package-id

You can set the size of the package authorization cache by using the PACKAGE AUTH CACHE field on the DSNTIPP installation panel. The default is 5 MB.

The following table lists the number of entries that can be cached for each cache size.

Cache size Average number of collection-id.package-id or collection-id.* entries
100 KB 120
512 KB 630
5 MB 6400

Procedure

To determine the best package authorization cache size for your Db2 subsystem:

Use a performance tool, such as IBM® Tivoli® Tivoli OMEGAMON® XE for Db2 Performance Expert on z/OS® XE for Db2 Performance Monitor on z/OS to determine if your package authorization cache is being used effectively. If the report from this tool shows that an authorization ID in the cache was overwritten many times, consider increasing the value of CACHEPAC.

What to do next

To cache more package authorization information, use any of the following strategies:

  • Granting package execute authority to collection.*
  • Increasing the size of the cache
  • Granting package authority to a secondary ID or role when running in a trusted context
  • Granting package execute authority to PUBLIC for some packages or collections