JP2008052635A - Database access system and its program - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To maintain access performance to a vertically divided database by reducing the probability of displacement processing of data generated at high frequency in association with the addition of data entry. <P>SOLUTION: A database access program provided at a mainframe connected to a network to convergently process access requests to the database vertically divided into a basic section and an extended section, from a plurality of client computers, allows the mainframe to carry out processing which includes processing of determining whether there is a space in the basic section by comparing the effective data number of the basic section with the maximum repetition amount of basic section data in the basic section, and data ejection processing of putting a plurality of data together and displacing it from the basic section to the extended section if there is no space as a result of determination. <P>COPYRIGHT: (C)2008,JPO&INPIT

Description

  The present invention relates to a database access method in a computer system, and more particularly to a database access system and a program for smoothly adding data to a vertically divided database.

  One type of database management system is a relational database, which has the feature that different data can be easily combined using key data (management number, name, etc.). .

In addition, there is SQL (Structured Query Language) as a kind of relational database operation language, ANSI (American National Standards American Standards Association).
(Institute), ISO, and JIS. This SQL is an operation language for a relational database system and a structured query language for a database to be accessed. And data is acquired by issuing SQL for every item defined in the table. That is, it is used as a general access method to a relational database.

  By using this SQL, most operations related to the relational database such as table definition, data operation, and relational operation can be described as text that can be read by a machine. Then, it becomes possible to standardize the operation language of the database management system for the database to some extent. In addition, data definitions obtained in units of rows and data definitions for vertical division are also known in connection with SQL.

  FIG. 7 is an explanatory diagram of data definition acquired in units of rows in a conventional relational database access method. As shown in FIG. 7, when the amount of data is very large, there is a method in which data is acquired in units of rows and necessary items are separated on the memory in order to eliminate the disadvantage that the access time is prolonged. However, even if this method is used, it is inevitable that the access speed is reduced to some extent according to the length (amount) of data. That is, there is a problem that the effect of shortening the access time is reduced for data with long rows.

  Note that NULL shown in FIG. 7 is defined up to the theoretical maximum number of repetitions, which means that one key has a large number of repetitions theoretically. However, in actual data, a repetition amount smaller than the theoretical maximum value is usually sufficient.

  FIG. 8 is an explanatory diagram of data definition to be vertically divided in a conventional access method to a relational database. As shown in FIG. 8, the data to be accessed is defined to have a table (hereinafter referred to as “basic part”) having a defined amount until the most data fits, and the remaining repetition amount. It is divided vertically into a table (hereinafter referred to as “extended portion”). With this vertical data definition, it is possible to reduce the burden of data processing when accessing a database.

The extension unit shown in FIG. 8 is necessary when the amount of data handled is so large that it cannot be accommodated in the basic unit, and the definition of NULL in the extension unit is as follows.
Extension NULL = theoretical maximum repetition-maximum repetition of the basic part

  When designing a database, in particular, regarding the capacity, data that is scheduled to be entered once (unit) is usually designed to have a capacity that can be accommodated only by the basic part in the database. Therefore, the total amount of data to be entered is not a problem.

  However, as the number of accesses to the database increases, redundant parts of records increase due to history, logs, etc., so that it is well known that the database itself will grow over time (increase in the number of accesses). .

  For this reason, it is naturally impossible to attempt additional data entry even though the record capacity of the basic part has already reached the allowable limit. In such a case, there has been a conventional technique in which data records are avoided from the basic part to the extended part by the number of additional entry records.

