SA-11888

Summary

Report definition rule is not fetching correct data from Database. The values fetched by Report definition rule have many missing characters in it.

A manual select statement using pr_read_from_stream is also fetching invalid data.

A listview is working properly on the same data.

Error Messages

No Error Message. ReportDefinition fetching incorrect data.

Steps to Reproduce

1. Run the report definition rule and observe the results.
2. Results are missing some characters randomly.

Root Cause

The root cause of this problem is defect/misconfiguration in the operating environment.
It is use of a Report Definition which returns results of a query from a table where the data is coming from the blob (pzpvStream) and because the RD is querying against the pzpvStream (blob) column and the functions & java classes not being correct, it fails and appears to be corrupted

Resolution

This issue is resolved through the following local change:

TAKE A DATABASE BACKUP BEFORE PERFORMING THE STEPS.
 
In order to resolve it, one must drop the functions, re-load the udf entries, recreate the functions and then recompile the java classes.  This is done in a series of command-line and sqlplus steps to restore your functions/classes to a usable state.
 
We have to create a file for the connection to the database, drop the functions, re-load the udf entries, recreate the functions and then recompile the java classes.  Once these 9 sequential steps are completed, the issue should be resolved.
 
1. Create a file called user-deployudf.properties as a text file.
 
2. Add the following and replacing the parameters (ServerName_or_IP, port, OracleSID, PegaUser and DATAWORK) with your values:
               oracledate.jdbc.class=oracle.jdbc.OracleDriver
              dev.oracledate.url=jdbc:oracle:thin:@ServerName_or_IP:port:oracleSID
              dev.oracledate.username=PegaUser
              dev.oracledate.password=PegaUser
               dev.oracledate.schema=DATAWORK
 
3. Save the file in the D:\PRPC716_GA\archives\udf folder with the prreadstream.jar file being loaded. Note : This can change from system to system. So locate the archive folder at your end after talking to the database administrator.
 
4. Drop the functions that are causing the udf directstreamreader errors.  Connect to your database via sqlplus (Oracle) and execute the drop commands.  Replace the schema qualified name "DATAWORK" with the same name/value specified in step 2 for "dev.oracledate.schema=".
               SQL> drop function DATAWORK.pr_read_from_stream;
               SQL> drop function DATAWORK.pr_read_decimal_from_stream;
               SQL> drop function DATAWORK.pr_read_int_from_stream;
 
5. Load the classes.  This is done by using a Java Call from a command prompt and specifying seven parameters for the Java call.


 java          Program execution file used to process jar files.

            -cp           Class search path of directories and zip/jar files. 

             D:\PRPC716_GA\scripts\prdeploy.jar          Followed by a quoted string specifying the prdeploy.jar file which is used to load the files .

                  D:\lib\ojdbc6.jar         You must also specify the location of where the jdbc driver is for the database. Separated by a semi-colon (;).

                  com.pega.pegarules.install.DatabaseLibraryLoader           The name of the class in the prdeploy.jar file being called.

                  --config="D:\PRPC716_GA\archives\udf\user-deployudf.properties"            The location and name of the properties file created in step 1 & 2

                  --profile=dev.oracledate             Name of the profile in the config file being called.  Used to identify the database type

                  "D:\PRPC716_GA\archives\udf\prreadstream.jar"            Name of the jar file being processed and imported into the system.

To put all the parameters together, open a command prompt and execute the below.  Note that D:\PRPC716_GA is the folder containing the distribution image and \scripts is the folder containing the the prdeploy.jar file.

D:\PRPC716_GA\scripts> java -cp "D:\PRPC716_GA\scripts\prdeploy.jar;D:\lib\ojdbc6.jar" com.pega.pegarules.install.DatabaseLibraryLoader --config="D:\PRPC716_GA\archives\udf\user-deployudf.properties" --profile=dev.oracledate "D:\PRPC716_GA\archives\udf\prreadstream.jar"
 

6. Rebuild / Create the three functions removed in step 4.  Replace the schema qualified name "DATAWORK" with the same name/value specified in step 2 for "dev.oracledate.schema=".
          SQL> CREATE FUNCTION DATAWORK.pr_read_from_stream
(property in varchar2, insKey in varchar2, 
   storage_stream in blob)
  return varchar2 DETERMINISTIC
