Upgrade from 5.08.04 to 6.0 process

All IMSMA databases are different and all of the steps below should be done in the mentioned order.

Data Quality SQL queries (mandatory)

There are several SQL queries that should be run before starting the upgrade process, see Data quality checks before upgrading to V6.0.

Run V5.08.04 tools - Locale fix (mandatory)

This tool was created for the upgrade to 5.08.04 and addresses old issues. Many countries did not run it and import scripts may have created new records with issues. This tool has to be run before recalculation the area/length and upgrading to V6.0.

The user-entered format of the coordinates is stored in the database in this format: X | Y | language | country | e.g. 474522|4615907|en|GB| in the column userenteredcoord in the table geopoint.

The locale portion (|en|GB|) of userenteredcoord was added in V5.06.00 but the existing geopoints were not given locale information by the database upgrade functionality. If the locale information was missing, it was added later when field reports were e.g. imported. However, then it was the regional settings of the user doing the import that was used. This created invalid combinations when the data entry was done with other regional settings.

The Locale fix tool will verify that there are no invalid combinations of decimal symbol and locale information in the database. If invalid combinations are stored the tool will try to fix them but there may be combinations that will require SQL updating. This query finds userenteredcoord that the tool cannot fix.

You might need to run the tool with two different regional settings i.e. first with dot as decimal symbol and then with comma as decimal symbol.

Start the tool by clicking on C:\IMSMAng\ext\LocaleUpdater\run.bat.

Click on the Update Database button

Click on the OK button, note that this is the database user imsma, not the application user imsma

If all existing invalid combinations of decimal symbol used and locale information in the database is corrected, the output in the window will look like the above image. One common error that will need to be corrected seconds without decimals but with a decimal dot in Degrees:Minutes:Seconds userenteredcoord e.g. 105:44:95.1|15:10:804.|en|US| which may be corrected by removing the dot.

MGRS will be listed in the output even if they are written with correct format since the Locale tool cannot correct them. If your country has been using MGRS you need to make sure that they are written in the correct format before running the Recalc area/length tool.

Run V5.08.04 tools - Recalculation of area/length (mandatory)

The calculations of calculated area (m2) and calculated length (m) have been updated in V5.08.04. Due to that in previous version different formulas were used and anti-clock wise entered polygons resulted in negative square meters the calculated area/length needs to be recalculated before upgrade to V6.0.

The tool needs the projection files the coordinate system used by the country for the recalculation of the coordinates

Start the tool by clicking on C:\IMSMAng\ext\CalcAreaMigration\run.bat.

Click on the OK button

Click on the OK button, note that this is the database user imsma, not the application user imsma

Click on the Start button.

If it takes much longer time than usual to process the coordinates e.g. 6200 Accidents takes normally approx. 2 minutes but if it takes 20 minutes, then read the log file because that slow recalculation is an indication that the coordinates vs userenteredcoord are invalid.

A report will be given at the end. This will show how many objects were updated, processing time, number of failures, and a detailed report of failures with error messages and object id. This report will also be saved automatically to a file under a "reports" subfolder.

The recalculation can be cancelled by the user at any time. The next time it is run, the recalculation will pick up from the last non-completed table. All tables have to be recalculated before continuing with the next step.

Published templates

There might be templates that are created with an old version of IMSMA^NG and may contain errors that have been undetected in 5.08.04 but will not be working with V6.0. There are countries that have several hundred unused published templates. In order to avoid upgrade problems it is recommended to

1. delete unused published templates (query).
2. discuss with the country if some of the used published templates should be switched together (query)
3. check the names of the remaining templates for odd characters like @ #
4. open the remaining templates in the Designer and verify that they are not multi-object e.g. Location and Land or two Accidents.

It is recommended to save the very latest version of all remain published templates been used for data entry as files. Save them for future use in case the country report problems with them after the upgrade.

Take backup (mandatory)

Take a backup and make sure that the country has a complete backup of the attachments (especially for countries that has GBs of attachments). Otherwise all attachments might be lost when they uninstall 5.08.04 and install 6.0!!!.

Extract MySQL (mandatory)

I recommended to add pause to the run.bat so it will not automatically close because it is helpful to be able to see any error messages. The run.bat require that JAVA_HOME variable is set. If not: set the variable to "C:\IMSMAng\jre1.6.0_01" OR change "%JAVA_HOME%\bin\java" in the run.bat to ""%IMSMA_HOME%jre1.6.0_01\bin\java"

