KB268394: Enterprise Manager 10 data load process fails with error: "Violation of PRIMARY KEY constraint CT_EXEC_STATS_PK. Cannot insert duplicate key in object"

SUMMARY
This technical document covers a know issue with Strategy 10 Enterprise Manager, also affecting versions 10.1 and 10.1 Hotfix 1. The Strategy Enterprise Manager Data Load fails with error: "Violation of PRIMARY KEY constraint CT_EXEC_STATS_PK. Cannot insert duplicate key in object". This issue has been fixed in Strategy 10.2. Take advantage of upgrading for a fix.
Also, a fix  has been developed to address this known issue in versions 10,  10.1 and 10.1 Hotfix 1
  
SYMPTOM
When running a Strategy Enterprise Manager data load from Operations Manager, the data load fails with the following error message:
 

"Violation of PRIMARY KEY constraint CT_EXEC_STATS_PK. Cannot insert duplicate key in object"

 
CAUSE
In environments with a large number of Mobile clients, it has been observed that mobile executions can sometimes be logged a second time to the Staging Mobile Statistics tables once they have already been truncated after migrating its contents to the Statistics tables during a data load process. When running a second data load, the records that were added to the staging tables a second time will return the primary key constraint error when attempting to move them to the Statistics tables.
 
ACTION
This issue has been fixed in Strategy 10.2. Take advantage of upgrading for a fix.
Also, a fix  has been developed to address this known issue in versions 10,  10.1 and 10.1 Hotfix 1. See the workaround section.
 
WORKAROUND
The following steps can be followed to avoid seeing this issue:

1. Ensure that no data load process is currently running by checking the data load schedule(s).
2. Using Strategy DB Query Tool, run the following 2 queries:
   

   
   DELETE FROM STG_CT_EXEC_STATS WHERE EXISTS
   (
   SELECT DISTINCT STG_CT_EXEC_STATS.*
                   FROM STG_CT_EXEC_STATS
                   JOIN CT_EXEC_STATS
                   ON STG_CT_EXEC_STATS.DAY_ID = CT_EXEC_STATS.DAY_ID
                   AND STG_CT_EXEC_STATS.DEVICEINSTID = CT_EXEC_STATS.DEVICEINSTID
                   AND STG_CT_EXEC_STATS.CTSESSIONID = CT_EXEC_STATS.CTSESSIONID
                   AND STG_CT_EXEC_STATS.ACTIONID = CT_EXEC_STATS.ACTIONID
   )  
    ;
   
   DELETE FROM STG_CT_MANIP_STATS WHERE EXISTS
   (
   SELECT DISTINCT STG_CT_MANIP_STATS.*
                   FROM STG_CT_MANIP_STATS
                   JOIN CT_MANIP_STATS
                   ON STG_CT_MANIP_STATS.DAY_ID = CT_MANIP_STATS.DAY_ID
                   AND STG_CT_MANIP_STATS.DEVICEINSTID = CT_MANIP_STATS.DEVICEINSTID
                   AND STG_CT_MANIP_STATS.CTSESSIONID = CT_MANIP_STATS.CTSESSIONID
                   AND STG_CT_MANIP_STATS.ACTIONID = CT_MANIP_STATS.ACTIONID
                   AND STG_CT_MANIP_STATS.MANIPSEQUENCEID = CT_MANIP_STATS.MANIPSEQUENCEID
                   AND STG_CT_MANIP_STATS.MANIPVALUESEQ = CT_MANIP_STATS.MANIPVALUESEQ
   );

   
    
   
   
   
3. Run the data load again. This time it should be completed successfully without running into any primary key constraint errors.
4. Navigate to the path: "C:\Program Files (x86)\Strategy\Enterprise Manager\Scripts" (Windows) OR "/opt/Strategy/EnterpriseManager/Scripts" (Unix).
5. Edit the file "em_close_orphan_sessions_XXX.sql", when "XXX" refers to the database being used as the Statistics and Enterprise Manager Repository (e.g. "em_close_orphan_sessions_ora.sql"  = Oracle).
6. Add the queries in Step 2 to the Close Orphan Sessions script.The final version of the script should be as follows:
   

   
   /* RG - 4/19/2002 - Script created */
   /* VM - New version for Statistics Version 8.0 */
   
   UPDATE IS_SESSION_STATS
   SET DISCONNECTTIME = CONNECTTIME + 1, RECORDTIME = SYSDATE WHERE (SYSDATE - CAST(CONNECTTIME AS DATE)) > 0 AND DISCONNECTTIME IS NULL;
   
   
   UPDATE IS_PROJ_SESS_STATS
   SET DISCONNECTTIME = CONNECTTIME + 1, RECORDTIME = SYSDATE WHERE (SYSDATE - CAST(CONNECTTIME AS DATE)) > 0 AND DISCONNECTTIME IS NULL;
   
   
   DELETE FROM STG_CT_EXEC_STATS WHERE EXISTS
   (
   SELECT DISTINCT STG_CT_EXEC_STATS.*
                   FROM STG_CT_EXEC_STATS
                   JOIN CT_EXEC_STATS
                   ON STG_CT_EXEC_STATS.DAY_ID = CT_EXEC_STATS.DAY_ID
                   AND STG_CT_EXEC_STATS.DEVICEINSTID = CT_EXEC_STATS.DEVICEINSTID
                   AND STG_CT_EXEC_STATS.CTSESSIONID = CT_EXEC_STATS.CTSESSIONID
                   AND STG_CT_EXEC_STATS.ACTIONID = CT_EXEC_STATS.ACTIONID
   )  
    ;
   
   DELETE FROM STG_CT_MANIP_STATS WHERE EXISTS
   (
   SELECT DISTINCT STG_CT_MANIP_STATS.*
                   FROM STG_CT_MANIP_STATS
                   JOIN CT_MANIP_STATS
                   ON STG_CT_MANIP_STATS.DAY_ID = CT_MANIP_STATS.DAY_ID
                   AND STG_CT_MANIP_STATS.DEVICEINSTID = CT_MANIP_STATS.DEVICEINSTID
                   AND STG_CT_MANIP_STATS.CTSESSIONID = CT_MANIP_STATS.CTSESSIONID
                   AND STG_CT_MANIP_STATS.ACTIONID = CT_MANIP_STATS.ACTIONID
                   AND STG_CT_MANIP_STATS.MANIPSEQUENCEID = CT_MANIP_STATS.MANIPSEQUENCEID
                   AND STG_CT_MANIP_STATS.MANIPVALUESEQ = CT_MANIP_STATS.MANIPVALUESEQ
   );

   
   
7. Save the "em_close_orphan_sessions_XXX.sql" script.
8. For all configured data loads, ensure that the data load option 'Close orphan sessions' is always checked.

 
Notes:

• Users need to perform Step 2 as a one-time process before adding the same queries to the Close Orphan Sessions script. After doing that, the queries will automatically be run within every subsequent data load, given that the option to "close orphan sessions" is checked for the data load configuration.
• The provided queries were developed for an Oracle database. Customization might be needed if a different database is being used.
• It is strongly advised to do a full backup of the Statistics and Enterprise Manager databases prior to the manual execution of any queries against the database.