JPH0764837A - Computer system - Google Patents

Abstract

PURPOSE:To improve the performance of data base operation responding to an SQL source by efficiently shortening the compilation time of this SQL source. CONSTITUTION:SQL information including the SQL source and the SQL object as its compilation result is generated for each inputted SQL source and is stored in an SQL information storage buffer 122 of a main memory 12. Contents of this SQL information storage buffer 122 are substituted by the technique of LRU or the like, and then, only the latest SQL information is left in the SQL information storage buffer 122, and the other SQL information are held on a disk device 14. The SQL information storage buffer 122 is retrieved in accordance with an inputted SQL source, and the SQL object corresponding to this SQL source is called and executed when existing. Since the compilation processing of the SQL source is unnecessary at this time, the data base operation is efficiently performed.

Description

Detailed Description of the Invention

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a computer system for compiling SQL, which is a database operation language, and executing its objects to perform database operations.

[0002]

2. Description of the Related Art Generally, in a database system constructed on a computer system, the database is searched and updated according to the database operation language issued from the terminal. SQL is often used as a database operation language. SQL is a language for making a search request or an update request to a relational database, and is expressed by a simple syntax consisting of three basic phrases SELECT, FROM, and WHERE in order to facilitate the operation in atypical work. To be done.

Such SELECT, FROM, WH
When the SQL source described by the ERE is issued by the terminal, the SQL source is sent to the database system of the host computer system. The database system first compiles its SQL source,
Convert to SQL object. Then, the database management system executes the SQL object to execute the database operation corresponding to the inquiry requested by the SQL source, and displays the operation result on the screen of the terminal as necessary.

As described above, in the database system of the host computer, it is necessary to compile each time the SQL source is received from the terminal. In this case, the SQL source is compiled with reference to the schema information of the database. The schema information defines the format of the data stored in the database, and is usually stored in the disk device as a database together with the stored data in the database. Therefore, a lot of input processing from the disk device is required to acquire the schema information. Therefore, the conventional database system has a drawback in that the compilation process takes a long time, which causes a decrease in system performance.

Therefore, recently, the following two methods for improving system performance have been considered. 1
One is to temporarily retain the SQL object in the main memory only while the user is using the database system, and when the SQL source corresponding to the SQL object is issued again, the retained SQL object is retained. It is a method of using.

[0006] The other is a method called a stored procedure, which corresponds to a specific inquiry process.
In this method, the QL source is precompiled, registered in a dedicated library, and the user calls it.

In the former method, the SQL having the same content is used in a specific session with the database system.
This is effective because the compilation process can be omitted when the source is issued many times by the user, but once the user finishes using the database system, all SQL objects held in the main memory are destroyed.
Therefore, when the user starts the session with the database system again, the SQL system does not exist and the database system needs to start the compilation process. Therefore, in the former method, practically sufficient performance improvement cannot be expected. Further, if the SQL object is kept in the main memory without being discarded even after the use of the database system, most of the main memory will be occupied by the SQL object. In this case, there are problems that memory objects are wasted by SQL objects that are used infrequently, and processing speed is reduced due to reduction in remaining memory capacity.

In the latter method, there is no problem in making a fixed inquiry, but there is a problem in that it cannot deal with an atypical inquiry in which the contents of the inquiry are not fixed. if,
In order to deal with atypical queries, it is necessary to register a program containing a large number of SQL objects corresponding to all possible query patterns in a library. In addition, this method has a problem that the original goodness of SQL, that is, the easiness of operation in an atypical work is impaired.

[0009]

Conventionally, it is necessary to compile the SQL source each time it is input.
As a result, the performance of the database system is degraded. Also, all SQs that are compilation results
If the L object is held in the main memory, most of the main memory is occupied by the SQL object, and the memory resource is wasted. Further, when the stored procedure method is adopted, although compiling is unnecessary, there is a drawback that the easiness of operation in non-routine work is impaired.

The present invention has been made in view of the above circumstances, and it is possible to reduce the number of compilations to be executed without wasting the memory resources of the main memory, and to achieve a high-performance database suitable for atypical work. An object is to provide a computer system capable of performing operations.

[0011]

Means and Actions for Solving the Problems
Compile the input SQL source for database inquiry to generate SQL object,
In a computer system that performs a database operation in response to a query by executing a QL object, SQL information including an SQL source and an SQL object that is a compilation result of the SQL source is generated for each of the input SQL sources, and the SQL information is generated. A means for storing information in an SQL buffer of the main memory of the computer system; and the SQL buffer and the computer so that only the SQL information corresponding to the latest one or more SQL sources is held in the SQL buffer of the main memory. A means for replacing the contents of the SQL buffer by transferring data to and from the secondary storage of the system, and searching the SQL buffer or the secondary storage of the main storage according to the input SQL source to find the SQL object corresponding to the SQL source. Call that Characterized by comprising a means for executing the QL object.

