Extracting BLOB Content from Work Table
- Archiving and Un Archving : - We need to extract the BLOB Content(Compressed Version) from the DB and map it on to a json Message:-
- Extracted Blob Content - Done using java Select query
- Saving the BLOB Content a temp Table - Using JAVA - Insert query
- When i am trying do obj-open-by-handle - getting a read operation error and the content saved which is binary form is getting changed (the symbols doesnt look as in from the main table
Updated: 7 Nov 2021 1:38 EST
SAMI AEC
SA
You can try with obj-browse as well , which will selectively fetches required blob fields when compared with the obj open and open by handle which will fetched all the blob fields.
Ernst & Young LLP
MT
Can you share a bit more details about your use case here?
What I understand you want to archieve (all ?) work data for some reason. And, for some reason you chose JSON as to be the data export format.
Couple of OOTB options -
- BIX extract - It can be configured to read details (BLOB) of all work items or specific work items and dump the required data to an internal/external db table or into CSV/XML file on server file system.
- Purge & Archieve - Normally utilized to purge older cases from PEGA DB, and create archive files prior to deletion.
- Use of OOTB PEGA api (via a custom connector rule) /cases/{ID} to get the work instance data (BLOB) into JSON format.
Thanks
Cognizant
IN
Objective:- Our Use Case is to Archive old cases and move the blob content to another Larger Database.
- We are having the process of Unarchive the Archive Case as well.
- So In this use case we have to form a json message which should contain some columns which are used for reporting and blob for unarchiving.
- So in this process when we save back the blob data (in compressed version) the actually process where we open a case from Manger Portal etc., should work.
- Actually process is a json message should contain Blob content and some reporting fields and this json message is read by a java programme (background process) and pust the message to a kafka queue and the queue is read by another java programme and saves it to another larger database.
Ernst & Young LLP
MT
While making any plan to archieve case data, always remember that it's not just the work table holding details about work object, there are other tables/classes too (e.g. attachment, history, pulse, Follow) that holds data about the work object.
You need to archieve the WHOLE data and not just the data in work table BLOB column.
From my perspective, Purge & Archive tool should be the best solution. It purges such old cases from all relevant tables, and create an PEGA archieve file with the data being purged [1]. You then can import those archieve files in a separate PEGA (archieve) server.
If you don't want to purge the older cases, and rather just want to copy old data to an archieve PEGA system, then Package work [2] is the other option you have. It allows you to move all the relevant data of an work object in a PEGA archieve file that can be imported in another PEGA application server.
Cognizant
IN
Hi Sharma,
Thank you very much for the prompt reply. The articles which is given above were taken into consideration and we tried our best to to check whether it suits our requirement. But it doesn't. Can you please help us whether we can retrieve the BLOB Content (Compressed Version) and mapped to a json Message.
Regards
Vivekanand P
Ernst & Young LLP
MT
- Obj-Browse or Report def to retrieve pzIsnKey of each of such old cases.
- Loop through the result list
- Open each instance on a page (without lock) using Obj-Open-By-Handle
- Use @(Pega-RULES:Page).pxConvertPageToString(tools, myStepPage, "json") function to convert the work object page content into JSON format.
Cognizant
IN
Hi Sharma,
Thank you very much for the reply
we have considered the above steps for reporting columns which are part of blob and formed a json message
In Pega, When a obj-open-by- Handle is used. it calls Open Method of Java which again Decompress the PZPVStream (BLOB) Column by Using Inflate Classes and Methods and forms a XML to form a Clip Board page.
When we do Obj-Save pega calls Save Function and Compresses the WorkPage by using Deflate java Classes and methods and save back the content to PZPVStream Column.
So my question is can we get the Compressed Version and mapped to a JSON Message
Thanks