JP2007213392A - Sql trace acquisition system and method - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To provide an SQL (structured query language) trace acquisition system and method, capable of acquiring an SQL trace without needing a heavy load. <P>SOLUTION: This system that is a Web system comprises a management computer 400 including an SQL trace acquisition code embedding means 402 for embedding, in a WEB system, an SQL trace acquisition code in a service providing application 401 and generating a service providing application 403 embedded with SQL trace acquisition code, and a service assignment means 404 for assigning the service providing application 403 to a server computer 200 through a network. A service providing application 202 in the server computer 200 acquires a trace mode corresponding to an original application name stored in a trace mode table 203, and issues, when the trace mode is valid, a trace start sentence, an SQL sentence and a trace end sentence in order to the server computer 300. <P>COPYRIGHT: (C)2007,JPO&INPIT

Description

  The present invention relates to an SQL trace acquisition system, method, program, and recording medium in a Web system including a second computer that receives an SQL statement execution request from a first computer and executes an SQL statement.

  The SQL trace is performance information related to the SQL statement executed in the database, and is an important clue for performance tuning.

  To obtain the SQL trace, execute the normal trace start statement (control SQL statement that enables trace output) to enable the trace output, then execute the SQL statement that you want to obtain the trace, and finally the trace end statement (Control SQL statement for invalidating trace output) may be executed and restored. However, since the trace start statement only validates the trace output of its own session, it is necessary to execute the trace start statement and the SQL statement to obtain the trace in the same session.

  Refer to the following Patent Document 1 as related technology.

JP 2001-175633 A

  However, when the SQL statement is embedded in the parent language and used, a new code for acquiring the SQL trace must be added. If the application cannot be changed, or if the application is already running There is a problem that can not be obtained. In addition, the application must be changed each time the SQL statement for which a trace is to be acquired is changed.

  In this case, if the trace output of another session is validated, it is possible to acquire the SQL trace without modifying the application.

  However, if the trace output of another session is enabled, the above-described method of acquiring the SQL trace without modifying the application is unrealistic. Because, in the Web system, it is impossible to predict in advance the connection (session) that is normally used to execute the SQL statement, so it is necessary to enable the trace output of all the sessions, and the large load and disk capacity. This is because there is a possibility of requesting.

  Accordingly, an object of the present invention is to provide an SQL trace acquisition system, method, program, and recording medium that can acquire an SQL trace without requiring a large load or disk capacity.

  In order to solve the above problems, the present invention employs means for solving the problems having the following characteristics.

An SQL trace acquisition system according to an aspect of the present invention includes:
In a Web system including a first computer (200) and a second computer (300) that executes an SQL statement in response to an SQL statement execution request from the first computer,
SQL trace acquisition code embedding means (402) for embedding the SQL trace acquisition code without modification in the source code in the service providing application (401) and generating the service providing application (403) with the embedded SQL trace acquisition code;
Management comprising service deployment means (404) for deploying the SQL trace acquisition code embedded service providing application (403) to the first computer (200) as a service providing application (202) with an SQL trace acquisition function via the network A server (400),
The first computer (200)
A trace mode table (203) that stores in advance the trace mode corresponding to the SQL statement issuer application name;
The corresponding trace mode is acquired from the trace mode table (203), and when the trace mode is valid, the trace start statement, the SQL statement, and the trace end statement are issued to the second computer (300) in this order. It is characterized by having a service providing application (202) with an SQL trace acquisition function that issues only an SQL sentence when it is invalid.

  Furthermore, the management server (400) has a trace mode update means (405) for recording mapping information of the call source application name of the SQL statement execution function and the trace mode in the trace mode table (203). .

  Further, when the second computer (300) executes the SQL statement and sends the execution result to the first computer (200), if the trace output is valid, the second computer (300) goes to the trace repository (303). It has a database management system (302) for storing SQL traces.

  Furthermore, the SQL trace acquisition code is a code for enabling the trace output of the current session based on the trace mode corresponding to the caller application name of the SQL statement execution function acquired from the trace mode table (203). This is a code to be restored after executing the SQL statement, and is characterized by being embedded before and after the SQL statement execution function existing in the service providing application 401.

