JP2019211805A - Database migration support system and program - Google Patents

Abstract

To enable, in migration of a database, efficient operation confirmation at a migration destination when using the same SQL as that used at a migration source.SOLUTION: A system 10 for supporting migration of a database includes a database language input unit 11 that inputs a database language implemented in a database management system, a structure diagram generation unit 12 that decomposes a character string of the database language input at the database language input unit 11, replaces it with a predetermined image, and generates a structure diagram of the database language, a convergence processing unit 13 that retrieves whether there are ones having the same structure in the structure diagram generated by the structure diagram generation unit 12, and if so, discards one of them to thereby converge the structure diagram, and a representative pattern generation unit 15 that replaces the structure diagram converged by the convergence processing unit 13 with the original character string and generates a representative database language which is patterned to a minimum number.SELECTED DRAWING: Figure 1

Description

  The present invention relates to a support technology for managing and operating a database, and more particularly, to a database migration support system and program for efficiently performing database migration (migration / conversion).

The database is migrated or switched from the current system to the new system for reasons such as product changes, function expansion / reduction, and hard disk resource replacement.
Various support systems have been proposed so far in order to perform database migration efficiently while guaranteeing the same processing and functions as the current system in the migration destination system during such database migration. .

For example, in Patent Document 1, based on the operation of the operator via the GUI, the migration destination SQL corresponding to the migration source SQL (Database Language: Structured Query Language) is automatically generated, so that the operator becomes familiar with it. A database migration method that does not depend on degree and has excellent versatility and operability has been proposed.
Patent Document 2 discloses a data conversion method for automatically extracting a SQL sentence that has not been properly converted by specifying a mismatched SQL sentence in the case of converting a migration source SQL to a migration destination SQL. Has been proposed.

JP 2002-351710 A JP 2011-258002 A

