SA-5114

Summary

Error when trying to open a Report Definition(Custom Data Table). However, standard data table's Report Definition works fine.

Error Messages

Error:

** ** An error occured on executing the query for the report definition - There was a problem getting a list: code: -440 SQLState: 42884 Message: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=FUNCTION;WN8.PR_READ_FROM_STREAM, DRIVER=4.14.113

Steps to Reproduce

1. Create a Simple table
2. Open the table's Report Definition rule and run

Resolution

Install UDF manually on a PRPC schema by following the below steps:

1. Take a backup of your existing DB: Use EXPDP command (in Oracle) to take a full database backup.
2. Verify that java is enabled on your database. To verify, run the following SQL 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 11gR2, 'JServer JAVA Virtual Machine'


 
3. Create Procedure DB privilege.
The database user used during the install must have the "create procedure" permission explicitly granted.
To verify DB privilege, run the following SQL as SYSDBA.

select * from dba_sys_privs where grantee='<PRPC USER>' and privilege='CREATE PROCEDURE';               
Replace <PRPC USER> with the real PRPC schema owner

A record should be returned, with the schema owner and the 'CREATE PROCEDURE' privilege listed. If not, grant Create Procedure DB privilege to PRPC DB schema owner.


 
Step 4: Map your DB properties. In the scripts folder in your PRPC media, configure the setupDatabase.properties file to map it to the PRPC schema where you want to install UDF.
For Example:

pega.jdbc.driver.jar=D:\Database_Drivers\ojdbc6.jar
pega.jdbc.driver.class=oracle.jdbc.OracleDriver
pega.database.type=oracledate
pega.jdbc.url=jdbc:oracle:thin:@localhost:1521/prpc
pega.jdbc.username=PRPC
pega.jdbc.password=PRPC
 
 
5. Modify the setupDatabase.xml file
Once the permissions are setup correctly, to run the setupUDF piece, add a new target to setupDatabase.xml which performs the initialization, configuration and UDF generation.

In setupDatabase.xml (Add below snippet):
<target name="installUDF" depends="Initialization, Configuration, UDF Generation">
<echo message="PegaRULES Process Commander UDF load complete."/>
</target>


6. Edit install.bat to install only UDF.
Then you can edit line 53 of install.bat to call this new target or just set ant_home and java_home and make the call from the command line.

call "%ANT_HOME%\bin\ant.bat" %ANT_PROPS% -f setupDatabase.xml installUDF
 
7. Execute the install.bat. The build should complete successfully.

8. Execute the below SQL:

select pr_read_from_stream('pylabel','DATA-ADMIN-WORKBASKET [email protected]_host.COM',pzpvstream) from pr_data_admin where pzinskey='DATA-ADMIN-WORKBASKET [email protected]_host';

This will make a call to a function, which makes use of the Java Objects.
 
9. Execute below to check invalid java objects.

SELECT dbms_java.longname(object_name) FROM user_objects WHERE status!='VALID' AND  object_type LIKE 'JAVA%';

At this moment, all the Oracle related objects should be valid.