This content has been archived.
Close popover

Understanding and troubleshooting user-defined functions

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. The sections on troubleshooting UDF issues pertain primarily to Oracle.

While this article mainly addresses user needs with Pega Platform™ 7.2.x, you can use most of the information if you are using any Pega Platform 7.1.x release and some of the information if you are using a Pega Platform 6.x release.

For simplicity, this article refers to deployment guide Pega 7.2.1 Platform Installation WebSphere Oracle, the section Running the Installation and Upgrade Assistant, Step 9 on page 26. In that deployment guide, see also Appendix C — Optional: Generating and applying DDL, and Appendix D — Installing user-defined functions.

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.

User-defined functions: What they are and when to use them

Privileges needed to install UDFs

Installing UDFs during Pega 7.2.1 installation or upgrade

Installing UDFs after Pega 7.2.1 installation or upgrade

Verifying UDF installation

Validating invalid UDFs

Forcing Oracle to revalidate UDFs

ResolveUDF SQL script

Resolving UDF errors

Before opening a Support Request: Must Gather information

User-defined functions: What they are and when to use them

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, that is, wherever an expression can occur.

Starting in Pega Platform 6.2 SP2, the system includes UDFs in the database to read and retrieve property values directly from Binary Large Objects (BLOBs) in the database. You can choose to install UDFs during product installation or upgrade or after installation or upgrade unless you choose to bypass installing them at that time.

These UDFs are required for report definition rules to retrieve data from the BLOB for properties that are not exposed. 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.

No report provided by Pega Platform requires the use of UDFs.

For a split schema, you must install the UDFs in both the rules and data schemas.

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.

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

Privileges needed to install UDFs

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.

  1. 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 Machine is 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]

  2. To verify that you have the proper privilege, run the following SQL query as SYSDBA, replacing PRPC_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.

Installing UDFs during Pega 7.2.1 installation or upgrade

By default, the Pega Platform 7.2.1 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 7.2.1, clear the check box Bypass Automatic DDL Application.
In prior 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:

bypass.pega.schema=

bypass.udf.generation=

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.

If you need to have the database administrator (DBA) apply schema changes and thus set bypass.pega.schema=true, the installation, upgrade, or update process also bypasses installing the UDFs, regardless of the bypass.udf.generation setting.

After installation, confirm that the UDFs are installed and valid. See Verifying UDF installation.

Installing UDFs after Pega 7.2.1 installation or upgrade

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 7.x software kit.

See Appendix D — Installing user-defined functions of the appropriate deployment guide for details of how to use this script.

For example, see Pega 7.2.1 Platform Installation WebSphere Oracle, Appendix D — Installing user-defined functions.

After installing the UDFs using this procedure, verify that they are installed and are valid. See Verifying UDF installation.

Verifying UDF installation

To verify if the UDFs are installed and valid, run the following query, replacing V72RULES with your schema name.

While there are three (3) UDFs, there are 15+ Java classes that provide the implementation of the functions. This query checks for Java objects owned by the rules schema.

The schema name must to be in all uppercase letters.

select dbms_java.longname(Object_Name), status from dba_objects where owner= 'V72RULES' and object_type like 'JAVA%';

com/pega/pegarules/data/udf/directstreamreader/BasicEnvironmentAdapter VALID

com/pega/pegarules/data/udf/directstreamreader/CachedDirectStream VALID

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReader VALID

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase$Version6 VALID

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase$Version7 VALID

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase VALID

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2LUW INVALID

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2ZOS INVALID

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderPostgreSQL INVALID

com/pega/pegarules/data/udf/directstreamreader/FixedCacheMap VALID

com/pega/pegarules/data/udf/directstreamreader/UnsupportedFeatureException VALID

com/pega/pegarules/data/internal/clipboard/directstream/BadReferenceException VALID

com/pega/pegarules/data/internal/clipboard/directstream/BadStreamDataException VALID

com/pega/pegarules/data/internal/clipboard/directstream/ByteArrayUtil VALID

com/pega/pegarules/data/internal/clipboard/directstream/DirectStream VALID

com/pega/pegarules/data/internal/clipboard/directstream/DirectStreamEnvironmentAdapter VALID

com/pega/pegarules/data/internal/clipboard/directstream/DirectStreamV7 VALID

com/pega/pegarules/data/internal/clipboard/directstream/InflaterV7 VALID

com/pega/pegarules/data/internal/clipboard/directstream/PropertyReferenceUtilities VALID

