iWD History Node Migration Information

Database Schema Migration

There are two scenarios to note when you should perform database schema migration:

• Creating a database schema from scratch in the initial startup process.
• Updating the database schema when upgrading iWD History Node to a new version.

In both cases it works the same way. Complete either one of the following procedures:

• Enable automatic database schema migration by setting the value of Configuration Server option [iWD]/migrate-schema to true. This means that the application will check the current data schema at startup and update (or create) it if necessary. Note that default value of [iWD]/migrate-schema is false. Or;
• Migrate the schema manually using the command line as shown below:
  • Linux—./iwd_history.sh -host CFG_SRV_HOST -port CFG_SRV_PORT -app HN_APP db migrate
  • Windows—JavaServerStarter.exe -host CFG_SRV_HOST -port CFG_SRV_PORT -app HN_APP db migrate

Important

All commands like "./iwd_history.sh" and "JavaServerStarter.exe" should be executed from the directory where History Node has been installed.

Troubleshooting

Schema migration can sometimes get stuck in a loop that prevents History Node starting. When this happens, the following entries are written to the log file:

INFO  [2017-05-26 10:10:57,497] com.genesyslab.iwd.history.HistoryApplication: Running database schema migration
INFO  [2017-05-26 10:11:03,313] liquibase: Waiting for changelog lock....
INFO  [2017-05-26 10:11:13,317] liquibase: Waiting for changelog lock....
...

To correct this, after stopping History Node application, do the following:

• On Linux

1. List any database locks.

./iwd_history.sh -host CFG_SRV_HOST -port CFG_SRV_PORT -app HN_APP db locks --list

2. Release the database locks.

./iwd_history.sh -host CFG_SRV_HOST -port CFG_SRV_PORT -app HN_APP db locks --force-release

• On Windows:

1. List any database locks.

JavaServerStarter.exe -host CFG_SRV_HOST -port CFG_SRV_PORT -app HN_APP db locks --list

2. Release the database locks.

JavaServerStarter.exe -host CFG_SRV_HOST -port CFG_SRV_PORT -app HN_APP db locks --force-release

Migrating GTL History Events

You can migrate GTL history events from the Event Log database to the History Node database by using History Node's built-in mechanism, which can be run with the following command:

• On Linux:

./iwd_history.sh -host CFG_SRV_HOST -port CFG_SRV_PORT -app HN_APP events-migrate <yaml-config-filename>

• On Windows:

JavaServerStarter.exe -host CFG_SRV_HOST -port CFG_SRV_PORT -app HN_APP events-migrate <yaml-config-filename>

where <yaml-config-filename> is the path to the YAML configuration file providing connection settings to the old environment.

In order to use the events migration mechanism, the History Node application must be properly configured. The mechanism itself is configured in the [event-log-migration] section of the yaml configuration file. You can find an example below.

Warning

When events migration is in process, make sure that one of the following conditions applies:

• No History Node applications are running.
• If any History Node applications are running, they must have history events removal disabled (that is, the stop-processing option must be set to keep)
• The GTL History Events Migration mechanism only migrates events into iWD Manager database and doesn't support migration to the Data Mart database.

YAML Configuration File Example

Commands must specify the path to the .yaml file. See the first line of the working example below.

Important

For security reasons, delete the passwords once migration/creation is complete.

Sample configuration

The following example consists of configuration for an Oracle database. For MSSQL databases you should specify the corresponding driver and appropriate validation query.

 logging:
  level: INFO
  appenders:
    - type: file
      currentLogFilename: iwd_history_startup.log
      threshold: ALL
      archive: false
      timeZone: UTC
 event-log-migration:
   solutionId: SLT3
   fetchSize: 10
   interaction-server:
     driverClass: oracle.jdbc.driver.OracleDriver
     url: jdbc:oracle:thin:@//<db_host>:<db_port>/<SID>
     user: <db_username>
     password: <db_password>
     logValidationErrors: true
     validationQuery: select 1 from DUAL
   eventlog:
     driverClass: oracle.jdbc.driver.OracleDriver
     url: jdbc:oracle:thin:@//<db_host>:<db_port>/<SID>
     user: <db_username>
     password: <db_password>
     logValidationErrors: true
     validationQuery: select 1 from DUAL

