DB2 SQL Error in sample application report
In the Solution provided for the Sky Airlines sample application - part of LSA course , I am getting SQLState error when I run the AllComplaints report and then select the category type.The error is as follows:
There was a problem getting a list: code: -440 SQLState: 42884 Message: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=PR_READ_FROM_STREAM;FUNCTION, DRIVER=3.59.81
I have setup the appliation on 6.2SP2 personal edition.
Please let me know if anybody faced this issue or has a solution for this.
Pegasystems Inc.
US
You should not see this error using the PVS but can see it if after deploying to a DB2 Cloud instance. The error results from attempting to read a column that is not exposed, i.e., is stored in the BLOB. Check your Report Definition warnings - PreFlight will show you these. "Kill two birds with one stone". Otherwise you need to find the missiing UDF jar that permits BLOB reads for DB2. Your time is better spent so doing the former - exposing the columns.
Virtusa Corporation
US
Hi,
Can you name the jar file that should be included or checked if its included?
We are in Pega 7.1.7 version.
Pegasystems Inc.
US
Both the 7.1.7 PVS and Personal Edition use Postgresql for the database. You would not see any UDF related error message.
There is no additional jar to download.
Are you asking how to connect to the Postgresql database on either the PVS or PE?
Virtusa Corporation
US
I am asking Enterprise version of Pega 7.1.7.
We are getting this error from Report Definition in our Development environment.
The backend is DB2.
Pegasystems Inc.
US
I came across the Pega Mesh post below.
Hope it solves your problem.
You can create a Pega Support request just in case.
8>< - - - - - -><8
We had an issue where some ootb reports were not working properly because the installation had skipped creating User-Defined-Functions on the database.
There are many posts on the Mesh about this, but all involve using SQLLoader to load the jar in to the database, then running scripts to create the functions.
I came across the Pega Mesh post below.
Hope it solves your problem.
You can create a Pega Support request just in case.
8>< - - - - - -><8
We had an issue where some ootb reports were not working properly because the installation had skipped creating User-Defined-Functions on the database.
There are many posts on the Mesh about this, but all involve using SQLLoader to load the jar in to the database, then running scripts to create the functions.
Running SQLLoader is un-necessarily complicated, and I couldn't find the create function scripts in the installation archive!
So here's the method I used, hope it is useful for someone else.
Open the DB2 command prompt , then run the following commands:
CALL SQLJ.INSTALL_JAR('file:D:\location_of_jar\prreadstream.jar','WORK.prreadstream',0);
create function WORK.pr_read_from_stream(property varchar(255), key varchar(255), stream blob)
returns varchar(255)
language java
parameter style java
no sql
fenced threadsafe
deterministic
returns null on null input
no external action
external name 'WORK.prreadstream:com.pega.pegarules.data.udf.directstreamreader.DirectStreamReaderDB2LUW.get'
;
commit;
CALL sqlj.REFRESH_CLASSES();