Change the database user schema

This topic describes the problems that require manual repair (cannot be fixed automatically by the repair process), and recommends solutions for these problems. If you encounter any of the problems mentioned below, consult with your database administrator or contact OpenText Support for further guidelines to resolve these problems before upgrading.

The stability of the new database upgrade component depends on the database user schema validity. We recommend that you not use the Exception file to change the database user schema.

In this topic:

Missing database objects

Missing database objects can be symptoms of a bigger problem.

Problem: Missing database objects (for example, tables and indexes) can yield unexpected and unwanted behavior.

Solution: Although you can use the repair process to add these objects, we recommend that you contact OpenText Support to make sure that the missing objects are not just symptoms of a bigger problem.

Missing list warning

User-defined fields of List type must be associated with lists.

Problem: If a list is missing for a user-defined field, the verification process generates a Missing List warning.

Solution: Contact OpenText Support for instructions on changing the data type of the user-defined field from List to String in the SYSTEM_FIELD table.

Sequences warning

An internal mechanism manages IDs and other system numerators. The table SEQUENCES holds the name of the table or other entity whose numeration is being tracked as well as its highest current value.

Problem: If one of the records is missing in this table, or if one of the values is incorrect, the verification process generates a Sequences warning.

Solution: The repair process fixes the problem automatically.

Changed database objects

Any of the following cases is defined as a Changed Database Object:

• Data type of a column was changed

• Length of a column was changed

• Nullability of a column was changed

• Column is defined as identity although it should not be defined as such, or vice versa

Problem: A changed column data type can result in incorrect behavior on the server side.

Solution: To avoid this behavior, make sure that you have resolved all data type and length concerns before beginning the upgrade.

For every changed database object that is found, do the following:

1. Create a new column with the required attributes as originally defined by the OpenText Application Quality Management server.

2. Move the data from the old column to the new one.

   If you cannot move the data (for example, move strings to numeric columns, or move large data to smaller fields), contact OpenText Support.

3. Remove the old column.

4. Rename the new column to the original column name.

Extra database objects

OpenText Application Quality Management has various customization options. One option is to add user-defined fields (UDFs). You can add a UDF by using either the project customization user interface or through OTA (Open Test Architecture).

Problem: Any other addition to the database user schema (for example, defining extra objects on top of OpenText Application Quality Management schema) can result in a failure, such as the following:

• Name Conflict. If the later version happens to include a name that you added for a proprietary database object (for example, a table, view, or column), the two names will be in conflict.

• Copy and Synchronize Failure. If the database user schema contains extra or missing database objects, some OpenText Application Quality Management mechanisms for copying and synchronizing might fail.

• Extra Triggers. If the database contains extra triggers, some update operations might fail.

Solution:

For each extra database object that is found, perform the corresponding solution:

• Move extra columns to newly created tables.

  To make sure a new table has a one-to-one relationship with the original table, define the primary key of the new column in the new table with the value of the primary key of the original column in the original table.

• Move extra tables to a different database user schema.

  These extra tables include those tables created above. You might need to amend the proprietary application data access of these tables. You can still access these tables from within the OpenText Application Quality Management database connection by specifying the full name.

  • Oracle

    <schema name>.<table name>

  • SQL Server

    <database name>.td.<table name>

  To be able to see these tables, you must grant the necessary permissions for the database user schema.

• Move extra views to a different database user schema.

  Like extra tables, these views can be moved to a different database user schema. In addition, you must grant reading permissions to the newly created database user schema on the database user schema objects.

• Remove referential integrity between customer database objects and OpenText Application Quality Management database objects.

  This removal includes no data loss.

• Remove extra triggers before the upgrade, and, only if truly necessary, restore them after the upgrade.

  No data loss is involved. The upgrade process includes data upgraders that perform some data manipulations (for example, removing duplicate values, fixing tree structures, and so on).

  Your triggers will not be invoked on these update events.

  As a result, you need to do the following:

  1. Ask OpenText Support for information about the data upgrader activity.

  2. Review the information about the data upgrader activity.

  3. Decide on which proprietary updates you need to perform.

• Remove extra indexes.

  You can log all indexes before the upgrade, and (only if necessary) restore them after the upgrade. No data loss is involved.

• Oracle Database only: Move extra sequences to a newly created database user schema.

  To access the extra sequences from the database user schema, you must grant OpenText Application Quality Management the required permissions. When moving these sequences, set them to start with the number they reached at the time of the move.