JP7055064B2 - Database migration support system and program - Google Patents

Description

The present invention relates to an assistive technology for managing and operating a database, and more particularly to a database migration support system and a program that enable efficient database migration (migration / conversion).

The database is migrated / switched (migrated) from the current system to a new system for reasons such as product changes, expansion / reduction of functions, replacement of hard disk resources, and the like.
So far, various support systems have been proposed so that the database migration can be performed efficiently while guaranteeing the same processing and functions as the current system in the migration destination system when migrating such a database. ..

For example, in Patent Document 1, the operator's proficiency is obtained by automatically generating the migration destination SQL corresponding to the migration source SQL (database language: Structured Query Language) based on the operation of the operator via the GUI. A database migration method with excellent versatility and operability that does not depend on the degree has been proposed.
Further, Patent Document 2 describes a data conversion method for automatically extracting SQL statements that have not been appropriately converted by specifying SQL statements that do not match between the two when converting the source SQL to the migration destination SQL. Has been proposed.

Japanese Unexamined Patent Publication No. 2002-351710. Japanese Unexamined Patent Publication No. 2011-258002

However, the technologies disclosed in Patent Document 1 and Patent Document 2 are premised on generating SQL newly for the new system of the migration destination, and the SQL of the currently operating system of the migration source is migrated. No problems with verification or operation guarantee when applied / issued first are assumed.
When migrating a database to a new system, a database management system (DBMS: DATABASE MANAGEMENT SYSTEM) that includes hardware resources to record data is a database language used for data transfer, although new products are introduced at the migration destination. For SQL), the SQL used at the migration source is used as it is. In particular, in a large-scale database, it is not realistic to create a new SQL corresponding to the new system from scratch because it takes a lot of time and cost.

On the other hand, when the migration source SQL is applied to the migration destination as it is, the operation of the migration destination may not be guaranteed correctly.
In the database, the "brain" part called the optimizer of the DBMS interprets SQL and generates an "execution plan (plan)" that acquires optimal data from the data table logically recorded on the hard disk.
However, the maturity level of this optimizer 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, a different "wrong result" will be returned. Can happen. That is, even if the SQL is the same, there may be some that can return the correct result and some that cannot return the correct result depending on the product of the DBMS (optimizer).

A SQL is composed of, for example, more than 50 lines of SQL statements if it is complicated, and in a large-scale system, for example, nearly 100,000 such SQLs may be implemented.
It takes a lot of time and effort to efficiently and surely verify such a huge amount of SQL and guarantee its operation, and in reality, it is difficult or impossible to test and verify all SQLs. be.
No prior art has been proposed that can effectively deal with such problems such as operation guarantee and verification when the same SQL is used in the migration source and the migration destination, including the above-mentioned Patent Documents 1 and 2.

In order to solve the above problems, the present invention provides a database migration support system and a program that can efficiently confirm the operation when the same SQL as the migration source is used at the migration destination when migrating the database. The purpose is to provide.

In order to achieve the above object, the present invention is a system that supports the migration of a database from the migration source database to the migration destination database, and is created in a predetermined database language implemented in the database management system of the migration source database. The database language input unit for inputting the database language sentence and the character string of the database language sentence input in the database language input unit are decomposed and replaced with a predetermined image to create a structural diagram of the database language. It is configured to include a structural diagram creating unit and execute an operation confirmation test of the database language sentence in the migration destination database based on the structural diagram created by the structural diagram creating unit .

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, by structurally plotting and patterning SQL when migrating a database, it is possible to efficiently check the operation when the same SQL as the migration source is used at the migration destination. ..

It is a functional block diagram which shows typically the whole structure of the database migration support system (hereinafter referred to as "the present system") which concerns on one Embodiment of this invention. It is explanatory drawing which shows typically the algorithm of the patterning of SQL in this system. It is explanatory drawing which shows typically the algorithm of the astringent processing of SQL in patterning. It is explanatory drawing which shows schematically the algorithm of the test processing of astringent 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 the similarly evolved SQL. It is explanatory drawing which shows an example of the report output which changed the display order of the SQL test result. It is explanatory drawing which shows schematically the algorithm of the degeneration processing of the patterned SQL. It is explanatory drawing which shows an example of the report output which shows the test result of degenerate SQL. It is explanatory drawing which shows the structure of the "SELECT" sentence included in SQL. Similarly, it is explanatory drawing which shows the structure of the "SELECT" sentence. Similarly, it is explanatory drawing which shows the structure of the "SELECT" sentence. It is explanatory drawing which shows the method of 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 structural diagram. Similarly, it is explanatory drawing in the case of replacing the "SELECT" sentence shown in FIG. 13 with a structural diagram. It is explanatory drawing in the case of replacing an "INSERT" statement with a structural diagram. Similarly, it is an explanatory diagram in the case of replacing the "INSERT" statement with a structural diagram. It is explanatory drawing in the case of replacing the "UPDATE" sentence with a structural diagram. It is explanatory drawing in the case of replacing a "DELETE" sentence with a structural diagram. It is explanatory drawing which shows the method of mapping the generated structural diagram, and is the case where the structural diagram is a "SELECT" statement. Similarly, it is an explanatory diagram in the case of mapping the structural diagram of the "INSERT" statement. It is explanatory drawing which shows the method of astringent processing of the mapped structure diagram. Similarly, it is explanatory drawing which shows the method of astringent processing of a structural drawing. Similarly, it is explanatory drawing which shows the method of astringent processing of a structural drawing. It is explanatory drawing which shows the method of evolution processing of the mapped structure diagram. Similarly, it is an explanatory diagram showing the method of evolution processing of the structural diagram. It is explanatory drawing which shows the method which automatically generates the data corresponding to the evolved structure, and the output result. Similarly, it is an explanatory diagram showing the method of automatic generation of data corresponding to the evolved structure and the output result. It is explanatory drawing which shows the method of exchanging output data and outputting and displaying. It is explanatory drawing which shows the degeneration processing method of the mapped structure diagram. Similarly, it is explanatory drawing which shows the method of degeneration processing of a structural diagram. It is explanatory drawing which shows the output result of the data corresponding to the degenerated structure. Similarly, it is explanatory drawing which shows the output result of the data corresponding to the degenerate structure. It is explanatory drawing which shows an example of a special function file. It is a flowchart which shows the process of operation confirmation processing of the migration source and migration destination SQL in this system. It is explanatory drawing which shows an example of the screen display in operation confirmation processing. Similarly, it is explanatory drawing which shows an example of the screen display in operation confirmation processing. It is a flowchart of "representative pattern: creation / arrangement of SQL file" process of FIG. FIG. 36 is a flowchart of the “representative pattern: input of test data / creation / arrangement of correct answer data” process. It is a flowchart of "representative pattern: determination of representative SQL" processing of FIG. Similarly, it is a flowchart of "representative pattern: determination of representative SQL" processing. Similarly, it is a flowchart of "representative pattern: determination of representative SQL" processing. It is a flowchart of "representative pattern: test execution (current guarantee / evolution / degeneration)" process of FIG. Similarly, it is a flowchart of "representative pattern: test execution (current guarantee / evolution / degeneration)" processing. Similarly, it is a flowchart of "representative pattern: test execution (current guarantee / evolution / degeneration)" processing. Similarly, it is a flowchart of "representative pattern: test execution (current guarantee / evolution / degeneration)" processing. It is a flowchart of "special function: representative SQL determination" processing of FIG. It is a flowchart of "special function: test execution" processing of FIG. FIG. 36 is a flowchart of the “representative pattern / special function: result report creation” process of FIG.

