KR102125010B1 - System and method for analyzing database migration - Google Patents

Abstract

The present invention relates to a system for analyzing a database migration, which is capable of significantly reducing the analysis cost and time required to perform a database migration project. The system for analyzing a database migration includes a SQL extraction unit for extracting structured query language (SQL) information from an original database and an original application; an object analysis unit for analyzing an object from the extracted SQL information; a syntax analysis unit for analyzing an SQL statement from the extracted SQL information; and a migration cost calculation unit for calculating man-hours or costs required for database migration by using the analysis result of the object analysis unit and the syntax analysis unit, wherein the SQL extraction unit extracts SQL information from meta information of the original database including information on an internal object of the original database and a source code of the original application, the object analysis unit and the syntax analysis unit classify the object and SQL syntax analyzed from the extracted SQL information into a case where it is officially confirmed that the object and SQL syntax are basically incompatible with a target database or a target application and a case where it is not officially confirmed that the object and SQL syntax are incompatible with a target database or a target application other than basic, and the migration cost calculation unit calculates the man-hours or costs required for database migration by setting weights differently for the case of basic incompatibility and the case of non-compliance not officially confirmed.

Description

System and method for analyzing database migration}

The present invention relates to a system and method for analyzing a database conversion cost, and calculates expected airlifts and costs in a preliminary stage of a database conversion project to stably and efficiently perform a project for converting a source database system to a target database system. Plan review for management of these projects can be made.

In the early stages of application conversion and database migration projects that are involved in the introduction of database changes, estimated costs are calculated and plans for managing these projects need to be performed in a short period of time. It is performed in the form of inquiries, collection, review, and reporting by hand.

In addition, when evaluating the conversion of multiple database systems in a short period of time, there is a high possibility that an error will occur in calculating the cost of the project due to the inconsistent standard of each other due to the variation in the ability of each manpower used for the evaluation.

In addition, when evaluating the conversion of the database system, it is highly likely that an error will occur because the evaluation cannot be performed in the same simulation environment as the target database.

In addition, it is difficult to secure the reliability of the results because the actual experience of application conversion and database migration project is not accumulated in the database.

Registered Patent Publication No. 10-0928382 relates to a migration apparatus and method for converting a database of a mainframe system to a database suitable for an open system, and minimizes errors due to a code system and more quickly uses a database used in the mainframe system. It includes a code conversion module to convert to a database suitable for an open system, but it does not show how the code system is investigated beforehand, so it is possible to calculate the expected cost in advance and review the plan for the management of these projects. There is a problem that it is difficult to achieve.

Japanese Unexamined Patent Publication No. 2009-223851 relates to a method of migrating a database system, analyzes an original database system to generate a table for conversion, and then performs migration of the database system using the table for conversion, and for the conversion The table is repeatedly updated to prevent conversion errors, but it does not show how to investigate the code system in advance, so it calculates the expected cost in the preliminary stage and reviews plans for management of these projects. There is a problem that it is difficult to lose.

Japanese Patent Application Publication No. 2017-162152 relates to a data migration system and a control method, in which a source system and a target system are operated in parallel in a system that manages customer data in tenant units in each customer-only space, step by step in tenant units In the case of performing database migration, it is prevented from updating data of the tenant's original system by controlling not to perform batch processing such as data aggregation, but it does not show how the investigation of the code system is done in advance. There is a problem in that it is difficult to calculate an expected cost in a preliminary stage and to review a plan for managing such a project.

Japanese Patent Application Publication No. 2018-109898 relates to a data migration system, and records data items of the structured database and tables of the relational database so that database migration can be systematically and step-by-step when the structured database system is converted to a relational database system. Although the and fields are maintained, there is a problem in that it is difficult to calculate the expected cost in the preliminary stage and to review the plan for the management of these projects because it does not show how the investigation of the code system is performed in advance.

Registered Patent Publication No. 10-0928382 Japanese Patent Application Publication No. 2009-223851 Japanese Patent Application Publication No. 2017-162152 Japanese Patent Application Publication No. 2018-109898

