SA-33112

Summary

The following error occurs in the logs for a campaign on a daily basis.
"java.sql.SQLSyntaxErrorException: ORA-00904: "C"."PARTITIONKEY": invalid identifier"

Error Messages

2017-01-23 11:25:01,239 [ PegaRULES-Batch-10] [ STANDARD] [ <YOUR APP>:01.01.01] (l.access.ConnectionManagerImpl) ERROR - Not returning connection 20 for database "externalmktdata" to the pool as it previously encountered the following error
User ID: System
Last SQL: SELECT R.FlowErrorDetail as "R_FlowErrorDetail", R.FlowStatus as "R_FlowStatus", R.LastFlowStep as "R_LastFlowStep", R.ParentGroup as "R_ParentGroup", R.ParentIssue as "R_ParentIssue", R.ParentOffer as "R_ParentOffer", R.ProgramKeyCode as "R_ProgramKeyCode", R.StatusWork as "R_StatusWork", R.TreatmentKeyCode as "R_TreatmentKeyCode", R.WaitExpirationTime as "R_WaitExpirationTime", R.pyPartitionKey as "R_pyPartitionKey", R.pyWorkID as "R_pyWorkID", R.pyOriginalComponent as "R_pyOriginalComponent", R.pxSegment as "R_pxSegment", R.pxPriority as "R_pxPriority", R.BundleType as "R_BundleType", R.IsOfferInProgress as "R_IsOfferInProgress", R.pxInteractionID as "R_pxInteractionID", R.pyTreatment as "R_pyTreatment", R.BundleHead as "R_BundleHead", R.KeyCode as "R_KeyCode", R.pyGroup as "R_pyGroup", R.pyDivision as "R_pyDivision", R.pyName as "R_pyName", R.BudgetedCost as "R_BudgetedCost", R.StartDate as "R_StartDate", R.ExpectedResponseRate as "R_ExpectedResponseRate", R.pxOutcomeTime as "R_pxOutcomeTime", R.pyPropensity as "R_pyPropensity", R.pyCategory as "R_pyCategory", R.Type as "R_Type", R.BundleName as "R_BundleName", R.pyChannel as "R_pyChannel", R.ClickThroughURL as "R_ClickThroughURL", R.Revenue as "R_Revenue", R.EndDate as "R_EndDate", R.pyApplicationVersion as "R_pyApplicationVersion",
...
"CS_Channel9Quarterly",CS.Channel9SemiMonthly as "CS_Channel9SemiMonthly",CS.Channel9Weekly as "CS_Channel9Weekly",CS.Channel9Yearly as "CS_Channel9Yearly",CS.CustomerID as "CS_CustomerID",CS.PartitionKey as "CS_PartitionKey" FROM PEGANBAM_EXTERNAL.BatchOutPR5941 R INNER JOIN PEGANBAM_EXTERNAL.MARKETING_SPINE C ON ( ( R.CustomerID = C.CASE_ID AND R.PARTITIONKEY = C.PARTITIONKEY ) ) LEFT OUTER JOIN PEGANBAM_EXTERNAL.MKT_CONTACT_SUMMARY CS ON ( ( R.CustomerID = CS.CustomerID AND R.PARTITIONKEY = CS.PARTITIONKEY ) ) WHERE (R.FLOWSTATUS = 'Wait' AND R.WAITEXPIRATIONTIME <= current_timestamp AND R.PARTITIONKEY = 7 AND C.PARTITIONKEY = 7) ORDER BY R.CustomerID, R.BundleName, R.BundleParent desc, R.pxRank
java.sql.SQLSyntaxErrorException: ORA-00904: "C"."PARTITIONKEY": invalid identifier

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3685)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1376)
at com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl.execute(DatabasePreparedStatementImpl.java:581)
at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:6202)
at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:5979)
at com.pega.decision.dsm.batch.RDBBatchReader.<init>(RDBBatchReader.java:73)
at com.pegarules.generated.createSQLBasedIterator_071017_zTX2OGhikH8QqU1riZfsfA.createSQLBasedIterator07_10_17(createSQLBasedIterator_071017_zTX2OGhikH8QqU1riZfsfA.java:131)
at com.pegarules.generated.createSQLBasedIterator_071017_zTX2OGhikH8QqU1riZfsfA.invoke(createSQLBasedIterator_071017_zTX2OGhikH8QqU1riZfsfA.java:107)
at com.pega.pegarules.generation.internal.library.LibraryRuntime.resolveAndinvokeFunctionViaReflection(LibraryRuntime.java:133)
at com.pega.pegarules.generation.internal.library.LibraryRuntime.invokeLibraryRuntime(LibraryRuntime.java:110)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeLibraryRuntime(Executable.java:9000)
at com.pega.pegarules.priv.generator.LibrarySupport.resolveAndInvokeFunctionViaReflection(LibrarySupport.java:181)
at com.pegarules.generated.pega_decisionengine_batchdecision.createSQLBasedIterator(pega_decisionengine_batchdecision.java:488)
at com.pegarules.generated.activity.ra_action_processofferbatch_395384499f5b16d3ce1768b3d6273cbc.step34_circum0(ra_action_processofferbatch_395384499f5b16d3ce1768b3d6273cbc.java:3327)
at com.pegarules.generated.activity.ra_action_processofferbatch_395384499f5b16d3ce1768b3d6273cbc.perform(ra_action_processofferbatch_395384499f5b16d3ce1768b3d6273cbc.java:753)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3505)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10563)
at com.pegarules.generated.activity.ra_action_processwaitingofferflows_08a5d23431caadb13652d81931027b53.step7_circum0(ra_action_processwaitingofferflows_08a5d23431caadb13652d81931027b53.java:729)
at com.pegarules.generated.activity.ra_action_processwaitingofferflows_08a5d23431caadb13652d81931027b53.perform(ra_action_processwaitingofferflows_08a5d23431caadb13652d81931027b53.java:191)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3505)
at com.pega.pegarules.session.internal.async.agent.QueueProcessor.runActivity(QueueProcessor.java:721)
at com.pega.pegarules.session.internal.async.agent.QueueProcessor.execute(QueueProcessor.java:602)
at com.pega.pegarules.session.internal.async.BatchRequestorTask.run(BatchRequestorTask.java:945)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.performTargetActionWithLock(PRSessionProviderImpl.java:1259)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:1008)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:901)
at com.pega.pegarules.session.internal.async.BatchRequestorTask.run(BatchRequestorTask.java:670)
at com.pega.jsr166backport.java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:991)
at com.pega.jsr166backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:532)
at java.lang.Thread.run(Thread.java:745)
2017-01-23 11:25:01,247 [ PegaRULES-Batch-10] [ STANDARD] [ YOUR_APP:01.01.01] (fferBatch.System_Queue_.Action) ERROR - ProcessOfferBatch: Batch execution failed. java.lang.Throwable: com.pega.pegarules.pub.PRRuntimeException: Cannot list the database
at com.pegarules.generated.activity.ra_action_processofferbatch_395384499f5b16d3ce1768b3d6273cbc.step34_circum0(ra_action_processofferbatch_395384499f5b16d3ce1768b3d6273cbc.java:3331)
at com.pegarules.generated.activity.ra_action_processofferbatch_395384499f5b16d3ce1768b3d6273cbc.perform(ra_action_processofferbatch_395384499f5b16d3ce1768b3d6273cbc.java:753)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3505)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10563)
at com.pegarules.generated.activity.ra_action_processwaitingofferflows_08a5d23431caadb13652d81931027b53.step7_circum0(ra_action_processwaitingofferflows_08a5d23431caadb13652d81931027b53.java:729)
at com.pegarules.generated.activity.ra_action_processwaitingofferflows_08a5d23431caadb13652d81931027b53.perform(ra_action_processwaitingofferflows_08a5d23431caadb13652d81931027b53.java:191)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3505)
at com.pega.pegarules.session.internal.async.agent.QueueProcessor.runActivity(QueueProcessor.java:721)
at com.pega.pegarules.session.internal.async.agent.QueueProcessor.execute(QueueProcessor.java:602)
at com.pega.pegarules.session.internal.async.BatchRequestorTask.run(BatchRequestorTask.java:945)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.performTargetActionWithLock(PRSessionProviderImpl.java:1259)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:1008)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:901)
at com.pega.pegarules.session.internal.async.BatchRequestorTask.run(BatchRequestorTask.java:670)
at com.pega.jsr166backport.java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:991)
at com.pega.jsr166backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:532)
at java.lang.Thread.run(Thread.java:745)

Steps to Reproduce

Run a Pega Marketing campaign

Root Cause

A defect or configuration issue in the operating environment . The marketing configuration was changed recently to introduce partitioning.
In the class db table the column is named differently to PARTITIONKEY. The error is coming from Offer Wait Processing agent and the queue items gets into broken state.

Resolution

Perform the following local-change:
Update the underlying table column and use PARTITIONKEY column to partition data.
Update the Pega Marketing custom class external mapping tab to assign PARTITIONKEY table column to .PartitionKey property.