JPH10312325A - Database index utilization program creation method - Google Patents

Abstract

(57) [Summary] To create a program for accessing a database, the database access efficiency is improved based on the index information set in the database management system and the program execution trace. In an index utilization program list creation process performed based on a database index analysis information storage device 12 and a program execution trace information storage device 14 of FIG. 1, access is performed based on an access start time and an access end time. Table information, index information, source program start line, source program end line, access time and average of access time are calculated, and an index-using program list is created to identify the access section that is taking longer than the average time. The program creator can directly select a source program to be re-created by the screen input device 18, automatically correct the source program by the program source automatic correcting device 19 based on the selected item, and Modified Pro To automatically re-create the ram until the execution module.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

[0001] 1. Field of the Invention [0002] The present invention relates to a database processing system, and more particularly to a task of creating a program suitable for a relational database management system and a task of improving performance.

[0002]

2. Description of the Related Art In general, in order to construct a system using a database management system or the like, system requirements are summarized, data items sufficient for executing a business system are output, and a logical design showing a relation between the data items is performed and created. Based on the logical information obtained, a physical design is performed to achieve a processing efficiency time applicable to a real business system. In such a system using a database management system, in order to maintain business processing at a constant processing efficiency, create an index in the database management system and enable the index registered in the database management system from the source program. Processing (data access)
Improving speed is essential.

In the conventional database design, after designing a database and designing an index of a database management system, a source program has been created in consideration of an index. In addition, the index design creates an index suitable for each process because one table is used from multiple source programs,
It has been created as a secondary index and a tertiary index. Therefore, if multiple indexes have been created for the table accessed from the source program, the index used when accessing the database is controlled by the database management system side.
In some cases, tables were accessed without using an index that the source program creator was aware of. for that reason,
In some cases, the processing efficiency for the business process assumed in the design stage could not be obtained.

[0004] Patents relating to automatic creation of a database search statement relating to creation of a new source program include JP-A-7-287652 and JP-A-7-160558. Also, when changing the database table configuration,
A patent that changes the search formula is disclosed in Japanese Patent Laid-Open No.
There is a number. Japanese Patent Application Laid-Open No. Hei 6-110933 discloses a patent that eliminates a change in a program in response to a change in the index definition of a database in an existing search process. However, these conventional techniques take into account the index of the database when creating a source program.
QL (Structured Query Langu)
age) It does not generate a sentence. For this reason, there is a difficulty that it is not possible to create a source program for performing a process of accessing a database in which an index is defined and to efficiently create an SQL statement with respect to database access efficiency.

[0005]

The conventional technique automatically generates a search sentence for a search process of a program that does not consider an index, reduces coding errors caused by automatically generating a search sentence for program creation, and reduces a program error. Not creating code was effective in reducing the number of work steps.

Further, the prior art eliminates the influence on the program when the index is changed, so that the program itself does not need to be recompiled and the program does not need to be changed when the database index is changed. Although the search processing procedure was not changed, it was effective in reducing the number of man-hours due to the change of the source program. However, this method did not consider the processing efficiency of the system and was not a method for improving the processing efficiency.

[0007] An object of the present invention is to solve the problems of the prior art and, when creating a program for accessing a database, create an index-using program list that evaluates an index configuration defined in a database management system. With regard to database access, which code in the source program is used at the time of program execution, especially by displaying a list of source codes with low processing efficiency, it is supported to create an efficient program, and it has been used in conventional system development. Tuning work.

Another object of the present invention is to clarify an index used among a plurality of index definitions defined in a table to be accessed when creating a program for accessing the database. In order to improve access efficiency, an SQL statement is automatically re-created to reduce the number of steps for creating a program.

[0009]

The above object can be achieved by the following method.

