IFS Cloud File Storage Migration Tool
This document describes the IFS Cloud File Storage Migration Tool, or FS Mig Tool for short.
What is this tool?
This tool copies BLOBs/files from almost any source database to IFS Cloud File Storage.
In this document, the terms "migrate" or "transfer" will also be used. It's important to understand that no file will be "moved", i.e. copied and then automatically deleted in the source database. Only a copy is being made.
The target audience of the tool are customers with many documents and media items who are upgrading from IFS Applications to IFS Cloud and where the database is too big to be moved into the cloud before the files can be moved into File Storage.
This migration tool will only do the copy from the source database to the target IFS Cloud environment. Manual work is required to complete the process, and this document will explain it.
For smaller installations, where the full source database can be moved into the managed cloud environment as is, there are two Aurena assistants for moving document files and media items to IFS Cloud File Storage (Transfer Documents and Transfer Media, respectively.)
That option is fully automatic and is preferable to using this tool.
What is this tool not?
This tool is not a magic wand that will automatically do all the steps needed for migration of large Docman and media implementations. Manual steps, including analyzing the source data, as well as performing post-activities will be needed. For example, when the tool has copied all the data, and when the rest of the database has been moved into the cloud and upgraded, all the file pointers in the upgraded database must be changed to point to the File Storage file repository. Example post-scripts are included with the tool and this documentation explains the process too.
The following areas of IFS Applications are supported:
- Docman - Database repository only.
- Media Library - Only the media_object column from media_item_tab is copied. This is the full-size/original media file. Thumbnails are not copied, and neither are media texts (CLOBs).
What does "valid" mean?
In this document, we mention "valid" documents and media. Certain checks are done for each record that is a candidate for migration.
For documents, this is what "valid" means:
- The document must exist. This is relevant when document information is provided in a text file.
- The document must be checked in. Documents that are checked out or in any other state will be ignored and have to be handled separately.
- The document repository type must be Database.
- The document transfer status is not In Progress.
- The document transfer status is not Done (unless it has changed from when it was last transferred.)
For media, this is what "valid" means:
- The media item must exist. This is relevant when media information is provided in a text file.
- The media item is not empty. It must have a size larger than zero (0) bytes.
- The media item status is not In Progress.
- The media item transfer status is not Done (unless it has changed from when it was last transferred.)
Supported IFS source database versions
The following source database versions have been tested on:
- Apps 9
- Apps 10
- IFS Cloud 21R2
Supported IFS target versions
- IFS Cloud 21R2 and later
Where can I get a copy of this tool?
You can download it from here
After downloading the zip file, unpack it anywhere on the client where it will be run.
Before the tool can be used these steps needs to be taken:
- Installing a custom PL/SQL API used by the tool in the source database
- Create an IAM client for cloud authentication in the target IFS Cloud environment, a service user and a permission set
- Configure the target environment with all file extensions
- Make sure all media items have a file extension
Installing the PL/SQL API in the source database
The API is installed by executing the file FsMigTool.sql in the database folder in SQL*Plus or any other similar tool. It needs to be deployed in the application owner schema (normally IFSAPP) since it requires direct access to the source tables.
Note: This script is ONLY meant to be installed in the source database.
Creating the IAM client
In order to connect to the target IFS Cloud environment, a client ID and client secret needs to be set up, including a service user to be used. This is done in the IAM Client page in Aurena.
The service user used for the IAM client needs to have been granted the projection FssMigrationHandling via a permission set.
Follow these steps:
- Create a user named FS_MIG_TOOL. Set User Type to Service User
- Create a permission set named FS_MIG (it should be an End User Role)
- Grant the FssMigrationHandling projection to the permission set FS_MIG
- Grant the FS_MIG permission set to the FS_MIG_TOOL user
- Create a IAM client with the name IFS_fsmigtool.
- Enable it and select Service Accounts
- In the Username field, select the new FS_MIG_TOOL user and save
For more details on IAM client, refer to the technical documentation for IFS Cloud.
Make sure all media items have a file extension
In order for a media item to be transferred, it must have a valid file extension. There is one scenario where a file name and extension is missing, and that is when a user has copied and pasted an image (not an image file), to the Attachments / Media panel in IEE.
The file name and file extension are saved in the media_file column in media_item_tab. The following UPDATE statement can be used to populate it with a file extension by analyzing the media content (media_object column) itself:
UPDATE media_item_tab m1 SET media_file = (SELECT name || '.' || CASE WHEN DBMS_LOB.Substr(media_object, 3, 1) = hextoraw('FFD8FF') THEN 'JPG' WHEN DBMS_LOB.Substr(media_object, 8, 1) = hextoraw('89504E470D0A1A0A') THEN 'PNG' WHEN DBMS_LOB.Substr(media_object, 4, 1) = hextoraw('47494638') THEN 'GIF' END AS image_type FROM media_item_tab m2 WHERE m1.item_id = m2.item_id) WHERE (media_file IS NULL OR media_file NOT LIKE '%.%') AND media_item_type = 'IMAGE';
Only image formats are supported since the problem should only happen there. If needed, the above can be extened to audio and video file formats as well.
Configure the target environment with all file extensions
The OData provider has a feature to stop the transfer of files with unwanted file extensions. In order to allow uploading of all document and media files all file extensions needs to be added to an allowed list of file extensions.
Open Solution Manager / Setup / System Parameters and find the parameter Allowed File Extensions except which are specified in DOCMAN and Media Extension.
The following query can be executed in the source database to generate a comma-separated list of all the file extensions used by IFS Document Management:
SELECT LISTAGG(file_extention, ',') WITHIN GROUP (ORDER BY file_extention) AS file_extensions FROM edm_application_tab;
For media items, this is the list of supported file extensions:
Add the output of both these lists to the extensions already there in the parameter.
Overall process of migrating files
- Analyze data in the source database
- Decide what to migrate and not
- Migrate the data (what this tool does)
- Delete migrated data from the source database (example scripts provided)
- Export meta data from fss_file_tab (documented elsewhere).
- Move database to IFS managed cloud and upgrade to IFS Cloud
- Import exported meta data into fss_file_tab.
- Update all file references (documents and media, scripts provided)
Migration Tool Usage
This tool uses Java technology. A Java JRE is bundled with this tool under the Java folder.
Use the FsMigTool.cmd script in the root folder to start the tool. The script accepts the following command line arguments:
--alldocs | --docsfromfile FILE | --docclasses CLASS1,CLASS2,... --allmedia | --mediafromfile FILE | --mediatype IMAGE|VIDEO|AUDIO --dbconn CONNSTRING --dbuser USERNAME --dbpass PASSWORD --cloudurl URL --cloudclientid CLIENTID --cloudclientsecret CLIENTSECRET --cloudrealm CLOUDREALM
When transferring documents, only one of these options can be used at a time. The options go from simple and easy to use to advanced but more flexible.
Transfer all valid document files
Transfer all valid document files from a comma-separated list of document classes
Reads the information about each document that should be copied from a comma-separated text file (see example below)
Media library options:
For media, the following options are available. As with documents, only one option at a time can be used:
Transfer all valid media.
Transfer either image, video or audio media. Only one option can be used at a time.
The media item IDs for the media to be copied are taken from a text file (see example below)
Database (source) options:
This is a JDBC connection on the following format: jdbc:oracle:thin:@//DBSERVER:DBPORT/SID.
The database username for logging in to the source database. Normally ifsapp.
Password for the database user.
IFS Cloud connection options:
The tool uses a client ID and client secret to connect. This needs to be configured in the IAM Client page.
The root/server URL (e.g. https://my-server.com)
This enables logging in the fs_mig_log_tab table in the database. The information added to the log table is the same logged to the local log file (that logging is always enabled).
A note on performance
Since each database log entry requires a database call, this logging has an impact on the overall performance. The lower the network latency is, the lower effect database logging has. It is recommended to use this for testing only or when it is necessary to keep the log information in the database. Since the log information is in the database the overall transfer can be remotely monitored in detail by querying the database log table.
Note: transfer status information saved in the fs_mig_status_tab table cannot be disabled as it is critical to know the transfer status of each file.
In this example all valid media will be transferred
fsmigtool --allmedia --dbconn "jdbc:oracle:thin:@//DSE1PDE43:1521/IFSPDB" --dbuser ifsapp --dbpass ifsapp --cloudurl https://ifscloudr2dev-cmb.rnd.ifsdevworld.com --cloudclientid IFS_fssmigtool --cloudclientsecret 4e62774c-c8a9-42ef-9b01-ae8f28896012 --cloudrealm ifscloudr2devcmb
Media defined in a text file
In this example the media items that should be copied are defined in a text file, media.txt.
fsmigtool --mediafromfile media.txt --dbconn "jdbc:oracle:thin:@//DSE1PDE43:1521/IFSPDB" --dbuser ifsapp --dbpass ifsapp --cloudurl https://ifscloudr2dev-cmb.rnd.ifsdevworld.com --cloudclientid IFS_fssmigtool --cloudclientsecret 4e62774c-c8a9-42ef-9b01-ae8f28896012 --cloudrealm ifscloudr2devcmb
Media of a certain type
fsmigtool --mediatype IMAGE --dbconn "jdbc:oracle:thin:@//DSE1PDE43:1521/IFSPDB" --dbuser ifsapp --dbpass ifsapp --cloudurl https://ifscloudr2dev-cmb.rnd.ifsdevworld.com --cloudclientid IFS_fssmigtool --cloudclientsecret 4e62774c-c8a9-42ef-9b01-ae8f28896012 --cloudrealm ifscloudr2devcmb
Above, all media images will be transferred.
Documents of certain classes
fsmigtool --docclasses DRAWING,SPEC --dbconn "jdbc:oracle:thin:@//DSE1PDE43:1521/IFSPDB" --dbuser ifsapp --dbpass ifsapp --cloudurl https://ifscloudr2dev-cmb.rnd.ifsdevworld.com --cloudclientid IFS_fssmigtool --cloudclientsecret 4e62774c-c8a9-42ef-9b01-ae8f28896012 --cloudrealm ifscloudr2devcmb
Above, all valid documents with the class DRAWING or SPEC will be transferred.
Transfer documents and media in the same run
fsmigtool --alldocs --allmedia --dbconn "jdbc:oracle:thin:@//DSE1PDE43:1521/IFSPDB" --dbuser ifsapp --dbpass ifsapp --cloudurl https://ifscloudr2dev-cmb.rnd.ifsdevworld.com --cloudclientid IFS_fssmigtool --cloudclientsecret 4e62774c-c8a9-42ef-9b01-ae8f28896012 --cloudrealm ifscloudr2devcmb
Above, all valid documents and all valid media will be transferred. Note: read about the non-existent support for parallel transfers at the end of this document.
What happens during a transfer?
- Lock record (only documents) - A document has its file status set to Operation In Progress while it is being transferred to IFS Cloud File Storage. This is to stop any user from editing the document while it is copied.
- Create transfer status record in fss_mig_status_tab, set to In Progress
- Transfer (copy) data from the source database to IFS Cloud File Storage. The file is streamed and is never saved temporarily on the local disk.
- Unlock record (documents) - The document file status is set back to Checked In
- Set transfer status to Done, or Failed if the transfer failed
Monitoring: Logs and transfer status information
Information about the status of each transfer is saved in a local log file and can also be recorded in a table in the source database.
The overall progress of the migration is shown on the terminal where the tool was started, but additional information can be extracted from the log file or the database tables described below.
The tool creates local log files for each run. The files have a name on the following format:
"FsMigTool - YYYY.MM.DD.HH24.MI.N"
The date and time is when the tool was started and it will remain the same during the whole execution. N is a log number, starting at 0 for the latest log file, 1 for the previous, 2 for the one previous to that, and so on (highest number = oldest log file in that run.) New log files are created during a run if the size exceeds 5 MB.
Database tables used by the tool
When the tool is installed, two database tables are added to keeping track of transfer status for each record (mandatory) and for logging (optional).
After all files have been migrated and the files are deleted from the source database, the information in the first table below, fs_mig_status_tab, is important to export from the source database and then to import to the target database after the main upgrade.
lu_name VARCHAR2(100) NOT NULL key_ref VARCHAR2(2000) NOT NULL object_rowversion DATE NOT NULL content_length NUMBER NULL status VARCHAR2(20) NOT NULL status_date TIMESTAMP NOT NULL
This table is critical since it keeps track of what records that has been transfered, the status of the transfer, the version of the record that was transfered and when the record was transfered. This information allows rerunning the tool over and over again until all valid records has been transfered successfully.
All transfered data is recorded in this table, for all areas, by using the generic LU name and Key Ref approach.
This table can be joined with other database tables to find out what data that has been transferred and what data is left to be transferred.
The values used for LU name for documents and media is EdmFileStorage (the entity that keeps the document BLOBs) and MediaItem, respectively. The key refs are the proper key refs for these LUs/entities.
- A document key ref: DOC_CLASS=200^DOC_NO=120001^DOC_REV=A1^DOC_SHEET=1^DOC_TYPE=ORIGINAL^FILE_NO=1^
- A media item key ref: ITEM_ID=1234^
lu_name VARCHAR2(100) NOT NULL key_ref VARCHAR2(2000) NOT NULL log_timestamp TIMESTAMP NOT NULL info VARCHAR2(4000) NOT NULL
The log table optionally keeps track of detailed information about the different steps involved in transferring each record. The recorded data is not used by the tool itself, but can be used for analyzing the details of the transfers.
If a transfer fails, it can be retried by running the tool again with the same input. All records that was successfully transferred will be skipped, unless they have changed since they were transfered the last time.
Retrying things that went well
It's possible to make the tool try a successful transfer again.
For documents, run the following UPDATE statement in the source database to make the tool transfer a certain document again:
UPDATE fs_mig_status_tab SET object_rowversion = object_rowversion - 1 WHERE lu_name = 'EdmFileStorage' AND key_ref LIKE 'DOC_CLASS=100^DOC_NO=1200023^%'
Adjust the WHERE condition above as needed. This will make the tool think that the document in the database is newer than the one that was transferred.
For media items, run the following UPDATE statement in the source database to make the tool transfer a certain media item again:
UPDATE fs_mig_status_tab SET object_rowversion = object_rowversion - 1, hash = 'DUMMYHASH' WHERE lu_name = 'MediaItem' AND key_ref LIKE 'ITEM_ID=1234^'
Adjust the WHERE condition above as needed. This will make the tool think that the media item in the database is newer (using rowversion) than the one that was transferred, and that the content have changed (using the hash).
Format of the media text file
The media text file contains a list of media items to be copied, one per line. The file should contain the media item ID on each line as well as the file name (stored as meta data in the File Storage file reference table), separated by a comma (,) like this:
2032,engine.png 2033,rotor.jpg 2040,motor.jpg 2042,failure.png
If you chose the option above you need to fetch the information from the source table (media_item_tab) yourself. You might find that not all media items have a file name stored and then you should provide a stand-in file name with the right file extension, if possible.
Format of the document information text file
If you chose the option to migrate documents based on information in a text file, you need to provide the information below about each document. There should be one line in the text file per document file. The values must be separated by a comma (,). If a value contains a comma, it must be escaped with a backslash (\).
- Doc Class
- Doc No
- Doc Sheet
- Doc Rev
- Doc Type - ORIGINAL, VIEW, ...
- File No - 1, most of the time
- Repository file name - This is the value of the file_name column in edm_file_tab. It is critical that this is correct since it is used for locking the edm_file_tab record during transfer. This value is used as the file name in the File Storage repository.
- Original File Name - It is suggested that you take this from the user_file_name column in edm_file_tab. This is used as extra meta data in File Storage.
Aborting the tool prematurely
If needed, it should be possible to close the tool during migration with little harm.
There is however one caveat to it which is that a document file reference can get stuck in the status Operation In Progress.
If you have to close the tool before it's done you can query the fs_mig_status_tab table for records where the status column = In Progress (ideally there should be only one). You will then need to reset the rowstate column in edm_file_tab to Checked In and the status column in fs_mig_status_tab to Failed (or delete the record in the latter table.)
Since it might be cumbersome to have to do these database updates repeatedly during the initial testing of the tool, it is recommended that only a few records are being transferred, such that the process does not take a very long time and the tool does not need to be aborted.
Here is an UPDATE statement that will change back the file status after an aborted run. Make sure you understand what it does before you run it:
UPDATE edm_file_tab e SET e.rowstate = 'Checked In' WHERE e.rowstate = 'Operation In Progress' AND EXISTS (SELECT 1 FROM fs_mig_status_tab f WHERE f.status = 'In Progress' AND f.lu_name = 'EdmFileStorage' AND f.key_ref = 'DOC_CLASS=' || e.doc_class || '^DOC_NO=' || e.doc_no || '^DOC_REV=' || e.doc_rev || '^DOC_SHEET=' || e.doc_sheet || '^DOC_TYPE=' || e.doc_type || '^FILE_NO=' || e.file_no || '^');
UPDATE fs_mig_status_tab SET status = 'Failed' WHERE status = 'In Progress' AND lu_name = 'EdmFileStorage';
Note: You need to be sure that no transfers are actually in progress when you run the UPDATE statements above.
Similarly, for media:
UPDATE fs_mig_status_tab SET status = 'Failed' WHERE status = 'In Progress' AND lu_name = 'MediaItem';
For most records, the thing that takes most of the time will be the transfer (copying) the source file content to File Storage. This includes authentication/security and other technical details.
The tool also needs to query and update the database, which will have some overhead. But it is critical for the stability of the tool to record what is happening and the tool of course needs to read the data out of the database.
It is recommended to run the tool "as close to the source database" as possible, to reduce the cost of each database call. At the same time you want the tool to run as close as possible to the target IFS Cloud installation, for speedy transfers. Try out different setups to see what works well in a couple of test runs.
Syncing data that have changed since it was transferred
Documents or media items that was successfully transferred by this tool might change before the main upgrad happens. A decision must be taken on if those records should be handled automatically or by this tool.
The following queries can be used to find records that has been changed after they was transferred. The output of these queries can be used by this tool by using the --docsfromfile and --mediafromfile command line options of the tool.
SELECT key_ref FROM fs_mig_status_tab f, edm_file_tab e WHERE f.status = 'Done' AND f.lu_name = 'EdmFileStorage' AND f.key_ref = 'DOC_CLASS=' || e.doc_class || '^DOC_NO=' || e.doc_no || '^DOC_REV=' || e.doc_rev || '^DOC_SHEET=' || e.doc_sheet || '^DOC_TYPE=' || e.doc_type || '^FILE_NO=' || e.file_no || '^' AND f.object_rowversion < e.rowversion;
Deleting migrated data from the source database
This tool will not take care of deleting migrated data from the source database.
Below are some suggestions on how the deletion can be done. Make sure you understand exactly what data you are removing.
For documents, the script POST_Docman_UpdateEdmFileLocationName.sql, mentioned earlier, can be used as a base for deleting migrated document content from the source database.
Instead of updating the Repository attribute of the EdmFile entity, the corresponding record in EdmFileStorage should be deleted.
For media, the script POST_Appsrv_UpdateMediaItemRepository.sql, mentioned earlier, can be used as a base for deleting media item content from the source database.
Instead of updating the Repository attribute, the script should set the MediaObject attribute to NULL.
Updating file pointers in the upgraded database
Two scripts has been provided to update the file pointers for media and documents, respectively. These should be seen as examples or templates and MUST be read through and understoon before they are run.
These scripts are found under the database folder in the distribution of this tool.
This script will use the data in the fss_mig_status_tab to understand what media items that has been successfully transferred to IFS Cloud File Storage.
For each item, the Repository attribute will be set to FILE_STORAGE.
This is an example script and MUST be modified to match the customer's document repository setup. The person who runs and modifies the script must have a good understanding on how the repository data model works.
Parallel transfers not supported
This tool doesn't support parallel transfer. Running multiple instance of the tool in parallel is not officially support and has not been tested.
If there is a need to run the tool in parallel, you may do so at your own risk but it is important that the same file is not picked by several instances. Therefore you would need segregate files accordingly.
How to handle archived media items
The Media Archiving functionality is still available in IFS Cloud 21R2 but will be deprecated in IFS Cloud 22.
This tool does not try to handle media items archived using the Media Archiving functionality. Customers who use that, and who are going to upgrade to IFS Cloud, must first bring in the archived media items they want to migrate into the database, then this tool can migrate those items to File Storage. This could be done in batches.