Subject: Upgrade Notes for Deployment Pack up to Version 12.1.x

ABOUT: This upgrade procedure applies to Deployment Pack versions starting from 11.x and supports upgrades to versions up to 12.1.x.

Minimum Requirements:

-Deployment Pack: v11.2.x or 11.3.x

-Service Update: v11.2.x or 11.3.x

-XRay version: v11.2.x or 11.3.x

These steps below assume local installation of all related services.

To upgrade an existing meshIQ Enterprise Manager, see Upgrading an Existing meshIQ Enterprise Manager (Admin Package / Console).

For the manual upgrade, see meshIQ Platform 11.2.X or 11.3.X to 12.1.X Manual Upgrade

Upgrade Steps:

When the Database is PostgreSQL

No manual changes are required. Proceed directly to the Run upgrade.sh section to complete the upgrade.

When the Database is MySQL

Before running the upgrade, migrate the existing MySQL schemas to the new meshIQ schema names.

Create a new schema named meshiq_wgs:

mysql -u root -p -e "CREATE DATABASE meshiq_wgs;"

Copy the data, tables, routines, triggers, and events from nastel_apwmq to meshiq_wgs:

mysqldump -u root -p"YourPassword" --routines --triggers --events nastel_apwmq | mysql -u root -pNastel\@1234 meshiq_wgs

Create a new schema named meshiq_manage:

mysql -u root -p -e "CREATE DATABASE meshiq_manage;"

Copy the data, tables, routines, triggers, and events from nastel_permv3 to meshiq_manage:

mysqldump -u root -p"YourPassword" --routines --triggers --events nastel_permv3 | mysql -u root -pNastel\@1234 meshiq_manage

Remove the Old Schemas

Do not delete the old schemas until you have verified that the migration is complete.

After verifying the migration, remove the old schemas:
```
DROP DATABASE nastel_apwmq;
DROP DATABASE nastel_permv3;
```

After completing the MySQL schema migration, follow the steps in the Run upgrade.sh section to complete the upgrade.

When the Database is Microsoft SQL Server

Rename the nastel_apwmq Database

Run the following command:

sqlcmd -S localhost\SQLEXPRESS -U sa -P "YourPassword" -Q "ALTER DATABASE [nastel_apwmq] MODIFY NAME = [meshiq_wgs]"

This renames the nastel_apwmq database to meshiq_wgs.

Rename the nastel_permv3 Database

Run the following command:
```
sqlcmd -S localhost\SQLEXPRESS -U sa -P "YourPassword" -Q "ALTER DATABASE [nastel_permv3] MODIFY NAME = [meshiq_manage]"
```
This renames the nastel_permv3 database to meshiq_manage.

Replace YourPassword with the password for the SQL Server sa account. Make sure the SQLEXPRESS instance is running before executing these commands.

After completing the Microsoft SQL database renaming, follow the steps in the Run upgrade.sh section to complete the upgrade.

When the Database is Oracle

Use the following steps to migrate the existing Oracle source schema to the new target schema.

Export the source schema using Oracle Data Pump:

expdp SOURCE_SCHEMA/<password>@//dbs1:1521/ORCLCDB \
  directory=DATA_PUMP_DIR 
  dumpfile=source_exp.dmp 
  logfile=source_exp.log 
  reuse_dumpfiles=y

Create the Target Schema

If you are creating the user from the CDB root, enable the required Oracle script setting first:

ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;

Create the target schema:

CREATE USER TARGET_SCHEMA 
IDENTIFIED BY "<password>" 
DEFAULT TABLESPACE USERS 
TEMPORARY TABLESPACE TEMP;
GRANT ALL PRIVILEGES TO TARGET_SCHEMA;

Import the Schema with REMAP_SCHEMA

Import the exported schema into the target schema using Oracle Data Pump:
```
impdp system/<password>@//dbs1:1521/ORCLCDB \
  directory=DATA_PUMP_DIR 
  dumpfile=source_exp.dmp 
  logfile=target_imp.log \  
  remap_schema=SOURCE_SCHEMA:TARGET_SCHEMA
```
The REMAP_SCHEMA parameter imports objects from the source schema into the target schema.

Verify the Target Schema

After the import completes, verify that the objects were created successfully.

Check the number of tables:

SELECT owner, COUNT(*) FROM dba_tables 
WHERE owner = 'TARGET_SCHEMA' 
GROUP BY owner;

Check for invalid objects:

SELECT owner, object_name 
FROM dba_objects 
WHERE owner = 'TARGET_SCHEMA'
 AND status = 'INVALID';

Remove the Source Schema

After the application has been switched to the target schema and the migration has been verified as successful, remove the old schema:
```
ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;
DROP USER SOURCE_SCHEMA CASCADE;
```
DROP USER ... CASCADE permanently deletes the source schema and its objects. Perform this step only after the migration has been verified and the application is successfully using the target schema.

After completing the Oracle schema migration, follow the steps in the Run upgrade.sh section to complete the upgrade.

When the Database is DB2

Set the Database Variables

Define the database and schema names used by the commands in the following steps:
```
DB=TESTDB; OLD=NASTEL_APWMQ; NEW=MESHIQ_WGS; OWNER=NASTEL; BK=/database/backup
```
Back Up the Database and Verify the Backup

Force all applications to disconnect, deactivate the database, create a compressed backup, and verify the backup.

The backup verification must end with successful.
```
db2 force applications all; db2 deactivate db $DB; db2 backup db $DB to $BK compressdb2ckbkp $(ls -t $BK/$DB.0.* | head -1)
```
Connect to the database and create the SYSTOOLSPACE tablespace required for the schema copy.

If the tablespace already exists, you can continue to the next step.
```
db2 connect to $DBdb2 "CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4"
```
Save the Row Count for Each Table in the Old Schema

Generate and execute SQL statements to capture the row count for every table in the old schema. The results are saved in /tmp/base.txt for comparison after the schema is copied.
```
db2 -x "SELECT 'SELECT ''' || TABNAME || ''', COUNT(*) FROM $OLD.\"' || TABNAME || '\";' FROM SYSCAT.TABLES WHERE TABSCHEMA='$OLD' AND TYPE='T'" > /tmp/b.sql; 

db2 -x -tf /tmp/b.sql > /tmp/base.txt
```

Copy the contents of the Old Schema to the New Schema using ADMIN_COPY_SCHEMA.

The command must return:

Return Status = 0

db2 "CALL SYSPROC.ADMIN_COPY_SCHEMA('$OLD','$NEW','COPY','$OWNER',NULL,NULL,'ERRSCHEMA','ERR_$NEW')"

Reset Identity Columns in the New Schema to MAX + 1. This prevents new inserts from conflicting with existing identity values.

cat > /tmp/g.sql <<EOF
SELECT 'SELECT ''ALTER TABLE ' || RTRIM(TABSCHEMA) || '."' || RTRIM(TABNAME) || '" ALTER COLUMN "' || RTRIM(COLNAME) || '" RESTART WITH '' || RTRIM(CHAR(BIGINT(COALESCE(MAX("' || RTRIM(COLNAME) || '"),0)+1))) || '';'' 
FROM ' || RTRIM(TABSCHEMA) || '."' || RTRIM(TABNAME) || '";' FROM SYSCAT.COLUMNS 
WHERE TABSCHEMA='$NEW' 
AND IDENTITY='Y';
EOF
db2 -x -tf /tmp/g.sql > /tmp/g2.sql; 
db2 -x -tf /tmp/g2.sql | grep ALTER > /tmp/fix.sql; 
db2 -tf /tmp/fix.sql

Compare Row Counts Between the Old and New Schemas

Run the generated queries against the new schema and compare the results with the row counts captured from the old schema.
```
sed "s/FROM $OLD\./FROM $NEW./" /tmp/b.sql > /tmp/n.sql; 
db2 -x -tf /tmp/n.sql > /tmp/new
```
The row counts should match for all tables.

Verify that there are no differences between /tmp/base.txt and /tmp/new.txt before proceeding with the upgrade.

After completing the DB2 database migration, follow the steps in the Run upgrade.sh section to complete the upgrade.

Run `upgrade.sh`

Follow these steps to run the upgrade script.

Place the upgrade package