However, the techniques disclosed in Patent Document 1 and Patent Document 2 are based on the premise that a new SQL is generated for the new system at the migration destination, and the SQL of the system currently operating at the migration source is migrated. There are no assumptions about verification or operation assurance issues when applied and issued first.
When a database is migrated to a new system, a database management system (DBMS: DATABASE MANAGEMENT SYSTEM) that includes hardware resources for recording data introduces a new product or the like at the migration destination, but the database language ( As for (SQL), the SQL used at the migration source is used as it is. In particular, in a large-scale database, it is very impractical to create a new SQL corresponding to the new system from scratch because it takes much time and cost.

On the other hand, when the migration source SQL is directly applied to the migration destination, the operation of the migration destination may not be correctly secured.
In the database, a “brain” portion called a DBMS optimizer interprets SQL and generates an “execution plan (plan)” for acquiring optimal data from a data table logically recorded on the hard disk.
However, the optimizer's maturity level may vary depending on the product. For example, if the complex SQL used at the migration source is applied to the migration destination as it is, an "wrong result" that is different from the original is returned. It can happen. In other words, even with the same SQL, there may be a product that can return a correct result depending on a product of the DBMS (optimizer) and a product that is not.

If the SQL is complicated, it is composed of, for example, an SQL statement having more than 50 lines. In a large-scale system, for example, nearly 100,000 such SQLs may be implemented.
It is very time-consuming and time-consuming to verify such a large amount of SQL efficiently and reliably, and it takes time and effort. In reality, it is difficult or impossible to test and verify all SQL. is there.
Conventional techniques capable of effectively dealing with problems such as operation guarantee and verification when the same SQL is used at the migration source and the migration destination, including the above-described Patent Documents 1 and 2, have not been proposed.

  In order to solve the above-described problems, the present invention provides a database migration support system and program capable of performing an efficient operation check when a database is migrated using the same SQL as the migration source at the migration destination. The purpose is to provide.

  In order to achieve the above object, the present invention is a system for supporting database migration, which is implemented in a database management system and is a database language input unit for inputting a database language sentence created in a predetermined database language, A structure diagram is provided that includes a structure diagram creation unit that decomposes a character string of the database language sentence input by the database language input unit and replaces it with a predetermined image to create a structure diagram of the database language.

Further, the present invention can be configured as a program executed by the database migration support system according to the present invention as described above.
Furthermore, the present invention can also be implemented as a method that can be implemented by the database migration support system and program according to the present invention as described above.

  According to the present invention, when the database is migrated, the SQL is structured and patterned, so that when the same SQL as the migration source is used at the migration destination, an efficient operation check can be performed. .

1 is a functional block diagram schematically showing an overall configuration of a database migration support system (hereinafter referred to as “the present system”) according to an embodiment of the present invention. It is explanatory drawing which shows typically the algorithm of SQL patterning in this system. It is explanatory drawing which shows typically the algorithm of the SQL convergence process in patterning. It is explanatory drawing which shows typically the algorithm of the test process of the converged SQL. It is explanatory drawing which shows typically the algorithm of the evolution process of the patterned SQL. It is explanatory drawing which shows an example of the report output which shows the test result of the evolved SQL. It is explanatory drawing which shows an example of the report output which shows the test result of SQL evolved similarly. It is explanatory drawing which shows an example of the report output which changed the display order of the test result of SQL. It is explanatory drawing which shows typically the algorithm of the degeneration process of the patterned SQL. It is explanatory drawing which shows an example of the report output which shows the test result of degenerated SQL. It is explanatory drawing which shows the structure of the "SELECT" sentence contained in SQL. Similarly, it is an explanatory diagram showing the structure of a “SELECT” sentence. Similarly, it is an explanatory diagram showing the structure of a “SELECT” sentence. FIG. 12 is an explanatory diagram showing a technique for replacing the structure of the “SELECT” sentence shown in FIG. 11 with a predetermined structure diagram. Similarly, it is explanatory drawing in the case of replacing the “SELECT” sentence shown in FIG. 12 with a structure diagram. Similarly, it is explanatory drawing in the case of replacing the “SELECT” sentence shown in FIG. 13 with a structure diagram. It is explanatory drawing in the case of replacing an "INSERT" sentence with a structure figure. Similarly, it is an explanatory diagram in the case of replacing the “INSERT” sentence with a structure diagram. It is explanatory drawing in the case of replacing an "UPDATE" sentence with a structure diagram. It is explanatory drawing in the case of replacing a "DELETE" sentence with a structure drawing. It is explanatory drawing which shows the method of mapping the produced | generated structure diagram, and is a case where a structure diagram is a "SELECT" sentence. Similarly, it is an explanatory diagram when mapping a structure diagram of an “INSERT” sentence. It is explanatory drawing which shows the method of the convergence process of the mapped structure figure. Similarly, it is explanatory drawing which shows the method of the convergence process of a structure drawing. Similarly, it is explanatory drawing which shows the method of the convergence process of a structure drawing. It is explanatory drawing which shows the method of the evolution process of the mapped structure figure. Similarly, it is an explanatory view showing a method of evolution processing of the structure diagram. It is explanatory drawing which shows the method and output result which automatically produce | generate the data corresponding to the structure by which the evolution process was carried out. Similarly, it is explanatory drawing which shows the method and output result of the automatic generation of the data corresponding to the structure by which the evolution process was carried out. It is explanatory drawing which shows the method of outputting and displaying by replacing output data. It is explanatory drawing which shows the method of the degeneration process of the mapped structure figure. Similarly, it is explanatory drawing which shows the method of the degeneration process of a structure figure. It is explanatory drawing which shows the output result of the data corresponding to the structure by which the degeneration process was carried out. Similarly, it is explanatory drawing which shows the output result of the data corresponding to the structure by which the degeneration process was carried out. It is explanatory drawing which shows an example of a special function file. It is a flowchart which shows the process of the operation | movement confirmation process of the transfer origin and transfer destination SQL in this system. It is explanatory drawing which shows an example of the screen display in an operation confirmation process. Similarly, it is explanatory drawing which shows an example of the screen display in an operation confirmation process. 37 is a flowchart of the “representative pattern: creation / placement of SQL file” process of FIG. 36. FIG. 37 is a flowchart of the “representative pattern: input of test data / creation / arrangement of correct answer data” processing of FIG. 36. 37 is a flowchart of the “representative pattern: representative SQL determination” process of FIG. 36. Similarly, it is a flowchart of a “representative pattern: determination of representative SQL” process. Similarly, it is a flowchart of a “representative pattern: determination of representative SQL” process. 37 is a flowchart of the “representative pattern: test execution (current guarantee / evolution / degeneration)” process of FIG. 36. Similarly, it is a flowchart of the “representative pattern: test execution (current guarantee / evolution / degeneration)” process. Similarly, it is a flowchart of the “representative pattern: test execution (current guarantee / evolution / degeneration)” process. Similarly, it is a flowchart of the “representative pattern: test execution (current guarantee / evolution / degeneration)” process. 37 is a flowchart of the “special function: representative SQL determination” process of FIG. 36. 37 is a flowchart of the “special function: test execution” process of FIG. 36. 37 is a flowchart of the “representative pattern / special function: result report creation” process of FIG. 36.

Embodiments of a database migration support system and program according to the present invention will be described below with reference to the drawings.
Here, the database migration support system of the present invention described below is realized by processing, means, and functions executed by a computer in accordance with instructions of a program (software). The program can send commands to each component of the computer to perform the following predetermined processing and functions according to the present invention. That is, each process, means, and function in the present invention are realized by specific means in which a program and a computer cooperate.

Note that all or part of the program is provided by, for example, a magnetic disk, optical disk, semiconductor memory, or any other computer-readable recording medium, and the program read from the recording medium is installed in the computer and executed. The The program can also be loaded and executed directly on a computer through a communication line without using a recording medium.
Moreover, this system can also be comprised with a single information processing apparatus (for example, one personal computer etc.), and can also be comprised with a some information processing apparatus (for example, several server computer group etc.).

[System configuration]
As shown in FIG. 1, this system 10 is a system that supports database migration from a migration source database 100 to a migration destination database 200, and is configured by a predetermined information processing apparatus, for example, one or more server computers. can do.
Although not particularly illustrated, the information processing apparatus constituting the system 10 includes hardware resources such as arithmetic means, storage means, input means, and output means, and software resources such as predetermined applications / data. Needless to say.

The migration source database 100 is an information processing apparatus (currently operating machine) that is currently operating, and includes a database management system (DBMS) that includes hardware resources such as a hard disk in which target data is recorded, and a predetermined data used in and out of data. The database language sentence is created in the database language, and is composed of SQL or the like. The migration source database 100 naturally includes databases that are not currently operating but have been operating normally in the past.
The migration destination database 200 is an information processing apparatus that is scheduled to be newly operated, and includes hardware resources, DBMS, and the like. The migration destination database 200 also includes databases that have been operating in the past.

Here, if the DBMS and hardware configuration of the migration source database 100 and the migration destination database 200 are the same, the migration source SQL is basically used only by migrating the data, so that the operation at the migration destination is basically It is guaranteed and the operation test can be performed in a confirming manner.
Therefore, in the present system 10, particularly when the DBMS and the hardware configuration are different between the migration source database 100 and the migration destination database 200, for example, the database products are different between the migration source and the migration destination, and the matured DBMS optimizer is implemented. When there is a difference in the level, it is suitable for database migration support in a case where the hard disk configuration is different between the migration destination and the migration source between the shared disk type and the distributed disk type.
However, even when the database configuration of the migration source / destination is the same, it is of course possible to apply the present system 10 to check the operation of the migration destination and the extension / change function.

In the present system 10, as shown in FIG. 1, the executed SQL information and data are acquired from the currently operating migration source database 100, and the data is input to the migration destination database 200. Whether the same result is output or whether it operates with the same performance (for example, whether the CPU, the memory usage rate, the processing time, etc. are the same) is checked.
The system 10 can automatically execute the operation confirmation process as described above when the system 10 can communicate with both the migration source database 100 and the migration destination database 200.
Further, when the system 10 is in an environment where communication with the migration source database 100 cannot be performed, for example, when the migration source database and the migration destination database are provided by different system integrators or the like, the SQL / data for confirming the operation is stored as a user or the like. Is manually prepared, the operation confirmation process in the system 10 can be activated.

Here, the SQL operating in the migration source database 100 targeted by the system 10 is complicated, for example, the number of SQL statements including 400 rows or more and the number of usage tables of 30 or more is nearly 100,000. It may be composed of a large amount of character data (source code).
Preparation of operation confirmation data and correct results for SQL of such scale takes a lot of labor and time, and it is difficult or impossible to verify all of the SQL. In reality, it is impossible to check.
In response to these issues, the system 10 patterns the target SQL to enable efficient operation confirmation, thereby realizing a safe and secure database migration and predicting the project construction period. It is possible to prevent a long period of time and quality deterioration.

Specifically, in the present system 10, by inputting the SQL in operation from the migration source database 100, the following three processes of “patterning of SQL”, “evolution test”, and “report output” are largely performed. Is to be executed.
[SQL patterning process]
The SQL is patterned to minimize the number of lines, thereby improving the test efficiency.
Specifically, in the patterning process, the following three processes are executed.
(1) 4 classifications (SELECT, UPDATE, INSERT, DELETE)
This is a process of classifying and extracting SQL according to four basic syntaxes (SELECT, UPDATE, INSERT, and DELETE).
(2) Creation of structure diagram This is a process of converting character data into image data by applying and replacing an image showing a predetermined structure diagram for each classified basic syntax.
(3) Convergence processing This is a process of converging / reducing the entire structure diagram by deleting the same / overlapping structure in the generated structure diagram.

[Evolution test processing]
(1) Test This is a process for executing a test by converting the converged structure diagram into the original SQL character data.
(2) Automatic generation of evolved SQL This is a process of automatically generating an evolved SQL by evolving a converged structural diagram. By testing the evolution SQL, it is possible to perform an evolution test such as application confirmation in anticipation of future future development.
(3) Application test by automatic data amplification corresponding to evolution SQL This is a process of automatically generating virtual data corresponding to evolution SQL, and the evolution test can be executed efficiently.

Report output processing
The test result including the evolution test described above is output processing.
As a result, the operation confirmation in the migration destination database 200 can be visually confirmed on a display screen or the like, and the result of the evolution test can be easily confirmed.

[Function configuration]
In order to execute each processing as described above, the present system 10 includes a database language input unit 11, a structure diagram creation unit 12, a convergence processing unit 13, an evolution / degeneration processing unit 14, a representative pattern, as shown in FIG. The generation unit 15, the representative pattern test execution unit 16, the special function test execution unit 17, and the report output unit 18 are configured to function as respective units.
The database language input unit 11 inputs SQL that is a database language sentence created in a predetermined database language implemented in the DBMS of the migration source database 100.
The structure diagram creation unit 12 decomposes the SQL character string input by the database language input unit 11 and replaces it with a predetermined image to create a structure diagram of the SQL. In the present system 10 capable of converting and creating such SQL into a structure diagram, verification / testing based on representative SQL as shown below can be automatically performed, for example, a system engineer who manages the system, etc. It is possible to reduce the number of tests by checking and checking the structure diagram visually.

The convergence processing unit 13 searches the structural diagram created by the structural diagram creating unit 12 to determine whether or not the same structure is included, and if the same structure is included, discards one to obtain the structural diagram. Astringent.
The evolution / degeneration processing unit 14 adds the structure to the structure diagram converged by the convergence processing unit 13 to evolve, or deletes the structure to degenerate.

The representative pattern generation unit 15 replaces the structural diagram converged by the convergence processing unit 13 with the original character string, and generates a representative SQL (representative database language sentence) patterned to the minimum number.
The representative pattern generation unit 15 replaces the structure diagram evolved or degenerated by the evolution / degeneration processing unit 14 with a character string, and generates a representative SQL.
The representative pattern test execution unit 16 executes a test using predetermined correct answer data for the representative SQL generated by the representative pattern generation unit 15.
In this system 10, after the SQL is converted to the structure diagram, the SQL statement is generated by performing reverse conversion from the structure diagram in order to perform SQL verification and evolution / degeneration. Since it is converted into a diagram, verification and evolution / degeneration can be performed by reading the original SQL recorded without recording the original SQL sentence by recording the original SQL sentence. It is also possible.

The special function test execution unit 17 searches for whether or not a predetermined special function is included in the SQL character string input by the database language input unit 11, and if the predetermined special function is included, A test using predetermined correct answer data is executed for the special function.
The report output unit 18 outputs test results executed by the representative pattern test execution unit 16 and the special function test execution unit 17.
Details of the processing and operation for database migration support executed by the units 11 to 18 of the system 10 as described above will be described in detail below with reference to the drawings.

[algorithm]
First, a specific algorithm (procedure) for realizing the processing / operation in the system 10 will be described with reference to FIGS.
[SQL patterning]
FIG. 2 schematically shows an algorithm for SQL patterning in the system 10.
As shown in the figure, when the SQL of the migration source database 100 is input to the system 10 (database language input unit 11), character data is extracted in units of “1 file: 1 SQL” from the source code constituting the SQL. Each file is sorted according to the SQL syntax type.

Specifically, as shown in FIG. 2, SQL statements are distributed one file at a time to a “SELECT system”, “UPDATE system”, “INSERT system”, and “DELETE system”, and a predetermined SQL storage directory (storage means) ) Is stored. This process can be speeded up by performing the process in parallel for each directory.
Next, the character data of the SQL sentence distributed for each syntax type is subjected to replacement / conversion processing to a predetermined structure diagram for each file (see FIGS. 14 to 20), and a structure diagram file is generated. (Structural drawing creation unit 12).
Then, the generated structural diagram data is converged / reduced by a convergence process using an inclusion relationship (see FIG. 3) described later (convergence processing unit 13), and a “representative pattern” is generated. The original SQL to become. Patterns are formed into representative pattern data composed of converged structure diagrams (representative pattern generation unit 15).