A database index analysis information storage device for storing trace information outputted as a SQL trace from a database management system as a database access status, and a program execution for storing, as trace information, a source code for accessing the database when executing the program From the trace information storage device, the SQ of the database index analysis information storage device
The L trace information and the program execution trace information in the program execution trace information storage device are read, and a list of index-using programs is created. At this time, by displaying the database access time as trace information for each reference table in creating the index-using program list, it is possible to specify the relevant line of the program source where the index is not used effectively.

Further, in order to delete the tuning man-hours for improving the processing efficiency, the search conditions are automatically reconfigured for those whose processing speed is lower than the overall average, based on the index utilization program list. Thus, it is sufficient to provide a program source automatic correction device for improving the database access efficiency.

Further, in order to improve the efficiency of program creation by changing the program source, it is possible to automatically recompile the program source code based on the created program source code by the automatic program source correction device. With the automatic execution module creating apparatus, the modified program can be automatically modified and automatically recompiled, so that the program can be automatically modified.

A database index analysis information storage device that stores SQL trace information to be output from a database management system in a database access using an index, and a program execution trace information storage device that traces a source code that accesses a database when executing a program By using the index-based program list created based on the above, the time of database access can be known from the execution program, and the line number of the source code of the access program that takes longer than the average access time can be clarified. Identify places where the index set in the database management system is not used effectively in the program, and provide the program creator with a new search condition. Since thereby al, the efficiency of the fix. In addition, the number of man-hours for improving (tuning) the system processing performance can be reduced.

Further, the program source automatic correction device includes:
Based on the list of programs using the index, if the processing speed is slower than the overall average, the search conditions can be automatically reconfigured according to the instructions of the program creator. The database access efficiency can be improved without having to change the program.

Further, since the automatic modification and automatic recompilation of the changed program can be performed by the automatic program source modifying device, the compiling work accompanying the program modification is eliminated, and the man-hour can be reduced.

[0016]

Embodiments of the present invention will be described below in detail with reference to the drawings.

FIG. 1 is a diagram for explaining a system configuration according to one embodiment of the present invention. FIG. 2 is a diagram illustrating an analysis of an index usage state at the time of database access analyzed by a database index analysis apparatus and an SQL statement. FIG. 3 shows a list in a database index analysis information storage device storing information, and FIG. 3 shows a program execution trace information storage device storing information obtained by tracing a source code for accessing a database when a program is executed by a program execution trace device. Represents the list in the
FIG. 4 shows information on the database index analysis information storage device and the program execution trace information storage device, and shows a list of created index-using programs. FIG.
4 shows a flowchart of a program source automatic correction.

In FIG. 1, reference numeral 11 denotes information on database access, such as a table name, an index name, an access start time, an access end time, and an SQL number, which are accessed from a database management system. A database analysis device for storing the obtained data in a database index analysis information storage device; and a database index analysis information storage device for storing information obtained from the database index analysis information storage device as a table structure. , 13 are programs for accessing the database, generate an access ID that can be uniquely identified in the program with respect to the database access code of the source program, and use the database as trace information when executing the program. A program execution tracing device that outputs an access code and obtains information for accessing a database at the time of program execution, such as a table name to be accessed, an access ID, an access start time, an access end time, and a start line and an end line of a program source code; 14 is a program execution trace information storage device for storing information obtained by the 13 program execution trace devices as a table structure, 15 is a database read information read from the 12 database index analysis information storage device, Reads program execution information from the program execution trace information storage device,
Based on the access start time and access end time from each table, the table information being accessed, index information, source program start line, source program end line, access time, and average of access time are calculated. An index-using program list creating device that creates an index-using program list that specifies an access unit that takes a long time, and an index for storing information created by the index-using program list creating device as a table structure. A use program list storage device, 17 is an output unit that outputs information created by the 15 index use program list creation devices as list information, and 18 is a screen that displays the list information created by the 15 index use program list creation devices. And 19 It makes it possible to select the processing target of the automatic correction device program source, an input unit, 1
9 is an automatic program source correction device for automatically changing an access condition expression for an access condition selected as a re-creation target by the screen input unit of 18 based on 16 index-using program list storage devices; 20
Is composed of an automatic execution module creating apparatus that automatically recompiles the changed source program created by the 19 automatic program source modifying apparatuses.

