JPH0981427A - Method for generating update difference data extraction program and device therefor - Google Patents

Abstract

PROBLEM TO BE SOLVED: To provide a method for generating update difference data extraction program by which an update difference data extraction program can be generated in short time without manual aid and the device therefor. SOLUTION: By reading the table name and the data item definition defined on a data base 401 to be an update object from the data base and visually editing the table name and data item definition information by a selection execution part 405, a selectable menu is prepared, the menu is displayed on a display device 402 and the table name and the data item definitions selected from the menu by an input device 403 are performed. By an automatic program generation execution part 406, the definitions are incorporated as parameters in templates 407 to 409 for extraction of update difference data which are preliminarily prepared, an update difference data extraction procedure update difference data extraction program is generated in accordance with the templates and difference data is extracted by using a procedure.

Description

Detailed Description of the Invention

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to an update difference data extraction program creation method and an update difference data extraction program creation apparatus in a database, and more specifically, data distribution between databases (data of one database between databases). Method to reduce the time and effort required to input data by automatically reflecting the data in the other database), and create the difference data extraction program required in a short time and without much human intervention. The present invention relates to the update difference data extraction program creation method and the update difference data extraction program creation device.

[0002]

2. Description of the Related Art In recent years, many jobs in companies have been processed by computers, and the data to be handled have also been managed as a database. Since the database in that case is designed and operated individually for each business, a plurality of databases corresponding to different business often have duplicate data having the same meaning. Therefore, it is necessary to manually re-input the data, which should be input only once, into each database many times. As a conventional method for solving this problem, the data in the database is automatically reflected in other databases and the same data can be input only once.
There is a method.

When the method of "data distribution" is classified according to the range of data to be distributed, "all data reflection method" for extracting all data in a database and reflecting it in another database, and "data distribution" are updated in the database. There is an "update difference data reflection method" that extracts only data and reflects it in other databases. When a large amount of data is included in the database from which data is extracted, the update data differential reflection method is usually used because the processing performance deteriorates when the full data reflection method is used.

In order to carry out the above-mentioned update difference data reflection method, it is necessary to have a function of extracting the updated data on the database. A program that realizes this function is called an update difference data extraction program. FIG. 1 is a diagram for explaining the function of the update difference data extraction program. In the figure, 101 is an application program and 10
Reference numeral 2 is an update difference data extraction unit, 103 is an update target table, 104 is an update difference table, 105 is an update difference data creation unit, and 106 is an update difference data extraction program.

When the application program 101 updates the data on the update target table 103, the update is detected by the database trigger function which is one of the functions of the DBMS (database management system), and the process started by the database trigger function. By the procedure (corresponding to the update difference data creation unit 105 of the update difference data extraction program 106), the data for which an update is detected is read from the update target table 103, the corresponding update difference data is created, and the updated data is stored. It is stored in the update difference table 104 created for this purpose. Then, the update difference data extracting unit 102 of the update difference data extracting program 106 extracts the update difference data from the update difference table 104.

As shown in FIG. 2, the data created by the update difference data creating unit 105 includes post-update data 201 indicating the data updated by the application program 101, and pre-update data indicating which data has been updated. Data 202, update type 203 indicating what kind of update (addition, update, deletion of data) has been performed,
And update order 2 to prevent data inconsistency between databases due to update and update being reflected in the wrong order 2
04 is given. In the example of the figure, the data “BBB10
"0" is changed to data "AAA100", "first" is changed
It has been done. A series of data composed of the post-update data 201, the pre-update data 202, the update type 203, and the update order 204 is hereinafter referred to as “update difference data”, and the table as shown in FIG. Is.

The update difference data extraction program 106 including the update difference data creation unit 105 and the update difference data extraction unit 102 has a simple utility function (unload function, database migration function) provided by a DBMS (database management system). It cannot be realized by a combination, and therefore it is necessary to create a program by using a database trigger function which is one of the functions of a DBMS (database management system) that detects an update of data in a database of a database and a general-purpose programming language. Therefore, it took a lot of time and manpower to create it.

[0008]

