Tags: database conversion errors
One of the world’s largest information technology companies decided to migrate their Oracle database to SQL Server. They leveraged SQL Server Migration Assistant (SSMA) for Oracle to automate the major part of their migration effort. However, their source database code included over 400 hierarchical queries that SSMA could not convert. Neither could the customer’s technical staff. So, they turned to DB Best asking for assistance in converting a number of Oracle database schemas.
We helped our customer to solve this complicated task by converting the required database schemas within the short period of time. Watch the following video to learn more about the approach we’ve used to convert the hierarchical queries from Oracle to SQL Server.
Original Customer’s System
Our customer was using Oracle database with a significant number of schemas. By the time they turned to us, they were already engaged in the database migration to SQL Server. Their Oracle license was about to expire, so they needed to move to the new database environment just in time.
At the very beginning of the database migration, they successfully utilized the SQL Server Migration Assistant to automate the conversion of their source database schemas. However, some of the original schemas included hierarchical queries with the ‘nocycle’ clause. Check the example of the hierarchical query with the ‘nocycle’ clause below.
1 2 3 4 5 | SELECT rt.id, parent_id FROM TEST_ORACLE_MSSQL.T_HIERARCHICAL_QUERIES rt CONNECT BY NOCYCLE PRIOR id = parent_id; |
SQL Server Conversion Issue
While building a hierarchical tree, a row may link to one of its parents or even to itself as a child. This results in an endless loop. You can identify this problem only on the data level, but not on the level of the query code. Actually, the customer’s data included a lot of rows that linked to themselves as a child.
If an endless loop occurs during the execution of the ‘connect by’ condition of the query, the Oracle’s ‘nocycle’ clause allows you avoiding this issue. SQL Server supports recursive queries that stand for Oracle’s hierarchical queries. They look as following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | WITH h$cte AS ( SELECT rt.ID, rt.PARENT_ID, 1 AS LEVEL, CAST(ROW_NUMBER() OVER( ORDER BY @@spid) AS VARCHAR(MAX)) AS path FROM TEST_ORACLE_MSSQL.T_HIERARCHICAL_QUERIES AS rt UNION ALL SELECT rt.ID, rt.PARENT_ID, h$cte.LEVEL + 1 AS LEVEL, path + ',' + CAST(ROW_NUMBER() OVER( ORDER BY @@spid) AS VARCHAR(MAX)) AS path FROM TEST_ORACLE_MSSQL.T_HIERARCHICAL_QUERIES AS rt, h$cte WHERE h$cte.ID = rt.PARENT_ID ) SELECT h$cte.ID, h$cte.PARENT_ID FROM h$cte ORDER BY h$cte.path GO |
The problem is that these recursive queries do not include a built-in mechanism for loop detection. That is why SSMA was unable to convert these queries and returned an error ‘The following SQL clause cannot be converted’.
The customer’s technical staff could not convert these queries manually, so, they contacted DB Best in order to get some help with solving the occurred migration issues in a number of database schemas.
We examined the provided customer’s schemas and discovered the original database code included over 400 hierarchical queries. That means that first of all, we needed to develop the right approach to the conversion of such queries.
An Ultimate Solution for Conversion of Hierarchical Queries
First, we developed an algorithm that detects endless loops in real time, just when the query selects the data from the tables. We placed this algorithm into a recursive procedure that now allowed for emulation of the Oracle’s ‘nocycle’ clause. We executed this procedure every time during the execution of the converted recursive query in SQL Server.
Using this approach, we manually converted Oracle’s hierarchical queries to SQL Server’s recursive queries. Then we updated the queries code so that it could now support the results returned by the loop detection procedure.
This allowed us to keep the functionality of the original customer’s solution in the new database environment. Also, this helped our customer complete the database migration right on time before their Oracle’s license expired.
Customer’s Benefits
After completing the database schema conversion, we set up the Database Compare Suite tool to automatically migrate the data and verify the migration. In the end, the customer received the following benefits:
- Completed a tough database migration project that included over 400 hierarchical queries that were not automatically convertible;
- Delivered the project on time despite some conversion difficulties;
- Maintaining high performance indicators of the customer’s system in the new environment.
Moreover, we now have a ready-to-use methodology of converting Oracle’s hierarchical queries to SQL Server. This allows for optimizing our future migration projects for potential customers like you. Feel free to contact DB Best to complete your database migration project. Make sure to check out our new Jumpstart for Oracle migration offer!
