Follow

Database migration/ conversion.

Often, for a number of reasons , it may be necessary to move data from one database vendor to another, or to upgrade the version of database software being used.

The most trivial case of Database migration is illustrated below.

The manual database migration, especially for heterogeneous databases is a difficult, expensive and time-consuming task.

Actually modern database engines have a similar set of capabilities. But their data types, internal structure and SQL syntax are not identical. The aim of automated database migration software is to convert database structures, relations and data from source db to target correctly to preserve data integrity.

Specific data types may be absent in target database or their length and precision may be different for various databases. So mapping corresponding data type equivalents between source and target databases accordingly is another job for database migration tool.

Database views cannot be migrated as is. The idea of our software is to translate them from one SQL dialect to another automatically with know SQL knowledge.
Here are the sample views converted with our migration tools.

MySQL View

     SELECT 
                `InstanceName`,
                `ReportID`,
                `UserName`,
                CASE
                RequestType 
                WHEN 1 
                THEN CONVERT('1', BIT) 
                ELSE CONVERT('0', BIT) 
                END AS `RequestType`,
                `Format`,
                `Parameters`,
                `TimeStart`,
                `TimeEnd`,
                `TimeDataRetrieval`,
                `TimeProcessing`,
                `TimeRendering`,
                CASE
                Source 
                WHEN 6 
                THEN 3 
                ELSE Source 
                END AS `Source`,
                `Status`,
                `ByteCount`,
                `RowCount` 
                 FROM
                `ExecutionLogStorage`

            

MS SQL Server View

     SELECT 
                [InstanceName],
                [ReportID],
                [UserName],
                CASE
                ([RequestType]) 
                WHEN 1 
                THEN CONVERT(BIT, 1) 
                ELSE CONVERT(BIT, 0) 
                END AS [ RequestType ],
                [FORMAT],
                [Parameters],
                [TimeStart],
                [TimeEnd],
                [TimeDataRetrieval],
                [TimeProcessing],
                [TimeRendering],
                CASE
                ([Source]) 
                WHEN 6 
                THEN 3 
                ELSE [Source] 
                END AS Source,
                [status],
                [ByteCount],
                [RowCount] 
                FROM
                [ExecutionLogStorage] WITH (NOLOCK) 
                WHERE [ReportAction] = 1 


Find more information about Views and queries translation

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk