SQL (Structured Query Language) statement positioning method and device, electronic equipment and storage medium

Abstract

The invention provides an SQL statement positioning method and device, electronic equipment and a storage medium, and the method comprises the steps: obtaining the operation state information of an SQL statement in operation; wherein the running state information comprises a client identifier and a client port corresponding to the SQL statement; determining a client process corresponding to the SQL statement according to the client identifier and the client port; according to a process tree related to a client process, determining a service process and a service program corresponding to the SQL statement; wherein the business program is a program containing SQL statements, and the business process is a process generated by executing the business program. Therefore, through the causal relationship of the physical objects involved in the SQL statement execution process, the SQL statement operated in the database can be positioned to the service process and service program granularity level, the positioning accuracy is improved, and accurate positioning of the SQL statement is realized.

Description

Technical Field

[0001] The present application relates to the field of databases and operating systems, and in particular to a method, device, electronic device and storage medium for locating SQL statements. Background Art

[0002] In some database maintenance and analysis scenarios, it is usually necessary to locate and trace the Structured Query Language (SQL) statements running on the database to find the source where the SQL statements were initiated and executed.

[0003] In the related technologies, SQL statements can be located on the client host or business system, but the location is not accurate enough; some are matched between SQL statements and business procedures through text, which has low differentiation and easily leads to confusion. Summary of the invention

[0004] The present application aims to solve one of the technical problems in the related art at least to some extent.

[0005] To this end, the first objective of the present application is to propose a method for locating SQL statements to achieve accurate positioning of SQL statements.

[0006] The second objective of the present application is to provide a device for locating SQL statements.

[0007] The third objective of the present application is to provide an electronic device.

[0008] A fourth objective of the present application is to provide a computer-readable storage medium.

[0009] A fifth object of the present application is to provide a computer program product.

[0010] To achieve the above-mentioned purpose, the first embodiment of the present application proposes a method for locating an SQL statement, including:

[0011] Obtaining running status information of a running SQL statement; wherein the running status information includes a client identifier and a client port corresponding to the SQL statement;

[0012] Determine the client process corresponding to the SQL statement according to the client identifier and the client port;

[0013] According to the process tree related to the client process, the business process and business program corresponding to the SQL statement are determined; wherein the business program is a program including the SQL statement, and the business process is a process generated by executing the business program.

[0014] To achieve the above-mentioned purpose, the second aspect of the present application proposes a SQL statement positioning device, including:

[0015] An SQL statement perception module, used to obtain the running status information of the running SQL statement; wherein the running status information includes the client identifier and the client port corresponding to the SQL statement;

[0016] A client determination module, used to determine the client process corresponding to the SQL statement according to the client identifier and the client port;

[0017] The business determination module is used to determine the business process and business program corresponding to the SQL statement according to the process tree related to the client process; wherein the business program is a program containing the SQL statement, and the business process is a process generated by executing the business program.

[0018] To achieve the above-mentioned purpose, the third aspect of the present application provides an electronic device, including: a processor, and a memory communicatively connected to the processor;

[0019] The memory stores computer-executable instructions;

[0020] The processor executes the computer-executable instructions stored in the memory to implement the method as described in the embodiment of the first aspect.

[0021] To achieve the above-mentioned purpose, the fourth aspect embodiment of the present application proposes a computer-readable storage medium, in which computer execution instructions are stored. When the computer execution instructions are executed by a processor, they are used to implement the method described in the first aspect embodiment.

[0022] To achieve the above-mentioned purpose, the fifth aspect of the present application proposes a computer program product, including a computer program, which implements the method described in the first aspect when executed by a processor.

[0023] The SQL statement positioning method, device, electronic device and storage medium provided by the present application determine the client process corresponding to the SQL statement according to the client identifier and client port in the running state information of the running SQL statement, and then determine the business process and business program corresponding to the SQL statement according to the process tree related to the client process. Therefore, according to the running state information of the running SQL statement, based on the causal relationship of the physical objects involved in the SQL statement execution process, the SQL statement running in the database is positioned to the granularity level of the business process and business program, which improves the positioning accuracy and realizes the precise positioning of the SQL statement. Moreover, the information used in the SQL statement positioning process all comes from the physical objects naturally generated during the SQL statement execution process, and no additional medium or intermediary is required, so the implementation cost is low.

[0024] Additional aspects and advantages of the present application will be given in part in the description below, and in part will become apparent from the description below, or will be learned through the practice of the present application. BRIEF DESCRIPTION OF THE DRAWINGS

[0025] The above and / or additional aspects and advantages of the present application will become apparent and easily understood from the following description of the embodiments in conjunction with the accompanying drawings, in which:

[0026] Figure 1 A flowchart of a method for locating an SQL statement provided in an embodiment of the present application;

[0027] Figure 2 A schematic diagram of the process from the business program as the SQL statement initiator to the SQL statement being executed by the database provided in the embodiment of the present application;

[0028] Figure 3 A flowchart of another method for locating an SQL statement provided in an embodiment of the present application;

[0029] Figure 4 A flowchart of another method for locating an SQL statement provided in an embodiment of the present application;

[0030] Figure 5 A schematic diagram of the execution result of a process information acquisition command provided in an embodiment of the present application;

[0031] Figure 6 A flowchart of a positioning SQL statement provided in an embodiment of the present application;

[0032] Figure 7 A schematic diagram of the structure of a device for locating a SQL statement provided in an embodiment of the present application;

[0033] Figure 8 It is a schematic diagram of the structure of an electronic device shown in an exemplary embodiment of the present application. DETAILED DESCRIPTION

[0034] Embodiments of the present application are described in detail below, and examples of the embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals throughout represent the same or similar elements or elements having the same or similar functions. The embodiments described below with reference to the accompanying drawings are exemplary and are intended to be used to explain the present application, and should not be construed as limiting the present application.

[0035] It should be noted that the information (including but not limited to user device information, user personal information, etc.), data (including but not limited to data used for analysis, stored data, displayed data, etc.) and signals involved in this application are all authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with relevant laws, regulations and standards of relevant countries and regions.

[0036] The data acquisition, transmission, storage, use, and processing in the technical solution of this application are in compliance with the provisions of national laws and regulations.

[0037] It should be noted that in the embodiments of the present application, certain software, components, models and other existing solutions in the industry may be mentioned, and they should be regarded as exemplary. Their purpose is only to illustrate the feasibility of implementing the technical solution of the present application, but it does not mean that the applicant has or will necessarily use the solution.

[0038] The following describes the SQL statement positioning method and device according to the embodiment of the present application with reference to the accompanying drawings.

[0039] Figure 1 A flowchart of a method for locating an SQL statement provided in an embodiment of the present application.

[0040] In the related art, SQL statements can be located on the client host or business system, but the location is not accurate enough.

[0041] To address this problem, an embodiment of the present application provides a method for locating SQL statements, which can locate the initiating process of the SQL statement, trace its program source, and achieve accurate positioning of the SQL statement.

[0042] In the relevant technical field, the business system architecture may include physical objects such as networks, servers, clients, application nodes, programs, processes, ports, etc.

[0043] Figure 2The figure shows the process from the business program as the origin of the SQL statement to the SQL statement being executed by the database: (1) The business program contains the SQL statement to be executed; (2) The business program is executed, and the corresponding business process is generated on the execution machine; (3) The business process calls and submits the SQL statement to the database service through the database client process; (4) The database service executes the SQL statement.

[0044] Figure 2 The process shown involves the following physical objects in sequence: business program, business process, database client process, and SQL statement in operation, wherein the latter of adjacent physical objects is generated by the former.

[0045] In this regard, the SQL statement positioning method of the embodiment of the present application, based on the cause-effect relationship of the physical objects involved in the execution of the SQL statement, traces back from the running SQL statement to the corresponding business process and business program in the order of effect and cause, so as to achieve accurate positioning of the SQL statement.

[0046] like Figure 1 As shown, the method for locating the SQL statement includes the following steps:

[0047] Step 101, obtaining the running status information of the running SQL statement.

[0048] The running SQL statement may be any running SQL statement that needs to be located and traced.

[0049] In this application, the running status information of the running SQL statement can be obtained from the database system table. Among them, the running status information may include information such as the client identifier and the client port corresponding to the running SQL statement. For example, the client identifier may be the client name, client address, client IP, etc.

[0050] For example, if the database system is PostgreSQL and its derivative database products (such as Greenplum, etc.), the running status information can be obtained through the database system table pg_stat_activity, and the client address in the running status information can be used as the client identifier.

[0051] Optionally, the business program may include one or more SQL statements. In order to distinguish different SQL statements, the running status information of the SQL statements may further include an identifier of the SQL statement.

[0052] It should be noted that, if the network environment is complex (for example, there are virtual machines and network port mapping conversion), the network address and port mapping need to be restored before the subsequent steps are executed.

[0053] Step 102: Determine the client process corresponding to the SQL statement according to the client identifier and the client port.

[0054] The client process refers to the database client process that is called by the business process and submits the SQL statement to the database service for execution.

[0055] In the present application, when the client process on the execution machine is called, the execution machine port is used to connect to the database service; wherein the execution machine identifier and port correspond to the client identifier and client port in the running status information of the SQL statement recorded in the database. Therefore, the client process corresponding to the running SQL statement can be determined based on the client identifier and the client port based on the corresponding relationship.

[0056] Step 103: Determine the business process and business program corresponding to the SQL statement according to the process tree related to the client process.

[0057] The process tree is a group of processes with multi-level parent-child relationships generated by process calls in the operating system, and has a tree structure with hierarchical calling relationships.

[0058] The process tree may include, but is not limited to, information such as the client process, ancestor processes of the client process at all levels, process commands, and sessions of the client process.

[0059] In this application, the business process corresponding to the running SQL statement can be determined from the processes on the process tree related to the client process, and then the business procedure can be determined based on the business process.

[0060] Among them, the business program is the program to which the corresponding static text paragraph belongs before the running SQL statement is executed, and the business process is the process generated by executing the business program.

[0061] It can be seen that static text paragraphs in the program may undergo preprocessing such as variable replacement and expansion before actual execution, and some macro commands are parsed by the database into actual execution statements during execution; therefore, there are differences between the SQL statements running on the database and the SQL statements in the business program, which leads to the obvious defect of inaccurate positioning results in other technical methods by matching SQL statements with business programs through text.

