This process is required when a data location has been deleted (from the workbench/application builder side) without dropping data and the user wants to recreate a new data location referencing this same schema.
This could also be used in the process of restoring a data location schema dump on another environment.
If trying to achieve this without running the following steps, when trying to create the new data location the workbench will throw an error explaining that the schema isn't empty and already contains Semarchy tables.
To workaround the error we'll need to remove some key tables and sequences that are used by Semarchy internal mechanisms to find out if the schema already holds data location details or not.
- Tables: All tables that start with "DL_" (DL_DATA_LOC, DL_BATCH and others. The exact list varies with the xDM release version.)
Full procedure for Semarchy xDM v4 or v5
- Make sure to backup the current data location schema before going any further
Drop the DL_DATA_LOC table
Copy the content of other DL_ tables to temporary tables (do not try to rename existing tables or end up with conflicting indexes later)
Drop all DL_ tables (make sure the USR_DL tables have been created and contain data)
Get current sequence values and keep them (we will delete these later and restore them with the same values)
Drop the 3 sequences
- SQL Server only: delete both the scalar-valued user function
sem_split_tbltype as well as the user_defined table type
- Create a new data location from the workbench, referencing this schema. This should now be allowed because DL_ tables and sequences do not exists anymore in the schema)
- Once the data location installation is done, all missing DL_ tables and sequences should have been recreated
- Stop Semarchy (to prevent anyone from accessing it)
Restore data to DL_ tables using USR_DL_ temporary backup tables
Re-align sequences values
- Restart Semarchy
- Test the application, everything should be running ok at this stage
Make sure everything works, drop temporary backup USR_DL_ tables to clean schema
When using this procedure to synchronize data across environments, pay attention to the current value of both SEQ_LOADID and SEQ_BATCHID (repository schema), or could end up re-processing rows that were already been submitted with the same loadid or batchid on the source environment.
Also keep in mind that Continuous loads and Notification policies are defined at the data location level. There may be external processes that rely on these elements, so make sure that recreating them before deleting the data location.