Migrating non-reference fields to reference fields
Summarize
Summary of Migrating non-reference fields to reference fields
This process guides ServiceNow customers through migrating non-reference fields (stored as strings) to proper reference fields by preserving sysids. This improves data integrity and system consistency when handling archived records and their related tables.
Show less
Determining tables to migrate
Start by running a background script that creates migration records in the sysarchiverefmigration table for both direct table extensions and related archived records. This ensures only valid tables are processed for migration. The script also handles related archive map tables to include all relevant data in the migration.
Copying archive references
The Archive Reference Copy (RefCopyJob) job processes each table, identifying reference field sysids and updating display values accordingly. It processes records in batches of 10,000 based on archived timestamps to efficiently handle large datasets.
Changing field types from string to reference
After migration of associated tables completes, the ArchiveRefJob job updates the sysdictionary field types on archive tables from string to proper reference fields, finalizing the migration.
Handling node failures
If a node failure interrupts the RefCopyJob or ArchiveRefJob, migration statuses can become inconsistent. For RefCopyJob, check for records stuck in "migrating" status within sysarchiverefmigration and manually update them to "waiting" to resume migration.
For ArchiveRefJob interruptions, verify if fields are partially migrated (some reference, some string). To recover, trigger a background job that restarts the field type conversion process by inserting a job trigger record for ArchiveRefJob. This ensures migration resumes and completes successfully.
Practical benefits for ServiceNow customers
- Ensures archived reference fields maintain correct sysids rather than display names, preventing data inconsistency.
- Automates migration across related archive tables to maintain data integrity.
- Provides mechanisms to detect and recover from node failures during migration, minimizing downtime and manual intervention.
- Improves overall system reliability by properly configuring field types to reference fields after migration.
Manually preserve sys_ids in reference fields instead of storing the display name as a string.
Determining tables to migrate
var tables = GlideDBObjectManager.get().getAllExtensions(current.table);
for (i = 0; i < tables.size(); i++) {
var gr = new GlideRecord('sys_archive_ref_migration');
gr.addQuery('table', 'ar_' + tables.get(i));
gr.query();
if (!gr.next()) {
if (GlideTableDescriptor.isValid('ar_' + tables.get(i))) {
var gr2 = new GlideRecord("sys_archive_ref_migration");
gr2.initialize();
gr2.setValue('rule', current.sys_id);
gr2.setValue('table', 'ar_' + tables.get(i));
gr2.setValue('reference_migration_progress', 'waiting');
gr2.insert();
}
}
}
//Also get insert related records tables as well
var map = new GlideRecord('sys_archive');
map.addQuery('table', current.table);
map.query();
if (map.next()) {
var id = map.getValue('sys_id');
if (!(id === undefined)) {
var related = new GlideRecord('sys_archive_related');
related.addQuery('archive_map', id);
related.addQuery('action', 'archive');
related.query();
while (related.next()) {
if (!GlideTableDescriptor.isValid('ar_' + related.getValue('table'))) {
gs.log('Related Record table: ' + related.getValue('table') + ' not created yet');
continue;
}
var gr3 = new GlideRecord("sys_archive_ref_migration");
gr3.initialize();
gr3.setValue('rule', current.sys_id);
gr3.setValue('table', 'ar_' + related.getValue('table'));
gr3.setValue('reference_migration_progress', 'waiting');
gr3.insert();
}
}
}Copying archive references
For 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.
Changing field types from string to reference
After 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.
Fixing node failures for RefCopyJob and ArchiveRefJob
If 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.
GlideRecord trigger = new GlideRecord('sys_trigger');
trigger.initialize();
trigger.setValue('state', 0);
trigger.setValue('trigger_type', 0);
trigger.setValue('next_action', new GlideDateTime());
trigger.setValue('job_context', 'fcRuleId=' + ruleId);
trigger.setValue('name', 'Job Reference Migration' + ' Node - ' + new GlideClusterSynchronizer().getSystemID());
trigger.setValue('trigger_class', 'com.glide.db.auxiliary.job.ArchiveRefJob');
trigger.insert();