JP2009223851A - Method for migrating database system - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To provide a method of an appropriate preprocessing for a migration between database systems. <P>SOLUTION: An operating state of a first database system is analyzed to generate a table for conversion (S101). The table for conversion converts a first database connection-control character string having a connection character string including a first SQL sentence for a first database engine of the first database system and a database control character string, into a second database connection-control character string having a connection character string including a second SQL for a second database engine of the second database system and the database control character string. After the table for conversion is generated, it is used for migration of the first database system to the second database system. <P>COPYRIGHT: (C)2010,JPO&INPIT

Description

  The present invention relates to processing associated with migration of a database system.

  When a company that originally used a stand-alone database system in relation to the business scale intends to introduce a client / server database system as the business scale expands, or from an open source database system You may want to migrate the database system, such as when changing to a commercial database system. As a technique aiming to meet such a database system migration request, for example, there is one disclosed in Patent Document 1.

  As described in paragraph 0006 of Patent Document 1, usable SQL sentences differ depending on each database system. Therefore, the database connection character string and the database control character string (hereinafter referred to as “database connection / control character string”) including SQL statements such as modules and stored procedures included in the data source database system are left as they are. May not be applicable to other database systems. On the other hand, in the system of Patent Document 1, the database system is smoothly shifted by linking the GUI technology and the SQL sentence automatic conversion process.

JP 2002-351710 A

  In order to realize the system of Patent Document 1, it is necessary to enable automatic SQL sentence conversion processing. However, the automatic SQL sentence conversion process as illustrated is practically impossible without appropriate preprocessing. is there. However, Patent Literature 1 does not describe “appropriate preprocessing” as described above, and generally does not have a technique established as “appropriate preprocessing”.

  For example, the same company develops and sells two types of database systems: a stand-alone database system and a client / server database system. Furthermore, the transition from the former database system to the latter database system is a wizard. However, even after doing so, a complete migration process cannot be achieved without manual correction following the migration process error report after the migration process using the wizard. That is the situation. This is evidence that “appropriate preprocessing” as described above has not been established. In other words, if “appropriate preprocessing” is provided, automatic conversion processing of the existing database system also functions. Become.

  Accordingly, an object of the present invention is to provide a method for appropriate preprocessing for database system migration.

According to the present invention, as a first database system migration method, user data having a plurality of records, a first database engine, a database connection character string including a first SQL statement for the first database engine, and a database control character A first database system having at least a first database connection / control string having a column, the user data, a second database engine, and a database connection string including a second SQL statement for the second database engine; A database system migration method for migrating to a second database system having at least a second database connection / control character string having a database control character string,
A conversion table creation step of analyzing an operational state of the first database system and creating a conversion table for converting the first database connection / control character string into the second database connection / control character string;
A database system migration method comprising: a migration step of migrating the first database system to the second database system using the conversion table.

Further, according to the present invention, as the second database system migration method, in the first database system migration method,
The conversion table creation step includes:
An extraction step of extracting the first SQL sentence at runtime of the first database system;
An SQL statement conversion step of converting the extracted first SQL statement according to an SQL statement conversion table to obtain a converted SQL statement;
An execution result acquisition step of acquiring an execution result of the first SQL statement by the first database engine as a first execution result, and acquiring an execution result of the converted SQL statement by the second database engine as a second execution result. When,
An execution result determination step of comparing the acquired first execution result and the second execution result to determine whether or not they match;
If the first execution result and the second execution result do not match as a result of the execution result determination step, the SQL statement conversion table is updated so that the mismatch can be resolved, and the new SQL statement conversion is performed. A table update step for making a table, and as a result of the execution result determination step, the SQL statement conversion step, the execution result determination step, and the step until the first execution result matches the second execution result A database system migration method in which a table update step is repeatedly performed, and the SQL statement conversion table at the time when the first execution result and the second execution result coincide with each other as a result of the execution result determination step is used as the conversion table. can get.

According to the present invention, as the third database system migration method, in the second database system migration method,
The extraction step includes
A format conversion step for converting the format of the file including the database connection / control character string to obtain a converted file, wherein the format can confirm and change the contents of the database connection / control character string A format conversion step that is
The content of the converted file is confirmed to identify a portion related to the execution of the first SQL statement, and an extraction code for extracting the first SQL statement when executed is inserted into the specified portion. A code insertion step for modifying the converted file to generate a modified file;
A reverse format conversion step for generating an intermediate file having the same format as the file by performing reverse format conversion on the changed file;
There is obtained a database system migration method comprising: an intermediate file operation step of extracting the first SQL sentence by operating the intermediate file.

According to the present invention, as the fourth database system migration method, in the second or third database system migration method,
The first execution result and the second execution result are each obtained in the form of a record set,
The execution result judging step includes the number of records, the number of data fields, the data values of all fields of the first record, the data values of all fields of the last record, and a specific one between the first record and the last record. A database system migration method for comparing the first execution result and the second execution result for only five points of the data values of all the fields of the record is obtained.

According to the present invention, as the fifth database system migration method, in the fourth database system migration method,
The execution result determination step includes
If the first execution result and the second execution result do not match, the step of storing the first execution result and the second execution result that do not match the first SQL statement related to the mismatch as a mismatch result table When,
A specifying step for specifying a method for matching the first execution result and the second execution result based on the mismatch result table;
There is obtained a database system migration method comprising: a new table creation step of creating information on the rule obtained from the identified technique and adding the information to the SQL sentence conversion table to create the new SQL sentence conversion table.

Furthermore, according to the present invention, as a sixth database system migration method, the database system migration method according to any one of the first to fifth aspects, wherein the user data and the first database connection / control character string are In the case where a tool is included that is included in one file and assists the migration of the first database system to the second database system for the file,
The transition step includes:
An export step of exporting all objects contained in said file to a text file;
A text file conversion step of converting the first SQL sentence contained in the exported text file into the second SQL sentence using the conversion table;
An import step of importing the converted text file into the file;
A database system migration method is provided that includes a tool use step of migrating from the file after import using the tool.