In FIG. 2, reference numeral 21 denotes a program name XXX for accessing the database. The access status of the database to the program XXX is managed as a list.

Reference numeral 22 denotes a table name being accessed obtained from the database management system. Reference numeral 23 denotes an index name used during execution of the program. Reference numeral 24 denotes an access start time at the time of execution of the program. The access end time 26 of S is managed by the database management system.
Since the QL number is assigned every time an access is made, the number is obtained from the database management system. Such information is used in existing commercial database systems, and is also implemented in Hitachi's HiRDB.

By displaying these pieces of information as a table structure, a list of access statuses for each SQL number, which has been conventionally handled as a list of characters, can be displayed in a list, so that management of database access statuses becomes easy.

In FIG. 3, reference numeral 31 denotes a program name XXX for accessing the database. The execution status of the database access program for the program XXX is managed as a list.

Reference numeral 32 denotes the name of the table being accessed, obtained from the program execution trace information device;
Indicates a database access ID uniquely set in the program by the program execution trace information device, 34 indicates an access start time at the time of program execution 35, an access end time at the time of program execution 36 indicates a program trace information The program start line number 37 in the source program with respect to the set access ID indicates the program end line number in the source program with respect to the access ID set in the program trace information.

The database access code created in the source program is expanded into an implementation program language (for example, COBOL or C) by a preprocessor provided by the database management system. Has more lines than the code created by. Therefore, even if you simply create runtime trace information, the line number in the program and the
Line number information at the time of execution does not always match. However, according to the present invention, by creating a unique number for the database access code in the source program, even if the number of lines of the execution code is increased by the preprocessor of the database management system, the number of lines in the created source code is increased. To create numbers as trace information,
It is possible to easily identify a portion where the database access efficiency is low.

In FIG. 4, the information in the list is based on the information read from the database index analysis information storage device and the information read from the program execution trace information storage device based on the access start time and the access end time. Identifies the table that was read, the index that was used, and the program line that was executed when was executed.

Reference numeral 41 denotes a program name XXX for accessing the database.

Reference numeral 42 denotes an average access time of each database access process in the database index utilization program list creation device.

Reference numeral 43 denotes the name of the table accessed by the program. Reference numeral 44 denotes the name of the index used by the program. Reference numeral 45 denotes the start line of the database access code in which the program was executed. Display of the end line of the database access code 47 indicates the total execution time during which the database access was performed, and 48 indicates a program source automatic correction target selection column indicating the evaluation with the average time of 42. For this purpose, a list that is longer than the average time is displayed in an easily recognizable list such as by changing the background color when displaying the screen. At the time of printing, display such as shading is performed.

The list can be printed. This list can be displayed on the screen, and the program creator can select 48 program source automatic correction target selection fields from the screen, and the program source automatic correction is performed for the selected item.

FIG. 5 is a flow chart showing the creation of an index-using program list and the automatic reorganization of source program search conditions when selecting a program source automatic correction target selection field.

A processing unit 51 reads the program execution trace information stored in the program execution trace information storage device.

At 52, it is determined whether or not the process for accessing the database has been performed with the information read at 51. This is because the executed program may not access the database in some cases.

Reference numeral 53 denotes a case where a database access process is performed in the database access process determination process in 52,
Perform processing to read database index analysis information.

Reference numeral 54 denotes the program execution trace information read in 51 and the database index analysis information read in 53, based on the access start time and the access end time, based on the table name and index of the program referred to by the program. Create a name, program start line, and program end line as a list. Also,
Upon creation, an access execution time is obtained as an access end time-access start time of each process, and an average access processing time is obtained. For a database access having an access time longer than the average access time, the selection information is set as a default in the program source automatic correction target selection column.

