Skip to content

Database Enhancements

Increased Capacity for Auto Increment Fields

Auto increment fields are commonly used through out the schema. Examples include:

  • The id column on the Plan table, and the internal_id column on the Input_Reference table in the scheduling schema.
  • The id columns on the ARC_Audit and ARC_Snapshot tables in the archive schema, along with many 'key' columns in the reporting tables.
  • The id column on the RAM_Update table in the resource planning schema.

Previously these all used Int32 data types, but this has been updated in almost all cases to instead use Int64. This increases the maximum allowed value in these columns from just over 2 billion to almost 10^19, removing any prospect of this maximum value being exceeded.

In addition, any columns that reference these auto increment columns have also been updated to the new data type. For example, this would include the plan_id column on all output tables.

The only current exceptions to this are the External_Database.id column in the system schema, and some archive reporting tables which are used to store lookup data (e.g. ARC_Activity_Class).

Warning

Please note that these changes impact the majority of tables in the database, and so the upgrade process is likely to take significantly longer than might usually be expected. This is especially the case on larger systems. Please allow appropriate time for the upgrade to proceed.

It is also highly recommended that a database backup is taken before commencing with the upgrade.

Warning

While internally the PSO system will now handle any of the updated columns having values over the previous limit, please note that integration changes may also be required to handle this situation if it were to occur. This could apply for example when reading the Plan.id while processing scheduling output data.

Also note that some API methods have been updated to accept or return Int64 data types as a result of the update. However, this should not result in any immediate required changes to integrations.

Warning

Due to an issue in previous versions data inserted via bulk insert could have been inserted violating foreign key constraints. This issue only applies to SQL Server or SQL Azure and has been fixed in this release. However, the existing data in the database may cause issues with the database upgrade process causing the upgrade to fail. The installer will check all the database constraints before upgrading SQL Server or SQL Azure databases and stop the installation from going ahead until the data issues have manually been fixed.

Constraint issues can be found by running the following SQL query:
'DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS'
Issues identified through this method can be addressed through consultation with IFS if needed.

Warning

Any custom indexes that have been added to the database can cause issues with the upgrade process. The installer will check for any custom indexes that could cause problems and stop the installation from going ahead until the custom indexes have been removed.

Removal of Foreign Keys from Archive Fact Tables

Most foreign key constraints have been removed from the fact tables in the archive database. This has been done in order to improve performance when deleting reporting data from the database. Foreign keys to the ARC_Dataset and ARC_Snapshot_Detail tables will remain, but all other foreign keys have been removed. This should not result in any loss of functionality, and there are no changes to the the archive tables and columns resulting from this change.

Minor Changes

  • The obsolete tables used to support a previous version of the WISE have now been removed from the resource planning schema.
  • Added load_id to Input_Reference to allow handling changes that do not apply to the current LOAD.