Jiwa Upgrading

[SBarnes]

Hi Mike,

Quite often when we are upgrading a Jiwa database particularly for a client that is coming out of 6.5.13 due to foreign keys, data can cause the upgrade process to fall over, I was wondering would it be possible to add into the upgrade process a before scripts table for want of a better term, that could contain scripts put there by the JSP but also the number of upgrade script before which this script would need to be run?

The reason for the before script number is in case of the situation where Jiwa's upgrade process has changed the the database structure at some point in the process that needs to happen first.

My reason for the suggestion is on some very big databases we have to upgrade fairly soon the process is going to take days to run and the needing of manual intervention means someone has to watch the process.

What do you think?

[Mike.Sheen]

Hi Stuart,

You can edit the JiwaDatabaseScripts.mdb in Access to insert any scripts you want.

We execute the scripts in the qryUpgrade query in order of DatabaseMajor, DatabaseMinor, DatabaseRelease, UpgradeScriptOrder - so just add your scripts to that Access query.

Mike

[SBarnes]

Hi Mike,

is there gaps in the number sequence to be able to insert through? i.e. gaps in upgrade script order as upgrade script order is a number, I just checked?

[Mike.Sheen]

Hi Mike,

is there gaps in the number sequence to be able to insert through? i.e. gaps in upgrade script order as upgrade script order is a number, I just checked?

We have a convention of incrementing the UpgradeScriptOrder by 10 for just such contingencies! So it should be easy enough to insert 9 or fewer before a script. If you need more, then you'll need to renumber the UpgradeScriptOrder for the all scripts afterwards for that version.

For instance, we currently have scripts from 7.1.7 onwards looking like this:

PK, DatabaseMajor, DatabaseMinor, DatabaseRelease, UpgradeScriptOrder
34441, 7, 1, 7, 10
34442, 7, 1, 7, 20
34443, 7, 1, 7, 30
34448, 7, 1, 8, 10
34449, 7, 1, 8, 20

If you wanted to insert 2 scripts before the 2nd script 7, 1, 7, 20 then you'd simply add:
XXXXX, 7, 1, 7, 18
XXXXX, 7, 1, 7, 19

I put XXXXX for the PK because we don't care about that value at all - it's automatically generated by access and we don't use it in determining the order

If you needed to add more than will fit with the gap between 10 and 20, you'll need to renumber the 7, 1, 7 scripts after UpgradeScriptOrder 20 also - so the result would look like:
34441, 7, 1, 7, 10
<your scripts from here>
XXXXX, 7, 1, 7, 11
...
XXXXX, 7, 1, 7, 55
</your scripts from here>
<below script was 34442, 7, 1, 7, 20, we adjust UpgradeScriptOrder to be > our last inserted script UpgradeScriptOrder and increment each subsequent UpgradeScriptOrder for this version>
34442, 7, 1, 7, 60
34443, 7, 1, 7, 70
<the below remain unchanged as they are for a later version and the UpgradeScriptOrder restarts the sequence>
34448, 7, 1, 8, 10
34449, 7, 1, 8, 20

[SBarnes]

Hi Mike,

We have all of this working but we are getting a timeout on one of the SQL scripts that then has to be run in SQL explorer any options you can think of to get around this issue?

[Mike.Sheen]

Hi Mike,

We have all of this working but we are getting a timeout on one of the SQL scripts that then has to be run in SQL explorer any options you can think of to get around this issue?

Hi Stuart,

Under such circumstances I would do as you have done, and run the script in SQL Management Studio and then flag the script as processed (SET Status = 1) instead of in error (Status of 2) in the SY_UpgradeScripts table, then resume the upgrade.

As we abort upgrade on the first error and flag it as in error with a Status of 2, you can get away with the following:

Code: Select all

UPDATE SY_UpgradeScripts SET Status = 1 WHERE Status = 2

So you don't need to know the exact PK value of the script, and thus if you encounter multiple failures during upgrade, you can just repeatedly run the above once you've manually run the erroneous script separately to completion.

And whilst you've probably already arrived at way of finding which script failed, my usual technique is simply:

Code: Select all

SELECT * FROM SY_UpgradeScripts WHERE Status = 2

And from that you can copy paste the Script field into Management Studio and also examine the error message.

Mike