[0062] In the embodiment of the present application, the client process corresponding to the SQL statement is determined according to the client identifier and the client port in the running state information of the running SQL statement, and then the business process and business program corresponding to the SQL statement are determined according to the process tree related to the client process. Thus, according to the running state information of the running SQL statement, based on the causal relationship of the physical objects involved in the SQL statement execution process, the SQL statement running in the database is located at the business process and business program granularity level, which improves the positioning accuracy and realizes the precise positioning of the SQL statement. Moreover, the information used in the SQL statement positioning process all comes from the physical objects naturally generated during the SQL statement execution process, and no additional medium or intermediary is required, so the implementation cost is low.

[0063] Figure 3 A flowchart of another method for locating an SQL statement provided in an embodiment of the present application.

[0064] like Figure 3 As shown, the method for locating the SQL statement may include the following steps:

[0065] Step 301, obtaining the running status information of the running SQL statement.

[0066] In the present application, step 301 can be implemented in any of the embodiments of the present application, so it will not be described in detail here.

[0067] Step 302: Determine the execution machine corresponding to the client identifier according to the client identifier.

[0068] Exemplarily, if the client identifier is a client IP, the host corresponding to the client IP may be determined as the execution machine.

[0069] The computer running the database client process can be called an execution machine, an application node, a scheduling controlled node, an ETL execution machine, an ETL node, a client host, etc. The full name of ETL is Extract-Transform-Load, and the full name of ETL in Chinese is "Extract-Transform-Load", which usually refers to "data processing program".

[0070] Step 303, obtaining the port usage status information of the execution machine.

[0071] The port usage status information may include the port used by the process, the process identifier of the occupied port, etc. For example, the process identifier may be a process number.

[0072] Exemplarily, the port usage status information can be obtained by running a "port usage status acquisition" command on the execution machine.

[0073] For example, taking the execution machine operating system as Linux as an example, the command: lsof-in can be used to obtain the port usage status information, and the execution result of the command is taken: the port used by the process (the ninth column, the column name NAME), the process number (the second column, the column name PID), to obtain one or more groups of port usage status information such as data items [port used by the process, process number], hereinafter referred to as [port, process number].

[0074] Step 304: query the port usage status information according to the client port to determine the process occupying the client port.

[0075] In the present application, the client port can be matched with the port in the port usage status information to determine the process occupying the client port on the execution machine. Exemplarily, the port consistent with the client port can be screened from multiple groups of port usage status information [port, process number], and the process identified by the process number corresponding to the port in the same group of status information is the process occupying the client port.

[0076] That is to say, the client port can be used as a feature to filter out the process number that matches the client port from the port usage status information. For example, taking the execution machine operating system as Linux as an example, based on the aforementioned "port usage status acquisition" command (lsof-in), assuming that the variable client_port has been assigned to the client port number, the following command can be used:

[0077] lsof-in|grep":${client_port}-"|awk'{print$2}'

[0078] A process number matching the client port number is obtained from the port usage status information, and the process identified by the process number is the process occupying the client port.

[0079] Step 305: determine the process occupying the client port as the client process.

[0080] In the present application, the process occupying the client port can be determined as the client process corresponding to the running SQL statement, that is, the process identified by the process number that matches the client port filtered out from the port usage status information can be determined as the client process corresponding to the running SQL statement.

[0081] Step 306: Determine the business process and business program corresponding to the SQL statement according to the process tree related to the client process.

[0082] In the present application, step 306 can be implemented in any of the embodiments of the present application, so it will not be described in detail here.

[0083] In the embodiment of the present application, the corresponding execution machine can be determined according to the client identifier in the running state information of the SQL statement being run, and then according to the client port in the running state information, the process occupying the client port can be determined by querying the port usage state information of the corresponding execution machine, and the process occupying the client port can be determined as the client process corresponding to the SQL statement being run. Thus, according to the relationship between the client port recorded in the database, the process on the execution machine, the port used by the process, etc. during the execution of the SQL statement, the client process can be determined, which is not only highly accurate, but also does not require additional media, and has low implementation costs.

[0084] Figure 4 A flowchart of another method for locating an SQL statement provided in an embodiment of the present application.

[0085] like Figure 4 As shown, the method for locating the SQL statement may include the following steps:

[0086] Step 401, obtaining the running status information of the running SQL statement.

[0087] Step 402: Determine the client process corresponding to the SQL statement according to the client identifier and the client port.

[0088] In the present application, step 401-step 402 can be implemented in any of the embodiments of the present application, so they will not be described in detail here.

[0089] Step 403: Obtain process information according to the client process.

[0090] The process information may include but is not limited to data items such as process, parent process, session, command, etc.

[0091] In this application, the process information can be obtained according to the client process through the process information acquisition command.

[0092] For example, taking the execution machine operating system as Linux, assuming that the variable client_pid has been assigned the client process ID, by executing the following command:

[0093] ps-o pid,ppid,sid,cmd-p$client_pid

[0094] To obtain the process information of the client process, the data items of the command result include: process number, parent process number, session number, command, etc.

[0095] Step 404: Determine a process tree related to the client process based on the process information.

[0096] The processes on the process tree include a client process or a parent process of at least one client process.

