Question
Incessant Technologies Pvt Ltd.
AU
Last activity: 3 May 2017 22:11 EDT
Report definition - Without acquiring lock on table
Pega 7.1.8 - Report Definition
1. How to configure report definition to get the records from a table without acquiring the lock on the table?, dirty read is fine.
2. How to customize (which activity to customize) the report definition to call custom activity but not the activity that gets called by default?
Regards,
Hareen
Pegasystems
IN
1) There is no way to configure a different isolation level for report definitions that what is defined on the data source.
2) Report definitions do not allow for customization of the SQL generated. Please let me know your use case on why you do need to customize. Is there a SQL that report definition configuration is unable to generate?
Incessant Technologies Pvt Ltd.
AU
One of the use case is to display the list of assignments to the user on click of a button. This use case doesn't need to get any lock on the table while fetching the records for us.
Database: SQL Server 2008 R3.
Pegasystems
IN
So as I understand, you want to display the assignments, for a given user, even if a different transaction may have deleted the same entries in that table? Why would you want to do that?
As I said before, we currently don't have a way to set the isolation level to allow for dirty reads. Even with list views, unless you are acquiring your own database connection to execute the query, there is no way to change the isolation level on the connection.
Incessant Technologies Pvt Ltd.
AU
This is one of the use case but there are many other use cases.Even in this use case, we would like to go with transaction isolation level set to read uncommitted.
With listviews, we could customize the listview activity to set the isolation level. So is there any activity that could be used to customize just like we have for list view?
Pegasystems
IN
There is no way to use a custom activity with report definitions. I would like to understand how you were changing the isolation level in the list view activity.
Pegasystems Inc.
AU
Hi Rajiv,
Following discussion with Hareen,
their requirement would be to have a report definition where they can define the isolation level, through either an activity or another other method, can you confirm if this is possible.
I have searched through a report definition, there is no possibility of defining any custom activity. furthermore SQL function rule do not have ability to set isolation level.
Would you know of any alternatives ? or maybe if this can be done on DB layer ?
Pegasystems
US
I didn't think you needed to get a lock when you run a report. Also, back when report rules were list view rules, you could specify your own get-content activity. Can you still do that with report rules ? /Eric
There is a strong case for report definitions to add WITH (NOLOCK) to generated DML from reports but that is not currently part of product behavior.
You should be able to use SQL function rule to add WITH (NOLOCK) to report-generated DML
Pegasystems Inc.
AU
Hi Hareen,
Maybe test this on your Dev, there is no OOTB implementation.
HI Andrew: would you have any PDN or steps to include the NOLOCK in the arbitrary text ?
Pegasystems Inc.
AU
There are often requirements to implement custom report functionality for business where the underlying report definition may take longer to complete, e.g. 1 minute while it must still allow intensive case update to happen. Since typical SQL SELECT will acquire a shared lock by default, an option to suppress this will be very useful to prevent any performance degradation while the report is running.