In addition, an SQL trace acquisition method according to an aspect of the present invention includes:
Embedding SQL trace acquisition code without modification in the service providing application (401) in the management server (400) connected to the first computer (200) via the network;
Generating an SQL trace acquisition code embedded service providing application (403);
Deploying the SQL trace acquisition code embedded service providing application (403) to the first computer (200) as a service providing application (202) with an SQL trace acquisition function via a network;
Storing the trace mode corresponding to the SQL statement issuer application name in the trace mode table (203) in advance;
Obtaining the corresponding trace mode from the trace mode table (203) in the first computer;
When the trace mode is valid, a trace start statement, an SQL statement, and a trace end statement are issued to the second computer (300) in this order, and when the trace mode is invalid, only a SQL statement is issued. It is characterized by.

  The trace mode table (203) further includes a step of recording and updating the mapping information of the caller application name of the SQL statement execution function and the trace mode.

  Furthermore, when the SQL statement is executed in the second computer (300) and the execution result is transmitted to the first computer (200), if the trace output is enabled, the trace repository (303 ) To store the SQL trace.

  Furthermore, the SQL trace acquisition code is a code for enabling the trace output of the current session based on the trace mode corresponding to the caller application name of the SQL statement execution function acquired from the trace mode table (203). This is a code to be restored after executing the SQL statement, and is characterized by being embedded before and after the SQL statement execution function existing in the service providing application 401.

In addition, according to the present invention, a computer
Embedding the SQL trace acquisition code without modification in the service providing application (401) in the service providing application (401) in the management server (400) connected to the first server (200) via the network;
Generating an SQL trace acquisition code embedded service providing application (403);
Deploying the SQL trace acquisition code embedded service providing application (403) to the first server (200) as a service providing application (202) with an SQL trace acquisition function via a network;
Storing the trace mode corresponding to the SQL statement issuer application name in the trace mode table (203) in advance;
Obtaining the corresponding trace mode from the trace mode table (203) in the first server (200);
When the trace mode is valid, a trace start statement, an SQL statement, and a trace end statement are issued to the second server (300) in this order, and when the trace mode is invalid, only the SQL statement is issued. A program characterized by this can be obtained.

  According to the present invention, there is provided a computer-readable information recording medium (compact disk, flexible disk, hard disk, magneto-optical disk, digital video disk, magnetic tape, or semiconductor memory) characterized by recording the program. Including) is obtained.

  According to the present invention, first, since the SQL trace acquisition code is mechanically embedded in the service providing application without correcting the source code, there is no need to modify the application, and the application developer is not conscious at all. good. For this reason, the trace of the SQL sentence issued from an application can be acquired by a realistic method.

  In addition, according to the present invention, secondly, since the trace mode is managed for each application and only the SQL statement issued from a specific application can be acquired, a large load or disk capacity is imposed on the server computer. Is no longer required.

  Next, embodiments of the present invention will be described in detail with reference to the drawings. The SQL trace acquisition system according to the first embodiment of the present invention will be described below with reference to FIG.

  This system operates on a client computer 100 operating under program control, a server computer 200 operating under program control, a server computer 300 operating under program control, a management computer 400 operating under program control, and a server computer 200. Service processing means 201 to perform, a service providing application 202 with an SQL trace acquisition function stored in an auxiliary storage device or storage medium on the server computer 200, and a trace stored in a main storage device or storage medium on the server computer 200 Mode table 203, SQL statement processing means 301 operating on server computer 300, database management system 302 stored in an auxiliary storage device or storage medium on server computer 300, and auxiliary on server computer 300 Trace repository 303 stored in storage device or storage medium, SQL trace acquisition code embedding unit 402 operating on management computer 400, service deployment unit 404, trace mode table updating unit 405, and auxiliary on management computer 400 The service providing application 401 is stored in a storage device or a storage medium, and the service providing application 403 has an SQL trace acquisition code embedded therein.

  The server computer 200 includes service processing means 201. The server computer 300 includes SQL statement processing means 301. The management computer 400 includes an SQL trace acquisition code embedding unit 402, a service deployment unit 404, and a trace mode table update unit 405.

  Each of these means generally operates as follows. The SQL trace acquisition code embedding unit 402 embeds an SQL trace acquisition code without modification of the source code in the service providing application 401 to generate the SQL trace acquisition code embedded service providing application 403.

  The service deployment means 404 deploys the SQL trace acquisition code embedded service providing application 403 to the server computer 200 as the service providing application 202 with the SQL trace acquisition function.

  The trace mode update means 405 records the call source application name of the SQL statement execution function and the trace mode mapping information in the trace mode table 203.

  Upon receiving an execution request from the client computer 100, the service processing unit 201 transfers the request to the service providing application 202 with the SQL trace acquisition function, and returns the execution result of the application to the client.

  The service providing application 202 with the SQL trace acquisition function transfers the SQL statement execution request to the SQL statement processing unit 301. When receiving the SQL statement execution request from the service providing application 202 with the SQL trace acquisition function, the SQL statement processing unit 301 transfers the SQL statement to the database management system 302 and returns the execution result of the SQL statement to the client.

  The database management system 302 executes the SQL statement and returns the execution result to the client. At this time, if the trace output is enabled, the SQL trace is stored in the trace repository 303.

  Next, the operation of the SQL trace acquisition method, that is, the SQL trace acquisition system according to the present invention will be described in detail.

  First, processing when a service providing application is deployed will be described. The SQL trace acquisition code embedding unit 402 of the management computer 400 mechanically embeds the SQL trace acquisition code without modification of the source code in the service providing application 401, and generates the SQL trace acquisition code embedded service providing application 403. In the service providing application in which the SQL trace acquisition code is embedded, for example, as shown in FIG. 3, the SQL trace acquisition code is embedded before and after the SQL statement execution function.

  The service deployment means 404 deploys the SQL trace acquisition code embedded service providing application 403 to the server computer 200 as the service providing application 202 with the SQL trace acquisition function.

  Hereinafter, processing executed by the service providing application 202 with the SQL trace acquisition function will be described with reference to FIG. In the SQL statement issue location in the service providing application 403 with the embedded SQL trace acquisition code, the service providing application 202 with the SQL trace acquisition function first acquires the trace mode corresponding to the SQL statement issuing source application name from the trace mode table 203 (step A1). Here, for example, as shown in FIG. 2, the SQL statement issuer application name and the corresponding trace mode are stored in the trace mode table 203 in advance.

  When the trace mode is valid, the trace start sentence, the SQL sentence, and the trace end sentence are issued to the SQL processing unit 301 in this order (step A2). If the trace mode is invalid, the service providing application 202 with the SQL trace acquisition function issues only an SQL statement (step A3).

  Here, the SQL statement issuer application name stored in advance in the trace mode table 203 and the trace mode corresponding to the application name are stored in a format as shown in FIG. 2, for example.

  Next, processing when changing the trace mode will be described.

  The trace mode table update unit 405 records, in the trace mode table 203, the name of an application that issues an SQL statement for which a trace is to be acquired.

  Finally, processing when an application is executed will be described with reference to FIG. Note that processing 1, processing 2, and processing 3 in FIG. 5 indicate processing for accessing a database, that is, execution processing of so-called business logic that is processing other than execution of an SQL statement.

  The client computer 100 transmits an execution request to the service processing unit 201. The service processing unit 201 transfers the execution request to the service providing application 202 with the SQL trace acquisition function.

  The service providing application 202 with the SQL trace acquisition function that has received the execution request transmits the SQL statement execution request to the SQL statement processing means 301 after performing the above-described SQL statement issuing processing as shown in FIG. 4 (FIG. 5). SQL statement issue processing 1, 2). The SQL statement processing unit 301 transfers the SQL statement that is the target of the execution request to the database management system 302. The database management system 302 executes the SQL statement. At that time, if the trace output is enabled, the trace for which the trace output is enabled is stored in the trace repository 303. The service providing application 202 with the SQL trace acquisition function transmits the execution result of the application to the client computer 100.

  The invention also includes a program for causing a computer to execute each step in the above SQL trace acquisition method. This program may be the program itself, or the program is stored in a computer-readable recording medium. It may be.

  In the present invention, as the recording medium, a memory necessary for processing by the microcomputer, such as a ROM itself, may be a program medium, or a program as an external storage device (not shown). It may be a program medium provided with a reading device and readable by inserting a recording medium therein. In any case, the stored program may be configured to be accessed and executed by the microcomputer, or in any case, the program is read out, and the read program is stored in the program storage area of the microcomputer. The program may be loaded and executed by the program. It is assumed that this loading program is stored in the main device in advance.

  Here, the program medium is a recording medium configured to be separable from the main body, such as a tape system such as a magnetic tape or a cassette tape, a magnetic disk such as an FD (flexible disk) or HD (hard disk), or a CD-ROM / Supports fixed programs including optical disks such as MO / MD / DVD, card systems such as IC cards (including memory cards) / optical cards, and semiconductor memories such as mask ROM, EPROM, EEPROM, flash ROM, etc. It may be a medium.

  Further, in the present invention, since the system configuration is connectable to a communication network including the Internet, the medium may be a medium that dynamically carries the program so as to download the program from the communication network. When the program is downloaded from the communication network in this way, the download program may be stored in the apparatus main body in advance or may be installed from another recording medium.

  Furthermore, in the present invention, the program itself may be a process executed by a microcomputer, or may be acquired by accessing a communication network including the Internet, or may be acquired. You may send out from.

  Further, each of the above-described embodiments is merely an example of a preferred embodiment of the present invention, and the present invention can be implemented with various modifications without departing from the gist thereof.

  According to the present invention, the SQL trace acquisition system according to the present invention is applied for the purpose of acquiring an SQL trace without modifying an application and without imposing a heavy load on the server computer.

  The present invention can be applied to uses such as a SQL trace acquisition system for acquiring an SQL trace and a program for realizing a system for acquiring an SQL trace in a computer. Further, the present invention can be applied to an application such as providing an SQL trace acquisition function for acquiring an SQL trace to various programs operating on a computer.

It is the block diagram which showed the structure of the SQL trace acquisition system which concerns on this invention. It is the figure which showed an example of the SQL mode issuer application name stored in the trace mode table (203) and the trace mode corresponding to it. It is the figure which showed the application before embedding SQL trace acquisition code, and the application after SQL trace acquisition code embedding. It is the flowchart which showed the process which the service provision application with a SQL trace acquisition function performs. It is the figure which showed the positioning of the SQL sentence issue process in the whole process of this invention.

Explanation of symbols

DESCRIPTION OF SYMBOLS 100 Client computer 200 Server computer 201 Service processing means 202 Service providing application with SQL trace acquisition function 203 Trace mode table 300 Server computer 301 SQL statement processing means 302 Database management system 303 Trace repository 400 Management computer 401 Service providing application 402 SQL trace acquisition code Embedding means 403 SQL trace acquisition code embedded service providing application 404 service deployment means 405 trace mode table updating means

Claims (10)

In a Web system including a first computer and a second computer that executes an SQL statement in response to an SQL statement execution request from the first computer,
SQL trace acquisition code embedding means for generating an SQL trace acquisition code embedded service providing application by embedding a SQL trace acquisition code without modifying the source code in the service providing application;
A management server comprising service deployment means for deploying an SQL trace acquisition code embedded service providing application to the first computer as a service providing application with an SQL trace acquisition function via a network;
The first computer is
A trace mode table storing in advance a trace mode corresponding to the application name of the SQL statement issuer;
When the corresponding trace mode is acquired from the trace mode table and the trace mode is valid, the trace start statement, the SQL statement, and the trace end statement are issued to the second computer in this order, and the trace mode is invalid. An SQL trace acquisition system comprising a service providing application with an SQL trace acquisition function that issues only an SQL statement.   2. The SQL trace acquisition system according to claim 1, wherein the management server has trace mode update means for recording mapping information of the call source application name of the SQL statement execution function and the trace mode in the trace mode table.   The second computer has a database management system that stores an SQL trace in the trace repository when trace output is enabled when an SQL statement is executed and an execution result is transmitted to the first computer. The SQL trace acquisition system according to claim 2.   The SQL trace acquisition code is a code that validates the trace output of the current session based on the trace mode corresponding to the call source application name of the SQL statement execution function acquired from the trace mode table. The SQL trace acquisition system according to claim 1, wherein the SQL trace acquisition system is embedded before and after an SQL statement execution function existing in the service providing application. In a method for acquiring an SQL trace in a Web system including a second computer that receives an SQL statement execution request from a first computer and executes an SQL statement,
Embedding SQL trace acquisition code without modification in the service providing application in the service providing application in the management server connected to the first computer via a network;
Generating an SQL trace acquisition code embedded service providing application;
Deploying the SQL trace acquisition code embedded service providing application to the first computer as a service providing application with an SQL trace acquisition function via a network;
Storing a trace mode corresponding to the SQL statement issuer application name in the trace mode table in advance;
Obtaining the corresponding trace mode from the trace mode table in the first computer;
When the trace mode is valid, a trace start statement, an SQL statement, and a trace end statement are issued to the second computer in this order, and when the trace mode is invalid, only a SQL statement is issued. SQL trace acquisition method.   6. The SQL trace acquisition method according to claim 5, further comprising the step of recording and updating the mapping information of the call source application name of the SQL statement execution function and the trace mode in the trace mode table.   When executing a SQL statement in the second computer and transmitting the execution result to the first computer, the method includes a step of storing an SQL trace in the trace repository when trace output is enabled. The SQL trace acquisition method according to claim 6, wherein:   The SQL trace acquisition code is a code that validates the trace output of the current session based on the trace mode corresponding to the call source application name of the SQL statement execution function acquired from the trace mode table. The SQL trace acquisition method according to claim 5, wherein the SQL trace acquisition method is embedded before and after an SQL statement execution function existing in the service providing application. On the computer,
Embedding the SQL trace acquisition code without modification in the service providing application in the service providing application in the management server connected to the first server via the network;
Generating an SQL trace acquisition code embedded service providing application;
Deploying the SQL trace acquisition code embedded service providing application to the first server as a service providing application with an SQL trace acquisition function via a network;
Storing a trace mode corresponding to the SQL statement issuer application name in the trace mode table in advance;
Obtaining the corresponding trace mode from the trace mode table in the first server;
When the trace mode is valid, a trace start statement, an SQL statement, and a trace end statement are issued to the second server in this order, and when the trace mode is invalid, only the SQL statement is issued. Program. A computer-readable information recording medium (including a compact disk, a flexible disk, a hard disk, a magneto-optical disk, a digital video disk, a magnetic tape, or a semiconductor memory), wherein the program according to claim 9 is recorded.

Images