As described above, the conventional technique has a problem that it takes a lot of time and labor to create an updated differential data extraction program for extracting differential data. An object of the present invention is to solve the above-mentioned problems of the prior art, and to create an update difference data extraction program and an update difference data extraction program creation method capable of creating the update difference data extraction program in a short time and without much human intervention. To provide.

[0009]

In order to achieve the above object, an update differential data extraction program creating method according to the present invention uses a differential data extraction procedure (difference) in a database managed by a database management system having a database trigger function. The data extraction program) is interactively completed using the table names and data item definitions defined on the database.

More specifically, a template, which is a skeleton of the function for extracting update difference data, is created in advance, and the table name and data item definition defined in the database to be updated are read from the database. (Steps 501 and 505 in FIG. 5), by visually editing the table name and the data item definition information, a selectable menu is created and displayed (Steps 502 to 503 and 506 to 507). A required table name and data item definition are selected from the displayed menu (steps 504 and 508), the selected table name and data item definition are incorporated into the template as parameters, and the update difference data extraction procedure is performed according to the template. (Differential data extraction program) is created.

Further, the update difference data extraction program creating apparatus of the present invention comprises a database means (401 in FIG. 4) to be updated and a display means (402 of the same table) for displaying a menu of selectable table names and data item definitions. )When,
Input means (403) for selecting a required table name and data item definition from the menu displayed on the display means (402), and unfinished update difference data extraction including an undefined table name or data item name Completing the plurality of template means (407 to 409) by a plurality of template means (407 to 409) describing the procedure and the table name and data item definition obtained as a result of selection by the input means (403). Then, a means (a program automatic creation execution unit 406) for generating an update difference data extraction procedure, executing the update difference data extraction procedure for the database, and registering the update difference data extraction procedure is provided.

In short, the most characteristic point of the present invention is the following points (1) to (3). (1) A template 407 of a program that performs table definition of the update difference table 104 for storing update difference data, and a database trigger that detects update of data, creates update difference data, and saves it in the update difference table 104. Template 408 of the defining program
Then, prepare a template 409 of a program for extracting the update difference data from the update difference table 104 and thereafter deleting the extracted update difference data. (2) Read the table and data item definition (composed of data item name and data type) from the database that is the target of update difference data extraction, display it as a menu, and display the tables and data items that need update difference data extraction. Providing a selection execution unit 405 that allows the user to select.

(3) The table and data items selected by the user in (2) above are expanded in the template prepared in (1) above to create an update difference data extraction program and execute it. Providing a program automatic creation execution unit 406. Note that the template referred to in the specification of the present application is, as shown in FIG. 3, a tag 301 (% T
AG) refers to an unfinished program containing undefined parts. The template becomes a completed program by expanding the portion of the undefined tag 301 with an actual parameter (TABLE NAME).

[0014]

BEST MODE FOR CARRYING OUT THE INVENTION In the present invention, an undefined portion indicated by a tag in a template 407 or 408 prepared in advance is expanded (incorporated) with a table name and a data item definition selected from a display menu by an input means. To complete the programs and execute these programs against the target database to detect data updates by the application program 101,
The update difference data can be stored in the update difference table 104. That is, the update difference data creation unit 10
5 can be realized.

After the execution of the update difference data creation unit 105, the template 409 is expanded (embedded) with the table name selected from the display menu by the undefined portion indicated by the tag by the input means to complete the program. To the target database,
Extraction of update difference data and deletion of updated data are performed. That is, the update difference data extraction unit 102 can be realized.

As described above, the templates 407, 40
Update difference data extraction program 106 (update difference data creation unit 105 and update by combining the programs completed by expanding the undefined parts indicated by tags 8 and 409 with the table name and data item definition selected from the display menu) (Consisting of the difference data extraction unit 102) can be created. By executing the update difference data extraction program 106 created as described above, the update difference data can be extracted and the extracted update difference data can be deleted.

In the present invention, the user's work required to create the update difference data extraction program is only to select the table name and data item displayed as a menu by the selection executing unit 405, and the conventional method is used. It is possible to significantly reduce the enormous amount of time and labor required to create the updated differential data extraction program that describes the necessary differential data extraction procedure.

