Database Update Wizard
Schema Change WIzard

Running the Database Update Wizard and Schema Change Wizard on Different Database Servers

This topic contains the following sections:

• Oracle Destination Databases

• Microsoft SQL Server Destination Databases

• General Notes

• Data Copy Issues

• Copying Data with Circular References

• Custom Databases

See Also

• Database Update Wizard Overview

• Schema Change Wizard Overview

Oracle Destination Databases

• Trailing Spaces. Trailing spaces on CHAR and VARCHAR fields are removed.
• Time Fields. The date portion of time fields that are part of a primary key is set to 1970-01-01 to match any existing data generated from the Archibus grid. (This affects tables such as wrcf, wrpt, wrtl, hwrcf, hwrpt, hwrtl which all use time fields as part of the primary key.)
• Auto-Numbered Tables. Auto-numbered tables are implemented via a trigger and sequence added to each auto-numbered table.
• Auto-Numbered Fields. You should only use the AUTOINCREMENT default value for the primary key of a table.
• Reducing Data Precision. Oracle will not let you use an ALTER TABLE statement to decrease precision if that table already has data. If you get the error "ORA-01440: column to be modified must be empty to decrease precision or scale" choose the option of the Schema Change Wizard that re-creates the table from scratch rather than the default option, which alters the existing table structure. Another option is to remove the data from that field before running the wizard.
• Copying Preventive Maintenance Summary Results Data. When you copy the data in the PmpSum table from Sybase to Oracle you will see the error "ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert" for every record that has an Equipment Code (eq_id) field whose value is a single blank (" "). This blank does not translate, and as a result these records will not be added to the destination database. However, these records are not necessary. The PmpSum table is a results table, meaning that its contents are updated by Archibus before the are presented to any user. As such, you can safely ignore these error messages.
• Use Field. If you use the Schema Change Wizard to update the "Use" field to "Use1", the wizard cannot rename the field on Oracle databases as this functionality is not supported. Any data you have will still be in the "Use" field. To copy this data to the "Use1" field, use SQL Plus to issue a command in the form "UPDATE EM SET USE1=USE".
• Hoteling views. Oracle databases require SQL views for Hoteling. If you use the Database Update Wizard to produce a new Oracle project database from an SQL Server database, after you have completed the transfer in, you will need to run the Schema Change Wizard. The Schema Change Wizard has a step built into it that re-creates a number of database objects, among them the SQL views needed for Archibus applications.

Microsoft SQL Server Destination Databases

• Trailing Spaces. Trailing spaces on CHAR and VARCHAR fields are removed.
• Time Fields. The date portion of time fields that are part of a primary key are set to 1970-01-01 to match any existing data generated from the Archibus grid. (This affects tables such as wrcf, wrpt, wrtl, hwrcf, hwrpt, hwrtl which all use time fields as part of the primary key.)
• Auto-Numbered Tables. Auto-numbered tables are implemented via an IDENTITY constraint added to each auto-numbered table.
• Auto-Numbered Fields. You should only use the AUTOINCREMENT default value for the primary key of a table.
• Timeout Expired Error. If you have a larger Archibus Tasks or Archibus Subtasks table in your destination database, you get a timeout expired message if you use the re-create from scratch option. The reason is that deleting the old records may take some time, and the SQL Server driver times out. To work around this issue, delete the contents of the table in the destination database before running the wizard. If you are running both the wizard and SQL Server on the same computer, memory swapping to disk may increase the query execution time and cause these timeouts. Consider running the wizard phase-by-phase. Also consider using a memory defragmenting tool (such as MemTurbo -- www.MemTurbo.com) to reclaim memory freed by the applications but not made available by the operating system.
• Large Varchar Fields. If you have a table that contains both long varchar fields (e.g. 1000 character) that are not marked as memo fields, and memo fields, you should set these fields to type "memo" before running the wizard so that when it calculates the actual field size in the SQL table, it can remain under the 8000 byte limit for a single record under SQL Server 7.0.
• Re-creating Tables. If you add or remove the autonumbered default (implemented as an IDENTITY constraint described above), or if you change the primary key, the wizard will always re-create the table in question, even if you have specified that the wizard should use the alter table method. The reason is that SQL Server must change the physical layout of the data in response to either change, and therefore the wizard must copy the data to a new table, delete the old table, and rename the new table again.
• Multi-Part Foreign Keys. There is a limitation in the way SQL Server implements multi-part foreign key constraints. To avoid this limitation the wizard implements these constraints as triggers. Specifically, if the foreign key constraint is on a field that is the second or greater part of a multi-part primary key in the validating database, then the wizard creates the constraint as a trigger. An example of such constraints is the city_id field in the Buildings table which is validated against the state_id+city_id in the Cities- table. Another example is the Room Code field of the Equipment table, which is validated against the bl_id+fl_id+rm_id value of the Rooms table.

  The specific limitation is that if you omit the last key field value in a composite foreign key, a SQL Server constraint will reject the UPDATE statement, even if that field allows NULL values. For instance, if in the Buildings table you fill a State Code, but not a City Code, SQL Server will reject the change.

