Database management program, a database managing method and an apparatus therefor

[0002] The present invention relates to a database, and particularly relates to a database management program, a database managing method and a database managing system for carrying out rearrangement of a database or the like on the basis of log information showing access status to data.

[0003] As a conventional database optimizing system based on trace information, there is a database optimizing/rearranging system as disclosed in JP-A-5-2515. In this system, information of a method for gaining access to a database at data operation requests issued from respective user programs is accumulated as trace information. When the database is rearranged at regular time intervals, the optimum storage order of data in the database and the necessity of an index are determined with reference to the accumulated trace information. Thus, buffer quantity assignment parameters are created for securing a buffer quantity required for rearranging the database.

[0004] In the above-mentioned conventional technique, description is made on a method for optimizing and storing the order of columns, the order of lines and the necessity of creating an index in a database on the basis of access frequency. Such processing is carried out automatically routinely, and information is not disclosed to any user. Therefore, the user does not know what standard is used and what is optimized. Thus, classification between the case to optimize and the case not to optimize about one item cannot be defined by the judgement of the user. In addition, for the optimization of the database, the order of columns or the order of lines is merely changed or creation of an index is merely designated. That is, only static optimization is carried out, and description is not made on any dynamic optimization method in which optimization is carried out in accordance with the behavior of the system always undergoing a variation.

[0005] It is an object of the present invention to provide a method and a system for supporting user's database management to optimize the system dynamically, and a program for carrying out the support.

[0006] Further, it is another object of the invention to provide a database optimizing method which can optimize various conditions such as priorities of data, storage places, storage methods, management of resources, indexes, priorities of jobs, and so on, in accordance with optimization rules defined by a user on the basis of a disclosed optimization criterion (reference).

[0007] In order to achieve the foregoing objects, database managing means according to one aspect of the invention has a function to extract log information satisfying predetermined conditions from among log information of application programs having accessed to a database, and a function to rearrange data in the database in accordance with predetermined rules on the basis of commands included in the extracted log information.

[0008] Other objects, features and advantages of the present invention will become apparent from the description of the embodiments of the invention taken in conjunction with the accompanying drawings.

[0009] FIG. 1 is a block diagram showing the configuration of a database managing system according to an embodiment of the present invention;

[0010] FIG. 2 is a diagram showing an example of an access log;

[0011] FIG. 3 is a flow chart of an extraction process;

[0012] FIG. 4 is a diagram showing an example set of definitions of extraction conditions;

[0013] FIG. 5 is a diagram showing an example of an extraction filter;

[0014] FIGS. 6A and 6B are diagrams showing examples of extracted data respectively;

[0015] FIG. 7 is a flow chart of a translation process;

[0016] FIG. 8 is a flow chart of a translation table creating process;

[0017] FIG. 9 is a diagram showing an example set of translation rules;

[0018] FIG. 10 is a diagram showing an example of a translation source list;

[0019] FIG. 11 is a diagram showing an example of a translation table;

[0020] FIG. 12 is a flow chart of an aggregation process;

[0021] FIG. 13 is a diagram showing an example of an access table;

[0022] FIGS. 14A and 14B are diagrams showing example sets of aggregation condition rules respectively;

[0023] FIGS. 15A and 15B are diagrams showing examples of aggregation templates respectively;

[0024] FIGS. 16A and 16B are diagrams showing examples of aggregation filters respectively;

[0025] FIG. 17 is a diagram showing an example of an aggregation result;

[0026] FIGS. 18A and 18B are diagrams showing an example set of aggregation condition rules and an example of an aggregation filter respectively;

[0027] FIG. 19 is a diagram showing an example of an aggregation target definition table;

[0028] FIG. 20 is a diagram showing an example of an aggregation result output using the aggregation target definition table;

[0029] FIG. 21 is an illustration showing an example of another output format of the aggregation result by a screen image;

[0030] FIG. 22 is a flow chart of an optimization process;

[0031] FIG. 23 is a diagram showing an example set of judgement rules for optimization;

[0032] FIG. 24 is a block diagram showing the configuration of a database managing system according to another embodiment of the invention;

[0033] FIG. 25 is a flow chart of an object life cycle analyzing process;

[0034] FIG. 26 is an illustration showing another example of an output by a screen image;

[0035] FIG. 27 is a diagram showing an example set of judgement rules for the object life cycle analyzing process;

[0036] FIG. 28 is an illustration showing another example of an output by a screen image; and

[0037] FIG. 29 is a diagram showing another example set of judgement rules for the object life cycle analyzing process.

[0038] Embodiments of the present invention will be described below with reference to the drawings.

[0039] FIG. 1 shows the configuration of a database managing system according to an embodiment of the present invention. A computer 1 has a database management program 101 for storing data into a database 100, and updating and reading the data from the database 100. A computer 2 has an application program 102 and outputs a data read request, a data update request and a data store request to the computer 1 in accordance with necessity. The computer 2 also stores access log data 103 showing the access status to the database in accordance with the execution of the application program 102. A computer 3 has an access log edition/aggregation processing program 104. The access log edition/aggregation processing program 104 fetches the access log data from the computer 2, and edits, aggregates and analyzes the access log data. Then, on the basis of the result of the analysis, the access log edition/aggregation processing program 104 determines the optimized location of data in the database, the priorities of commands, and so on. The access log edition/aggregation processing program 104 has respective programs for an extraction process 111, a translation process 112, a translation table creating process 113, an aggregation process 114, an output process 115 and an optimization process 116. In the extraction process 111, information is extracted from the access log on desired conditions. In the translation table creating process 113, a translation table for translating values of the extracted part of data is created on the basis of translation rules defined by a user. In the translation process 112, data is translated on the basis of this translation table. In the aggregation process 114, aggregation is performed on the extracted data or the translated data. In the output process 115, the result of the aggregation is outputted to an internal memory or external input/output means. In the optimization process 116, data is optimally located on the basis of the result of the aggregation. Input/output means 105 forms a part of a user terminal, and is connected to be able to communicate with the respective computers 1 to 3 which are connected to each other over a network.

[0040] FIG. 2 shows an example of the access log outputted by the application program. In this format of the log, access date and time, user ID, and log information (including process ID, command, and object targeted by command) are outputted.

[0041] Upon reception of the access log from the computer 2, the computer 3 starts to execute the access log edition/aggregation processing program 104.

[0042] FIG. 3 shows a flow of the extraction process 111. When the computer 3 receives the access log data 103, the computer 3 executes the processing program 104 to create an extraction filter (211). The extraction filter is created with extraction keys and conditional expressions defined on the basis of the extraction condition rules defined by the user. Next, the extraction filter is used to extract required access log data from the access log data 103 (212).

[0043] FIG. 4 shows an example set of extraction conditions. These extraction conditions are set or established by the user. In this example, the priority of data extraction is set by time>user>object>process ID>message. Thus, the data extraction is processed according to this priority. Since both start time and end time have not been specified for the time assignment having highest priority, the start time and the end time of input data are used as they are. Since the extraction duration is defined to be 30 minutes, extraction is carried out every 30 minutes. That is, data is extracted every 30 minutes in a section from the start time of the access log data 103 to the end time thereof. The user extraction condition is ACUser6 or ACUser3. Accordingly, only access logs of these users are extracted. As for objects, specified objects (only one in this example) are extracted. In addition, extraction conditions are also defined for process IDs and messages respectively. An extraction filter for extracting access log data satisfying all the extraction conditions is created in the extraction process 211.

[0044] FIG. 5 shows an example of the extraction filter. This extraction filter is created on the basis of the extraction conditions of FIG. 4. Extraction start time START_TIME of the extraction filter is in agreement with start time START_TIME of first input data. Extraction end time END_TIME is in agreement with the start time plus extraction duration (DURATION), that is, 30 minutes after the start of the log. First, input data is divided in this first 30-minute section. Data is extracted from every divided section on the conditions of the extraction filter. When START_TIME is replaced by END_TIME at last, the end time is set by END_TIME=START_TIME+DURATION from the next time, and the extraction processing is carried out on data in the next 30-minute section.

[0045] FIGS. 6A and 6B show examples of extracted data. The data is extracted from the access log data of FIG. 2 by the extraction filter shown in FIG. 5. FIG. 6A shows extracted data in Extraction Section 1, that is, in the first 30-minute section. FIG. 6B shows extraction data in Extraction Section 2, that is, in the next 30-minute section.

[0046] FIG. 7 shows a flow chart of the translation process 112 in FIG. 1.

[0047] It is confirmed whether a translation table is present or absent (711). If absent, a translation table is created (113). If the user prepares a translation table, or if a translation table has been created before, the values of the extracted access log data are translated with reference to the translation table (713).

[0048] FIG. 8 shows a flow chart of the translation table creating process 113. Translation keys are obtained with reference to translation rules defined by the user in advance (811). The translation keys are generic terms of data to be translated in this process. See FIGS. 9 to 11.