[SQL pattern convergence]
FIG. 3 schematically shows the convergence processing algorithm of the structure diagram as described above.
As shown in the figure, the convergence process is performed according to the inclusion relationship of the structure diagram.
Specifically, when a plurality of structures included in the structure diagram have the same structure, the structure diagram is converged by including (discarding) one of the structures as an overlapping structure.
For example, in the example shown in FIG. 3, two cases of “UNION” structure can be included in three cases of “UNION”.
In this way, focusing on the same structure in the structure diagram, the target structure that can be included (discarded) is selected.

[test]
Then, based on the structural diagram converged as described above, an operation check test can be performed (representative pattern test execution unit 16).
As shown in FIG. 4, the operation check test is performed on the SQL converged and patterned by the structure diagram. As a result, it is only necessary to test the converged / reduced SQL as compared to the original enormous SQL, and correct data indicating the correct result of the test can be handled with less data. On the other hand, if an attempt is made to test the original SQL as it is, it is necessary to prepare correct answer data corresponding to all SQL, and it is actually difficult or impossible to prepare such correct answer data. It is.
The correct answer data of the test can be stored in a table, for example, and prepared in advance in the storage means of the system 10.

[SQL pattern evolution / degeneration]
Then, according to the result of the test processing as described above, the patterned SQL can be evolved / degenerated (evolution / degeneration processing unit 14).
Specifically, first, as a result of the test, when the same correct result as that of the current migration source database 100 is obtained in the migration destination database 200, the SQL can be "evolved". As a result, future development prediction and the like in the migration destination database 200 can be performed.
On the other hand, if an erroneous result is obtained in the migration destination database 200 as a result of the test, the current SQL is “degenerated”. As a result, it is possible to predict and generate an SQL that produces a correct result in the migration destination database 200.
Such evolution / degeneration of SQL can be performed by the automatic generation process shown below. In particular, when SQL is evolved, evolution data and correct answer data are automatically generated, and an application test based on automatic data amplification is performed. (Evolution test) can be executed.

[evolution]
FIG. 5 schematically shows an algorithm of automatic evolution processing.
First, when SQL is evolved, as shown in FIG. 5, the SQL can be changed (evolved) according to the following three patterns in the patterned SQL structure diagram.

(1) Extending laterally by UNION By adding the same structure sideways in the structure diagram, the same functions as the structure can be added and expanded.
Thus, for example, “business system A”, “business system B” in which the current system manages data of three items of “purchaser”, “purchase amount (yen)”, and “payment due date” as attribute information. When the three functions “business system C” are implemented, the functions of a new service / new business system such as “business system D” can be added or expanded.
FIG. 6 shows an example of an output result when the SQL structure is horizontally evolved by UNION (report output unit 18).

(2) Extending vertically due to JOIN In the structure diagram, by adding the structure of stored data vertically, attributes of management data can be added and added, and the number of JOINs can be increased.
Thus, for example, “business system A” and “business system B” in which data of three items of “purchaser”, “purchase amount (yen)”, and “payment due date” are managed as attributes of management data are managed. , A new attribute such as “gender: male or female” can be assigned and added to “business system C”.
FIG. 7 shows an example of the output result when the SQL structure is vertically evolved by JOIN.

(3) Intervening by adjusting the display order In the structure diagram, the display order can be changed and adjusted by changing the horizontal position of the same structure. As a result, for a plurality of functions having the same structure, the display position of an arbitrary function can be moved and placed, for example, between the previously displayed functions.
FIG. 8 shows an example of the output result when the display order is changed. In this example, “business system A”, “business system B”, “business system” managing three item data of “purchaser”, “purchase amount (yen)”, and “payment due date” as management data. This is a case where the display order of “C” is replaced with “business system A”, “business system C”, and “business system B”.

[Degeneration]
On the other hand, in the case of degenerating SQL, as shown in FIG. 9, the SQL can be changed (degenerated) in the following two patterns similar to the above-described evolution in the patterned SQL structure diagram.
(1) Cutting a UNION and shrinking it horizontally In the structure diagram, by deleting a part of the same structure lined up horizontally, the function of the structure can be reduced or degenerated.
As a result, for example, when the current system implements three functions of “business system A”, “business system B”, and “business system C”, the function of “business system C” can be deleted or reduced. Can do.
FIG. 10 shows an example of an output result when UNION is degenerated horizontally for the SQL structure.

(2) Cutting JOIN and vertically contracting In the structure diagram, by deleting a part of the structure of stored data arranged vertically (deleting the JOIN table), it is possible to reduce / degenerate the attributes of management data.
Even when degenerating SQL, it goes without saying that the display order can be changed and adjusted by changing the horizontal position of the same structure in the structure diagram as in the case of the evolution described above. .

[Structural decomposition of SQL / Creation of structure diagram]
Next, a description will be given of structure decomposition and creation of a structure diagram in which the source code constituting the above-described SQL is distributed according to the syntax type of SQL.
As described above, the structure decomposition of SQL is executed according to the basic syntax of the SQL statement, focusing on the four syntaxes of “SELECT system”, “UPDATE system”, “INSERT system”, and “DELETE system”.
Basically, "SELECT ~;", "UPDATE ~;", "INSERT ~;", and "DELETE ~;" in the source code are extracted as a unit of one structure, and syntax is included in the command unit included therein. Are extracted and decomposed, and replacement / conversion is performed on a predetermined structure diagram image for each decomposed element (FIGS. 14 to 20).

Here, since the SELECT system syntax can describe a structure not found in other syntaxes (UPDATE system, INSERT system, DELETE system), first, a structure peculiar to the SELECT statement will be described with reference to FIGS. To do.
FIG. 11 to FIG. 13 are explanatory diagrams when disassembling the structure of the SELECT statement.
As shown in these figures, there are three syntax patterns in the SELECT statement.
FIG. 11 shows a syntax that can have a nested structure inside.
In the example shown in FIG. 11, when there are two data tables of “employee table” and “salary table” as management data of the database, for example, an employee number of “300,000 yen or more per month” is selected, In the SELECT statement, the employee number is used as a list of employee names and finally sorted in order of employee numbers.
In such a case, the syntax of the “nested structure” described in “(˜)” of “WHERE column name = (˜)” of the SELECT statement (the portion surrounded by a thick line in FIG. 11) is included. Thus, it is extracted as the structure of one SELECT statement.

FIG. 12 shows a syntax that can temporarily create an intermediate table and output a result.
In the example shown in FIG. 12, when there are two data tables of “employee table” and “retirement details” as management data of the database, for example, the “name of retirement” is designated and the employee name is acquired. It is a sentence.
In such a case, the structure described in “LEFT OUTER JOIN” in “FROM to WHERE” (the portion surrounded by a thick line in FIG. 12) is extracted as the structure of one SELECT statement. .

FIG. 13 shows a syntax that can create a union of results.
The SELECT statement shown in FIG. 13 is a union from the affiliated employee table for each base, for example, when there are two data tables of “base A working employee table” and “base B working employee table” as management data of the database. Is output.
In such a case, the syntax of two “SELECT to FROM table name” connected by “UNION ALL” (three parts surrounded by a thick line in FIG. 13) is extracted as the structure of one SELECT statement. .

