This article explains what user-defined functions (UDFs) are and when to use them. The sections on UDF use and installation apply to all databases.
If you are using another Pega Platform release or database other than Oracle, find the sections that explain user-defined functions (UDFs) in your deployment guide, either the installation guide, the upgrade guide, or the update guide.
Within a database, you can write user-defined functions (UDFs) to provide functionality that is not available in SQL or built-in database functions. User-defined functions can appear in a SQL statement anywhere SQL functions can appear - in other words, wherever an expression can occur.
Pega Platform includes UDFs in the database to read and retrieve property values directly from Binary Large Objects (BLOBs) in the database. These UDFs can be used for report definition rules to retrieve data from the BLOB for properties that are not exposed. However, no report provided by Pega Platform requires the use of UDFs. While the use of UDFs can be useful, especially during development, you need to be careful about using them in a production environment, because they can severely affect performance in some cases.
You can choose to install UDFs during product installation or upgrade, or after installation or upgrade if you chose to bypass installing them during the installation process. If you do not want to install the UDFs during installation or upgrade, then you need to expose any column that you use in a report. Note that for a split schema, you must install the UDFs in both the rules and data schemas.
For the Oracle, DB2, and PostgreSQL databases, the UDFs are implemented in Java. For SQL Server, they are implemented in C# using Common Language Runtime (CLR). Ensure that Java or CLR is enabled on the database.
For more information on when to use or when not to use UDFs, see this article: When to use — and when not to use — unoptimized properties in reports
Besides having Java or CLR enabled on the database, the database user installing the UDFs needs the privileges to create the procedure.
If this is a single schema in Oracle and the database operator used during the installation of the UDFs is the schema user, then the CREATE PROCEDURE or CREATE ANY PROCEDURE permission needs to be explicitly granted. For a split schema installation, the deployment user needs to be used.
See the appropriate Pega deployment guide for details of the privileges needed for the deployment user and enabling Java or CLR for SQL Server on your database.
- You must have Java enabled on your Oracle database. To verify this, run the following SQL query as SYSDBA.
select comp_name, version, status from dba_registry where upper(comp_name) like '%JAVA%';
A record for JVM should be returned. For example, on Oracle 11g Release 2,
JServer JAVA Virtual Machineis returned.
For more information, see the Oracle support document, How to check whether JVM for Oracle is installed or not in the database?[ID 397770.1]
- To verify that you have the proper privilege, run the following SQL query as SYSDBA, replacingPRPC_USER with the real Pega Platform deployment user.
The username specified in the Where clause must be in all uppercase letters as PRPC_USER is.
select * from dba_sys_privs where grantee='PRPC_USER' and privilege like 'CREATE %PROCEDURE'
A record should be returned with the deployment and the CREATE PROCEDURE privilege listed.
By default, the Pega Platform Installation and Upgrade Assistant (IUA) generates and applies the schema changes, including the UDFs supplied by Pegasystems, to your database.
- Do not choose to bypass Pega schema or UDF generation when you run the Installation and Upgrade Assistant (IUA) .
For Pega Platform, clear the check box Bypass Automatic DDL Application. (Note that in different releases, the Bypass option has different labels and placement in the user interface of the installation wizard.)
- If you are using the command line scripts, instead of the Installation and Upgrade Assistant (IUA), make sure that the following properties in the setupDatabase.properties file are left blank:
As long as the deployment user has the necessary privileges, the UDFs are installed as part of the installation, upgrade, or update. See Privileges needed to install UDFs on Oracle.
After installation, confirm that the UDFs are installed and valid. See Verifying UDF installation in **Troubleshooting user-defined functions.**LINK**
If you chose to bypass installing the UDFs during the installation, upgrade, or update process and want to install them now, you can use the generateudf.bat or generateudf.sh script that comes with the Pega Platform software kit.
See the section of the appropriate deployment guide for details of how to use this script. For example, see the section Installing user-defined functions in your installation guide.
After installing the UDFs using this procedure, verify that they are installed and are valid. See Verifying UDF installation.