SQL Queries - CAST Central Base - Corruptions on Transactions - How to list and clean Transactions that are targeting the same Object

This page provide queries that list and clean Transactions that are targeting the same Object

1. Run the following query in order to instal a temporary table:

   CREATE TEMPORARY TABLE transaction_object_tmp AS 
     (SELECT object_id, 
             snapshot_id 
      FROM   dss_object_info o2 
      WHERE  o2.object_type_id = 30002) 

2. Check transactions that target the same entry point. Replace the <current snapshot id> and <previous snapshot id> with the corresponding values before runnig the query. 

   SELECT l2.snapshot_id        AS lastsnap, 
          l2.previous_object_id AS transactionid_lastsnap, 
          l2.next_object_id     AS firstobjectid_lastsnap, 
          l1.snapshot_id        AS previoussnap, 
          l1.previous_object_id AS transactionid_previoussnap, 
          l1.next_object_id     AS firstobjectid_previoussnap 
   FROM   dss_link_info l1 
   JOIN   transaction_object_tmp t1 
   ON     t1.object_id = l1.previous_object_id 
   AND    t1.snapshot_id = l1.snapshot_id 
   JOIN   dss_link_info l2 
   ON     l2.next_object_id = l1.next_object_id 
   AND    l2.previous_object_id != l1.previous_object_id 
   AND    l2.link_type_id = l1.link_type_id 
   AND    l2.snapshot_id = <CURRENT snapshot id> 
   AND    l1.snapshot_id = <previous snapshot id> 
   AND    l1.link_type_id = 11002 
   JOIN   transaction_object_tmp t2 
   ON     t2.object_id = l2.previous_object_id 
   AND    t2.snapshot_id = l2.snapshot_id

   Query result example

   3;60764;3456;2;46786;3456
   Query result interpretation

    This means that transaction 60764 from snapshot 3, and transaction 46786 from snapshot 2 are refering to the same object (object with ID 3456) 

In order to fix this corruption behavior do the following:

1. If you are using an AIP version lower then 8.2.4 then install the stored procedures contained in TOOLKIT_DEDOUBLE.sql then go to step 3
2. Else install the stored procedures contained oin the file TOOLKIT_DEDOUBLE.sql that is located at the followoing path of the install directory: <CAST_Install>\InstallScripts\CastStorageService\EFP_CENTRAL, then go to step 3

3. Run the following:

   select DEDOUBLE_ALL( 0,1)