Page Comparison

...

Make sure that the model editions in DEV and PROD are compatible.
(Model versions will never match, since dev is always ahead of prod. The requirements are that the physical tables are compatible (same columns, same data types, etc.)
Stop Tomcat. Do not submit any new loads. Stopping Tomcat is critical - not just a nice-to-have precaution. Keeping Tomcat running and dropping the DEV data location will cause a jdbc datasource not bound in context type of error and restarting Tomcat was not able to easily fix it. If this happens - you may have to delete and recreate the data location in the Workbench. So be warned!
Backup the DEV data location schema.

Make a note of the data location's UUID.

Code Block

language	sql
title	Save the existing UUID

select UUID from DEV_MDM.DL_DATA_LOC;
/* Sample result: 09DE459BA8934FAE92A217B60A263EDD 
 * You will need this value in step #8
 */

Backup the PROD data location schema to a file you will use in the next step.
Consult your DBA for specifics to your implementation.
In Oracle: this is likely to be done using Oracle's data pump, but there are other mechanisms for backup which could be equally good.
In PostgreSQL: this is typically done with pg_dump.
In Microsoft SQL Server: this is typically done using BACKUP DATABASE.
Consult your DBA for specifics to your implementation.

Drop and re-create DEV schema

Code Block

language	sql
title	Drop and re-create DEV data location schema

/* This is an Oracle example. Your exact commands will be different. But this sample highlights the key ideas. */
DROP USER DEV_MDM CASCADE;
CREATE USER DEV_MDM IDENTIFIED BY DEV_MDM DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE TO DEV_MDM;

Load the PROD schema backup into the DEV database schema.
Note: Depending on the details of your environment, you may need to re-grant permissions on the objects in this schema.

Update DL_DATA_LOC to use the previous UUID value.

Code Block

language	sql
title	Restore the UUID

update DEV_MDM.DL_DATA_LOC set UUID = '09DE459BA8934FAE92A217B60A263EDD';
/* Use the value you saved from step 4 for the UUID */

Make sure that the loadId and batchid sequences in the repository are greater than the max loadid and batchid in DL_BATCH

Code Block

language	sql
title	Confirm that the loadid and batchid sequences are large enough

select
  DEV_REPO.SEQ_LOADID.nextval + 1 as next_dev_loadid,
  PROD_REPO.SEQ_LOADID.nextval + 1 as next_prod_loadid,
  DEV_REPO.SEQ_BATCHID.nextval + 1 as next_dev_batchid,
  PROD_REPO.SEQ_BATCHID.nextval + 1 as next_prod_batchid,
  CASE 
    WHEN (
          DEV_REPO.SEQ_LOADID.nextval  >= PROD_REPO.SEQ_LOADID.nextval
      AND DEV_REPO.SEQ_BATCHID.nextval >= PROD_REPO.SEQ_BATCHID.nextval
    ) THEN 'IDs are good. No action needed.' 
    ELSE 'You must manually increase the nextval of SEQ_LOADID or SEQ_BATCHID' 
  END as message
from dual;
/* In cases where your DEV and PROD environments are physically separated, you must modify this query to gather the DEV and PROD values separately. */

If the sequence current value is less than the max in DL_BATCH then update it to the max. This will ensure that you don't start a load or a batch using an id that has already been reserved.

Restart Tomcat. You may resume submitting data loads.

...

Versions Compared

Old Version 39

New Version 40

Key