JPH09305449A - Data base managing system - Google Patents

Abstract

PROBLEM TO BE SOLVED: To reduce overhead at the time of reprogramming a data base area for mass variable length data by providing a data base storage area for storing the data of basic data items and a data base storage area for storing the data of mass variable length data items. SOLUTION: A mass variable length data managing part 24 performs access to the mass variable length data stored in a data base area 60 for mass variable length data. A buffer pool managing part 25 inputs/outputs data with a buffer pool 32 securing data from a data dictionary 40, basic row data base area 50 and data base area 60 for mass variable length data. A log managing part 26 manages the log of update history information of data performed by a transaction inputted from a terminal 18S and allocates the log sequence numbers of respective log records and when the transaction is completed, these log records are written from a log buffer 34 to a log file 70.

Description

Detailed Description of the Invention

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a database management system, and more particularly to a database management system for efficiently managing data of large and large variable length data items such as images, sounds and documents.

[0002]

2. Description of the Related Art In the first prior art, data of basic data items (data items that are not long and large variable length data items) are stored in a basic row database area. The data of the long and large variable length data items is stored in a long and large variable length data database area different from the basic row database area. Then, a physical pointer to the area storing the data of the long and large variable length data item is stored in the basic row database area, and it is displayed as if it exists in the same database area. In the second conventional technique, the data of the basic data item is stored in the basic row database area. The data of the long and large variable length data items is stored as a file for each data in a long and large variable length data database area different from the basic row database area. Then, those file names are stored in the basic row database area and displayed as if they exist in the same database area. As described above, the reason why the basic row database area and the database area for long and large variable length data are separately provided is that if both data are stored in the same database area in a mixed manner, the access efficiency becomes very poor.

[0003]

In the first prior art described above, since the physical pointer to the area storing the data of the large and large variable length data items is stored in the basic row database area, the large and large variable length data is stored. If reorganization is performed when the data arrangement in the database area for data is disturbed, the physical pointer stored in the basic row database area must be updated significantly, which causes a large overhead at the time of reorganization. On the other hand, in the second conventional technique, the file management is entrusted to the system program, so there is a problem in terms of security and processing efficiency. Therefore, an object of the present invention is to provide a database management system in which the overhead at the time of reorganization of a database area for long and large variable length data is small, and the problems of security and processing efficiency do not occur.

[0004]

According to a first aspect, the present invention provides a first database storage area for storing data of a basic data item (a data item that is not a long and large variable length data item), and a long and large variable length. A second database storage area for storing data of the data item;
The database storage area is a data storage area that physically stores the data of the basic data item in page units of fixed length, and a free area management area that manages the free space in the data storage area by using / not using the page unit. The second database storage area includes a large and large variable length data storage area for storing data of a large and large variable length data item, a free area management area for managing the free space of the long and large variable length data storage area, and the long and large size storage area. A numbering area that adds a serial number that monotonically increases or decreases with the addition of data of a variable length data item, and a data mapping area that associates the serial number with the storage position of the data of the large and large variable length data item,
Further, the basic data item includes a data item that manages a storage position of data of the long and large variable length data item,
A database management system is provided in which the data of the data item includes the serial number. The first
In the database management system from the viewpoint of, the data of the basic data items and the data of the large and large variable length data items are separately stored in different database areas, and the data of the long and large variable length data items is added with a serial number and the serial number is added. To be one of the basic data items and make it appear as if they exist in the same database area.
There is no need to access the first database area because the serial number does not change even if the second database area is reorganized. Therefore, the reorganization of the first database area and the second database area can be performed independently, and the overhead can be reduced. Further, since the file management is not entrusted to the system program, there is no problem of security and processing efficiency.

According to a second aspect of the present invention, in the database management system having the above structure, when a request to insert data of a large and large variable length data item is given, a serial number is assigned to the data and the data is stored. The data is stored in the second database area, the correspondence between the storage position and the serial number is held in the data mapping area, the data of the basic data item including the serial number as a data item is created, and the data is stored in the first database area. (EN) Provided is a database management system characterized by storing in. In the database management system according to the second aspect, it is possible to preferably insert data of large and large variable length data items.

