Skip to main content


         This documentation site is for previous versions. Visit our new documentation site for current releases.      
 

Java user-defined functions in your Pega database

Updated on March 21, 2022

Beginning in Pega Platform version 8.6, Pega deprecated usage of the Java user-defined functions (UDFs) due to their severe performance impact. In a future release of Pega Platform, Pega will no longer support Java UDFs for on-premises deployments.

For clients running their applications in a Pega Cloud deployment, Pega does not support the use of UDFs in the latest Pega Cloud infrastructure. Pega Cloud clients must work with Pega to remove the UDFs from their database. Pega Platform deployments do not require Java UDFs and in most cases, Pega does NOT recommend the installation of Java UDFs as this article describes.

Overview and use of Java UDFs

Pega Platform supports Java UDFs to provide temporary reporting functionality on the rules and data of your Pega application, to avoid having to optimize every property during initial report development. User-defined functions appear in a SQL statement anywhere that property references can appears, or wherever an expression can occur.

Application rules can reference these Java UDFs in report definition rules to retrieve data from the BLOB for properties that are not exposed. However, the use of Java UDFs is not required for any Pega Platform reports.

When to use user-defined functions and when to avoid them

It is important to review the appropriate use of Java UDFs in your application so that you can decide whether or not you should install Java UDFs on your system during an installation or update.

By default, Pega software stores the values of all aggregate properties and all Single Value properties in a BLOB column (the Storage Stream), which usually exists in a compressed format.

When to use Java UDFs with your Pega application

Using Java UDFs in your database allows you to develop your application without optimizing, or exposing every property during initial report development. With Java UDFs, you can test simple reporting rules, since the report or application query can reference a BLOB column and the Java UDFs can temporarily extract the column property and allow your report or query to retrieve data from it.

Java UDFs are best used in development or staging environments especially early during the application development life cycle.

When to avoid using Java UDFs with your Pega application

Relying on Java UDFs to extract one or more BLOB columns on which you run reports or application queries, can negatively affect application performance. This is especially true with large database tables or multiple, simultaneous BLOB column extractions, which can occur on large, complex applications.

If you expose column data by optimizing your column properties using Pega-provided optimization tools, you can avoid the need to use Java UDFs in your database. To learn more about optimizing reporting properties in Pega applications, see Optimizing database properties.

Remove Java UDFs in production environments or in any development or staging environments with your application in a mature phase of their application life cycle because of the hardware resource drain. You should not use them in a production environment, because they severely affect performance as the size of the table increases.

Install user-defined functions in on-premises systems

By default, in Pega Platform version 8.7 and earlier, database users with the necessary privileges, can install Java UDFs into the database as part of a Pega Platform installation or update. For complete instructions, see the installation or update guide for your application server and database combination.

Note: If you choose to install Java UDFs in a system that you installed using a split schema, you must install them in both the rules and data schemas.

General database requirements for installing Java UDFs

If you decide that you need to install Java UDFs into your database, it is a best practice to review your system database requirements before you install Pega Platform or Java UDFs in your database:

  • For postgreSQL databases: Java UDFs require the PL/Java add-on for PostgreSQL and a new dedicated schema. The new schema, called SQLJ, is defined for the Pega Platform database. Before you install or update Pega Platform and install or re-install Java UDFs for your database, you must build and install the version of PL/Java for the appropriate JDK version in your database. For details on how to build PL/Java, see Building PL/Java; for details on installing PL/Java, see Installing PL/Java.
  • For Oracle databases: You must have Java enabled. To verify that Java is running, run the following SQL query as a SYSDBA.

    select comp_name, version, status from dba_registry where upper(comp_name) like
                                    '%JAVA%';

    If Java is running, the script returns a record for JVM. For example, on Oracle 11g Release 2, JServer JAVA Virtual Machine is returned.

    For more information, review the Oracle support document, Oracle JVM and Java Stored Procedures FAQ.

    The Oracle database user installing the Java UDFs needs the privileges to create the procedure. To verify that you have the proper privileges, run the following SQL query as a SYSDBA, replacing PRPC_USER with the username of the Pega Platform deployment user. The username must be in all uppercase letters,

    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.

    If this is a single schema and the database operator installing the Java 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 installer must use the deployment user credentials. See the appropriate Pega Platform installation or update guide to review setting the required privileges for the deployment user on your database.

  • For MSSQL databases: You must implement Java UDFs in C# using Common Language Runtime (CLR); therefore your database must have CLR enabled on the database. See the appropriate Pega Platform installation guide for your application server and MSSQL database.

Installing Java UDFs during Pega Platform installation

Both the Installation and Update Assistant(IUA) and the command line scripts support installing Java UDFs during your installation or update. The IUA does not support automatically applying the DDL without the Java UDF installation.

To install Java UDFs during your software installation, you must leave the following two properties in the setupDatabase.properties file blank:

bypass.pega.schema=
bypass.udf.generation=

To confirm that the Java UDFs are installed and valid, see Verifying UDF installation.

Installing Java UDFs after a Pega Platform installation or update

If you choose to bypass installing the Java UDFs during the initial Pega Platform installation process, you can install the UDFs later, after installation, or you can re-install them after you complete a software update.

For details, see the Optional: Manually installing Java user-defined functions (UDFs) if you did not opt to automatically install UDFs section in the appropriate Pega Platform version 8.7 or earlier update guide. To confirm that the Java UDFs are installed and valid, see Verifying UDF installation.

Have a question? Get answers now.

Visit the Support Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega.com is not optimized for Internet Explorer. For the optimal experience, please use:

Close Deprecation Notice
Contact us