[Create structure diagram image]
Then, for the SQL statement extracted as one structure according to the basic syntax of SQL (SELECT, UPDATE, INSERT, DELETE) as described above, the syntax is extracted and decomposed in units of commands included therein, and the decomposed elements A structure diagram showing the structure of the SQL is created by replacing / converting the image into a predetermined structure diagram image every time.
It should be noted that the images constituting the predetermined structure diagram shown below are stored and stored in advance as image data in the storage means of the system 10 or the like.

[SELECT statement]
FIG. 14 shows a case where the SELECT statement shown in FIG. 11 is converted into a predetermined structure diagram.
As shown in the figure, the SELECT statement in this case is created as a single structural diagram by replacing each element of the SELECT statement with a predetermined image according to the following procedure.
(1) In the syntax, “drum can mark” is written on the rightmost side to mean “innermost table”.
(2) In the meaning of “extract records that match the condition”, “single parenthesis” is written on the left side of “drum can mark”.
(3) Write “arrow” on the left side of “brackets” to mean “use the result”.
(4) “Drum can mark” is written on the left side of “arrow” in the sense of “concatenated with data from another table”.
(5) Write “parentheses” on the left side of “drum can mark” to mean “extract applicable record or column”.
The SELECT statement shown on the left side of FIG. 14 (FIG. 11) is replaced and generated as the structure diagram shown on the right side of FIG.

FIG. 15 shows a case where the SELECT statement shown in FIG. 12 is converted into a predetermined structure diagram. The SELECT statement is replaced and created as a structure diagram by the following procedure.
(1) Two “drum can marks” indicating “JOIN tables” are arranged vertically on the rightmost side in the order of table names.
(2) Write “JOIN type” and “arrow” to the left of the corresponding “drum can mark”.
(3) “Drum can mark” indicating “JOIN destination table” is arranged on the left side of “arrow”.
(4) Write “arrow” on the left side of “drum can mark” to mean “use the result”.
(5) “Drum can mark” is written on the left side of “arrow” in the sense of “concatenated with data from another table”.
(6) Write “parentheses” on the left side of “drum can mark” to mean “extract applicable record or column”.
The SELECT statement shown on the left side of FIG. 15 (FIG. 12) is replaced and generated as the structure diagram shown on the right side of FIG.

FIG. 16 shows a case where the SELECT statement shown in FIG. 13 is converted into a predetermined structure diagram. The SELECT statement is replaced and created as a structure diagram by the following procedure.
(1) “Drum can mark” and “single parenthesis” indicating the first SELECT statement are placed on the rightmost side, and they mean “results (set) output above (before) the UNION phrase”. Enclose with "Square frame".
(2) “Horizontal line” is connected to the left side of “square frame” to mean “union” and “UNION type” is described.
(3) “Drum can mark” and “single parenthesis” indicating the second SELECT statement are placed on the leftmost side, and they mean “result (set) output under (after) UNION phrase”. It is enclosed with a “square frame” and connected to the left side of the “horizontal line”.
In addition, since “UNION” and “UNION ALL” exist as commands that can “create the union of results”, when generating a structure diagram, an image that can distinguish between them is assigned. To.
The SELECT statement shown on the left side of FIG. 16 (FIG. 13) is replaced and generated as the structure diagram shown on the right side of FIG.

[INSERT statement]
FIG. 17 shows a case where an INSERT statement is converted into a predetermined structure diagram. The INSERT statement is replaced and created as a structure diagram by the following procedure.
(1) “Drum can mark” is written on the rightmost side to mean “data insertion destination table”.
(2) The “left to right arrow” indicating “data insertion work” is arranged on the left side of the “drum can mark”.
(3) “Black circle“ I ”character” indicating “INSERT” in the meaning of “data insertion base point” is arranged on the left side of “arrow”.
The INSERT statement shown on the left side of FIG. 17 is replaced and generated as a structural diagram shown on the right side of FIG.

FIG. 18 shows a case where an INSERT statement capable of inserting data retrieved from another table is converted into a predetermined structure diagram as an INSERT statement. The INSERT statement is replaced as a structure diagram by the following procedure.・ Created.
(1) “Drum can mark” is written on the rightmost side to mean “data insertion destination table”.
(2) The “left to right arrow” indicating “data insertion work” is arranged on the left side of the “drum can mark”.
(3) “Black circle“ I ”character” indicating “INSERT” in the meaning of “data insertion base point” is arranged on the left side of “arrow”.
(4) In the meaning of “extract applicable record or column”, “single parenthesis” (in the opposite direction to the SELECT statement) is written on the left side of “black circle“ I ”character”.
(5) “Drum can mark” is written on the left side of “single parenthesis” in the sense of “table storing original data used as data insertion destination”.
Through the above processing / procedure, the INSERT statement shown on the left side of FIG. 18 is replaced and generated as a structure diagram shown on the right side of FIG.

[UPDATE statement]
FIG. 19 shows a case where an UPDATE statement is converted into a predetermined structure diagram. The UPDATE statement is replaced and created as a structure diagram by the following procedure.
(1) “Drum can mark” is written on the rightmost side to mean “data update destination table”.
(2) A “left to right arrow” indicating “data update work” is arranged on the left side of the “drum can mark”.
(3) “Black circle“ UP ”character” indicating “UPDATE” in the meaning of “data update base point” is arranged on the left side of “arrow”.
The UPDATE statement shown on the left side of FIG. 19 is replaced and generated as a structure diagram shown on the right side of FIG.

[DELETE statement]
FIG. 20 shows a case where a DELETE statement is converted into a predetermined structure diagram. The DELETE statement is replaced and created as a structure diagram by the following procedure.
(1) “Drum can mark” is written on the rightmost side to mean “data deletion destination table”.
(2) The “left to right arrow” indicating “data deletion work” is arranged on the left side of the “drum can mark”.
(3) “Black circle“ D ”character” indicating “DELETE” in the meaning of “data deletion base point” is arranged on the left side of “arrow”.
The DELETE statement shown on the left side of FIG. 20 is replaced and generated as a structure diagram shown on the right side of FIG.

[Structure mapping / convergence]
Next, the mapping / convergence processing of the structure diagram created as described above will be described.
21 and 22 are explanatory diagrams showing a method for mapping the generated structure diagram. FIG. 21 shows a case where the structure diagram is a SELECT statement, and FIG. 22 shows a case where it is an INSERT statement.
As shown in these drawings, the mapping is executed by preparing a “square map” having an initial value of “1001 square × 1001 square”, for example, and attaching a structural diagram on this map.
The grid map starts from “vertical and horizontal 1001 squares” as an initial value, but can be expanded when there are insufficient squares.
Also, such grid map data is stored and stored in advance as image data in the storage means of the system 10 together with the image data of the structure diagram described above.

Mapping rule
The mapping process is executed based on the following rules.
First, for the SELECT statement, the following rules are used (see FIG. 21).
[SELECT statement rule]
・ Start writing from the center of the right end ・ Symbol: 1 per square ・ “SELECT” is placed in order from right to left ・ “JOIN” is placed in order from top to bottom ・ Result set is the minimum size that includes all symbols A circle with a thick frame ・ Arrow: Connect one symbol across the symbol (the connection point is in the middle)
In order to distinguish the “JOIN” type, when “JOIN”, the classification is described below the arrow. − “JOIN”: “J” is described − “LEFT OUTER JOIN”: “LJ”
-“RIGHT OUTER JOIN”: “RJ”
-"FULL OUTER JOIN": "FJ"
・ "UNION" that creates a union is connected with a horizontal line. To distinguish "UNION" type, a category is written below the horizontal line.-"UNION": "U" is written.-"UNION ALL": "UA"
・ The color is expressed in black and white

