SUPPORT / SAMPLES & SAS NOTES
Problem Note 61171: %TKMI_JOB_RO etl_end_service generates "ORA-00001: unique constraint (DI_DM.RPP_PLAN_PROD_METRICS_PK) violated"
 |  |  |  |  |
The SAS® Promotion Optimization %TKMI_JOB_RO etl_end_service task might generate the following error in the MerchIntelGridSvr.log file:
<YYYY-MM-DD HH:MM:SS,sss> [pool-<n>-thread-<n>] ERROR com.sas.solutions.di.server.db.SQLHandler - BAD SQL(writeProductMetrics): INSERT INTO rpp_plan_prod_metrics ( plan_sk, prod_hier_sk, kpi_sk, fcst_kpi_value, base_kpi_value, actual_kpi_value ) VALUES ( ?, ?, ?, ?, ?, ?)
<YYYY-MM-DD HH:MM:SS,sss> [pool-<n>-thread-<n>] ERROR com.sas.solutions.di.tkmiserver.rpp.ExecuteBatchPostEval - Batch post evaluaion processing failed for promotion with PLAN_SK=<plan_sk>
com.sas.solutions.di.server.db.api.PersistenceException: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (DI_DM.RPP_PLAN_PROD_METRICS_PK) violated
at com.sas.solutions.di.server.db.SQLHandler.execute(SQLHandler.java:93)
at com.sas.solutions.di.server.db.SQLHandler.execute(SQLHandler.java:102)
at com.sas.solutions.di.tkmiserver.rpp.eval.OptEvalHelper.insertProductForecastMetrics(OptEvalHelper.java:343)
at com.sas.solutions.di.tkmiserver.rpp.eval.OptEvalHelper.aggregateProductForecastMetrics(OptEvalHelper.java:250)
at com.sas.solutions.di.tkmiserver.rpp.ExecuteBatchPostEval.run(ExecuteBatchPostEval.java:104)
at com.sas.solutions.di.tkmiserver.rpp.Execute.execute(Execute.java:161)
at com.sas.solutions.di.tkmiserver.rpp.RPPTkService.aggregatePendingPromotions(RPPTkService.java:1490)
at com.sas.solutions.di.tkmiserver.data.DataServiceImpl$RPPAggregationTask.run(DataServiceImpl.java:9258)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (DI_DM.RPP_PLAN_PROD_METRICS_PK) violated
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.executeForRows(T4CPreparedStatement.java:1044)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3665)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1352)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
at com.sas.solutions.di.server.db.SQLHandler.executeUpdate(SQLHandler.java:73)
at com.sas.solutions.di.tkmiserver.rpp.eval.OptEvalHelper$6.handler(OptEvalHelper.java:356)
at com.sas.solutions.di.server.db.SQLHandler.execute(SQLHandler.java:90)
... 10 more
This error occurs when the MerchIntelGridSvr log4j.xml setting for the com.sas.solutions.di.tkmiserver logger is set to TRACE.
To work around this issue, perform the following steps:
- Make a backup of the <SAS-configuration-directory>/<app_server_level>/Applications/SASMerchIntelGridSvr5.41/log4j.xml file.
- Edit the <SAS-configuration-directory>/<app_server_level>/Applications/SASMerchIntelGridSvr5.41/log4j.xml file.
- Modify the com.sas.solutions.di.tkmiserver logger and change the level value from "TRACE" to "DEBUG"
- Stop and restart the MerchIntelGridSvr. You must be logged on as sas to stop or start the MerchIntelGridSvr.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Promotion Optimization | Microsoft® Windows® for x64 | 5.2_M4 | 9.3 TS1M2 | ||
| Linux for x64 | 5.2_M4 | 9.3 TS1M2 | ||||