JP2503361B2 - Database search method - Google Patents

Description

Detailed Description of the Invention

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a database search system for searching a database.

[0002]

2. Description of the Related Art Conventionally, a relational database (SQL specification) uses the following statements as a database search method using a cursor.

DECLARE statement Search conditions and cursor names are defined. OPEN statement Opens the table to be searched and positions the cursor at the beginning of the table.

FETCH statement Positions the cursor on a record that satisfies the condition and reads the record pointed to by the cursor.

CLOSE statement Invalidates the cursor and closes the table to be searched. An application program (hereinafter referred to as an application) prepares a buffer 23 for reading records as shown in FIG. 4, issues FETCH statements until the buffer 23 becomes full, and when the buffer 23 becomes full, executes a CLOSE statement. Issue. After processing the records read in the buffer 23, the OPEN statement is issued again, and the processing of the remaining records is similarly repeated. The configuration of FIG. 4 will be briefly described.

FIG. 4 shows an explanatory view of the prior art. In FIG. 4, the application 21 is an application program, and here accesses the database and performs business processing.

The database monitor 22 searches the database. The buffer 23 stores the record read from the database.

The table 24 stores the records that make up the database.

[0009]

In the conventional database search, even if an attempt is made to continue processing by reading from the subsequent record processed by the first OPEN-CLOSE statemen described above, the second and subsequent OPEN statement executions are executed. Since the cursor is sometimes positioned at the top record of the table, the record that has already been processed becomes the search target again, and there is a problem that the search time becomes longer as the record becomes later.
Specifically, with reference to FIG. 4, the first OPEN-
The record in the range of the table 24 is read by the CLOSE statement, and the buffer 23 is full. Then, since the cursor is positioned at the beginning of the table 24 in the second OPEN-CLOSE statement, the part of is also searched again, and the part of the dotted line is searched again. It will be long.

The present invention solves these problems.
The objective is to save the record address (cursor information) during the OPEN-CLOSE process and to provide management information to continuously judge whether the record is possible or not, thereby enabling continuous record retrieval and speeding up. .

[0011]

FIG. 1 is a block diagram showing the principle of the present invention. In FIG. 1, a table 21 is a table that stores records to be searched.

The control table 12 stores and stores the record address at which the search of the table 21 is interrupted.
The management information 5 sets and stores the reference / update distinction of the table 21 for each application 1.

[0013]

According to the present invention, as shown in FIG. 1, when the application 1 tries to search the table 21 constituting the database 2, the management information 5 is set, and other management information 5 is set in the management information 5. When it is determined from the application 1 that the table 21 has been updated, the search is started from the first record of the table 21 specified by the search condition, and when it is determined that the table 21 has not been updated, the control table 12 stores it. The table 21 is searched and restarted from the suspended record address.

At this time, when the application 1 searches the record of the table 21 constituting the database 2 according to the specified search condition, the management information 5 provided for each application 1
The application name, the table name, the reference / update distinction are set therein, and the record address at which the search of the table 21 is interrupted according to the search condition is stored and stored in the control table 12.

Therefore, the record address (cursor information) of the table 21 to be interrupted by the OPEN-CLOSE process.
And the management information 5 is updated / referenced (continuously searchable / not possible) is set and the records in the table 21 constituting the database 2 are continuously searched by referring to these to speed up record search. Can be realized.

[0016]

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS Next, the construction and operation of an embodiment of the present invention will be described in detail with reference to FIGS.

FIG. 1 is a block diagram showing the principle of the present invention. FIG.
In the application, the application 1 is an application program, and refers to / updates the table 21 of the database 2 to perform various business processes. Here, the OPEN statement, the FETCH statement, and the CLOSE statement are issued, and a record is read from the table 21 of the database 2 to perform various processes.

The task area (current) 11 is an area used by the application 1 and is an area where the control table 12 is provided here. In the control table 12, the application 1 has a database 2
When the table 21 is searched, the cursor name at that time and the record address from the beginning of the table 21 pointed by the cursor name are set and stored. As a result, when continuing the search of the table 21 from the second time onward, the search can be restarted from the record of this record address, and the search from the beginning to this record address can be omitted, and the speed of the search of the table 21 can be increased. This is possible (see FIGS. 2 and 3).

