Question
Calling Pega UDFs from database
Hi,
We are using DB2 database on z/OS. To extract data from BLOBs, we'd like to run Pega UDFs directly on Pega DB without using PRPC. Can we invoke UDFs and retrieve results on DB server? If so, how can we do this and save results to a staging DB or to a staging location in XML format?
We are considering this approach as using UDFs from PRPC has performance impacts.
Thanks!
Thanks for the response. BIX jobs are taking very long time to run. So, wanted to try this option on DB.
Thanks. Please elaborate on "BIX jobs are taking very long time to run". You can see the performance of BIX extracts in the BIX user guide - https://pdn.pega.com/documents/bix-721-user-guide
Quick question: do all RDs use UDFs when reporting on unoptimized columns from blob?
In our system there are no UDFs installed, but we have many RDs that report on unoptimized properties. How are they working fine even in absence of UDFs in the PRPC DB?
What version of Pega are you on? UDFs were introduced in 6.2sp2 and are used for any unoptimized properties. If you are running reports with unoptimized properties and do not have UDFs installed then I expect that you are on an earlier version of Pega.
The easiest way figure out if report definition rules executed with unexposed properties are using UDFs or not, is to look at the SQL generated in the results page in the clipboard. If the SQL contains references to pr_read_from_stream function being called, then UDFs are being used. You can also check for the existence of this function in the database to verify if UDFs are installed or not.
The current UDFs shipped with the product only return scalar values. They don't return the entire BLOB as JSON / XML to be saved into a file.
They can be used directly from the database. Please refer to the query generated by the report definition rule when unoptimized properties are used.
If you really want to extract data out of the BLOB into a relational structure, you could consider using declare index or BIX.