Identified Issue

Microsoft SQL Server 2016 and later use the datetime2 data type which is not compatible with standard Pega Platform datetime architecture. So, if you plan to use these versions, for MSSQL JDBC driver version 12.2 or later, you can specify the datetime data type when configuring your JDBC driver URL. 

Below are some of the scenarios that can occur as a result of this change if no action is taken. Additionally, other issues may occur, and will be added to this document if discovered.

Keep up to date with the platform support guide and deployment guides to be appraised of this and similar types of issues.

Scenario 1: Duplicate processing on Queue Processors, impacting Kafka's disk usage (Stream service)

Due to the datetime vs datetime2 conflict between the Pega Platform and Microsoft's SQL 2016+ versions, delayed records that are already processed will get re-processed multiple times in an infinite loop. This happens because the delayed-processing mechanism that scans the database to remove already processed records is not able to fetch records correctly based on the datetime property. Hence, it fails to remove some of the already processed records, which results in executing these records repeatedly and eventually maxing out the Kafka disk space on the stream service side.

From the Pega side, on the Queue Processor landing page, items can remain in the Scheduled queue for any Queue Processor and the Ready to Process queue would have steady continued growth.

Scenario 2: Issue when client filters on a datetime property

When you are using the Data Grid Filter/Report definition, Obj-Browse, or a custom SQL command, and filtering on exposed date/time columns for classes with a BLOB, or classes that have a date/time property as part of the class key, the system may fail to return the correct results. 

Example of incorrect results:

You know that there are 22 records in a table that have a 11/20/yy date. But when you create a report filtering by the pxUpdateDateTime property on the 11/20/yy date, you only receive 10 results.

Details

This situation occurs in an internal class/table (with a BLOB column) which includes one or more exposed datetime properties. The exposed column value is different from the value in the BLOB column, due to increasing precision of the millisecond value and a rounding error. 

The reason that fewer results are displayed is because when multiple records have the same timestamp that differs only at the millisecond level, the filter will only display the top-most record.

As the out-of-the-box database tables almost always contain standard system datetime properties, the error could be seen when reporting on any of the Pega database tables.

Scenario 3: Issue when using a datetime property as an internal class key property

A Report Definition or custom SQL may fail to return the correct results when using Obj-Open by class-key property (pzInsKey), including a date/time property, as a filter. 

Again, the exposed column’s value is different from the pzInsKey’s precision value.

Scenario 4: Issue when using a datetime property as an external class key property

An Obj-Browse/Report definition or custom SQL may fail to return the correct results when using Obj-Open by class-key property (pzInsKey), including a date/time property, as a filter. 

Obj-Save may fail with a “duplicate key violation” exception.

The Data Type record tab may fail to edit/search/delete the record using the selected value.

Failed Filter : SELECT * FROM pega123.pr_Org_Sample_Data_DT1 "PC0" WHERE  ( "PC0"."TimeCreated" BETWEEN ?  AND ?  )  ORDER BY 1 ASC, 
2 ASC <2023-02-21 11:11:11.357> <2023-02-21 11:11:12.356>

Scenario 5: Issue when a data type/external class includes a datetime property

An Obj-Browse/Report definition or custom SQL may fail to return the correct results when using a date/time property as a filter. 

The Data Type record tab may fail to filter/edit the record using the selected value.

Scenario 6: Issue when a filter uses a function alias of datetime

Any filter using function alias of datetime may return no/incorrect results, including Pega User Defined Functions (UDF).

Scenario 7: Pega UDF is not supported on AWS/MSSQL

Pega UDF is not supported on AWS/MSSQL and Azure/MSSQL 2017+. You must make sure that all the properties for reporting are exposed.

To test whether all properties are exposed, create a Report Definition, and enable an application or ReferencingRuleSet filter.

Example:

select pxReferencingRuleSetName, pxReferencingRuleSetVersion, pxReferencingClassName, pxReferencingRuleName, pxWarningName, 
pxWarningType, pxIsWarningJustified, pyWarningJustifiedMessage

from pegarules.pr_index_warnings where pxWarningType = 'Performance' and pxInsIndexedClass = 'Rule-Obj-Report-Definition' and 
(upper(pxWarningName) like '%OPTIMIZED%'  or upper(pxWarningName) like '%EXPOSED%'  ) order by pxReferencingRuleSetName

Explanation

Beginning in Microsoft SQL Server 2016, Microsoft changed the value of the compatibility_level property. 

The difference in compatibility_level can cause changes with rounding datetime vs. datetime2 values.

For details, see: Change in datetime2 implementation in SQL Server 2016

Prior to MSSQL 2016, the compatibility_level value was always 120, which is the value that Pega software expects. The changed values in the later versions of MSSQL can cause a number of issues with Pega software, as described above.

Solution

Set a JDBC URL parameter to convert the datetime data type from datetime2 to datetime on the PegaRULES and PegaDATA JDBC data sources. This JDBC URL parameter can only be used with Microsoft's JDBC driver versions 12.2 and newer. Therefore, if the current version is lower than JDBC driver 12.2, it needs to be upgraded before setting the parameter.

The parameter in question is datetimeParameterType=datetime.

Once the parameter is added, restart all the Pega nodes.

If Scenario 1 is observed, manually clear the old delayed records from Queue Processors.

To clear the records manually, access the Queue Processors landing page in Admin Studio, click on the Scheduled count for each Queue Processor that has stuck records and clear them through Actions menu on the top-right section of the page.