Further, according to the present invention, as a conversion table creation method,
A conversion table creation method for creating a conversion table used for migration from a first database system to the second database system,
The first database system includes a first database connection having user data having a plurality of records, a first database engine, a database connection character string including a first SQL statement for the first database engine, and a database control character string. A control string and at least
The second database system includes the user data, a second database engine, a database connection character string including a second SQL statement for the second database engine, and a second database connection / control character string having a database control character string. Having at least
In the conversion table creation method, the conversion table is for converting the first database connection / control character string into the second database connection / control character string.
An extraction step of extracting the first SQL sentence at runtime of the first database system;
An SQL statement conversion step of converting the extracted first SQL statement according to an SQL statement conversion table to obtain a converted SQL statement;
An execution result acquisition step of acquiring an execution result of the first SQL statement by the first database engine as a first execution result, and acquiring an execution result of the converted SQL statement by the second database engine as a second execution result. When,
An execution result determination step of comparing the acquired first execution result and the second execution result to determine whether or not they match;
If the first execution result and the second execution result do not match as a result of the execution result determination step, the SQL statement conversion table is updated so that the mismatch can be resolved, and the new SQL statement conversion is performed. A table update step for making a table, and as a result of the execution result determination step, the SQL statement conversion step, the execution result determination step, and the step until the first execution result matches the second execution result A conversion table creation method in which a table update step is repeatedly performed, and the SQL statement conversion table at the time when the first execution result and the second execution result coincide with each other as a result of the execution result determination step is the conversion table. Is obtained.

  According to the present invention, since the runtime SQL statement in the migration source database system is acquired and the conversion table is created while verifying virtually all of the acquired SQL statements, A high conversion table can be obtained, and the database system can be reliably migrated using the conversion table.

  A part of the above-described database system migration method or conversion table creation method according to the present invention can be implemented in a program.

  Hereinafter, as a database migration method according to an embodiment of the present invention, a case where the migration source database system is Microsoft Access and the migration destination database system is also Microsoft SQL Server will be described in detail with reference to the drawings. explain. Microsoft Access provides a migration tool called “Upsizing Wizard” for migrating a database system configured with Access to SQL Server. In this upsizing wizard, the Access database file (mdb file) is divided into an Access project file (adp file) and a file for SQL Server. In addition to the user data, database connection / control character strings such as stored procedures and modules are also stored in the mdb file, but the user when the mdb file was executed by Access is stored in the Access project file. Only modules for realizing the interface are included, and user data and stored procedures are stored in a file for SQL Server. It should be noted that there is a difference in interpretable SQL statements between the Access database engine (Jet engine) and the SQL Server database engine. Due to the difference in the SQL sentence, a conversion error may occur when the upsizing wizard is executed. Conversion errors that have occurred can only be corrected manually by referring to the report output during conversion. On the other hand, in the embodiment described below, the SQL for Access included in the mdb file is used by using the conversion table obtained by analyzing the mdb file before performing conversion by the upsizing wizard. By converting the database connection / control character string including the SQL statement for the database connection / control character string SQL Server including the sentence in advance, a conversion error does not occur when the upsizing wizard is executed. That is, the database migration method according to the present embodiment is mainly composed of two phases as shown in FIG. The first phase is an analysis phase (conversion table creation phase) in which the conversion source database system is analyzed to create a conversion table (S101), and the subsequent phase is a database using the created conversion table. This is a transition phase for performing system transition (S102).

  In the analysis phase, as shown in FIG. 2, first, an SQL sentence issued to the Access database engine when the mdb file as the migration source is actually used is extracted (S201). In the present embodiment, more specifically, the mdb file as the migration source is converted into a predetermined format to obtain a converted file (S301). Here, the predetermined format is a format that allows the contents of the database connection / control character string to be confirmed and changed, and in the present embodiment, is a text file format.

  Next, the content of the converted file is confirmed, a portion related to the execution of the SQL statement for Access is specified (S302), and an extraction code is inserted into the specified portion (S303). This extraction code is used to cut out and output a corresponding SQL sentence when the mdb file is actually used, and is described in VBA in the present embodiment.

  After the extraction code is inserted into all the SQL statements included in the converted file to generate the changed file (“None” in S302), the reverse of the format conversion of S301 is performed on the changed file. An intermediate file is generated by performing reverse format conversion as a work (S304). As understood from this, the intermediate file has the same format as the mdb file that is the migration source, but the extraction code is inserted into the portion related to the execution of the SQL statement included in the mdb file. It has become a thing.

  After the intermediate file is generated, the SQL file is extracted when the mdb file is executed using the intermediate file (S305). An example extracted in this way is shown in FIG. In FIG. 4, what is described in the bottom line is the runtime SQL statement of the mdb file.

  Returning to FIG. 2, when the runtime SQL sentence is extracted (S201), the extracted SQL sentence is converted according to the SQL sentence conversion table to obtain the converted SQL sentence (S202). Here, the SQL sentence conversion table is as shown in FIG. 5, for example, and the converted SQL sentence is as shown in the bottom line of FIG. 6, for example.

  If the SQL statement conversion table at this point appropriately corresponds to all of the SQL statements included in the mdb file, this conversion results in no conversion error even in the migration process by the upsizing wizard. In order to examine it, first, the runtime SQL statement extracted in step S201 is executed by the Access database engine to obtain the first execution result, while the converted SQL statement obtained in step S202 is obtained from the SQL Server database. A second execution result is obtained by executing the engine (S203). The first execution result and the second execution result obtained in this manner are both record sets including a plurality of records.

  Next, the first execution result and the second execution result obtained in step S203 are compared, and it is determined whether or not they match (S204). Here, the syntax of the SQL statement is composed of four elements: an output field, a target table, a conditional statement, and a data order, so the number of records, the number of data fields, the data values of all fields of the first record, the final A comparison is made only for five points of the data values of all the fields of the record and the data values of all the fields of one specific record between the first record and the last record, and these are compared with the first execution result and the second If it can be confirmed that the execution results match, it is possible to confirm that the four elements of the SQL statement described above are the same without comparing all the data values of the record set.

  If there is data that does not match as a result of the comparison in step S204, a conversion rule that eliminates the data is specified, and the SQL statement conversion table is updated according to the content (S205). Using the SQL sentence conversion table, steps S202 to S204 are repeated until the determination results in step S204 match.

  More specifically, as shown in FIG. 7, regarding the processing in step S205, first, a mismatch result table is stored for data that did not match as a result of comparing execution results in step 204 (S701). . For example, as shown in FIG. 8, the mismatch result table stores a target SQL statement and a set of execution results thereby. When there is such a mismatch result table, it becomes easy to identify a problem. By examining this mismatch result table, a technique for eliminating the mismatch is identified (S702), and information obtained by making the identified technique into a rule is added to the SQL sentence conversion table (see FIG. 5). In the present embodiment, the method for resolving the inconsistency is manually performed. In this way, by repeatedly identifying the problem and updating the SQL statement conversion table to solve it, it is possible to confirm that all the execution results are the same in step S204. . In this way, when it can be confirmed that the first and second execution results are all the same, the SQL statement conversion table at that time can be used as a conversion table in the migration phase (S102).

  In the present embodiment, as described above, the migration source database system is Access and the migration destination database system is SQL Server. In the migration phase (S102), an upsizing tool is used for the mdb file. 9 is performed, it is possible to eliminate the occurrence of errors during conversion by the upsizing tool.

  Specifically, as shown in FIG. 9, first, the migration source mdb file 901 is exported to a text file 902, and the exported text file 902 is converted into a conversion table (SQL sentence conversion table created in the analysis phase). By converting 903 and DAO => ADO conversion table, and importing the converted text file 904 into the mdb file 905, the outline is an mdb file, but the included SQL sentence is that of SQL Server. A file is created. When an upsizing tool is applied to the mdb file 905 after the import, an appropriate adp file 906 and an SQL Server file are appropriately created.

  As described above, the present invention has been described by taking a specific database system as an example. However, the present invention is not limited to the above-described embodiment, and can be applied to migration between other database systems. .