Use the MySQLtoPGSQL tool which will generate two files:

1. Dump.sql and
2. Views.sql.

The MySQLtoPGSQL tool does not have any installation program. Just copy the folder to somewhere on the C-drive.

1. If there is already a dump.sql and/or views.sql in the folder, the tool will append to these files. So do not forget to delete these files between working with different countries.

2. Click on run.bat to start the tool.

3. When the tool has finished, scroll through up in the window and check if there are any error messages. If you get an error that disrupts the raw dump creation you have encountered most likely an invalid data, see Link invalid date.

4. If not, copy the raw dump.sql and views.sql files to a folder e.g. Lebanon Raw. If you have the attachments copy them also to Lebanon Raw
The dump file created with the tool is a raw dump file in PosGreSQL format which is possible to restore with V6.0. Raw as in it is PostgreSQL format but the table structure, etc. are still 5.08.04 and needs to be upgraded to V6.0.
5. The upgrade scripts do not handle the subobjects that should be transformed into normal multi-selects well (2014-11-25/NAA). Run these queries in order to see if there are any records in these tables that later will have to be inserted manually (mandatory) in other tables in PostgreSQL. If there are rows you need to export them and manually add the values in the correct tables with SQL after the upgrade.

Restore the raw dump (mandatory)

Do NOT restore all files from the full 5.08.04 backup: GIS database - you need to rebuild the sandbox after the upgrade anyhow Map data - you will overwrite the ArcGIS 10.1-version of imsma.mxd in C:\IMSMAng\server\gis\maps with the ArcGIS 9.3.1-version Form templates - these are work-in-progress ffml files outside the database. If you do restore, make sure that you do not mix countries Report templates - these are work-in-progress iReports outside the database. If you do restore, make sure that you do not mix countries Translations - you will overwrite the 6.0 translations with 5.08.04 translations.

1. Did the previous database that you worked with on the V6.0 computer have any attachments? If yes, delete them before restoring this database;
2. Restore the raw PostgreSQL dump on a computer with IMSMA V6.0;
3. Scan the log C:\IMSMAng\trayLauncher\trayLauncher.log for error messages;

   As a general rule of thumb, between the row NOTICE: index "workitem_has_cdfvalue_workitemhascdfvalue_cdfvalue_key" does not exist, skipping and the row SqlDump restore Completed it is NOT normal to get any error messages.

   The two error messages below are the so far encountered types of error messages but since table structure, relation rules between tables and contents of all IMSMA^NG databases are different there might be other error messages that you need to react on.

   • error message COPY fieldreportdesc means that it was not possible to load the table so the table is empty;
   • error message ERROR: insert or update on table "fieldreport" violates foreign key constraint "fieldreport_fieldreportdesc_guid_fkey means that mandatory reference (FKs) between tables did not get created because of missing values in e.g. imsmaenum table. IMSMA^NG depends on that FKs are created.;

Check to be done BEFORE next step (mandatory)

There are a few checks and possibly manual export to be done BEFORE next step.

1. The upgrade scripts will wrongly change enumvalues to Inactive and therefore the current values must be documented ( query)

   e.g. if the country has decided that enumvalue OutOfRange in enumcategory Priority should be inactive, the upgrade script will set it to active.

2. How many rows are there in the attachment table? If more than 0, then you have to verify that the attachments are included in the backup folder or instruct the country on how to handle them when you are sending them the 6.0 backup.

Upgrade the database (mandatory)

Start the TrayLauncher for starting the upgrade functionality.



If you are upgrading from MySQL there are 134 upgrade scripts to be applied (March 2018 build). The added upgrade scripts in 24 October 2014 build takes several hours to run and the update-reconciliation-locations-column has cause problems:

1. upgrade script finished without error message but when reading the log there were database changes not done (Laos which already were on 6.0)
2. upgrade script starting to fill the log with error messages so I aborted the upgrade and re-started (Sri Lanka from MySQL)
3. upgrade script throwing error message at every restart of the upgrade process (Abyei) then one approach is to update the column yourself with SQL
4. upgrade script could not finish even after +50 restarts (Yemen). It seems that the database changes were roll backed. If this happens then the work-around is to update the infoversions with SQL and update schema_info.

So check the log C:\IMSMAng\trayLauncher\trayLauncher.log even if the upgrade scripts have not thrown an error.

Check to be done BEFORE next step (mandatory)