Place the meshIQ_Dpack_Full_Upgrade_to_12.1.1_UDPvx.x.tar.Z file directly in the misc folder at the root of the meshIQ installation. Do not place the file in any other folder.
Before extracting the new upgrade package, remove the existing updates directory and upgrade.sh file from the installation directory.
```
rm - r updates
```
Navigate to the meshIQ installation path misc folder.
```
cd /YourInstallationPath/misc
```

Extract the upgrade package

Run the following command:

tar -zxvf meshIQ_Dpack_Full_Upgrade_to_12.1.1_UDPvx.x.tar.Z

Run the upgrade.sh script

Navigate to the updates folder and run the upgrade script:
```
cd /YourInstallationPath/misc/updates
./upgrade.sh
```
During the upgrade, the process will reach the database update stage and display the following message:
```
Running database update scripts
```
When this message appears, complete the required database configuration and migration steps before proceeding with the upgrade.
- Update the database and schema details in the following property files located in /opt/meshiq/platform/sql-scripts:
  - manage.properties
  - core.properties
  - track.properties
  - ds.properties
  - scheduler.properties
- Also, verify that the required JDBC driver files are available in the following directories:
  - platform/lib
  - apache-tomcat/lib
- Perform the required database migration and upgrade operations.
  For more information, see Migrate Databases
  and Creating Database Tables for Track, DS, Pinot Using jdbcmk Utility.
Run upgrade.sh again if any upgrade steps remain after completing the required database migration and upgrade operations.
After the upgrade completes successfully, click Continue.

Post-Upgrade Configuration

Navigate to /opt/meshiq/platform/apache-tomcat/conf.
Update server.xml with the following database configuration:
- Database connection URL
- Database credentials
- JDBC driver details
Update the cep-ds.properties file in the /opt/meshiq/platform directory for the Observe and Track components.
After updating cep-ds.properties, restart CEP to apply the changes.
In Enterprise Manager, update the following configurations:
- WGS Properties
  - Update the WGS properties with the correct schema and database details.
  - Verify that WGS points to the new meshiq_wgs database.
- Permits Expert Properties
  - Under Domain Server, locate the Permits Expert properties.
  - Update the required database/schema and JDBC configuration.
  - Verify that Permits Expert points to the correct database.
- Job Scheduler Properties
  - Update the Job Scheduler properties with the correct schema and database details.
  - Configure the database connection details, credentials, and JDBC driver.

Start All Services

After completing the database and configuration changes, start all required meshIQ services.

Verify that all services start successfully without errors related to:

Database connectivity
JDBC drivers
Authentication
Missing tables
Invalid schemas
Configuration properties

Validate Applications

After all services are running, log in to the applicable meshIQ applications and verify that:

All applications are accessible.
Users can log in successfully.

If you are upgrading from a version 10.x release, request a new instance-based license before completing the upgrade.

NOTICE for TIBCO EMS Management & Monitoring Users

If you use TIBCO EMS Management and Monitoring, complete the following steps before starting a new EMS Connection Manager.

New TIBCO EMS connections require access to the TIBCO EMS libraries.

Navigate to the nsqcmems Directory

/YourInstallationPath/meshiq/manage/nsqcmems

Edit the file:
```
nsqcmems.properties
```
Locate the line:
```
tib.dir=
```
and specify the location of the Tibco EMS lib folder.

Example:
```
tib.dir=/opt/ems/lib
```

If the tib.dir= entry is not present, add it on a new line immediately after the tib.ems.admin= entry.

After the update script finishes, restart all services.

The upgrade is now complete.

Below additional steps are required only if you are also running Track/XRay deployment on the same system.

Additional Steps

These steps are required only if Track or XRay is deployed on the same system.

Switch to sbin directory under root of your installation and run below additional steps:

Optional: run ./config.sh lic - if you need to load a new Track license
run ./config.sh kaf - to load new Kafka topics
run ./config.sh solrupdate — to upgrade Solr Track Cores

After completing these steps, restart all services.

Track Data Migration

After completing the Track upgrade, migrate the Track UI data.

For information about migrating Track UI data, see Data Migration from 11.x to 12.x.