• Memory. If you have made several invocations of the wizard and find yourself running low on memory, restart the SQL Server program, as it often does not release all used memory.

General Notes

• Renaming Reserved Fields. If you have older versions of the Archibus standard schema, the wizard will rename certain fields when copying the data dictionary. It will also copy data from fields with the old name to the corresponding field with the new name.

Old Name	New Name
size	afm_size
image	image_file
use	use1

The names were changed to avoid words that are reserved in some of the servers. ("Size" is a reserved word in Oracle; "use" and "image" are reserved words in Microsoft SQL Server.)

• Renaming Fields. One of the advantages of the alter table option is that you can merge your Archibus schema with an existing schema. One consequence to remember is that if you rename a field, and if you use the alter table option rather than the re-create table option the old field name is not dropped..

• Script Files and Re-creating Tables. If you are using the re-create table option for Schema Change Wizard, you must execute directly to the destination database; you cannot output your changes to a script file. The reason is that the re-create process uses an afm_temp table as described above. When writing to a script file, the afm_temp table is not created; the wizard can't copy the data from the original table to the afm_temp table, and it stops executing..
• Primary Key Errors. If you have two fields designated as the same primary key part, e.g. two fields marked as the first primary key part, the wizard will stop. This is so that existing data in this table and in tables that validate against this one will not be affected.
• Memory. If you are performing a large number of changes, such as building a database from scratch, you should review your available RAM. If you are running both the reference and the destination servers on the same computer, 192 MB is a recommended minimum; if you are transferring a large amount of information between SQL Server and Oracle, you may require even more.
• If you have limitations on the amount of memory available, you can reduce the amount of memory required by performing the copy data dictionary phase, shutting down the reference database server, then performing the alter table phase with a separate invocation of the wizard. You can also run the reference and destination databases on different computers.
• The Schema Change Wizard will use more memory for every table it alters, particularly in the phase where it re-creates foreign keys. The memory is in table definitions, which are cached as they are in the Archibus program. The memory is released when the wizard finishes.
• Database Version Number. If you perform an update on a destination database but you do not see the Hotlist, or if you find that cascading updates and deletes do not work, check the Database Version Number in your Schema Preferences. Make sure this is equal to or greater than the Database Version Number in the reference database. The Archibus program uses this number to determine which features that database supports. For instance, if this is number 104, Archibus will not look for a Hotlist table or for the Use Archibus Function for Cascade preference. Should you wish to set this value manually, you can use an SQL statement like the following:

UPDATE afm_scmpref SET afm_db_version_num = 113

• AutoCommit All Preference. If you are upgrading from an older version of the schema, you may get an error during the copy data dictionary phase, stating that the wizard cannot set the Auto Commit All preference, since this field is only created when you run the alter table structure phase of the wizard. You can set this yourself after the database is created by opening the destination project in Archibus and loading the Archibus Schema Preferences table.
• Tables Missing from Schema. You create a table in the Archibus Tables and Archibus Fields tables and use Schema Change Wizard to create the table in the SQL database. Now you delete the table from the Archibus Tables and Archibus Fields table, but you leave it in the database. Schema Change Wizard will state "Table is in SQL only" message. In this case the Schema Change Wizard job will drop the table.

Data Copy Issues

• Merging Data. For data tables, the wizard will try to merge the data from the export with the data in the destination database. To do so, it first tries to insert a record with the same primary key. If this insert does not succeed, the wizard issues an update. This method saves a query in the case where the record exists. If you echo your SQL statements to the log file, you will see these insert statements failing followed by the update statement succeeding. This is as designed.
• Counting Records. One way to determine if your data all copied is to use the Perform Comparison Only option in the Database Update Wizard to compare the database with the exported .csv file. The comparator log file contains record counts for the .csv and the database.
• Missing Records. You might find that your destination database has fewer records than your source database. In this case, check that you do not have invalid data in those records. For instance, if you have Work Requests that reference Work Order numbers that are not in the database, those Work Requests will not be inserted. As another example, Oracle will not allow this record to be inserted. The reason these records are rejected is because the wizard enforces foreign key constraints before it imports your data. In this way, you can find which records are failing. Otherwise, the foreign key creation would fail without any indication as to why. This order also lets the wizard separate the alter table structure phase from the copy data phase so that the wizard can be used for copying information as well.