[0018]

An embodiment of the present invention will be described below with reference to the drawings. The embodiment described below is based on ISO (Interterna).
It is intended for database management systems that support SQL3, which is being standardized by the National Organization for Standardization). FIG. 4 is a diagram showing a basic configuration example of the present invention. In the figure, 401 is a database from which update difference data is to be extracted, 402 is a display device for displaying a menu, 403 is an input device such as a mouse used for selecting a menu, 404
Is a temporary storage device for storing temporary data, 40
Reference numeral 5 is a selection execution unit that selects a table name and data item from the menu, 406 is an automatic program creation execution unit that creates a program from the selected table name and data item, and 407 to 409 are unprepared and prepared in advance. It is a template that is a program. FIG. 5 is a flowchart showing the flow of processing performed by the selection execution unit 405, and FIG. 6 is a flowchart showing the flow of processing performed by the program automatic creation execution unit 406.

7 to 10 are diagrams for explaining an operation example of the present invention. Figure 7 shows the table name menu (list)
FIG. 10 is a diagram illustrating an operation of selecting a table name and a data item from a data item menu (list) and a data item menu. In FIG. 7, 701 is an example of a menu (table name list) of table names displayed on the display device 402 by the selection execution unit 405, 702 is an example of table names selected by the user from the menu 701, and 703 is a selection execution unit. 405
An example of a menu of data items displayed on the display device 402 by 704 is an example of a data item selected by the user from the menu 703.

FIG. 8 is a diagram showing a template of a program that creates the update difference table 104 and a program that completes the template. In FIG. 8, 80
1 is a description example when SQL3 of the template 407 of the program that defines the update difference table 104 is used,
802 is an example of a completed program in which tags are expanded by the program automatic creation execution unit 406.

FIG. 9 is a diagram showing a program of processing executed when the database trigger function detects an update of data on the update target table 103. In FIG. 9, 9
01 is SQL3 of the template 408 of the program that detects the update of the data, creates the update difference data, and defines the database trigger to be stored in the update difference table.
902 is a part of a description example, and 902 is a part of a completed program example in which tags are expanded by the program automatic creation execution unit 406.

FIG. 10 shows a template of a program for reading the update difference data stored in the update difference table 104 and deleting the read-out data so that the read-out data will not be read next time and the program that completes the template. FIG. In FIG. 10, reference numeral 1001 is an example of the description in SQL3 of the template 409 of the program that extracts the update difference data from the update difference table and then deletes the extracted update difference data, and 1002 has the tags expanded by the program automatic creation execution unit 406. , Is an example of a completed program.

Next, the operation of this embodiment will be described. First,
The processing performed by the selection execution unit 405 will be described with reference to FIG. 7 and according to the flowchart of FIG. Selection execution unit 40
5 extracts the table name defined in the database 401 (table name extraction step 501) and stores the table name in the temporary storage device 404. The data representing the table name stored in the temporary storage device 404 is edited to generate a list menu (table name menu generation step 502), and the table name list menu is displayed on the display device 402 such as a CRT (table name menu). Display step 503). 701 in FIG. 7 is an example of the display,
"Shop" and "employee" as table names
Is displayed.

Next, using the input device 403 such as a mouse, the user is made to select the table name required to extract the update difference data (table name menu selection step 504),
The result is stored in the temporary storage device 404. 70 of FIG.
2 is an example of data that is stored in the temporary storage device and represents the table name selected by the above menu. Of the displayed “shop” and “employee”,
This shows a case where "employee" is selected. The data item definition of the selected table is extracted from the database 401 by the table name selected in the table name selection step 504, and the data is stored in the temporary storage device 4.
04 (data item definition extraction step 50)
5).

The above data is edited to generate a data item menu (data item menu generation step 50).
6), the data item menu is displayed on the display device 402 such as a CRT (data item menu display step 5).
07). Reference numeral 703 in FIG. 7 is a display example of the data item menu in that case, and the table name is “employee”.
Then, as a data item, "name char (2
0) ”,“ address char (50) ”,“ t ”
elephone char (12) ”is shown.

