JP2001256085A - Relational data base management system - Google Patents

Abstract

PROBLEM TO BE SOLVED: To provide a relational data base managing system capable of objectively grasping the using situation of a data base file, and preventing the failure of a system due to the excess of a data base file capacity. SOLUTION: This relation data base managing system for managing a data base 7 in which a data base file 71 having a table 73 constituted of columns and lines is prepared is provided with an umber of stored record calculating part 11 for calculating the maximum number of records which can be stored in a table 73 based on the size of an extent constituted of data areas preset in the table 73 and the total sum of the sizes of columns and a file activity ratio calculating part 12 for calculating the activity ratio of the data base file 71 based on the maximum number of stored records calculated by the number of stored record calculating part 11 and the actual number of stored records.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a relational database management system for managing a relational database in which a database file having a table composed of columns and rows is created.

[0002]

2. Description of the Related Art A relational database management system stores one database in a database in response to an operation from a user.
The above database files are created, and a table is created for each database file. Each created table is composed of, for example, columns (columns) such as an employee number, a name, an affiliation, and a business, and rows (rows) in which information of each employee is set and combined in these columns. In addition, the relational database executes processes such as data search, insertion, deletion, and update in a database storing a plurality of tables in response to an inquiry from a user.

Further, conventionally, a relational database management system is used to monitor the capacity of a database as part of system operation management. in this case,
Monitor the usage status of the database file using the function of the relational database management system, or simply calculate the number of bytes of the database file,
The capacity of the database is monitored by calculating the usage rate of the database file based on the calculated number of bytes and the actual number of bytes of the data stored in the database file.

[0004]

However, in the conventional capacity monitoring method, the usage status of the database file is calculated in terms of the number of bytes. Therefore, the usage rate of the database file is objective even if it is numerically accurate. She lacked gender. For this reason, it was not efficient in system operation management.

Further, when the amount of data stored in the file reaches the capacity of the database file, a failure occurs in the system. For this reason, there has been a demand for avoiding system failures.

SUMMARY OF THE INVENTION An object of the present invention is to solve the above-mentioned problem, and an object of the present invention is to make it possible to objectively grasp the use status of a database file and thereby to manage the operation of the system. An object of the present invention is to provide a relational database management system that can be performed efficiently and that can prevent a system failure due to an excess of a database file capacity.

[0007]

To achieve the above object, a relational database management system according to the present invention manages a database in which a database file having a table including columns and rows is created. A storage number calculation means for calculating the maximum number of records that can be stored in the table based on the total size of the extents and the sizes of the columns consisting of a data area preset in the table, and the storage number calculation means And a utilization rate calculating means for calculating a utilization rate of the database file based on the maximum number of stored records and the actual number of stored records.

According to this invention, the number-of-storage-calculating means calculates the maximum number of records that can be stored in the table based on the extent size and the sum of the column sizes, and the usage rate calculating means calculates the maximum record Calculate the database file usage rate based on the number of records and the actual number of records. That is, since the usage status of the database file is not calculated in the conventional byte unit but calculated in the record unit, the usage status of the database file can be grasped objectively.

An alert output means for judging whether or not the usage rate of the database file calculated by the usage rate calculation means has reached a preset threshold value and outputting an alert when the usage rate has reached the threshold value is provided. It is characterized by having.

According to the present invention, an alert is output when the usage rate of the database file reaches the threshold value, so that a system failure due to an excess of the database file capacity can be notified in advance.

Further, when an alert is received from the alert output means, a process changing means for stopping writing of data to the table which has reached the threshold value and changing to a process of writing to the first table, and a new database file. A file creation unit that creates and creates a second table in the database file; and a writing unit that writes data in the table that has reached the threshold value and data in the first table to the second table. .

According to the present invention, when an alert is received, the writing of data to the table that has reached the threshold is stopped, the process is changed to the writing process for the first table, and a new database file is created. Since the second table is created in the file and the data in the table that has reached the threshold and the data in the first table are written in the second table, a failure can be avoided without stopping the system.

[0013]

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS Hereinafter, a relational database management system according to an embodiment of the present invention will be described in detail with reference to the drawings. The relational database management system according to this embodiment has a database capacity monitoring function and a failure avoidance function.

FIG. 1 is a diagram showing a configuration of a relational database management system according to an embodiment. FIG.
Is a client device 1 such as a personal computer connected to a network 2 and making an inquiry to a server 3;
A server 3 that provides information in response to an inquiry from the client device 1 is provided.

The server 3 includes a computer (machine) 5 and a database 7 such as a disk device connected to the computer 5. Note that the database 7 may be built in the computer 5.

The database 7 stores a plurality of database files 71a to 71i. The database files 71a to 71f constitute an instance A, and the database files 71g to 71i constitute an instance B.