Similarly, the following rules are used for the INSERT statement, the UPDATE statement, and the DELETE statement (see FIG. 22).
[INSERT statement, UPDATE statement, DELETE statement rule]
・ Start writing from the center of the right edge ・ Symbol: One in one square ・ Arrange in order from right to left ・ Contain the result set in a thick frame with a minimum size including all symbols ・ Arrow: One square across the symbol Connect with (the connection point is in the middle)
Describe from left to right • Use parentheses to extract values from the table (use the opposite direction to SELECT • Describe the category at the base point to distinguish INSERT / UPDATE / DELETE-“INSERT”: “I -“UPDATE”: “UP”
-"DELETE": "D"
・ The color is expressed in black and white

In accordance with the mapping rule as described above, the convergence process of the structure diagram is executed, and the patterned representative SQL is determined.
Convergence of the structure diagram is performed by brute force by image processing. By using such an image processing technique, the convergence of the structure diagram can be executed at high speed and accurately.
[Preprocessing]
First, as a “pre-processing” of convergence, as shown in FIG. 23, processing for deleting the filler from the map is executed.
Specifically, margins other than the “thick line” shown in FIG. 23 are deleted.
However, the “horizontal line” and “black circle” indicating “UNION” are not deleted.

[Main processing]
Next, as the “main process” of convergence, as shown in FIGS. 24 and 25, “brute force” by image processing and “check of inclusion relation” based on it are executed.
First, the “main process” is executed by the following procedure (see FIG. 24).
(1) Execute processing for each directory of SELECT, UPDATE, DELETE, and INSERT systems
(2) Sort by the grid used
(3) Extract the structure map with the few squares that you use the most and make it the comparison target
(4) The structure diagram selected in “(3)” is searched for the same structure in the structure diagram having the largest grid (see FIG. 25).
(5) When the same structure is found, the structure diagram selected in “(3)” is discarded as being included in the upper SQL.
(6) Select the structure diagram with the largest number of grids after the structure diagram selected in (3), and execute the same processing as (3) to (5)
(7) If a structure that is “included” is not found by the processes of “(3) to (5)”, the comparison target is changed to the second largest structure diagram, and the same process is executed. By repeating the above, the most complicated SQL can be found.

FIG. 25 shows the details of the search process for checking the inclusion relationship of “(3) to (5)”.
As shown in the figure, the search for inclusion relations is executed according to the following procedure.
(1) Arrange the structure diagram that is the target of inclusion check so that it is aligned with the upper left corner of the grid (2) Move the target structure diagram one cell at a time in the right direction and check whether there is the same structure (3) Target structure When the figure reaches the right end of the grid, move it downward by one square (4) Move the target structure diagram one by one from the left to the right again, and perform the same check. End when it reaches the bottom

By repeating the above processing, the structure diagram included in another structure diagram is discarded, and only the largest (complex) structure diagram including all the structure diagrams is extracted, and the structure diagram becomes the representative SQL. Will be determined.
Then, the structure diagram showing the representative SQL determined as such can be converted and restored to the original SQL character data (source code), and the operation check test in the migration destination database 200 can be executed. Become.
As a result, the operation check test of the migration destination database 200 can be executed based on the representative SQL converged and patterned by the structure diagram as described above, and is faster and more efficient than the original enormous SQL. Test can be executed, and correct answer data of the test can be handled with less data.

[Structural evolution / degeneration]
As described above, the structure diagram showing the representative SQL generated by the mapping / convergence processing can be further evolved / degenerated (FIGS. 5 and 9) by the following procedure.
The evolution / degeneration process of the structure diagram can be executed by image processing using squares, similarly to the generation of the structure diagram described above.

[Evolution processing]
FIG. 26 to FIG. 30 show a structure diagram evolution processing technique.
FIG. 26 shows a case in which the “elongation” evolution (see FIG. 5) is performed by “UNION” which is the first evolution process.
Specifically, when the symbol “U” or “UA” indicating “UNION” is included in the structure diagram, as shown in FIG. 26, the existing structure connected by the “UNION” Shift left by half the squares above.
After that, the same structure diagram is generated by copying, pasted to the right side of the shifted existing structure, connected with “UNION”, and the structure diagram is saved.
As a result, structural expansion (evolution) in the lateral direction is performed.
By repeating the same processing, it is possible to extend in the horizontal direction by an arbitrary number.

FIG. 27 shows a case where the “elongation” evolution (see FIG. 5) is performed by “JOIN” which is the second evolution process.
Specifically, when the symbols “J”, “LJ”, “RJ”, and “FJ” indicating “JOIN” are included in the structure diagram, the “JOIN” indicates the vertical direction as shown in FIG. An image of the same “drum can mark” is arranged under “drum can mark” indicating “data” arranged in, and connected with “JOIN” to store the structure diagram.
Thereby, the structure extension (evolution) in the vertical direction is performed.
By repeating the same processing, it is possible to extend in the vertical direction by an arbitrary number.

28 and 29 show a method of automatically generating data to be stored and correct data when the structural diagram is evolved in the horizontal and vertical directions as described above, and FIG. 28 shows the expansion in the horizontal direction. FIG. 29 shows the case of extension in the vertical direction.
As shown in FIG. 28, when horizontal extension is performed, data corresponding to the copy source structure is copied as data to be stored in the extended structure. At that time, for the character item of the stored data, for example, the last character is replaced with another character, for example, “1”. No change is made for numeric items.
By doing so, as shown in the “assumed result” in FIG. 28, data to be stored in the expanded structure and correct data are automatically generated.
When providing a plurality of extension structures, there is a possibility of overflowing by simply adding “1” as described above. Therefore, when further expanding the structure, the data of the next extension structure is “2”. Can be dealt with by increasing the number sequentially.

As shown in FIG. 29, when vertical extension is performed, data of the previous (upper) structure (table) is copied as data to be stored in the extended structure. At this time, as in the case of the horizontal direction, for example, the last character is replaced with “1” for the character item of the payment data. No change is made for numeric items.
By doing so, as shown in the “assumed result” in FIG. 29, the data to be stored in the expanded structure and the correct answer data are automatically generated.
In the case of this vertical extension, as in the case of the horizontal direction, when the structure is further extended, “2” is assigned to the data of the next extended structure, and the numbers are sequentially increased thereafter. it can.

FIG. 30 shows a data output result in the case of performing the third “evolution process” (see FIG. 5), which is the “evolution by adjusting the display order”.
In this case, as shown on the left side of FIG. 30, display is performed by replacing column names (for example, “column name 1, column name 2...”) In the source code converted from the structure diagram into the original character data. The order can be changed.
As a result, as shown on the right side of FIG. 30, for example, the output results displayed in the order of “business system A” and “business system B” can be exchanged so that “business system B” can be displayed at the top. Become.
Note that “between” is expressed as “between” because there are cases where the result record set falls between three or more “columns” of rearrangement.

[Degeneration]
FIG. 31 to FIG. 34 show a method of degenerating the structure diagram.
FIG. 31 shows a case where “UNION”, which is the first degeneration process, is removed to cause “decrease horizontally” degeneration (see FIG. 9).
Specifically, when the structure diagram includes a symbol “U” or “UA” indicating “UNION”, as shown in FIG. 31, the leftmost of the existing structures connected by the “UNION” ” The arranged structure, the symbol indicating “UNION” and the “horizontal line” are deleted.
As a result, the structure is reduced (degenerated) in the lateral direction.
By repeating the same processing, it is possible to perform reduction in the horizontal direction by an arbitrary number.

FIG. 32 shows a case where “JOIN”, which is the second degeneration process, is removed to cause “deterioration” (see FIG. 9).
Specifically, when the symbols “J”, “LJ”, “RJ”, and “FJ” indicating “JOIN” are included in the structure diagram, the “JOIN” indicates the vertical direction as shown in FIG. The “drum can mark” at the bottom of the “drum can mark” indicating “data” arranged in is deleted.
As a result, the structure is reduced (degenerated) in the vertical direction.
By repeating the same processing, it is possible to extend in the vertical direction by an arbitrary number.

33 and 34 show the data output results when the structural diagram is degenerated in the horizontal and vertical directions as described above. FIG. 33 shows reduction in the horizontal direction, and FIG. 34 shows reduction in the vertical direction. This is the case.
When the reduction in the horizontal direction is performed, as shown in the “assumed result” in FIG. 33, for example, the output results of “business system A” and “business system B” before the degeneration are displayed as “ Only “Business System A” is output and displayed.
When the reduction in the vertical direction is performed, as shown in FIG. 34, for example, data of three items of “purchaser”, “purchase amount (yen)”, and “payment due date” before degeneration as attribute information Is displayed as an output result, the one-line item (“payment due date”) disappears and is not displayed.
In this case, it is important in ensuring the execution of SQL that the search result record set does not change even if the number of “JOIN” decreases.

[Special function test]
Next, the special function test will be described with reference to FIG.
As described above, in the present system 10, the operation of the migration destination database 200 is performed by selecting and determining a representative SQL pattern by creating a structural diagram showing the structure of the SQL and its convergence, and performing a test based on the representative SQL pattern. A guarantee is made.
The patent function test can be executed as finishing / complementation of such a representative pattern test, and the operation check of special functions included in SQL, for example, special controls and functions (for example, SUM, AVG, etc.) shown in FIG. The patent function test searches whether or not a predetermined special function is included in the SQL character string input to the system 10, and when the predetermined special function is included. Performs a test using predetermined correct answer data for the special function (special function test execution unit 17).

Specifically, for example, using a spreadsheet application such as “Excel” of Microsoft Corporation, an analysis table of special functions included in SQL is created, and all SQL cases are searched by, for example, “Grep” and hit. This can be done by checking whether the results are the same in the migration source database 100 and the migration destination database 200, one case at a time.
As a special function to be targeted, for example, a “special function file” as shown in FIG. 35 can be stored in the storage means of the system 10.

Such a special function test is a rare case to be picked up, but by performing such “finishing”, it is possible to almost completely guarantee the operation of the migration source database 100 and the migration destination database 200. Become.
Also for this special function test, test results can be output and displayed in the same manner as the SQL representative pattern test (report output unit 18).

[Operation]
Next, specific processing / operation (implementation of the database migration support method) in the system 10 as described above will be described with reference to FIGS.
[Overall process]
FIG. 36 is a flowchart showing the entire process of the operation confirmation process of the system 10.
As shown in the figure, in the present system 10, processing is performed in two systems: “operation confirmation by searching for a representative pattern” and “operation confirmation by searching for a special function”.
Specifically, it is as follows.
[Operation check of representative pattern]
First, “operation check by searching a representative pattern” is “creation / placement of SQL file: manual” → “determination of representative SQL: automatic” → “input / test data creation / placement: manual” → "Test execution (current guarantee / evolution / degeneration): automatic" → "result report creation: automatic" is executed in this order.
Note that “manual” during processing means that, for example, a user, administrator, operator, or the like of the system 10 manually selects (specifies / inputs) data, etc.
On the other hand, “automatic” means that data is automatically extracted, selected, determined, generated, and the like by the operation of the information processing apparatus constituting the system 10.

[Operation check of special function]
Next, “operation check by searching for special functions” is performed as follows: “SQL file creation / placement: manual” → “representative SQL determination: automatic” → “test data input / correction file creation / placement: manual” "→" Test execution (current guarantee): Automatic "→" Result report creation: Automatic ".
Note that “manual” and “automatic” during processing are the same as the “operation confirmation by searching for a representative pattern”.
In addition, in the “confirmation of operation by searching for a representative pattern”, if “SQL file creation / placement” and “test data input / correct answer file creation / placement” have already been performed manually, Can be reused.

[System screen configuration]
FIG. 37 and FIG. 38 show an example of screen display in the operation check process of the system 10.
In the present system 10, when an operation confirmation process described below is executed, the display means serving as the operator's user interface, such as a liquid crystal display connected to the present system 10, is as shown in FIGS. A display screen is generated and displayed. Then, when the operator performs an input operation along the display screen, each step of the operation check process is executed.
The screen configuration shown below is an example, and it goes without saying that other screen configurations can be generated and output.

[0. System startup screen]
This screen is initially displayed as an initial screen of the system 10.
In this screen, as shown in FIG. 37, when “operation check execution” is selected according to the input operation of the operator according to the screen display, the screen transits to “1. system execution format selection screen”. If “execution record browsing” is selected, the process proceeds to “5. execution record browsing screen”.
[1. System execution format selection screen]
This screen is a screen that is transitioned to when “operation check execution” is selected in “0. system startup screen”, and displays the system execution format in the present system 10 so that it can be selected.
Specifically, on this screen, as shown in FIG. 37, an “automatic execution selection button” and a “manual execution selection button” are displayed so as to be selectable, and depending on the input operation of the operator, any execution format is displayed. Is selected and executed.

[2. Progress display screen]
This screen displays the progress status of the operation confirmation process executed in the system 10, and the same contents as those in FIG. 36 described above are displayed.
In this screen, when the execution format selected / designated in “1. System execution format selection screen” described above is “Manual execution”, a predetermined announcement screen is displayed.
On the other hand, when “automatic execution” is selected and executed, the completed step is displayed in gray, for example, corresponding to the progress of the automatic execution process, and the step being executed is highlighted in red, for example. The progress of processing is displayed.
Further, when all the processes are completed, the “complete” button becomes valid, and when the “complete” button is pressed by an input operation by the operator, the above-described “0. system start screen” is returned.

[3. SQL file acquisition during manual execution]
This screen displays a screen for prompting an operation for acquiring an SQL file when the execution format selected / designated in “1. System execution format selection screen” is “Manual execution”.
Specifically, as shown in FIG. 37, for example, an announcement display “Please specify the SQL file storage location ▼” is displayed, and when the “▼” button is pressed by the operator's input operation, an arbitrary display is displayed. A screen that allows folder selection is displayed (not shown).
Here, if a valid file is not specified, it is not accepted.
Further, after the file storage location is specified, the “next” button is pressed to return to the above-described progress display screen and proceed to the next step.

[4. Data input instruction screen]
This screen also displays a screen that prompts the user to input data into the migration destination database 200 and obtain the correct file when the execution format selected / specified in “1. System execution format selection screen” is “Manual execution”. Is.
Specifically, as shown in FIG. 38, for example, an announcement display “Load data to the migration destination database” or “Specify the correct file storage location” is displayed, and the operator's input operation When the “▼” button is pressed, a screen on which an arbitrary folder can be selected is displayed (not shown). Also, if a valid file is not specified, it will not be accepted.
Then, after the file storage location is designated, the “next” button is pressed to return to the above-described progress display screen and proceed to the next step.

[5. Execution record browsing screen]
This screen is a transition screen when “Operation Check Execution” is selected in “0. System Startup Screen”, and displays an execution record desired to be browsed in the operation check processing of the system 10 in a selectable manner. Is.
Specifically, as shown in FIG. 38, a plurality of “date and time” and “number of executions” of the operation confirmation executed in the past in the system 10 are displayed on this screen, and can be scrolled up and down with a slide bar, for example. A plurality of (for example, about five) execution histories are always displayed on the screen. As a record of execution history, a predetermined number of records can be stored, and for example, information of up to 1000 execution logs can be stored.
Then, as shown in FIG. 38, when a button corresponding to an arbitrary execution history is clicked and selected in accordance with an input operation by the operator, the center of the button is inverted to black, and the selected execution history is displayed. Will be identified. In the case shown in FIG. 38, the selectable execution history is only one line (one case), but it is of course possible to select a plurality of execution histories.
If the “Return” button is pressed, the screen returns to “0. System startup screen”.

[6. Desired browsing process selection screen]
This screen displays the date / time of execution history and the number of executions selected in the previous screen (5. execution record browsing screen), and “representative pattern” and “ One of the “special functions” is displayed so as to be selectable.
Specifically, as shown in FIG. 38, for example, “January 1, 2018-12:00 first execution record” is displayed as information indicating the selected execution history. Either “confirmation of operation by searching for representative pattern” or “confirmation of operation by searching for special function” is displayed in a selectable manner.
When the button corresponding to either “representative pattern” or “special function” is clicked according to the input operation by the operator, the center of the button is highlighted in black, and the selected processing content is specified. Will come to be.
After that, if the “Browse” button is pressed, the screen shifts to the following “7. Report display screen”.
When the “return” button is pressed, the screen returns to “5. execution record browsing screen”.

[7. Report display screen]
This screen displays a report file (see FIG. 50 to be described later) corresponding to the execution recording / processing content selected on the previous screen (6. desired browsing processing selection screen).
Although a specific display example of the screen is omitted, by displaying this report file, the user can browse the contents and results of the operation check process executed in the past.
When the “return” button is pressed, the screen returns to the initial screen “0. system startup screen”.

[flowchart]
Next, specific contents of the operation confirmation process in the system 10 executed according to the screen display as described above will be described with reference to the flowcharts shown in FIGS.
[Representative pattern: Creation / placement of SQL file]
FIG. 39 shows a flowchart of the “representative pattern: creation / placement of SQL file” process.
In this SQL file creation / arrangement process, as shown in FIG. 39, first, the progress display screen displayed on the display means (display or the like) of the present system 10 is initialized (step 3901) and input by the operator. It is determined whether the execution format of the operation confirmation process selected according to the operation is automatic execution or manual execution (step 3902).

If the execution format is “manual”, the display screen is changed to the “3. SQL file acquisition during manual execution” screen (see FIG. 37) (step 3903), and the process ends. In this case, as described above, in the SQL file, an arbitrary folder is selected and data is acquired in accordance with the input operation of the operator (see FIG. 37).
On the other hand, when the execution format is “automatic”, after the “SQL file creation / placement” box on the transition screen (see FIGS. 36 and 37) is colored in red or the like (step 3904), the SQL file is automatically created. Creation processing is executed (steps 3905 to 3910).

In creating the SQL file, first, the continuous management number used for the file name is set to “0” (step 3905), and then the SQL is extracted from the SQL execution record of the migration source database 100 (step 3906). This extraction process is performed by extracting all SQL files for each SQL.
The extraction process is repeated until the continuous management number reaches “100,000” (step 3907), the continuous management number used for the file name is incremented by “1” (step 3908), and the file name is “mig_continuous”. "Management number.txt" is output to the storage location "INPUT-SQL file storage directory" (step 3909). When the continuous management number reaches "100,000" (step 3907), the progress of the progress display screen is advanced. (Step 3910), the process is terminated.

[Representative pattern: Test data input / Creation / arrangement of correct answer data]
FIG. 40 shows a flowchart of the “representative pattern: test data input / correction data creation / arrangement” process.
In this data input / arrangement process, as shown in FIG. 40, it is first determined whether the execution format of the operation check process selected according to the input operation of the operator is automatic execution or manual execution (step 4001), in the case of “manual”, the display screen is changed to “4. data input instruction screen” (see FIG. 38) (step 4002), and the processing is ended. In this case, as described above, the data input / correct answer file is arranged by selecting an arbitrary folder according to the input operation of the operator (see FIG. 38).

On the other hand, in the case of “automatic”, the “test data input / correct answer file creation / placement” box on the transition screen (see FIGS. 36 and 37) is colored in red to indicate that the process is being executed. (Step 4003), test data input processing (Step 4004) and correct file creation / arrangement processing (Steps 4005 to 4009) are executed.
First, the test data input process is performed by sequentially extracting data from the migration source database 100 and inputting / inputting it to the migration destination database 200 (step 4004).

Next, in the correct file creation / placement process, the SQL file created in the previous process (see FIG. 39) is sequentially read, and the process is executed for all the SQL files (step 4005).
Specifically, first, the name of the file being opened is recorded (step 4006), the read SQL is issued to the migration destination database 200, and data is stored for the following items (step 4007). .
-Results-CPU usage rate-Memory usage rate-Response time

The correct answer file is saved with a different name (step 4008). The file name can be generated by adding “_correct” or the like immediately after the open result file name.
Then, the correct mapping file is searched with the open file name, the correct file name is written next to the corresponding correct file name (step 4009), and the correct file creation and arrangement process is completed.
Thereafter, the progress of the progress display screen is advanced (step 4010), and the process proceeds to the next representative SQL determination process.

[Representative pattern: Determination of representative SQL]
41 to 43 show flowcharts of the “representative pattern: determination of representative SQL” process.
In this representative SQL determination process, as shown in FIG. 41, first, an input is classified into four categories of “SELECT, UPDATE, INSERT, and DELETE” according to the basic syntax (steps 4101 to 4102).
Specifically, the SQL file is read for each file, and this reading process is repeatedly executed up to the final file (step 4101).
Then, the SQL classification sorting subroutine is called, and copy files are arranged in the four directories of SELECT / UPDATE / INSERT / DELETE (step 4102).

Thereafter, as shown in FIGS. 42 and 43, the configuration diagram creation processing is executed in four systems for each syntax system of SELECT / UPDATE / INSERT / DELETE. By creating a structure diagram in such a four-fold manner, the processing speed can be increased.
First, the SQL file is read for each file, and the reading process is repeated until the final file (step 4201).
Next, a subroutine for creating an SQL structure diagram is called, and an SQL structure diagram is generated from the SQL file (step 4202: FIGS. 14 to 20).
The specific contents of the subroutine will be omitted, but the subroutine uses a known programming technique to create a program that can execute the above-described SQL structure diagram creation algorithm (see FIGS. 11 to 34). It is realized by doing. Hereinafter, the same applies to the subroutine.

Subsequently, as shown in FIG. 43, the SQL structure diagram file is read for each file, and the reading process is repeatedly performed up to the final file (step 4301).
Then, a subroutine for SQL structure diagram convergence is called to determine a representative SQL structure diagram (step 4302: FIGS. 21 to 25).
Further, the SQL file before being structured is referred to the SQL mapping file and stored in the representative SQL directory (step 4302).
Thereafter, the progress of the progress display screen is advanced (step 4303), and the process proceeds to the test processing by the next representative SQL.

[Representative pattern: Test execution (current guarantee / evolution / degeneration)]
44 to 47 show flowcharts of the “representative pattern: test execution (current guarantee / evolution / degeneration)” process.
In this test process, as shown in FIG. 44, first, an SQL file is read from the representative SQL directory for each file and repeatedly executed until the final file (step 4401). Here, first, a test is started on whether or not the same result as that of the migration source database 100 is obtained in the migration destination database 200.
Specifically, the current guarantee test subroutine is called to execute the test (step 4402). The test is performed by issuing the SQL determined in the previous process (see FIGS. 41 to 43) to the migration destination database 200 and comparing the correct answer with reference to the correct answer mapping file.

Then, it is determined whether or not the test result for the migration destination database 200 matches the migration source database 100 (step 4403). If the test results are the same, an “evolution” process / test (flow on the left side of the drawing) When the test results are different, “degeneration” processing / test (flow on the right side of the drawing) is executed.
First, the test result is output to the representative pattern port file (step 4404). Thereafter, it is determined whether or not “SELECT” exists before the first blank appears on the first line (step 4405). If there is, “1” is set as the number of times of evolution / degeneration (step 4406). On the other hand, if there is no “SELECT”, the evolution / degeneration process is not performed, and the test process ends.

Subsequently, an evolution / degeneration subroutine is called to generate an evolution / degeneration SQL (step 4407: see FIGS. 26, 27, 31, and 32), and the evolved / degenerated SQL is stored in the representative SQL directory. The
Next, an automatic data amplification subroutine is called to automatically create data for the evolved / degenerated SQL, and correct data is automatically registered in the correct mapping file (step 4408: FIG. 28, FIG. 29, FIG. 33, FIG. 34).
After that, the evolution / degeneration test subroutine is called, and the evolved / degenerated SQL is issued to the migration destination database 200, and the correct mapping file is referred to and compared with the correct answer, so that the first evolution / degeneration is performed. A test is performed (step 4409).

Then, it is determined whether or not the result of the evolution / degeneration test matches with the migration source database 100 (step 4410). If the test results match, the “second” evolution / degeneration process / test (each drawing) When the flow on the left side is executed and the test results are different, the process ends (flow on the right side of each drawing).
As shown in FIG. 45, first, when the evolution / degeneration test result is different from that of the migration source database 100, after being output to the representative pattern port file (step 4501), the progress of the progress display screen is advanced (step 450). 4502) The process ends.
On the other hand, if the evolution / degeneration test result matches the migration source database 100, after being output to the representative pattern port file (step 4503), "2" is set as the number of evolution / degeneration (step 4504).
Similarly to the first time, the evolution / degeneration subroutine (step 4505), the automatic data amplification subroutine (step 4506), and the evolution / degeneration test subroutine (step 4507) are called and the evolution / degeneration process and test process are called. Is executed.