com/pega/pegarules/data/internal/clipboard/directstream/ReferenceString VALID

From this query you can see that there are twenty (20) Java classes installed in the v72rules schema in this environment. Seventeen (17) are valid; three (3) are not valid. The three Java classes that are invalid, indicated by their names, are not applicable to an Oracle installation and can be ignored.

However, if your query returns different results, you might need to take action:

Validating invalid UDFs

Follow this procedure to validate the invalid Java classes detected during Verifying UDF installation.

Retrieve a list of installed, invalid UDFs

Test Oracle auto-validation on first-time use

Retrieve a list of installed, invalid UDFs

  1. Connect as the schema user because the command you run in the next step queries against user_objects.
  2. Run the following command to retrieve a list of UDFs that are installed but not valid.
    Select Dbms_Java.Longname(Object_Name) From User_Objects Where Status!='VALID' AND object_type LIKE 'JAVA%';

Examine the results, which may vary.
This example result set shows all of the Pega Platform 7.2 UDFs in the V72RULES schema as invalid.

com/pega/pegarules/data/internal/clipboard/directstream/ReferenceString

com/pega/pegarules/data/internal/clipboard/directstream/PropertyReferenceUtilities

com/pega/pegarules/data/internal/clipboard/directstream/InflaterV7

com/pega/pegarules/data/internal/clipboard/directstream/DirectStreamV7

com/pega/pegarules/data/internal/clipboard/directstream/DirectStreamEnvironmentAdapter

com/pega/pegarules/data/internal/clipboard/directstream/DirectStream

com/pega/pegarules/data/internal/clipboard/directstream/ByteArrayUtil

com/pega/pegarules/data/internal/clipboard/directstream/BadStreamDataException

com/pega/pegarules/data/internal/clipboard/directstream/BadReferenceException

com/pega/pegarules/data/udf/directstreamreader/UnsupportedFeatureException

com/pega/pegarules/data/udf/directstreamreader/FixedCacheMap

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderPostgreSQL

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2ZOS

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2LUW

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase$Version7

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase$Version6

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReader

com/pega/pegarules/data/udf/directstreamreader/CachedDirectStream

com/pega/pegarules/data/udf/directstreamreader/BasicEnvironmentAdapter

Test Oracle auto-validation on first-time use

Sometimes, when the Java classes are installed, they may be in an invalid state. The first time that you use them, Oracle validates them, and there is nothing more that you need to do.
Follow these steps to use the UDFs for the first time to see if Oracle automatically validates them. These queries can be used on any database to test the UDF functions.

Test queries

  1. Replace v72rules with the name of your rules schema and run this command:

    SELECT v72rules.pr_read_from_stream('pzInsKey', pzInsKey, pzPVStream) as "StringValue", v72rules.pr_read_int_from_stream('pzRuleSetVersionMajor', pzInsKey, pzPVStream) as "IntegerValue", v72rules.pr_read_decimal_from_stream('pzRuleSetVersionMinor', pzInsKey, pzPVStream) as "DecimalValue" from v72rules.pr4_rule where pxInsID like '%CANNOT%';
  2. Replace v72data with the name of your data schema and run this command:

    SELECT v72data.pr_read_from_stream('pzInsKey', pzInsKey, pzPVStream) as "StringValue", v72data.pr_read_int_from_stream('pzRuleSetVersionMajor', pzInsKey, pzPVStream) as "IntegerValue", v72data.pr_read_decimal_from_stream('pzRuleSetVersionMinor', pzInsKey, pzPVStream) as "DecimalValue" from v72data.pr_data_admin;
  3. Examine the results of each query to look for an error.
    In any case, you are not interested in the results of the query but in whether the query throws an error.

Example

SQL> connect v72data/v72data

Connected.

SQL> Select Dbms_Java.Longname(Object_Name) From User_Objects Where Status!='VAL

ID' AND object_type LIKE 'JAVA%';

DBMS_JAVA.LONGNAME(OBJECT_NAME)

--------------------------------------------------------------------------------

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderPostgreSQL

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2ZOS

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2LUW

SQL> connect v72rules/v72rules

Connected.

SQL> Select Dbms_Java.Longname(Object_Name) From User_Objects Where Status!='VAL

ID' AND object_type LIKE 'JAVA%';

DBMS_JAVA.LONGNAME(OBJECT_NAME)

--------------------------------------------------------------------------------

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderPostgreSQL

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2ZOS

com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2LUW