In this computer system, SQL information including the SQL source and the SQL object that is the compilation result of the SQL source is generated for each input SQL source, and the SQL information is stored in the SQL buffer of the main memory. The contents of the SQL buffer are replaced by a technique such as LRU, whereby only the latest SQL information is left in the SQL buffer, and other SQL information is held in the secondary storage. For this reason, the SQL buffer of the main memory eventually contains an SQL source that includes a frequently used SQL source.
Since the information is retained, it is possible to prevent waste of memory resources. The SQL buffer in the main memory is searched according to the input SQL source, and if there is an SQL object corresponding to the SQL source, it is called and executed. At this time, it is not necessary to compile the SQL source, so that the database operation according to the inquiry can be efficiently performed.

Furthermore, by deleting the SQL information including the SQL source referring to the schema information to be changed from the SQL buffer or the secondary storage, the consistency between the held SQL object and the actual database is guaranteed. You can also do it.

[0014]

Embodiments of the present invention will be described below with reference to the drawings. FIG. 1 shows the overall configuration of a computer system according to an embodiment of the present invention. This computer system includes a host computer 10 and a plurality of terminals 20 connected to the host computer 10,
The host computer 10 provides services such as database search and update to each terminal 20.

As shown, the host computer 10 includes a CPU 11, a main memory 12, a database processing unit 13, a disk unit 14, and an I / O channel 1.
5 and a communication interface 16, and the CPU 11, main memory 12, database arithmetic processing unit 13, I / O channel 15, and communication interface 16 are interconnected via a system bus 17.

The CPU 11 controls the entire system of the host computer 10. By executing a database management system (DBMMS) 121, the CPU 11 manages the database 141 and operates data stored in the database 141. To do. Also, CPU
Reference numeral 11 issues a command to the database operation processing device 13 as necessary, and causes the database operation processing device 13 to execute processing such as sorting and relational operation.

The main memory 12 is for storing programs and data to be executed by the CPU 11, and includes a database management system (DBMM).
S) 121 is stored. The database management system (DBMMS) 121 is a system program for managing a database. Further, a certain storage area of the main memory 12 is defined as an SQL information storage buffer 122, where the CPU 1
1 stores SQL information. This SQL information is generated each time the SQL source is compiled, and shows the correspondence relationship between the SQL source issued from each terminal 20 and the SQL object or the like that is the compilation result. It will be described later with reference to FIG.

The database arithmetic processing unit 13 is a dedicated hardware for executing processing such as sorting and relational operation at high speed, and operates according to commands from the CPU 11.
The disk device 14 is connected to the system bus 17 via the I / O channel 15, and the host computer 1
Used as secondary storage of zero. This disk device 14
Is composed of a magnetic disk, a magneto-optical disk, a semiconductor disk device, or the like, and a data dictionary 141 and a database 142 are stored therein.

The data dictionary 141 is a catalog data group for managing the accumulated data of the database 142, and schema information 141a and SQL information 141b.
Is included. The schema information 141a defines the accumulated data of the database 142, and is composed of information about the tables and columns of the database 142. This schema information 141a is the database 14
Database 14 updated in response to changes in definition 2
Every time the contents of 2 are updated, it will be upgraded.

The SQL information 141b is the same information as that stored in the above-mentioned SQL information storage buffer 122. That is, the SQL information storage buffer 122
The content of L is such that only the latest content is retained there.
S replaced by the RU method (Least-Recently-Used method) and discharged from the SQL information storage buffer 122
The QL information is transferred to the data dictionary 141 as the SQL information 141b. The database 142 is composed of a plurality of tables forming a relational model.

FIG. 2 shows an example of the data structure of SQL information. As shown in the figure, each SQL information includes registration time information, schema version information, SQL identifier, S
It is composed of a QL source and an SQL object, which are stored in the corresponding entries e1 to e5 of the SQL information storage buffer 122, respectively.

The registration time information set in the entry e1 indicates the time at which the generated SQL information is registered in the SQL information storage buffer 122, and includes registration date information and time information. Also, the data dictionary 1
Since the content of 41 is in a format that can be accessed by the SQL source (that is, the same format as the table on the database 142), the SQL information 1 stored therein is
41b itself can be accessed by SQL source. In this case, the registration time depends on what S
It can also be used for operations such as notifying the user of when the QL information was registered by displaying a screen.