In addition, the following patent document is mentioned as a well-known technical document relevant to this invention.
JP-A-06-052231 JP 07-064835 JP 2002-108669 A

  However, when such additional data entries for the basic part frequently occur, it takes time to move data from the basic part to the extension part. There is a problem that it is difficult to take advantage of the advantages. That is, when accessing a database in which additional entries occur frequently, there is a problem that it is difficult to reduce data processing even if a vertically defined data definition is used.

  In view of the above situation, the present invention provides a database access system and its program that can maintain access performance when performing data movement processing that occurs with the addition of high-frequency data to a vertically divided database. The purpose is to provide.

  In order to solve the above problems, the invention according to claim 1 is directed to a database access system having a mainframe as a core, which is connected to a network and centrally processes access requests from a plurality of client computers. A database characterized by comprising: a vertically divided database; and data eviction processing means for moving a plurality of data from the basic part to the extended part if there is a record addition exceeding the capacity of the basic part. Access system.

  According to the first aspect of the present invention, if the capacity of the basic part is exceeded once, the data eviction processing means moves a plurality of data from the basic part to the expansion part at a time. A certain amount of space is secured without having to move data one by one. Therefore, even if additional data occurs frequently, the probability that data movement is necessary can be reduced. That is, it is possible to maintain the access speed by reducing the time required for data movement and the processing burden to the minimum necessary.

  According to a second aspect of the present invention, in addition to the database access system according to the first aspect, the data eviction processing means includes a parameter file for simultaneously executing a specified number at the time of the data movement. It is characterized by that.

  According to the second aspect of the invention, the number of data movements can be set to an optimum value according to the number of movements specified by the parameter.

  The invention according to claim 3 is a database access program provided in a mainframe for centrally processing access requests to a database vertically divided into a basic part and an extension part from a plurality of client computers connected to a network. A process for determining whether or not there is a vacancy in the basic part by comparing the number of valid data in the basic part with respect to a maximum repetition amount of the basic part data in the basic part; A database access program characterized by causing the mainframe to execute a process including a data eviction process for moving a plurality of data collectively from one to an extension unit.

  According to the invention of claim 3, there are substantially the same effects as the invention of claim 1.

  According to a fourth aspect of the present invention, in addition to the database access program according to the third aspect, the data eviction process includes a process of acquiring a movement number from a parameter file in order to move the plurality of data collectively. It is characterized by.

  According to the invention of claim 4, there are substantially the same effects as the invention of claim 2.

  According to the first and third aspects of the invention, data movement can be reduced to the minimum necessary, the time required for data movement can be reduced, and overall performance including access speed can be maintained.

  According to the second and fourth aspects of the invention, the number of data movements can be set to an optimum value.

Hereinafter, an embodiment of the present invention (hereinafter referred to as “the present embodiment”) will be described in detail with reference to the drawings. FIG. 1 is a system configuration diagram of a mainframe provided with a database access program according to the present embodiment. As shown in FIG. 1, the mainframe 101 includes a database access program 102, a database (basic unit) 103, a database (extended unit) 104, a parameter file (hereinafter simply referred to as “parameter”) 107, and a database access. A computer system (hereinafter also referred to as “this system”) including a business program 108 that uses the program 102. A business terminal 109 for giving work instructions is connected to the main frame 101.
The data structure in the database will be described later with reference to FIG.

The mainframe is a general term for high-speed, large-capacity computers, and processes requests from a plurality of client computers in a centralized manner with respect to personal computers that are basically owned on an individual level. Means a large computer.
Note that there is a host computer that is defined almost the same as the mainframe, which broadly means a computer that provides a service in a network environment.
These mainframes and host computers typically provide high-speed arithmetic processing services and database services.

  A database basic part (hereinafter abbreviated as “basic part”) 103 stores basic part data 105 that is a basic part of vertically divided data, and a database expansion part (hereinafter abbreviated as “expansion part”) 104. Stores extension part data 106 which is an extension part of vertically divided data. The description of the access method using SQL for these relational databases will be continued.

  FIG. 2 is a data configuration diagram according to the present system, wherein (a) basic part data and (b) extension part data. These are the configuration examples of the basic part data 105 and the extended part data 106 shown in FIG. 1. Here, the theoretical value of the data repetition amount is 1000, the basic part data is divided into 10, and the extended part data is divided into 990. An example is shown below.

  The basic part data 105 includes a primary key D201, an extended part head key D202 that stores the first subkey of the extended part data 106 for the data of the primary key D201, and the number of valid data in the basic part data 105. Is composed of a valid data count D203 and a data item D204. Further, the data item D204 is composed of a sub key D205 and a data content D206.

  The data items D204 are arranged in the ascending order (or descending order) of the subkey D205, and are stored in a left-justified manner. Since the repetition amount of the basic part data 105 is assumed to be 10 at the maximum, the data item D204 exists from D20001 to D20010.

  The extension part data 106 is composed of D211 which is a primary key, a valid data number D213 indicating the number of valid data in the extension part data 106, and a data item D214. Further, the data item D214 includes a sub key D215 and a data content D216.

  The data items D214 are arranged in the ascending order (or descending order) of the subkey D215, and are stored in the left-justified order. Here, it is an example in the ascending order. The expansion unit 104 stores data that cannot be accommodated in the basic unit 103 and overflows. Since the maximum repetition amount of the extension data 106 is assumed to be 990, there are data items D214 from D21001 to D21990.

  Here, when a user (not shown) operates the business terminal 109 shown in FIG. 1 to start the business program 108 and make an access request, the database access program 102 accesses the database. In such an access operation as a whole, the operation is a user and the execution subject is a computer.

  However, instead of omitting the obvious explanations of "user terminal operation" and "execution of program by computer", each program itself may be anthropomorphic as if it were an intentional execution subject. is there. This is for convenience of explanation, and in the case described above, it can be simply described as “access request from the business program 108 to the database access program 102”.

  If there is a request from the database access program 102 to access the database in response to an access request resulting from the execution of the business program 108 shown in FIG. 1, the data items D204 and D214 shown in FIG. Access processing is executed in units. At this time, by specifying the primary keys D201 and D211 and the secondary keys D205 and D215 and executing the database access program 102, the corresponding data is searched from the basic part data 105 or the extended part data 106, and the access process is performed. Do.

  FIG. 3 is a flowchart showing execution of a data addition request. A case where a data addition request is made from the business program 108 to the database access program 102 will be described along FIG. 3 with reference to FIGS.

  As shown in FIGS. 1, 2, and 3, primary data D201 and D211, secondary keys D205 and D215, and additional data items D204 and D214 are input as input data from the business program 108 to the database access program 102. . (D301)

  First, the basic part data 105 is acquired from the database using the primary key D201 (S301). At this time, it is determined whether there is target data to be acquired (S302). If this determination (S302) is No and data does not exist, the basic part data 105 is newly created with the primary key D201 and the additional data item, and the data addition request is terminated (S308).

  If the determination (S302) is Yes and data exists, it is determined by comparing whether or not the subkey to be added is smaller than the subkey of the last data item in the acquired basic data 105 ( S303). If this determination (S303) is Yes and the subkey to be added is smaller than the last subkey of the basic unit 103, this additional data is added to the basic unit 103, and the next determination (S309) is performed. It is determined whether or not there is a space in the basic part data 105.

  In the determination (S309), it is determined whether there is a vacancy by comparing the number of valid data D203 with the maximum repetition amount (here, 10) of the basic data 105 (S309). If this determination (S309) is No and there is no free space, a data eviction process (S310) is performed in which a plurality of data items are moved collectively from the basic data 105 to the extended data 106. Details of the data eviction process (S310) will be described later.

  If the determination (S309) is Yes and there is a vacancy, an additional data item D204 is inserted at the corresponding location in the basic data 105, and the process ends (S311).

  If the determination (S303) is No and the subkey to be added is larger than the last subkey of the basic unit 103, it is determined whether or not the expansion unit head key D202 is NULL (whether or not the expansion unit data exists). (S304). If this determination (S304) is Yes and NULL, the extension unit 104 does not exist in the basic unit data 105. Therefore, it is determined whether or not there is a space in the basic part data 105 (S312).

  If the determination (S312) is Yes and there is a vacancy, an additional data item is inserted into the corresponding location in the basic part data 105 as it is, and the process ends (S313). On the other hand, if the determination (S312) is No and there is no free space, the expanded portion data 106 is newly created from the primary key D211 and the additional data item D214, and the process ends (S314).

  If the determination (S304) is No and the extension part head key D202 is not NULL, the extension part 104 exists in the basic part data 105. Therefore, it is determined by comparing whether or not the subkey to be added is smaller than the extension head key D202 (S305).

  If the determination (S305) is No and the subkey to be added is greater than or equal to the extension head key D202, this data is added to the extension 104. Therefore, the extension part data 106 is acquired from the primary key D211 (S306), and an additional data item D214 is inserted at the corresponding place in the extension part data 106, and the process is terminated (S307).

  If the determination (S305) is Yes and the subkey to be added is smaller than the expanded portion head key D202, it is determined by checking whether or not there is a free space in the basic portion data 105 (S315).

  If the determination (S315) is Yes and there is a vacancy, the additional data item D204 is inserted into the corresponding location in the basic part data 105 as it is, and the process ends (S316). If the determination (S315) is No and there is no space, the extension part data 106 is acquired from the primary key D211 (S317), and an additional data item D214 is inserted at the head and the process ends (S318).

