Restoring time series

Sometimes there might be a need to analyze some data that is already outside the configured history length of a ABB Ability™ History system and no longer available. If this data is still available as a backup there are a few options available, which can be used to restore it to a running system without restarting any of the components.

By extending the history length

One way is to extend the history length from the HistoryTables -table configuration and then restore the backed up table files to database directory by simply copying them there. When copying files to a running system the .TableData -files should be copied first and the .TableDefinition files only after the copying of .TableData -files are finished to prevent the database from trying to open tables too early (in which case it would create an empty .TableData -file by itself and lock the files preventing the successful restore). The good thing in this approach is that after restore data is instantly available the same way as it originally was and removing it can be done simply by restoring the history length to its original setting. It is also possible to flag interesting periods of the original history data to TrailLog -table so that they will be kept in the Trail -table even after the history length is restored back to its original setting. The bad thing is that when history length is extended to cover also the restored area the growing limit of the corresponding table is also removed and it can keep growing until the disk is full. Due to this, when making this kind of configuration, there should always be some watchdog code that restores the history length to original setting also in case the end-user forgets to do it him/herself.

The following example scripts are provided as an example and need engineering before used in an actual production system.

Example restore script (.bat)

@echo off
SET BACKUP_DIR=PUT_YOUR_BACKUP_DIRECTORY_HERE
SET HISTORYTABLE=CurrentHistory
rem The following start and end times do not represent actual times, but the history split file starting times! Start is inclusive, end non-inclusive.
SET STARTTIME=20170301
SET ENDTIME=20170701
rem Save the current history length (a safety check included to prevent messing up the save in case it was already extended)
praox %APP_CORE_DSN% -sql "SELECT CTHL,CTHLU,HTID FROM HistoryTables WHERE HTNAM='%HISTORYTABLE%' AND (CTHL<1000 OR CTHLU<8)" -noecho -opt fmt "UPDATE HistoryTables SET CTHL=%%s,CTHLU=%%s WHERE HTID=%%s;\n" -outappend "%APP_DATAPATH%\savedlength.sql"
rem Extend the history length to 1000 years
praox %APP_CORE_DSN% -sql "UPDATE HistoryTables SET CTHL=1000,CTHLU=8 WHERE HTNAM='%HISTORYTABLE%'"
rem Copy the tables from backup to the database directory
for %%I in ("%BACKUP_DIR%\RTDB_%HISTORYTABLE%_*.TableData") do (
   if /i "%%~nI" GEQ "RTDB_%HISTORYTABLE%_%STARTTIME%_*" if /i "%%~nI" LSS "RTDB_%HISTORYTABLE%_%ENDTIME%_*" (
      copy /Y "%BACKUP_DIR%\%%~nI.TableData" "%APP_DATAPATH%"
      copy /Y "%BACKUP_DIR%\%%~nI.TableDefinition" "%APP_DATAPATH%"
   )
)

Example cleanup script (.bat)

@echo off
rem Restore original history length 
praox %APP_CORE_DSN% -ss "%APP_DATAPATH%\savedlength.sql"
del "%APP_DATAPATH%\savedlength.sql"

By renaming the tables

Another way is to have another history definition with a different name in the HistoryTables -table reserved for the later analysis purposes and then renaming the tables before restoring them. The renaming must be done before restoring the tables and "RTDB_ScanDB -a tablename" -command must be run for the tables after the rename and before the restore in order to correct the table name also in the internal structures. After the ScanDB has been run the files can be moved to database directory the same way as described in the "By extending the history length" -paragraph. With this approach there is no need to touch the history definitions of the tables that are used to store the on-line process data, thus eliminating the risk of on-line histories filling up the disk. The bad thing, however, is that in order to access this restored data, the user needs to give different history table as a parameter when fetching the data from the database. This might not be a big deal as the "History" parameter could always be there and just be null in a normal run and then changed to point to another history when analyzing the restored data. The restored data can be removed by simply adjusting the history length of this analysis history to zero (there is a slight delay before the tables actually get deleted).

The following example scripts are provided as an example and need engineering before used in an actual production system.

Example restore script (.bat)

@echo off
SET BACKUP_DIR=PUT_YOUR_BACKUP_DIRECTORY_HERE
SET HISTORYTABLE=CurrentHistory
SET ANALYSISTABLE=AnalysisHistory
rem The following start and end times do not represent actual times, but the history split file starting times! Start is inclusive, end non-inclusive.
SET STARTTIME=20170301
SET ENDTIME=20170701
rem Create save-file for setting the history length to zero (do it here so that we do not need the table names in the cleanup side)
echo UPDATE HistoryTables SET CTHL=0,CTHLU=8 WHERE HTNAM='%ANALYSISTABLE%';> "%APP_DATAPATH%\savedlength.sql"
rem Extend the history length to 1000 years
praox %APP_CORE_DSN% -sql "UPDATE HistoryTables SET CTHL=1000,CTHLU=8 WHERE HTNAM='%ANALYSISTABLE%'"
rem Copy the tables from backup to a temporary directory
md "%APP_DATAPATH%\temp"
for %%I in ("%BACKUP_DIR%\RTDB_%HISTORYTABLE%_*.TableData") do (
   if /i "%%~nI" GEQ "RTDB_%HISTORYTABLE%_%STARTTIME%_*" if /i "%%~nI" LSS "RTDB_%HISTORYTABLE%_%ENDTIME%_*" (
      copy /Y "%BACKUP_DIR%\%%~nI.TableData" "%APP_DATAPATH%\temp"
      copy /Y "%BACKUP_DIR%\%%~nI.TableDefinition" "%APP_DATAPATH%\temp"
   )
)
rem Rename the files
ren "%APP_DATAPATH%\temp\RTDB_%HISTORYTABLE%_*" "RTDB_%ANALYSISTABLE%_*"
rem Run the ScanDB
RTDB_ScanDB -ab "%APP_DATAPATH%\temp" "%ANALYSISTABLE_%*"
rem Move the tables from temporary directory to database directory
move "%APP_DATAPATH%\temp\RTDB_%ANALYSISTABLE%_*.TableData" "%APP_DATAPATH%"
move "%APP_DATAPATH%\temp\RTDB_%ANALYSISTABLE%_*.TableDefinition" "%APP_DATAPATH%"
rd "%APP_DATAPATH%\temp"

Example cleanup script (.bat)

@echo off
rem Set length of analysis history to zero
praox %APP_CORE_DSN% -ss "%APP_DATAPATH%\savedlength.sql"
del "%APP_DATAPATH%\savedlength.sql"

By restoring data to _Trail -table

Each history in ABB Ability™ History system has a _Trail -table, that can contain values outside of the history. Data can be also restored to _Trail table later time by using some tool that copies the desired rows from the backup to the _Trail -table. In this case, it is also required to insert a row to TrailLog -table describing the restored range in order to prevent ABB Ability™ History from removing the restored data too early. In this approach the restored data is instantly available as part of the original history just like in the "extending history length" -method and there is no need to fiddle with fetch parameters or history definitions. The copying of the data, however, is much heavier operation than copying the entire tables and removing it after the analysis is complete does not free the disk space even though the removed data areas are recycled in for the next analysis.