Share all details about your problem, including any error messages you may have received.
Hi,
jasper_page_bucket table contains large amount of data which is around 2gb and We are trying to prune jasper_page_bucket data externally by deleting oldest data from Jasper_page_bucket table. This process will affect jasper_result table, task result table and task result UI page.
Please let me know who taskresult, jasper_page_bucket and jasper_result are related?
I’m trying to correlate how these three objects have been correlated.
The spt_task_result, spt_jasper_page_bucket and spt_jasper_result tables are related in the following way:
spt_task_result table:
This table stores the metadata and status information about tasks that have been executed, such as reports or certifications.
Each row in the spt_task_result table represents a specific task instance.
The spt_task_result table typically contains columns like task ID, task name, task type, launcher, start time, completion time, and status and this had a column report which is typically for your reports
spt_jasper_result table:
The spt_jasper_result table is associated with the spt_task_result table.
It stores references to the actual report data generated by Jasper Reports, which is a reporting tool integrated with SailPoint IdentityIQ.
When a report task is executed, an entry is created in the spt_task_result table, and a corresponding entry is added to the spt_jasper_result table and typically the report id from spt_task_result is the id of the spt_jasper_result table
spt_jasper_page_bucket table:
The spt_jasper_page_bucket table is used to store the actual content of the generated Jasper reports.
Each row in the spt_jasper_page_bucket table represents a page of a report.
The spt_jasper_page_bucket table contains column handler_id which will be present in spt_jasper_result table
@iamksatish Thanks for the explanation and correlation of these three tables.
We are at the verge of deleting the data from past 4 years from spt_jasper_page_bucket table alone, if we delete it that it will affect task result and jasper table?
We also have another approach like pruning all three tables via performance maintenance by mentioning days before task result deletion in settings page in iterative process like start 1800 days and iterating with 1700 days.
Frist approach will have any impact?
Second approach will have impact on DB if we increase days and task will run for longer period of time.
Please be careful with the first approach because if you are going with table data deletion you relationship with other tables may corrupt data and cause unwanted issues sometimes, please make sure you test this first
or you can go with a API approach of terminator of JasperResult Object actually, this may help you to some extent but please try this in lower with appropriate testing
With second approach go with incremental mode of older days.
i have few questions about second approach these are the below data of our QA environment. When i run performance maintenance task with prune task result and global settings updated as 100 for days before deletion of task result. completed task result are up till 29-Sep-24. But data from jasper result and jasper page bucket are not getting pruned, is there any further option needs to be enabled in task and global settings?
@kchengalvarayan
I don’t think there is a OOTB pruning for the jasper report related objects, you have to write a custom rule which can delete the older objects using API or use IIQ console command to delete the JasperResult Objects