10 Replies Latest reply on May 4, 2018 2:46 PM by Mohammad Rehman

    Delta Data Migration - Unique Index Violation

    Marek Ceizel

      Hello experts,

       

      I kindly ask you here for discussion / tips / ideas.

       

      We are doing a Delta Data Migration. Source is a Production Platform and the time difference of DB Dump on Destination Platform is about one month (expected lot of records). We use RRRchive (thank you Misi Mladoniczky) with setting is Copy and Overwrite, Qual is Modified_Date (6) and compare id is Request_ID (1) - this is fixed in RRRchive.

       

      A good example for our problem is form CTM:People Permission Groups. Of course we cannot ask the customer to freeze the Production Platform so much that the People Permission cannot be changed. But in this form the entries are deleted and created by the system always when you modify some Person Permissions. Therefore we got almost 800 unique index violations during Delta Data Migration on this form. Unique Index is InstanceID and Person_ID+PermissionGoupID.

       

      I understand what happened. Some Permissions were removed, some were added. For different Person or maybe for the same person. It could happen that for the same person permissions were removed and the same permission added again. why and however the Request_ID changed, but the Unique index values stayed the same (as PersonID was not changed and Permission Group ID either).

       

      Therefore I need to remove records which do not exist on Production (source) but existon on destination.

      - RRRchive syncdeleted crashes the tool on my server , I don't know why.

      - DeltaDataMigrator from BMC has no options for Deleting (or is this somehow possible).

       

       

      Is there some other tool or best practice I can use except truncating destination (because I don't want to truncate all andf the CTM:People Permission Groups from is not the only one where I got this problem)

       

       

      Thanks a lot for help/tips

       

      Best Regards

      Marek

        • 1. Re: Delta Data Migration - Unique Index Violation
          ryan nicosia

          When we ran DDM, there were some forms where we just used Import tool to import records and there were also a couple forms where we moved the data using Atrium Integrator.

           

          My recommendation would be to just delete the data in the target and then do a complete export and import of the values from your source.  You can use either of the 2 tools mentioned.

          • 2. Re: Delta Data Migration - Unique Index Violation
            Mohammad Rehman

            I use DDM with compare option, after few sync i run the SQL queries to get the difference on both systems and then run another set to mark the orphan/duplicate records to hard delete.

            Note:- I have Oracle database, its easier to create production db link remprod on staging db

            Here is the comparing query on Oracle DB

            Check Records missing on Staging:

            SELECT PEOPLE_PERMISSION_GROUP_ID, REMEDY_LOGIN_ID, PERMISSION_GROUP, PERMISSION_GROUP_ID, INSTANCEID, ASSIGNEE_GROUPS_PARENT, PERMISSION_TAG_NAME, PERSON_ID FROM CTM_PEOPLE_PERMISSION_GROUPS@REMPROD

            MINUS

            SELECT PEOPLE_PERMISSION_GROUP_ID, REMEDY_LOGIN_ID, PERMISSION_GROUP, PERMISSION_GROUP_ID, INSTANCEID, ASSIGNEE_GROUPS_PARENT, PERMISSION_TAG_NAME, PERSON_ID FROM CTM_PEOPLE_PERMISSION_GROUPS;

             

            Check Records on Staging but not on production:

            SELECT PEOPLE_PERMISSION_GROUP_ID, REMEDY_LOGIN_ID, PERMISSION_GROUP, PERMISSION_GROUP_ID, INSTANCEID, ASSIGNEE_GROUPS_PARENT, PERMISSION_TAG_NAME, PERSON_ID FROM CTM_PEOPLE_PERMISSION_GROUPS

            MINUS

            SELECT PEOPLE_PERMISSION_GROUP_ID, REMEDY_LOGIN_ID, PERMISSION_GROUP, PERMISSION_GROUP_ID, INSTANCEID, ASSIGNEE_GROUPS_PARENT, PERMISSION_TAG_NAME, PERSON_ID FROM CTM_PEOPLE_PERMISSION_GROUPS@REMPROD;

             

            then you can run this to mark these orphan records from staging:

            UPDATE CTM_PEOPLE_PERMISSION_GROUPS

            SET Submitter='DELETE'

            WHERE PEOPLE_PERMISSION_GROUP_ID IN ( Select a.PEOPLE_PERMISSION_GROUP_ID from

            (SELECT PEOPLE_PERMISSION_GROUP_ID, REMEDY_LOGIN_ID, PERMISSION_GROUP, PERMISSION_GROUP_ID, INSTANCEID, ASSIGNEE_GROUPS_PARENT, PERMISSION_TAG_NAME, PERSON_ID FROM CTM_PEOPLE_PERMISSION_GROUPS

            MINUS

            SELECT PEOPLE_PERMISSION_GROUP_ID, REMEDY_LOGIN_ID, PERMISSION_GROUP, PERMISSION_GROUP_ID, INSTANCEID, ASSIGNEE_GROUPS_PARENT, PERMISSION_TAG_NAME, PERSON_ID FROM CTM_PEOPLE_PERMISSION_GROUPS@REMPROD) a);

             

            You can adjust the queries according to your environment.

            2 of 2 people found this helpful
            • 3. Re: Delta Data Migration - Unique Index Violation
              Marek Ceizel

              Hello together,

               

              Thanks guys for the inputs.

              @Ryan, this is no go for us. We need to do a real delta data migration. We have no time for export, import, compare or check. now it is not prod environment but when we go live, we have only one hour reserved outage.

               

              @Mohammad: SQL is of course an option. Anyway we have no point in network where we can access both databases. we managed hardy to get a computer which can access both arsystems (so actual prod and new dev/test/prod).

               

              These two tools should be the easy solution but one has no features and another one crashes. I will ask Misi if there is some bug. The syncdeleted of rrrchive would be good option for us.

               

              I just thought there is some another tool or some hack for deltadatamigrator from bmc (so that it deletes too).

               

               

              Thanks again

              best regards

              Marek

              1 of 1 people found this helpful
              • 4. Re: Delta Data Migration - Unique Index Violation
                ryan nicosia

                This won't impact your outage window.  On day of your outage, 2 hours before you actually start, take a fresh export of that form and import it to your target.  It won't take you but 15 minutes.   You put a real admin freeze in place on people permissions the day before and you should be all set.

                • 5. Re: Delta Data Migration - Unique Index Violation
                  Misi Mladoniczky

                  Hi,

                   

                  You could take out a few forms and do a target_clearallrecords=YES which would delete all records prior to your execution.

                   

                  I presume you use the transfertype=SYNCTOTARGET, which might encounter these problems based on how the data was changed. Running it multiple times should usually decrease the number of violation errors and finally there would be none left.

                   

                  If a lot of records was deleted from the form, you might try the transfertype=SYNCDELETED first, which would delete all records no longer existing. After this is done you can run the transfertype=SYNCTOTARGET.

                   

                          Best Regards - Misi, RRR AB, http://rrr.se

                  5 of 5 people found this helpful
                  • 6. Re: Delta Data Migration - Unique Index Violation
                    Jameer Inamdar

                    Hi Marek,

                     

                    Migrator can help you here, Follow link How to handle record deletion in DDM

                     

                    HTH

                    1 of 1 people found this helpful
                    • 7. Re: Delta Data Migration - Unique Index Violation
                      Misi Mladoniczky

                      Hi,

                       

                      One of the good things with RRR|Chive compared to DDM, is that there will be no orphaned records. The SYNCTOTARGET will take care of everything.

                       

                      The problem with the duplicate index might not be one of orphaned records, it might be that of changed records as well.

                       

                      If you have three records for example, with data A, B, C.

                       

                      You can change this in the source system to X, B, A and then to C, B, A.

                       

                      The problem when you do a delta migration is that when you try to import the first record which now should contain C, the target server already has C in the last record resulting in a duplicate index error. When it comes to the last record that should change from C to A, it will also fail as the first record failed to update from A to C...

                       

                      In other words it is hard to do a migration of the following data without first deleting all records:

                      Source Server data to migrate to Target ServerTarget Server data prior to Delta Data MigrationComment
                      CAWill fail as C already exist in the last record
                      BB
                      ACWill fail as A already exists in the first record.

                       

                      So in this case you can opt for the target_clearallredords=YES in RRR|Chive to delete all records in the target prior to the transfer.

                       

                      The alternative would be to manually delete the failed records and run the SYNCTOTARGET operation again.

                       

                              Best Regards - Misi, RRR AB, http://rrr.se

                      4 of 4 people found this helpful
                      • 8. Re: Delta Data Migration - Unique Index Violation
                        Marek Ceizel

                        Hello Misi,

                         

                        Thanks a lot for you reply. Actually your example is also good anyway this is not our case. You compare field 1 in rrrchive and this is never reused after record deletion. If data are changed in a record, then almost never unique indexed fields.

                         

                        Our problems were data removed on source and recreated - like permissions

                        New data created on destination using requestids of different data from source (but we shouldnt create new data on dest, i agree)

                        Data removed from source but present on dest.

                         

                        As you wrote, deletion and synctotarget should be the right.

                        Btw-any idea why syncdeleted crashes the tool (leaving modified filters in system) ?

                         

                        Thank you

                        Best regards

                        marek

                        • 9. Re: Delta Data Migration - Unique Index Violation
                          Misi Mladoniczky

                          Hi,

                           

                          I do not know why it crashes, of course it should not.

                           

                          I think the SYNCDELETED might not be used that often. I recall a bug that made SYNCDELETED crash, but this was fixed.

                           

                          Did you use the version compiled against the 9.1 ARAPI? The 7.6.04 version is from 2013, and it might not have the SYNCDELETED bug fixed...

                           

                                  Best Regards - Misi, RRR AB, http://rrr.se

                          • 10. Re: Delta Data Migration - Unique Index Violation
                            Mohammad Rehman

                            Hi Marek,

                             

                            I just wanted to make you and other honorable community members who are upgrading to 9.1.04x from previous versions, please be aware if you are using DDM or using User tool to export and Import using Data Import tool or any utility which is written in C to export will lock out the users while sync of User form.

                            Work around is export the "login name" and "password" fields using midtier ar system report and then import using Data import tool with option update existing record and duplicate check using login name field with suppress filter check.

                            Misi Mladoniczky, please share your tool update whether it uses C API or Java API.

                            Here is the Defect ID SW00540999

                            Thanks

                            Mohammad

                            3 of 3 people found this helpful