Bix Extract from Work History without using BLOB
Hi,
I need to perform a BIX extract from the workhistory table that contains 100 millions lines. (After an initial extraction, we'll use the PEGA Purge tool to reduce the amount of data in the database, but we need an extraction first)
The property needed are :
- pxAddedByID
- pxTimeCreated
- pyMessageKey
- pyPerformer
All that properties are exposed so use of BLOB is not necessary.
However, PEGA keep using pzPVStream while extracting and that take too much time.
We use PEGA 7.1.9. is it possible to force PEGA to not use that BLOB column or to delete the BLOB column in order to perform a faster BIX extraction ?
If we only have exposed column to extract, is it usefull to use BIX or not ?
Thanks.
***Edited by Moderator Marissa to update platform capability tags****
Pegasystems Inc.
IN
Extraction of data from non-BLOB based tables is possible from 7.1.9 onwards.
Please refer below discussion
https://collaborate.pega.com/question/it-possible-extract-data-using-bix-table-without-blob-pzpvstream-column
https://pdn.pega.com/community/pega-product-support/question/bix-extraction-table-no-pzpvstream
Oligos
FR
I've already read theses posts, but it didn't answer my problem. It only says that
Extraction of data from non-BLOB based tables is possible from 7.1.9 onwards.
My table has a Blob column, but all the properties i need are exposed. But BIX use BLOB to extract data, and is not using exposed column. Can we force BIX to do that?
Pegasystems Inc.
US
Hi Shezad,
Basically BIX extraction tool is being used to extract the BLOB content from the database into a CSV/Excel/Database type. Coming to the point, if you need to extract the data from the exposed columns, you can simply use Report Definition or Select SQL query(export to your format in sql developer tool) to fetch the data from the exposed columns from table, in my perspective you don't need BIX to achieve this functionality as the common use of the BIX extract tool is to extract the data from the blob column. If you still want to use BIX to fetch the exposed column data then will research and let you know if that's possible.
Hope this information might help you.
Regards
Mahesh M
Pegasystems Inc.
US
After cross checking with one of my colleague to make sure, came to know that BIX always pulls data from BLOB column as the real data resides in BLOB column. If you want to fetch the data from exposed columns then prefer Report Definition or Select SQL as specified above.
Pegasystems Inc.
US
If i understand your requirement correctly, you need to extract the data from exposed columns and the extracted data shouldn't be repeated from one execution to another execution. If so then you can simply write a filter condition on your report definition and try to filter the work items with the help of pxCreateDatetime property. Lets say, if you extract the data in between Jan 1st 2019 to Jan 31st 2019 then you can pass Feb1st to Feb28th for your next execution of the report definition this way you can ignore the previous data which has been extracted from database. Hope that might help you.