[0097] In the present application, the client process can be used as the starting point, and the parent process of the client process can be determined based on the parent process number in the process information, and then the parent process of the parent process can be determined. In this way, the process group including the hierarchical parent-child relationship can be determined as a process tree related to the client process step by step.

[0098] Optionally, the process tree under the same session as the client process may be determined as the process tree related to the client process according to the session number in the process information.

[0099] For example, if the operating system of the execution machine is Linux, assume that the variable client_pid has been assigned the client process ID, and execute the following command:

[0100] sid=`ps-o sid-p$client_pid|grep-v'SID'`

[0101] ps-o pid,ppid,sid,cmd-H--sid$sid

[0102] The first command obtains the session number in the process information according to the client process; the second command obtains the process tree under the same session number. The execution results are as follows: Figure 5 As shown, a set of process information with a parent-child relationship is shown, where the process includes a client process and multiple parent processes thereof, and the data items include a process, a parent process, a session, and a command. Figure 5 In the example, the process ID of the client process is 13682, its parent process ID is 13681, and its session ID is 5985; the process ID of the session head process of the client process is 5985.

[0103] Step 405: determine the service process on the process tree related to the client process.

[0104] Figure 5 A process tree is shown when the business program is called up for execution. In the figure:

[0105] The process with process number 5985 corresponds to a session login process command, and this process is the session head process of the process tree. When the scheduling system calls a job on the execution machine, the operating system of the execution machine generates a session. The job and all subordinate processes are in this session. Each process in the tree uses the process number of the session head process as the session number. The previous steps are based on this principle and obtain the process tree with the session number. The scheduling system uses this process to log in to the operating system of the execution machine. If the Linux system and bash are used as the command execution environment, the command line of this process is displayed as "-bash".

[0106] The process with process number 13679 corresponds to a process command for executing public processing. The parent process number 5985 of the process is the same as the process number of the session head process. That is to say, the process with process number 13679 is a child process of the session head process. The process can be a process generated by the scheduling system after logging into the execution machine operating system and completing the business program processing with the public processing program. The corresponding public processing process command is, for example, "sh run.sh job_abc.sh", where "run.sh" is the public processing program and "job_abc.sh" is the business program. Under this process, its child process 13681 is generated.

[0107] The process with process number 13681 corresponds to a business main program process command, which is a process generated by executing the business main program. The corresponding business main program process command is, for example, "sh job_abc.sh", where "job_abc.sh" is the business main program. Under this process, its child process 13682 is generated.

[0108] The process with process number 13682 is a client process generated by the main business program by calling the client program psql to connect to the database and execute SQL statements or scripts. For example, if the name of the executed SQL script is abc.sql, the process command is in the form of "psql...-f abc.sql".

[0109] The above is a typical example of the hierarchical calling relationship and causal relationship between processes on the process tree. In the actual production system, the process tree may have a greater level depth due to multiple job processing links. A process will have more superiors (ancestors) or subordinates (descendants) instead of just Figure 5 The following will explain how to determine the business process in the process tree:

[0110] In the process tree related to the client process, a parent process of the client process can be determined as a business process. For example, in Example 1, the parent process of the client process can be determined as a business process. For example, Figure 5 In the example, the process ID of the client process is 13682, and the process ID of its parent process is 13681. Then the process with the process ID 13681 can be used as the business process.

[0111] In the process tree related to the client process, a subordinate process of the first process of the session where the client process is located can be determined as a business process; for example, in Example 2, a direct child process of the first process of the session where the client process is located can be determined as a business process. For example, Figure 5In the example, the process ID of the client process is 13682, the process ID of the first process of the session of the client process is 5985, and the process ID of the child process of the first process is 13679. The process with the process ID 13679 can be determined as the business process.

[0112] The business process can be determined by matching the command line features of the business process with the process on the process tree related to the client process. In practice, for the call and execution of a business process, the corresponding command generally has obvious pattern features, such as the number of parameters, order, prefix and suffix matching, keywords, etc., due to the constraints of the business program naming specifications, job design, and scheduling mechanism. Therefore, the business process can be determined based on the matching of command line features. Exemplarily, in Example 3, the command line features of the preset business process are matched with each command line on the process tree, and the process corresponding to the matching command line on the process tree is determined as the business process. For example, "starting with sh and containing the job_ keyword" is used as the preset feature, from Figure 5 The commands of each process are matched. For example, if process numbers 13679 and 13681 match, either one can be determined as a business process.

[0113] The client process itself can be determined as the business process. In practice, it is not ruled out that the SQL file or even the SQL statement string is used as the main business program when the operating system is designed, and the execution is initiated only by the database client program; in this case, the client process can be determined as the business process. For example, in Example 4, when the client process is directly called by the scheduling system, the client process is determined as the business process. For example, in this scenario Figure 5 In the example, there are no parent processes 13681 and 13679 of the client process. There are only client process 13682 and its parent process 5985 which is also the first process of the session on the process tree. Then the process with process number 13682 can be determined as the business process. This example can also be regarded as a special case of the same method as Example 2.

[0114] Step 406, determine the business procedure according to the business process.

[0115] As an implementation method, a command for invoking a business process may be obtained, and the business procedure may be determined according to the command for invoking the business process, thereby improving the accuracy of determining the business procedure.