Forcing Oracle to revalidate UDFs

To force Oracle to validate invalid Java classes, run the following command for each Java class that is in an invalid state.
As before, replace V72RULES in this example command with the name of your schema.

​ALTER JAVA CLASS

"com/pega/pegarules/data/internal/clipboard/directstream/ByteArrayUtil"

RESOLVER ((*V72RULES)(* public))

RESOLVE;

The ResolveUDF.sql script has the commands to resolve all of the Java classes in Pega Platform 7.2.
In earlier versions of Pega Platform, not all of these Java classes are applicable.

Consider the following choices for working with the ResolveUDF.sql script:

  • Run the script, replacing V72RULES with your schema name: The UDFs that are not applicable simply return an error.
  • Run the script for the UDFs that show as invalid in your environment.

After you run the RESOLVER command for each UDF, you can rerun the following command to make sure that all UDFs are now valid:

Select Dbms_Java.Longname(Object_Name) From User_Objects Where Status!='VALID' AND object_type LIKE 'JAVA%';

If you have tried to resolve all of the Java classes listed above and there are some classes that still do not resolve, you should follow the instructions to drop the UDFs and reinstall them. See Installing UDFs after Pega 7.2.1 installation or upgrade.

ResolveUDF SQL script

ALTER JAVA CLASS "com/pega/pegarules/data/internal/clipboard/directstream/ByteArrayUtil"

RESOLVER ((*V72RULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/internal/clipboard/directstream/BadReferenceException"

RESOLVER ((*V72RULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/internal/clipboard/directstream/BadStreamDataException"

RESOLVER ((*V72RULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/internal/clipboard/directstream/DirectStream"

RESOLVER ((*V72RULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/internal/clipboard/directstream/DirectStreamEnvironmentAdapter"

RESOLVER ((*V72RULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/internal/clipboard/directstream/InflaterV7"

RESOLVER ((*V72RULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/internal/clipboard/directstream/PropertyReferenceUtilities"

RESOLVER ((*V72RULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/udf/directstreamreader/BasicEnvironmentAdapter"

RESOLVER ((*V72RULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/udf/directstreamreader/CachedDirectStream"

RESOLVER ((*V72RULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase"

RESOLVER ((*V72RULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/udf/directstreamreader/DirectStreamReader"

RESOLVER ((*V72RULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/udf/directstreamreader/FixedCacheMap"

RESOLVER ((*V72RULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/udf/directstreamreader/UnsupportedFeatureException"

RESOLVER ((*V72RULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/internal/clipboard/directstream/DirectStreamV7"

RESOLVER ((*V72RULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase$Version7"

RESOLVER ((*V72RULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase"

RESOLVER ((*V72RULES)(* public))

RESOLVE;

ALTER JAVA CLASS "com/pega/pegarules/data/udf/directstreamreader/DirectStreamReader"

RESOLVER ((*V72RULES)(* public))

RESOLVE;

Resolving UDF errors

If the UDFs are not installed or have been in some way corrupted, you see the issue in the following ways and can resolve it using the appropriate procedure.

UDF errors, explanations, and actions to take

Symptom or Error

Explanation

Action to Take

ORA-29541 or
ORA-00904 errors
involving pr_read_from_stream

Cannot resolve the UDF function

Cannot find the UDF that is being queried

Verifying UDF installation

When the UDFs are used and the data shown in the report is almost correct, but has some minor issues like extra spaces or spelling errors

UDFs are found but, especially when the UDFs are installed in another schema and migrated to a new schema using database specific tools, they can become corrupted. If the UDFs are corrupted, you must reinstall them.

Installing UDFs after Pega 7.2.1 installation or upgrade

ORA-04043 error

Rather than being unresolved Java classes, the necessary Java classes are not even in the database.

Installing UDFs after Pega 7.2.1 installation or upgrade

Before opening a Support Request: Must Gather information

If you have followed the instructions to drop and reinstall the UDFs and are still seeing an issue, you need to create a Support Request (SR).

Before submitting your SR, gather the following artifacts to attach to it:

  • The file setupDatabase.properties from the run, located in the directory <Pega 7.x Media>/scripts
  • The log from executing the generateudf script, which is the file <Pega 7.x Media>/scripts/logs/PRPC_DatabaseLibraryLoader-*.log
  • Screenshots of the output from the queries you have run in the section Test queries.

 

Suggest Edit

100% found this useful


Related Content

Have a question? Get answers now.

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