Working Command Line with Output Example

 [genesys@xx-yyy-iwd119 iwd_history_1]$ ./iwd_history.sh -host xx-yyy-iwd119 -port 8888 -app iwd_history_1 events-migrate /tmp/iwd_historynode_gte.yaml
 INFO  [2017-06-21 12:25:18,228] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Migrate events from Event Log db to the History Node
 INFO  [2017-06-21 12:25:21,084] com.genesyslab.iwd.history.HistoryApplication: Queue mappings for solution SLT3: IWDQueue [NEW=iWD_New_custom, ERROR_HELD=iWD_ErrorHeld_custom, CAPTURED=iWD_Captured_custom, QUEUED=iWD_Queued_custom, COMPLETED=iWD_Completed_custom, CANCELED=iWD_Canceled_custom, REJECTED=iWD_Rejected_custom]
 INFO  [2017-06-21 12:25:21,084] com.genesyslab.iwd.history.HistoryApplication: Strategies names for solution SLT3: IWDStrategy [CLASSIFICATION=Classification_custom, PRIORITIZATION=Prioritization_custom]
 INFO  [2017-06-21 12:25:21,084] com.genesyslab.iwd.history.HistoryApplication: Queue mappings for solution SLT4: IWDQueue [NEW=iWD_New, ERROR_HELD=iWD_ErrorHeld, CAPTURED=iWD_Captured, QUEUED=iWD_Queued, COMPLETED=iWD_Completed, CANCELED=iWD_Canceled, REJECTED=iWD_Rejected]
 INFO  [2017-06-21 12:25:21,085] com.genesyslab.iwd.history.HistoryApplication: Strategies names for solution SLT4: IWDStrategy [CLASSIFICATION=Classification, PRIORITIZATION=Prioritization]
 INFO  [2017-06-21 12:25:21,085] com.genesyslab.iwd.history.HistoryApplication: Stop processing: KEEP
 INFO  [2017-06-21 12:25:21,085] com.genesyslab.iwd.history.HistoryApplication: GTL messages processing: ENABLED
 INFO  [2017-06-21 12:25:21,085] com.genesyslab.iwd.history.HistoryApplication: DM messages processing: ENABLED
 INFO  [2017-06-21 12:25:21,085] com.genesyslab.iwd.history.HistoryApplication: Migrate database schema on startup: ENABLED
 INFO  [2017-06-21 12:25:21,297] com.genesyslab.iwd.history.HistoryApplication: History Node version=9.0.000.S318
 HistoryApplicationConfiguration{
  queue=MessageQueueFactory{initialContextFactory='org.apache.activemq.jndi.ActiveMQInitialContextFactory', connectionFactory='ConnectionFactory', reconnectDelay=10, queues=[InxEventLogQueue queue at tcp://xx-yyy-iwd119.emea.int.genesyslab.com:61616]}
  processorThreads=10
  receiveTimeout=10
  batchSize=1000
  stopProcessingInterval=60
  stopProcessing=KEEP
  stopProcessingDelay=60
  processGTL=true
  processDM=true
  migrateSchemaOnStartup=true
  database.url=jdbc:oracle:thin:@//xx-yyy-iwd220:1521/ci, database.driverClass=oracle.jdbc.OracleDriver, database.validationQuery=select 1 from DUAL
 }
 INFO  [2017-06-21 12:25:21,781] com.genesyslab.iwd.history.HistoryApplication: Database name: Oracle
 INFO  [2017-06-21 12:25:21,781] com.genesyslab.iwd.history.HistoryApplication: Database version: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 INFO  [2017-06-21 12:25:21,781] com.genesyslab.iwd.history.HistoryApplication: Database driver: Oracle JDBC driver
 INFO  [2017-06-21 12:25:21,781] com.genesyslab.iwd.history.HistoryApplication: Database driver version: 12.1.0.1.0
 INFO  [2017-06-21 12:25:21,783] com.genesyslab.iwd.history.HistoryApplication: Running database schema migration
 INFO  [2017-06-21 12:25:24,493] liquibase: Successfully acquired change log lock
 INFO  [2017-06-21 12:25:27,001] liquibase: Reading from IWD_HISTORY_IWD119.DATABASECHANGELOG
 INFO  [2017-06-21 12:25:27,027] liquibase: Successfully released change log lock
 INFO  [2017-06-21 12:25:27,229] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Connecting to the Interaction Server database
 INFO  [2017-06-21 12:25:27,839] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Events for 15 interaction(s) will be migrated
 INFO  [2017-06-21 12:25:27,839] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Connecting to the Event Log database
 INFO  [2017-06-21 12:25:30,054] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Migration progress: 1/15
 INFO  [2017-06-21 12:25:30,068] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Migration progress: 2/15
 INFO  [2017-06-21 12:25:30,071] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Migration progress: 3/15
 INFO  [2017-06-21 12:25:30,073] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Migration progress: 4/15
 INFO  [2017-06-21 12:25:30,075] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Migration progress: 5/15
 INFO  [2017-06-21 12:25:30,077] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Migration progress: 6/15
 INFO  [2017-06-21 12:25:30,079] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Migration progress: 7/15
 INFO  [2017-06-21 12:25:30,081] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Migration progress: 8/15
 INFO  [2017-06-21 12:25:30,084] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Migration progress: 9/15
 INFO  [2017-06-21 12:25:30,087] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Migration progress: 10/15
 INFO  [2017-06-21 12:25:30,089] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Migration progress: 11/15
 INFO  [2017-06-21 12:25:30,091] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Migration progress: 12/15
 INFO  [2017-06-21 12:25:30,093] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Migration progress: 13/15
 INFO  [2017-06-21 12:25:30,095] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Migration progress: 14/15
 INFO  [2017-06-21 12:25:30,097] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Migration progress: 15/15
 INFO  [2017-06-21 12:25:30,099] com.genesyslab.iwd.eventlog.EventLogEventsMigrator: Migration finished
 INFO  [2017-06-21 12:25:30,103] com.genesyslab.iwd.history.HistoryApplication: Shutdown hook triggered
 INFO  [2017-06-21 12:25:30,103] com.genesyslab.iwd.history.HistoryApplication: Shutdown hook completed
 INFO  [2017-06-21 12:25:30,103] com.genesyslab.iwd.history.HistoryApplication: Shutdown hook exited
 [genesys@xx-yyy-iwd119 iwd_history_1]$