Next, using the input device 403 such as a mouse, the user selects a data item that needs to be updated difference data extracted from the data item menu displayed to the user, and stores the result in the temporary storage device 404. (Data item menu selection step 508). Reference numeral 704 in FIG. 7 is an example of data stored in the temporary storage device and representing the data item selected from the data item menu.
har (20) ”and“ address char (5
0) ”is selected. The table name menu 701, the selected table name 702, the table item menu 703, and the selected data item name 704 shown in FIG. 7 are merely examples, and needless to say, they are not limited to these. Absent.

Next, the processing of the automatic program creation execution unit 406 will be described with reference to the flowchart of FIG. FIG.
When the selection of the data item is completed by the processing of the selection execution unit 405 shown in the flowchart of FIG.
Data 702 “employee” and data item 704 representing the selected table name stored on
"Namechar (20)" and "address
char (50) "is used to develop an undefined tag in the template and complete the template to create a program (tag expansion step 601).

Next, a procedure for expanding the undefined tag in the template and incorporating it in the template to complete the program will be specifically described with reference to FIGS. The template 801 shown in FIG. 8 is the SQ of the template of the program that defines the update difference table 104.
This is a description example when L3 is used, in which the pre-update data 202 and the post-update data 201 required for specifying the updated record, for example, the update occurrence order such as the update occurrence date and time is specified, and the extracted update difference Update order 2 that enables data to be reflected in other databases in the order of update occurrence
04, update type 203 definition indicating the type of update that has occurred (addition, change, deletion).

More specifically explaining FIG. 8, "cr
"eate table ext ..." is a statement that specifies the name of the table to be created, and "before ..."
Is data before update, “after ...” Is data after update, “extender ...” Is update order, and “ext type ...” Is update sentence. Also, in the template 801, “% TABL
The parts "E" and "% ITEMDEF" are tags that represent undefined parts until the table and the data item for which the update difference data is extracted are determined.

In the tag expansion step 601, the portion indicated by the undefined "% TABLE" tag is stored in the table name "employed" 702 until the table name is determined.
ee], the part indicated by the undefined “% ITEMDEF” tag is stored in 704 until the data item is determined, and the data item definition “name char (2
0) ”and“ address char (50) ”are used to complete the template 801 and automatically create the program example 802.

The template 901 shown in FIG. 9 is one of the description examples in the case of using the SQL3 of the template of the program for detecting the update of data, creating the update difference data, and defining the database trigger to be stored in the update difference table. This is a part for performing processing when data is added. More specifically explaining the template 901,
"Create trigger insert ...
・ "Is the designated sentence of the trigger name. "After ins
.. is a designation statement of the trigger activation timing, and in this case means that it is activated after data is added to the table called ... An identifier (old as old v) that indicates before update in the three lines after "referencing"
value) and an identifier (new as ne) that represents the post-update
w value) is specified. "For each
"row" is a statement indicating the range in which the update is detected, and in this case, it means to specify the entire range.

“Begin” means the start of the process definition, and “insert into ext ...” is a designation statement for adding (saving) data to the update difference table 104, and the next “value (...)”. The value to be added later is specified. “Oldvalue ...” means that the data before the update occurs in the update target table 103 is stored in the update difference table 104, and “n
ew value. ... "is the update target table 103
The data after the update occurs on the update difference table 10
It means saving to 4. "Current
"timestamp," means that the time when the update occurs is added to identify the update order. "'
"I '" means to add a value indicating the update type. As the update type, “I” is used for addition (insert), “U” is used for change (update), and “D” is used for deletion (delete), and “% TABLE” in the template 901 is used. And "% ITE
The "M" portion is a tag indicating an undefined portion that cannot be described until the table and the data item for which the update difference data extraction is performed are determined.

In the tag expansion step 601, the portion indicated by the undefined "% TABLE" tag is stored in 702 until the table name is determined, and the table name "employe" is stored.
The template is created by expanding the part indicated by the undefined “% ITEM” tag using the data item definitions “name” and “address” stored in 704 until the above data item is determined using “e”. 901
Is completed and the example program shown in 902 is automatically created. The template 901 is a part that performs processing when data is added, but data modification (updat
A program for performing processing when e) or deletion occurs is created in the same procedure.

