Procedure

4.4.2. Procedure

Enable database optimizations (optional) #

When upgrading it is possible to automatically apply database optimizations to a database through a patch. This can be achieved by turning on optimized mode before applying database patches. Please refer to the comments in conf/sics-database-sources.xml for a description on how to enable the optimized mode for a specific datasource.

Optimizing an existing database through database patch requires time and space and should be considered as an option only when dealing with small databases. In other cases, a script for optimizing the database can be generated at any time in SICS System Administration Console using the Database Setup -> Manage Tables -> Optimization Script function.

The database optimization patch will be disabled in the case when database patches are executedwith optimized mode turned off. For running it at a later point in time, it will be necessary to enable the optimized mode and to explicitly activate the database patch (re_2143) in SICS System Administration Console. Then the patch can be run.

Optimized mode is turned off by default and must be enabled explicitly.

Please refer to the installation document for details regarding the merits of database optimization.

Enable database Unicode mode (optional) #

This setting only applies to SQL Server

When upgrading it is possible to automatically apply database Unicode mode to a database through a patch. This can be achieved by turning on Unicode mode before applying database patches. Please refer to the comments in conf/sics-database-sources.xml for a description on how to enable the Unicode mode for a specific datasource.

Applying Unicode mode to an existing database through database patch requires time and space and should be considered as an option only when dealing with small databases. In other cases, a script for applying Unicode mode to the database can be generated at any time in SICS System Administration Console using the Database Setup -> Manage Tables -> Unicode Mode Script function.

The database Unicode patch will be disabled in the case when database patches are executedwith Unicode mode turned off. For running it at a later point in time, it will be necessary to enable the Unicode mode and to explicitly activate the database patch (re_2240) in SICS System Administration Console. Then the patch can be run.

Unicode mode is turned off by default and must be enabled explicitly.

Please refer to the installation document for details regarding the merits of database Unicode mode.

Turn on event logging #

Open the System Administration Folder and double-click Event Log. Select the menu item Logging Options -> Event Log Setup and check that event logging is enabled. If not press the edit button and enable event logging.

Review database patch parameters #

Open the Database Updating folder and open the “Update Database Definition - Tool” window.

Every time you use the “Update Database Definition” window, you should review the parameters. Your previous choices may or may not be kept between uses of this window.

The two tabs of the Update Database Definition - Tool window:

1_Update_Database_Definition_Tool_patches_window_NEW.png

2_Update_Database_Definition_Tool_parameters_window_NEW.png

Set Additional DDL Parameters #

The ‘Parameters’ tab in this window allows you to specify additional DDL parameters. For example, you can specify freespace information for indexes. The parameter string you specify is appended to the appropriate DDL statement. You may leave the parameter field(s) empty.

For Oracle, one or more of these parameters may be relevant for creation of indexes:

TABLESPACE <tablespace> PCTFREE <pctfree> STORAGE <storage-clause>

Set Commit String #

When logging to file, explicit database commits will be inserted throughout the file. The default commit string is ‘commit;’. You can set the commit string according to your preferences.

Log Patches to File #

If you want to log to file instead of updating the database directly check the option “Log only”. You will be prompted for a filename where the SQL will be written. Database update operations are grouped in different passes. When you log ‘Initial’ patches to file, all operations in pass 0 will be logged. When you log ‘Main’ patches to file all operations in pass 1 will be logged. All pass 0 patches must be executed and reference data updated before the pass 1 can be logged to file.

The generated file can only be used on the schema it was created for. The contents of the file depend on the current state of the database. The database must not be modified from the time the file is generated until the content of the file is executed.

Drop Oracle Extended Statistics #

If using an Oracle database with extended statistics, it may be necessary to drop extended statistics on SICS tables. The reason is, that Oracle implicitly creates a virtual column on all fields that participate in extended statistics. If such a field is modified (length or otherwise) by a SICS database update operation, the update will fail. The work around solution is to drop all the extended statistics, do the upgrade and add the extended statistics again.

Disable SSP Patches Producing Errors #

When patches have been delivered in an SSP stream and the database was created on or after their inclusion using that stream, then when upgrading to the main stream version where they are introduced (or later) then the patch will still be enabled. This may cause no problem with some patches that can be re-run but in cases where e.g. a create table where the table already exists then the patch will stop with an error. In this case the patch should be disabled.

e.g. Patch re_2387 introduced in SICS 4.8.6 SSP1 with main stream delivery in SICS 4.9.2 For DB created before SICS 4.8.6 SSP1 no action required (patch will run and record as run in log). For DB created on or after SICS 4.9.2 no action required (patch not required and will not run). For DB created with SICS 4.8.6 SSP1 or later but before SICS 4.9.2 then the patch will be enabled. In the case of an object already exists error then the patch should be disabled as not required.

Runpass 0 initial update operations #

Open the Database Updating folder and open the “Update Database Definition” window.

Press “Apply Initial”. This will execute the first set of update operations, i.e. those update operations that must be applied before updating Reference Data.

