Hi to all,

i'm working with a company, which is planning migration from MS SQL to Oracle (only DB part, considering that application remains the same).
I have tried searching for recommendations, what is the easiest + fastest solution for such task.
Business accepts downtime of existing system's downtime for max 2 days, therefore we are looking for a strategy which would enable us run full DB migration in this period of time (consider that DB size is ~500GB).

First, i'm interested, if DB migration is a custom solution which is client oriented or a general Maximo DB migration sequence which cannot be changed?

Two phase migration (data and subsequent delta sync) seems to be too complex tasks, therefore we consider putting system to maintenance mode -> migrating -> starting system with changed DB. 

Second, what is the way of migrating audit data, where users/system specific configs differ between databases. Should we introduce mapping and keep original data or migrate data with introduced data changes?

Third, migration of other DB objects (functions/procedures to functions/procedures/ other objects). What is the best option for this code migrations? 

Fourth, what is the best way of testing migrated data and other DB objects? For tables, i see that row count/hashsum of tables. 
Does it make implementing migration of partial data and afterwards using scripts to run full data comparison?

Fifth, data in Oracle DB split over tablespaces. I know MAXDATA and MAXINDEX do exist. Due to maintenance questions, backups, other reasons, we are trying to see if there are benefits of introducing new tablespaces?

Looking forward to getting some details on respective points.

/saulius

------------------------------
Saulius S
------------------------------

Database conversions are a complex task that is very difficult without someone with deep expertise in Maximo as well as the underlying database platforms. There are companies that offer this as a service, and I would strongly recommend reaching out to them before deciding to do it yourself.

Some examples (not even close to an exhaustive list) of challenges you'll have:

1) SQL Server is case insensitive by default. This means in a table you may have abc123 and ABC123 and SQL Server queries will treat them as identical. In Oracle it will be case sensitive, so you need to correct the data, or you'll encounter unexpected problems using the application. 
2) Until MAS, Maximo does not use sequences on SQL Server. Maximo uses Oracle sequences, so you'll need to create these sequences with the proper values based on the maxsequences table.
3) Maximo uses a special column type on SQL Server to have rowstamp update when records are updated. Maximo uses database triggers on Oracle to maintain the column so you'll need to create these database triggers for the tables
4) Maximo defines various settings for database specific settings like the mxe.db.systemdateformat system property for using getdate() on SQL Server or sysdate on Oracle that will need to be updated. 
5) All KPIs, saved queries, custom reports, relationships, conditions, escalations, etc. would need to be evaluated and potentially updated for the new platform
6) Some tables in Maximo contain encrypted values like endpoints (integrations), user passwords, etc. These values cannot be migrated from the old platform to the new platform which means they need to be reset after the migration
7) Maximo uses SQL Server columns such as IMAGE (IMGLIB table), TEXT (think CLOB), VARBINARY (BLOB), etc. that are sometimes difficult to migrate depending on the tools you utilize. 

Needless to say, it's not an easy project.


Maximo doesn't officially support functions/procedures at the database level (other than the ones it needs like the rowstamp topic above) so if you have these, they'll need to be evaluated, updated, and migrated. I would use this opportunity to see if they can be moved into automation scripts or something else rather than depending on database procedures. Especially if these are modifying records, you can cause serious problems by not going through the MBO framework. 

Regarding the multiple tablespaces, out of box there are two (MAXDATA & MAXINDEX as you mentioned) but nothing stops you from creating additional tablespaces and Maximo will support it. What Maximo does not support today is table partitioning where a single table (such as WORKORDER) is separated based on some criteria (SITEID, dates, etc.). I know some customers have done it but there are scenarios where Maximo needs to recreate the table during a CONFIGDB/UPDATEDB and those changes can be lost.

Hi to all,

i'm working with a company, which is planning migration from MS SQL to Oracle (only DB part, considering that application remains the same).
I have tried searching for recommendations, what is the easiest + fastest solution for such task.
Business accepts downtime of existing system's downtime for max 2 days, therefore we are looking for a strategy which would enable us run full DB migration in this period of time (consider that DB size is ~500GB).

First, i'm interested, if DB migration is a custom solution which is client oriented or a general Maximo DB migration sequence which cannot be changed?

Two phase migration (data and subsequent delta sync) seems to be too complex tasks, therefore we consider putting system to maintenance mode -> migrating -> starting system with changed DB. 

Second, what is the way of migrating audit data, where users/system specific configs differ between databases. Should we introduce mapping and keep original data or migrate data with introduced data changes?

Third, migration of other DB objects (functions/procedures to functions/procedures/ other objects). What is the best option for this code migrations? 