[0049] FIG. 9 shows an example set of the translation rules. Here, the translation keys are user ID and object ID. The method of translation will be described. Translation of users is prior to translation of objects. All the IDs of the objects are replaced by object names. In addition, if an object name is not allotted to an object ID, the object ID is replaced by another property. All the IDs of users are replaced by user names. In addition, if a user name is not allotted to a user ID, the user ID is not replaced but used as it is.

[0050] Next, values corresponding to the translation keys (for example, user ID and object ID) are obtained from the extracted access log data so as to create a list of values of translation sources (812).

[0051] FIG. 10 shows an example of the list of values of translation sources. Key1 designates a translation key user ID having priority, and Key2 designates an object ID. For example, translation sources of user IDs in Key1 are two values, ACUser6 and ACUser3.

[0052] Next, the translation source list is used to access the computer 1, and values of accessible translation destinations are obtained to create a translation table (813).

[0053] FIG. 11 shows an example of the translation table. In this example, it is prescribed that the user ID ACUser6, the user ID ACUser3 and the object ID which are translation keys are translated into the user name “Taro Yamada”, the user name “Hanako Hitachi” and the object name “functional specifications”, respectively. The values of translation destinations (Translated Values) of this table are obtained with reference to values stored in the database. Accordingly, if this process does not have access authority to the data, the process cannot obtain the values of translation destinations. Thus, the translation keys are not translated but left as they are. Although a translation table can be created by reading from the database, the user can prepare a translation table specially. For example, if the user wants user names, object names and so on not to be known for the purpose of security guarantee, such translation keys in the translation table may be translated into random numbers or ciphers.

[0054] FIG. 12 shows a flow chart of the aggregation process 114. In the aggregation process, either the extracted data or the translated data can be specified as input data. If it cannot be confirmed that either data is present, an error message is outputted, and the process is terminated.

[0055] First, specified data is read as input data, and an access table (FIG. 13) for aggregation is created on a memory (1211). Here, when the output of the access table is designated, this access table is outputted to the external input/output unit. FIG. 13 shows an example of the access table. In the access table, columns are made up for every item included in input information. The input information is developed on the table, and used to carry out subsequent calculation repeatedly at a high speed.

[0056] If the access table has been created, an aggregation filter is created along aggregation condition rules and an aggregation template (1212).

[0057] Each of FIGS. 14A and 14B shows an example set of the aggregation condition rules. FIG. 14A shows an example set when there is one aggregation key. FIG. 14B shows an example set when there are two aggregation keys. In the example where there are two aggregation keys, aggregation is defined as follows. That is, aggregation is carried out in user unit and in object unit. Objects are subject to the aggregation. The unit of aggregation is the number of times of command issue. Commands different in conditions other than commands themselves are aggregated as the same command. Finally, the total sum of results of all users is calculated. Aggregation condition rules may be predetermined as default values for the system, or may be defined individually by users.

[0058] Each of FIGS. 15A and 15B shows an example of the aggregation template with which the aggregation result is outputted. FIG. 15A shows a template when there is one aggregation key. FIG. 15B shows a template when there are two aggregation keys. These templates are assumed to be outputted to an input/output screen or a file. However, information listed here may be outputted as a file in a CSV format. An aggregation template may be predetermined as a default value for the system, or may be defined individually by users.

[0059] Each of FIGS. 16A and 16B shows an example of the aggregation filter. FIG. 16A shows the case where there is one aggregation key. FIG. 16B shows the case where there are two aggregation key. In the previous example, there are two aggregation keys, Key1 and Key2, and user ID is defined as a high priority key (Key1) by way of example.

[0060] Returning to the description of FIG. 12, values in the access table are aggregated through the aggregation filter created in the step 1212 (1213). The aggregation result is outputted to the external input/output means 105 by the output process 115, or stored in a memory or a buffer. Thus, the aggregation process 114 is terminated.