The data eviction process (S310) in FIG. 3 will be described in detail using FIGS. 4, 5, and 6 in order.
FIG. 4 is a flowchart showing the data eviction process. As shown in FIG. 4, first, the number of movements is obtained from the parameter 107 (FIG. 1) (S401). The number of movements is the number of data items moved from the basic unit 103 to the expansion unit 104 at a time, and a plurality of data items are moved together at the same time. Next, the extension part data 106 is acquired from the extension part 104 using the primary key D211 (S402).

  Next, the data item of the extension unit 104 is moved backward by the number of movements (S403). As a result, the storage location of the data item moved from the basic unit 103 is secured.

  Next, the insertion position into the basic unit 103 is obtained using the sub key D205 (S404). Here, it is determined whether or not the insertion position is larger than the reference value (S405). If this determination (S405) is Yes and the insertion position is larger than the reference value (maximum repetition amount of basic unit 103−movement number + 1), there are data items greater than or equal to the movement number behind the data to be added.

  If the determination (S405) is Yes, the data item D204 of the basic unit 103 is moved from the end to the top of the expansion unit 104 by the number of movements (S406). Next, additional data is inserted into the basic unit 103 (S407).

  On the other hand, if the determination (S405) is No and the insertion position is smaller than the reference value (maximum repetition amount of basic unit 103−number of movements + 1), the data to be added is included in data item D204 of basic unit 103 to be moved. It is.

  Therefore, if the determination (S405) is No, the data item D204 of the basic unit 103 is moved from the end to the beginning of the expansion unit 104 by the number of movements minus one (S408). Thereafter, an additional data item D214 is added to the extension unit 104.

  FIG. 5 is an explanatory diagram of data from (S405) to (S406) in the data eviction process shown in FIG. 4, and shows (a) basic data and (b) extended data. As shown in FIG. 5, when data is added to the data shown in FIG. 2, the determination (S <b> 405) is Yes, and the insertion position (8) is the reference (maximum repetition amount of the basic unit 103 −number of movements + 1). ) An example in the case of value (8) or less is shown. Here, since the number of movements is assumed to be 3, the data item D214 of the expansion unit 104 is moved backward 3 (D51004 to D51006).

  If the subkey of the data to be added is 20050075, the insertion position of this data is next to the subkey 2005007 and is 8. In the determination (S405), when the reference position (maximum repetition amount of the basic unit 103−movement number + 1) value = 10−3 + 1 = 8 and the insertion position 8 are compared, the insertion position 8 ≦ the reference value 8 is satisfied, and therefore, “Yes”. Therefore, the process (S406) causes the extension unit 104 to move from the back of the data item of the basic unit 103 by the number of movements 3 (D51001 to D51003). Thereafter, additional data is inserted into the corresponding position of the basic unit 103 by the process (S407), and the process ends (D50008).

  FIG. 6 is an explanatory diagram of data from (S405) to (S408) in the data eviction process shown in FIG. 4, and shows (a) basic part data and (b) extension part data. As shown in FIG. 6, when data is added to the data shown in FIG. 2, the determination (S405) is No, and the insertion position (10) is the reference (maximum repetition amount of basic unit 103−number of movements + 1). ) An example in which the value is greater than (8) is shown. Here, since the number of movements is assumed to be 3, the data item D214 of the expansion unit 104 is moved backward by 3 (D61004 to D61006).

  Assuming that the subkey of the data to be added is 20050095, the insertion position of this data is next to the subkey 2005090, so it is 10. In the determination (S405), when the insertion position 10 is compared with the reference (the maximum repetition amount of the basic unit 103−the number of movements + 1) value = 10−3 + 1 = 8 and the insertion position 10, the result is No. Therefore, the processing unit (S408) moves the extension unit 104 from the rear of the data item of the basic unit 103 by the number of movements 3-1 = 2 (D61001, D61003). Thereafter, the additional data is inserted into the corresponding position of the extension unit 104 through the process (S409), and the process ends (D61002).

  As described above, when data is to be added exceeding the allowable limit of the basic unit 103, a plurality of data is collectively moved from the basic unit 103 to the expansion unit 104. As a result, an empty space is provided in the basic unit 103, so that it is possible to reduce the probability that data movement is necessary even if additional data is frequently generated thereafter.

  In particular, once the basic unit 103 reaches the allowable limit, data must be moved every time data is added to the basic unit 103. Therefore, making the basic unit 103 empty according to the present invention is effective in reducing the processing load of data movement. Also, by giving the number of movements with an external parameter 107, an optimum value can be set for each table.