[0006] In a third aspect, the present invention provides a database management system having the above configuration, wherein the serial number is not a rollback target. In the database management system according to the third aspect, when recovering from a failure, a new serial number is assigned to the data of the large and large variable length data items.
That will simplify management.

[0007]

BEST MODE FOR CARRYING OUT THE INVENTION Embodiments of the present invention will be described below with reference to the accompanying drawings. First, the row insertion will be described with reference to FIGS. 1 to 5, and then the reorganization will be described with reference to FIGS.

FIG. 1 is a block diagram of a computer system 10 including a database management system 20 according to an embodiment of the present invention. However, the configuration of the database management system 20 shows a portion related to row insertion. The computer system 10 includes a CPU 12, a main storage device 14, an external storage device 30 such as a magnetic disk, and a large number of terminals 18. A database management system 20 is placed on the main storage device 14. On the external storage device 30, as a database managed by the database management system 20, a data dictionary 40 for managing table definition information and storage definition information defined in the database, and a basic row database area for storing data of basic data items. 50, a large and large variable length data database area 60 for storing data of long and large variable length data items, and a log file 70 which is database update history information are stored.

The database management system 20 includes a question analysis processing unit 21, a database calculation processing unit 22, a basic row data management unit 23, a large and large variable length data management unit 24, a buffer pool management unit 25, a log management unit 26, and a buffer pool 32. And a log buffer 34. The question analysis processing unit 21 receives an SQL (Structured Query Language) which is a database inquiry request from a user, performs an optimization process for determining an optimum access route of database access through a syntactic analysis and a semantic analysis process, and makes a determination. An internal processing code for database processing is generated based on the access path. The database operation processing unit 22
Performs database access based on the generated internal processing code. The basic row data management unit 23 accesses the data of the basic data item stored in the basic row database area 50. The long and large variable length data management unit 24
Accesses the data of the large and large variable length data items stored in the large and large variable length data database area 60. The buffer pool management unit 25 inputs / outputs data from / to the buffer pool 32 that secures data from the data dictionary 40, the basic row database area 50, and the large / large variable length database area 60. The log management unit 26 manages a log that is update history information of data performed by a transaction input from the terminal 18, allocates a log sequence number of each log record,
When the transaction is completed, the log file is written from the log buffer 34 to the log file 70.

FIG. 2 is an explanatory diagram of the database definition information stored in the data dictionary 40. In a database management system, especially a relational database management system, data is managed in a table (relation) composed of a plurality of rows. The table is composed of multiple columns. Each column has attribute information such as data type and data length.
Further, since the data is stored in the basic row database area 50 or the large / large variable length data database area 60 of the external storage device 30, it is necessary to have information for managing the database area. The database area management table 41 is
It is a table of information for managing a database area, a database area name, a type indicating the type of the database area (D is a data dictionary 40, R is a basic row database area 50, and L is a large and large variable length data database area 60. ) And the number of constituent files (the logical number of files constituting the database area). The database area is regarded as a logical area so that it can cope with an increase in the database capacity, and is composed of multiple files. The database area configuration file management table 42 is a table for managing the files that make up the database area, and is composed of a database area name, a number indicating the order of files, and a configuration file name column. The table management table 43 includes columns of a table name, a table owner, the number of columns constituting the table, and a database area name (basic row) for storing data of basic data items of the table. Composition column information management table 44
Is the definition information of the columns that make up the table. The column name, the table name that has the column, the table owner, the data type of the column (integer is a numeric data type, char is a character type, and LOB is a large variable length). Data type), maximum data length, column number indicating the order in which the column configures the table, and the database area name column that stores the data if the column is a large-sized variable-length data item (for large-sized variable-length data) Composed of. If the column is an elementary data item, the value of the database area name will be NULL.
Is stored.