Alternatively, log the patches to file as described earlier. Review the file and customize to local standards if needed. Then execute the file.

Update reference data #

Open the Database Updating folder and open the “Update Database Definition” window.

Press the “Update Ref” button to execute the update of Reference Data.

Runpass 1 main update operations #

Open the Database Updating Folder and double click on the “Update Database Definition” icon.

Press Apply Main. This will execute the pass 1 main patches.

Alternatively, log the patches to file as described earlier. Review the file and customize to local standards if needed. Then execute the file.

Drop/Create BO Reporting Views (optional) #

This only applies to PostgreSQL

During DB upgrade in PostgreSQL while updating patches if it throws any error then need to drop reporting views first before executing the “Update Database Definition” window.

After updating the patches needs to create the reporting views again.

Add Constraints #

After applying patches, constraints must be added to new and changed database tables. There are two alternative procedures for adding new constraints, with and without dropping existing constrains first. These two alternatives are described as procedure A and B below. Procedure A is the recommended and easiest way to ensure all constraints are correctly added. However, adding all constraints to all tables is a very time consuming process on larger databases. Procedure B avoids this problem by only adding the new constraints, but there is a risk that some constraints may be missing after the upgrade. These missing constraints must be identified and manually added.

Procedure A - Adding constraints with dropping

Open the Database Setup Folder and select Manage Tables.

Select operations “Drop Constraints” and “Add constraints” and press Start. Note: Do not select option “Silently Log Errors”.

Procedure B - Adding constraints without dropping

Open the Database Setup Folder and select Manage Tables.

Select operation “Add constraints” and option “Silently Log Errors” and press Start. Use the schema browser described in section 4.1to identify missing constraints. Any missing constraints must be manually added to the schema.

Add Indexes #

After adding constraints new defined indexes and foreign key indexes must added. Open the Database Setup Folder and select Manage Tables. Select the following operations and options and press start:

  • Add Defined Indexes
  • Add Foreign Key Indexes
  • Silently Log Errors

This will add all new indexes to the database.

NOTE: It is not necessary to select ‘Drop all Indexes’ before adding indexes. However, if ‘Drop all Indexes’ is selected, please note that indexes with these prefixes will NOT be dropped:

  • ‘BO_’ used by Business Objects reporting solution
  • ‘CLI_’ intended for client’s own use.

Add Views #

After upgrading you have to drop and recreate the views. Open the Database Setup Folder and select Manage Tables. Select the operations “Drop all User Views” and “Build Views” and press Start.

NOTE: ‘Drop all User Views’ will NOT drop views prefixed by ‘V_’. This prefix is reserved for the client’s own use.

Grant access rights #

In a multi-user setting all SICS tables and views must be made visible to all users. The SICS group or role (depending on database) must be given access rights to all tables and views. Please refer to the installation document for details.

Analyze tables and compute statistics #

Optional - The database should be tuned after the patches have been run. Statistics for all tables should be updated.

Update table column comments #

Optional - If you want to update the table and column comments:

Some of the patches have added new tables and columns to the database schema. In order to update/add comments for these elements, use the Manage Tables GUI, select “Create Table Comments” and press Start.

Update system data #

Combined database or pure P&C, Life, Cede database #

Open the Database Updating Folder and execute the functions:

  • Update Messages
  • Update Security
  • Update Tag Comments (Update Reference Data was performed already, see Update reference data).

Pure P&C and Cede database #

In a pure P&C system with EDI module in use run the function:

  • Update EDI Base Data

In pure P&C systems with the eMessagingModule in use, the following functions must also be run:

  • Update eMessaging Rules
  • Update eMessaging Factory Rule Sets
    • Note: Existing rule sets can be updated here with rule changes but any changes required can be made later in eMessaging System Parameters.
  • Rebuild eMessaging Rule Set Relationships

Combined database #

If you use a combined P&C and Life database, then you have already run the functions Update Reference Data, Update Messages and Update Security from the Life System Administration Utility. You must now log on to the P&C System Administration Utility and execute:

  • Update Reference Data
  • Update Security

It is not necessary to run Update Messages.

If P&C EDI module is in use:

  • Update EDI Base Data

In systems with the eMessagingModule in use for P&C, the following functions must also be run:

  • Update eMessaging Rules
  • Update eMessaging Factory Rule Sets
    • Note: Existing rule sets can be updated here with rule changes but any changes required can be made later in eMessaging System Parameters.
  • Rebuild eMessaging Rule Set Relationships

Please see list later in document about needed indexes for combined databases to be checked.

Update database version #

When the update database definition function completes successfully it automatically updates the version information stored in the environment. After this is done you will not be able to log into the environment using older Workstations any longer.

There is a menu item in the Update Database Definition GUI called Update Database Version. This will set the database version. In a normal procedure this function should not be used. The Update Database Version function should only be run after consultation with SICS support. If you need to run this function, it should be done from a Life System Administration Utility if you have a Life database or a combined P&C and Life database. Use the P&C System Administration Utility if you have a pure P&C database.