The schema version information set in the entry 2 indicates the version of the schema information 141a used when compiling the SQL source. In addition, the schema version information is the schema information 1 which is the base when the SQL information is generated.
When 41a is changed or deleted by the user, the same schema version is searched and the SQL information 141b of the same version as the schema version is searched.
Is also used for deleting the database 142 from the data dictionary 141 so that the database 142 is not accessed by an old and invalid SQL object.

The SQL identifier set in the entry 3 is ID information for identifying the corresponding SQL information,
When the database inquiry of the same content is repeatedly made, the terminal 20 can issue the SQL identifier instead of the SQL source. In this case, for example, all or part of the SQL information is displayed on the terminal 20, the SQL source displayed there is confirmed, and the corresponding SQL is displayed.
Operation such as designating an identifier from the terminal 20 can be performed, and operability can be improved.

The SQL source set in the entry e4 is the source program itself described in SQL, and is described as, for example, SELECT * FROM employee table WHERE years of service> = 10.

This is an inquiry designating that all fields (columns) having been in service for 10 years or more are selected from the employee table registered as a table in the database 142.

The SQL object set in the entry e5 is the compilation result itself of the SQL source in the entry e4. Next, the database management operation of the host computer 10 will be described.

First, referring to the flow chart of FIG.
A case where the user first logs on to the host computer 10, that is, there is no precompiled SQL object will be described as an example.

When the user logs on from the terminal 20 to issue an inquiry to the database 142 on the disk device 14 and issues an SQL source, first, the SQL source is accepted by the database management system 121 operating on the CPU 11. (Step S1
1). The database management system 121 includes a memory 12
The SQL information storage buffer 12 prepared in advance above
Place the received SQL source 21 in the entry e4 of one SQL information area in 2 and compile it to SQ
Generate an L object (steps S11 and S1)
2).

After this, the database management system 121
Is the registration time, schema information 141 regarding the tables and columns stored in the data dictionary 141.
The version of a, the SQL identifier for identifying the SQL source, and the SQL object as SQL information are SQL
The SQL information having the same content as that generated in the information storage buffer 122 is registered in the data dictionary 141 of the disk device 14 as the SQL information 141b (steps S14 and S15).

The database management system 121
Executes the SQL object on the memory 12 and displays the result on the terminal 30 (steps S16 and S17). In the same manner, SQ is individually applied to a plurality of SQL sources.
The L information is generated, registered in the data dictionary 141, and the user is logged off when a series of inquiry processing is completed.

Even after the user logs off, the database management system 121 does not erase the SQL information storage buffer 122 on the memory 12 and the host computer 1
Hold 0 until the system is stopped. This holding method is the so-called LRU method (Least-Rece
ntly-Used method), and the SQL source issued the oldest or SQL with the SQL identifier added
Data dictionary 1 for SQL information corresponding to the source
By writing to the 41, the SQL information storage buffer 122 having a fixed size can always hold only the latest collection of SQL information.

Next, with reference to FIG. 4, the operation when the user logs off and then logs on again and issues the SQL source for database inquiry will be described.
Upon receiving the SQL source, the database management system 121 receives the same SQL as that previously issued.
In order to check whether it is the source, S on the memory 12 is first.
The buffer 122 for storing QL information is searched according to the SQL source (steps S21 to S23).

If the same SQL source exists in the SQL information storage buffer 122, the SQL object stored in the SQL information is called from the memory 12 and executed (steps S24, S31, S3).
2).

On the other hand, if it does not exist, the disk device 1
SQL information 141 of data dictionary 141 on 4
The SQL source in b is searched, and if the same SQL source exists, the SQL stored in the SQL information is searched.
The object is called from the disk device 14 onto the memory 12 and executed (steps S25, S26, S27,
S31, S32).

If the corresponding SQL source does not exist in either the memory 12 or the disk device 14, the SQL source is compiled and the SQL information is generated and registered as described above. SQ
Execute the L object (steps S28 to S3)
2).

After that, when the system of the host computer 10 is stopped, the database management system 121 uses all the SQL information in the SQL information storage buffer 122 on the memory 12 as SQL information 141b in the data dictionary 141 on the disk device 14. After saving, the process ends. Therefore, the latest SQL information can be retained even after the power is turned off.

