Working with Excludes and Data Preservers on TPC Tables When CloningDescriptionThis article details steps that customers/admins can follow to ensure that orphan records are not created when cloning extensible TPC tables. A TPC table for starters is a table that physically exists in the database. Overview Before following the steps provided please note that data preservers and excludes should only be configured on tables that consist of configuration data. This is namely any table that is found within the 'Application File' (sys_metadata) hierarchy as data preservers and excludes are meant for configuration type data only. Any configuration outside of this as at the customer/admin discretion and may result in undesirable results Example Tables Diagram: Table A > Parent table that is extensible Table B > Child of Table A Table C > Child of Table A In the diagram above we can see that there are a set of tables that exist both in our source and target instances. If a clone were to be scheduled from instance 1 to instance 2 we would expect that all the data from tables A, B, and C from instance 1 will overwrite the data from tables A, B, and C in instance 2. Scenario 1: I need to preserve all the data on Table A in instance 2 To accomplish this task we will need to use data preservers/excludes but first we should review how excludes and data preservers work Data preservers - only used for configuration data and the process involves taking an XML copy of all data on the table to be re-applied later Excludes - only use for configuration data and copies over the schema of the table from the source to the target but does not copy over any data from the table The general configuration that most users/admin would implement to accomplish this would be to create a data preserver on Table A in Instance 1 but before this is done it is important to understand the data/table structure of Tables A, B, and C. Looking at the structure of the tables, any record that exists in Tables B or Tables C will also have an entry in Table A since Tables B and C are children's tables of A. Another way of looking at this is that tables B and C are actually classes of the main parent table, Table A. If a data preserver is created only against Table A the only record truly preserved is the record where the class 'IS A' (A12345). There will be entries for records B23456, B23457, and C56789 but these records will be from the source instance from Table A in Instance 1. Simply put, when a data preserver is configured for Table A, users/admins should be aware that table A also contains all of the records in Table B and Table C.Therefore a preserver also then needs to be created against all the tables in Table A hierarchy (preserver needed for Table B and Table C). In the example above let's assume that the data from a sys_id perspective is the same between record B23456 in Instance 1 and Instance 2 but there have been additional changes (data added) to the record which exists in Instance 2. When a clone has performed the record on the target instance is expected to be preserved however if the same record exists in the source it will then overwrite the data that is in the target even with a data preserver configured. To ensure that the target records are truly preserved requires that a data preserver and an exclude is created for every single table in the Table A hierarchy (A-C). This will ensure that data on the target is preserved and the exclude will ensure that no data from the source is copied over to the target. Orphaned Data/Ghost Records Using the logic described above improper configuration of data preservers/excludes for TPC tables that have table extensions can easily result in orphaned data/ghost records if the correct configuration is not put into place. Ex: For the sake of our example we are going to assume that the records in Instance 1 and Instance 2 have different sys_ids as this is what really happens when there are orphaned records after a clone. User-created a data preserver and an exclude on Table A (assuming the sys_ids are different) Result: All records on Table B and C are orphaned (child records are orphaned) records since the sys_ids of the records from the source instance are not the same as the target. To see that this is truly a user/admin could attempt to open the records from Table B and C in the Table A list view to find a message indicating "No record found." Additionally, since the sys_id's for records in Table B and Table C are different the records that are Tables B and C are also orphaned since there is no Parent record that exists for these records in Table A. To prevent orphaned data when attempting to preserved data on a target instance as such, users/admins should create a data preserver and exclude it against all tables in the table hierarchy. If the damage has been done here are 2 options to resolve the issue. Re-clone again and ensure the correct data preservers and data excludes are properly configuredThis alternative is in the context of the example above. Using the class field we can actually write a simple script to trick the system and then reclassify the records as a class of A. Once this is done since there is no longer orphaned data in the picture the user/admin can then delete the record and then if the data exists in a different instance import the data into the target instance. Example script: //This script will only work for records which are orphaned at the parent level where the child record did not carry over. If this is a reverse orphan situation where the child exist but the parent is not available please contact support as these would need to be handled from the database. The easier option is to clone again with the correct configuration. findOrphans('A'); function findOrphans(table) { var gr = new GlideRecord(table); gr.query(); while(gr.next()) { if(isOrphan(gr)) { gs.info("Updating orphan from " + table + ": " + gr.sys_id); gr.sys_class_name = table; gr.setWorkflow(false); gr.autoSysFields(false); gr.update(); } } } function isOrphan(gr) { if(gr.sys_class_name == null) return false; var childClass = new GlideRecord(gr.sys_class_name); childClass.get(gr.sys_id); return !childClass.isValidRecord(); } (The code provided here should always be tested before executing on any instance. The provide code is in the context of the example provided as there may not be a class field available to users/admin) //This code essentially changes the class of the records in Table B and C in the example above which now no longer makes them orphaned. Once this is done the record can then be deleted and the user/admin can import the data from another instance.