[0061] FIG. 17 shows an example of the outputted aggregation result. First, the number of times of command issues is aggregated for every user and for every object in Section 1. Next, the number of times of command issues is aggregated for every user and for every object in Section 2. Finally, the numbers of times of command issues is aggregated totally. Values specified by the user as shown in FIG. 18A may be used as the aggregation condition rules for the aggregation. In the example of FIG. 18A, the aggregation condition rules are specified so that aggregation is carried out with document processing commands as aggregation targets. An aggregation target definition table (FIG. 18A) in which the correspondence between document processing commands and access commands is defined is Customised_Command_Table_1 in the aggregation filter (FIG. 18B). FIG. 19 shows an example of the aggregation target definition table. In this example, the access commands are defined to be classified into seven kinds of document processing commands. For example, a command EXECUTE SEARCH and a command GET RESULT are classified into search commands because both of the commands search a document similarly. FIG. 20 shows the result of aggregation classification using the aggregation target definition table of FIG. 19. The aggregation result is outputted while the aggregation targets are not commands but replaced by document processing commands. FIG. 21 shows an output example of the aggregation result when the aggregation template is a graph image. Here, the numbers of issues of document processing commands are graphed and outputted for two objects X and Y. Comparing these two graphs with each other, the user can know easily which object is higher in access frequency. If the user wants to optimize the location about data with a high access frequency or data with a low access frequency, the optimization process 116 is carried out as follows.

[0062] FIG. 22 shows a flow of the optimization process 116. The access table is read (2311), and the number of command issues is aggregated for every extraction section with the object ID as a key (2312). If it is concluded in Step 2313 that the number of accesses is always beyond a predetermined value in each of the sections, the access frequency is regarded as high, and the location of objects is optimized. If such a conclusion is not made in Step 2313, the frequency of access commands to each object is compared for every section, and the tendency of increase/decrease in frequency of access commands is judged on the basis of judgement rules (FIG. 23) for optimization.

[0063] FIG. 23 shows an example set of the judgement rules. The judgement rules may be determined by individual users. The example of FIG. 23 shows the case where there are an even number of sections. Incidentally, in this example, if there are an odd number of sections, the number of increases and the number of decreases in differences for each section are compared with each other, and the large number is selected. If there are an even number, judgement of increase/decrease is performed as illustrated in this drawing.

[0064] Next, as a result of judgement (2314), the location of an object judged to be high in access frequency is optimized (2315). Some examples will be shown about such optimized location. First, an object judged to be high in access frequency is stored from the database into a cache memory or a buffer memory in the computer 1. Thus, it is possible to provide high-speed access to the object judged to be high in access frequency.

[0065] In addition, the priority of a process for the object judged to be high in access frequency is increased. For example, this is effective in the case where the computer 1 is designed to receive access requests from a plurality of computers 2, and the computer 1 executes a process in accordance with priority given thereto in order to carry out processing efficiently. In this case, the access log edition/aggregation program sends the computer 1 the object judged to be high in access frequency and the priority for the object. In the computer 1, the priority and the object sent thereto are stored. Then, when a command for the object is sent, processing is carried out in accordance with the stored priority.

[0066] In addition, the computer 1 may be requested to create an index for the object judged to be high in access frequency. Alternatively, in another method, an object column or line judged to be high in access frequency may be relocated. When a plurality of objects are accessed with a high frequency by one application, the access is gained at a high speed if these objects are stored together. If data is distributed and stored in a plurality of databases, these plurality of objects may be stored in different databases. In such a case, databases to be accessed are distributed, and there is no fear that accesses are concentrated on one database. Thus, data can be read at a high speed.

[0067] On the contrary, an object judged to be low in access frequency is subject to processing of optimized location, such as decreasing the stored order of the object, storing the object in a lower-rank storage, or reducing the priority of a process (2316). Thus, the priority of a process or the stored position of an object is changed in accordance with the access frequency so that the efficiency is improved in the database system as a whole.

[0068] Incidentally, the whole of the above-mentioned processing may be carried out as a batch job automatically at predetermined time intervals.

[0069] It has been known that an access log usually has an enormous volume of data beyond gigabytes. By the extraction process in this embodiment, processing can be performed on only required data extracted from a large volume of access log information. Accordingly, there is an effect that the processing can be carried out at a high speed or resources can be saved.

[0070] In addition, any identifier inside the database is defined uniquely in order to enhance the processing performance, and the value of the identifier is often a string of characters undecipherable for users. By the translation process in this embodiment, the access log information can be outputted to be replaced by expression decipherable for users. Thus, there is an effect that the visibility for the users is enhanced, and the access log information can be replaced by various properties desired by the users.

[0071] Further, by the aggregation process, the access log can be aggregated for every item, arranged in accordance with classification defined by the users, and presented to the users. Thus, there is an effect that it is possible to provide an interface with which accessed data or access frequency can be grasped intuitively with good visibility by the users.

[0072] Next, description will be made of a second embodiment of the present invention.