It is a system configuration figure of the mainframe provided with the database access program concerning this embodiment. It is a data block diagram concerning this system, and is (a) basic part data and (b) expansion part data. It is a flowchart which shows execution of a data addition request. It is a flowchart which shows a data eviction process. It is explanatory drawing of the data from (S405) to (S406) among the data eviction processes shown in FIG. 4, (a) Basic part data and (b) Extended part data are shown. It is explanatory drawing of the data from (S405) to (S408) among the data eviction processes shown in FIG. 4, (a) Basic part data and (b) Extended part data are shown. It is explanatory drawing of the data definition acquired per line in the conventional access method to a relational database. It is explanatory drawing of the data definition divided | segmented vertically in the conventional access method to the relational database.

Explanation of symbols

101 Mainframe 102 Database access program 103 Database (basic part)
104 Database (extended part)
107 Parameters (file)
108 Business Program 105 Basic Data 106 Extended Data D201, D211 Primary Key D202 Extended Data Head Key D203 Number of Valid Data D204 Data Item D205, D215 Subkey D206, D216 Data Contents D213 Valid Data Number D214 Data Item

Claims (4)

In a database access system having a mainframe connected to a network and centrally processing access requests from a plurality of client computers,
A database divided vertically into a basic part and an extended part;
A database access system comprising: data eviction processing means for moving a plurality of data from the basic part to the extension part if there is a record addition exceeding the capacity of the basic part. The data eviction processing means includes
2. The database access system according to claim 1, further comprising a parameter file for collectively executing the number designated at the time of data movement. In a database access program provided in a mainframe that centrally processes access requests to a database vertically divided into a basic part and an extension part from a plurality of client computers connected to a network,
A process of determining whether or not there is a vacancy in the basic part by comparing the number of valid data of the basic part with respect to a maximum repetition amount of the basic part data in the basic part;
A database access program for causing the mainframe to execute a process including a data eviction process for moving a plurality of data collectively from the basic unit to the expansion unit if there is no free space by the determination. In the data eviction process,
4. The database access program according to claim 3, further comprising a process of acquiring a movement number from a parameter file in order to move the plurality of data collectively.

Images