Currently it is technically possible (though not recommended) to share one persistent state database/database schema between different independent services. With future Runtime releases, this will not be allowed anymore.

This means that you need to rework services that share the same persistent state database/database schema. This article describes what you need to do.

Roadmap

The latest version of PAS is PAS 24.1. The upcoming PAS version 24.2  will still come with a Runtime that has the legacy persistent state implementation, but we will provide a Runtime beta version with the changed behavior.

PAS 24.3 then will come with this changed behavior included as per standard.

What this is about

You have set up a persistent state database schema of which the database tables contain data of multiple (2 or more) independent services.

As this is not allowed anymore, you need to

1. create dedicated database schemes for every single service
2. move (first copy, then delete) the data from the shared database to the dedicated database

You can keep one of the services in the original database.

To do this, follow the procedure described below.

Prerequisites

To follow the procedure described below, you need to have at least Runtime 2019.9 installed. If your current Runtime is older than 2019.9, please update your installation to 2019.9 (at least) before performing the migration steps.

Step 1: Stop the affected services

Stop all services that are affected by this rework.

Step 2: Create a database backup

Before starting with this migration, we strongly recommend to do a database backup. This way you can rollback your changes if anything goes wrong during the migration.

Step 3: Create a new database schema for every service

For each service you need to create a separate database schema. This is done by database commands that depend on the database system you are using. It may be e.g. something like

• CREATE SCHEMA <name of new schema> (Oracle)
  or
• CREATE DATABASE <name of new database> (SQL Server)

Next, you need to create all necessary persistent state tables to the database schema. To help you with that, the Runtime comes with scripts for every supported database (MySQL/MariaDB, Oracle and SQL Server).

1. Go to your Runtime directory, to <path to Runtime directory>/<your OS>/resource/sql/<your DB system>/pstate.

   If you are using an older Runtime (before Runtime 2023.1), these scripts reside in <path to Runtime directory>/<your OS>.

2. Find the script CreateSchema.sql for your database.

   The MySQL script also fits for MariaDB.

3. Run this script for each newly created schema to create persistent state tables.

Step 4: Copy the data

Copy the data of each service to the corresponding database schema. To help you with that task, we have prepared some scripts, or you can execute the steps manually.

The owner concept

To perform the next steps, you need to be familiar with the owner concept of the PAS persistent state implementation.

• Services are owners of persistent state objects.
• You need to know the owner to distinguish the persistent state data.
• The owner name is the service name as per default.

• The owner name can be changed in the service configuration/settings:

  Configuration Section	Key	Name	Value
  Persistent State	Persistent State: Owner:	AL_PS_OWNER	Service_2

  For more information on how to access the service configuration, refer to Adapting the Configuration of Containerized xUML Services for the PAS Administration, and to Adapting Integration Service Configuration for the Integration/Bridge.

This is why owner names are coded into owner IDs in a dedicated table OWNERS, e.g.

Database items like persistent state object data is coded to the owner ID, some other items are coded to the owner name. Keep this in mind when doing the migration manually - the migration scripts (see below) already cover that.

Use the scripts

The following scripts are available:

Split the data manually

For each service, copy the related data from the old to the new database schema. To identify the data, refer to the owner name or owner ID.

UPDATE <new schema>.VERSION
SET MAX_OWNER_ID =
(SELECT MAX_OWNER_ID FROM <old schema>.VERSION)

INSERT INTO <new schema>.OWNERS
SELECT * FROM <old schema>.OWNERS
WHERE OWNER = <your owner name>

INSERT INTO <new schema>.OBJECT_DATA
SELECT * FROM <old schema>.OBJECT_DATA
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

INSERT INTO <new schema>.EXTERNAL_ATTRIBUTES
SELECT * FROM <old schema>.EXTERNAL_ATTRIBUTES
WHERE OBJECT_ID IN
(SELECT OBJECT_ID FROM <new schema>.OBJECT_DATA)

INSERT INTO <new schema>.TOKENS
SELECT * FROM <old schema>.TOKENS
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

INSERT INTO <new schema>.EVENTS
SELECT * FROM <old schema>.EVENTS
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

INSERT INTO <new schema>.OBJECT_IDENTIFIERS
SELECT * FROM <old schema>.OBJECT_IDENTIFIERS
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

INSERT INTO <new schema>.HISTORY_STATES
SELECT * FROM <old schema>.HISTORY_STATES
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

INSERT INTO <new schema>.CONVERSATION_LOCKS
SELECT * FROM <old schema>.CONVERSATION_LOCKS
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

INSERT INTO <new schema>.OBJECT_KEYS
SELECT * FROM <old schema>.OBJECT_KEYS
WHERE OWNER = <your owner name>

INSERT INTO <new schema>.CONVERSATION_EVENTS
SELECT * FROM <old schema>.CONVERSATION_EVENTS
WHERE OWNER = <your owner name>

DELETE FROM <old schema>.OBJECT_KEYS
WHERE OWNER = <your owner name>

DELETE FROM <old schema>.CONVERSATION_EVENTS
WHERE OWNER = <your owner name>

DELETE FROM <old schema>.EXTERNAL_ATTRIBUTES
WHERE OBJECT_ID IN
(SELECT OBJECT_ID FROM <new schema>.OBJECT_DATA)

DELETE FROM <old schema>.CONVERSATION_LOCKS
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

DELETE FROM <old schema>.HISTORY_STATES
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

DELETE FROM %OLD_schema%.OBJECT_IDENTIFIERS
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

DELETE FROM <old schema>.EVENTS
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

DELETE FROM <old schema>.TOKENS
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

DELETE FROM <old schema>.OBJECT_DATA
WHERE OWNER IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

DELETE FROM <old schema>.OWNERS
WHERE OWNER_ID IN (SELECT OWNER_ID FROM <new schema>.OWNERS)

Step 5: Change the service configuration

After the data migration, you need to update the service configuration (settings) of the migrated services. You need to update the configured database to the new one the data has been migrated to.

You can do this in the PAS Administration or on the Integration Component (Bridge), or you can change the definitions in your MagicDraw model and redeploy it.

Step 6: Restart the services

Now you can restart the migrated services.