[0073] According to the second embodiment, in the access log edition/aggregation processing program 104, access log data is extracted, translated and aggregated in a period from the time when an object was created till the time when the object was deleted. Thus, optimization of the access to a database is supported to reflect the result of analyzing the life cycle of the object.

[0074] FIG. 24 shows the configuration of a database managing system according to the second embodiment of the present invention. The configuration of the system as a whole is the same as that in FIG. 1. However, only the access log edition/aggregation processing program 104 in the computer 3 is different. Particularly, the access log edition/aggregation processing program in this embodiment has a life cycle analyzing process 117.

[0075] FIG. 25 shows a flow chart of the life cycle analyzing process 117.

[0076] In the life cycle analyzing process, an extraction process is carried out while the start time of the extraction process is set to be the time when an object was created and the end time of the extraction process is set to be the time when the object was deleted. This is followed by a translation process (112) and an aggregation process (114). Here, in order to analyze the object over its life cycle, aggregation is carried out upon the number of document processing commands accessed in a period from the time when each object was created to the time when the object was deleted. On the basis of the result of the aggregation, the average life of the objects is obtained in an analysis process (2613). The average life and the aggregation result of the number of command issues are compared, analyzed, and statistically analyzed. Thus, the result is outputted.

[0077] FIG. 26 shows the analysis and the output in an screen image by way of example. In this example, an average life of objects is obtained, and the frequency of the number of document processing commands issued during this average life is analyzed. When the result of the analysis is just as shown in FIG. 26, for example, assume that commands issued by half the average life occupied 80% of commands issued in the whole of the life of an object. That is, this means that 80% of all the commands for the object were intensively issued before the object reached half the average life. In addition, assume that commands issued by three fourths of the average life occupied 90% of the whole. When such an analysis result is obtained, inquiries are sent to a user in a guidance image 2704 (FIG. 26) as to whether the user adds a rule for optimizing the processing on the basis of the analysis result. If the user indicates addition, a rule as shown in FIG. 26 is added to judgement rules for analyzing the life cycle (FIG. 27). Incidentally, this process may be operated automatically without user's judgement.

[0078] FIG. 27 shows an example set of the judgement rules to be added. In this example, there is a tendency for the access frequency to an object to increase when the existence time of the object (that is, from the time when the object was created to the present moment) is shorter than 50% of the average life. In this case, optimization is carried out so that the object is loaded into a cache memory of the computer 1, priority is given to a command or a process, or an index is created. On the contrary, when the existence time of the object exceeds 75% of the average life, there is a tendency for the access to decrease. In this case, optimization is carried out so that the object is saved on a backup, or the priority of a command or a process is reduced.

[0079] Further, FIG. 28 shows an example in which aggregation was performed on the details of the document processing commands. In an analysis result 2901 in this example, the number of update command to be issued is the largest when the existence time of an object is shorter than 50% of the average life of objects. However, the number of reference command to be issued becomes larger when the existence time is not shorter than 50% and shorter than 75% of the average life. Then, queries are sent in a guidance image 2902 as to whether the user adds a rule on the basis of such an analysis result. If addition is selected, a judgement rule is added as shown in FIG. 28. In the example of FIG. 29, when the existence time of an object is shorter than 50% of the average life, optimization is carried out in accordance with the contents of document processing commands in addition to the optimization process in FIG. 27. For example, priority is given to an object update processing command or an update processing step, and exclusive control is carried out for updating the object. On the contrary, when the existence time of the object exceeds 75% of the average life, priority is given to search processing, and it is made necessary to create an index for carrying out search at a high speed. In addition, because the object is not updated, the system is optimized so that exclusive control of the object is prohibited.

[0080] Thus, according to the second embodiment, in addition to the effects described in the first embodiment, it is further possible for a user to manage a database and access data optimally and efficiently in accordance with the life cycle of each object.

[0081] As described above, according to this embodiment, access information such as access frequency to data and access categories is edited, aggregated and analyzed with an access log to a database as input. Thus, this information can be presented to a user in a way easy to be understood, so that the user can be supported on database management.

[0082] Further, according to this embodiment, it is possible to provide a database optimizing method for locating data optimally in accordance with optimization judgement rules defined by the user.

[0083] According to the present invention, a user obtains an aggregation result of access frequency to data, access categories, and so on, using an access log to a database. Thus, the user can carry out optimal management of the database.

[0084] It will be further understood by those skilled in the art that the foregoing description has been made on embodiments of the invention and that various changes and modifications may be made in the invention without departing from the spirit of the invention and scope of the appended claims.