[0116] As mentioned above, in practice, the corresponding commands for calling and executing business processes generally have obvious pattern characteristics, such as the number of parameters, order, prefix and suffix matching, keywords, etc., due to the constraints of business program naming specifications, job design, and scheduling mechanisms; therefore,

[0117] For example, the business program name can be extracted from the command that calls the business process according to the parameter characteristics of the process command or the name characteristics of the business program therein, and then the business program can be determined according to the business program name, that is, the business program identified by the business program name is the business program. Figure 5 In the above example 1, the business process determined is 13681. For example, its command is "sh job_abc.sh". According to the parameter characteristics, the second parameter in the process command "sh job_abc.sh", i.e. "job_abc.sh", can be taken as the business program name; in the above examples 2 and 3, the business process determined is 13679 or 13681. According to the business program name characteristic "job_???.sh", a matching substring such as "job_abc.sh" can be obtained from the process command as the business program name; in the above example 4, the business process determined is 13682. According to the business program name characteristic "???.sql", a matching substring such as "abc.sql" can be obtained from the process command as the business program name. The program identified by the business program name is the business program; further, through the business program name, the path of the business program can be determined in the production environment in combination with the system deployment specification, and the path of the business program submission can be determined in the code base in combination with the R&D specification.

[0118] Thus, the business procedure can be determined from the command for invoking the business process, and the method is simple, convenient and highly accurate.

[0119] In the embodiment of the present application, by obtaining process information with a parent-child relationship according to the client process, the process tree is determined step by step upward through the parent process based on the process information, and the business process corresponding to the SQL statement is determined according to the process tree, and then the business program is determined according to the business process. Therefore, by using the process tree related to the client process to determine the business process and business program, the accuracy can be improved, and the forward tracing and positioning of the SQL statement in operation can be realized.

[0120] In order to facilitate understanding of the positioning method of the SQL statement of this application, the following Figure 6 To explain, Figure 6 A flowchart of locating SQL statements provided in an embodiment of the present application.

[0121] like Figure 6 As shown, the process is as follows:

[0122] S610, acquiring the running status information of the running SQL statement from the database system table, where the running status information includes data items such as the client identifier and the client port corresponding to the SQL statement.

[0123] In some embodiments, in order to distinguish multiple SQL statements, the running status information may further include an SQL statement identifier.

[0124] Afterwards, based on the client ID and the client port, the process occupying the corresponding port on the client host with the corresponding ID is obtained, and the process is determined to be the client process corresponding to the SQL statement. Specifically:

[0125] S621, determine the corresponding execution machine according to the client identifier.

[0126] In some embodiments, when "client IP" is used as an identifier, the host corresponding to the IP address is determined as the execution machine.

[0127] S622, on the execution machine, obtaining the execution machine port usage status, the usage status including: the port used by the process, the process number and other data items.

[0128] In some embodiments, the port usage status information of the execution machine can be obtained through commands. For details, please refer to the above embodiments, which will not be repeated here.

[0129] S623, in the above results, use the port as a feature to filter the process number that is consistent with the client port

[0130] In some embodiments, the process ID may be obtained through a command, and details may be found in the above embodiments.

[0131] S624: The process identified by the above screened process number is determined as the client process corresponding to the SQL statement.

[0132] S630: Determine a process as a service process on a process tree related to the client process, wherein the process tree is a process tree on the execution machine, that is, the execution machine process tree.

[0133] In some embodiments, starting from the client process, the process tree related to the client process is determined step by step through the parent process. Exemplarily, the relevant process tree can be determined based on the session of the client process.

[0134] In some embodiments, on the process tree, the parent process of the client process may be determined as the service process.

[0135] In some embodiments, on the process tree, a child process of the session head process of the client process may be determined as a service process.

[0136] In some embodiments, on a process tree, a business process may be determined based on command line features on the process tree.

[0137] S640, determining the business procedure according to the command for invoking the business process.

[0138] Exemplarily, the service program name may be extracted from the command according to the service program extension.

[0139] The SQL statement positioning method of the present application can locate the SQL statements running in the database to the granularity level of individual business processes and business programs with high accuracy. The positioning method can avoid the likelihood of keyword matching SQL statements, and is based on the causal relationship of physical objects involved in the execution of SQL statements, with high accuracy. In addition, the positioning process of SQL statements only involves physical objects naturally generated during the execution of SQL statements, without the need for additional media or intermediaries, and has low implementation costs.

[0140] The above illustrates: based on the causal relationship, in the order from effect to cause, a method for locating the corresponding business process and business program from the running SQL statement; based on the reverse use of the above-mentioned SQL statement positioning and tracing method, in "another embodiment" of the present application, also based on the causal relationship, the corresponding running SQL statement can be located from the business program and business process in the order from cause to effect.

[0141] In this embodiment, the running business program and the business process corresponding to the running business program can be obtained, and based on the process tree related to the business process corresponding to the running business program, the client process connected to the database can be found, and the connection information of the client process connected to the database can be obtained. Based on the connection information, the SQL statement running on the database can be determined.

[0142] The connection information may include the identifier of the execution machine where the client process is located, the occupied client port number, the client IP, the database IP, etc.