FIG. 3 is a storage structure diagram of the basic row database area 50 and the long and large variable length data database area 60. The basic row database area 50 includes free area management information 51 for managing free areas and data 5 for each row of the table.
2a to 52e. Free space management information 5
1 is to express all the pages in a bitmap by dividing the files into a plurality of pages of a fixed length based on a predefined size for a plurality of files constituting the basic row database area 50, Manage free space on a page-by-page basis. The row data 52 is stored in the page allocated based on the free area management information 51. Plural lines are stored in one page. The data in the plurality of columns in each row are stored in the order of the defined columns. In the example of FIG. 3, the table is composed of four columns. Columns 1 to 3 are basic data items. The fourth column is a large and large variable length data item. In order to make it appear as if large and large variable length data exists there, the fourth column stores the serial number (LOBID) assigned to the data stored in the large and large variable length data database area 60. When the data of a plurality of tables is stored in the basic row database area 50, information for managing the storage information of the data for each table may be added.

Database area 60 for long and large variable length data
Is an LO for adding a serial number to the data in the area.
BID numbering information 61, free area management information 62 for managing free areas, data mapping area 63 and data 64 for mapping physical position information of data corresponding to serial numbers
a to 64d. The free space management 62 divides all the files constituting the database area for long and large variable length data into a plurality of pages of a fixed length based on a predefined size so that all the pages are Express it in a bitmap, etc., and manage the vacancy in page units. The free area management information 6
2 may have position information at the beginning and end of the free area and the total free area length, and may be realized by a method of chaining between the free areas with a pointer.

