Implementing Db2 system-defined routines

A system-defined routine is a function or stored procedure for which an authorization ID with system DBADM or SQLADM authority has the implicit execute privilege on the routine and any of its packages.

System-defined routines have the value 'S' in column SYSTEM_DEFINED of catalog table SYSIBM.SYSROUTINES.

A Db2-supplied routine or user-created routine is marked as system-defined if both of the following conditions are true when the routine is created, or the routine is altered to be system-defined:
  • The authorization ID that is used to create the routine has installation SYSADM authority or installation SYSOPR authority.
  • The current SQLID is set to SYSINSTL.

Implementing user-created routines as system-defined routines

To define an existing user-created routine as system-defined, you need to use an authorization ID with installation SYSADM or installation SYSOPR authority. Follow these steps:
  1. Issue SQL statement SET CURRENT SQLID='SYSINSTL'.
  2. Issue an SQL ALTER PROCEDURE or ALTER FUNCTION statement on the routine. The ALTER statement does not need to change any parameter values.

Suppose that user-created function ADMF001.TAN is defined like this:

CREATE FUNCTION ADMF001.TAN (X DOUBLE)
      RETURNS DOUBLE          
      LANGUAGE SQL            
      CONTAINS SQL            
      NO EXTERNAL ACTION      
      DETERMINISTIC           
      RETURN SIN(X)/COS(X);

To verify that function ADMF001.TAN is not system-defined, issue a SELECT statement like this one.

SELECT SYSTEM_DEFINED FROM SYSIBM.SYSROUTINES
 WHERE SCHEMA='ADMF001' AND NAME='TAN';

If ADMF001.TAN is not system-defined, the value of SYSTEM_DEFINED is blank.

To define ADMF001.TAN as system-defined, use an ID with installation SYSADM authority to issue SQL statements like these. The ALTER statement does not change any properties of the function.

SET CURRENT SQLID='SYSINSTL';
ALTER FUNCTION ADMF001.TAN (X DOUBLE)
      LANGUAGE SQL                    
      CONTAINS SQL                    
      NO EXTERNAL ACTION              
      DETERMINISTIC;

To verify that function ADMF001.TAN is system-defined, issue a SELECT statement like this one.

SELECT SYSTEM_DEFINED FROM SYSIBM.SYSROUTINES
 WHERE SCHEMA='ADMF001' AND NAME='TAN';

If ADMF001.TAN is system-defined, the value of SYSTEM_DEFINED is 'S'.