1. Check if the upgrade scripts created duplicate enumvalues and if necessary fix them (mandatory)( query )(depends on the enumvalues the country had before the upgrade);

   Duplicate enumvalues cause import issues i.e. empty fields, which are difficult to detect due to there are no error messages;

2. Check if the upgrade scripts wrongly changed enumvalues to Inactive (mandatory)( query )(still happening with 18 Feb 2015);
3. Check if the upgrade scripts created duplicate translation and if necessary fix them (mandatory)( query)(depends on the translations the country had before the upgrade and their enumvalues, ICR-297 & ICR-299);
4. If needed add Detection/Samle/Sector/Vegetation removal records to the correct tables e.g. hazreducinfoversion_has_imsmaenum;
5. The upgrade script should have changed all points that used Location point to Reference point but it does not. If there are any Location points you need to inform the country that the type has changed to Reference points. These queries will do the update.

   A database that is already upgrade to V6.0 with one of the early Betas might need manual manipulation e.g. Beta 2 to Beta 3.

   The upgrade script does not update user_entered_x and user_entered_y when distance and bearing have been used. This creates problems for the countries so you MUST fix this.

6. Run query Check coordinates distance and bearing (mandatory).
7. If there area any Distance and bearing then refer to this page and check if there is more than the upgrade issue to fix with Distance and bearing Check coordinates distance and bearing 6.0 (mandatory).

Check database for geo orphans, distance/bearing and link issues (mandatory)

There might be other issues with the database that has gone undetected by the country. Examples:

• geo orphans
• childless geospatial
• odd cases of distance/bearing
• issues with the link table.

This page describes known issues which should be fixed.

Apply updates of Fkeys VA (mandatory)

There are Fkeys in the database that causes lost of data if e.g. an Organisation is deleted.

Update imsmaenum

It is good to update widgettype and seqno so the enumeration values are sorted in a logical/alphabetical order.

Rebuild sandbox (mandatory)

If you have completed the previous steps, and no errors remain, you may proceed with rebuilding the sandbox. Both the IMSMA database and GIS database have been modified to support e.g. the Assistance object. As a result, the GIS database (sandbox) must be completely rebuilt in IMSMA V6.0 format.

If you are connected to a network/wireless network that is not stable, disable it while rebuilding the sandbox.

1. Delete the C:\IMSMAng\client\gis\sandbox folder;
2. Start the IMSMA server and the IMSMA client;
3. When the rebuild is done, open the client log and check for error messages.

INFO  2014-11-18 19:22:36,861 [changelog.ChangeLogHandler] line:609 -   Synchronizing 1692 Place items ...
FATAL 2014-11-18 19:22:46,268 [features.AddToMapHelper] line:116 - com.fgm.imsma.gis.GeoException: Error creating Domain Object label marker
com.fgm.imsma.gis.GeoException: Error creating Domain Object label marker
	at com.fgm.imsma.gis.impl.DomainObject.finish(DomainObject.java:766)
	at com.fgm.imsma.gis.impl.DomainObject.getDomainObject(DomainObject.java:454)

ERROR 2014-11-18 19:22:46,268 [changelog.ChangeLogHandler] line:366 -    Error synchronizing geospatial data cache with Place item c8ff-ff94-1495a9e0369-e7382be5-33-107
com.fgm.imsma.gis.GeoException: Error creating Domain Object label marker
	at com.fgm.imsma.gis.impl.DomainObject.finish(DomainObject.java:766)
	at com.fgm.imsma.gis.impl.DomainObject.getDomainObject(DomainObject.java:454)
	at com.fgm.imsma.client.gis.features.AddToMapHelper.addObject(AddToMapHelper.java:522)

This error message indicates that geographical data is missing in the database e.g. the record in table geopoint is deleted.

Templates - Run the image tool (mandatory)

Only the 2014052701-IMAGE_UPDATE version of this tool works.

Image in 5.08.04	Image in 6.0

If you do not want to use the tool, you may delete the images/logotypes in either 5.08.04 or 6.0 manually. In 5.08.04 they are easier to see than in V6.0.

The tool should be run twice:

1. Published templates that are in database;
2. Files that are in C:\IMSMAng\client\FormTemplates\MyTemplates.

Other errors in templates are NOT fixed by this tool or by upgrading. If you do not address the image issue the templates will not be possible to use for data entry or to edit in V6.0. It will be possible to publish them once.

Open Data Inventory Manager and the Workbench (mandatory)

1. Open the console log
2. Open DIM
3. Check for any error messages in the log?
4. Open WB
5. Check for any error messages in the log?

Templates - Checking the templates by publishing them in V6.0 (mandatory)

After you have either run the tool or removed the images manually you should test that all published templates are still working by publish them in V6.0. Check the log when you drag them into the designer. This error message in the log means that there is still an image in wrong format in the template: Need to update FFML image to Image object

1. The template RAPID Accident has to be fixed: delete Wind speed, drag it in again and publish the template.
   
2. Victims templates with Time to First Medical Facility and/or Time to Hospital must be updated with the new widget.
   
3. Land and Activity templates with the five Time to Hospital must be updated with the new widget.
   
4. Activity templates that had the 3 deleted subobjects must be updated with the new fields.
   
5. Accident templates with the old widget that included time for Time of Accident must be updated and Unknown device Y/N removed.
   
6. Data Entry Forms still using Default Migration xx Templates should be switched to newer templates.
   

Other possible issues are:

• the same field more than once in the template;
• value list of multi-select is changed;
• other problems are found Upgrade Category Error.

Data Entry, export/import and viewing the Reconciliation tab will NOT work if the above actions are not done.

Templates - Checking the templates by opening them for data entry and saving (mandatory)

After you have published the templates then

1. open them
2. check the log when you open them
3. scroll through the template and look for any single and multi-select that have default values that do not look OK/normal to be set to default
4. set Local ID e.g. Victim ID and save (important for link check).

If you have errors like this or error message for comboboxes, delete the field from the template, drag it in again and publish.

This error message in the console log:

Internal Exception: Error retrieving field report description (c0a8-f801-146a9ae9801-3a0d4f83-2-b273) to load into form

java.lang.NullPointerException at javax.swing.ImageIcon.<init>(Unknown Source) at com.fgm.freeform.component.ImagePanel.loadFrom(ImagePanel.java:250)
means that there is an image in wrong format in the template and therefore IMSMA^NG may not load the template into the Template Designer or DEF editor.

This error message in the console log:

[component.SingleSelectComboBox] line:194 - Field report template contains SingleSelectComboBox data that does not match data in the DIM.

means that there is still something wrong with a value list for one of the comboxboxes in the template.

This error message in the console log:

[fieldreport.FieldReportFrameController] line:559 - Error saving fr java.lang.NullPointerException

means that there could be a field in the template that has been deleted either by the administrator since template was published the last time or by the upgrade scripts e.g. Unknown device in Accident.

Reimport map (could be done by the country)

The base imsma.mxd file has been updated to support e.g. the Assistance object and relabelling of objects. The background map must, be reimported and it is recommended to upgrade the background map's mxd-file to version 10.1.

Before reimporting the map, make sure that the C:\IMSMAng\server\gis\maps\imsma.mxd is the 10.1 version. It should be approx. 5MB, if it is 15MB then you have replaced it with the 9.3.1 version by restoring Map data. Verify also that the background map is a clean map or otherwise you will get duplicate layers in the merged mxd.

Verify that all categories used in your database have a symbol in the Map display in IMSMA^NG. Categories without a symbol will be invisible on the map. Adjust imsma.mxd with ArcGIS Desktop and reimport the map. Remember to rename your background map mxd for each reimport.

Verify that the fonts are OK too.

Upgrade iReport (mandatory)

IMSMANG version 6.0 comes with 35 inspirational iReport templates. Therefore, if there are more than 35 in the Report Template Manager in the Customisation menu, it means that custom ones have been created by the country, and need to be investigated one by one. In IMSMANG version 6.0 there are 3 Report definitions. Therefore, if there are more than 3 in the Report List Window in the Report menu, it means that the custom Report definitions have been done by the country. After the iReports templates themselves have been upgraded then the templates have to be tested with their Saved Searches. For more details and step-by-step instructions, see Upgrade iReports to 6.0.

Take backup (mandatory)

If you have not worked with the background map in ArcGIS 10.1, etc. you only need to include the GIS database. Take backup and make sure that you save the backup folders e.g. on NAS*

1. the 5.08.04
2. the Raw
3. the 6.0

Create a staging area database (mandatory)

Create a staging area database with geodata. During the creation of the staging area database other data issues may become known. Correct the data until you may create a staging area.

Implement V6.0 in the country (mandatory)

• Send them the V6.0 backup
• Installation package
• Apply/change/implement what is needed from the below check list.

Check with the countries (mandatory)

• Import scripts

All scripts needs to be reviewed and adapted to PostgreSQL/V6.0, see IMSMA3 script changes 6.0;

Manual update of the column position in table cdfvalue need to be added.

• Import coordinates for one polygon with Excel or shp
  Do their templates and/or procedure work with V6.0 and ArcGIS 10.1?

• Saved searches

There are many database changes between previous versions and V6.0 and therefore existing saved searches might need to be updated.

The new saved searches might need to be adapted to the country's terms and process.

• Data entry templates

There are many database changes between previous versions and V6.0 and therefore existing templates might need to be updated. In the Using IMSMA portal, Viewing and Browsing, Items Pane there are lists with the new fields added in the upgrade.

• Summary templates

The 5.08.04 summary templates that the countries had developed get status Archived during the upgrade process. The 5.08.04 summary templates may contain fields that do not exist anymore and therefore it is "safer" to customize the new standard summary templates by e.g. add CDFs to them than to adjust the 5.08.04 summary templates to 6.0.

• iReports

Please note when you upgrade an existing database existing iReports will not automatically be upgraded. The required changes are documented on the Upgrade iReports to 6.0 page.

• SQL views

The SQL views needs to be redone in PostgreSQL and may be redone from scratch or by editing view.sql (output from the extracting step).

• Do they have data stored in CDFs that there are standard fields for in V6.0?

If yes, is there performance or other gains by moving the data to standard fields?

If yes, the source and target fields need to be mapped. There is a template Excel file for that.

• Coordinate system manager

If they want to use other coordinates systems than WGS 84 and MGRS it has to be specified again.

• Local projections

Prj files that are not standard ESRI needs to be copied to C:\IMSMAng\server\gis\coordinate systems.

• Server and client settings/configuration incl. replication

memory allocation is in C:\IMSMAng\trayLauncher\TrayLauncher.properties

If they had configured any settings you need to apply them again.

• Users and permissions

Are there any users that should be inactivated?

Does existing users have appropriate permissions?

• Export of xml

If the country was using File – Export function, verify that they understand how to export from Advanced Search.

• Import of xml

Test that it works

• Wiki

Show them where the off-line wiki is stored C:\IMSMAng\trayLauncher\exe\Kiwix and copy the latest zim file there.

Create a favourite link to the on-line wiki in the Internet browser.

{{#switch:|subgroup|child=|none=|#default=

}}{{#if:|}}{{#if:Upgrade Process|<td style="text-align:left;border-left-width:2px;border-left-style:solid;|{{#if:|}}}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:upgrade from 5.08.04 to 6.0 process

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}{{#if:|{{{group2}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list2}}}

}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:upgrade from 5.08.04 to 6.0 process

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}{{#if:|{{{group3}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list3}}}

}}{{#if:|{{#if:|{{{group4}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list4}}}

}}{{#if:|{{#if:|{{{group5}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list5}}}

}}{{#if:|{{#if:|{{{group6}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list6}}}

}}{{#if:|{{#if:|{{{group7}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list7}}}

}}{{#if:|{{#if:|{{{group8}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list8}}}

}}{{#if:|{{#if:|{{{group9}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list9}}}

}}{{#if:|{{#if:|{{{group10}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list10}}}

}}{{#if:|{{#if:|{{{group11}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list11}}}

}}{{#if:|{{#if:|{{{group12}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list12}}}

}}{{#if:|{{#if:|{{{group13}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list13}}}

}}{{#if:|{{#if:|{{{group14}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list14}}}

}}{{#if:|{{#if:|{{{group15}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list15}}}

}}{{#if:|{{#if:|{{{group16}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list16}}}

}}{{#if:|{{#if:|{{{group17}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list17}}}

}}{{#if:|{{#if:|{{{group18}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list18}}}

}}{{#if:|{{#if:|{{{group19}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list19}}}

}}{{#if:|{{#if:|{{{group20}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list20}}}

}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:upgrade from 5.08.04 to 6.0 process

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}

{{{below}}}

}}{{#switch:|subgroup|child=

|none=|#default=}}{{#ifeq:|Template|{{#ifeq:|child||{{#ifeq:|subgroup||{{#switch:upgrade from 5.08.04 to 6.0 process

|doc
|sandbox
|testcases =
|#default = {{#switch:hlist
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}