Next, the operation of the database management system 20 will be described. From the terminal 18 shown in FIG.
Assume a case where a row insertion request by the NSERT statement is input. INSERT INTO DOCUMENT VALUES (1001, 'Patent abstract 1001', John, 'Invention name ...
* ') When the above SQL is input, the database management system 20 causes the question analysis processing unit 21 to analyze the syntax. When the grammar is determined to be correct as a result of the analysis, the data dictionary 40 is searched, and the specified table "DOCUMEN" is searched.
The existence of "T" is checked by the table management table 43 shown in FIG. When the existence of the table "DOCUMENT" is confirmed, the basic row database area name "AREA2" for storing the data of the table "DOCUMENT" is acquired. Further, the constituent column information of the table name “DOCUMENT” is acquired from the constituent column information management table 44. At this time, the column name "CONTEXT" is
It can be seen that the data type is "LOB" of the large and large variable-length data item, and the data is stored in the database area name "AREA3" for large and large-sized variable length data. Next, in the question analysis processing unit 21,
An internal processing code for database processing is generated according to the acquired information, and an execution request for the internal processing code is issued to the database arithmetic processing unit 22. Here, a row insertion processing request is issued. Specifically, the table "DOCU
Data for columns other than the column name "CONTEXT" should be stored in the basic row database area "AREA2", and data for the column name "CONTEXT" should be stored in the database area "AREA3" for long and large variable length data. A request to insert a row is issued. The database arithmetic processing unit 22 analyzes the processing request, and the column name “CON
The basic row data management unit 23 issues an insertion processing request for data other than "TEXT", and the long and large variable-length data management unit 24 performs an insertion processing request for data having a column name "CONTEXT".

FIG. 4 shows the database operation processing section 22.
5 is a flowchart of a row insertion process controlled by the.
In step 2310, the specified table "DOCUMEN"
Basic row database area "AR" for storing data of "T"
A lock for exclusive control is acquired for "EA2".
This is to prevent the improper deletion of the database area. In step 2312, the table "CONTEX
Acquire a lock for "T". By acquiring the exclusive mode (X: Exclusive) as the lock mode, the concurrency for the table from other users is secured. If a mechanism for locking at the page level or row level is provided as a finer level of locking, such a table level exclusive mode can be set to IX (Inten
t Exclusive) mode is preferred.

In step 2314, the database management system 20 creates a row in the work area secured in the main memory 14 according to the basic row storage format, such as row 52 in the basic row database area 50 in FIG. assemble. At this time, the data in the column “CONTEXT” may be anything. In step 2316, it is checked whether or not there is long and large variable length data, and if there is data, the process proceeds to step 2318, and if not, the process jumps to step 2322. In step 2318, a long and large variable length data insertion process is performed. This long and large variable length data insertion processing will be described later with reference to FIG. In step 2320, the serial number LOBID assigned by the insertion of the large and large variable length data is set to the column “CONT”.
EXT ”data.

At step 2322, the page of the database area "AREA2" for storing the completed basic row is determined from the free area management information 51. Even if the page is in use, if there is a free area in which the basic row can be stored, the page in use is determined as the row storage page. If the used page has no free area, a new page is secured and the page is determined as the row storage page. Further, a row id is assigned to the basic row as an identifier. This row id is composed of a page number and a number indicating a storage position in the page.

In step 2324, the row id is used to lock the row level in exclusive mode (X: Exclusive).
To get. In step 2326, a row storage page buffer allocation request is issued to the buffer pool management unit 25, and the buffer is acquired in the buffer pool 32.
At this time, if the determined row storage page is the page in use, the buffer allocated to the page in use is acquired. If the determined row storage page is a new page, a free buffer is acquired.

In step 2328, before inserting the row into the acquired page buffer, the pre-update log is written for recovery processing due to a failure or the like. Writing the log means making a log record writing request to the log management unit 26 and writing the log in the log buffer 34. In the case of a database management system that holds a log of physical pages, a log write process is required, but a logical operation log (a log indicating that data has been inserted, for example, the SQL INSERT statement described above is used. If the same information) is held, log write processing is unnecessary.

In step 2330, a basic line is inserted into the page buffer. In step 2332, the log management unit 26 is requested to write the updated log.
The reason for writing the updated log is that the external storage device 30 is executed after the transaction including the insertion processing of the data is completed.
This is to make it recoverable in the case where a failure occurs, or when the system is stopped before the writing from the buffer pool 32 to the database area on the external storage device 30 is completed.

In step 2334, the buffer pool management unit 25 is requested to write the row storage page buffer. The write request requested to the buffer pool management unit 25 does not necessarily have to be immediately reflected in the database area on the external storage device 30. The buffer pool management unit 25 may write to the database area on the external storage device 30 when the page buffer needs to be written according to the management method of the buffer pool 32. However, by the time the transaction completes,
The log written in the transaction needs to be output to the log file 70.

FIG. 5 is a flowchart of the large and large variable length data insertion processing (step 2318). Step 24
In 10, the lock is acquired for the long and large variable length data database area "AREA3" that stores the long and large variable length data string "CONTEXT" of the specified table "DOCUMENT". In step 2412, a page buffer acquisition request is made to the buffer pool management 25 in order to assign a serial number LOBID to the long and large variable length data to be inserted, and the LOBID numbering information 61 is obtained in the page buffer. At this time, it is necessary to prohibit simultaneous access to the LOBID numbering information 61 from other users. Therefore, the buffer pool management unit 25 performs a latch when acquiring a page buffer (means that guarantees physical consistency of the buffer pool for a short period of time faster than lock).
Set the access prohibition state with. Note that simultaneous access may be prohibited by the method of acquiring the X mode lock.

At step 2414, the current number of the serial number LOBID is incremented by "1" to obtain a new serial number LOBID. In step 2416, the LOB
The page buffer storing the ID numbering information 61 is immediately written in the long and large variable length data database area 60. This is because the serial number LOBID is not a rollback target. If the serial number LOBID is not the rollback target, a new serial number will be assigned when recovering from a failure, but management can be simplified by newly assigning the serial number rather than recovering the serial number.

In step 2418, an area for storing large and large variable length data is secured. Similar to the basic row database area that stores basic rows, when managing an area in page units with a bitmap, the free area management information 62 is used.
Secure enough pages to store large and large variable length data.

At step 2420, the position information (page number) of the secured long and large variable length data storage area is held, and the data of the column name "CONTEXT" is written in the designated area. Since the long and large variable-length data has a long data length, the effect of reducing the input / output processing due to the buffering in the buffer pool 32 cannot be expected. Therefore, the column name "" stored in the work area secured in the main storage device 14 is stored. C
The data of "ONTEXT" is directly written in the database area 60 for large and large variable length data.

In step 2422, the column name "CONTE
The data of "XT" is written out as a log after updating. At this time, the updated log needs to be forcibly output to the log file 70 as well as written to the log buffer 34.

In step 2424, the data mapping area 63 corresponding to the serial number LOBID of the inserted data.
The position information of the inserted data is stored in the entry (FIG. 3).

By repeating the operations of newly adding a row, deleting a row, and updating a column, all pages in the database area are in the in-use state. However, in reality, a phenomenon occurs in which many pages are vacant. This is called disorder of data arrangement.
When the data arrangement is disturbed, when a new row is to be added, the processing time overhead for searching for an empty area for storing the row increases. Therefore, in order to reduce this overhead, it is necessary to perform processing for collecting the empty areas in the database area. This process is called database reorganization. Next, the operation of database reorganization will be described.

FIG. 6 is a block diagram of a computer system 10 including a database management system 20 according to an embodiment of the present invention. However, the configuration of the database management system 20 shows a portion related to reorganization. The database management system 20 includes a reorganization processing unit 27. The reorganization processing unit 27 includes a database search processing unit 271, an unload processing unit 272, and a reload processing unit 273. When the database reorganization request is input from the terminal 18, the database search processing unit 271 searches the requested database area for database definition information. The unload processing unit 272 unloads the data existing in the requested database area into the unload file 80 prepared in the external storage device 30. The reload processing unit 27
3 initializes the free space in the database area by initializing the free area management unit in the database area after the unload processing is completed. Then, the data is read from the unload file 80 and reloaded in the database area.

FIG. 7 is a configuration diagram of the unload file 80. The unload header section 81 is composed of information identifying an unload file, information indicating a basic row database area and a database area for large and large variable-length data as an unload type, and information on the creator of the unload file. It The table information unit 82 is composed of table definition information (information equivalent to the table management table 43 in FIG. 3) included in the unloaded database area. The column information unit 83 is composed of column information (information equivalent to the constituent column information management table 44 of FIG. 3) acquired based on the definition information of the table. The data section 84 stores the unloaded data.

Now, when a request for reorganizing the database area "AREA3" is input from the terminal 18, the database definition information searching unit 271 causes the data dictionary 4 to be searched.
From the database area management table 41 and the database area configuration file management table 42 existing in 0, it is acquired as information that the database area “AREA3” exists and is the database area for large and large variable length data.
Then, the database area for the large and large variable length data "A
Unload processing request of REA3 "
Issue to 72.

The unload processing unit 272 acquires a lock (X mode) for the database area "AREA3" for large and large variable length data. As a result, access to the long and large variable length database area "AREA3" is prohibited. However, the string "CO
Access to the table "DOCUMENT" is granted unless access to "NTEXT" is made.
Next, the unload processing section 272 writes the unload header section 81, the table information section 82, and the column information section 83 in the unload file 80. Next, the unload processing unit 272
Refers to the data mapping area 63 (FIG. 3) from the beginning and finds data having a value, the serial number LOBID, data length, and actual data are displayed in the data section of the unload file 80 as shown in FIG. Write to 84.
After writing all the data existing in the database area “AREA3” for large and large variable length data to the unload file 80, the control is passed to the reload processing unit 273.

The reload processing unit 273 initializes all the bitmaps of the database area "AREA3" for long and large variable length data included in the free area management information 62 (makes all data storage areas empty). Typically,
It is more efficient to initialize the bitmap data included in the free area management information 62 than to delete the data one by one at the time of reorganization. Next, the reload processing unit 273 reads the data in the order of the data stored in the unload file 80, and in the same manner as in FIG.
Insertion processing of long and large variable length data with D specified is performed. However, unlike the case of FIG. 5, the serial number LOBID numbering process (step 2414) is not necessary. Also, LOBID data mapping entry update processing (step 2424)
Then, the storage position information newly determined at the time of reloading is stored. When all the data is reloaded from the unload file 80, the lock (X mode) for the database area "AREA3" for long and large variable length data is released.

As described above, no access occurs to the basic row database area "AREA2" at the time of reorganization of the database area "AREA3" for large and large variable length data. Therefore, the update processing overhead can be reduced. Further, since the file management is not entrusted to the system program, there is no problem of security and processing efficiency.

[0034]

According to the database management system of the present invention, since the database area for basic data items and the database area for long and large variable length data items can be reorganized independently, the overhead is reduced and the processing efficiency can be improved. Further, since the file management is not entrusted to the system program, there is no problem in terms of security and processing efficiency.

[Brief description of drawings]

FIG. 1 is a configuration diagram of a computer system including a database management system (row insertion related part) according to an embodiment of the present invention.

FIG. 2 is a configuration diagram of a data dictionary.

FIG. 3 is a configuration diagram of a database area.

FIG. 4 is a flowchart of a row insertion process.

FIG. 5 is a flowchart of a process of inserting long and large variable length data.

FIG. 6 is a configuration diagram of a computer system including a database management system (reorganization related part) according to the embodiment of the present invention.

FIG. 7 is a configuration diagram of an unload file.

[Explanation of symbols]

 10: Computer system 12: CPU 14: Main storage device 18: Terminal 20: Database management system 21: Question analysis processing unit 22: Database operation processing unit 23: Basic row data management unit 24: Large and large variable length data management unit 25: Buffer Pool management unit 26: Log management unit 27: Reorganization processing unit 271: Database definition information search unit 272: Unload processing unit 273: Reload processing unit 30: External storage device 32: Buffer pool 34: Log buffer 40: Data dictionary 50 : Basic row database area 51: Free area management information 52: Row 60: Large and large variable length data database area 61: LOBID numbering information 62: Free area management information 63: Data mapping area 64: Large and large variable length data 70: Log fill 80 : Unload file

 ─────────────────────────────────────────────────── ─── Continuation of the front page (72) Inventor Norihiro Hara 12 890 Kashimada, Sachi-ku, Kawasaki-shi, Kanagawa Hitachi, Ltd. Information & Telecommunication Development Division (72) Kenichi Kitamura Onoe-machi, Naka-ku, Yokohama-shi, Kanagawa 6-81 Hitachi Software Engineering Stock Association In-house

Claims (3)

[Claims] 1. A first database storage area for storing data of basic data items (data items that are not large and large variable length data items) and a second database storage area for storing data of long and large variable length data items. The first database storage area is a data storage area for storing data of basic data items in physically fixed page units, and the availability of the data storage area in page units. A second large area variable storage area for storing data of long and large variable length data items;
A free area management area for managing the free space of the large and large variable length data storage area, a numbering area for adding a serial number that monotonically increases or decreases with the addition of the data of the large and large variable length data item, the serial number and the serial number A data mapping area for associating the storage position of the data of the large and large variable length data items, and the basic data item further includes a data item for managing the storage position of the data of the large and large variable length data items,
The database management system, wherein the data of the data item includes the serial number. 2. The database management system according to claim 1, when a request to insert data of a large and large variable length data item is given, a serial number is assigned to the data and the data is stored in a second database area. To store the correspondence between the storage position and the serial number in the data mapping area, create the data of the basic data item including the serial number as a data item, and store the data in the first database area. Characteristic database management system. 3. The database management system according to claim 1 or 2, wherein the serial number is not a rollback target.