Post-upgrade scripts

You may not need post-upgrade scripts in your release at all. But every decision to create one must always be made during development, at the time when you are conscious of making changes that could affect data upgrades on target machines. It is bad practice to leave these decisions until release time.

Avoid writing a post-upgrade script that does something that the database upgrade script will automatically take care of. For example:

•If you add a new mandatory column and this column has a Default Value, then the "200.upg.rdbms.sql" database upgrade script will automatically apply the default value to existing records. You do not need to write a script for this.

In practice, typical use cases for post-upgrade scripts include new calculation rules and data migration:

New calculation rule

A post-upgrade script may be needed when you have new calculation rules on existing data. For example,

•If existing calculated data is calculated differently in the new application version, this new calculation may need to be applied to existing cases as well. However, it is often desirable in such cases that the new calculation is applied only to new cases, not to existing cases.

•If you add a new mandatory column and this column must be set to the result of some calculation performed by a new constraint, you usually need a special script that applies the new constraint SQL to existing records.

In these 2 use cases, the solution is often simply to copy the constraint SQL that does the calculation to your post-upgrade script.

Data migration

Sometimes, existing data must migrate to a different data structure. One example is that you split an old column PERSON.ADDRESS into PERSON.HOME_ADDRESS and PERSON.BILLING_ADDRESS and it was decided that each existing address must be preserved initially both as home address and as billing address. Another example is that you move a column (BANK_ACCOUNT) from a subtype (EMPLOYEE) to a supertype (PERSON) and it was decided that old "employee bankaccount numbers" must be preserved as "person bank account numbers".

One typical way of organising this kind of data migration is to have a pre-upgrade script that preserves the old data, followed by a post-upgrade script that picks up the preserved data and gives them their new place. This technique is known as stashing.

To create a post-upgrade script, add a new entry in the Current Upgrade Scripts window and :

•Set Processing Order = Post-upgrade.

•Set a Seqno (sequence number). The sequence number determines in what order post-upgrade scripts must be run between them.

•Set the script's Processing Mode.

Next, in the SQL Statements box, write the SQL statement(s) that you want your pre-upgrade script to perform. Again, set Seqnos (sequence numbers) to decide in what order these statements are to be executed.

For a step-by-step instruction, go to " Writing an upgrade script ".

Before you can release a post-upgrade script, you must have it checked. Do this by pressing the Check button. If the check is successful, the script is set to Checked = Yes and you are ready to release it.

The script checker performs three important tasks:

•The SQL statement is checked for syntax correctness and correctness against the data model (table names, column names, relationship names).

•Path variables and path placeholders (if any) are expanded to absolute and machine-dependent paths.

•For each item of added XSL, a call to USXSL.APPLY2FILE is added so that the XSL will be applied when the released script is run on a target machine.

Delivery Manager will attempt to determine whether a script must be re-checked. For example, if you change a SQL Statement, the script will be set to Checked = No. However, because the data model is in a remote database account and not in Delivery Manager, it is not possible to detect automatically all events that could potentially invalidate a script. For this reason, the "Release scripts" action performs a last-minute re-check of all the scripts before they are released to the file system.

You can release post-upgrade scripts only by running a Task that executes the "Release scripts" action. Releasing a script consists of compiling the actual script file with file extension .SQL, and placing it in the \scripts subfolder (the "scripts" section) of the current release folder.

The Release Scripts action is called for all the current upgrade scripts of a given application. You can only release the scripts of an application if there are no remaining unchecked scripts associated with that application.