[0143] Exemplarily, positioning is performed with the business program as the starting point: first, when determining the business process, a command containing the name of the business program can be found in the process information, and the process called up by the command is the business process; application scenario, for example, when executing a business program of a certain transaction code, it is found that it has been stagnant at a certain stage for a long time, then according to the running business program, continue to analyze the physical objects involved along the corresponding business process, that is, through the method of "another embodiment" of the present application, it can be determined which SQL statement of the business program the database is currently running, and then the SQL statement can be optimized later.

[0144] Therefore, for a running business program, the causal relationship of the physical objects involved in the SQL statement execution process can be used to determine which SQL statement in the business program the database is running, thereby locating the running SQL statement.

[0145] In order to implement the above embodiment, the present application also proposes a SQL statement positioning device. Figure 7 A schematic diagram of the structure of a device for locating a SQL statement provided in an embodiment of the present application.

[0146] like Figure 7 As shown, the SQL statement positioning device 700 includes:

[0147] The SQL statement sensing module 710 is used to obtain the running status information of the running SQL statement; wherein the running status information includes the client identifier and the client port corresponding to the SQL statement;

[0148] The client determination module 720 is used to determine the client process corresponding to the SQL statement according to the client identifier and the client port;

[0149] The business determination module 730 is used to determine the business process and business program corresponding to the SQL statement according to the process tree related to the client process; wherein the business program is a program containing the SQL statement, and the business process is a process generated by executing the business program.

[0150] Further, in a possible implementation of the embodiment of the present application, the client determination module 720 is used to:

[0151] According to the client identifier, determining an execution machine corresponding to the client identifier;

[0152] Acquire the port usage status information of the execution machine; wherein the port usage status information includes the port used by the process and the process identifier occupying the port;

[0153] According to the client port, query the port usage status information to determine the process occupying the client port;

[0154] The process occupying the client port is determined as the client process.

[0155] Furthermore, in a possible implementation of the embodiment of the present application, the service determination module 730 is configured to:

[0156] According to the client process, obtaining process information;

[0157] Determine, according to the process information, a process tree related to the client process; and determine, on the process tree related to the client process, the service process;

[0158] The business procedure is determined according to the business process.

[0159] Furthermore, in a possible implementation of the embodiment of the present application, the service determination module 730 is configured to:

[0160] Determine a parent process of the client process on the process tree related to the client process as the service process; or

[0161] Determine a subordinate process of the first process of the session of the client process on the process tree related to the client process as the service process; or

[0162] Determine the service process according to command line features on a process tree related to the client process; or

[0163] The client process itself is determined as the service process.

[0164] Furthermore, in a possible implementation of the embodiment of the present application, the service determination module 730 is configured to:

[0165] Extracting the business program name from the command for invoking the business process according to the command parameter feature or the business program name feature;

[0166] The business program is determined according to the business program name.

[0167] Furthermore, in a possible implementation manner of the embodiment of the present application, the process tree related to the client process includes the client process or at least one parent process of the client process.

[0168] Furthermore, in a possible implementation of the embodiment of the present application, the device may also include:

[0169] Business perception module, used to obtain the running business programs and their corresponding business processes;

[0170] The client process determination module is used to find the client process connected to the database according to the process tree related to the business process corresponding to the running business program, and obtain the connection information between the client process connected to the database and the database;

[0171] The SQL statement determination module is used to determine the SQL statement being executed according to the connection information.

[0172] It should be noted that the above explanation of the embodiment of the method for locating an SQL statement is also applicable to the device for locating an SQL statement in this embodiment, and will not be repeated here.

[0173] In the embodiment of the present application, the client process corresponding to the SQL statement is determined according to the client identifier and the client port in the running state information of the running SQL statement, and then the business process and business program corresponding to the SQL statement are determined according to the process tree related to the client process. Thus, according to the running state information of the running SQL statement, based on the causal relationship of the physical objects involved in the SQL statement execution process, the SQL statement running in the database is located at the business process and business program granularity level, which improves the positioning accuracy and realizes the precise positioning of the SQL statement. Moreover, the information used in the SQL statement positioning process all comes from the physical objects naturally generated during the SQL statement execution process, and no additional medium or intermediary is required, so the implementation cost is low.

[0174] In order to implement the above embodiments, the present application also proposes an electronic device, comprising: a processor, and a memory communicatively connected to the processor; the memory stores computer-executable instructions; the processor executes the computer-executable instructions stored in the memory to implement the method provided by the above embodiments.

[0175] In order to implement the above embodiments, the present application also proposes an electronic device, comprising: a processor, and a memory communicatively connected to the processor; the memory stores computer-executable instructions; the processor executes the computer-executable instructions stored in the memory to implement the method provided by the above embodiments.

[0176] As an example, Figure 8 is a schematic diagram of the structure of an electronic device shown in an exemplary embodiment of the present application, such as Figure 8 As shown, the electronic device 800 may further include:

[0177] The memory 810 and the processor 820, a bus 830 connecting different components (including the memory 810 and the processor 820), the memory 810 stores a computer program, and when the processor 820 executes the program, the method for locating the SQL statement described in the embodiment of the present application is implemented.

