Updating SQL Tables with Database Dictionary Changes (Schema Change Wizard)

After specifying the behavior of the Schema Change Wizard and choosing the Save and Compare button, the Wizard moves you to the Update SQL Tables tab so that the Wizard can generate the physical representation of the SQL tables and fields in the project to which you are connected.

This can be done in one of two ways, depending on the options that you chose when you specified the behavior of the Schema Change Wizard:

• execute the changes immediately on the current database
• output the changes to an SQL Script

The Wizard moves to the Update SQL Tables tab and shows the progress of the comparison between the Archibus data dictionary definitions and the physical table definitions. The wizard creates a table of differences, which it displays on the Update SQL Tables tab.

At this point, the Wizard has not yet made changes and the status of the differences is shown as pending. If you have not already done so, you should make sure that you have a backup of your project database before proceeding.

You can use the check boxes to select the tables for which you want to execute changes.

Execute Changes Immediately

If you chose to execute changes immediately, when you click on Start, the system steps through the differences and changes the physical database to match the Archibus data dictionary. The progress of the changes can be monitored in the Update Job Progress Window at the top of the form. The wizard announces the current application as it progresses through these phases:

1. Altering data dictionary tables
2. Dropping foreign keys
3. Altering tables
4. Re-creating foreign keys

You will also see the status of the differences change from Pending, to In Progress, and then to Updated.

When the system finishes updating the SQL tables, it automatically moves you to the Re-Create Structures tab.

Output Changes to SQL Script

If you chose to output changes to a SQL script, the system steps through the differences and creates a file containing sql statements that can be used to change the physical database to match the Archibus data dictionary. The Wizard generates the update.sqlfile and saves it in the folder: ...\archibus\projects\users\public\dt\database-update\personalized-database\03_changes-database-table-structure.sql.

1. Run the Schema Change Wizard and choose the option to create an SQL script. See Output Changes to an SQL Script for the steps to do so.
2. Apply it and test the script on a staging server.
3. Apply the script to the production database server by loading it into the SQL query editor for your server. You can use the following query editors:

• for Sybase, use the ISQL utility
• for Microsoft SQL Server, use the Query Analyzer
• for Oracle, use SQL Plus or SQL Worksheet (Note SQL Plus may need parameters set to allow for longer lines than the default.)

4. Restart the production application server. (There is no need to restart the production database server.)