The present invention calculates expected airlifts and costs in the preliminary stages of performing the database conversion project so that the project for converting the source database system to the target database system can be performed stably and efficiently, and a plan review for the management of these projects can be made. It is aimed at making it possible.

In addition, another object of the present invention is to make it possible to significantly reduce the analysis cost and time required to perform the database conversion project.

In addition, another object of the present invention, to apply a database-based rules when evaluating to perform a database conversion project to enable the performance of evaluation work of a certain quality.

Another object of the present invention is to provide a basis for maintaining quality by promptly responding to an environment change for database conversion by immediately reflecting changes and additions of rules for database conversion to the rule database.

In addition, another object of the present invention is to provide a basis for responding to the change in airborne due to the deviation in the ability of the project execution personnel by allowing knowledge to be shared for database conversion.

The problems to be solved by the present invention are not limited to the above-mentioned purposes only, and other technical problems not explicitly indicated above are easily understood by those of ordinary skill in the art to which the present invention pertains through the configuration and operation of the present invention. Will be able to.

In this invention, the following structures are included in order to solve the said subject.

The present invention relates to a database conversion analysis system, a SQL extraction unit for extracting SQL (Structured Query Language) information from the original database and the original application; An object analysis unit analyzing an object from the extracted SQL information; A parsing unit parsing the SQL syntax from the extracted SQL information; The object analysis unit and the parsing unit using the analysis results of the conversion cost calculation to calculate the required airborne or cost for the database conversion; includes, The SQL extraction unit includes information about the internal object (object) of the original database SQL information is extracted from the source information of the original application and the meta information of the original database, and the object analysis unit and the classification analysis unit are analyzed by the object and SQL syntax from the extracted SQL information. Or, if it is incompatible with the target application, it is divided into a basic incompatible case that is officially confirmed and a non-basically incompatible case that is not officially confirmed. It is characterized by setting the weights differently for incompatible cases other than the basic, so that the airlift or cost required for database conversion can be calculated.

When the object analyzed from the extracted SQL information is incompatible with the target database, the object analysis unit first registers a basic incompatible object and adds a non-default incompatible object. It is characterized in that it is possible to update non-basic non-compatible objects.

The conversion cost estimator of the present invention sets the weight differently according to at least one of the length of the SQL syntax, the number of join (JOIN) reference tables, and the number of sub-query implementations for the SQL syntax analyzed from the extracted SQL information. It is characterized by being able to calculate the airlift or cost required for conversion.

The conversion cost calculation unit of the present invention is characterized in that it is possible to calculate the required air or cost for database conversion by setting different weights according to the query type for the SQL syntax analyzed from the extracted SQL information.

The SQL extracting unit of the present invention is characterized in that it extracts SQL information according to a rule that is officially confirmed, or extracts SQL information by inferring a code for determining a starting keyword sentence of SQL information when there is no officially confirmed rule. .

In addition, the present invention relates to a database conversion analysis method, extracting SQL (Structured Query Language) information from the original database and the original application in the SQL extraction unit; Analyzing an object from the extracted SQL information in an object analysis unit; Parsing the SQL syntax from the extracted SQL information in the parsing unit; In the conversion cost calculation unit using the analysis results of the object analysis unit and the parsing unit to calculate the required number or cost for the database conversion; includes, The SQL extraction unit includes information about the internal object of the original database Extracting the SQL information from the source information of the original application and the meta information of the original database, and the object analysis unit and the classification analysis unit are analyzed by the object (object) and SQL syntax from the extracted SQL information target database or In the case of incompatibility with the target application, it is divided into basic incompatible cases that are officially confirmed and non-basically incompatible cases that are not officially confirmed. Other than that, it is characterized by setting the weights differently for incompatible cases so that the airlift or cost required for database conversion can be calculated.

In addition, the present invention may be a computer program stored in a medium to execute the database conversion analysis method.