[0178] Bus 830 represents one or more of several types of bus structures, including a memory bus or memory controller, a peripheral bus, an accelerated graphics port, a processor or a local bus using any of a variety of bus architectures. For example, these architectures include but are not limited to Industry Standard Architecture (ISA) bus, Micro Channel Architecture (MAC) bus, Enhanced ISA bus, Video Electronics Standards Association (VESA) local bus and Peripheral Component Interconnect (PCI) bus.

[0179] The electronic device 800 typically includes a variety of electronic device readable media. These media can be any available media that can be accessed by the electronic device 800, including volatile and non-volatile media, removable and non-removable media.

[0180] The memory 810 may also include computer system readable media in the form of volatile memory, such as random access memory (RAM) 840 and / or cache memory 850. The electronic device 800 may further include other removable / non-removable, volatile / non-volatile computer system storage media. By way of example only, the storage system 860 may be used to read and write non-removable, non-volatile magnetic media ( Figure 8 not shown, usually called a "hard drive"). Although Figure 8 Not shown, a disk drive for reading and writing to a removable non-volatile disk (e.g., a "floppy disk"), and an optical disk drive for reading and writing to a removable non-volatile optical disk (e.g., a CD-ROM, a DVD-ROM, or other optical medium) may be provided. In these cases, each drive may be connected to bus 830 via one or more data medium interfaces. Memory 810 may include at least one program product having a set (e.g., at least one) of program modules that are configured to perform the functions of each embodiment of the present application.

[0181] A program / utility 880 having a set (at least one) of program modules 870 may be stored, for example, in the memory 810, such program modules 870 including but not limited to an operating system, one or more application programs, other program modules, and program data, each of which or some combination may include an implementation of a network environment. The program modules 870 generally perform the functions and / or methods of the embodiments described herein.

[0182] The electronic device 800 may also communicate with one or more external devices 890 (e.g., keyboard, pointing device, display 891, etc.), may also communicate with one or more devices that enable a user to interact with the electronic device 800, and / or may communicate with any device that enables the electronic device 800 to communicate with one or more other computing devices (e.g., network card, modem, etc.). Such communication may be performed via an input / output (I / O) interface 892. Furthermore, the electronic device 800 may also communicate with one or more networks (e.g., local area network (LAN), wide area network (WAN) and / or public network, such as the Internet) via a network adapter 893. As shown, the network adapter 893 communicates with other modules of the electronic device 800 via a bus 830. It should be understood that, although not shown in the figure, other hardware and / or software modules may be used in conjunction with the electronic device 800, including but not limited to: microcode, device drivers, redundant processing units, external disk drive arrays, RAID systems, tape drives, and data backup storage systems.

[0183] The processor 820 executes various functional applications and data processing by running the programs stored in the memory 810 .

[0184] In order to implement the above embodiments, the present application also proposes a computer-readable storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are executed by a processor, they are used to implement the methods provided by the above embodiments.

[0185] In order to implement the above embodiments, the present application also proposes a computer program product, including a computer program, which implements the methods provided by the above embodiments when executed by a processor.

[0186] The collection, storage, use, processing, transmission, provision and disclosure of user personal information involved in this application are in compliance with relevant laws and regulations and do not violate public order and good morals.

[0187] It should be noted that personal information from users should be collected for legitimate and reasonable purposes and should not be shared or sold outside of these legitimate uses. In addition, such collection / sharing should be carried out after receiving the user's informed consent, including but not limited to notifying the user to read the user agreement / user notice and sign the agreement / authorization including authorization of relevant user information before the user uses the function. In addition, any necessary steps should be taken to protect and safeguard access to such personal information data and ensure that others who have access to personal information data comply with its privacy policy and procedures.

[0188] This application is expected to provide an implementation scheme for users to selectively block the use or access of personal information data. That is, this application is expected to provide hardware and / or software to prevent or block access to such personal information data. Once the personal information data is no longer needed, risks can be minimized by limiting data collection and deleting data. In addition, when applicable, personal identifiers are removed from such personal information to protect the privacy of users.

[0189] In the description of the aforementioned embodiments, the description with reference to the terms "one embodiment", "some embodiments", "example", "specific example", or "some examples" etc. means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present application. In this specification, the schematic representations of the above terms do not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described may be combined in any one or more embodiments or examples in a suitable manner. In addition, those skilled in the art may combine and combine the different embodiments or examples described in this specification and the features of the different embodiments or examples, without contradiction.

[0190] In addition, the terms "first" and "second" are used for descriptive purposes only and should not be understood as indicating or implying relative importance or implicitly indicating the number of the indicated technical features. Therefore, the features defined as "first" and "second" may explicitly or implicitly include at least one of the features. In the description of this application, the meaning of "plurality" is at least two, such as two, three, etc., unless otherwise clearly and specifically defined.

[0191] Any process or method description in a flowchart or otherwise described herein may be understood to represent a module, fragment or portion of code comprising one or more executable instructions for implementing the steps of a custom logical function or process, and the scope of the preferred embodiments of the present application includes alternative implementations in which functions may not be performed in the order shown or discussed, including performing functions in a substantially simultaneous manner or in the reverse order depending on the functions involved, which should be understood by technicians in the technical field to which the embodiments of the present application belong.