The template 1001 is an example of description in SQL3 of a template of a program for extracting update difference data from the update difference table 104 and then deleting the extracted update difference data. Also, the template 10
“Select * from ext ...
"" Indicates that the update difference data is read from the update difference table 104, and "delete ext%"
“TABLE” indicates that the update difference data on the update difference table 104 is deleted. “% TABLE” is a tag indicating an undefined portion until the table for extracting the update difference data is determined. Until the table name is determined by the tag expansion step 601, the undefined "% TA
The template 1001 is completed by expanding the portion indicated by the “BLE” tag using the table name “employee” stored in 702, and the program example shown in 1002 is automatically created.

When the program 1002 is created by the tag expansion step 601, the programs 802 and 902 that need to be executed on the database are executed on the database 401 and registered (program execution step 602). After executing the programs 802 and 902 on the database 401, for example, an application program attached to the database is used to execute the program 1002 for extracting the update difference data to extract the update difference data. Note that the above template is merely an example, and the present invention is not limited to these.

[0036]

According to the present invention, the user's work required for creating the updated differential data extraction program that describes the differential data extraction procedure is the table name and data items displayed as a menu by the selection execution unit 405. By simply selecting, it is possible to greatly reduce the enormous amount of time and labor required to create a program using the database trigger function and general-purpose programming language of the database required by the conventional method. It has a remarkable effect.

[Brief description of drawings]

FIG. 1 is a diagram for explaining a conventional technique.

FIG. 2 is a diagram for explaining update difference data.

FIG. 3 is a diagram for explaining a template of a program.

FIG. 4 is a configuration diagram showing an embodiment.

FIG. 5 is a processing flowchart in a selection execution unit.

FIG. 6 is a process flowchart in a program automatic creation execution unit.

FIG. 7 is a diagram illustrating an operation of selecting a table name and a data item from a table name menu (list) and a data item menu (list), respectively.

FIG. 8 is a diagram showing a template of a program that creates an update difference table 104 and a program that completes the template.

FIG. 9 is a table 1 for updating the database trigger function.
It is a figure which shows the program of the process performed when the update of the data on 03 is detected.

FIG. 10 is a diagram showing a template of a program for reading the update difference data stored in the update difference table 104, and deleting the read-out data so that the read-out data will not be read next time and the program that completes the template. is there.

[Explanation of symbols]

101: application program, 102: update difference data extraction unit, 103: update target table, 104:
Update difference table, 105: Update difference data creation unit, 1
06: update difference data extraction program, 401: database, 402: display device, 403: input device, 40
4: Temporary storage device, 405: Selection execution unit, 406: Program automatic creation execution unit, 407 to 409: Template.

Claims (3)

[Claims] 1. In a database managed by a database management system having a database trigger function, a differential data extraction procedure (differential data extraction program) is interactively used by using a table name and data item definition defined on the database. A method for creating an update difference data extraction program characterized by being completed. 2. A template, which is a skeleton of a function for extracting update difference data, is created in advance, and a table name and a data item definition defined on the database to be updated are read from the database, and the table name And by visually editing the data item definition information, a selectable menu is created and displayed, and the required table name and data item definition are selected from the displayed menu, and the selected table name and data are selected. A method for creating an update difference data extraction program, characterized in that the item definition is incorporated into the template as a parameter and an update difference data extraction procedure (difference data extraction program) is created according to the template. 3. Database means (40) to be updated
1), a display means (402) for displaying a menu of selectable table names and data item definitions, and an input means for selecting a necessary table name and data item definition from the menu displayed on the display means (402). (4
03), a plurality of template means (407 to 409) describing the unfinished update difference data extraction procedure including the undefined table name or data item name, and the selection result by the input means (403). The plurality of template means (407 to 409) are completed by the table name and the data item definition, an update difference data extraction procedure is generated, and the update difference data extraction procedure is executed and registered in the database means (401). Means (program automatic creation execution unit 406), and a difference data extraction program creation device characterized by the above-mentioned.