Thereafter, it is determined whether the result of the evolution / degeneration test matches the migration source database 100 (step 4508). If the test results match, the “third” evolution / degeneration process / test (each drawing) When the flow on the left side is executed and the test results are different, the process ends (flow on the right side of each drawing).
That is, as shown in FIG. 46, when the second evolution / degeneration test result is different from that of the migration source database 100, the progress is displayed on the progress display screen after being output to the representative pattern port file (step 4601). (Step 4602) The process ends.
On the other hand, if the result of the second evolution / degeneration test matches the migration source database 100, after being output to the representative pattern port file (step 4603), "3" is set as the number of evolution / degeneration (step 4604). Thereafter, similarly, the evolution / degeneration subroutine (step 4605), the automatic data amplification subroutine (step 4606), and the evolution / degeneration test subroutine (step 4607) are called to execute the evolution / degeneration process and the test process. .

For the third evolution / degeneration test, after it is determined whether the test result matches the migration source database 100 (step 4608), as shown in FIG. 47, regardless of the test result. After the test result is output to the representative pattern port file (step 4701), the progress on the progress display screen is advanced (step 4702), and the process is terminated.
This is because if evolution / degeneration processing is performed about three times, prediction of system expansion due to evolution, detection of system improvement due to degeneration, and the like are sufficiently possible.
However, of course, the evolution / degeneration process / test as described above can be performed 4 times or more and 2 times or less, and an arbitrary number of times can be appropriately executed in consideration of the scale and cost of the system. it can.