A determination is made at 55 as to whether or not there is an item for which selection information has been set in the program source automatic correction target selection column of the index-based program list display for an item longer than the average access time obtained at 54. I do. If the selection information is not set in the program source automatic correction target selection column, an end process is performed.

When the selection information is set in the program source automatic correction target selection column, the selected database access condition is reviewed, and the program source search condition automatic reorganization process is performed. For example, in a SQL sentence, a WHERE clause is used as a search condition expression. However, an index at the time of database access may be different depending on a sequence of setting conditions of the WHERE clause, and an expected processing performance may not be obtained. In such a case, the processing exceeds the average access time. Therefore, by rearranging the arrangement of the WHERE phrases or adding search conditions, it is expected that the database access processing time will be improved.

[0037]

According to the present invention, the execution trace of the source program for accessing the database can be traced, the average access time in the program is automatically totaled, and if the access time is longer than the average access time, the access such as the index is performed. Investigation of correction targets for improvement of processing efficiency because conditions may not be set properly in the source program and locations of inefficient access can be identified by displaying the number of lines of source program code Can be reduced. In addition, by selecting a program with poor access efficiency from the list displayed, the access conditions of the program source are automatically reorganized, so that the number of program correction steps for improving the processing efficiency can be reduced.

[Brief description of the drawings]

FIG. 1 is a system configuration diagram of an embodiment of the present invention;

FIG. 2 shows a data structure in a database index analysis information storage device created by the database index analysis device;

FIG. 3 shows a data structure in a program execution trace information storage device created by the program execution trace device;

FIG. 4 is an index utilization program list screen,

FIG. 5 shows a flowchart of creation of a list of index-based programs and automatic reorganization of source program search conditions when a program source automatic correction target selection field is selected.

[Explanation of symbols]

11 Database Index Analysis Device 12 Database Index Analysis Information Storage Device 13 Program Execution Trace Device 14 Program Execution Trace Information Storage Device 15 Index Use Program List Creation Device 16 Index Use Program List Information Storage Device 17 Index Use Program List Information Output Unit 18 Index Knitting target input unit 19 Automatic program source correction device 20 Automatic execution module creation device 21 Database index analyzer target program name 22 Database index analyzer target table name column 23 Database index analyzer target index name column 24 Database index analyzer target access start Time column 25 At the end of access to the database index analyzer Column 26 SQL number column for database index analysis device 31 Program name for program execution trace device 32 Table table for program execution trace device 33 Access number column for program execution trace device 34 Access start time column for program execution trace device 35 Program execution trace device Target access end time column 36 Program execution trace device target program start line column 37 Program execution trace device target program end line column 41 Index use program list information target program name 42 Index use program list information average time 43 Index use program list information table column 44 Index use program list information index number column 45 Index use program list information program Program start line field 46 index use program list information program end row field 47 index use program list information processing time field 48 index use program list information source program automatic correction target selection field 51 program execution information reading step 52 database search processing judgment section 53 database Index analysis result reading step 54 Index utilization program list creation step 55 Execution efficiency judgment unit 56 Program source database access condition automatic reorganization step

Claims (3)

[Claims] In creating a program for accessing a database, based on program execution trace information,
Based on the database access condition statement declared in the source program and the database index usage information collected from the database management system, the relationship between the index used in the database access processing at the time of program execution and the source program line is clarified A database index utilization program creation method characterized by creating a list and displaying it as an index program utilization list, and an output unit having an output function. 2. A program creator directly selects or deselects a program source for which a database access conditional expression is to be changed on an indexed program list display screen created and displayed as a list in claim 1. Screen input method characterized by input. 3. An index created in a database can be effectively used for a source program automatic change target program arbitrarily selected by a program creator from a screen on the list created in claim 1. An automatic program modification method that automatically sorts search conditions.