Upgrade Your Database

Oracle - UTF-8 Schema Required

As of ServiceScheduling 9.2.1.0, when using an Oracle database for ServiceScheduling, the database must use a UTF-8 character set.

When upgrading ServiceScheduling, if the existing database is not using a UTF-8 character set, the following actions must be performed:

On the existing, non-UTF-8 database schema, run just the dbup.008000.00.sql script from the new ServiceScheduling release. This dbinstall script updates the column lengths from BYTE to CHAR.
- This needs to be done before moving schema (and data) from the non-UTF-8 database into a UTF-8 database, in order to ensure that characters beyond the basic ASCII range (character codes over 127) will be converted correctly.
Create a new Oracle database with NLS_CHARACTERSET set to AL32UTF8 (using the CREATE DATABASE statement).
Your existing non-UTF-8 database schema then needs to be imported into the new, UTF-8 database.
Once completed, continue to follow the standard database upgrade instructions below, using the new, UTF-8 database.

NOTES:

The conversion to a UTF-8 database only needs to be performed once. However, even if you are running a version of ServiceScheduling later than 9.2.1.0, if you have not yet converted your database to a UTF-8 character set, you should do so with your next upgrade of ServiceScheduling.

Constraint & Index Re-Creation

When upgrading to ServiceScheduling 10 (or later) from a ServiceScheduling database prior to ServiceScheduling 10, the dbinstall script will convert database column types from char/varchar to nchar/nvarchar will be executed. To do this, it will drop and then re-create all constraints and indexes on the affected columns.

The dbinstall script dynamically creates the SQL using data in the system tables. However, the indexes and constraints are re-added using the database defaults; the script does not support non-default values. Therefore, if any changes have been made to constraints or indexes in the ServiceScheduling database, the DBA will need to revisit them after the upgrade dbinstall script has been run.

For instance, the index fill factor will not be explicitly included in the index create statement, so the database default value (usually 0 = 100% full) will be used.

Note that the dbinstall script updates all tables starting with SPxxx_ (where x is a number) or starting with SPAUDIT. If additional tables have been created in the ServiceScheduling database that start with these prefixes, the dbinstall will attempt to update them and may fail if they cannot be updated.

The database user running dbinstall must have select privileges on the sys and INFORMATION_SCHEMA schemas.

It is a pre-requisite for SQLServer that the compatibility on the SQLServer database be set to at least 90.

Execute the following (in the order given)...

Execute the following command from the %SPHOME%/database directory:
```
[perl] dbinstall -d <databaseConnectionString>
```
This script will perform any relevant updates to the schema for this release. The use of the command ‘perl’ (which runs the Perl interpreter to run the Perl script dbinstall) may or may not be required, depending on the operating system configuration.
Check the output log file to see that dbinstall has run correctly and ensure there are no errors.
SQLServer Only - possible warnings, no cause for concern

DBD::ODBC::st fetchrow_array warning: no select statement currently executing at Script.pm line 1640, <SCRIPT> line 40.

What is the <databaseConnectionString>?

All ServiceOptimizer processes require the database connection details to be supplied on the command line, in one of the following forms:

   [ <dbUser>/<dbPassword>[@<dbHost>]                                 ] |

   [ORACLE:<dbHost>:<dbUser>:<dbPassword>                             ] |

   [MSSQL:<dbServer>[\<dbInstance>]:<dbName>:[<dbUser>]:[<dbPassword>]]

The first and second variants are Oracle only; the third is MS SQL Server only.
If there is no password configured in the SP database then the dbPassword is also used to authenticate the database password supplied by an SP client (such as ServiceManager or ServiceGANTT).
Default Oracle dbHost is environment specific (specified for example by LOCAL or TWO_TASK environment variables).
The MS SQL Server dbInstance is only required if you are connecting to a named instance.
If MS SQL Server dbUser is not specified, the connection to MS SQL Server will be made as the trusted user (i.e. the operating system user). dbPassword is then not used to authenticate the ServiceScheduling connection to the database.

Where do I find the logfile and what does it tell me?

The log file is named dbinstall.<datetime> where <datetime> has the format YYYYMMDDHHMISS.

The logfile can be found in:

SQLServer	Oracle
`%SPHOME%/<instancename>/log`	`$SPHOME/<instancename>/log`

Where the directory referenced by <instancename> will be set to the name of the operating system user under which ServiceOptimizer is running.

To see a list of other parameter options for dbinstall type the following command:

[perl] dbinstall -h