[Special function: Determination of representative SQL]
FIG. 48 shows a flowchart of the “special function: representative SQL determination” process.
In the special function representative SQL determination process, as shown in FIG. 48, first, a special function file (see FIG. 35) is opened, and the registered function is stored in the storage means (step 4801).
Thereafter, for each registered function, it is confirmed whether or not a corresponding function exists in each SQL (step 4802), and a special function test is executed (steps 4803 to 4805).

Specifically, the SQL file is read for each file and repeatedly executed until the final file (step 4803).
Then, it is determined whether or not the corresponding function exists in the read SQL (step 4804). When the corresponding function does not exist, the special function test is ended.
On the other hand, if the corresponding function exists in SQL, the SQL file is saved with a different name (step 4805). The file name can be generated, for example, by adding “_function name” after the open result file name.
Then, the SQL file is stored in the special function directory and added to the special function correct answer mapping file (step 4805). As a result, the representative SQL including the special function is determined.
Thereafter, the progress of the progress display screen is advanced (step 4806), and the process ends.

[Special function: Test execution]
FIG. 49 shows a flowchart of the “special function: test execution and report creation” process.
First, as shown in FIG. 49, in the special function test process, the SQL file is first read from the above-mentioned special function file storage directory and is repeatedly executed until the final file (step 4901).
Then, the current guarantee test subroutine for the special function is called to execute the test (step 4902). The test is performed by issuing the representative SQL determined in the previous process (see FIG. 48) to the migration destination database 200 and referring to the correct mapping file for the special function and comparing it with the correct answer.
Thereafter, it is determined whether or not the test result for the migration destination database 200 matches the migration source database 100 (step 4903). If the test results are the same, “OK” is output to the special function report file. (Step 4904) If the test results are different, “NG” is output to the special function report file (Step 4905), and the process ends.