[0192] The logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as an ordered list of executable instructions for implementing logical functions, and can be embodied in any computer-readable medium for use by an instruction execution system, device or apparatus (such as a computer-based system, a system including a processor, or other system that can fetch instructions from an instruction execution system, device or apparatus and execute the instructions), or in combination with these instruction execution systems, devices or apparatuses. For the purpose of this specification, "computer-readable medium" can be any device that can contain, store, communicate, propagate or transmit a program for use by an instruction execution system, device or apparatus, or in combination with these instruction execution systems, devices or apparatuses. More specific examples of computer-readable media (a non-exhaustive list) include the following: an electrical connection with one or more wires (electronic device), a portable computer disk box (magnetic device), a random access memory (RAM), a read-only memory (ROM), an erasable and programmable read-only memory (EPROM or flash memory), a fiber optic device, and a portable compact disk read-only memory (CDROM). In addition, the computer-readable medium may even be paper or other suitable medium on which the program is printed, since the program may be obtained electronically, for example, by optically scanning the paper or other medium and then editing, interpreting or processing in other suitable ways if necessary, and then stored in a computer memory.

[0193] It should be understood that the various parts of the present application can be implemented by hardware, software, firmware or a combination thereof. In the above-mentioned embodiments, multiple steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system. For example, if implemented by hardware, as in another embodiment, it can be implemented by any one of the following technologies known in the art or their combination: a discrete logic circuit having a logic gate circuit for implementing a logic function for a data signal, a dedicated integrated circuit having a suitable combination of logic gate circuits, a programmable gate array (PGA), a field programmable gate array (FPGA), etc.

[0194] A person skilled in the art may understand that all or part of the steps in the method for implementing the above-mentioned embodiment may be completed by instructing related hardware through a program, and the program may be stored in a computer-readable storage medium, which, when executed, includes one or a combination of the steps of the method embodiment.

[0195] In addition, each functional unit in each embodiment of the present application may be integrated into a processing module, or each unit may exist physically separately, or two or more units may be integrated into one module. The above-mentioned integrated module may be implemented in the form of hardware or in the form of a software functional module. If the integrated module is implemented in the form of a software functional module and sold or used as an independent product, it may also be stored in a computer-readable storage medium.

[0196] The storage medium mentioned above may be a read-only memory, a magnetic disk or an optical disk, etc. Although the embodiments of the present application have been shown and described above, it can be understood that the above embodiments are exemplary and cannot be understood as limiting the present application. A person of ordinary skill in the art may change, modify, replace and modify the above embodiments within the scope of the present application.

Claims

1. A method for locating an SQL statement, characterized in that: The method comprises the following steps: Obtaining running status information of a running SQL statement; wherein the running status information includes a client identifier and a client port corresponding to the SQL statement; Determine the client process corresponding to the SQL statement according to the client identifier and the client port; According to the process tree related to the client process, the business process and business program corresponding to the SQL statement are determined; wherein the business program is a program including the SQL statement, and the business process is a process generated by executing the business program.

2. The method according to claim 1, characterized in that The determining, according to the client identifier and the client port, the client process corresponding to the SQL statement includes: According to the client identifier, determining an execution machine corresponding to the client identifier; Acquire the port usage status information of the execution machine, wherein the port usage status information includes the port used by the process and the process identifier occupying the port; According to the client port, query the port usage status information to determine the process occupying the client port; The process occupying the client port is determined as the client process.

3. The method according to claim 1, characterized in that Determining the business process and business program corresponding to the SQL statement according to the process tree related to the client process includes: According to the client process, obtaining process information; Determine a process tree related to the client process according to the process information; Determine the service process on a process tree related to the client process; The business procedure is determined according to the business process.

4. The method according to claim 3, characterized in that Determining the service process on the process tree related to the client process includes: Determine a parent process of the client process on the process tree related to the client process as the service process; or Determine a subordinate process of the first process of the session of the client process on the process tree related to the client process as the service process; or Determine the service process according to command line features on a process tree related to the client process; or The client process itself is determined as the service process.

5. The method according to claim 3, characterized in that Determining the business procedure according to the business process includes: Extracting the business program name from the command for invoking the business process according to the command parameter feature or the business program name feature; The business program is determined according to the business program name.

6. The method according to claim 3, characterized in that in, The process tree related to the client process includes the client process or at least one parent process of the client process.

7. The method according to any one of claims 1 to 6, characterized in that Also includes: Get the running business programs and their corresponding business processes; According to the process tree related to the business process corresponding to the running business program, find the client process connected to the database, and obtain the connection information between the client process connected to the database and the database; The SQL statement being executed on the database is determined according to the connection information.

8. A device for locating SQL statements, characterized in that: include: An SQL statement perception module, used to obtain the running status information of the running SQL statement; wherein the running status information includes the client identifier and the client port corresponding to the SQL statement; A client determination module, used to determine the client process corresponding to the SQL statement according to the client identifier and the client port; The business determination module is used to determine the business process and business program corresponding to the SQL statement according to the process tree related to the client process; wherein the business program is a program containing the SQL statement, and the business process is a process generated by executing the business program.

9. An electronic device, characterized in that: include: A processor, and a memory communicatively connected to the processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory to implement the method according to any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 7 when executed by a processor.

11. A computer program product, characterized in that The invention comprises a computer program, which implements the method according to any one of claims 1 to 7 when being executed by a processor.

Images