Access path synchronization method and device

Abstract

The embodiment of the present application provides an access path synchronization method and device, which can be used in the field of big data technology. The method includes: importing the statistical information corresponding to the database system table of the production environment into the target test environment that is currently in the state of restoring the production environment data, so that the target test environment is currently simulated as an environment with the same data volume and data distribution as the production environment; based on the target test environment with the same data volume and data distribution as the production environment, determining the database target access path of the target test environment, so that the database target access path is the same as the database access path pre-determined by the optimizer of the production environment. The present application can automatically synchronize the access path of the production environment database to the test environment, and can effectively simulate the production scenario corresponding to the production environment in the test environment, thereby effectively improving the application reliability and stability of the test environment, and can effectively improve the program efficiency and effectiveness of the test.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, in particular to the field of big data technology, and specifically to an access path synchronization method and device. Background Art

[0002] The access path of a host database such as DB2, a relational database management system, is usually determined by the optimizer of the host database. The optimizer determines the access path of each statement based on the query conditions of the access statement and the database statistics (CATALOG). The optimizer selects an access path that it considers to be the best for the access statement based on the data distribution recorded in the statistics. However, the optimizer's selection is not absolutely accurate, and the optimizer may mistakenly select a path with poor efficiency. When the test environment restores the production environment data, it is generally necessary to undergo desensitization and extraction because the cost of restoring the full amount of production data is too high. The extracted production data has significant differences in statistical information content such as data volume and data distribution from the production environment. Therefore, the access path selected by the host database optimizer through the test environment statistics is often quite different from that of the production environment, and cannot simulate the production scenario.

[0003] Therefore, it is necessary to synchronize the access path of the production environment database to the test environment. Otherwise, the test environment cannot correctly simulate the production scenario, and a large number of program efficiency problems will occur, making it impossible to successfully complete the batch job execution plan in the test environment. Summary of the invention

[0004] In response to the problems in the prior art, the present application provides an access path synchronization method and device, which can automatically synchronize the access path of the production environment database to the test environment, and can effectively simulate the production scenario corresponding to the production environment in the test environment, thereby effectively improving the application reliability and stability of the test environment, and can effectively improve the program efficiency and effectiveness of the test, so that the batch job execution plan in the test environment can be successfully completed.

[0005] In order to solve the above technical problems, this application provides the following technical solutions:

[0006] In a first aspect, the present application provides an access path synchronization method, comprising:

[0007] Importing statistical information corresponding to the database system table of the production environment into the target test environment currently in the state of restoring the production environment data, so that the target test environment is currently simulated as an environment with the same data volume and data distribution as the production environment;

[0008] Based on the target test environment having the same data volume and data distribution as the production environment, a database target access path of the target test environment is determined so that the database target access path is the same as the database access path predetermined by the optimizer of the production environment.

[0009] Further, the importing of the statistical information corresponding to the database system table of the production environment into the target test environment which is currently in the state of restoring the production environment data includes:

[0010] Based on a preset statistical information type for determining a database access path, extract corresponding statistical information from a database system table of the production environment;

[0011] The statistical information is imported into the target test environment which is currently in the state of restoring the production environment data.

[0012] Further, the importing of the statistical information into the target test environment which is currently in a state of restoring production environment data includes:

[0013] Importing the statistical information into a temporary table corresponding to the target test environment that is currently in the state of restoring production environment data, and making data correspondence between the temporary table and the database system table of the production environment;

[0014] A corresponding update statement is generated for the temporary table to import the statistical information into the target test environment.

[0015] Furthermore, the database system table of the production environment includes:

[0016] Production environment table information table, production environment table space information table, production environment index information table and production environment field information table;

[0017] Correspondingly, the statistical information types include: information types corresponding to the production environment table information table, the production environment table space information table, the production environment index information table and the production environment field information table.

[0018] Furthermore, the determining of the target database access path of the target test environment based on the target test environment having the same data volume and data distribution as the production environment includes:

[0019] The optimizer corresponding to the target test environment whose current data volume and data distribution are the same as those of the production environment is triggered, so that the optimizer determines a target access path to the database of the target test environment.

[0020] Further, after determining the target access path of the database of the target test environment based on the target test environment having the same data volume and data distribution as the production environment, the method further includes:

[0021] According to the database target access path, updating the current database access path of the target test environment in a preset access path summary table, wherein the access path summary table is used to store the correspondence between the database access path of the production environment and the database access path of the target test environment;

[0022] In the access path summary table, the manually adjusted access paths in the current database access paths of the target test environment are all modified to be the same as the corresponding database access paths of the production environment.

[0023] Furthermore, in the access path summary table, all manually adjusted access paths in the database access path of the current target test environment are modified to be the same as the corresponding database access path of the production environment, including:

[0024] Determine whether the access path summary table contains a record in which the database access path of the production environment is different from the database access path of the target test environment; if so, determine the database access path of the target test environment in the record as a manually adjusted access path;

[0025] The manually adjusted access path is modified to be the same as the database access path of the production environment in the same record.

[0026] In a second aspect, the present application provides an access path synchronization device, including:

[0027] An environment simulation module is used to import the statistical information corresponding to the database system table of the production environment into the target test environment which is currently in the state of restoring the production environment data, so that the target test environment is currently simulated to be an environment with the same data volume and data distribution as the production environment;

[0028] The path synchronization module is used to determine the database target access path of the target test environment based on the target test environment whose data volume and data distribution are the same as those of the production environment, so that the database target access path is the same as the database access path predetermined by the optimizer of the production environment.

[0029] In a third aspect, the present application provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the access path synchronization method when executing the program.

[0030] In a fourth aspect, the present application provides a computer-readable storage medium having a computer program stored thereon, which implements the access path synchronization method when executed by a processor.

[0031] It can be seen from the above technical scheme that the present application provides an access path synchronization method and device, the method comprising: importing statistical information corresponding to the database system table of the production environment into a target test environment that is currently in a state of restoring the production environment data, so that the target test environment is currently simulated as an environment with the same data volume and data distribution as the production environment; based on the target test environment with the same data volume and data distribution as the production environment, determining the database target access path of the target test environment, so that the database target access path is the same as the database access path predetermined by the optimizer of the production environment, by importing the statistical information corresponding to the database system table of the production environment into the target test environment that is currently in a state of restoring the production environment data. In the environment, the production scenario corresponding to the production environment can be effectively simulated in the test environment, so that the database of the target test environment determines that the local data volume and data distribution are the same as those of the production environment, and then when the target test environment restores the production environment data, after its optimizer generates a database access path different from that of the production environment, the optimizer of the target test environment is triggered again to redetermine the database target access path, so that the database target access path is synchronized with the database access path determined by the optimizer of the production environment, thereby effectively improving the application reliability and stability of the test environment, and effectively improving the program efficiency and effectiveness of the test, so that the batch job execution plan in the test environment can be successfully completed, and the user experience of the operation and maintenance personnel is effectively improved. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0033] Figure 1 It is a schematic diagram of the relationship between the access path synchronization device and the client device in an embodiment of the present application.

[0034] Figure 2 This is a first flow chart of the access path synchronization method in the embodiment of the present application.

[0035] Figure 3 This is a second flow chart of the access path synchronization method in the embodiment of the present application.

[0036] Figure 4This is a third flow chart of the access path synchronization method in the embodiment of the present application.

[0037] Figure 5 This is a fourth flow chart of the access path synchronization method in the embodiment of the present application.

[0038] Figure 6 This is a fifth flow chart of the access path synchronization method in the embodiment of the present application.

[0039] Figure 7 This is a sixth flow chart of the access path synchronization method in the embodiment of the present application.

[0040] Figure 8 It is a structural diagram of an access path synchronization device in an embodiment of the present application.

[0041] Fig. 9 It is a schematic diagram of the overall process of host database synchronization provided by the application example of this application.

[0042] Fig.10 It is a specific flow chart of database statistical information synchronization provided by the application example of this application.

[0043] Fig.11 It is a specific flow chart of manually adjusted access path synchronization provided by the application example of this application.

[0044] Fig.12 It is a schematic diagram of the structure of an electronic device in an embodiment of the present application. DETAILED DESCRIPTION

[0045] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0046] It should be noted that the access path synchronization method and device disclosed in the present application can be used in the field of big data technology, and can also be used in any field other than the field of big data technology. The application field of the access path synchronization method and device disclosed in the present application is not limited.

[0047] In view of the fact that there is currently no method for automatically synchronizing the access path of the production environment database to the test environment, which makes it impossible for the test environment to simulate the generation scenario, thereby affecting the program efficiency and effectiveness of the test, and making it impossible for the batch job execution plan in the test environment to be successfully completed, the embodiment of the present application provides an access path synchronization method, which imports the statistical information corresponding to the database system table of the production environment into the target test environment that is currently in the state of restoring the production environment data, so that the target test environment is currently simulated as an environment with the same data volume and data distribution as the production environment; based on the target test environment with the same data volume and data distribution as the production environment, determines the database target access path of the target test environment, so that the database target access path is the same as the database access path predetermined by the optimizer of the production environment, and By importing the statistical information corresponding to the database system table of the target test environment into the target test environment which is currently in the state of restoring the production environment data, the production scenario corresponding to the production environment can be effectively simulated in the test environment, so that the database of the target test environment determines that the local data volume and data distribution are the same as those of the production environment, and then when the target test environment restores the production environment data, after its optimizer generates a database access path different from that of the production environment, the optimizer of the target test environment can be triggered again to re-determine the database target access path, so that the database target access path is synchronized with the database access path determined by the optimizer of the production environment, thereby effectively improving the application reliability and stability of the test environment, and effectively improving the program efficiency and effectiveness of the test, so that the batch job execution plan in the test environment can be successfully completed, and the user experience of the operation and maintenance personnel can be effectively improved.

[0048] Based on the above content, the present application also provides an access path synchronization device for implementing the access path synchronization method provided in one or more embodiments of the present application. The access path synchronization device can be a server. Figure 1 The access path synchronization device can communicate and connect with each client device in sequence by itself or through a third-party server, etc. The access path synchronization device can receive a trigger instruction for access path synchronization sent by the client device, and import the statistical information corresponding to the database system table of the production environment into the target test environment that is currently in the state of restoring the production environment data, so that the target test environment is currently simulated as an environment with the same data volume and data distribution as the production environment; based on the target test environment with the same data volume and data distribution as the production environment, determine the database target access path of the target test environment, so that the database target access path is the same as the database access path predetermined by the optimizer of the production environment, and the access path synchronization device can also send the database access path synchronization result to the user's client device, etc. for display.

[0049] In another practical application scenario, the access path synchronization part of the aforementioned access path synchronization device can be executed in a server as described above, or all operations can be completed in the user terminal device. The specific selection can be based on the processing capability of the user terminal device and the limitations of the user's usage scenario. This application does not limit this. If all operations are completed in the user terminal device, the user terminal device may also include a processor for specific processing of access path synchronization.

[0050] It is understandable that the mobile terminal may include any mobile device capable of loading applications, such as a smart phone, a tablet electronic device, a network set-top box, a portable computer, a personal digital assistant (PDA), a vehicle-mounted device, a smart wearable device, etc. Among them, the smart wearable device may include smart glasses, smart watches, smart bracelets, etc.

[0051] The above-mentioned mobile terminal may have a communication module (i.e., a communication unit) that can communicate with a remote server to achieve data transmission with the server. The server may include a server on the task scheduling center side, and other implementation scenarios may also include a server on an intermediate platform, such as a server on a third-party server platform that has a communication link with the task scheduling center server. The server may include a single computer device, or a server cluster consisting of multiple servers, or a server structure of a distributed device.

[0052] The server and the mobile terminal may communicate with each other using any suitable network protocol, including network protocols that have not yet been developed on the date of filing this application. The network protocols may include, for example, TCP / IP, UDP / IP, HTTP, HTTPS, etc. Of course, the network protocols may also include, for example, RPC (Remote Procedure Call Protocol) and REST (Representational State Transfer) protocols used on top of the above protocols.

[0053] The details are described in detail through the following embodiments and application examples.

[0054] In order to solve the problem that there is currently no method for automatically synchronizing the access path of the production environment database to the test environment, which makes it impossible for the test environment to simulate the generation scenario, thereby affecting the program efficiency and effectiveness of the test, and making it impossible for the batch job execution plan in the test environment to be successfully completed, the present application provides an embodiment of an access path synchronization method, see Figure 2 The access path synchronization method performed by the access path synchronization device specifically includes the following contents:

[0055] Step 100: Import the statistical information corresponding to the database system table of the production environment into the target test environment which is currently in the state of restoring the production environment data, so that the target test environment is currently simulated as an environment with the same data volume and data distribution as the production environment.

[0056] It is understandable that the database system table of the production environment may refer to the system table of the host database corresponding to the production system, and the database system table of the target test environment may refer to the system table of the host database corresponding to the test system or the delivery system.

[0057] In one or more embodiments of the present application, the system table of the production environment database is imported into the temporary table of the test environment by means of backup and recovery; since the system table will affect the normal operation of the production environment database, it is necessary to accurately identify all information that affects the access path and import it into the test environment; after ensuring the consistency of statistical information between the production and test environments, the full access path is re-determined.

[0058] Step 200: Based on the target test environment having the same data volume and data distribution as the production environment, determine the database target access path of the target test environment so that the database target access path is the same as the database access path predetermined by the optimizer of the production environment.

[0059] In step 200, the statistical information also exists in the form of a system table in a host database such as DB2. The present invention extracts the statistical information of the production environment during data recovery and tries to import it into the test environment. When the actual amount of data in the test environment table is small, the host database detects that the data amount and data distribution of the test environment after the production environment statistical information is imported are consistent with the production environment, so as to achieve the purpose of enabling the optimizer to select the same access path in the test environment as that in the production environment.

[0060] From the above description, it can be seen that the access path synchronization method provided in the embodiment of the present application can effectively simulate the production scenario corresponding to the production environment in the test environment by importing the statistical information corresponding to the database system table of the production environment into the target test environment that is currently in the state of restoring the production environment data, so that the database of the target test environment determines that the local data volume and data distribution are the same as those of the production environment, and then, when the target test environment restores the production environment data, after its optimizer generates a database access path different from the production environment, it can trigger the optimizer of the target test environment again to re-determine the database target access path, so that the database target access path is synchronized with the database access path determined by the optimizer of the production environment, thereby effectively improving the application reliability and stability of the test environment, and can effectively improve the program efficiency and effectiveness of the test, so that the batch job execution plan in the test environment can be successfully completed, and the user experience of the operation and maintenance personnel can be effectively improved.

[0061] In order to ensure that the extracted statistical information can be used to determine the database access path, an embodiment of the access path synchronization method provided in this application is as follows: Figure 3 , step 100 of the access path synchronization method specifically includes the following contents:

[0062] Step 110: Based on a preset statistical information type for determining a database access path, corresponding statistical information is extracted from a database system table of the production environment.

[0063] Step 120: Import the statistical information into the target test environment that is currently in the state of restoring production environment data.

[0064] It is understandable that the database system table of the production environment contains all the information required by the optimizer of the production environment database to determine the access path of a statement. After a large number of tests and experiments, it is known that extracting the application table statistical information from the database system table of the production environment can achieve the synchronization of statistical information without affecting the normal operation of the system. The preset statistical information type for determining the database access path can be pre-set by the operation and maintenance personnel and stored in the local access path synchronization device or in a database accessible to the access path synchronization device.

[0065] From the above description, it can be seen that the access path synchronization method provided in the embodiment of the present application, through the preset setting of the statistical information type used to determine the database access path, and extracting the corresponding statistical information from the database system table of the production environment according to the statistical information type, can effectively ensure that the extracted statistical information can be used to determine the database access path, and then can effectively improve the effectiveness and reliability of subsequent applications of the statistical information, so as to further improve the reliability and effectiveness of access path synchronization.

[0066] In order to import the statistical information corresponding to the database system table of the production environment into the target test environment without affecting the operation stability of the production environment, an embodiment of the access path synchronization method provided in the present application is provided. Figure 4 , step 120 of the access path synchronization method specifically includes the following contents:

[0067] Step 121: import the statistical information into a temporary table corresponding to the target test environment that is currently in the state of restoring production environment data, and make data correspondence between the temporary table and the database system table of the production environment.

[0068] Step 122: Generate a corresponding update statement for the temporary table to import the statistical information into the target test environment.

[0069] Specifically, import all the statistical information of the production environment described above into the temporary table of the test environment, match the production environment table with the test environment table one by one through the database name, table name, owner and other information, and then generate update statements for each table in the test environment to accurately import the statistical information of the production environment into the test environment. At this time, the database of the test environment will detect that the local data volume is consistent with that of the production environment, and the data distribution is also the same as that of the production environment. After completing the above actions, the redetermination of the full access path can be triggered. At this time, the access path re-determined by the optimizer of the test environment is completely consistent with the access path automatically confirmed by the optimizer of the production environment database.

[0070] From the above description, it can be seen that the access path synchronization method provided in the embodiment of the present application, by setting a temporary table, can import the statistical information corresponding to the database system table of the production environment into the target test environment without affecting the operating stability of the production environment. At the same time, by generating corresponding update statements for the temporary table, the efficiency and reliability of importing the statistical information into the target test environment can be effectively improved, thereby enabling the access path of the production environment database to be automatically synchronized to the test environment, and the production scenario corresponding to the production environment can be effectively simulated in the test environment, thereby effectively improving the application reliability and stability of the test environment, and effectively improving the program efficiency and effectiveness of the test, so that the batch job execution plan in the test environment can be successfully completed.

[0071] In order to further improve the reliability of the access path synchronization process, in an embodiment of the access path synchronization method provided in the present application, the database system table of the production environment in the access path synchronization method specifically includes the following content:

[0072] Production environment table information table, production environment table space information table, production environment index information table and production environment field information table;

[0073] Correspondingly, the statistical information types include: information types corresponding to the production environment table information table, the production environment table space information table, the production environment index information table and the production environment field information table.

[0074] Specifically, the production environment table information table, the production environment table space information table, the production environment index information table, and the production environment field information table store all the information required by the optimizer of the production environment database to determine the access path of a statement.

[0075] After a lot of tests and experiments, we know that correctly modifying the application table statistics in these four tables can achieve the synchronization of statistical information without affecting the normal operation of the system.

[0076] Among them, the information types corresponding to the production environment table information table include: table data volume, the total number of data pages allocated to the table, the proportion of compressed records in the table to all records, the average record length, the total number of data pages used by the table, the physical size of the table, and the proportion of non-empty pages in the table.

[0077] The corresponding information types in the production environment tablespace information table include: the total number of active pages in the tablespace.

[0078] The corresponding information types in the production environment index information table include: the degree to which the index is arranged in sequence (when the value is 1, the index is completely arranged in sequence), the physical space size of the index, the number of elements in the first field of the index after deduplication, the number of records in all fields of the index after deduplication, the number of leaf nodes in the index, and the number of layers of the tree structure that makes up the index.

[0079] The information types corresponding to the field information table in the production environment include: the number of elements after deduplication of the field (database estimated value), maximum value, minimum value, and field type.

[0080] From the above description, it can be seen that the access path synchronization method provided in the embodiment of the present application has been found through a large number of tests and experiments that correctly modifying the statistical information in the production environment table information table, the production environment table space information table, the production environment index information table and the production environment field information table can achieve the synchronization of statistical information without affecting the normal operation of the system, thereby further improving the reliability of the access path synchronization process.

[0081] In order to further improve the application reliability and stability of the test environment, an embodiment of the access path synchronization method provided in this application is provided. Figure 5 , step 200 of the access path synchronization method specifically includes the following contents:

[0082] Step 210: triggering the optimizer corresponding to the target test environment whose current data volume and data distribution are the same as those of the production environment, so that the optimizer determines the database target access path of the target test environment.

[0083] Specifically, the database in the test environment will detect that the local data volume is consistent with the production environment, and the data distribution is also the same as the production environment. After completing the above actions, the full access path can be re-determined. At this time, the access path determined by the optimizer in the test environment is completely consistent with the access path automatically confirmed by the optimizer of the production environment database.

[0084] From the above description, it can be seen that the access path synchronization method provided in the embodiment of the present application triggers the optimizer corresponding to the target test environment whose current data volume and data distribution are the same as those of the production environment, so that the optimizer determines the database target access path of the target test environment. The method of automatically selecting the access path by the optimizer is more efficient than the method of forcibly determining the access path, which can further improve the application reliability and stability of the test environment, and can effectively improve the program efficiency and effectiveness of the test, so that the batch job execution plan in the test environment can be successfully completed.

[0085] In addition, when the optimizer selects an access path that it considers to be the best for the access statement based on the data distribution recorded in the statistical information, the optimizer's selection is not absolutely accurate. If the optimizer mistakenly selects a less efficient path, the operation and maintenance personnel will need to manually adjust it to the optimal path.

[0086] When the test environment restores the production environment data, the cost of restoring the full amount of production data is too high, so it is generally necessary to undergo desensitization and extraction. The extracted production data has significant differences in statistical information content such as data volume and data distribution from the production environment. As a result, the access path selected by the host database optimizer based on the test environment statistical information is often significantly different from that of the production environment, and cannot simulate the production scenario. Even the access path manually adjusted by the production environment operation and maintenance personnel will not take effect in the test environment.

[0087] Therefore, in order to automatically modify the manually adjusted access path, an embodiment of the access path synchronization method provided in the present application is provided. Figure 6 , the access path synchronization method further specifically includes the following contents after step 200:

[0088] Step 300: According to the database target access path, update the current database access path of the target test environment in a preset access path summary table, wherein the access path summary table is used to store the correspondence between the database access path of the production environment and the database access path of the target test environment.

[0089] Step 400: In the access path summary table, all manually adjusted access paths in the current database access path of the target test environment are modified to be the same as the corresponding database access path of the production environment.

[0090] It is understandable that the full access path of the production environment is synchronized to the temporary table of the test environment; the manually modified access path is extracted from the temporary table, and the corresponding access path of the test environment is found by program name and SQL sequence number; the access path summary table of the test environment is adjusted to be consistent with the production environment, and a command is issued to make the database re-determine the access path. Ensure that the access paths between the production and test environments are completely consistent.

[0091] From the above description, it can be seen that the access path synchronization method provided in the embodiment of the present application can automatically modify the manually adjusted access path, automatically compare the environment differences for the access path manually adjusted by the production operation and maintenance personnel, and synchronize the production access path to the test environment to maintain the consistency of the test environment and the production environment. Avoid efficiency problems when the test environment runs online or in batches, which affects the test progress.

[0092] In order to effectively identify the manually adjusted access paths in the access path summary table, an embodiment of the access path synchronization method provided in the present application is described in detail. Figure 7 , step 400 in the access path synchronization method specifically includes the following contents:

[0093] Step 410: Determine whether the access path summary table contains records in which the database access path of the production environment is different from the database access path of the target test environment. If so, execute step 420.

[0094] Step 420: Determine the database access path of the target test environment in the record as the manually adjusted access path.

[0095] Step 430: Modify the manually adjusted access path to be the same as the database access path of the production environment in the same record.

[0096] Specifically, after the optimizer in the test environment reconfirms the access path of the database, it will be saved in the access path summary table together with the access path selected by the optimizer in the production environment, and compared with the access path summary table in the test environment according to the program name and SQL sequence number. If they are consistent, they will be skipped. Otherwise, the difference in the access path summary table will be identified. Since the statistical information of the production environment database has been synchronized in the test environment, the access paths determined by the database optimizer in the test and production environments should be completely consistent. At this time, the other differences in the access path summary table are all access paths manually modulated by production operation and maintenance personnel. Therefore, the difference in the access path summary table can be identified by program name, SQL sequence number, etc.

[0097] Then, the access path of the test environment in the difference part of the access path summary table is changed to an access path consistent with the production environment, and a command is issued to make the optimizer reselect the access path to force the test environment database to use the access path of the production environment.

[0098] From the above description, it can be seen that the access path synchronization method provided in the embodiment of the present application can effectively identify the manually adjusted access path in the access path summary table by determining whether the access path summary table contains records that are different between the database access path of the production environment and the database access path of the target test environment, thereby effectively improving the efficiency and reliability of automatically modifying the manually adjusted access path.

[0099] From the software level, in order to solve the problem that there is currently no method to automatically synchronize the access path of the production environment database to the test environment, which makes it impossible for the test environment to simulate the generation scenario, thereby affecting the program efficiency and effectiveness of the test, and making it impossible for the batch job execution plan in the test environment to be successfully completed, the present application provides an embodiment of an access path synchronization device for executing all or part of the content of the access path synchronization method, see Figure 8 , the access path synchronization device specifically includes the following contents:

[0100] An environment simulation module 10 is used to import the statistical information corresponding to the database system table of the production environment into the target test environment which is currently in the state of restoring the production environment data, so that the target test environment is currently simulated to be an environment with the same data volume and data distribution as the production environment;

[0101] The path synchronization module 20 is used to determine the database target access path of the target test environment based on the target test environment whose data volume and data distribution are the same as those of the production environment, so that the database target access path is the same as the database access path predetermined by the optimizer of the production environment.

[0102] The access path synchronization device provided in the present application can be used to execute the processing flow of the access path synchronization method in the above embodiment. Its functions are not described in detail here, and reference can be made to the detailed description of the above method embodiment.

[0103] From the above description, it can be seen that the access path synchronization device provided in the embodiment of the present application can effectively simulate the production scenario corresponding to the production environment in the test environment by importing the statistical information corresponding to the database system table of the production environment into the target test environment that is currently in the state of restoring the production environment data, so that the database of the target test environment determines that the local data volume and data distribution are the same as those of the production environment, and then when the target test environment restores the production environment data, after its optimizer generates a database access path different from the production environment, it can trigger the optimizer of the target test environment again to re-determine the database target access path, so that the database target access path is synchronized with the database access path determined by the optimizer of the production environment, thereby effectively improving the application reliability and stability of the test environment, and can effectively improve the program efficiency and effectiveness of the test, so that the batch job execution plan in the test environment can be successfully completed, and the user experience of the operation and maintenance personnel can be effectively improved.

[0104] To further illustrate the present solution, the present application example provides an access path synchronization method, which relates to the field of computer technology, and specifically to the process of quickly synchronizing the host database access path to the test environment. Statistical information also exists in the form of system tables in host databases such as DB2. The present invention extracts the production environment statistical information during data recovery and tries to import it into the test environment. When the actual amount of data in the test environment table is small, the host database detects that the data volume and data distribution of the test environment after importing the production environment statistical information are consistent with the production environment, so as to achieve the purpose of enabling the optimizer to select the same access path as the production environment in the test environment. The overall process of host database synchronization can be found in Fig. 9 .

[0105] For the access paths manually adjusted by the production environment operation and maintenance personnel, they are implemented simultaneously in the test environment through analysis and re-implementation to ensure that the access paths of the test environment are consistent with the production environment and to simulate the business scenarios of the production environment as much as possible.

[0106] See also Fig.10 , the specific process of database statistics synchronization is as follows:

[0107] 1. Import the system table of the production environment database into the temporary table of the test environment through backup and recovery.

[0108] 2. Since system tables affect the normal operation of the production environment database, it is necessary to accurately identify all information that affects the access path and import it into the test environment.

[0109] 3. Ensure the consistency of statistical information between the production and test environments and then redefine the full access path.

[0110] The production environment table information table, the production environment table space information table, the production environment index information table and the production environment field information table store all the information required by the optimizer of the production environment database to determine the access path of a statement.

[0111] After a lot of tests and experiments, we know that correctly modifying the application table statistics in these four tables can achieve the synchronization of statistical information without affecting the normal operation of the system.

[0112] Among them, in the production environment table information table, the following information needs to be collected: table data volume, the total number of data pages allocated to the table, the proportion of compressed records in the table to all records, the average record length, the total number of data pages used by the table, the physical size of the table, and the proportion of non-empty pages in the table.

[0113] In the production environment tablespace information table, you need to collect: the total number of active pages in the tablespace.

[0114] In the production environment index information table, the following data must be collected: the degree to which the index is arranged in sequence (when this value is 1, the index is completely arranged in sequence), the physical space size of the index, the number of elements in the first field of the index after deduplication, the number of records in all fields of the index after deduplication, the number of leaf nodes in the index, and the number of layers of the tree structure that makes up the index.

[0115] In the production environment field information table, you need to collect: the number of elements in the field after deduplication (database estimated value), maximum value, minimum value, and field type.

[0116] Taking the application table BTBDFNJM update table information table as an example, the data volume and data distribution of the table BTBDFNJM in the test environment are quite different from those in the production environment. There are only 6 records of the test environment of the table BTBDFNJM in the system table, while there are more than 480,000 records in the production environment. At this time, if the SQL access path is selected, the optimizer in the test environment will think that the amount of data in the table BTBDFNJM is very small and tends to use the full table scan method to access it. The optimizer in the production environment will detect that the amount of data in the table BTBDFNJM is large, so it will not use the full table scan method to access it, which makes the access path of the test environment to the table BTBDFNJM inconsistent with the access path of the production environment to the table BTBDFNJM. It is difficult for the test to cover the actual access path of the production environment, and it will cause efficiency problems after the amount of data in the test environment changes.

[0117] In order to solve the above problems, this application queries the system table of the production environment and finds that the total number of records in the system table is 480468, the compression ratio is 0, the average record length is 382 bytes, the total number of used data pages in the system table of the production environment is 48232, the physical size of the system table of the production environment is 624960, and the proportion of non-empty pages is 38%. The above statistical information is updated to the system table of the test environment. After the statistical information synchronization is completed, the optimizer of the test environment will consider that the table information of the system table of the test environment is consistent with the system table in the production environment.

[0118] Import all the above-mentioned production environment statistics into the temporary table of the test environment, match the production environment table with the test environment table one by one through the database name, table name, owner and other information, and then generate update statements for each table in the test environment to accurately import the production environment statistics into the test environment. At this time, the database of the test environment will detect that the local data volume is consistent with that of the production environment, and the data distribution is also the same as that of the production environment. After completing the above actions, the full access path can be triggered to be re-determined. At this time, the access path re-determined by the optimizer of the test environment is completely consistent with the access path automatically confirmed by the optimizer of the production environment database.

[0119] See also Fig.11 ,The specific process of manually adjusted access path synchronization is as follows:

[0120] 1. Synchronize the full access path of the production environment to the temporary table of the test environment.

[0121] 2. Extract the manually modified access path from the temporary table, and find the access path corresponding to the test environment according to the program name and SQL sequence number.

[0122] 3. Adjust the access path summary table of the test environment to be consistent with the production environment, and issue a command to have the database redefine the access path. Ensure that the access paths between the production and test environments are completely consistent.

[0123] The specific implementation steps of manually adjusted access path synchronization are as follows:

[0124] S21: Get the access path summary table of the production environment and test environment:

[0125] After the optimizer in the test environment reconfirms the access path of the database, it is saved in the access path summary table together with the access path selected by the optimizer in the production environment.

[0126] S22: Compare the program name and SQL sequence number with the test environment access path summary table. If they are consistent, skip. Otherwise, identify the difference in the access path summary table and execute S23.

[0127] Since the statistical information of the production environment database has been synchronized in the test environment, the access paths determined by the database optimizer in the test and production environments should be completely consistent. At this time, the other differences in the access path summary table are all access paths manually adjusted by the production operation and maintenance personnel. Therefore, the differences in the access path summary table can be identified by program name, SQL sequence number, etc.

[0128] S23: Use the production environment access path to cover the test environment:

[0129] The access path of the test environment in the difference part of the access path summary table is changed to an access path consistent with the production environment.

[0130] S24: Issue a command to enable the optimizer to reselect the access path to force the test environment database to use the access path of the production environment.

[0131] Now take an access path of program PAHTRU5 as an example:

[0132] When the production environment database optimizer selects the access path of one of the SQLs in the host program PAHTRU5, the table has a large amount of data, but only one SQL is selected. After analysis, the optimizer selects the access path of full table scan.

[0133] However, due to the large amount of data in the table, even though the SQL only selects one, the scanning efficiency is low. Therefore, the production operation and maintenance personnel manually adjusted the access path of the SQL to match the index. After synchronizing the statistical information in the test environment, the access path selected by the optimizer is still different from that in the production environment.

[0134] The application example of this application compares the production and test environments in the access path record table of the database based on the program name and the unique number of the SQL. After synchronizing the statistical information, it is found that the access path of the test environment program is still different from that of the production environment. At this time, the specific access path of the production environment is overwritten in the access path table to the test environment table, and then a command is issued to force the optimizer to select the correct access path.

[0135] The access path synchronization method provided by the application example of the present application keeps the access path selected by the database optimizer consistent with the production environment by loading the statistical information of the production environment database into the test environment, so that the access path synchronization method provided by the application example of the present application has the following advantages:

[0136] 1. If the statements in the test environment are changed or added, the access path of the statements in the production environment can also be predicted and simulated.

[0137] 2. Using the database optimizer to automatically select the access path is much more efficient than forcibly determining the access path.

[0138] 3. After the statistical information of the production environment is synchronized to the test environment, it helps to analyze access path problems.

[0139] For access paths manually adjusted by production operation and maintenance personnel, the environment differences are automatically compared and the access paths of production are synchronized to the test environment to maintain the consistency between the test environment and the production environment. This avoids efficiency problems when the test environment is running online or in batches, which affects the test progress.

[0140] From the hardware level, in order to solve the problem that there is currently no method for automatically synchronizing the access path of the production environment database to the test environment, which makes it impossible for the test environment to simulate the generation scenario, thereby affecting the program efficiency and effectiveness of the test, and making it impossible for the batch job execution plan in the test environment to be successfully completed, the present application provides an embodiment of an electronic device for implementing all or part of the content of the access path synchronization method, and the electronic device specifically includes the following content:

[0141] Fig.12 FIG. 9 is a schematic block diagram of the system structure of the electronic device 9600 according to an embodiment of the present application. Fig.12 As shown, the electronic device 9600 may include a central processor 9100 and a memory 9140; the memory 9140 is coupled to the central processor 9100. It is worth noting that Fig.12 is exemplary; other types of structures may also be used to supplement or replace this structure to implement telecommunication functions or other functions.

[0142] In one embodiment, the access path synchronization function may be integrated into a central processing unit, wherein the central processing unit may be configured to perform the following control:

[0143] Step 100: Import the statistical information corresponding to the database system table of the production environment into the target test environment which is currently in the state of restoring the production environment data, so that the target test environment is currently simulated as an environment with the same data volume and data distribution as the production environment.

[0144] Step 200: Based on the target test environment having the same data volume and data distribution as the production environment, determine the database target access path of the target test environment so that the database target access path is the same as the database access path predetermined by the optimizer of the production environment.

[0145] From the above description, it can be seen that the electronic device provided by the embodiment of the present application can effectively simulate the production scenario corresponding to the production environment in the test environment by importing the statistical information corresponding to the database system table of the production environment into the target test environment that is currently in the state of restoring the production environment data, so that the database of the target test environment determines that the local data volume and data distribution are the same as those of the production environment, and then when the target test environment restores the production environment data, after its optimizer generates a database access path different from the production environment, it can trigger the optimizer of the target test environment again to re-determine the database target access path, so that the database target access path is synchronized with the database access path determined by the optimizer of the production environment, thereby effectively improving the application reliability and stability of the test environment, and can effectively improve the program efficiency and effectiveness of the test, so that the batch job execution plan in the test environment can be successfully completed, and the user experience of the operation and maintenance personnel can be effectively improved.

[0146] In another embodiment, the access path synchronization device may be configured separately from the central processor 9100. For example, the access path synchronization device may be configured as a chip connected to the central processor 9100, and the access path synchronization function may be implemented under the control of the central processor.

[0147] like Fig.12 As shown, the electronic device 9600 may also include: a communication module 9110, an input unit 9120, an audio processor 9130, a display 9160, and a power supply 9170. It is worth noting that the electronic device 9600 does not necessarily have to include Fig.12 In addition, the electronic device 9600 may also include Fig.12 For components not shown, reference may be made to the prior art.

[0148] like Fig.12 As shown, the central processing unit 9100 is sometimes also referred to as a controller or an operation control, and may include a microprocessor or other processor device and / or logic device. The central processing unit 9100 receives input and controls the operation of various components of the electronic device 9600.

[0149] The memory 9140 may be, for example, one or more of a cache, a flash memory, a hard drive, a removable medium, a volatile memory, a non-volatile memory or other suitable devices. The above-mentioned information related to the failure may be stored, and a program for executing the relevant information may also be stored. The CPU 9100 may execute the program stored in the memory 9140 to implement information storage or processing.

[0150] The input unit 9120 provides input to the central processing unit 9100. The input unit 9120 is, for example, a key or a touch input device. The power supply 9170 is used to provide power to the electronic device 9600. The display 9160 is used to display display objects such as images and texts. The display may be, for example, an LCD display, but is not limited thereto.

[0151] The memory 9140 may be a solid-state memory, such as a read-only memory (ROM), a random access memory (RAM), a SIM card, etc. It may also be a memory that saves information even when the power is off, can be selectively erased, and is provided with more data, examples of which are sometimes referred to as EPROMs, etc. The memory 9140 may also be some other type of device. The memory 9140 includes a buffer memory 9141 (sometimes referred to as a buffer). The memory 9140 may include an application / function storage unit 9142, which is used to store application programs and function programs or processes for executing the operation of the electronic device 9600 through the central processor 9100.

[0152] The memory 9140 may also include a data storage unit 9143 for storing data, such as contacts, digital data, pictures, sounds, and / or any other data used by the electronic device. The driver storage unit 9144 of the memory 9140 may include various drivers for communication functions of the electronic device and / or for executing other functions of the electronic device (such as messaging applications, address book applications, etc.).

[0153] The communication module 9110 is a transmitter / receiver 9110 that sends and receives signals via an antenna 9111. The communication module (transmitter / receiver) 9110 is coupled to the central processor 9100 to provide input signals and receive output signals, which may be the same as the case of a conventional mobile communication terminal.

[0154] Based on different communication technologies, multiple communication modules 9110 may be provided in the same electronic device, such as a cellular network module, a Bluetooth module and / or a wireless LAN module, etc. The communication module (transmitter / receiver) 9110 is also coupled to a speaker 9131 and a microphone 9132 via an audio processor 9130 to provide an audio output via the speaker 9131 and receive an audio input from the microphone 9132, thereby realizing a common telecommunication function. The audio processor 9130 may include any suitable buffer, decoder, amplifier, etc. In addition, the audio processor 9130 is also coupled to the central processor 9100, so that recording can be performed on the local machine through the microphone 9132, and the sound stored on the local machine can be played through the speaker 9131.

[0155] The embodiment of the present application also provides a computer-readable storage medium capable of implementing all the steps in the access path synchronization method in the above embodiment. The computer-readable storage medium stores a computer program. When the computer program is executed by a processor, all the steps of the access path synchronization method in the above embodiment are implemented by the execution subject being a server or a client. For example, when the processor executes the computer program, the following steps are implemented:

[0156] Step 100: Import the statistical information corresponding to the database system table of the production environment into the target test environment which is currently in the state of restoring the production environment data, so that the target test environment is currently simulated as an environment with the same data volume and data distribution as the production environment.

[0157] Step 200: Based on the target test environment having the same data volume and data distribution as the production environment, determine the database target access path of the target test environment so that the database target access path is the same as the database access path predetermined by the optimizer of the production environment.

[0158] From the above description, it can be seen that the computer-readable storage medium provided in the embodiment of the present application can effectively simulate the production scenario corresponding to the production environment in the test environment by importing the statistical information corresponding to the database system table of the production environment into the target test environment that is currently in the state of restoring the production environment data, so that the database of the target test environment determines that the local data volume and data distribution are the same as those of the production environment, and then when the target test environment restores the production environment data, after its optimizer generates a database access path different from the production environment, it can trigger the optimizer of the target test environment again to re-determine the database target access path, so that the database target access path is synchronized with the database access path determined by the optimizer of the production environment, thereby effectively improving the application reliability and stability of the test environment, and can effectively improve the program efficiency and effectiveness of the test, so that the batch job execution plan in the test environment can be successfully completed, and the user experience of the operation and maintenance personnel can be effectively improved.

[0159] It will be appreciated by those skilled in the art that embodiments of the present invention may be provided as methods, devices, or computer program products. Therefore, the present invention may take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Moreover, the present invention may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0160] The present invention is described with reference to flowcharts and / or block diagrams of methods, devices (apparatus), and computer program products according to embodiments of the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0161] These computer program instructions may also be stored in a computer readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer readable memory produce an article of manufacture including an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0162] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process in the computer or other programmable device. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0163] The present invention uses specific embodiments to illustrate the principles and implementation methods of the present invention. The description of the above embodiments is only used to help understand the method of the present invention and its core idea. At the same time, for those skilled in the art, according to the idea of ​​the present invention, there will be changes in the specific implementation methods and application scope. In summary, the content of this specification should not be understood as a limitation on the present invention.

Claims

1. A method for synchronizing an access path, It is characterized in that include: Importing statistical information corresponding to the database system table of the production environment into the target test environment currently in the state of restoring the production environment data, so that the target test environment is currently simulated as an environment with the same data volume and data distribution as the production environment; Determine, based on the target test environment having the same data volume and data distribution as the production environment, a database target access path of the target test environment, so that the database target access path is the same as a database access path predetermined by an optimizer of the production environment; The importing of the statistical information corresponding to the database system table of the production environment into the target test environment which is currently in the state of restoring the data of the production environment comprises: importing the statistical information into the target test environment which is currently in the state of restoring the data of the production environment; The importing of the statistical information into the target test environment currently in the state of restoring the production environment data includes: importing the statistical information into a temporary table corresponding to the target test environment currently in the state of restoring the production environment data, and making data correspondence between the temporary table and the database system table of the production environment; generating a corresponding update statement for the temporary table to import the statistical information into the target test environment; The importing of the statistical information into a temporary table corresponding to the target test environment currently in the state of restoring production environment data, and making data correspondence between the temporary table and the database system table of the production environment, includes: importing all the statistical information of the production environment into the temporary table of the test environment, and making one-to-one correspondence between the production environment table and the test environment table through database name, table name, and owner information; Generating a corresponding update statement for the temporary table to import the statistical information into the target test environment includes: generating an update statement for each table in the test environment to accurately import the statistical information of the production environment into the test environment; The step of determining a target database access path of the target test environment based on the target test environment having the same data volume and data distribution as the production environment includes: triggering an optimizer corresponding to the target test environment having the same current data volume and data distribution as the production environment, so that the optimizer determines the target database access path of the target test environment; The triggering of the optimizer corresponding to the target test environment whose current data volume and data distribution are the same as those of the production environment, so that the optimizer determines the target access path of the database of the target test environment, includes: the database of the test environment detects and learns that the local data volume is consistent with the production environment, and the data distribution is also the same as the production environment; triggering the redetermination of the full access path, so that the access path re-determined by the optimizer of the test environment is completely consistent with the access path automatically confirmed by the optimizer of the production environment database.

2. The access path synchronization method according to claim 1, It is characterized in that The importing of statistical information corresponding to the database system table of the production environment into the target test environment which is currently in the state of restoring the production environment data includes: Based on a preset statistical information type for determining a database access path, corresponding statistical information is extracted from a database system table of the production environment.

3. The access path synchronization method according to claim 2, It is characterized in that The database system tables of the production environment include: Production environment table information table, production environment table space information table, production environment index information table and production environment field information table; Correspondingly, the statistical information types include: information types corresponding to the production environment table information table, the production environment table space information table, the production environment index information table and the production environment field information table.

4. The access path synchronization method according to any one of claims 1 to 3, It is characterized in that After determining the target access path of the database of the target test environment based on the target test environment having the same data volume and data distribution as the production environment, the method further includes: According to the database target access path, updating the current database access path of the target test environment in a preset access path summary table, wherein the access path summary table is used to store the correspondence between the database access path of the production environment and the database access path of the target test environment; In the access path summary table, the manually adjusted access paths in the database access paths of the current target test environment are all modified to be the same as the corresponding database access paths of the production environment.

5. The access path synchronization method according to claim 4, It is characterized in that In the access path summary table, all manually adjusted access paths in the database access path of the current target test environment are modified to be the same as the corresponding database access path of the production environment, including: Determine whether the access path summary table contains a record in which the database access path of the production environment is different from the database access path of the target test environment; if so, determine the database access path of the target test environment in the record as a manually adjusted access path; The manually adjusted access path is modified to be the same as the database access path of the production environment in the same record.

6. An access path synchronization device, It is characterized in that include: An environment simulation module is used to import the statistical information corresponding to the database system table of the production environment into the target test environment which is currently in the state of restoring the production environment data, so that the target test environment is currently simulated to be an environment with the same data volume and data distribution as the production environment; The environment simulation module is further used to import the statistical information into a target test environment that is currently in a state of restoring production environment data; The environment simulation module is further used to import the statistical information into a temporary table corresponding to the target test environment that is currently in the state of restoring production environment data, and to make data correspondence between the temporary table and the database system table of the production environment; to generate a corresponding update statement for the temporary table to import the statistical information into the target test environment; The environment simulation module is also used to import all the statistical information of the production environment mentioned above into the temporary table of the test environment, and to correspond the production environment table to the test environment table one by one through the database name, table name, and owner information; and to generate an update statement for each table of the test environment to accurately import the statistical information of the production environment into the test environment; A path synchronization module, configured to determine a target database access path of the target test environment based on the target test environment having the same data volume and data distribution as the production environment, so that the target database access path is the same as the database access path pre-determined by the optimizer of the production environment; The path synchronization module is also used in the test environment database to detect that the local data volume is consistent with the production environment, and the data distribution is also the same as the production environment; trigger the redetermination of the full access path so that the access path re-determined by the optimizer of the test environment is completely consistent with the access path automatically confirmed by the optimizer of the production environment database.

7. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, It is characterized in that When the processor executes the computer program, the access path synchronization method according to any one of claims 1 to 5 is implemented.

8. A computer-readable storage medium having a computer program stored thereon, It is characterized in that When the computer program is executed by a processor, the access path synchronization method described in any one of claims 1 to 5 is implemented.

9. A computer program product, It is characterized in that The computer program product comprises a computer program, and when the computer program is executed by a processor, the method according to any one of claims 1 to 5 is implemented.

Images