Hereinafter, embodiments of the database migration support system and the program according to the present invention will be described with reference to the drawings.
Here, the database migration support system of the present invention shown below is realized by the processes, means, and functions executed by the computer according to the instructions of the program (software). The program can send a command to each component of the computer to perform predetermined processing, functions, and the like according to the present invention shown below. That is, each process, means, and function in the present invention is realized by concrete means in which a program and a computer cooperate.

All or part of the program is provided by, for example, a magnetic disk, an optical disk, a semiconductor memory, or any other computer-readable recording medium, and the program read from the recording medium is installed and executed in the computer. To. The program can also be loaded and executed directly on the computer via a communication line without going through a recording medium.
Further, this system can be configured by a single information processing device (for example, one personal computer or the like), or can be configured by a plurality of information processing devices (for example, a group of a plurality of server computers).

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

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

Here, if the DBMS and hardware configurations of the migration source database 100 and the migration destination database 200 are the same, the operation at the migration destination is basically performed by using the migration source SQL only for migrating the data. It is guaranteed, and the operation test should be done confirmatively.
Therefore, in this system 10, when the DBMS and hardware configurations are different, especially in 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 maturity of the implemented DBMS optimizer. It is suitable for database migration support when there is a difference in the degree level, when the hard disk configuration is different between the shared disk type and the distributed disk type between the migration destination and the migration source.
However, even if the database configuration of the migration source / migration destination is the same, it is of course possible to apply this system 10 to check the operation of the migration destination and the expansion / change function.

In this system 10, as shown in FIG. 1, the executed SQL information and data are acquired from the migration source database 100 currently in operation, and the data is input to the migration destination database 200, and the same SQL is obtained. It is designed to check whether the same result is output and whether it operates with the same performance (for example, whether the CPU, memory usage rate, processing time, etc. are the same).
If the system 10 can communicate with both the migration source database 100 and the migration destination database 200, the operation confirmation process as described above can be automatically executed.
Further, when the system 10 cannot communicate with the migration source database 100, for example, when the migration source database and the migration destination database are provided by different system integrators, the SQL / data for checking the operation is used by the user or the like. By manually preparing the system 10, the operation confirmation process in the system 10 can be operated.

Here, the SQL running in the migration source database 100 targeted by the system 10 is complicated, for example, the SQL of 400 rows or more, the number of used tables of 30 or more, etc. is close to 100,000. It may be composed of a huge amount of character data (source code).
It takes a lot of labor and time to prepare operation confirmation data and correct results for SQL of such scale, and it is difficult or impossible to verify all of SQL, and in reality, how many. The reality is that you cannot check.
In response to such issues, this system 10 patterns the target SQL and enables efficient operation confirmation, thereby realizing safe / secure database migration and predicting the project construction period. It is possible to prevent a long period of time and deterioration of quality.

Specifically, in this system 10, by inputting the SQL in operation from the migration source database 100, the following three processes of "SQL patterning", "evolution test", and "report output" are roughly performed. Is to be executed.
[SQL patterning process]
The SQL is patterned to minimize the number, thereby improving the efficiency of the test.
Specifically, in the patterning process, the following three processes are roughly executed.
(1) 4 categories (SELECT / UPDATE / INSERT / DELETE)
This is a process of classifying and extracting SQL according to four basic syntaxes (SELECT, UPDATE, INSERT, DELETE).
(2) Creation of structural diagram This is a process in which an image showing a predetermined structural diagram is applied and replaced for each classified basic syntax, and character data is converted into image data.
(3) Convergence processing This is a processing to reduce or reduce the entire structural drawing by deleting the same / overlapping structure of the generated structural drawing.

[Evolution test process]
(1) Test This is a process of converting the astringent structural diagram into the original SQL character data and executing the test.
(2) Automatic generation of evolved SQL This is a process to automatically generate evolved SQL by evolving the astringent structural diagram. By testing this evolution SQL, it is possible to perform evolution tests such as application confirmation that predicts future development in the future.
(3) Applied test by automatic data amplification corresponding to evolution SQL This is a process to automatically generate virtual data corresponding to evolution SQL, and the evolution test can be executed efficiently.

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

[Functional configuration]
In order to execute each of the above processes, the system 10 has a database language input unit 11, a structural diagram creation unit 12, a convergence processing unit 13, an evolution / degeneration processing unit 14, and a representative pattern, as shown in FIG. It is configured to function as each of the generation unit 15, the representative pattern test execution unit 16, the special function test execution unit 17, and the report output unit 18.
The database language input unit 11 inputs SQL which is a database language sentence created in a predetermined database language implemented in the DBMS of the migration source database 100.
The structural 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 structural diagram of the SQL. In this system 10 that can convert and create such SQL into a structural diagram, verification / testing based on the representative SQL as shown below can be automatically performed, and for example, a system engineer who manages the system, etc. It is also possible for human beings to reduce the number of tests by visually checking and checking the structural drawing.

The astringent processing unit 13 searches for whether or not the structural diagram created by the structural diagram creating unit 12 contains the same structure, and if the same structure is included, discards one of them to obtain a structural diagram. Converge.
The evolution / degeneration processing unit 14 adds a structure to the structural diagram converged by the astringent processing unit 13 to evolve it, or deletes the structure to degenerate it.

The representative pattern generation unit 15 replaces the structural diagram converged by the astringent processing unit 13 with the original character string, and generates a representative SQL (representative database language sentence) patterned to the minimum number.
Further, the representative pattern generation unit 15 replaces the structural diagram evolved or degenerated by the evolution / degeneration processing unit 14 with a character string to generate 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 converting SQL into a structural diagram, in order to verify and evolve / degenerate the SQL, the SQL statement is generated by reverse conversion from the structural diagram, but the structure is first generated from the SQL statement. Since it is converted to a figure, by recording the original SQL statement, verification and evolution / degeneration can be performed by reading the recorded original SQL without performing the inverse conversion of the structural diagram. 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, the relevant special function is included. Execute a test using predetermined correct answer data for special functions.
The report output unit 18 outputs the test results executed by the representative pattern test execution unit 16 and the special function test execution unit 17.
The details of the processing / operation for supporting the migration of the database executed by the respective parts 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. 2 to 35.
[SQL patterning]
FIG. 2 schematically shows the SQL patterning algorithm 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 from the source code constituting the SQL in units of "1 file: 1SQL". 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 "SELECT system", "UPDATE system", "INSERT system", and "DELETE system", and a predetermined SQL storage directory (storage means). ) Will be stored. It should be noted that this processing can be speeded up by performing the processing in parallel for each directory.
Next, the character data of the SQL statement sorted by syntax type is replaced and converted into a predetermined structural diagram one file at a time (see FIGS. 14 to 20), and a structural diagram file is generated. (Structural drawing creation unit 12).
Then, the generated structural diagram data is converged / reduced by astringent processing using an inclusion relationship (see FIG. 3) described later (convergence processing unit 13), and a "representative pattern" is generated from a huge amount of character data. The original SQL is. It will be patterned into representative pattern data consisting of astringent structural diagrams (representative pattern generation unit 15).

[SQL pattern convergence]
FIG. 3 schematically shows an algorithm for astringent processing of the structural diagram as described above.
As shown in the figure, the astringent treatment is performed by the inclusion relationship of the structural diagram.
Specifically, regarding a plurality of structures included in the structural diagram, if the structures are the same, the structural diagram is converged by including (discarding) one of them as overlapping structures.
For example, in the example shown in FIG. 3, the case where the "UNION" structure is two can be included in the case where the "UNION" is three.
In this way, paying attention to the same structure in the structural diagram, the target structure that can be included (discarded) is selected.

[test]
Then, the operation confirmation test can be performed based on the structural diagram converged as described above (representative pattern test execution unit 16).
As shown in FIG. 4, the operation confirmation test is performed on the SQL that has been converged and patterned by the structural diagram. As a result, it is sufficient to perform a test on the astringent / reduced SQL as compared with the original enormous SQL, and the correct answer data showing the correct result of the test can be dealt with with a small amount of data. On the other hand, if you want to test the original SQL, 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. Is.
The correct answer data of the test can be stored in a table, for example, and can be prepared in advance in a storage means of the system 10.

[SQL pattern evolution / degeneration]
Then, the patterned SQL can be evolved / degenerated according to the result of the test processing as described above (evolution / degeneration processing unit 14).
Specifically, first, as a result of the test, if the same correct result as the current migration source database 100 is obtained in the migration destination database 200, the SQL can be "evolved". This makes it possible to predict future development in the migration destination database 200.
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". This makes it possible to predict and generate SQL that produces correct results 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 by automatic data amplification is performed. (Evolution test) can be executed.

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

(1) By adding the same structure horizontally in a structural drawing that extends horizontally by UNION, the same function as the structure can be added or expanded.
As a result, for example, "business system A" and "business system B" in which the current system manages data of three items of "purchaser", "purchase amount (yen)", and "payment deadline" as attribute information. ], And when the three functions of "business system C" are implemented, it is possible to further add / expand the functions of a new service / new business system such as "business system D".
FIG. 6 shows an example of the output result when the SQL structure is laterally evolved by UNION (report output unit 18).

(2) In the structure diagram that extends vertically by JOIN, by adding the structure of the stored data vertically, the attributes of the management data can be added / added, and the number of JOINs can be increased.
As a result, for example, "business system A" and "business system B" in which data of three items of "purchaser", "purchase amount (yen)", and "payment deadline" are managed as attributes of management data. , "Business system C" can be given / added new attributes such as "gender: male or female", respectively.
FIG. 7 shows an example of the output result when the SQL structure is vertically evolved by JOIN.

(3) The display order can be changed / adjusted by changing the horizontal position of the same structure in the structural drawing that is intervened by adjusting the display order. As a result, for a plurality of functions having the same structure, the display position of any function can be moved and, for example, can be inserted between the functions previously displayed.
FIG. 8 shows an example of the output result when the display order is changed. In this example, "business system A", "business system B", and "business system" that manage three item data of "purchaser", "purchase amount (yen)", and "payment deadline" as management data. This is the case where the display order of "C" is replaced with "business system A", "business system C", and "business system B".

[Degenerate]
On the other hand, when the SQL is degenerated, as shown in FIG. 9, the SQL structure diagram of the patterned SQL can be changed (degenerated) by the following two patterns similar to the evolution described above.
(1) In a structural drawing in which UNION is shaved and shrunk horizontally, the function of the structure can be reduced or degenerated by deleting a part of the same structure arranged side by side.
As a result, for example, when the current system implements the 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 be done.
FIG. 10 shows an example of the output result when UNION is laterally degenerated for the SQL structure.

(2) Shrinking JOIN vertically In the structure diagram, by deleting a part of the structure of the stored data arranged vertically (deleting the JOIN table), the attributes of the management data can be reduced or degenerated.
Needless to say, even when SQL is degenerated, the display order can be changed and adjusted by changing the horizontal position of the same structure in the structural diagram as in the case of evolution described above. ..

[Structural decomposition of SQL / Creation of structural diagram]
Next, structural decomposition and creation of a structural diagram will be described in which the source code constituting the above-mentioned SQL is distributed according to the syntax type of SQL.
As described above, the structural decomposition of SQL is executed by focusing on the four syntaxes of "SELECT system", "UPDATE system", "INSERT system", and "DELETE system" according to the basic syntax of the SQL statement.
Basically, "SELECT ~;", "UPDATE ~;", "INSERT ~;", "DELETE ~;" in the source code are extracted as one structure unit, and the syntax is set for each command contained therein. Is extracted and decomposed, and each of the decomposed elements is replaced and converted into a predetermined structural diagram image (FIGS. 14 to 20).

Here, since the SELECT system syntax can describe a structure that is not found in other syntaxes (UPDATE system, INSERT system, DELETE system), first, the structure peculiar to the SELECT statement will be described with reference to FIGS. 11 to 13. do.
11 to 13 show explanatory views when the structure of the SELECT statement is decomposed.
As shown in these figures, the SELECT statement has three syntax patterns.
Shown in FIG. 11 is a syntax that can be nested internally.
In the example shown in FIG. 11, when there are two data tables, "employee table" and "salary table", as database management data, for example, an employee number of "monthly salary of 300,000 yen or more" is selected, and then the employee number is selected. So, it is a SELECT statement that the employee names are listed using the employee numbers and finally sorted in the order of the employee numbers.
In such a case, include the syntax of the "nested structure" described in the "(~)" of the "WHERE column name = (~)" in the SELECT statement (the part surrounded by the thick line in FIG. 11). Then, it is extracted as the structure of one SELECT statement.

Shown in FIG. 12 is a syntax that can temporarily create an intermediate table and produce results.
In the example shown in FIG. 12, when there are two data tables, "employee table" and "retirement details", as database management data, for example, "retirement date" is specified and the employee name is acquired. It is a sentence.
In such a case, the syntax described in "LEFT OUTER JOIN" (the part surrounded by the thick line in FIG. 12) is included in "FROM to WHERE" and extracted as the structure of one SELECT statement. ..

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

[Creating a structural drawing image]
Then, for the SQL statement extracted as one structure according to the basic SQL syntax (SELECT, UPDATE, INSERT, DELETE) as described above, the syntax is extracted and decomposed for each command contained therein, and the decomposed elements. A structural diagram showing the structure of SQL is created by replacing and converting each with a predetermined structural diagram image.
The images constituting the predetermined structural diagram shown below shall be stored and stored as image data in advance in the storage means of the system 10.

[SELECT statement]
FIG. 14 is a case where the SELECT statement shown in FIG. 11 is converted into a predetermined structural diagram.
As shown in the figure, the SELECT statement in this case is created as one structural diagram by replacing each element of the SELECT statement with a predetermined image by the following procedure.
(1) Write the "drum mark" on the far right to mean "the innermost table" in the syntax.
(2) Write "single parentheses" on the left side of the "drum mark" to mean "extract records that meet the conditions".
(3) Write an "arrow" on the left side of the "single parenthesis" to mean "using the result".
(4) Write the "drum mark" on the left side of the "arrow" to mean "concatenate with the data in another table".
(5) Enter "single parentheses" on the left side of the "drum mark" to mean "extract the corresponding record or column".
By the above processing / procedure, the SELECT statement shown on the left side of FIG. 14 (FIG. 11) is replaced / generated as a structural 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 structural diagram, and the SELECT statement is replaced and created as a structural diagram by the following procedure.
(1) Two "drum marks" indicating "tables to join" are arranged vertically on the rightmost side in the order of table names.
(2) Enter the "JOIN type" and "arrow" on the left side of the corresponding "drum mark".
(3) Place the "drum mark" indicating the "Join destination table" on the left side of the "arrow".
(4) Write an "arrow" on the left side of the "drum mark" to mean "using the result".
(5) Write the "drum mark" on the left side of the "arrow" to mean "concatenate with the data in another table".
(6) Enter "single parentheses" on the left side of the "drum mark" to mean "extract the corresponding record or column".
By the above processing / procedure, the SELECT statement shown on the left side of FIG. 15 (FIG. 12) is replaced / generated as a structural 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 structural diagram, and the SELECT statement is replaced and created as a structural diagram by the following procedure.
(1) The "drum mark" and "single parenthesis" indicating the first SELECT statement are placed on the far right, and they mean "the result (set) output above (before) the UNION clause". Enclose with a "square frame".
(2) Connect the "horizontal line" to the left side of the "square frame" to mean "make a union", and describe the "UNION type".
(3) The "drum mark" and "single parenthesis" indicating the second SELECT statement are placed on the far left, and they mean "the result (set) output under (after) the UNION clause". Enclose it in a "square frame" and connect it to the left side of the "horizontal line".
Since there are "UNION" and "UNION ALL" as commands that can "create the union of the results", when generating a structural diagram, assign an image that can distinguish between them. To.
By the above processing / procedure, the SELECT statement shown on the left side of FIG. 16 (FIG. 13) is replaced / generated as a structural diagram shown on the right side of FIG.

[INSERT statement]
FIG. 17 shows a case where the INSERT statement is converted into a predetermined structural diagram, and the INSERT statement is replaced and created as a structural diagram by the following procedure.
(1) Write the "drum mark" on the far right, meaning "data insertion destination table".
(2) Place the "arrow from left to right" indicating "data insertion work" on the left side of the "drum mark".
(3) Place the "black circle" I "character" indicating "INSERT", which means "base point for data insertion", on the left side of the "arrow".
By the above processing / procedure, the INSERT statement shown on the left side of FIG. 17 is replaced / generated as the structural diagram shown on the right side of FIG.

FIG. 18 shows a case where an INSERT statement capable of inserting data searched from another table is converted into a predetermined structural diagram as an INSERT statement, and the INSERT statement is replaced as a structural diagram by the following procedure. -Created.
(1) Write the "drum mark" on the far right, meaning "data insertion destination table".
(2) Place the "arrow from left to right" indicating "data insertion work" on the left side of the "drum mark".
(3) Place the "black circle" I "character" indicating "INSERT", which means "base point for data insertion", on the left side of the "arrow".
(4) Enter "single parentheses" (in the opposite direction of the SELECT statement) to the left of the "black circle" I "character" to mean "extract the corresponding record or column".
(5) Write the "drum mark" on the left side of the "single bracket" to mean "a table that stores the original data used for the data insertion destination".
By the above processing / procedure, the INSERT statement shown on the left side of FIG. 18 is replaced / generated as the structural diagram shown on the right side of FIG.

[UPDATE statement]
FIG. 19 shows a case where the UPDATE statement is converted into a predetermined structural diagram, and the UPDATE statement is replaced and created as a structural diagram by the following procedure.
(1) Enter the "drum mark" on the far right to mean "data update destination table".
(2) Place the "arrow from left to right" indicating "data update work" on the left side of the "drum mark".
(3) Place the "black circle" UP "character" indicating "UPDATE" in the meaning of "data update base point" on the left side of the "arrow".
By the above processing / procedure, the UPDATE statement shown on the left side of FIG. 19 is replaced / generated as the structural diagram shown on the right side of FIG.

[DELETE sentence]
FIG. 20 shows a case where a DELETE statement is converted into a predetermined structural diagram, and the DELETE statement is replaced and created as a structural diagram by the following procedure.
(1) Write the "drum mark" on the far right, meaning "data deletion destination table".
(2) Place the "arrow from left to right" indicating "data deletion work" on the left side of the "drum mark".
(3) Place the "black circle" D "character" indicating "DELETE" in the sense of "base point for data deletion" on the left side of the "arrow".
By the above processing / procedure, the DELETE statement shown on the left side of FIG. 20 is replaced / generated as a structural diagram shown on the right side of FIG. 20.

[Structural diagram mapping / convergence]
Next, the mapping / convergence processing of the structural diagram created as described above will be described.
21 and 22 are explanatory views showing a method of mapping the generated structural diagram, FIG. 21 is a case where the structural diagram is a SELECT statement, and FIG. 22 is a case where the structural diagram is an INSERT statement.
As shown in these figures, mapping is performed by preparing, for example, a "square map" having "1001 squares x 1001 squares" as an initial value, and pasting 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 not enough squares.
Further, the data of such a grid map is also stored and stored in advance as image data in the storage means of the system 10 together with the image data of the above-mentioned structural diagram.

[Mapping rule]
The mapping process is executed based on the following rules.
First, the SELECT statement has the following rules (see FIG. 21).
[SELECT statement rule]
・ Start writing from the center of the right edge ・ Symbol: 1 in 1 cell ・ "SELECT" is arranged in order from right to left ・ "JOIN" is arranged in order from top to bottom ・ Result set is the minimum size including all symbols Enclose in a thick frame ・ Arrow: Connect the symbols with one horizontal square (the connection point is in the middle)
In order to distinguish the "JOIN" type, when "JOIN" is used, enter the classification below the arrow- "JOIN": enter "J"-"LEFT OUTER JOIN": "LJ"
-"RIGHT OUTER JOIN": "RJ"
-"FULL OUTER JOIN": "FJ"
-The union that creates the union is connected by a horizontal line. In order to distinguish the "UNION" type, describe the division under the horizontal line-"UNION": "U"-"UNION ALL": "UA"
・ Color is expressed in black and white

Similarly, the following rules are applied to the INSERT statement, UPDATE statement, and DELETE statement (see FIG. 22).
[INSERT statement, UPDATE statement, DELETE statement rule]
・ Start writing from the center of the right edge ・ Symbol: 1 in 1 square ・ Arrange in order from right to left ・ Enclose the result set in a thick frame of the minimum size that includes all symbols ・ Arrow: 1 square horizontally between symbols Connect with (the connection point is in the middle)
Describe from left to right ・ Use single brackets when extracting values from the table (opposite to SELECT ・ Describe the classification at the base point to distinguish INSERT / UPDATE / DELETE- "INSERT": "I"-"UPDATE":"UP"
-"DELETE":"D"
・ Color is expressed in black and white

According to the mapping rule as described above, the astringent processing of the structural diagram is executed, and the patterned representative SQL is determined.
Convergence of the structural drawing is performed by round-robin by image processing. By using such an image processing technique, it becomes possible to execute the convergence of the structural drawing at high speed and accurately.
[Preprocessing]
First, as a "pretreatment" of convergence, a process of removing the filler from the map is executed as shown in FIG. 23.
Specifically, the margins other than the "thick line" shown in FIG. 23 are deleted.
However, the "horizontal line" and "black circle" indicating "UNION" should not be deleted.

[Main processing]
Next, as the "main process" of the convergence, as shown in FIGS. 24 and 25, "round-robin" by image processing and "check of inclusion relationship" based on the "round-robin" are executed.
First, the "main process" is executed by the following procedure (see FIG. 24).
(1) Execute processing for each directory of SELECT system / UPDATE system / DELETE system / INSERT system.
(2) Sort in the order of the squares used
(3) Extract the most used structural diagram with few squares and use it as a comparison target.
(4) Search the structural diagram selected in "(3)" for the same structure in the structural diagram with the largest grid (see FIG. 25).
(5) If the same structure is found, the structure diagram selected in "(3)" is discarded as being included in the upper SQL.
(6) Select the structural diagram with the largest number of squares used next to the structural diagram selected in "(3)", and execute the same processing as in "(3) to (5)".
(7) If the structure "included" is not found by the process of "(3)-(5)", change the comparison target to the second largest structure diagram and execute the same process. 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 the above “(3) to (5)”.
As shown in the figure, the inclusion relationship search is performed by the following procedure.
(1) Arrange the structural diagram to be included in the inclusion check according to the upper left of the squares (2) Move the target structural diagram to the right one by one and check if there is the same structure (3) Target structure When the figure reaches the right end of the square, move it down one square at a time (4) Move the target structure diagram one square at a time from the left to the right of the square again, and perform the same check to the right of the square. When it reaches the bottom, it ends

By repeating the above processing, the structural diagrams included in the other structural diagrams are discarded, only the largest (complex) structural diagram including all the structural diagrams is extracted, and the structural diagram is the representative SQL. Will be determined as.
Then, the structural diagram showing the representative SQL determined in this way can be converted / restored to the character data (source code) of the original SQL, 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 that is converged and patterned by the structural diagram as described above, and is faster and more efficient than the original enormous SQL. Tests can be executed, and the correct answer data of the test can be dealt with with a small amount of data.

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

[Evolution processing]
26 to 30 show the method of evolution processing of the structural diagram.
FIG. 26 shows a case where evolution (see FIG. 5) that “extends laterally” is performed by “UNION”, which is the first evolution process.
Specifically, when the structural diagram contains the symbol "U" or "UA" indicating "UNION", as shown in FIG. 26, the existing structure connected by the "UNION" is displayed in squares. Shift to the left by half the squares above.
After that, the same structural drawing is generated by copying, pasted on the right side of the shifted existing structure, connected by "UNION", and the structural drawing is saved.
As a result, structural expansion (evolution) is performed in the lateral direction.
By repeating the same process, any number of lateral expansions can be performed.

FIG. 27 shows a case where “vertically extending” evolution (see FIG. 5) is performed by “JOIN”, which is the second evolution process.
Specifically, when the structural drawing includes the symbols "J", "LJ", "RJ", and "FJ" indicating "JOIN", as shown in FIG. 27, the "JOIN" indicates the vertical direction. An image of a similar "drum mark" is placed under the "drum mark" indicating the "data" placed in, and connected by "JOIN" to save the structural drawing.
As a result, structural expansion (evolution) in the vertical direction is performed.
By repeating the same process, any number of vertical expansions can be performed.

FIGS. 28 and 29 show a method of automatically generating data to be stored and correct answer data when the structural diagram is evolved in the horizontal and vertical directions as described above, and FIG. 28 shows an extension in the horizontal direction. , FIG. 29 is a case of vertical expansion.
As shown in FIG. 28, when the lateral expansion is performed, the data corresponding to the copy source structure is copied as the data to be stored in the expanded 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". Do not change the numerical items.
By doing so, as shown in the "assumed result" of FIG. 28, the data to be stored in the extended structure and the correct answer data are automatically generated.
In addition, when a plurality of extended structures are provided, there is a possibility of overflowing digits just by adding the above-mentioned "1". Therefore, when further expanding the structure, "2" is added to the data of the next extended structure. Can be dealt with by assigning and then increasing the numbers in sequence.

As shown in FIG. 29, when the vertical expansion is performed, the data of the previous (upper) structure (table) is copied as the data to be stored in the expanded structure. At that time, for example, the last character is replaced with "1" for the character item of the delivery data as in the case of the horizontal direction. Do not change the numerical items.
By doing so, as shown in the "assumed result" of FIG. 29, the data to be stored in the extended structure and the correct answer data are automatically generated.
In the case of this extension in the vertical direction as well as in the case of the horizontal direction, when the structure is further expanded, "2" is added to the data of the next expansion structure, and the numbers are sequentially increased thereafter. can.

FIG. 30 shows the output result of data in the case of performing evolution (see FIG. 5), which is the third evolution process, “intervening by adjusting the display order”.
In this case, as shown on the left side of FIG. 30, the column names (for example, "column name 1, column name 2 ...") in the source code converted from the structural diagram to the original character data are replaced to display the data. You can change the order.
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 replaced so that "business system B" is displayed at the top. Become.
It should be noted that "in between" is expressed as "in between" because the resulting record set may be in between if there are three or more "columns" in the sort.

[Degradation processing]
31 to 34 show a method of degenerating the structural diagram.
FIG. 31 shows a case where the first “UNION” of the degeneration process is removed to cause “horizontally shrinking” degeneration (see FIG. 9).
Specifically, when the structural diagram contains the symbol "U" or "UA" indicating "UNION", as shown in FIG. 31, the leftmost of the existing structure connected by the "UNION". Remove the placed structure, the symbol indicating "UNION" and the "horizontal line".
As a result, structural reduction (degeneration) is performed in the lateral direction.
By repeating the same process, any number of lateral reductions can be performed.

FIG. 32 shows a case where the second “JOIN” of the degeneration process is removed to cause “vertically shrink” degeneration (see FIG. 9).
Specifically, when the structural drawing includes the symbols "J", "LJ", "RJ", and "FJ" indicating "JOIN", as shown in FIG. 32, the "JOIN" indicates the vertical direction. Delete the bottom "drum mark" among the "drum marks" indicating "data" placed in.
As a result, the structure is reduced (degenerated) in the vertical direction.
By repeating the same process, any number of vertical expansions can be performed.

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 the reduction in the horizontal direction, and FIG. 34 shows the reduction in the vertical direction. This is the case.
When the reduction is performed in the horizontal direction, for example, as shown in the "assumed result" of FIG. 33, the output results of "business system A" and "business system B" are displayed before degeneration. Only "Business system A" will be output and displayed.
When reduction is performed in the vertical direction, as shown in FIG. 34, for example, data of three items of "purchaser", "purchase amount (yen)", and "payment deadline date" as attribute information before degeneration. Is displayed as an output result, but the item in one column ("payment deadline date") disappears and is not displayed.
In this case, it is important for SQL feasibility assurance that the search result record set does not change even if the number of "JOINs" decreases.

[Special function test]
Next, the special function test will be described with reference to FIG. 35.
As described above, in this system 10, the operation of the migration destination database 200 is performed by creating a structural diagram showing the structure of SQL, selecting and determining a representative SQL pattern by its convergence, and performing a test based on the representative SQL pattern. It is designed to provide a guarantee.
The patented function test can be executed as a finish / complement of such a representative pattern test, and the operation check of the special functions included in the SQL, for example, the special control and the function (for example, SUM, AVG, etc.) as shown in FIG. 35. The patented function test, which executes and guarantees the above, searches whether or not a predetermined special function is included in the SQL character string input to the system 10, and if the predetermined special function is included. Executes 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 Microsoft's "Excel", 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 for each.
As the 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.

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

[motion]
Next, the specific processing / operation (implementation of the database migration support method) in the system 10 as described above will be described with reference to FIGS. 36 to 50.
[Overall process]
FIG. 36 shows a flowchart showing the entire process of the operation confirmation process of the system 10.
As shown in the figure, in this system 10, processing is performed by 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, for "operation check by searching for a representative pattern", "Create / arrange SQL file: manual" → "determine representative SQL: automatic" → "input test data / create / arrange correct answer file: manual" → "Test execution (current guarantee / evolution / degeneration): automatic" → "result report creation: automatic" is executed in this order.
The term "manual" during processing means that, for example, a user, an administrator, an operator, or the like of this system 10 manually (manually / operates) selects / specifies / inputs data.
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.

[Checking the operation of special functions]
Next, for "operation check by searching for special functions", "Create / arrange SQL file: manual" → "determine representative SQL: automatic" → "input test data / create / arrange correct answer file: manual"">" Test execution (current guarantee): Automatic "→" Result report creation: Automatic "is executed in this order.
Note that "manual" and "automatic" during processing are the same as the above "operation check by searching for a representative pattern".
In addition, if "SQL file creation / placement" and "test data input / correct answer file creation / placement" have already been manually performed in "Search for representative pattern and check operation", the data is used. Can be reused.

[System screen configuration]
37 and 38 show an example of screen display in the operation confirmation process of the system 10.
In the system 10, when the operation confirmation process shown below is executed, a display means serving as a user interface of the operator, for example, a liquid crystal display connected to the system 10, is as shown in FIGS. 37 and 38. The display screen is generated and displayed. Then, when the operator performs an input operation along the display screen, each step of the operation confirmation 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 the initial screen of the system 10.
On this screen, as shown in FIG. 37, when "operation check execution" is selected in response to the input operation of the operator according to the screen display, the transition to "1. system execution format selection screen" is performed and ". If "View execution record" is selected, the screen transitions to "5. View execution record screen".
[1. System execution format selection screen]
This screen is a screen that transitions when "operation confirmation execution" is selected in "0. system startup screen", and displays the system execution format in the system 10 so as to be selectable.
Specifically, as shown in FIG. 37, the "automatic execution selection button" and the "manual execution selection button" are displayed in a selectable manner on this screen, and either execution format is selected according to the input operation of the operator. Is selected and executed.

[2. Progress display screen]
This screen displays the progress of the operation confirmation process executed by the system 10, and the same contents as those in FIG. 36 described above are displayed.
On this screen, when the execution format selected / specified in the above-mentioned "1. System execution format selection screen" is "manual execution", a predetermined announcement screen is displayed.
On the other hand, when "automatic execution" is selected and executed, the completed step is highlighted in gray, for example, and the running step is highlighted in red, for example, according to the progress of the automatic execution process. The progress of the process is displayed.
Further, when all the processes are completed, the "Done" button is enabled, and when the "Done" button is pressed by the input operation of the operator, the screen returns to the above-mentioned "0. System startup screen".

[3. SQL file acquisition during manual execution]
This screen displays a screen prompting an operation to acquire an SQL file when the execution format selected / specified in the above-mentioned "1. System execution format selection screen" is "manual execution".
Specifically, as shown in FIG. 37, for example, an announcement display such as "Please specify the SQL file storage location ▼" is displayed, and when the "▼" button is pressed by the input operation of the operator, any arbitrary A screen where you can select a folder is displayed (not shown).
If a valid file is not specified here, it will not be accepted.
Further, after the file storage location is specified, the "Next" button is pressed to return to the above-mentioned progress display screen and proceed to the next step.

[4. Data input instruction screen]
This screen also displays a screen prompting you to input data to the migration destination database 200 and acquire the correct answer file when the execution format selected / specified in "1. System execution format selection screen" is "manual execution". It is a thing.
Specifically, as shown in FIG. 38, an announcement display such as "Please load the data into the migration destination database" or "Please specify the correct answer file storage location ▼" is displayed, and the operator's input operation is performed. When the "▼" button is pressed, a screen that allows you to select any folder is displayed (not shown). Also, if a valid file is not specified, it will not be accepted.
Then, after the file storage location is specified, the "Next" button is pressed to return to the above-mentioned progress display screen and proceed to the next step.

[5. Execution record viewing screen]
This screen is a transition screen when "operation confirmation execution" is selected in "0. system startup screen", and the execution record desired to be viewed is displayed in a selectable manner for the operation confirmation processing of this system 10. It is a thing.
Specifically, as shown in FIG. 38, a plurality of "dates and times" and "number of executions" of operation checks executed in the past in the system 10 are displayed on this screen so that they can be scrolled up and down with a slide bar, for example. The execution history of a plurality of cases (for example, about 5 cases) is always displayed on the screen. As the record of the execution history, a predetermined number of cases can be held, and for example, information on the execution history of up to 1000 cases can be held.
Then, as shown in FIG. 38, when the button corresponding to an arbitrary execution history is clicked and selected in response to the input operation of the operator, the center of the button is highlighted in black and the selected execution history is selected. Will be identified. In the case shown in FIG. 38, the execution history that can be selected is only one line (one case), but it is of course possible to configure so that a plurality of execution histories can be selected.
If the "Back" button is pressed, the screen returns to "0. System startup screen".

[6. Desired browsing process selection screen]
This screen displays the date / time / number of executions of the execution history selected on the previous screen (5. Execution record viewing screen), and "representative pattern" and "representative pattern" and "representative pattern" as the processing contents desired to be viewed in the execution history. Any one of "special functions" is displayed in a selectable manner.
Specifically, as shown in FIG. 38, as information indicating the selected execution history, for example, "January 1, 2018-12 o'clock first execution record" is displayed, and among the execution history, " Either "Search for a representative pattern and check the operation" or "Search for a special function to check the operation" will be displayed in a selectable manner.
Then, when the button corresponding to either the "representative pattern" or the "special function" is clicked according to the input operation of the operator, the center of the button is highlighted in black and the selected processing content is specified. Will be done.
After that, if the "view" button is pressed, the process transitions to the following "7. Report display screen".
If the "Back" button is pressed, the screen returns to "5. Execution record viewing screen".

[7. Report display screen]
This screen displays the corresponding report file (see FIG. 50 described later) for the execution record / processing content selected in the previous screen (6. Desired browsing process selection screen).
Although a specific screen display example is omitted, by displaying this report file, the user can view the contents and results of the operation confirmation process executed in the past.
Then, when the "Back" button is pressed, the screen returns to the initial screen "0. System startup screen".

[flowchart]
Next, the 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. 39 to 50.
[Representative pattern: SQL file creation / placement]
FIG. 39 shows a flowchart of the “representative pattern: SQL file creation / arrangement” process.
In this SQL file creation / placement process, as shown in FIG. 39, first, the progress display screen displayed on the display means (display or the like) of the system 10 is initialized (step 3901), and the operator inputs. 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 at the time of manual execution" screen (see FIG. 37) (step 3903), and the process is completed. In this case, as described above, in the SQL file, an arbitrary folder is selected and data is acquired according to the input operation of the operator (see FIG. 37).
On the other hand, when the execution format is "automatic", the "SQL file creation / placement" box on the transition screen (see FIGS. 36 and 37) is colored in red or the like (step 3904), and then the SQL file is automatically executed. The creation process is executed (steps 3905 to 3910).

To create 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 one SQL at a time.
The extraction process is repeated until the continuous control number reaches "100,000" (step 3907), the continuous control number used for the file name is added to "1" (step 3908), and the file name is changed to "mig_continuous". Output as "control number.txt" to the storage destination "INPUT-SQL file storage directory" (step 3909), and when the continuous management number reaches "100,000" (step 3907), the progress of the progress display screen proceeds. (Step 3910), the process ends.

[Representative pattern: input of test data / creation / arrangement of correct answer data]
FIG. 40 shows a flowchart of the “representative pattern: input of test data / creation / arrangement of correct answer data” processing.
In this data input / arrangement process, as shown in FIG. 40, first, it is determined whether the execution format of the operation confirmation 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 process is completed. In this case, the data input / correct answer file is arranged by selecting an arbitrary folder according to the input operation of the operator as described above (see FIG. 38).

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

Next, in the process of creating / arranging the correct answer file, the SQL files created in the previous process (see FIG. 39) are 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 the data for the following items are stored (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 answer" or the like immediately after the open result file name.
Then, the correct answer mapping file is searched by the open file name, the correct answer file name is described next to the corresponding correct answer file name (step 4009), and the process of creating and arranging the correct answer file is completed.
After that, the progress of the progress display screen is advanced (step 4010), and the process proceeds to the determination process of the next representative SQL.

[Representative pattern: Determination of representative SQL]
41 to 43 show a flowchart of the “representative pattern: determination of representative SQL” process.
As shown in FIG. 41, in this representative SQL determination process, first, the SQL is sorted into four categories of "SELECT, UPDATE, INSERT, 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 until the final file (step 4101).
Then, the SQL classification sorting subroutine is called, and the copy files are arranged in the four directories of SELECT / UPDATE / INSERT / DELETE (step 4102).

After that, as shown in FIGS. 42 and 43, the configuration diagram creation process is executed in four systems for each of the SELECT / UPDATE / INSERT / SELECTE syntax systems. By creating a structural diagram with such four multiplexes, it becomes possible to speed up the processing.
First, the SQL file is read for each file, and the reading process is repeatedly executed until the final file (step 4201).
Next, the subroutine for creating the SQL structure diagram is called, and the SQL structure diagram is generated from the SQL file (step 4202: FIGS. 14 to 20).
Although the specific contents of the subroutine are omitted, the subroutine creates a program capable of executing the above-mentioned SQL structure diagram creation algorithm (see FIGS. 11 to 34) by using a known programming technique. 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 executed up to the final file (step 4301).
Then, the subroutine about the SQL structure diagram convergence is called, and the representative SQL structure diagram is determined (step 4302: FIGS. 21 to 25).
Further, the SQL file before being structurally plotted is stored in the representative SQL directory with reference to the SQL mapping file (step 4302).
After that, the progress of the progress display screen is advanced (step 4303), and the process proceeds to the next test process by the representative SQL.

[Representative pattern: test implementation (current guarantee / evolution / degeneration)]
FIGS. 44 to 47 show a flowchart of the “representative pattern: test implementation (current guarantee / evolution / degeneration)” process.
In this test process, as shown in FIG. 44, first, SQL files are read one by one from the representative SQL directory, and the final file is repeatedly executed (step 4401). Here, first, a test is started as to whether or not the same result as that of the migration source database 100 can be obtained in the migration destination database 200.
Specifically, the subroutine of the current guarantee test is called and the test is executed (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, referring to the correct answer mapping file, and comparing with the correct answer.

Then, it is determined whether or not the test result of the migration destination database 200 matches the migration source database 100 (step 4403), and if the test results are the same, the "evolution" process / test (flow on the left side of the drawing). Is executed, and if the test results are different, the "degeneration" process / 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), and then it is determined whether or not "SELECT" exists by the appearance of the blank at the beginning of the first line (step 4405), and "SELECT" is displayed. If so, "1" is set as the number of evolutions / degenerations (step 4406). On the other hand, if there is no "SELECT", the evolution / degeneration process is not performed and the test process ends.

Subsequently, the evolution / degeneration subroutine is called, the evolution / degeneration SQL is generated (step 4407: see FIG. 26, FIG. 27, FIG. 31, FIG. 32), and the evolved / degenerated SQL is stored in the representative SQL directory. Evolved.
Next, the automatic data amplification subroutine is called, data is automatically created for the evolved / degenerated SQL, and the correct answer data is automatically registered in the correct answer mapping file (step 4408: FIG. 28, FIG. 29, FIG. 33, see FIG. 34).
After that, the evolution / degeneration test subroutine is called, the evolved / degenerated SQL is issued to the migration destination database 200, and the first evolution / degeneration is performed by referring to the correct answer mapping file and comparing it with the correct answer. The test is run (step 4409).

Then, it is determined whether or not the result of the evolution / degeneration test matches the migration source database 100 (step 4410), and if the test results match, the "second" evolution / degeneration process / test (each drawing). (Flow on the left side) is executed, and if 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 the migration source database 100, the progress of the progress display screen is advanced after being output to the representative pattern port file (step 4501) (step). 4502) The process is completed.
On the other hand, if the evolution / degeneration test result matches the migration source database 100, "2" is set as the number of evolution / degeneration after being output to the representative pattern port file (step 4503) (step 4504).
Then, as in 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 the test process are performed. Is executed.

After that, it is determined whether or not the result of the evolution / degeneration test matches the migration source database 100 (step 4508), and if the test results match, the "third" evolution / degeneration process / test (each drawing). (Flow on the left side) is executed, and if 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 result of the second evolution / degeneration test is different from that of the migration source database 100, the progress of the progress display screen is advanced 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, "3" is set as the number of evolution / degeneration after being output to the representative pattern port file (step 4603) (step 4604). ), After that, the evolution / degeneration subroutine (step 4605), the automatic data amplification subroutine (step 4606), and the evolution / degeneration test subroutine (step 4607) are called in the same manner, and the evolution / degeneration process and the test process are executed. ..

Then, for the third evolution / degeneration test, after it is determined whether or not 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 of the progress display screen is advanced (step 4702), and the process ends.
This is because it is possible to predict the system expansion due to evolution and detect the improvement of the system due to degeneration if the evolution / degeneration process is performed about three times.
However, it is of course possible to perform the evolution / degeneration process / test as described above 4 times or more or 2 times or less, and it is possible to execute any number of times as appropriate in consideration of the scale and cost of the system. can.

[Special function: determination of representative SQL]
FIG. 48 shows a flowchart of the “special function: determination of representative SQL” process.
In the representative SQL determination process of this special function, 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).
After that, it is confirmed whether or not the corresponding function exists in each SQL for each registered function (step 4802), and the 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), and if the corresponding function does not exist, the special function test ends.
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" or the like 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 feature function is determined.
After that, the progress of the progress display screen is advanced (step 4806), and the process is completed.

[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, first, the SQL file is read for each file from the above-mentioned special function file storage directory, and the test process is repeatedly executed until the final file (step 4901).
Then, the subroutine of the current guarantee test for the special function is called and the test is executed (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, referring to the correct answer mapping file for the special function, and comparing with the correct answer.
After that, it is determined whether or not the test result of the migration destination database 200 matches the migration source database 100 (step 4903), and 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.
As the report creation process of the test result of the representative pattern / special function, the process as shown in FIG. 53 is executed (steps 5001 to 5003).

[Representative pattern]
First, as the result report of the representative pattern, the following contents can be generated and output (step 5001).
(1) Representative pattern report file (summary part)
The following results can be output to this summary part.
・ The total number of SQL tests is counted up and the number is output.
-Search the "Current Warranty Test" column of the representative pattern report file with "OK" and output the number of hits.
-The response time in the "INPUT-SQL file name" column and the "current warranty test" column of the representative pattern report file is subtracted, and the number of negative values is output.
(2) Representative pattern report file (degradation test part)
The following results can be output to this degradation test part.
-Degeneration test of representative pattern report file Counts the number of SQLs for the third time and outputs the number of degenerations 3.
・ If not found, the number of SQLs for the second time is counted and the number of degenerates is 2 and the number is output.
・ If it is not found, the number of SQLs for the first time is counted and the number of degenerates is 1 and the number is output.
・ If not found, output 0 degenerates and 0 degenerates.
(3) Representative pattern report file (degradation test part)
The following results can be output to this evolution test part.
-Evolution test of representative pattern report file Counts the number of SQLs for the third time and outputs the number of evolutions 3 and the number.
・ If not found, count the number of SQLs for the second time and output the number of evolutions 2 and the number.
・ If it is not found, the number of SQLs for the first time is counted and the number of evolutions is 1 and the number is output.
-If not found, output 0 evolutions and 0 evolutions.

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

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

Then, when the above-mentioned report result job change processing is performed, the progress of the progress display screen is advanced (step 5004), and the processing is completed.

As described above, according to the present system 10, when migrating from the migration source database 100 to the migration destination database 200, the SQL running at the migration source is replaced with a predetermined image to be patterned and evolved / degenerated. This makes it possible to efficiently check the operation and perform tests such as expansion / modification of the system.
As a result, safe / secure database migration can be realized, and problems such as unexpected extension of the project construction period and quality deterioration can be effectively prevented.

Although the present invention has been described above with reference to 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, SQL has been described as an example of the database language targeted by the system 10, but the present invention can of course target a database language other than SQL.
That is, the present invention can be applied to any language as long as it is a database language that is applied to and issued to a database system and used to transfer data stored in the database.

Further, the algorithms (FIGS. 2 to 35) and flowcharts (FIGS. 36 to 50) shown in the above-described embodiments are examples for carrying out the present invention, and are not limited to the embodiments shown in the drawings. Of course, it is possible to adopt other algorithms or the like as long as the technical idea of the present invention can be realized.
Further, in the above-described embodiment, the description assumes a large-scale data system / SQL, but the present invention is not limited to the scale and configuration of the database including SQL, and can be applied to various databases to be migrated. It is something that can be applied.

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

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

Claims (5)

A system that supports database migration from the migration source database to the migration destination database .
A database language input unit for inputting a database language statement created in a predetermined database language, which is implemented in the database management system of the migration source database, and
It is provided with a structural diagram creation unit that decomposes a character string of a database language sentence input in the database language input unit, replaces it with a predetermined image, and creates a structural diagram of the database language.
Based on the structural diagram created by the structural diagram creation unit, the operation confirmation test of the database language statement in the migration destination database is executed.
A database migration support system characterized by this. The astringent processing unit that converges the structural drawing by searching whether the structural drawing created by the structural drawing creation unit contains the same structure and discarding one if the same structure is included. When,
It is provided with a representative pattern generation unit that replaces the structural diagram converged by the convergence processing unit with the original character string and generates a representative database language sentence patterned to the minimum number .
Based on the representative database language statement generated by the representative pattern generation unit, the operation confirmation test of the database language statement in the migration destination database is executed.
The database migration support system according to claim 1, wherein the database migration support system is characterized in that. A representative pattern test execution unit that executes a test using predetermined correct answer data for a representative database language sentence generated by the representative pattern generation unit, and a representative pattern test execution unit.
The database migration support system according to claim 2, further comprising a report output unit that outputs the test results of the representative pattern test execution unit. The structure diagram converged by the astringent processing unit is provided with an evolution / degeneration processing unit that adds a structure to evolve or deletes the structure to degenerate.
The representative pattern generation unit
The database migration support system according to claim 2 or 3, wherein the representative database language sentence is generated by replacing the structural 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, and if the predetermined special function is included, a predetermined correct answer is given 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.

Images