Symptoms

Support tickets reported the following symptoms:

Pega Archive (pyPegaArchive) is not working and displays errors indicating that the column Case.pyresolvedtimestamp does not exist.

Pega Archive (pyPegaArchive) throws exception about missing pyResolvedTimestamp.

Pega Archive (pyPegaArchive) jobs are not working with logs detecting pyPegaArchiver, pyPegaIndexer, pyPegaPurger, and pyArchival_ReIndexer. You enabled case archival from Case Designer, the Settings tab.

After running the Pega Archive (pyPegaArchive) job, the status in the pr_metadata becomes Archive-Ready with the log error that contains no description: Exception :Data-ArchivalMetadata.pzDataBrokerService:Threw . . .

When starting the pzPerformArchive activity from the agent or manually, the archive process does not archive the cases and an exception is logged.

Errors

Support tickets reported the following errors:

2023-09-24 09:29:27,147 [ PegaRULES-Batch-57] [ STANDARD] [ ] [xxxxxxx] ( BatchRequestorExecution) ERROR BUO6I9ZKE6O6CZK1AH18GBEZC8E5OE7D6A 7709000 - Batch activity "Data-ArchivalMetadata.pzDataBrokerService"

--------------------------------------------------------------------------------------------------------------------------------------------------

column Case.pyresolvedtimestamp does not exist.

-----------------------------------------------------------------------------------

Exception :Data-ArchivalMetadata.pzDataBrokerService:Threw . . .  

---------------------------------------------------------------------------------------

Caused by: com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 0 SQLState: 42703 Message: ERROR: column Case.pyresolvedtimestamp does not exist Position: 42DatabaseException caused by prior exception: org.postgresql.util.PSQLException: ERROR: column Case.pyresolvedtimestamp does not exist Position: 42 | SQL Code: 0 | SQL State: 42703From: (B2H1XW9SAZKLC2E7UYP00OOZ1LRCYIIO5A)

SQL: SELECT

"Case"."pzinskey" AS "pzInsKey" ,

"Case"."pyresolvedtimestamp" AS "pyResolvedTimestamp"

FROM

pegadata.pr_other "Case"

WHERE (

"Case"."pxobjclass" = ?

AND "Case"."pxcoverinskey" IS NULL

AND "Case"."pzinskey" NOT IN (

SELECT

"ArchivedCases"."pyinskey" AS "pyinskey"

FROM pegadata.pr_metadata "ArchivedCases"

WHERE (

"ArchivedCases"."pyobjclass" = ? )

)

AND "Case"."pystatuswork" LIKE ?

AND CAST(

EXTRACT(

'day' from ((CURRENT_TIMESTAMP ::timestamp without time zone) - (to_timestamp("Case"."pyresolvedtimestamp", 'YYYYMMDD 00:00:00')::timestamp without time zone))) AS INT) >= ? ) LIMIT 100 OFFSET 0

Caused by SQL Problems.Problem #1 , SQLState 42703, Error code 0: org.postgresql.util.PSQLException: ERROR: column Case.pyresolvedtimestamp does not exist Position: 42

----------------------------------------------------------------------------------------

2021-06-09 05:15:14,025 [ PegaRULES-Batch-10] [ STANDARD] [ ] [ Tax:01.01.02] ( internal.mgmt.Executable) ERROR - Exception:
com.pega.pegarules.pub.PRRuntimeException: PRRuntimeException
at com.pega.pegarules.data.internal.sqlapi.exec.NativeSQLListExecutor.execute(NativeSQLListExecutor.java:85) ~[prprivate-data.jar:?]
at com.pega.pegarules.data.internal.sqlapi.exec.NativeSQLListExecutor.execute(NativeSQLListExecutor.java:52) ~[prprivate-data.jar:?]
at com.pega.pegarules.data.internal.PRDataProviderImpl.executeNativeSQL(PRDataProviderImpl.java:1678) ~[prprivate-data.jar:?]
at com.pega.pegarules.data.internal.PRDataProviderForModulesImpl.executeNativeSQL(PRDataProviderForModulesImpl.java:138) ~[prprivate-data.jar:?]
at com.pega.platform.logicaldatastore.databroker.internal.dao.ArchivalMetaDataDAO.getCasesToBeArchived(ArchivalMetaDataDAO.java:220) ~[logical-datastore.jar:?]
at com.pega.platform.logicaldatastore.databroker.internal.DataBrokerImpl.getCasesToBeArchived(DataBrokerImpl.java:125) ~[logical-datastore.jar:?]
at com.pega.platform.datastoreexecutor.archivebroker.internal.CrawlerManagerService.processParentInstancesPerCommit(CrawlerManagerService.java:148) ~[datastoreexecutor.jar:?]
at com.pega.platform.datastoreexecutor.archivebroker.internal.CrawlerManagerService.startCrawlerTasks(CrawlerManagerService.java:109) ~[datastoreexecutor.jar:?]
at com.pega.platform.datastoreexecutor.archivebroker.internal.CrawlerManagerService.runService(CrawlerManagerService.java:75) ~[datastoreexecutor.jar:?]

The query that is failing is this one:
SELECT "Case"."pzinskey" AS "pzInsKey" , "Case"."pyresolvedtimestamp" AS "pyResolvedTimestamp" FROM pegadata.pr_other "Case" WHERE ( "Case"."pxobjclass" = ? AND "Case"."pxcoverinskey" IS NULL AND "Case"."pzinskey" NOT IN (SELECT "ArchivedCases"."pyinskey" AS "pyinskey" FROM pegadata.pr_metadata "ArchivedCases" WHERE ( "ArchivedCases"."pyobjclass" = ? ) ) AND "Case"."pystatuswork" LIKE ? AND CAST(EXTRACT('day' from ((CURRENT_TIMESTAMP ::timestamp without time zone) - (to_timestamp("Case"."pyresolvedtimestamp", 'YYYYMMDD 00:00:00')::timestamp without time zone))) AS INT) >= ? ) LIMIT 100 OFFSET 0

-----------------------------------------------------------------------------------------

Caused by: com.pega.pegarules.data.internal.rd.queryexec.ExecuteQueryException: An error occured on executing the query for the report definition - There was a problem getting a list: code: 0 SQLState: 42703 Message: ERROR: column Case.pyresolvedtimestamp does not exist

Explanation

User misunderstanding of Pega Archive (pyPegaArchive) and its usage, especially regarding class mapping

An archival policy is defined on a class Group-Generic-Work-Notify that is mapped to the table pr_other. Case archival policy is defined on this class. The pzPerformArchive activity is trying to get the case details of the class Group-Generic-Work-Notify from the table pr_other, where some of the columns (for example, pyResponseTimeStamp) do not exist. This is the root cause of the exception captured in the logs.

The logs show the following query:

There was a problem getting a list: code: 0 SQLState: 42703 Message: ERROR: column Case.pyresolvedtimestamp does not exist Position: 42DatabaseException caused by prior exception: org.postgresql.util.PSQLException: ERROR: column Case.pyresolvedtimestamp does not exist Position: 42 | SQL Code: 0 | SQL State: 42703From: (B5UNT70XAFR3DH78NWK4C7M6I0A8P4JU0A) SQL: SELECT "Case"."pzinskey" AS "pzInsKey", "Case"."pyresolvedtimestamp" AS "pyResolvedTimestamp" FROM pegadata.pr_other "Case" LEFT OUTER JOIN (SELECT "excluded"."pyparentinskey" AS "srcol1" FROM pegadata.pr_archival_excluded_case "excluded" WHERE ("excluded"."pystatus" = ? AND "excluded"."pxremovedatetime" IS NULL AND "excluded"."pyobjclass" = ?)) "excluded" ON (("Case"."pzinskey" = "excluded"."srcol1")) WHERE ("Case"."pxobjclass" = ? AND "Case"."pxcoverinskey" IS NULL AND "Case"."pzinskey" NOT IN (SELECT "ArchivedCases"."pyinskey" AS "pyinskey" FROM pegadata.pr_metadata "ArchivedCases" WHERE ("ArchivedCases"."pyobjclass" = ?)) AND "excluded"."srcol1" IS NULL AND ("Case"."pystatuswork" LIKE ?) AND "Case"."pyresolvedtimestamp" < ?) LIMIT 1000 OFFSET 0

Environments

The problem was reported in the following environments:

• Pega Platform™ 8.8.3 in Pega Cloud® services 2.26.2
• Pega Platform 8.8.1 in Client Cloud
• Pega Platform 8.8.1 in Pega Cloud services 2.24.4
• Pega Platform 8.5.3 on-premises environment

Solution

To correct work table for your Pega deployment, disable the archive policy on the class Group-Generic-Work-Notify, which is mapped to pr_other, or change the mapping of the class Group-Generic-Work-Notify.

1. On the GenericNotify instance of the class Data-Retention-Policy, clear the Archive checkbox.
2. Change the Case status to match Resolved-* pattern.
   Important: Case Archival processes cases only if the status is Resolved-*. Notice the hyphen (-). It will not process cases with status Resolved *.
3. Re-run the pzPerformArchive activity.
4. In the Application Explorer, check instances of Log-ArchivalSummary to see how many cases each archival job scheduler can pick up.

Related content

Archiving and expunging case data