It is a flowchart which shows the database system transfer method by embodiment of this invention. It is a flowchart which shows the detail of S101 of FIG. It is a flowchart which shows the detail of S201 of FIG. It is a table containing the SQL sentence extracted in S201 of FIG. It is a figure which shows the SQL sentence conversion table used in S202 of FIG. It is a table containing the SQL sentence after converting in S202 of FIG. It is a flowchart which shows the detail of S205 of FIG. It is a figure which shows the example of the mismatch result table preserve | saved in S701 of FIG. It is a figure which illustrates the process of S102 of FIG.

Explanation of symbols

901 mdb file 902 text file 903 conversion table 904 text file 905 mdb file 906 adp file

Claims (7)

User data having a plurality of records, a first database engine, a database connection character string including a first SQL statement for the first database engine, and a first database connection / control character string having a database control character string A first database system comprising: the user data; a second database engine; a database connection character string including a second SQL statement for the second database engine; and a second database connection / control character string having a database control character string A database system migration method for migrating to a second database system having at least
A conversion table creation step of analyzing an operational state of the first database system and creating a conversion table for converting the first database connection / control character string into the second database connection / control character string;
A database system migration method comprising: a migration step of migrating the first database system to the second database system using the conversion table. The database system migration method according to claim 1,
The conversion table creation step includes:
An extraction step of extracting the first SQL sentence at runtime of the first database system;
An SQL statement conversion step of converting the extracted first SQL statement according to an SQL statement conversion table to obtain a converted SQL statement;
An execution result acquisition step of acquiring an execution result of the first SQL statement by the first database engine as a first execution result, and acquiring an execution result of the converted SQL statement by the second database engine as a second execution result. When,
An execution result determination step of comparing the acquired first execution result and the second execution result to determine whether or not they match;
If the first execution result and the second execution result do not match as a result of the execution result determination step, the SQL statement conversion table is updated so that the mismatch can be resolved, and the new SQL statement conversion is performed. A table update step for making a table, and as a result of the execution result determination step, the SQL statement conversion step, the execution result determination step, and the step until the first execution result matches the second execution result A database system migration method in which a table update step is repeatedly performed, and the SQL statement conversion table at the time when the first execution result and the second execution result coincide with each other as a result of the execution result determination step is used as the conversion table. In the database system migration method according to claim 2,
The extraction step includes
A format conversion step for converting the format of the file including the database connection / control character string to obtain a converted file, wherein the format can confirm and change the contents of the database connection / control character string A format conversion step that is
The content of the converted file is confirmed to identify a portion related to the execution of the first SQL statement, and an extraction code for extracting the first SQL statement when executed is inserted into the specified portion. A code insertion step for modifying the converted file to generate a modified file;
A reverse format conversion step for generating an intermediate file having the same format as the file by performing reverse format conversion on the changed file;
A database system migration method comprising: an intermediate file operation step of extracting the first SQL sentence by operating the intermediate file. In the database system migration method according to claim 2 or 3,
The first execution result and the second execution result are each obtained in the form of a record set,
The execution result judging step includes the number of records, the number of data fields, the data values of all fields of the first record, the data values of all fields of the last record, and a specific one between the first record and the last record. A database system migration method for comparing the first execution result and the second execution result for only five points of data values of all fields of a record. The database system migration method according to claim 4, wherein
The execution result determination step includes
If the first execution result and the second execution result do not match, the step of storing the first execution result and the second execution result that do not match the first SQL statement related to the mismatch as a mismatch result table When,
A specifying step for specifying a method for matching the first execution result and the second execution result based on the mismatch result table;
A database system migration method comprising: a new table creation step of creating information as a rule for the specified method and adding the information to the SQL statement translation table to create the new SQL statement translation table. 6. The database system migration method according to claim 1, wherein the user data and the first database connection / control character string are included in one file, and the file is targeted. In the case where a tool for assisting the migration of the first database system to the second database system is prepared,
The transition step includes:
An export step of exporting all objects contained in said file to a text file;
A text file conversion step of converting the first SQL sentence contained in the exported text file into the second SQL sentence using the conversion table;
An import step of importing the converted text file into the file;
And a tool use step of performing migration from the file after import using the tool. A conversion table creation method for creating a conversion table used for migration from a first database system to the second database system,
The first database system includes a first database connection having user data having a plurality of records, a first database engine, a database connection character string including a first SQL statement for the first database engine, and a database control character string. A control string and at least
The second database system includes the user data, a second database engine, a database connection character string including a second SQL statement for the second database engine, and a second database connection / control character string having a database control character string. Having at least
In the conversion table creation method, the conversion table is for converting the first database connection / control character string into the second database connection / control character string.
An extraction step of extracting the first SQL sentence at runtime of the first database system;
An SQL statement conversion step of converting the extracted first SQL statement according to an SQL statement conversion table to obtain a converted SQL statement;
An execution result acquisition step of acquiring an execution result of the first SQL statement by the first database engine as a first execution result, and acquiring an execution result of the converted SQL statement by the second database engine as a second execution result. When,
An execution result determination step of comparing the acquired first execution result and the second execution result to determine whether or not they match;
If the first execution result and the second execution result do not match as a result of the execution result determination step, the SQL statement conversion table is updated so that the mismatch can be resolved, and the new SQL statement conversion is performed. A table update step for making a table, and as a result of the execution result determination step, the SQL statement conversion step, the execution result determination step, and the step until the first execution result matches the second execution result A conversion table creation method in which a table update step is repeatedly performed, and the SQL statement conversion table at the time when the first execution result and the second execution result coincide with each other as a result of the execution result determination step is the conversion table. .

Images