Here, an instance is a group of memory structures and processes that communicate with a user process and control and operate data stored in a disk device. An instance is a combination of a system global area (SGA) and a process. The instance A is set, for example, for general affairs of the company, and the instance B is set, for example, for business of the company.

The database files 71a to 71i are:
As shown in FIG. 5, there are tables 73a to 73i composed of columns (columns) and rows (rows). In FIG. 5, each column of an employee code, an employee name, a zip code, and an address is set, and a column size is set for each column.

The data dictionary view 21 provided in the system management unit 20 is a virtual table that does not exist and is generated from a plurality of tables in the database 7. As shown in FIG. Database file names F1 to F6 in the instance A) and the size FS1 of the file
FS6, table names T1 to T6, extent sizes ES1 to ES6 of the table, and extended extent sizes EE1 to EE6.

Each table includes an initial extent, a first extended extent, and a second extended extent, as shown in FIG. An extent is a logical database area composed of continuous data blocks. When a table is created, an initial extent consisting of a specified data block is allocated. If the initial extent is full, the first extent is sequentially extended. , Second
Allocate extended extents.

As shown in FIG. 6, the extent comprises a header as an index and a real data area, and the real data area comprises a plurality of data blocks. Each data block is composed of a plurality of records, and each record is composed of a sum of column sizes.

The computer 5 constitutes a relational database management system, a capacity monitoring unit 10 for monitoring the capacity in the database 7, a data dictionary view 2
A system management unit 2 that has a database 1 and manages a database 7
0, a failure avoiding unit 30, and a shared memory 40.

The capacity monitoring unit 10 includes a record storage number calculation unit 11 corresponding to the storage number calculation unit, a file usage ratio calculation unit 12 corresponding to the usage ratio calculation unit, and an alert output processing unit 13 corresponding to the alert output unit. are doing.

The record storage calculation section 11 calculates the maximum number of records that can be stored for each table based on the sum of the extent size and the column size. The file usage rate calculation unit 12 calculates the file usage rate based on the maximum number of record storages calculated by the record storage number calculation unit 11 and the actual number of record storages. The alert output processing unit 13 determines whether or not the file usage rate calculated by the file usage rate calculation unit 12 has reached a preset threshold value, and issues an alert (warning) when the file usage rate has reached the threshold value. Output to the console 14.

The computer 5 is connected with a keyboard 8 for starting and requesting log file output, a console 14 for outputting alerts and log files, and a printer 15 for printing alerts and log file outputs.

The failure avoiding unit 30 has a process changing unit 31 corresponding to a process changing unit, a file creating unit 32 corresponding to a file creating unit, a file saving unit 33, and a writing unit 34 corresponding to a writing unit. are doing.

When the process change unit 31 receives an alert from the alert output processing unit 13, the process change unit 31 stops writing to the table whose threshold value has been reached, and stores the shared memory table 41 of the same structure on the shared memory 40 (the first table ) And the process is changed to the process of writing to the shared memory table 41.

The file creating section 32 creates a new database file in another disk device and creates a table (corresponding to a second table) in the file. The file saving unit 33 saves the data in the table that has reached the threshold value to another file. The writing unit 34 writes the data of another saved file and the data of the shared memory table 41 into the table created by the file creating unit 32.

Next, the operation of the relational database management system of the embodiment configured as described above will be described in detail with reference to the drawings. First, the file usage rate calculation processing by the capacity monitoring unit 10 will be described with reference to FIGS. 2 to 6 and the flowchart shown in FIG. Here, processing between the client device 1 and the server 3 will be described.

The keyboard 8 is used instead of the client device 1, and data stored in the database 7 is stored via the computer 5 in accordance with an operation from the keyboard 8.
Search, update, deletion, etc. may be managed.

First, when a request is made from the client device 1 to the server 3 via the network 2, the system management unit 20 connects to the specified instance (for example, instance A) (step S11).

Then, the data dictionary view 21 in the system management unit 20 is searched for the view of FIG. 2 showing the database file name and its file size, the table name and the extent size of the table (step S13), and A list of tables existing in the instance (for example, instance A) to be executed, database files using the tables, and extent sizes is created (step S15).

Next, a table showing system constants is searched in advance from the dynamic performance table, and the values of the system constants are statically set (step S17). Here, the dynamic performance table refers to several tables used for controlling and monitoring the performance. For example, when the number of client devices (sessions) changes, the value dynamically changes. Further, the system constant changes with the number of bytes of each column constituting the record when the number of bytes changes with a fixed length or a variable length.

Next, the header size of the header area in the extent is determined using the set system constant (step S19). That is, the header size changes according to the changed system constant. This header size is used when calculating the maximum recordable number of records as described later.