Fourth, what is the best way of testing migrated data and other DB objects? For tables, i see that row count/hashsum of tables. 
Does it make implementing migration of partial data and afterwards using scripts to run full data comparison?

Fifth, data in Oracle DB split over tablespaces. I know MAXDATA and MAXINDEX do exist. Due to maintenance questions, backups, other reasons, we are trying to see if there are benefits of introducing new tablespaces?

Looking forward to getting some details on respective points.

/saulius

------------------------------
Saulius S
------------------------------

Thanks Steven for your response.

Considering what you provided "use company for migration" approach is probably what we will have to consider.

In addition as you wrote - Some examples (not even close to an exhaustive list) of challenges you'll have - is this something standard or depends on client and solution?

Speaking about data migration, i see that there is a need to do initial analysis (what to migrate/what to skip) and execute post migration checks - are there any "guidelines" or this is based on every company? Considering that company A could apply totally different strategy compared with company B, this could have totally different time frames of migration duration.

Lastly, are there any recommendations from IBM Maximo, on migration steps (guidelines) or migration tools?

/saulius

Database conversions are a complex task that is very difficult without someone with deep expertise in Maximo as well as the underlying database platforms. There are companies that offer this as a service, and I would strongly recommend reaching out to them before deciding to do it yourself.

Some examples (not even close to an exhaustive list) of challenges you'll have:

1) SQL Server is case insensitive by default. This means in a table you may have abc123 and ABC123 and SQL Server queries will treat them as identical. In Oracle it will be case sensitive, so you need to correct the data, or you'll encounter unexpected problems using the application. 
2) Until MAS, Maximo does not use sequences on SQL Server. Maximo uses Oracle sequences, so you'll need to create these sequences with the proper values based on the maxsequences table.
3) Maximo uses a special column type on SQL Server to have rowstamp update when records are updated. Maximo uses database triggers on Oracle to maintain the column so you'll need to create these database triggers for the tables
4) Maximo defines various settings for database specific settings like the mxe.db.systemdateformat system property for using getdate() on SQL Server or sysdate on Oracle that will need to be updated. 
5) All KPIs, saved queries, custom reports, relationships, conditions, escalations, etc. would need to be evaluated and potentially updated for the new platform
6) Some tables in Maximo contain encrypted values like endpoints (integrations), user passwords, etc. These values cannot be migrated from the old platform to the new platform which means they need to be reset after the migration
7) Maximo uses SQL Server columns such as IMAGE (IMGLIB table), TEXT (think CLOB), VARBINARY (BLOB), etc. that are sometimes difficult to migrate depending on the tools you utilize. 

Needless to say, it's not an easy project.


Maximo doesn't officially support functions/procedures at the database level (other than the ones it needs like the rowstamp topic above) so if you have these, they'll need to be evaluated, updated, and migrated. I would use this opportunity to see if they can be moved into automation scripts or something else rather than depending on database procedures. Especially if these are modifying records, you can cause serious problems by not going through the MBO framework. 

Regarding the multiple tablespaces, out of box there are two (MAXDATA & MAXINDEX as you mentioned) but nothing stops you from creating additional tablespaces and Maximo will support it. What Maximo does not support today is table partitioning where a single table (such as WORKORDER) is separated based on some criteria (SITEID, dates, etc.). I know some customers have done it but there are scenarios where Maximo needs to recreate the table during a CONFIGDB/UPDATEDB and those changes can be lost.

Hi to all,

i'm working with a company, which is planning migration from MS SQL to Oracle (only DB part, considering that application remains the same).
I have tried searching for recommendations, what is the easiest + fastest solution for such task.
Business accepts downtime of existing system's downtime for max 2 days, therefore we are looking for a strategy which would enable us run full DB migration in this period of time (consider that DB size is ~500GB).

First, i'm interested, if DB migration is a custom solution which is client oriented or a general Maximo DB migration sequence which cannot be changed?

Two phase migration (data and subsequent delta sync) seems to be too complex tasks, therefore we consider putting system to maintenance mode -> migrating -> starting system with changed DB. 

Second, what is the way of migrating audit data, where users/system specific configs differ between databases. Should we introduce mapping and keep original data or migrate data with introduced data changes?

Third, migration of other DB objects (functions/procedures to functions/procedures/ other objects). What is the best option for this code migrations? 

Fourth, what is the best way of testing migrated data and other DB objects? For tables, i see that row count/hashsum of tables. 
Does it make implementing migration of partial data and afterwards using scripts to run full data comparison?

Fifth, data in Oracle DB split over tablespaces. I know MAXDATA and MAXINDEX do exist. Due to maintenance questions, backups, other reasons, we are trying to see if there are benefits of introducing new tablespaces?

Looking forward to getting some details on respective points.

/saulius

------------------------------
Saulius S
------------------------------