After Exposing a column in Pega ,Column not populated with old Value
After Exposing a column in Pega ,Column not populated with old Value,I want to populate the exposed column value for old cases.Column populator status show successful.
Accepted Solution
Updated: 20 Jan 2025 19:01 EST
HCA Healthcare
US
@UpendharIf the column population job is completed but data is missing for many cases, re-run the job by going to Dev Studio → Configure → System → Database → Column Populator, selecting the same table and column, and starting a new job (even if it was completed before). Use Tracer or logs in a lower environment to investigate why some cases are skipped, checking for errors or property mapping issues. Ensure Pega’s background processing is working correctly by verifying queue processors. Instead of reindexing cases individually, use the bulk case reindexing feature under Dev Studio → System → Search → Reindex. If the issue persists, clear existing column populator job data in the database (e.g., pr_sys_statusnodes table) and try again. Other solution is use the pega.read_from_stream function in SQL to extract property values from the BLOB (pzPVStream) and backfill the exposed column. If none of these resolve the issue, contact Pega support for a hotfix, thanks
SiXworks
GB
@SutanuHere is one way...
Create an activity with 2 parameters: parentClassToProcess, propertyToOptimize - both Strings.
The activity has a single Java step. The Java code is as follows:
final String parentClassToProcess = tools.getParameterPage().getString("parentClassToProcess");
final String propertyToOptimize = tools.getParameterPage().getString("propertyToOptimize");
IColPopulation colPop = ((PegaAPI)tools).getColumnPopulationAPI();
java.util.Set propertiesList = new HashSet();
try {
propertiesList.add(propertyToOptimize);
colPop.schedulePopulationForProperties(parentClassToProcess,propertiesList,null,null,null);
} catch (Exception e) {
oLog.error("Failed triggering column population for property "+propertyToOptimize+" in class "+parentClassToProcess+"due to the following error:\n" + e.getStackTrace());
}
Maantic
IN
Few things,
After optimizing through a pega wizard or manually creating a column on backend
- Is the property name you are trying to expose is same as the column name or not?
- Pega has the tendency to add something like COLUMNNAME_1 instead of COLUMNNAME when optimized through designer studio. Please verify
- Once property exposed if it's not same as column name make sure to add it on the class rule for which the property is exposed on External Mappings tab
- Once all above done, please save the class rule once and save the DB table instance also in pega.
- If the column population job not triggered then create a one time activity to populate the values
EvonSys Inc
US
In which version you started to run activities, instead of OOTB populations?
We are facing the same after upgraded to 8.5.3. OOTB populations are not working. We have to run an activity to save the objects manually.
Accenture
SG
@Dhanusha Facing similar issue in 8.7.4 version, after adding two new columns in Index table, the column population jobs having missing Properties column and after few hours when verified only 300 columns are populated with new data and rest 530K entries haven't updated resulting in missing these two property values.
HCA Healthcare
US
@Sutanu When a column is exposed in Pega, it won't automatically populate with old case values. To fix this, ensure the property has data for existing cases in the clipboard. Re-run the Column Populator utility from Dev Studio under Configure → System → Database → Column Populator, ensuring no errors occurred during the process (check logs if needed). If values are still missing, verify the database schema, confirm the column exists with the correct data type, and re-sync the schema if necessary. If the issue persists, use a custom activity to loop through cases and update the column using Obj-Save, or write a SQL script to extract data from the pzPVStream (BLOB) field and backfill the column. Finally, validate the database and test the application to ensure the column is populated correctly
Accenture
SG
@Sairohith The property has data for old cases, and few cases successfully showing the data after column population but most of the cases not showing the data. So, this verification is done already. How to Re-run Column Population job, I never saw this option of re-run when job is already completed, where did you find this can share screenshot? Column exist in Schema - this verified already before checking status under column population jobs. So, this verification is done already. Currently, we are doing forceful reindexing for each case in production every time new column is being added in DB schema as a workaround already, I am looking out for a solution of resolving this without going for custom forceful reindexing all the cases. If anyone has a solution can suggest. Thanks.
Tata Consultancy Services
IN
Hi @Upendhar,
Yes true, Even I can't see Column Populator, We can see status for the particular columns.
One morething, Pega will take care automatically column population while optimizing the Property Itself.
and @Sairohith Will you able to interpet/Extract BLOB by using SQL Query? If Yes please let me know which query we have to use ?
Kensho Tsuchihashi Wrote nice artcile with Questions and Answers How to optimize properties in production environment
Thanks,
Ashok
HCA Healthcare
US
To clarify, while Pega does handle column population during property optimization, there can be cases where not all historical values are populated due to underlying issues like incomplete BLOB interpretation.
If you'd like to extract data from the BLOB (pzPVStream) using SQL, you can use Pega's BLOB Decoder utility or write a custom SQL query with Pega's provided UDFs (User-Defined Functions). For instance, you can use pega.read_from_stream (available in PostgreSQL or other supported databases) to decode the BLOB and extract the property value. Here's an example query:
SELECT pega.read_from_stream(pzPVStream, '<PropertyName>') AS ExtractedValue, pzinskey FROM <TableName> WHERE <ExposedColumn> IS NULL;
This will fetch the value of the property from the BLOB for cases where the exposed column is null. Ensure that the <PropertyName> matches the exact property path, and <TableName> is the relevant database table. Let me know if you have any questions
Updated: 2 Jan 2025 22:11 EST
Tata Consultancy Services
IN
Hi @Sairohith
Thank you, Can you provide the screenshot of Query with Results? So that it will be easy to understand, e.g., I have attached the screenshot.
The below-mentioned query is to see/verify the values in the BLOB.
----****** With where clause ******----
select data.pr_read_from_stream('FirstName' , pzInsKey, pzPVStream) AS First_Name from data.pc_LIC_LICAutoI_Work
----****** Without where clause -- it will shows the all firstnames ******----
select data.pr_read_from_stream('FirstName' , pzInsKey, pzPVStream) AS First_Name from data.pc_LIC_LICAutoI_Work
Thanks,
Ashok
Accepted Solution
Updated: 20 Jan 2025 19:01 EST
HCA Healthcare
US
@UpendharIf the column population job is completed but data is missing for many cases, re-run the job by going to Dev Studio → Configure → System → Database → Column Populator, selecting the same table and column, and starting a new job (even if it was completed before). Use Tracer or logs in a lower environment to investigate why some cases are skipped, checking for errors or property mapping issues. Ensure Pega’s background processing is working correctly by verifying queue processors. Instead of reindexing cases individually, use the bulk case reindexing feature under Dev Studio → System → Search → Reindex. If the issue persists, clear existing column populator job data in the database (e.g., pr_sys_statusnodes table) and try again. Other solution is use the pega.read_from_stream function in SQL to extract property values from the BLOB (pzPVStream) and backfill the exposed column. If none of these resolve the issue, contact Pega support for a hotfix, thanks