The effect of the present invention is to calculate the expected airlift and cost in the preliminary stages of performing the database conversion project so that the project of converting the source database system to the target database system can be performed stably and efficiently. To make it happen.

In addition, another effect of the present invention is to significantly reduce the analysis cost and time required to perform the database conversion project.

In addition, another effect of the present invention is to make it possible to perform evaluation work of a constant quality by applying a database-based rule when evaluating a database conversion project.

In addition, another effect of the present invention is to provide a basis for maintaining quality by promptly responding to changes in the environment for database conversion by immediately reflecting changes and additions of rules for database conversion to the rules database.

In addition, another effect of the present invention is to provide a basis for responding to a change in airborne due to a deviation in the ability of a project execution manpower by allowing knowledge to be shared for database conversion.

The effects of the present invention are not limited to the above effects, and other effects not explicitly indicated above will be easily understood by those of ordinary skill in the art to which the present invention pertains through the configuration and operation of the present invention.

1 shows a schematic diagram of an overall configuration including a database conversion analysis system, a database conversion project execution system, and a database operation management system of the present invention.
2 shows a detailed internal configuration diagram of the database conversion analysis system and the database conversion project execution system of the present invention.
Figure 3 shows a specific flow chart of the database conversion analysis method of the present invention.

Hereinafter, the overall configuration and operation according to a preferred embodiment of the present invention will be described. These examples are exemplary, and do not limit the configuration and operation of the present invention, and other configurations and operations not explicitly indicated in the embodiments will also be described in the art to which the present invention pertains through the embodiments of the present invention. If the person having the understanding can easily understand it will be seen as a technical idea of the present invention.

There are several types of objects in the database, such as tables, views, indexes, etc. In the source database, there may be incompatible objects that are incompatible with the target database, Oracle, Microsoft, etc. It is common practice for database system providers to publish official documents to announce compatible and incompatible objects.

On the other hand, the inventors of the present invention have found that there is a difference from the actual when performing a database conversion project with respect to compatible objects and incompatible objects known in the official document of the provider. It is known as an incompatible object in the official document, and in fact, in the case of an incompatible object, it is classified as a basic incompatible object, and it is known as a compatible object in the official document, but is actually an incompatible object. In the case of (object), it is classified as a non-basic object.

Structured Query Language (SQL) is adopted as a standard in many database-related programs, and SQL is included in database meta information and the source code of an application that directly or indirectly manipulates the database.

The database meta information generally includes a procedure, function code, and data definition language (DDL). SQL is included in the procedure and function code. DDL is a kind of SQL, from which objects in the database can be extracted and analyzed.

It is common to include SQL in the source code of the application based on rules notified by the provider of the application, and include SQL in the source code of the application even if the rules are not announced by the provider.

Even in the case of SQL syntax, the source database may have incompatible SQL syntax that is incompatible with the target database. Providers of database systems such as Oracle and Microsoft publish official documents to announce compatible and incompatible SQL syntax. It is common.

In this regard, the inventors of the present invention have found that there is a difference from the actual when performing a database project with respect to the compatible SQL syntax and incompatible SQL syntax known in the official document of the provider. Known as SQL syntax, and if it is actually incompatible SQL syntax, it is classified as SQL syntax having basic difficulty, and in the official document, it is known as compatible SQL syntax, but when it is actually incompatible SQL syntax, it is classified as SQL syntax having complex difficulty. Doing.

1 shows a schematic diagram of an overall configuration including a database conversion analysis system, a database conversion project execution system, and a database operation management system of the present invention.

Referring to Figure 1, the original database 41 and the original application 42 to the target database 51 and the target application 52 to convert, operate and manage the present invention database (DB: Database) conversion analysis system ( 10), database (DB: Database) conversion project execution system 20, and includes a database (DB: Database) operation management system 30.

In particular, the DB conversion analysis system 10 of the present invention first performs such a project before performing a project of converting the source database 41 and the source application 42 to the target database 51 and the target application 52. Calculate the airlift or cost for the project and analyze the risk. Karate means the number of people required for work or working hours.

