Migrating data from backend and custom forms

The instructions in this topic are applicable for a Database Administrator. If custom or backend forms have a large amount of data, you can first migrate a large portion of that data by exporting and importing with SQL commands, and then you continue to use DDM for the subsequent runs.

Alternatively, if your database administrator can establish a database link between the source and destination server databases, they can simply select and insert data, which does not involve backing up and deleting indexes or renaming the tables.

Forms that have minimal or no data model changes between versions

The following forms have minimal or no data model changes between versions (these forms are usually the largest):

• All Audit log system forms; for example, HPD:HelpDesk_AuditLogSystem
• NTE Notifier Log
• FB Flashboard History
• Worklog or workinfo (or both) (after fixing field lengths at database level)
• All custom forms with large data

To perform data migration of backend and custom forms by exporting and importing

1. Determine which forms contain data that you want to migrate. Typically, these forms will be the largest forms, having minimal or no data model changes between versions.
2. Get the schema IDs for the forms.
3. Compare the descriptions of the T, B, and H tables between the source and destination servers and make a record of the columns that will need to be modified or added. Look for a field length change; for example, if the length of a field is more in the source server, see if you need to increase the same field's length on the destination server to accommodate the field's data. 
4. Export the source tables. Ensure that you also export the attachment tables (BxxCyyyyyyyyy) if any exist for the form.
5. Rename the destination tables to prevent them from being overwritten.
6. Import the source tables to the destination database.
7. Modify or add the columns identified in step 3. 
8. Rebuild the views. 
9. Recreate the indexes on the T, B, and H tables after dropping them from the originals on the destination. You need to use the same table names.
10. Verify the tables.
11. Remove the backup tables that you created in step 5.  

Example SQL export and import commands to migrate data from a form 

This example describes the migration of data in the NTE:Notifier Log:TXXX form. The data is migrated between Oracle databases.

1. Export tables from the source database by running the following command:

   expdp system/password schemas=aradmin parallel=7 tables=txxx,bxxx,hxxx directory=dpump_dir dumpfile=ddm%U.expdp logfile=ddm_expdp.log

2. Stop all BMC Remedy AR System servers that connect to the destination database. 

3. For each table to be migrated, identify existing indexes. (You can add the output to a script later if you decide you want to recreate the indexes.)

   Run the following command:

   select dbms_metadata.get_ddl('INDEX',index_name,'ARADMIN')
   from dba_indexes
   where table_name in ('T955','B955','H955') and owner = 'ARADMIN';

4. Drop the indexes from the tables on the destination database. This action will prevent conflicts when you import the indexes.

5. Rename the tables on the destination database by running the following command:

   rename txxx to txxx_backup;
   rename bxxx to bxxx_backup;
   rename hxxx to hxxx_backup;

6.  Import the tables to the destination database.

   impdp system/password schemas=aradmin parallel=4 directory=dpump_dir dumpfile=ddm%U.expdp logfile=ddm_impdp.log

7. Start the BMC Remedy AR System server for this database. 

8. Using Dev Studio, recreate the DB views for each of the forms.

   Choose any field, move it, move it back, click Save.

9. Query the forms from the BMC Remedy Mid Tier and verify the data and total counts on the forms.

To perform data migration of backend and custom forms with a database link

This alternative method of migrating form data must by performed by a database administrator. 

1. Determine which forms contain data that you want to migrate. Typically, these forms will be the largest forms, having minimal or no data model changes between versions.
2. Get the schema IDs for the forms.
3. Establish a database link.
4. Compare the descriptions of the T, B, and H tables between the source and destination servers and make a record of the columns that will need to be modified or added. Look for a field length change; for example, if the length of a field is more in the source server, see if you need to increase the same field's length on the destination server to accommodate the field's data.
5. Backup up all T, B and H table data for all the schema IDs identified in step 2.

6. Issue the TRUNCATE TABLE statement to empty the T, B, and H tables, as shown in the following example: 

   Truncate table T1697;
   Truncate table B1697;
   Truncate table H1697;

7. Issue SELECT and INSERT statements to copy data from the source database tables to the destination DB tables from the arsystem.dbo schema to the arsystem91.dbo schema, as shown in the following example:
   
   

   Insert into arsystem91.dbo .T1697 (select * from arsystem.dbo.T1697);
   Insert into arsystem91.dbo .B1697 (select * from arsystem.dbo.B1697);
   Insert into arsystem91.dbo .H1697 (select * from arsystem.dbo.H1697);

   If the column names are required for the SELECT and INSERT statements, then add them in the following format. For example, if the destination table has more columns (fields) than the source table, use the columns from the source table to map the values because it is recommended to map values for columns that exist in the source only.

   Insert into arsystem91.dbo .T1697 (C1,C2,C3,C4,C5…….) select C1,C2,C3,C4,C5……. from arsystem.dbo.T1697)

8. Query the forms by using SQL or from the BMC Remedy Mid Tier to verify the data and total counts on the forms.

Where to go from here