Next, referring to the flow chart of FIG.
The operation of deleting the held SQL information will be described.
For example, when the structure definition of the database 142 needs to be changed, the database management system 121 changes or deletes the schema information 141a of the data dictionary 141 in response to a request from the system administrator or a privileged user (step S41). . At this time, the database management system 121 uses the SQL of the data dictionary 141.
The information 141b is searched (step S43). Then, it finds the SQL information generated based on the old version of the schema information that has been changed or deleted, and deletes it from the data dictionary 141 and the SQL information storage buffer 122 (steps S43 and S).
44).

As described above, in the database system of this embodiment, S is input for each input SQL source.
SQL information including a QL source and an SQL object which is a compilation result of the QL source is generated and stored in the SQL information storage buffer 122 of the main memory 12. The content of the SQL information storage buffer 122 is, for example, L
It is replaced by a method such as RU, so that only the latest SQL information is stored in the SQL information storage buffer 122.
Other SQL information is retained in the data dictionary 141 of the disk device 14. Therefore, as a result, the SQL information storage buffer 122 of the main memory 12 holds the SQL information including the frequently used SQL source, so that the number of times of disk access can be suppressed and the waste of memory resources can be prevented. can do.

Further, the SQL information storage buffer 122 of the main memory 12 is searched according to the input SQL source, and if there is an SQL object corresponding to the SQL source, it is called and executed. At this time, S
Since the compilation process of the QL source is unnecessary, the database operation according to the inquiry can be efficiently performed.

Further, by deleting the SQL information including the SQL source that refers to the schema information to be changed from the data dictionary 141 and the SQL information storage buffer 122, the stored SQL object and the actual database are Consistency can also be guaranteed.

[0042]

As described above, according to the present invention, the SQL source and the SQL object which the user once issued are registered as SQL information in the data dictionary, so that the same SQL source after the reissue is issued. It is possible to eliminate the compile time and improve the performance of SQL source execution. Therefore, the number of compilations to be executed can be reduced without wasting the memory resource of the main memory, and a high-performance database operation suitable for atypical work can be realized.

[Brief description of drawings]

FIG. 1 is a diagram showing an overall configuration of a computer system according to an embodiment of the present invention.

FIG. 2 is an exemplary view showing an example of a data structure of SQL information managed by the computer system of the embodiment.

FIG. 3 is a flowchart illustrating a first example of an operation at the time of database access in the computer system of the same embodiment.

FIG. 4 is a flowchart illustrating a second example of an operation at the time of database access in the computer system of the same embodiment.

FIG. 5 is an SQ in the computer system of the same embodiment.
The flowchart explaining the deletion operation of L information.

[Explanation of symbols]

10 ... Host computer, 11 ... CPU, 12 ... Main memory, 13 ... Database operation processing device, 14 ... Disk device, 20 ... Terminal, 121 ... Database management system, 122 ... SQL information storage buffer, 141 ...
Data dictionary, 141a ... Schema information, 14
1b ... SQL information, 142 ... Database.

Claims (2)

[Claims] 1. A computer system for compiling an input SQL source for a database inquiry to generate an SQL object, and executing the SQL object to perform a database operation in response to the inquiry. S that includes the SQL source and the SQL object that is the compilation result for each SQL source
Means for generating QL information and storing the SQL information in an SQL buffer of the main memory of the computer system, and so that only the SQL information corresponding to one or more latest SQL sources is held in the SQL buffer of the main memory. And means for replacing the contents of the SQL buffer by data transfer between the SQL buffer and the secondary storage of the computer system, and searching the SQL buffer or secondary storage of the main storage according to the input SQL source. A computer system comprising: means for calling an SQL object corresponding to an SQL source and executing the SQL object. 2. A computer system for compiling an input SQL source to generate an SQL object, and executing the SQL object to perform a database operation, and stored data and schema information defining a format of the stored data. A database storing means for storing a database configured by: a means for updating the version of the schema information corresponding to the accumulated data of the database each time the schema information is changed; and for each of the input SQL sources, SQL source, SQL object which is the compilation result, and S
A means for generating SQL information including the version name of the database referred to by the QL source and storing the SQL information in the SQL buffer of the main memory, and one or more latest SQL sources in the SQL buffer of the main memory. Means for replacing the contents of the SQL buffer by a data transfer between the SQL buffer and the secondary storage of the computer system so that only SQL information is retained, and an SQL buffer of the main storage according to an input SQL source or A means for retrieving the secondary storage, invoking an SQL object corresponding to the SQL source, and executing the SQL object; and an S for the main storage according to the version of the schema information.
Means for searching the QL buffer or the secondary storage and deleting the SQL information including the SQL source referring to the schema information to be changed from the SQL buffer or the secondary storage of the main storage. Computer system.