The database 2 is constructed so that a large number of records to be searched can be easily searched, and is composed of a plurality of tables 21 and the like. Table 2
Reference numeral 1 stores a plurality of records, and is a target for retrieving a record that matches the specified condition by sequentially searching the table 21 specified by the search condition from the top.

The DB monitor (database monitor) 3 is
It performs various processes for referring to / updating the database 2 (see FIGS. 2 and 3). The common table 4 is
It is provided in the system area and stores and manages the start address of the management information 5 and the like.

The management information 5 is management information provided for each application 1 (for each task). Here, when the table 21 of the database 2 is referred to / updated, the information as shown is set and stored. , For restarting the search from the record continued from the table 21. Here, the management information 5 is provided for each application (for each task), and the following information shown in the figure is connected in time series.

Task information: Set the task (application 1) that refers / updates the table 21 of the database 2. Target table: Set the table name of the target searched by the application 1 in the database 2.

Reference / update type: The type (reference) of reference or update of the target table searched by the application 1 is set. This is because when the update is set, the contents of the table 21 have been updated, so that the continuous search is stopped.

Next management information address: The top address of the next management information 5 for linking the management information 5 in time series for each application 1 is set. Next, the operation of the configuration of FIG. 1 will be described in detail according to the order shown in FIGS. Here, the AP process is a process performed by the application 1 in FIG. 1, and the DB monitor process is the DB in FIG.
This is the process performed by the monitor 3.

In step S1, the definition sentence is fetched and held. S
In 2, the application 1 issues an OPEN statement.
In S3, the DB monitor 3 performs an open process.

In step S4, the cursor C1 is initialized.
This clears the cursor C1 of the control table 12 in the task area (current) 11 of the application 1 as the initialization of the cursor C1 defined by the definition statement of S1.

In step S5, the management information 5 is registered. For this, the management information 5 of the table name T1 defined by the definition statement of S1 is created, linked, and registered as shown below. -Task information: AP1 (application (AP1))-Target table: T1-Reference / update: reference This means that the processing corresponding to the issuance of the OPEN statement in S2 to S5 has been completed.

Next, S6 issues a FETCH statement. In S7, the DB monitor 3 performs a fetch process. In S8, the database is searched under the search condition.

In step S9, the record that matches the condition is fetched. This compares the search condition from the first record of the table T1 designated according to the search condition defined by the definition statement of S1 and extracts the matching record (record address Li at this time).

In S10, the address Li of the record is changed to C1.
Set to. As shown by the arrow, the record address Li when the matching record is fetched from the table (T1) 21 in S9 is set and stored in the record address position in the control table 12.

In step S11, the record is returned. As a result, the DB monitor 3 returns one record for which the application 1 is instructed to search. In S12, the application 1 stores the record returned in S11 in the buffer 13, and determines whether the buffer 13 is full. If yes,
Since it is full, a CLOSE statement is issued in S13 to perform the CLOSE processing of the database 2. On the other hand, in the case of NO, since the buffer 13 is not full,
Returning to S6, similarly, storing the next record in the buffer 13 is repeated until it becomes full.

By the above, the first OPEN-CLO
The SE process is ended, and records matching the search condition from the table name T1 of the database 2 designated by the definition statement of S1 are repeatedly stored in record units until the buffer 13 becomes full. When the buffer 13 is full,
Interrupted fetching records from table name T1,
The data in the buffer 13 is processed. By setting the control table 12 and the management information 5 in this manner, the search is restarted from the record address (stored in the control table 12) following the table name T1 during the second OPEN-CLOSE processing in FIG. Therefore, it is possible to eliminate unnecessary search of the table 21 and perform high-speed search. This will be described below.

FIG. 3 is a diagram (No. 2) for explaining the operation of the present invention. In FIG. 3, in S21, the application 1 issues an OPEN statement. In S22, the DB monitor 3 performs an open process.

In step S23, it is determined whether the management information 5 exists.
If yes, go to S25. If none, S2
In S4, the processes after S2 in FIG. 2 described above are performed as the first open process.

In S25, it is determined whether or not there is an update from another task (other application 1). Since the management information 5 exists in S23, it is determined whether there is an update from another task in the management information 5 (here, the search target table T).
It is determined whether there is an update from another application 1 to 1). If yes, the cursor information is invalid in S26, that is, it is found that the table name T1 to be searched is updated by another application 1, so the cursor information (record address) in the control table 12 is invalid. And On the other hand, if there is none, the process ends.