Further, the size of the usable area per data block size (for example, 102
4 bytes) of the static data dictionary view
The view of FIG. 4 showing the table name, the column name of the table, and the size of the column is searched, and the sum of the column size per record is obtained using the search result (step S21).

In the view of FIG. 4, for example, a plurality of columns cm11 to cm1n and a plurality of column sizes (8 bytes, 16 bytes...) Corresponding to the plurality of columns cm11 to cm1n are stored for the table T1. You. The sum of the plurality of column sizes is the sum of the column sizes, and corresponds to one record.

Then, the record storage number calculation unit 11
Divide the data block size by the sum of the column sizes,
The integer part of the obtained value is set as the number of records that can be stored per data block (step S23).
Further, the number of records that can be stored in a plurality of data blocks in the table, that is, the maximum number of records that can be stored in a table unit is obtained (step S2).
5).

The record storage number calculation unit 11 subtracts the header size from the extent size, divides the obtained value by the sum of the column sizes (record length), and converts the integer part of the obtained value into a table unit. The maximum number of records that can be stored can also be used. Therefore, the maximum number of records that can be stored in a table unit can be obtained without using a data block.

Next, the system management unit 20 searches for the number of records actually stored in the target database file (step S27), and uses the found actual number of records stored as the file usage rate calculation unit 12. Output to The file usage rate calculation unit 12 divides the searched actual number of stored records by the maximum number of stored records calculated by the number of stored records calculation unit 11, and obtains the obtained value,
That is, a file usage rate (extent usage rate) is obtained (step S29).

Then, it is determined whether or not the calculation processing of the file usage rate has been completed for all the tables (step S31). When the calculation processing has been completed for all the tables, the alert output processing unit 13 sets Every,
It is determined whether the file usage rate obtained by the file usage rate calculation unit 12 exceeds a set value (threshold). Then, a file whose file usage rate exceeds the threshold is marked up in the calculation result (step S33). next,
Output all calculation results to a log file (step S
35).

When the file usage rate exceeds the threshold value, the statistics of the amount of data written per hour are prepared in advance so that the extended extent is generated until the extent is generated.
Alternatively, it is also possible to calculate how many hours there is a margin before the capacity is exceeded.

The log file format is csv (Comma
By converting to a Separated Value) format (* .csv) or the like and outputting it, loading to a spreadsheet becomes possible. Furthermore, in the calculation result of the file usage rate,
If there is a table exceeding the threshold, an alert is output to the console 14 (step S37).

Next, the fault avoiding process by the fault avoiding unit 30 will be described with reference to the drawing of FIG. 8 and the flowchart shown in FIG. The failure avoiding unit 30 starts a failure avoiding process when receiving an alert from the alert output processing unit 13.

Here, for example, it is assumed that the file usage rate of the database file 71a having the table 73a exceeds the threshold value and an alert is raised.

First, the number of writes per hour (= t
rns), and as shown in FIG. 9, a table of the database 7 in which the size is a value obtained by multiplying the calculated number of writes by the sum of column sizes (= columns) obtained by the capacity monitoring unit 10. A shared memory table 41 having the same structure as that described above is created in advance (step S5).
1). The size of the shared memory table 41 may be any number of hours.

When writing to the database 7 has already been performed using the shared memory table 41,
After the reading from the currently used shared memory table 41 and the writing to the database 7 are all completed, a new shared memory table 41 is created, or the shared memory table 41 is created under a different name, and two shared memory tables are created when the database is reconstructed. It is necessary to read from 41.

Next, the process change unit 31 searches for a process that is writing to the table 73a for which an alert has been raised (step S53), stops the searched process, and writes the process to the shared memory table 41. (Step S55). That is, data is written to the shared memory table 41.

Next, the table 73a is determined (step S57), and the number of writes per hour (= trns)
As shown in FIG. 10, the calculated number of writes is multiplied by the number of hours until the work is completed (= t) in the future, and the number of records stored in the determined table 73a (=
records) and the expected number (= tota)
1 records).

Further, the file creation unit 32 calculates the total (= column) of the column sizes obtained by the capacity monitoring unit 10.
s) multiplied by the expected number of total records,
A new database file 71j having a file size of a value obtained by multiplying the obtained value by 1.1 to 1.2 times as a margin is created on another disk, for example. (Step S59).

If the database file 71j can be secured as an additional portion in the same disk, ((t
rns * t) * columns) * 1.1 or ((t
rns * t) * columns) * 1.2. However, the margin in this case is also arbitrary.

Further, the table 73a is exported (step S61). That is, the file saving unit 33
Stores the table 73a in another database file 71k
To the table 73k. Then, the table 73a for which the alert has been raised is deleted (step S6).
3).