The DB conversion project execution system 20 of the present invention can shorten the schedule of actual DB conversion project execution through previously performed karate or cost estimation and risk analysis, and prepares a conversion guide through karate or cost estimation and risk analysis As a result, standardized rules can be provided and work efficiency can be pursued.

In addition, the DB operation management system 30 of the present invention enables stable use of DB and pursues a simplified operation after performing the DB conversion project by the DB conversion analysis system 10 and the DB conversion project execution system 20. do.

2 shows a detailed internal configuration diagram of the database conversion analysis system and the database conversion project execution system of the present invention.

Referring to FIG. 2, the database conversion analysis system 10 of the present invention includes an SQL extraction unit 110, an object analysis unit 120, a parsing unit 130, and a conversion cost calculation unit 140. The database conversion project execution system 20 of the present invention includes a conversion guide providing unit 210, a conversion verification execution unit 220, and a conversion progress management unit 230.

The SQL extraction unit 110 includes information about an internal object of the original database 41 to extract structured query language (SQL) information from the original database 41 and the original application 42. SQL information is sensed from the meta information of the original database 41 and the source code of the original application 42.

The meta information of the original database 41 generally includes a procedure, function code, and data definition language (DDL) information. The procedure, function Since SQL information is included in the code and DDL is a type of SQL, it is possible to detect SQL information from meta information of the original database 41.

SQL information may be included in the source code of the original application 42 based on a rule announced by a provider, and SQL information is included in the source code of the original application 42 even if the rule is not announced by the provider It is common.

The object analysis unit 120 may analyze an object from SQL information extracted from the database meta information, and the object is incompatible with the target database 51 or the target application 52 In this case, the basic incompatibilities that are officially confirmed and the non-basic incompatibilities that are not officially confirmed are classified.

Eventually, by setting the weight given to the non-basic non-compatible object larger than the weight given to the basic non-compatible object, the required karate or non-basic non-compatible object than the basic non-compatible object You can make it costly.

The parsing unit 130 is a basic incompatible case that is officially confirmed when the SQL syntax analyzed from the extracted SQL information is incompatible with the target database 51 or the target application 52 and is not officially confirmed. It can be classified into non-basic non-compatible cases, and it is possible to calculate the required airlift or cost for non-basic non-basic SQL statements rather than basic non-compatible SQL statements.

Also, when SQL information is extracted from the original database 41 and the original application 42, the extracted SQL information is stored including path information, file name, SQL ID, and the like, and incompatible objects are stored in the number. Accordingly, it may be stored as an incompatible object name, and the SQL history used for the incompatible object may also be stored. Also, the extracted SQL information can be separated for each SQL statement and stored on a file-by-file basis for easy post-processing.

The conversion cost calculation unit 140 calculates the airlift or cost required for database conversion by setting different weights for the basic incompatibility and the non-basic incompatibility that is not officially confirmed.

In addition, the conversion cost calculator 140 may calculate the complexity of the SQL statement and set different weights according to the complexity. The length of the SQL statement, the number of reference tables in the SQL statement, and the number of implementations of subqueries included in the SQL statement And so on.

For example, the length of the SQL statement is usually 250 bytes, and the length of the SQL statement is very long, sometimes in the range of 3,000 to 4,000 bytes. If the length of the SQL statement is long, the weight is set high to increase the complexity. In addition, as the number of reference tables in the SQL statement increases, and as the number of implementations of subqueries increases, the weight can be set high to increase complexity.

In addition, the weights can be set differently for each type of query. Online transaction processing (OLTP) queries, online analytical processing (OLAP) queries, hierarchical queries, and queries using scalar functions are classified, for example, for OLTP queries. By setting the basic weights and setting the weights high for OLAP queries, hierarchical queries, and queries using scalar functions, the airlift or cost required for database conversion is calculated high.

In addition, when the object analyzed from the extracted SQL is incompatible with the target database, the object analysis unit 120 first registers a basic non-compatible object, and then adds a non-basic non-basic object. It is desirable to make it possible to update the non-basic incompatible object.

The conversion guide providing unit 210 provides the conversion guide based on the incompatible object and the incompatible SQL syntax, and the conversion verification performing unit 220 is compatible with the incompatible object. The function and data are verified by entering parameters for the SQL syntax and executing the query and verifying the execution result, and the conversion progress management unit 230 converts the incompatible object and the incompatible SQL syntax Progress will be managed step-by-step for conversion of files, upload of conversion sources, designation of test staff, and completion of tests.

On the other hand, there may be a case where it is not possible to switch from the source database 41 and the source application 42 to the target database 51 and the target application 52. In this case, the effort and cost to adjust the syntax or devise an alternative syntax Try to calculate.

In addition, an additional evaluation can be performed on the part that cannot be converted. In the additional evaluation, SQL statements are simulated in the target database 51 to store the correction values according to the additional evaluation and to reflect them when calculating the cost and cost. .

Figure 3 shows a specific flow chart of the database conversion analysis method of the present invention.

Referring to Figure 3, the database conversion analysis method of the present invention first performs a step (S100) of extracting SQL (Structured Query Language) information from the original database 41 and the original application 42 in the SQL extraction unit 110 do.

The SQL extraction unit 110 includes information about an internal object of the original database 41 to extract structured query language (SQL) information from the original database 41 and the original application 42. SQL information is sensed from the meta information of the original database 41 and the source code of the original application 42.

The meta information of the original database 41 generally includes a procedure, function code, and data definition language (DDL) information. The procedure, function Since SQL information is included in the code and DDL is a type of SQL, it is possible to detect SQL information from meta information of the original database 41.

SQL information may be included in the source code of the original application 42 based on a rule announced by a provider, and SQL information is included in the source code of the original application 42 even if the rule is not announced by the provider It is common.

Next, the object analysis unit 120 analyzes an object from the extracted SQL information (S200).

The object analysis unit 120 may analyze an object from SQL information extracted from the database meta information, and the object is incompatible with the target database 51 or the target application 52 In this case, the basic incompatibilities that are officially confirmed and the non-basic incompatibilities that are not officially confirmed are classified.

Eventually, by setting the weight given to the non-basic non-compatible object larger than the weight given to the basic non-compatible object, the required karate or non-basic non-compatible object than the basic non-compatible object You can make it costly.

Next, the parsing unit 130 analyzes the SQL syntax from the extracted SQL information (S300).

The parsing unit 130 is a basic incompatible case that is officially confirmed when the SQL syntax analyzed from the extracted SQL information is incompatible with the target database 51 or the target application 52 and is not officially confirmed. It can be classified into non-basic non-compatible cases, and it is possible to calculate the required airlift or cost for non-basic non-basic SQL statements rather than basic non-compatible SQL statements.

Next, the conversion cost calculating unit 140 performs the step of calculating the airlift or cost required for the database conversion using the analysis results of the object analysis unit and the syntax analysis unit.

The conversion cost calculation unit 140 calculates the airlift or cost required for database conversion by setting different weights for the basic incompatibility and the non-basic incompatibility that is not officially confirmed.

In addition, the conversion cost calculator 140 may calculate the complexity of the SQL statement and set different weights according to the complexity. The length of the SQL statement, the number of reference tables in the SQL statement, and the number of implementations of subqueries included in the SQL statement And so on.

For example, the length of the SQL statement is usually 250 bytes, and the length of the SQL statement is very long, sometimes in the range of 3,000 to 4,000 bytes. If the length of the SQL statement is long, the weight is set high to increase the complexity. In addition, as the number of reference tables in the SQL statement increases, and as the number of implementations of subqueries increases, the weight can be set high to increase complexity.

In addition, the weights can be set differently for each type of query. Online transaction processing (OLTP) queries, online analytical processing (OLAP) queries, hierarchical queries, and queries using scalar functions are classified, for example, for OLTP queries. By setting the basic weights and setting the weights high for OLAP queries, hierarchical queries, and queries using scalar functions, the airlift or cost required for database conversion is calculated high.

In addition, the database conversion analysis method may be in the form of a computer program to be stored on a storage medium and executed.

10: DB conversion analysis system
20: DB conversion project execution system
30: DB operation management system
41: Source database
42: original application
51: target database
52: target application
110: SQL extraction unit
120: object analysis unit
130: parsing unit
140: Conversion cost calculation
210: Conversion guide provider
220: conversion verification execution unit
230: transition progress management

Claims (7)

In the database conversion analysis system,
A SQL extraction unit that extracts structured query language (SQL) information from the original database and the original application;
An object analysis unit for analyzing an object from the extracted SQL information;
A parsing unit parsing the SQL syntax from the extracted SQL information;
It includes; the conversion cost calculation unit for calculating the required air or cost for the database conversion using the analysis results of the object analysis unit and the parsing unit;
The SQL extracting unit extracts SQL information from the meta-information of the original database and the source code of the original application, which includes information about an internal object of the original database,
The object analysis unit and the parsing unit are officially confirmed when the object and SQL syntax analyzed from the extracted SQL information are incompatible with the target database or target application. Non-basic non-compliant cases-Basic non-compatible cases are known as incompatible cases by the provider of the database system, and non-basic non-compatible cases are known as compatible cases, but are actually incompatible cases. It is a case of discrimination-
The conversion cost calculation unit sets the weights differently for the basic incompatibility case and the non-basic incompatibility case which is not officially confirmed, so that it is possible to calculate the airlift or cost required for the database conversion before performing the database conversion project. Database conversion analysis system.
According to claim 1,
When the object analyzed from the extracted SQL information is incompatible with the target database, the object analysis unit first registers a basic incompatible object and adds a non-default incompatible object. Database conversion analysis system characterized in that it is possible to update for non-basic non-compatible objects.
According to claim 1,
The conversion cost estimator needs to set the weight differently according to at least one of the length of the SQL syntax, the number of join (JOIN) reference tables, and the number of sub-query implementations for the SQL syntax analyzed from the extracted SQL information. Database conversion analysis system, characterized in that it is possible to calculate the airborne or cost.
According to claim 1,
The conversion cost estimator is a database conversion analysis system, characterized in that to set the weight according to the query type for the SQL syntax analyzed from the extracted SQL information differently to calculate the required air or cost for the database conversion.
According to claim 1,
The SQL extracting unit extracts SQL information according to officially confirmed rules or, if there is no officially confirmed rule, searches for a code to determine the starting keyword sentence of SQL information and extracts SQL information. system.
In the database conversion analysis method,
Extracting structured query language (SQL) information from the original database and the original application in the SQL extraction unit;
Analyzing an object from the extracted SQL information in an object analysis unit; Parsing the SQL syntax from the extracted SQL information in the parsing unit;
Including the conversion cost calculation step of calculating the karate or cost required for the database conversion using the analysis results of the object analysis unit and the parser;
The SQL extraction unit extracts SQL information from the source information of the original application and the meta information of the original database, which contains information about the internal object of the original database,
The object analysis unit and the parsing unit are officially confirmed when the object and SQL syntax analyzed from the extracted SQL information are incompatible with the target database or target application. Non-basic non-compliant cases-Basic non-compatible cases are known as incompatible cases by the provider of the database system. Non-basic non-compatible cases are known as compatible cases, but are actually incompatible cases. It is a case of discrimination-
The conversion cost calculation unit sets the weights differently for basic incompatibility and non-basic incompatibility cases that are not officially confirmed, so that it is possible to calculate the airlift or cost required for database conversion before performing the database conversion project. Database conversion analysis method.
A computer program stored in a medium to execute the method for analyzing a database conversion of claim 6.

Images