as 
--
-- $Id: 
--
-- Copyright (c) 2011  Pegasystems Inc.
-- All rights reserved.
--
-- This  software  has  been  provided pursuant  to  a  License
-- Agreement  containing  restrictions on  its  use.   The  software
-- contains  valuable  trade secrets and proprietary information  of
-- Pegasystems Inc and is protected by  federal   copyright law.  It
-- may  not be copied,  modified,  translated or distributed in  any
-- form or medium,  disclosed to third parties or used in any manner
-- not provided for in  said  License Agreement except with  written
-- authorization from Pegasystems Inc.
--
--
-- Name: pr_read_from_stream
--
-- Description:
--
language java 
name 'com.pega.pegarules.data.udf.directstreamreader.DirectStreamReader.get(java.lang.String, java.lang.String, java.sql.Blob) return java.lang.String';
/
SQL> CREATE FUNCTION DATAWORK.pr_read_decimal_from_stream
(property in varchar2, insKey in varchar2, 
   storage_stream in blob)
  return number DETERMINISTIC
as 
--
-- $Id: 
--
-- Copyright (c) 2011  Pegasystems Inc.
-- All rights reserved.
--
-- This  software  has  been  provided pursuant  to  a  License
-- Agreement  containing  restrictions on  its  use.   The  software
-- contains  valuable  trade secrets and proprietary information  of
-- Pegasystems Inc and is protected by  federal   copyright law.  It
-- may  not be copied,  modified,  translated or distributed in  any
-- form or medium,  disclosed to third parties or used in any manner
-- not provided for in  said  License Agreement except with  written
-- authorization from Pegasystems Inc.
--
--
-- Name: pr_read_decimal_from_stream
--
-- Description:
--
language java 
name 'com.pega.pegarules.data.udf.directstreamreader.DirectStreamReader.getDecimal(java.lang.String, java.lang.String, java.sql.Blob) return java.math.BigDecimal';
/
SQL> CREATE FUNCTION DATAWORK.pr_read_int_from_stream
(property in varchar2, insKey in varchar2,
   storage_stream in blob)
  return number DETERMINISTIC
as 
--
-- $Id: 
--
-- Copyright (c) 2011  Pegasystems Inc.
-- All rights reserved.
--
-- This  software  has  been  provided pursuant  to  a  License
-- Agreement  containing  restrictions on  its  use.   The  software
-- contains  valuable  trade secrets and proprietary information  of
-- Pegasystems Inc and is protected by  federal   copyright law.  It
-- may  not be copied,  modified,  translated or distributed in  any
-- form or medium,  disclosed to third parties or used in any manner
-- not provided for in  said  License Agreement except with  written
-- authorization from Pegasystems Inc.
--
--
-- Name: pr_read_int_from_stream
--
-- Description:
--
language java 
name 'com.pega.pegarules.data.udf.directstreamreader.DirectStreamReader.getInt(java.lang.String, java.lang.String, java.sql.Blob) return int';
/
 
7. Recompile all the Java Classes for the schema in question (DATAWORK).  Replace the schema qualified name "DATAWORK" with the same name/value specified in step 2 for "dev.oracledate.schema=". 
 
    One class at a time - Note the DB2LUW/DB2ZOS and PostgreSQL may or may not compile in Oracle. This can be ignored.
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2LUW" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/internal/clipboard/directstream/BadReferenceException" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/internal/clipboard/directstream/BadStreamDataException" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/internal/clipboard/directstream/ByteArrayUtil" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/internal/clipboard/directstream/DirectStream" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/internal/clipboard/directstream/DirectStreamEnvironmentAdapter" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/internal/clipboard/directstream/InflaterV7" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/internal/clipboard/directstream/PropertyReferenceUtilities" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/BasicEnvironmentAdapter" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/CachedDirectStream" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/DirectStreamReader" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderBase" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2LUW" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderDB2ZOS" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/DirectStreamReaderPostgreSQL" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/FixedCacheMap" resolve;
            SQL> alter java class "DATAWORK"."com/pega/pegarules/data/udf/directstreamreader/UnsupportedFeatureException" resolve;
  
8. Restart the PRPC Nodes and deleting the PegaRULES_Extract_Marker.txt file from their respective explictTempDir folders.

9. Re-test the offending issue with the corrupt query results.