Next, the same table as the deleted table 73a is created in the created database file 71j. At this time, the initial extent size is column
The value of s * total records is used, and the extended extent size is columns * total record.
rds * 0.1. (Step S65).

Next, the writing unit 34 imports the exported dump file 73k into the created table 73j (step S67).
A process of writing the data written in the shared memory table 41 to the created table 73j is started.
(Step S69). Then, the process is restored (step S71).

As described above, according to the relational database management system of the embodiment, the maximum number of records that can be stored for each table is calculated, and the calculated maximum number of records that can be stored is compared with the actual number of records that can be stored. Find the rate. That is, the database file usage status (extent usage status) is not calculated in the conventional byte unit, but is calculated in record units, so that the usage status of the database file (extent usage status) can be grasped objectively.

Further, it is possible to calculate whether the file usage rate has reached a preset threshold value or how many more files can be stored up to the threshold value, and output a log file. Also,
By setting the log file in a csv format (* .csv) or the like, the log file can be imported into a spreadsheet.

When the file usage rate has reached the threshold value, an alert can be raised to notify the user of the system performance deterioration due to the occurrence of the extended extent and the system failure due to the excess of the database file capacity.

In addition, for a table that has reached the threshold value, an alert is received, the process that is writing is temporarily stopped, the process is changed to a process that writes to the shared memory table, and during that time the database is reconstructed, and the data is rewritten. By starting read / write from the migration and shared memory table, failures can be avoided beforehand without stopping the operating system. In addition, since writing to the database is temporarily suspended and a new database is constructed, this is effective for business requirements with relatively low real-time properties.

As a modification of the relational database management system of the embodiment, a relational database management system as shown in FIG. 12 may be used. In a modified example of the relational database management system shown in FIG. 12, a log file of the calculation result can be sent by e-mail as an attached file to notify the user.

It is also possible to dial up to a subscribing telephone or a mobile telephone to send a voice message and notify an alert. Further, the user can be notified of the log file by fax transmission. Furthermore, an alert can be notified by dialing up to a pager (registered trademark).

[0060]

As described above, according to the present invention,
Since the usage status of the database file is calculated not in the conventional byte unit but in the record unit, the usage status of the database file can be grasped objectively.

Further, when the usage rate of the database file reaches the threshold value, an alert is output, so that a system failure due to an excess of the database file capacity can be notified in advance.

Further, when an alert is received, a failure can be avoided without stopping the system.

[Brief description of the drawings]

FIG. 1 is a diagram showing a configuration of a relational database management system according to an embodiment of the present invention.

FIG. 2 is a diagram showing a data dictionary view.

FIG. 3 is a diagram showing an extent and an extended extent in a table.

FIG. 4 is a diagram showing a static data dictionary view.

FIG. 5 is a diagram illustrating an example of a table including columns and rows.

FIG. 6 is a diagram showing a detailed configuration of an extent.

FIG. 7 is a flowchart illustrating a file usage rate calculation process performed by the capacity monitoring unit.

FIG. 8 is a diagram for explaining a fault avoiding process by a fault avoiding unit.

FIG. 9 is a diagram showing a table size created in a shared memory table.

FIG. 10 is a diagram showing a file size created in a new database file.

FIG. 11 is a flowchart illustrating a fault avoiding process performed by a fault avoiding unit.

FIG. 12 is a diagram showing a modification of the relational database management system of the embodiment.

[Explanation of symbols]

 REFERENCE SIGNS LIST 1 client device 3 server 5 computer 7 database 10 capacity monitoring unit 11 record storage count calculation unit 12 file usage rate calculation unit 13 alert output processing unit 14 console 15 printer 20 system management unit 30 failure avoidance unit 31 process change unit 32 file creation unit 33 file saving unit 34 writing unit 40 shared memory 71a-71i database file 73a-73i table

Claims (3)

[Claims] 1. A relational database management system for managing a database in which a database file having a table composed of columns and rows is created, wherein a size of an extent comprising a data area preset in the table and a size of the column Means for calculating the maximum number of records that can be stored in the table based on the sum total of the number of stored records, and the database based on the maximum number of records stored and the actual number of records that are calculated by the stored number calculation means. A relational database management system, comprising: utilization rate calculation means for calculating a utilization rate of a file. 2. An alert for determining whether or not the usage rate of the database file calculated by the usage rate calculation means has reached a preset threshold value, and outputting an alert when the usage rate has reached the threshold value. 2. The relational database management system according to claim 1, further comprising an output unit. 3. A process change unit that stops writing data to the table that has reached a threshold value and changes to a process of writing to the first table when an alert is received from the alert output unit, and a new database. File creation means for creating a file and creating a second table in the database file; and writing means for writing data in the table and data in the first table that have reached a threshold value in the second table. The relational database management system according to claim 2, further comprising: