{"revision":"c10276e953456835cdbb1f3a60d58ae1980be353","document":{"id":38417,"path":"platform-administration/archive-reference-migration.md","title":"Migrating non-reference fields to reference fields","bundle":null,"doc_type":null,"release":"australia","body":"\n# Migrating non-reference fields to reference fields\n\nManually preserve sys\\_ids in reference fields instead of storing the display name as a string.\n\n## Determining tables to migrate\n\nTo begin the data migration process, run the following background script. This script creates migration records for both direct table extensions and related archived records in the Archive Rule Reference Migration \\[sys\\_archive\\_ref\\_migration\\] table, ensuring that only valid tables are processed.\n\n```javascript\nvar tables = GlideDBObjectManager.get().getAllExtensions(current.table);\n    for (i = 0; i < tables.size(); i++) {\n        var gr = new GlideRecord('sys_archive_ref_migration');\n        gr.addQuery('table', 'ar_' + tables.get(i));\n        gr.query();\n        if (!gr.next()) {\n            if (GlideTableDescriptor.isValid('ar_' + tables.get(i))) {\n                var gr2 = new GlideRecord(\"sys_archive_ref_migration\");\n                gr2.initialize();\n                gr2.setValue('rule', current.sys_id);\n                gr2.setValue('table', 'ar_' + tables.get(i));\n                gr2.setValue('reference_migration_progress', 'waiting');\n                gr2.insert();\n            }\n        }\n    }\n    //Also get insert related records tables as well\n    var map = new GlideRecord('sys_archive');\n    map.addQuery('table', current.table);\n    map.query();\n    if (map.next()) {\n        var id = map.getValue('sys_id');\n        if (!(id === undefined)) {\n            var related = new GlideRecord('sys_archive_related');\n            related.addQuery('archive_map', id);\n            related.addQuery('action', 'archive');\n            related.query();\n            while (related.next()) {\n                if (!GlideTableDescriptor.isValid('ar_' + related.getValue('table'))) {\n                    gs.log('Related Record table: ' + related.getValue('table') + ' not created yet');\n                    continue;\n                }\n \n                var gr3 = new GlideRecord(\"sys_archive_ref_migration\");\n                gr3.initialize();\n                gr3.setValue('rule', current.sys_id);\n                gr3.setValue('table', 'ar_' + related.getValue('table'));\n                gr3.setValue('reference_migration_progress', 'waiting');\n                gr3.insert();\n            }\n        }\n    }\n```\n\n## Copying archive references\n\nFor each table that goes through the migration process, the Archive Reference Copy \\(RefCopyJob\\) job identifies the sys\\_id of the reference fields and updates the display value to be the correct sys\\_id. The job configures 10k records at a time unless there are more than 10k records with the same timestamp. The migration progress relies on the archived timestamp.\n\n## Changing field types from string to reference\n\nAfter the tables associated with an archive rule completely migrate, the ArchiveRefJob job executes. This job changes the sys\\_dictionary types of the archive table from string to reference.\n\n## Fixing node failures for RefCopyJob and ArchiveRefJob\n\nIf node failure occurs while these jobs are running, it leaves the status of the data migration in an improper state. If the **RefCopyJob** fails, it can leave a table in a migrating status. You can verify this condition by checking if rows in the sys\\_archive\\_ref\\_migration are stuck in **migrating** status for an extraordinary amount of time. Update the specific row status from **migrating** to **waiting** and the **RefCopyJob** continues the data migration on the table when the job runs again.\n\nThe node can also fail when the **ArchiveRefJob** terminates prematurely. Check if tables have fields that are reference fields and some that are still string type fields. The job might have stopped in the middle of changing the field types. You can resolve this condition by setting up a trigger job to execute in a background script, which starts the process over again:\n\n```javascript\nGlideRecord trigger = new GlideRecord('sys_trigger');\ntrigger.initialize();\ntrigger.setValue('state', 0);\ntrigger.setValue('trigger_type', 0);\ntrigger.setValue('next_action', new GlideDateTime());\ntrigger.setValue('job_context', 'fcRuleId=' + ruleId);\ntrigger.setValue('name', 'Job Reference Migration' + ' Node - ' + new GlideClusterSynchronizer().getSystemID());\ntrigger.setValue('trigger_class', 'com.glide.db.auxiliary.job.ArchiveRefJob');\ntrigger.insert();\n```\n\n**Parent Topic:**[Managing data growth in Core UI](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/platform-administration/data-management-policies.md)\n\n"}}