OPEN by S21 to S26
2 in response to the issue of the statement
Attempt to restart the search after the first time. Here, if the table name T1 has been updated from another application 1, the record address of the control table 12 (cursor information)
Disable. If not updated, control table 12
Leave the record address of valid.

In S27, the application 1 issues a FETCH statement. In S28, the DB monitor 3 performs a fetch process. In S29, it is determined whether the cursor information is valid. If it is valid, the search is performed by designating C1 in S30, that is, the search is restarted from the record address Li of the table T1 with the cursor name C1 set in the control table 12 as described on the right side. On the other hand, if the table is invalid, the database search is performed in S31 according to the search condition, that is, the search is started from the first record of the table T1 (since the table T1 has been updated, the search cannot be started halfway.
The search starts from the first record of 1.).

In step S32, the record matching the condition is taken out. In S33, the address Li of the record is set and stored in C1 of the current control table (task area) 12.

In step S34, the record is returned. As a result, the DB monitor 3 returns one record for which the application 1 is instructed to search. In S35, the application 1 stores the record returned in S34 in the buffer 13, and determines whether the buffer 13 is full. If yes,
Since it is full, a CLOSE statement is issued in S36 to perform the CLOSE processing of the database 2. On the other hand, in the case of NO, since the buffer 13 is not full,
Returning to S27, the next record is similarly stored in the buffer 13
Repeat to store in until full.

As described above, the second and subsequent OPEN-Cs
The LOSE process is executed, and the search of the record matching the search condition is restarted from the table address T1 of the database 2 designated by the definition statement of S1 from the interrupted record address (if not updated) or from the beginning of the table 21. It starts (when it has been updated), stores the returned record, and repeatedly stores it in record units until the buffer 13 is full or the search is completed. When the buffer 13 is full or when the search is completed, the retrieval of records from the table name T1 is interrupted or terminated, and the data in the buffer 13 is processed. By setting the control table 12 and the management information 5 in this way, the second and subsequent OPEN-CLOS
At the time of E processing, the search is restarted from the record address (stored in the control table 12) following the table name T1, and the table 21
It is possible to eliminate a wasteful search and perform a high-speed search.

[0041]

As described above, according to the present invention,
The record address (cursor information) to be interrupted in the table 21 of the database 2 during the OPEN-CLOSE processing is set and stored in the control table 12, and the management information 5 is updated / referenced (continuously searchable / not possible). Since the configuration is adopted in which the table 21 of the database 2 is searched by referring to these, when the record in the table 21 of the database 2 is searched, the search can be restarted from the previous record that was interrupted, A high-speed search can be performed by eliminating unnecessary search from the beginning of the table 21.

[Brief description of drawings]

FIG. 1 is a principle configuration diagram of the present invention.

FIG. 2 is an operation explanatory diagram (1) of the present invention.

FIG. 3 is an operation explanatory diagram (2) of the present invention.

FIG. 4 is an explanatory diagram of a conventional technique.

[Explanation of symbols]

 1: Application (application program) 11: Task area (current) 12: Control table 13: Buffer 2: Database 21: Table 3: DB monitor 4: Common table 5: Management information

Claims (2)

(57) [Claims] 1. A database search method for searching a database, a table (21) for storing a record to be searched, and a control table (12) for storing and storing a record address for which the search of the table (21) is interrupted. And the management information (5) which sets and stores the reference / update distinction of the table (21) for each application (1), and the application (1) searches the table (21) forming a database. At the time of trying, it is found that the management information (5) is set, and it is found that the table (21) has been updated from another application (1) in the management information (5). Table specified by (2
The search is started from the first record of 1), while the table (2) is started from the interrupted record address stored in the control table (12) when it is determined that the update is not made.
A database search method characterized in that the search of 1) is restarted. 2. When the application (1) searches the record of the table (21) constituting the database according to the instructed search condition, the management information (5) provided for each application (1) The application name, the table name, reference / update distinction are set, and the record address for which the search of the table (21) is interrupted according to the search condition is stored and stored in the control table (12). The database search method according to claim 1, characterized in that