Specifying How the Schema Change Wizard Works

After starting the Schema Change Wizard, you will need to answer a series of questions that determine how the update will take place.

The first choice is how the wizard will apply the changes. You must decide if you want the system to:

• Execute changes immediately on the project database. This option makes the changes immediately on the current database without any intermediate steps. This option is most often used when the changes are small and well understood. The only way to "undo" this type of change is to restore the database from a backup or manually restore the database to it's previous configuration.
• Output the changes to an SQL Script. With this option, you generate scripts to apply separately to the database through an external tool. This option is generally used when the schema changes are significant, or when you want to review the specific changes that will be made to the physical database before they are made. The sql script containing the changes will be saved in the file: ...\archibus\projects\users\public\dt\database-update\personalized-database. Typically, you will choose the SQL script option when:

• you are updating mission-critical databases on which round-the-clock business processes depend. These databases do not allow downtime during which to make changes.
• changes must be pre-vetted on a staging server.
• changes need to be applied to multiple project databases.

Note: The files for re-creating SQL views (ab-products\common\sqlviews) no longer include the afm_tbls and afm_flds import statements; these table and field definitions were moved to external csv import files (ab-products\common\sqlviews\dt). If you want to apply changes directly to the database using the "Output changes to an SQL view" option, you can manually import these files with this procedure:
1. Using data-transfer, import the .csv files from ab-products\common\sqlviews\dt.
2. Execute the .sql files matching the pattern schemawiz*<dbtype>*.sql files from \ab-products\common\sqlviews where <dbtype> represents your database vendor.
You now proceed to the step below: "The next decision is the type of change the wizard should make"

The next decision is the type of change the wizard should make:

• Re-create tables from scratch. This option completely wipes out the current table and re-creates a new table using the information in the Archibus data dictionary. Choose this option if you want to:
  • rename columns
  • get rid of deleted columns
  • re-create indexes
  • reorganize columns, for example: list columns in the same order as in the Archibus schema
  • change the primary key of the table
  • change a field that did or will be auto-numbered
• Alter existing table structure. This option leaves the physical table in place and adds or modifies column definitions. It will not delete columns that have no corresponding entry in the Archibus data dictionary. Consequently, this option allows your physical database to differ from the conceptual definition in Archibus. This may be desirable when the physical database contains columns that are not used by Archibus or your extensions. On the other hand, having the data dictionary consistent with the physical database is desirable for ease of maintenance.

The third parameter tells the wizard which tables to update.

• All tables with changes in the data dictionary made by the Database Update Wizard. In order to use this option, you first need to compare the Archibus data dictionary containing your changes and the physical SQL tables in the database. This comparison is performed when you import data dictionary tables using the Database Update Wizard. See Performing the Data Transfer: Merge Data Dictionary Tables.
• Tables like: With this option, you can name specific tables (separated by semicolon) or groups of tables using the % wildcard specification. For example, the table specification bl; fl; rm%; em% changes the bl table, the fl table, all tables that begin with rm, and all tables that begin with em.

Finally, there are two check boxes:

• Include all validating tables. This option includes all validating tables for any table listed in the"Tables like" option. For instance, if you name the Rooms (rm) table, this option will add not only the validating tables specified for fields in the Rooms table, but will also include tables for validated fields in the Buildings table and validating tables for the validated fields of the Buildings table, and so on. Entering the Rooms table and setting this option results in a list of more than 50 tables.
• Re-create all foreign key constraints into or from the tables. In cases where the structure of the table is changing, the Schema Change Wizard automatically drops and re-creates the constraints. This option is useful when the only change is that you have added or changed a validating table or fields to validate.

Oracle Databases Only

For Oracle projects, the Schema Change Wizard supports length semantics. The Set Oracle to measure field size in CHARs not BYTEs option has the following effects:

• If this option is checked:
  • Executes the DDL: ALTER SESSION SET NLS_LENGTH_SEMANICS = CHAR
  • Any create/alter column definition will use VARCHAR2(<size>) and actually create it in database as VARCHAR2(<size> CHAR)
• If this option is not checked:
  • Executes the DDL: ALTER SESSION SET NLS_LENGTH_SEMANICS = BYTE
  • Any create/alter column definition will use VARCHAR2(<size>) and actually create it in database as VARCHAR2(<size> BYTE)

Next

Click on Compare and Next to Update the SQL Tables with Database Dictionary Changes.