Applies to Pega Business Intelligence Exchange on Pega Platform™ versions 7.1.8 through 23.1.
Symptoms
Errors
Explanation
Details
Solution
Related content
Symptoms
As a BIX administrator, you see a runtime error when processing BIX extract rules if you have selected the BIX filter criterion Use last updated time as start (pxCommitDateTime). Using this filter criterion causes BIX to fetch an excessive volume of data, which causes the extract processing to time out. This problem occurs generally and more frequently during product updates.
This issue is erratic and unpredictable because of the Details provided in the Explanation.
Errors
When you attempt to run the Extract rule, you see the following error:
Could not process: PRRuntimeException
In cloud environments, you see an error like the following error:
Timeout while SQL query executes;
SELECT "PC0"."pzpvstream" AS "pzPVStream", "PC0"."pxcommitdatetime" AS "pxCommitDateTime", "PC0"."pzinskey" AS "pzInsKey" FROM pegadata.xxxx_xxxxxx_xx_WORK "PC0" WHERE ("PC0"."pxobjclass" = ? AND "PC0"."pxcommitdatetime" >= ? AND "PC0"."pxcommitdatetime" < ?) ORDER BY 3 ASC
Explanation
The BIX filter checkbox labeled Use last updated time as start originally used pxUpdateDateTime. In Pega Platform™ version 7.1.9, the checkbox was changed to use pxCommitDateTime.
BIX Extract should not use the BIX filter criterion Use last updated time as start (pxCommitDateTime).
A backward compatibility defect in Pegasystems code or rules causes a full database table scan that results in a socket timeout for BIX Extract processing. The full database table was scanned because the column population job was run using the updated pxCommitDateTime column.
The BIX extract filter criterion Use last updated time as start (pxCommitDateTime) was modified by Pega in Pega Platform version 7.1.9 to evaluate the database update time for records rather than the case update timestamp. This causes a full database table scan.
A defect correction has been requested: Use last updated time as start will use pxSaveDateTime.
Details
It is important to know the distinction between transactions made by the application layer compared to the reporting engine layer only.
pxSaveDateTime
pxSaveDateTime is always set by the reporting engine layer when the BLOB is updated. For example, any Obj-Save would be recorded here. It is updated when the application modifies cases and other work objects. pxSaveDateTime is the default property that you should use in BIX.
pxCommitDateTime
pxCommitDateTime is always set by the reporting engine layer when the row is updated. For example, imports and properties exposed as database columns are recorded here. This setting does not update pxUpdateDateTime or pxSaveDateTime.
Do not use this criterion filter for BIX extract processing in Pega Platform releases later than 7.1.9.
In general, pxCommitDateTime is not recommended for use because it is updated when any record is saved by the Pega engine.
Use pxCommitDateTime for the following use case:
When you migrate or import application data from another system, pxSaveDateTime and pxUpdateDateTime are not updated. The only property that is updated in this scenario is pxCommitDateTime.
pxUpdateDateTime
pxUpdateDateTime is set by specific activities in the rules layer only. For example, a simple Obj-Open, Property-Set, Obj-Save does not update this timestamp. It is updated when the application modifies cases and other work objects.
pxUpdateDateTime is maintained by the application. It is your responsibility to update this property when needed.
SocketTimeout, Statement_Timeout, or BIX Query Timeout with pxCommitDateTime
When you use pxCommitDateTime, you might see a SocketTimeout, Statement_Timeout, or BIX Query Timeout.
Since early releases of Pega Cloud, the SocketTimeout setting has been in place to prevent SQL statements from running when a node needed to be re-provisioned.
The limitation to this setting is that the backend database has no idea that the client (the Pega instance) has disconnected. This can cause runaway disconnected transactions on the database. When this happens, the DBA needs to terminate the SQL statements (using the KILL command) that sometimes have run on the database for hours and, in some instances, for days.
To prevent runaway database transactions, specify a value for the setting Statement_Timeout.
This setting ensures agreement between the client side (the Pega instance) and the backend database that the transaction will be terminated and rolled back after the time specified in the Statement_Timeout setting.
SocketTimeout and Statement_Timeout limit all database access from the Pega instance. This is good for restarting and re-provisioning and for application stability.
The BIX Query Timeout defaults to the reporting engine timeout of 30 seconds. The BIX extract code dynamically creates a report that the following DSS allows you to set for the timeout used by BIX when it creates its report:
Owning Ruleset: Pega-RULES
Purpose: BIX/selectQueryTimeOut
Value: 60
The maximum allowed value in a cloud environment is 90.
Example scenario for when pxCommitDateTime causes timeout issues
pxCommitDateTime can work well as a BIX filter, causing no issues for years. However, consider this example: When the system needs to add a new exposed property (optimized for reporting), a full database table scan occurs. All cases, work objects, and other artifacts will be extracted. The high-volume of data being extracted will most likely (not always) exceed the timeout values, the default values, or the values that you have specified for SocketTimeout, Statement_Timeout, and BIX/selectQueryTimeOut.
Solution
You might have some BIX Extraction use cases where pxUpdateDateTime is the better option and, in very rare cases, where you might want to use pxCommitDateTime.
Complete the following steps:
- Specify the appropriate BIX Extract filters to extract data within the time limits that you need. See Details.
- When specifying BIX Extract filters, use pxSaveDateTime.
For this label, specify Operator as Greater than and Value as Last Extraction Time.
Do not use Use last updated time as start (pxCommitDateTime): Clear this checkbox if it is selected. - Then run the extract.
BIX Extract filters will be enhanced in a future release of Pega Platform.
Watch the Pega Support Center Pega Platform Resolved Issues and search for the following Issues, when the releases are available.
For Pega Platform version 8.8.5, search for ISSUE-833106.
For Pega Platform release 23.1.1, search for ISSUE-833105.
For Pega Platform release 24.1, search for ISSUE-660619.
Related content
Pega Documentation
Creating and running an Extract: Filter Criteria
Troubleshooting BIX in Pega Cloud environments >> See this article’s sections:
Extraction process fails with query timeout exception
Support Documents
Troubleshooting BIX for cloud environments >> See Query timeout exception
Troubleshooting BIX for on-premise environments >> See Query timeout exception
Developer Knowledge Share
Using BIX with large number of records