Migration Scripts for Large Sets of Data in Value Streams (8.3 and below)
Overview
In ThingWorx 8.3 and earlier, there is a possibility that value stream entries reach the maximum limit (MAX_INT) and new value stream entries cannot be created. This can be prevented by running scripts provided by PTC.
When you run the scripts, the following occurs:
• A new table is created so that the ThingWorx server can start immediately. The value stream schema is changed to support big integers instead of integers so that you do not run into an issue with MAX_INT limitations when updating the database.
• Historical data is migrated from the old table to the new table as a background task. This allows you to use the new ThingWorx install immediately, since historical data may take more time to import into the new table.
Applicable Versions
New installs of ThingWorx 8.4.0 and later contain this feature, so no action is necessary. You can run these scripts from any version of ThingWorx, so if you are upgrading from an earlier version or if you want this feature in your current version, follow the steps below.
Location of Scripts
The scripts are available in the ThingWorx software download in the
update folder. Software downloads are located on the
PTC Support site.
Running the Scripts
Follow these steps to run the scripts:
1. Stop Tomcat.
2. Back up your PostgreSQL or MSSQL database.
3. Update the connection information (server, port, database, username, password) in the schema update and data migration scripts:
◦ PostgreSQL: thingworxPostgresValueStreamSchemaUpdate.sh (Linux) OR thingworxPostgresValueStreamSchemaUpdate.bat (Windows)
AND
thingworxPostgresValueStreamDataUpdate.sh (Linux) OR thingworxPostgresValueStreamDataUpdate.bat (Windows)
◦ MSSQL: thingworxMssqlValueStreamSchemaUpdate.sh (Linux) OR thingworxMssqlValueStreamSchemaUpdate.bat (Windows) AND thingworxMssqlValueStreamDataUpdate.sh (Linux) OR thingworxMssqlValueStreamDataUpdate.bat (Windows)
4. Execute the schema update script:
◦ PostgreSQL: thingworxPostgresValueStreamSchemaUpdate.sh (Linux) OR thingworxPostgresValueStreamSchemaUpdate.bat (Windows)
◦ MSSQL: thingworxMssqlValueStreamSchemaUpdate.sh (Linux) OR thingworxMssqlValueStreamSchemaUpdate.bat (Windows)
5. Start Tomcat.
6. Execute the data migration script:
◦ PostgreSQL: thingworxPostgresValueStreamDataUpdate.sh (Linux) OR thingworxPostgresValueStreamDataUpdate.bat (Windows)
◦ MSSQL: thingworxMssqlValueStreamDataUpdate.sh (Linux) OR thingworxMssqlValueStreamDataUpdate.bat (Windows)