Caching authorization IDs for routines

Db2 authorization can cache roles or primary authorization IDs for handling routines. 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

The routine authorization cache stores roles or authorization IDs with the EXECUTE privilege on a specific routine. A routine is identified as schema.routine-name.type, where the routine name is one of the following names:

  • The specific function name for user-defined functions
  • The procedure name for stored procedures
  • '*' for all routines in the schema

You can set the size of the routine authorization cache by setting the CACHERAC subsystem parameter with the ROUTINE 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 schema.routine.type or schema.*.type entries
100 KB 120
512 KB 630
5 MB 6400

Procedure

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

  1. Consider how many routines you plan to run concurrently.
    If you run a large number of routines concurrently, the default value for CACHERAC is likely too small. If your cache is too small, entries in the cache are overwritten, and Db2 must read them again from the Db2 catalog.
  2. 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 routine 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 CACHERAC.

What to do next

To cache more authorization information about routines, use the following strategies:

  • Granting EXECUTE on schema.*
  • Increasing the size of the cache
  • Granting package authority to a secondary ID or role when running in a trusted context
  • Granting routine execute authority to PUBLIC for some or all routines in the schema.