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.

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.