• Extra Records. If you have more records in your Navigator tables than you did in the source, you will see extra records in your destination database. If you are copying from the standard schema, you may find extra records in validating tables like States and Cost Categories, since the schema has an initial set of records for these tables, and these records may have been deleted from your destination database.
• Layer and Asset Type Circular References. Notice that the wizard will warn that the afm_layr and afm_atyp table have a circular reference. However, the wizard does have specialized code to ensure that the data for these tables is copied. You can ignore this warning.
• Cascading Errors. If a validating record does not copy correctly, all records that depend on that record will fail to copy. For instance if you have a Building record that does not insert, all dependent records -- such as floors, rooms, equipment, etc. -- will not insert either. The wizard copies validating tables before it copies validated tables. If you have a large number of data copy errors in your log file, start with the top-most copy errors, as these may be the cause of errors further down in the file.

Copying Data with Circular References

The Database Update Wizard ignores the afm_flds.validate_data value when checking for circular references. As a result, the Database Update Wizard may report circular references on fields that are not really validated. If there are data in tables with this condition, then the Database Update Wizard does not import all data.

The solution is as follows:

1. Prior to running the Database Update Wizard to copy data, remove the afm_flds.ref_table value from any fields that have circular references, even if they have afm_flds.validate_data set to '0' (“No”).

   Two typical offending fields are afm_atyp.pop_layer and dp.approving_mgr.

   For these two fields, the circular dependency message goes away if you run the following statements from ISQL before running the Database Update Wizard to copy data.

UPDATE afm_flds SET ref_table = NULL WHERE table_name = 'dp' AND field_name = 'approving_mgr';
UPDATE afm_flds SET ref_table = NULL WHERE table_name = 'afm_atyp' AND field_name = pop_layer';
COMMIT;

2. Next, run the Database Update Wizard to export and import data.
3. Close the Database Update Wizard,and add the ref_table values back in:

UPDATE afm_flds SET ref_table = 'em’ WHERE table_name = 'dp' AND field_name = 'approving_mgr';
UPDATE afm_flds SET ref_table = 'afm_layr' WHERE table_name = 'afm_atyp' AND field_name = pop_layer';
COMMIT;

Note that the following query will enumerate fields with circular references.

SELECT af0.table_name, af0.field_name, af0.ref_table, af1.field_name, af1.ref_table, af2.field_name, af2.ref_table, COUNT(*)
FROM afm_flds af0, afm_flds af1, afm_flds af2, afm_flds af3, afm_flds af4, afm_flds af5
WHERE af1.table_name = af0.ref_table AND af2.table_name = af1.ref_table AND af3.table_name = af2.ref_table
AND af4.table_name = af3.ref_table AND af5.table_name = af4.ref_table AND af5.ref_table = af0.table_name
AND af0.ref_table IS NOT NULL AND af1.ref_table IS NOT NULL AND af2.ref_table IS NOT NULL
AND af3.ref_table IS NOT NULL AND af4.ref_table IS NOT NULL AND af5.ref_table IS NOT NULL
GROUP BY af0.table_name, af0.field_name, af0.ref_table, af1.field_name, af1.ref_table, af2.field_name, af2.ref_table
ORDER BY af0.table_name, af0.field_name;

When interpreting the results of the above query, discard any table-field combinations that include references to previously listed table-fields, as only one link in the circular listing needs to be temporarily removed.

Custom Databases

• Copying Data. If you export data from a custom database and import those data to a standard destination database, some records may not translate if the custom database has changes to the primary key fields. For instance, if your custom database has a fl.fl_id field that is 12 characters wide and you are copying its floor data to the standard database schema which has a fl.fl_id field 4 characters wide, each fl_id will be truncated to 4 digits. Those records whose first four digits are not unique will be duplicate, and will not be inserted.
• Circular References. The standard Archibus schema does not contain any circular references (i.e. table A validates on table B, which in turn validates on table A).

If your custom database contains circular references, you cannot use the Database Update Wizard to import those data. The Transfer In option imports validating tables before the validated tables so as to be able to insert records while validation constraints are in force. In the case of circular tables, there is no correct order to do this.

If you are unsure as to whether your database contains circular references, see the section Copying Data with Circular References above for more detailed information.

If you do have a circular reference in your data, you can remove the circular constraint, import your data, then reapply the constraints. (See the section Copying Data with Circular References above for how to do this.) Alternately, you may wish to use your database server utilities – such as the Oracle Data Manager or the Microsoft Data Transformation Services -- which import data with circular references. These tools import the data in bulk, then reinstate the constraints.