[Create test result report]
FIG. 50 shows a flowchart of the “representative pattern / special function: test execution and report creation” process.
The report creation process of the test result of the representative pattern / special function is executed as shown in FIG. 53 (steps 5001 to 5003).

[Representative pattern]
First, as a representative pattern result report, the following contents can be generated and output (step 5001).
(1) Representative pattern report file (summary part)
In this summary part, the following results can be output.
-Count up the total number of test SQL and output the number.
・ Search the “Current Warranty Test” column of the representative pattern report file with “OK” and output the number of hits.
-Subtract the response time in the "INPUT-SQL file name" column and the "Current Guarantee Test" column of the representative pattern report file and output the number of negative values.
(2) Representative pattern report file (degeneration test part)
The following results can be output to this degeneration test portion.
-Count the number of SQLs in the third degeneration test of the representative pattern report file, and output the number of degenerations and 3
・ If not found, count the second SQL number and output the number of degenerates 2 and the number.
・ If not found, count the first SQL number and output the number of degenerates and 1 number.
・ If not found, outputs 0 and 0.
(3) Representative pattern report file (degeneration test part)
The following results can be output to this evolution test part.
-Count the number of SQL in the third evolution test of the representative pattern report file, and output the number of evolutions and 3.
-If not found, the second SQL number is counted and the evolution number 2 and the number are output.
・ If not found, count the first SQL number and output the evolution number 1 and number.
-If not found, output 0 evolution number and 0 number.

[Special function]
Next, the following contents can be generated and output as a result report of the special function (step 5002).
Special function report file (summary part)
-Count up the total number of test SQL and output the number.
・ Search the “Current Warranty Test” column of the representative pattern report file with “OK” and output the number of hits.
-Subtract the response time in the "INPUT-SQL file name" column and the "Current Guarantee Test" column of the representative pattern report file, and output the number of negative values.

[Other information]
Further, as other information other than the above, the following contents can be generated and output (step 5002).
Specifically, the date and time can be acquired using the system clock, and the following results can be output.
-Create directory Naming rule: "Year / Month_Date_Time_Number" (for example, 6 digits_2 digits / 24-hour notation_4 digits / maximum 1000)
・ Data copy Data contents: Store the complete set from the INPUT-SQL file storage directory to the result report storage directory while maintaining the directory structure.

  When the above-described report result entry process is performed, the progress of the progress display screen is then advanced (step 5004), and the process ends.

As described above, according to the present system 10, when migrating from the migration source database 100 to the migration destination database 200, SQL that is operating at the migration source is replaced with a predetermined image to be patterned and evolved / degenerated. As a result, it is possible to perform efficient operation confirmation and system expansion / change tests.
As a result, safe / safe database migration can be realized, and for example, problems such as an unexpectedly prolonged project construction period and quality deterioration can be effectively prevented.

While the present invention has been described with reference to the preferred embodiments, it is needless to say that the present invention is not limited to the above-described embodiments, and various modifications can be made within the scope of the present invention.
For example, in the above-described embodiment, the description has been made taking SQL as an example of the database language targeted by the system 10, but the present invention can of course target database languages other than SQL.
In other words, the present invention can be applied to any language as long as it is applied / issued to a database system and used for taking in / out data stored in the database.

Moreover, the algorithm (FIGS. 2 to 35) and the flowcharts (FIGS. 36 to 50) shown in the above-described embodiment are examples for carrying out the present invention, and are not limited to the forms shown in the figure. Of course, other algorithms can be adopted as long as the technical idea of the present invention can be realized.
Further, in the above-described embodiment, the explanation is made on the assumption of a large-scale data system / SQL, but the present invention is not limited to the scale and configuration of the database including SQL, and is applicable to various databases to be migrated It can be applied.

  The present invention can be suitably used as a system that supports data system replacement and migration for reasons such as product change, function expansion / change / reduction, and hard disk resource replacement.

DESCRIPTION OF SYMBOLS 10 Database migration support system 11 Database language input part 12 Structure drawing creation part 13 Convergence processing part 14 Evolution / degeneration processing part 15 Representative pattern generation part 16 Representative pattern test execution part 17 Special function test execution part 18 Report output part 100 Migration source database 200 Destination database

Claims (5)

A system that supports database migration,
A database language input unit for inputting a database language sentence created in a predetermined database language, which is implemented in the database management system;
A database migration comprising: a structure diagram creating unit for decomposing a character string of a database language sentence input by the database language input unit and replacing the character string with a predetermined image to create a structure diagram of the database language Support system. Convergence processing unit for converging the structure diagram by searching whether or not the same structure is included in the structure diagram created by the structure diagram creating unit and discarding one if the same structure is included When,
A representative pattern generation unit that generates a representative database language sentence patterned to a minimum number by replacing the structural diagram converged by the convergence processing unit with an original character string. The database migration support system described. For the representative database language generated by the representative pattern generation unit, a representative pattern test execution unit that executes a test using predetermined correct answer data,
The database migration support system according to claim 2, further comprising a report output unit that outputs a test result of the representative pattern test execution unit. An evolution / degeneration processing unit that evolves by adding a structure to a structural diagram converged by the convergence processing unit or deleting a structure and degenerating,
The representative pattern generation unit
The database migration support system according to claim 2 or 3, wherein the representative database language is generated by replacing the structure diagram evolved or degenerated by the evolution / degeneration processing unit with a character string. The database language character string input in the database language input unit is searched for whether or not a predetermined special function is included. If the predetermined special function is included, a predetermined correct answer is obtained for the special function. The database migration support system according to any one of claims 1 to 4, further comprising a special function test execution unit that executes a test using data.