System table management method, device and equipment of relational database management system

Abstract

The embodiment of the invention provides a system table management method, device and equipment for a relational database management system, and the method comprises the steps: receiving a first selection operation inputted by a user, and obtaining each candidate index meeting a quick index access starting condition; starting database playback, and determining the synchronous read reduction quantity in unit time after the quick index access function is started by each candidate index; determining the index candidate weight of each candidate index according to the synchronous read reduction quantity in unit time after the quick index access function is started by each candidate index and the corresponding memory space; according to each index candidate weight, determining target indexes to form an index list, and determining corresponding function start time and function start end time; and receiving a date setting operation input by a user, and setting a starting date of the index list. Automatic execution of system table management is realized, the learning cost of a user is reduced, complex manual calculation steps and workload are avoided, and the working efficiency is improved.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a system table management method, device and equipment for a relational database management system. Background Art

[0002] The host relational database management system (DB2) provides a Fast Index Travel (FIT) feature for indexes. This means that in the host DB2 system table (SYSINDEXCONTROL), you can set which indexes have FIT enabled, as well as the start and end time periods for enabling it. By enabling FIT for indexes, all non-leaf page data for indexes eligible for FIT can be loaded into memory, reducing unnecessary synchronous reads and improving access speed. Furthermore, enabling FIT for indexes does not require adjustments to the application system table structure or indexes, nor does it require rewriting Structured Query Language (SQL) statements or program code, resulting in a lower risk.

[0003] Business systems running on a host DB2 typically have a large number of tables, often reaching several thousand. Therefore, there are also many indexes eligible for FIT. Each index occupies a different amount of space, and each table has different peak access times. Limited by the memory size allocated by the host DB2 for FIT, not all eligible indexes can be FIT-enabled. Therefore, deciding which indexes can have FIT enabled, as well as the start and end times for FIT, is a challenge for operators.

[0004] The current method of enabling the FIT function has the following problems: high learning cost for users, security risks, difficulty in manually screening candidate indexes, difficulty in determining the final index list, error-prone manual setting of SYSINDEXCONTROL table data, and inability to set multiple peak access periods for an index. Summary of the Invention

[0005] The embodiments of the present invention provide a system table management method, apparatus and device for a relational database management system, which realizes the automatic execution of system table management, reduces the learning cost of users, avoids complex manual calculation steps and workload, and improves work efficiency.

[0006] In a first aspect, this embodiment provides a system table management method for a relational database management system, the method comprising:

[0007] Receiving a first selection operation input by a user, obtaining candidate indexes that meet a fast index access enabling condition from a database of a host relational database management system, wherein the first selection operation is selecting an index list recommendation function to enable a fast index access function in a presented system table management interface;

[0008] Enable database replay to determine the number of synchronous reads reduced per unit time after the fast index access function is enabled for each candidate index;

[0009] Determining the index candidate weight of each candidate index according to the number of synchronous reads reduced per unit time after the fast index access function is enabled for each candidate index and the memory space of each candidate index when the fast index access function is enabled;

[0010] According to the weight of each index candidate, determine the target index for enabling the fast index access function from each candidate index to form an index list, and determine the function enabling start time and function enabling end time of each target index, and present them in the system table management interface;

[0011] A date setting operation input by a user is received, and an activation date of the index list is set. The date setting operation is the setting of the activation date of the index list.

[0012] In a second aspect, this embodiment provides a system table management device for a relational database management system, the device comprising:

[0013] An index acquisition module, configured to receive a first selection operation input by a user and acquire candidate indexes that meet a fast index access enabling condition from a database of a host relational database management system, wherein the first selection operation is selecting an index list recommendation function to enable the fast index access function in a presented system table management interface;

[0014] A quantity determination module is used to enable database replay and determine the number of synchronous reads reduced per unit time after the fast index access function is enabled for each candidate index;

[0015] A weight determination module, configured to determine an index candidate weight of each candidate index based on a reduced number of synchronous reads per unit time after each candidate index enables a fast index access function, and a memory space when each candidate index enables a fast index access function;

[0016] An index recommendation module is used to determine, based on the weight of each index candidate, a target index for enabling the fast index access function from each candidate index to form an index list, and to determine the function enabling start time and function enabling end time of each target index, and present the result in the system table management interface;

[0017] The date setting module is used to receive a date setting operation input by a user and set the activation date of the index list. The date setting operation is to set the activation date of the index list.

[0018] In a third aspect, this embodiment provides an electronic device, including:

[0019] at least one processor; and

[0020] a memory communicatively connected to the at least one processor; wherein,

[0021] The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute the system table management method of the relational database management system described in any embodiment of the present invention.

[0022] An embodiment of the present invention provides a system table management method, apparatus, and device for a relational database management system, the method comprising: receiving a first selection operation input by a user, obtaining candidate indexes that meet the fast index access enabling conditions from a database of a host relational database management system, wherein the first selection operation is to select an index list recommendation function for enabling the fast index access function in a presented system table management interface; enabling database playback, determining the number of synchronous reads reduced per unit time after each candidate index enables the fast index access function; determining an index candidate weight of each candidate index based on the number of synchronous reads reduced per unit time after each candidate index enables the fast index access function, and the memory space for each candidate index enabling the fast index access function; determining a target index for enabling the fast index access function from each candidate index based on each index candidate weight to form an index list, and determining a function enabling start time and function enabling end time for each target index, and presenting the list in the system table management interface; receiving a date setting operation input by the user, setting an enabling date for the index list, wherein the date setting operation is to set the enabling date for the index list. The above technical solution shields users from the complex details of host operation commands. Users do not need to learn and master the commands of the host operating system and host DB2. They only need to be able to use common operating systems, which reduces the learning cost of users. Since this execution subject communicates with the host DB2 through application program interface calls, there is no need to log in to the management interface of the host DB2, avoiding security risks caused by misoperation. In addition, this execution subject can automatically obtain the index list that meets the requirements for enabling the FIT function based on the database metadata information and calculate the size of the memory space occupied by the index. It can also efficiently obtain the index list information and activation start and end time for enabling the FIT function based on the database access playback information, avoiding complex manual calculation steps and calculation workload, and improving work efficiency. The ability to automatically execute related operations reduces the workload of users.

[0023] It should be understood that the content described in this section is not intended to identify the key or important features of the embodiments of the present invention, nor is it intended to limit the scope of the present invention. Other features of the present invention will become readily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

[0024] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0025] Figure 1Provides quick access to FIT's technical schematics;

[0026] Figure 2 A schematic diagram of a process for managing a system table of a relational database management system provided in the first embodiment of the present invention;

[0027] Figure 3 A flowchart of another system table management method for a relational database management system provided in the second embodiment of the present invention;

[0028] Figure 4 A schematic diagram of the structure of a system table management device for a relational database management system provided in a third embodiment of the present invention;

[0029] Figure 5 This is a structural diagram of an electronic device provided in Example 4 of the present invention. DETAILED DESCRIPTION

[0030] In order to enable those skilled in the art to better understand the solutions of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the embodiments described are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts should fall within the scope of protection of the present invention.

[0031] It should be noted that the terms "first", "second", etc. in the description and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that the numbers used in this way can be interchanged where appropriate, so that the embodiments of the present invention described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions. For example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0032] To more clearly describe the embodiments of the present invention, some terms are explained below. Host DB2: This is DB2 for z / os, a relational database management system developed by IBM. Buffer Pool: This is the Chinese name for the term BufferPool in DB2. Its function is to temporarily store database table data or index data in memory. GETPAGE: A request to obtain data page information. When index data needs to be accessed, the database sends a request to obtain data page information to the buffer pool where the index is located. RANDOM GETPAGE: The number of random requests. This parameter value is the number of random access requests recorded by the database buffer pool. SyncRead: Synchronous read. When the data requested by the SQL statement is not in the buffer pool, the database needs to read the relevant data from the disk into the buffer pool. At this time, a synchronous read occurs. Database access recording: This is a function of the host DB2 that records all requests to access the database within a specified time period. Database access playback: This is a function of the host DB2 that, as needed, sends a pre-prepared database access recording of access requests to the host DB2 in the recorded order and time interval, simulating the database access status during the database access recording. This function is mainly used for database tuning.

[0033] The SYSINDEXCONTROL table is a system table on the DB2 host that primarily stores information about indexes with FIT enabled. This table specifies whether FIT is enabled for an index and the time window within which it is enabled or disabled, with start and end times accurate to the minute. FIT stands for FastIndex Travel, a fast index access feature provided by the DB2 database. This feature improves random access performance by storing data in non-leaf nodes of an index in memory. When data is needed, the index is cached in memory through fast lookups, reducing the number of synchronous reads. M stands for megabyte, a unit of size in calculations, equal to 1,048,576 bytes. Page replacement algorithm: Databases typically allocate a buffer for each table and index to cache data. When the buffer is reached, data in the buffer is replaced using a specific algorithm, known as the page replacement algorithm. Index FIT memory size: The memory size used to load all non-leaf pages of an index into memory when FIT is enabled. Index access step value: An integer value, such as 1000. The integer portion of the quotient of the number of index accesses per unit time divided by this value is used as the index access frequency level. This is used to calculate whether the FIT function is enabled for the index. Memory size step value: An integer value in megabytes (M), for example, 1 represents 1M bytes. This is used to classify the space occupied by indexes.

[0034] For example, Figure 1For quick index access to FIT's technical schematics, such as Figure 1 The table shown has a unique index with five levels. Since index access is mostly random in most cases, accessing the table through this index starts at the root node. Since access to nodes in the index tree is synchronous, accessing a single page of table data in the diagram below requires six synchronous reads (five synchronous reads of the index tree node + one synchronous read of the table data page). However, if FIT is enabled for this index, the database reads all index pages for non-leaf nodes into a buffer. This means that all index pages from levels 1 through 4 are read into the buffer, including the root node R, nodes N1, N2, and N3 in the diagram. When accessing table data pages, the non-leaf node information can be found in the buffer. Only the corresponding index leaf page and the corresponding table data page need to be read to retrieve the required data. This reduces the number of synchronous reads to two (one synchronous read of the index leaf page + one synchronous read of the table data page). This theoretically reduces the time required to access the table data page to one-third, improving access performance.

[0035] The following describes existing technologies. After a system developed based on a mainframe DB2 goes into production, as business grows and data volumes increase, the corresponding index data also increases. In highly concurrent access scenarios, if index page data is not in the buffer, frequent synchronous reads of index data will be triggered, significantly degrading access performance for some tables. In severe cases, this can even impact normal business operations. Therefore, it is necessary to optimize access methods for these tables.

[0036] Typically, a database accesses a table's data by searching an index of the table based on the query conditions for the physical address of the data in the table's data area that meets the query conditions. The database then accesses the table's data page corresponding to this physical address and loads it into a buffer to complete the access. When accessing an index, the database starts at the index's root node, and then determines which second-level node page data to access based on the root node page data. The processing logic for subsequent third-level node pages and index leaf pages is the same as that for the index's root node page. If the node page data being accessed is in the buffer, it is retrieved directly from the buffer. If it is not in the buffer, a synchronous read is performed to read the node page data from the disk where the index is stored into the buffer.

[0037] Because buffered data is in memory, a costly resource, the memory space allocated to each table is limited. Specifically, the available memory space for a specific index within a table is even smaller. When the table's memory usage limit is reached, the database replaces the data and index pages in the buffer using the page replacement algorithm configured for the buffer. In the case of high-volume random access to a table, an index page might be immediately loaded back into the buffer after being replaced, especially for data in non-leaf index nodes. This can generate a large number of unnecessary synchronous read requests, degrading performance of accessing the table.

[0038] To improve the overall performance of the database, various methods can be used, such as optimizing table and index design, rewriting SQL statements, and modifying program logic code. However, these methods usually require adjustments to the program code, which is risky and requires application developers to have high skills. In addition, the program must undergo long-term functional and performance testing, which may not meet the business requirements for improving system performance as quickly as possible.

[0039] The host DB2 provides the FIT function for indexes, which means that in the SYSINDEXCONTROL table of the host DB2, you can set which indexes have the FIT function enabled, as well as the start and end time periods for the enablement. By enabling the FIT function for the index, all non-leaf page data of the index that meets the requirements for enabling the FIT function can be loaded into memory to reduce unnecessary synchronous reads and improve access speed. Moreover, enabling the FIT function for the index does not require adjustments to the application system table structure and indexes, nor does it require rewriting SQL statements and program code, which reduces the risk. However, indexes that can enable the FIT function must also meet the following three constraints: the index must be a unique index; the sum of the index key value lengths does not exceed 64 bytes; and the index has fewer than 2 million leaf page nodes.

[0040] Access to a table has peak and off-peak periods, and some tables may have multiple peak access periods in a single day. Because an index can only have one record in the SYSINDEXCONTROL table, for indexes on tables with multiple peak access periods, either set a larger period to encompass these multiple peak periods or adjust the index's start and end period fields to the data for the next period after a peak period. Because host buffer resources are expensive, if an index with FIT enabled remains in the buffer pool during the table's off-peak period, its index page data will be wasted, affecting the access performance of other tables.

[0041] Business systems running on a host DB2 typically have a large number of tables, often reaching several thousand. Therefore, a large number of indexes are eligible for FIT, and each index occupies a different amount of space. Furthermore, the peak access period for each table is also different. Furthermore, due to the limited memory size allocated by the host DB2 for enabling FIT, not all eligible indexes can be enabled with FIT. Therefore, deciding which indexes can have FIT enabled, as well as the start and end time for enabling FIT, is a challenge for operators.

[0042] Specifically, there are the following problems:

[0043] 1) High learning cost for users. The host operating system's control interface is a character interface, and its operating habits are quite different from those of the Windows operating system. Users need a long time to learn the basic operations of the host, which is very costly.

[0044] 2) Security Risk: The SYSINDEXCONTROL table is a system table of the host DB2. Updating data in this table requires high permissions, usually those of the database administrator. Traditionally, accessing the host DB2 management interface is required to update the table, which poses a security risk.

[0045] 3) Manually screening candidate indexes is difficult. There are a large number of indexes eligible for FIT, and due to the limited memory available for FIT, it's impossible to enable FIT for all indexes that meet the above criteria. Therefore, it's necessary to obtain index access information from database statistics to calculate the number of times these indexes are accessed per unit time (e.g., every 1 minute or 5 minutes, configurable), thereby determining the time periods of frequent access. Due to the large number of indexes, the computational workload is enormous, requiring a high level of technical expertise and making the process difficult. Furthermore, manually searching for relevant data from statistical information is prone to errors, leading to deviations in the final calculation.

[0046] 4) Determining the final index list is difficult. After obtaining the list of frequently accessed indexes and their frequently accessed periods from statistical information, since each index has a different frequently accessed period, it is necessary to ensure that the total memory space occupied by the FIT-enabled indexes in the SYSINDEXCONTROL data during any period does not exceed the memory allocated by the host DB2 for FIT. This list also changes over time, making it difficult to determine the final index list.

[0047] 5) Manually setting data in the SYSINDEXCONTROL table is prone to errors. After the technician manually determines the index to enable the FIT function, they need to write and execute the corresponding SQL statements to set the data in the SYSINDEXCONTROL table. Since manual SQL statements require many entries, errors are prone to occur.

[0048] 6) Multiple access peak periods cannot be set for an index. Some businesses have multiple peak access periods in a day, but since an index can only have one record in the SYSINDEXCONTROL table, for indexes of tables with multiple peak access periods, either set a period to include multiple periods. If so, since the host's buffer resources are expensive resources, if the index with the FIT function enabled is in the low access period of the table, the data of its index pages will always be in the buffer pool, resulting in a waste of expensive resources and affecting the access performance of other tables. Alternatively, after a peak period, adjust the index's enable and end period field information to the data of the next period. Since there are multiple tables with multiple peak access periods in a system, this places high demands on the personnel who update the SYSINDEXCONTROL table data, especially when the peak access period is during non-working hours.

[0049] 7) You cannot set a list of indexes to enable the FIT function for specific dates. During Chinese lunar holidays, national holidays, and certain dates (such as May 20, June 18, and November 11), peak business hours may differ from those of ordinary days. Currently, you cannot set a list of indexes to enable the FIT function and set the start and end times for each index for these specific dates.

[0050] Therefore, a method is needed to solve the above problems.

[0051] Example 1

[0052] Figure 2 This is a flow chart of a system table management method for a relational database management system provided in a first embodiment of the present invention. The method is applicable to situations where system tables of a relational database management system are managed. The method can be executed by a system table management device for a relational database management system. The system table management device for the relational database management system can be implemented in the form of hardware and / or software and is generally integrated into an electronic device.

[0053] like Figure 2 As shown, the system table management method of a relational database management system provided in the first embodiment may specifically include the following steps:

[0054] S101: Receive a first selection operation input by a user, and obtain candidate indexes that meet a fast index access enabling condition from a database of a host relational database management system.

[0055] The first selection operation is to select an index list recommendation function for enabling a quick index access function in the presented system table management interface.

[0056] It should be noted that the system table management method of the relational database management system provided in this embodiment can be integrated into an electronic device, which can establish a connection with the host relational database management system (DB2) and provide a function for recommending an index list for enabling the FIT function, a function for setting an index list for enabling the FIT function, a function for automatically updating the SYSINDEXCONTROL table information, etc. The above functions can be presented to the user through a human-computer interaction interface to support the user to implement corresponding functions by operating the human-computer interaction interface. The human-computer interaction interface is recorded as a system table management interface. The electronic device can be regarded as an auxiliary tool for assisting the management of the host DB2 system table, such as enabling the setting of the FIT function. Its underlying logic and the presented human-computer interaction interface are pre-developed and designed by technical personnel based on actual needs.

[0057] For example, three areas can be pre-set in the system table management interface, each corresponding to a function. When a user operates in the area where the FIT function index list recommendation function is enabled, the FIT function index list recommendation function can be enabled. When a user operates in the area where the FIT function index list setting function is enabled, the FIT function index list setting function can be enabled. When a user operates in the area where the SYSINDEXCONTROL table information automatic update function is located, the SYSINDEXCONTROL table information automatic update function can be implemented.

[0058] In this embodiment, the conditions for enabling fast index access include: the index is a unique index; the sum of the index key value lengths does not exceed the set length, for example, the set length can be 64 bytes, and no specific restrictions are made here; the index's leaf page nodes are less than the set number, for example, the set number can be 2 million.

[0059] It is understandable that when the user inputs the first selection operation, that is, the index list recommendation function for enabling the FIT function is selected, it is necessary to determine the index list that can enable the FIT function through underlying logic processing and recommend it to the user. Specifically, when the user inputs the first selection operation, metadata is obtained from the database of the host relational database management system (DB2). The metadata records relevant information about each index, such as whether the index is unique, the length of the index key value, and the leaf nodes of the index. Based on this, indexes that meet the conditions for enabling the FIT function can be obtained from each index and recorded as candidate indexes.

[0060] S102: Enable database replay to determine the reduction in the number of synchronous reads per unit time after the fast index access function is enabled for each candidate index.

[0061] In this embodiment, after obtaining a list of candidate indexes, the memory space occupied by each candidate index with the FIT function enabled is calculated, and the names of the buffer pools corresponding to these candidate indexes are obtained. A host DB2 command is invoked to enable data access playback, and a workload is run. Buffer pool statistics, including the number of random requests and synchronous reads, are read from the buffer pools corresponding to these candidate indexes per unit time. The random request and synchronous read counts per unit time for each index are then calculated and stored in memory as the random request and synchronous read counts for each index when the FIT function is not enabled.

[0062] Continuing with the above description, based on the available memory space when the host DB2 has the FIT function enabled and the memory space occupied by each index when the FIT function is enabled, a simulation is performed to determine a batch of candidate indexes for which the FIT function is enabled. These candidate indexes are then recorded as a group of candidate indexes. Database revisits are enabled to obtain the number of synchronous reads per unit time after the FIT function is enabled for each candidate index. The number of synchronous reads per unit time for the candidate index without the FIT function enabled is subtracted from the number of synchronous reads per unit time after the FIT function is enabled for the candidate index to determine the reduction in the number of synchronous reads per unit time after the fast index access function is enabled for the candidate index. It is necessary to ensure that all candidate indexes receive the reduction in the number of synchronous reads after the FIT function is enabled.

[0063] S103 : Determine the index candidate weight of each candidate index according to the number of synchronous reads reduced per unit time after the fast index access function is enabled for each candidate index and the memory space of each candidate index when the fast index access function is enabled.

[0064] The index candidate weight is calculated by dividing the number of simultaneous access reads per unit time by the memory size of the index with the FIT function enabled. In this embodiment, the index candidate weight of each candidate index per unit time is calculated as follows: Index Candidate Weight = Number of Reduced Simultaneous Access Reads per Unit Time / Memory Size with the FIT Function Enabled. For each candidate index, the number of simultaneous access reads reduced per unit time is divided by the corresponding memory size with the FIT function enabled. The result is used as the index candidate weight of the candidate index.

[0065] S104: According to the weight of each index candidate, determine the target index for enabling the fast index access function from each candidate index to form an index list, and determine the function enabling start time and function enabling end time of each target index, and present them in the system table management interface.

[0066] In this embodiment, the target index can be specifically understood as an index selected from the candidate indexes for which the fast index access function is recommended for activation. Each candidate index is sorted in descending order by its candidate weight within each unit time, i.e., arranged from largest to smallest. A greedy algorithm is then used to determine the target index for enabling the FIT function within each unit time. These target indexes form an index list, and the FIT function is enabled for indexes in the index list, while the FIT function is disabled for indexes not in the index list.

[0067] In this embodiment, by enabling database revisits, the number of random requests per unit time for each target index in the index list can be obtained, thereby determining when the peak and trough access times for each target index occur, and further determining the activation period for the FIT function, i.e., the start and end times of function activation, which are recorded as the function activation start time and function activation end time, respectively. It is understood that the same index can have multiple activation periods. Finally, the index list and the function activation start and end times for each target index in the index list are displayed in the system table management interface for user viewing.

[0068] S105: Receive a date setting operation input by the user and set an activation date for the index list.

[0069] The date setting operation is to set the activation date of the index list. Preferably, the date setting operation is to input any date as the activation date of the index list, or to input any specific holiday as the activation date of the index list; or not to input a date.

[0070] In this embodiment, the FIT function activation index list and its start and end time can be set for any date, specific holiday, and ordinary date. Specifically, the user clicks the Save button to set this data as the index for activating the FIT function and designate it as one of the following three types: Enter a date to use the FIT function activation index list for the specified date; Use this auxiliary tool to set a different FIT function activation index list and activation start and end time for Chinese lunar holidays such as Spring Festival, Qingming Festival, Dragon Boat Festival, Mid-Autumn Festival, Qixi Festival, etc., national statutory holidays, and certain specific dates such as May 20, June 18, and November 11, which are different from ordinary dates; Or, do not enter a date to use the FIT function activation index list for ordinary dates.

[0071] The above technical solution shields users from the complex details of host operation commands. Users do not need to learn and master the commands of the host operating system and host DB2. They only need to be able to use common operating systems, which reduces the learning cost of users. Since this execution subject communicates with the host DB2 through application program interface calls, there is no need to log in to the management interface of the host DB2, avoiding security risks caused by misoperation. In addition, this execution subject can automatically obtain the index list that meets the requirements for enabling the FIT function based on the database metadata information and calculate the size of the memory space occupied by the index. It can also efficiently obtain the index list information and activation start and end time for enabling the FIT function based on the database access playback information, avoiding complex manual calculation steps and calculation workload, and improving work efficiency. The ability to automatically execute related operations reduces the workload of users.

[0072] As an optional embodiment of the present invention, based on the above embodiment, the method may be optimized to further include: before obtaining the list of enabled indexes that meet the fast index access enabling condition:

[0073] Read and parse the parameter configuration file, obtain the system information of the relational database management system in the host, and connect with the relational database management system in the host based on the system information.

[0074] In this embodiment, a parameter configuration file is set up, which includes the following information: the connection address of the host DB2 and the corresponding user name and password; the user name and password for enabling the host DB2 data access playback task; the length per unit time (i.e., minutes); the path and tool log file name for saving the collected statistical information of each buffer pool; the information saved in the file includes the index name, index size, index read times, actual synchronous read times, and actual asynchronous read times; the file name for saving the index name for enabling the FIT function and the start and end time period information for enabling it; the information saved in the file includes the date, index name, start time for enabling the FIT function, and end date.

[0075] In this embodiment, after this execution subject is enabled, it first reads and parses the parameter configuration file to obtain system information such as the host DB2 address, user name and password, database connection information, etc., connects to the host DB2 database based on this system information, and provides a function of recommending the activation of the FIT function index list, setting the index list function for enabling the FIT function, and automatically updating the SYSINDEXCONTROL table information.

[0076] As an optional embodiment of the embodiment of the present invention, based on the above embodiment, the method can be optimized to further include:

[0077] a1) receiving a second selection operation input by the user, and presenting an index list with the quick index access function enabled.

[0078] The second selection operation is to select the index list setting function with the quick index access function enabled in the presented system table management interface.

[0079] In this embodiment, if the user selects the index list setting function for enabling the FIT function, the user selects an existing FIT-enabled index list record in the execution subject, and the execution subject displays the detailed information of the FIT-enabled index list in this record on the system table management interface, including the start and end time of enabling the FIT function for each index, the memory usage, etc.

[0080] b1) Receive index adjustment operation input by the user.

[0081] In this embodiment, the index adjustment operation is to adjust the function activation start time and function activation end time of any index in the presented index list. Specifically, the user adjusts the function activation start time and function activation end time of some indexes in the index list to enable the FIT function.

[0082] c1) Determine whether the adjusted index list meets the fast index access enabling conditions.

[0083] In this embodiment, after adjusting the index list, it is necessary to verify whether the adjusted index list meets the conditions for enabling the FIT function, that is, it is necessary to determine whether the memory space occupied by each index in the adjusted index list when enabling the FIT function is less than the memory space available for the FIT function in the host DB2.

[0084] d1) If the conditions are met, the user's list saving operation is received.

[0085] The save control triggers the presentation of a save control. Specifically, if the adjusted index list meets the conditions for enabling fast index access, after the user completes the adjustment, click Save and select one of the following three types. If the selected data already exists in the tool, the original data will be overwritten: Enter a date to enable the FIT function for the specified date; or specify an index list to enable the FIT function for Chinese Lunar New Year, Qingming Festival, Dragon Boat Festival, Mid-Autumn Festival, Qixi Festival, etc.; or do not enter a date to enable the FIT function for ordinary dates.

[0086] e1) If not, return to execute the index adjustment operation of receiving the user input until the adjusted index list meets the fast index access enabling condition.

[0087] Specifically, if the memory space occupied by each index in the adjusted index list when the FIT function is enabled is greater than the memory space available for the FIT function in the host DB2, a prompt will be given to indicate how much memory space is exceeded for the user to refer to for further adjustment, and the index adjustment operation receiving user input will be returned to be executed until the adjusted index list meets the conditions for enabling fast index access.

[0088] The above technical solution adds the function that users can adjust the index list and save this information. It can also set the index list for enabling the FIT function on these special dates and the start and end dates for activation for Chinese lunar holidays, statutory holidays and specific dates, thereby improving the convenience and efficiency of user operations.

[0089] The system table management method of the relational database management system provided in this embodiment also supports the function of setting the system table data in the host DB2 host. As another optional embodiment of the embodiment of the present invention, based on the above embodiment, this optional embodiment can optimize the method and further include:

[0090] a2) receiving a third selection operation input by the user, and obtaining a target index list from an index file with a fast index access function enabled.

[0091] The third selection operation is to select a function for setting system table data in the relational database management system in the presented system table management interface.

[0092] In this embodiment, if the user selects the function to set the system (SYSINDEXCONTROL) table data in the host DB2, this operation is recorded as the third selection operation. Specifically, the target index list can be understood as the index list for which index-related information is to be set. Upon receiving the third selection operation input by the user, the index list corresponding to the current date is obtained from the index file with the fast index access function enabled and recorded as the target index list.

[0093] b2) setting an index having only one fast index access function enabled period and a first enabled period of an index having at least two fast index access function enabled periods into a system table of a relational database management system.

[0094] It is understandable that this execution subject is connected to the host DB2, and then sets the first enabled period information data of the index with only one enabled FIT period and the index with multiple enabled time periods into the SYSINDEXCONTROL table.

[0095] c2) For an index having at least two periods of enabling the fast index access function, periodically detect the current time, search the index for a period of enabling the fast index access function whose previous enabling end time is less than the current time and whose next enabling start time is greater than the current time, and set the period of enabling the fast index access function whose next enabling start time is greater than the current time into the system table of the relational database management system.

[0096] Specifically, for indexes with multiple FIT-enabled periods, this execution entity will periodically detect the current time and search the index information with multiple FIT-enabled periods for data where the end time of the previous enabled period is less than the current time, but the start time of the next enabled period is greater than the current time. Then, the start time and end time of the FIT-enabled period for these indexes in the SYSINDEXCONTROL table will be updated to the start time and end time of this period for the corresponding index.

[0097] It should be noted that based on the pre-specified list of indexes for enabling the FIT function and the start and end time information for each index for enabling the FIT function, it is automatically determined whether to obtain data for a specific date, a lunar calendar date, or a regular date, and then the host DB2 is connected and this information is updated in the SYSINDEXCONTROL table. Multiple time periods for enabling the FIT function can be set for an index. For indexes on tables with multiple peak access periods throughout the day, this execution entity can pre-set multiple enabling time period information for the index and then automatically update the start and end time for enabling the FIT function for the index before the start of each enabling time period, thereby enabling the FIT function for a single index in the host DB2 for multiple time periods.

[0098] The above technical solution adds the ability to set system table data in the host DB2. Multiple FIT-enabled start and end times can be set for a single index. The system automatically updates the next FIT-enabled start and end time for that index to the host DB2's SYSINDEXCONTROL table after a FIT-enabled period ends. Based on pre-set information, it automatically generates syntactically correct SQL statements to update the SYSINDEXCONTROL table, avoiding errors that might occur when manually writing SQL statements. This improves the accuracy of data set in the SYSINDEXCONTROL table.

[0099] As a specific implementation method, the steps of obtaining the target index list from the index file with the fast index access function enabled can be optimized, including:

[0100] a21) traverse the activation date of each index list that has the fast index access function enabled.

[0101] In this embodiment, the index information file with the FIT function enabled is searched to see whether there is data with the same date as the current date, and the activation dates of the index lists with the fast index access function enabled are traversed.

[0102] a22) If there is an index list with the same activation date as the current date, the index list with the activation date is used as the target index list.

[0103] Specifically, if there is an activation date that is the same as the current date, then the data with the same date as the current date is read, and the index list of the activation date is used as the target index list.

[0104] a23) If there is no activation date that is the same as the current date, determine whether the current date is a specific date and whether an index list is set for the specific date.

[0105] In this embodiment, the specific date can be understood as a Chinese lunar festival or some specific date. Specifically, if there is no activation time that is the same as the current date, it is determined whether the current date is a specific date in China, and whether there is data for this specific date.

[0106] a24) If the current date is a specific date and an index list is set for the specific date, the index list of the specific date is used as the target index list.

[0107] Specifically, if the current date is a specific date and an index list is set for the specific date, that is, data of this lunar festival is found, the data of this lunar festival is read, and the index list of the specific date is used as the target index list.

[0108] a25) Otherwise, obtain the index list of common dates as the target index list.

[0109] Specifically, if there is no activation date that is the same as the current date and the current activation date is not a specific date, the index-related data for enabling the FIT function on a common date is read, and the index list on the common date is used as the target index list.

[0110] The above technical solution specifies the steps of determining the corresponding target index list according to the current date, and provides a basis for the subsequent setting of the host DB2 system table.

[0111] For example, in order to more clearly illustrate the system table management method of the relational database management system provided by the embodiment of the present invention, the execution logic thereof is described exemplarily below:

[0112] In a parameter configuration file, you'll set the IP address of the DB2 host you want to connect to, the username and password for connecting, the username and password for starting the database data playback task, the data collection interval, and other information. Upon startup, the execution entity will first read and parse the parameter configuration file, saving the information in memory. It will then read the DB2 host's metadata file to retrieve indexes that meet the following conditions: the index must be unique; the sum of the index key values ​​must not exceed 64 bytes; and the index must have fewer than 2 million leaf pages. The execution entity will then retrieve the buffer pool names used by these indexes from the database metadata based on the index names.

[0113] Then start the database access replay task, read the cumulative number of accesses and synchronous reads of these buffer pools from the replay, and regularly calculate the number of index accesses and synchronous reads in two time intervals according to the data collection time interval parameters in the configuration file, and record them in the tool's log file.

[0114] Finally, the access count and synchronous read count for each index within each time interval are obtained from the log file. A graph is plotted and saved to aid in determining the optimal start and end times for using FIT for these indexes in the host DB2 system table SYSINDEXCONTROL. After the user sets the index information for enabling FIT and the start and end time periods, the execution entity automatically updates the data in the SYSINDEXCONTROL table based on the user's settings.

[0115] In this embodiment, a list of indexes recommended for enabling the FIT function and the start and end time periods for enabling the FIT function for these indexes are calculated based on the memory size that can be used when the FIT function is enabled in the host DB2 database, the list of indexes that meet the conditions for enabling the FIT function, and the access status of these indexes in various time periods. The list of indexes for enabling the FIT function and the start and end time periods for enabling the FIT function are automatically updated to the system table SYSINDEXCONTROL of the host DB2 according to the user's final settings, thereby improving the overall performance of the host DB2 and the work efficiency of technicians in determining the list of indexes for enabling the FIT function and the start and end time period information, thereby reducing the intensity of this work.

[0116] Example 2

[0117] Figure 3 A flow chart of another system table management method for a relational database management system provided in Example 2 of the present invention. This embodiment is a further optimization of the above embodiment. In this embodiment, the limitation optimization of "enabling database playback, determining the number of synchronous reads reduced per unit time after each candidate index enables the fast index access function" is further implemented, as well as the limitation optimization of "determining the target index for enabling the fast index access function from each candidate index based on the weight of each index candidate to form an index list, and determining the function enabling start time and function enabling end time of each target index" is implemented.

[0118] like Figure 3 As shown, this embodiment 2 provides a system table management method for a relational database management system, which specifically includes the following steps:

[0119] S201: Receive a first selection operation input by a user, and obtain candidate indexes that meet a fast index access enabling condition from a database of a host relational database management system.

[0120] The first selection operation is to select the index list recommendation function to enable the quick index access function.

[0121] S202: Determine the memory space occupied by each candidate index when the fast index access function is enabled.

[0122] In this embodiment, after obtaining the candidate index list, the memory space occupied by each candidate index when the FIT function is enabled is calculated, and the buffer pool names corresponding to these candidate indexes are obtained.

[0123] S203: Enable database playback to obtain the number of first synchronous reads per unit time when the fast index access function is not enabled for each candidate index.

[0124] Specifically, the host DB2 command is called to enable data access playback, and the workload is run. The statistical information of the buffer pool corresponding to these candidate indexes is read from the buffer pool per unit time, including the number of random requests and the number of synchronous reads. Then, the number of random requests and the number of synchronous reads per unit time for each index are calculated and saved in memory as the random request number and the number of synchronous reads when the FIT function is not enabled for each index, and recorded as the first synchronous read number.

[0125] S204: Determine multiple groups of candidate indexes for simulating enabling the fast index access function based on the available memory space for enabling the fast index access function of the relational database management system and the memory space occupied by each candidate index for enabling the fast index access function.

[0126] Each group of candidate indexes includes multiple candidate indexes. In this embodiment, based on the available memory space of the host DB2 when the FIT function is enabled and the memory space occupied by each index when the FIT function is enabled, a batch of candidate indexes with the FIT function enabled are simulated and determined, and the batch of candidate indexes with the FIT function enabled are recorded as a group of candidate indexes.

[0127] S205 : Enable database playback to obtain the number of second synchronous reads per unit time after the fast index access function is enabled for each candidate index.

[0128] Specifically, enable database revisit and obtain the number of synchronous reads per unit time after the FIT function is enabled for each candidate index, which is recorded as the second synchronous read number.

[0129] S206 : For each candidate index, subtract the second synchronous read number from the first synchronous read number of the candidate index to obtain the reduced number of synchronous reads per unit time after the fast index access function is enabled for the candidate index.

[0130] For each candidate index, the first synchronous read count is subtracted from the second synchronous read count. The result is recorded as the number of synchronous reads reduced per unit time after the FIT function is enabled for the candidate index. It should be noted that it is necessary to determine whether all candidate indexes have achieved the reduced number of synchronous reads after the FIT function is enabled. If not, continue to execute steps S204-S206.

[0131] S207 : Determine the index candidate weight of each candidate index according to the number of synchronous reads reduced per unit time after the fast index access function is enabled for each candidate index and the memory space of each candidate index when the fast index access function is enabled.

[0132] S208 , arranging the weights of the candidate indexes from large to small, using a greedy algorithm to select target indexes with a fast index access function enabled from the candidate indexes, and forming an index list with the target indexes.

[0133] A greedy algorithm selects the current optimal solution at each step, aiming to find a local optimal solution, but not necessarily a global optimal solution. In this embodiment, each candidate index is sorted in descending order by its weight within each unit time, from largest to smallest. Then, a greedy algorithm is used to determine the target index for enabling the FIT function within each unit time. These target indexes form an index list, and the FIT function is enabled for indexes in the index list and disabled for indexes not in the index list.

[0134] It should be noted that users can adjust this recommended index list. For example, they can adjust the list of indexes enabled for FIT within a certain unit of time. Each time an adjustment is made, the execution entity automatically calculates whether the total memory space occupied by the indexes enabled for FIT within that unit of time is less than or equal to the memory space available for FIT in the host DB2 database. If it is greater than the system's available memory for FIT, the user is prompted with the amount of excess memory space for further adjustment.

[0135] S209: Enable database playback to obtain the number of random requests per unit time for each target index in the index list, and determine the function activation start time and function activation end time of each target index based on each random request number, and present them in the system table management interface.

[0136] In this embodiment, by enabling database revisits, the number of random requests per unit time for each target index in the index list can be obtained, thereby determining when the peak and trough access times for each target index occur, and further determining the activation period for the FIT function, i.e., the start and end times of function activation, which are recorded as the function activation start time and function activation end time, respectively. It is understood that the same index can have multiple activation periods. Finally, the index list and the function activation start and end times for each target index in the index list are displayed in the system table management interface for user viewing.

[0137] It should be noted that enabling database revisit can simultaneously obtain the number of random requests and the number of synchronous reads per unit time. Therefore, when obtaining the number of synchronous reads of the target index, the number of random requests can be obtained synchronously and stored in the memory, and then retrieved from the memory when needed.

[0138] S210: Receive a date setting operation input by the user, and set an activation date for the index list.

[0139] The date setting operation is to set the activation date of the index list.

[0140] The above technical solution specifically includes the steps of determining the reduction in the number of synchronous reads per unit time after enabling the fast index access function for each candidate index, determining the target indexes from each candidate index to form an index list for enabling the fast index access function, and determining the start and end times for enabling the function for each target index. The available memory size for enabling the FIT function in the host DB2 database is read, and information about indexes that meet the requirements is obtained from database metadata. The memory size occupied by these indexes after enabling the FIT function is calculated. Statistical information about these indexes is then read from database playback information to obtain the number of accesses per unit time (e.g., per minute) over time. A list of indexes that can enable the FIT function in each time period is calculated and displayed to the user along with the start and end times for enabling the FIT function. The list of indexes that can enable the FIT function and their start and end times are generated by querying the host DB2 database metadata and reading database playback information. This method avoids complex manual calculation steps and workload, improving work efficiency. Related operations can be automatically performed, reducing the workload of users.

[0141] Example 3

[0142] Figure 4 This is a schematic diagram of the structure of a system table management device for a relational database management system provided in the third embodiment of the present invention. The device is applicable to situations where the system table of a relational database management system is managed. The system table management device for the relational database management system can be implemented in the form of hardware and / or software and is generally integrated into an electronic device. Figure 4 As shown, the device includes: an index acquisition module 31, a quantity determination module 32, a weight determination module 33, an index recommendation module 34 and a date setting module 35, wherein:

[0143] The index acquisition module 31 is configured to receive a first selection operation input by a user and obtain candidate indexes that meet the fast index access enabling conditions from a database of a host relational database management system. The first selection operation is to select an index list recommendation function to enable the fast index access function in a presented system table management interface.

[0144] The number determination module 32 is used to enable database playback and determine the number of synchronous reads reduced per unit time after the fast index access function is enabled for each candidate index;

[0145] The weight determination module 33 is used to determine the index candidate weight of each candidate index based on the number of synchronous reads reduced per unit time after the fast index access function is enabled for each candidate index and the memory space of each candidate index when the fast index access function is enabled;

[0146] The index recommendation module 34 is used to determine the target indexes for enabling the fast index access function from each candidate index based on the weight of each index candidate, form an index list, and determine the function activation start time and function activation end time of each target index, and present them in the system table management interface;

[0147] The date setting module 35 is used to receive a date setting operation input by a user and set the activation date of the index list. The date setting operation is to set the activation date of the index list.

[0148] The above technical solution shields users from the complex details of host operation commands. Users do not need to learn and master the commands of the host operating system and host DB2. They only need to be able to use common operating systems, which reduces the learning cost of users. Since this execution subject communicates with the host DB2 through application program interface calls, there is no need to log in to the management interface of the host DB2, avoiding security risks caused by misoperation. In addition, this execution subject can automatically obtain the index list that meets the requirements for enabling the FIT function based on the database metadata information and calculate the size of the memory space occupied by the index. It can also efficiently obtain the index list information and activation start and end time for enabling the FIT function based on the database access playback information, avoiding complex manual calculation steps and calculation workload, and improving work efficiency. The ability to automatically execute related operations reduces the workload of users.

[0149] Optionally, the quantity determination module 32 is specifically configured to:

[0150] Determine the memory space occupied by each candidate index when enabling the fast index access function;

[0151] Enable database replay to obtain the number of first synchronous reads per unit time for each candidate index when the fast index access function is not enabled;

[0152] Determine, based on the available memory space of the relational database management system for enabling the fast index access function and the memory space occupied by each candidate index when the fast index access function is enabled, multiple groups of candidate indexes for simulating enabling the fast index access function, each group of candidate indexes including multiple candidate indexes;

[0153] Enable database replay to obtain the number of second synchronous reads per unit time after the fast index access function is enabled for each candidate index;

[0154] For each candidate index, the number of first synchronous reads minus the number of second synchronous reads of the candidate index is subtracted to obtain the number of synchronous reads reduced per unit time after the fast index access function is enabled for the candidate index.

[0155] Optionally, the weight determination module 33 is specifically configured to:

[0156] Arrange the weights of the candidate indexes from large to small, use a greedy algorithm to select the target indexes that enable the fast index access function from the candidate indexes, and form an index list with the target indexes;

[0157] Enable database playback to obtain the number of random requests for each target index in the index list, and determine the function activation start time and function activation end time of each target index based on each random request number.

[0158] Optionally, the date setting operation is to input any date as the activation date of the index list, or to input any specific date as the activation date of the index list; or to not input a date.

[0159] Optionally, the device further includes a list setting module, specifically configured to:

[0160] Receiving a second selection operation input by the user, presenting an index list with the fast index access function enabled, the second selection operation being selecting an index list setting function with the fast index access function enabled in the presented system table management interface;

[0161] Receive an index adjustment operation input by a user, where the index adjustment operation is to adjust a function activation start time and a function activation end time of any index in the presented index list;

[0162] Determine whether the adjusted index list meets the conditions for enabling fast index access;

[0163] If the conditions are met, the user's list save operation is received, and the list save operation triggers the presentation of the save control;

[0164] If not, the process returns to executing the index adjustment operation of receiving the user input until the adjusted index list meets the fast index access enabling condition.

[0165] Optionally, the device further includes a system table setting module, which may include:

[0166] a list acquisition unit, configured to receive a third selection operation input by a user, and acquire a target index list from an index file with a fast index access function enabled, wherein the third selection operation is selecting a function for setting system table data in a relational database management system in a presented system table management interface;

[0167] a time period setting unit, configured to set an index having only one time period for enabling the fast index access function, and a first enabling time period of an index having at least two time periods for enabling the fast index access function, into a system table of the relational database management system;

[0168] The time period updating unit is used to periodically detect the current time for an index having at least two time periods for enabling the fast index access function, search the index for a time period for enabling the fast index access function whose last enabling end time is less than the current time and whose next enabling start time is greater than the current time, and set the time period for enabling the fast index access function whose next enabling start time is greater than the current time into a system table of the relational database management system.

[0169] Optionally, the list acquisition unit is specifically configured to:

[0170] Iterate through the activation date of each index list that has enabled the fast index access function;

[0171] If there is an index list with the same activation date as the current date, the index list with the activation date will be used as the target index list;

[0172] If there is no activation date that is the same as the current date, determine whether the current date is a specific date and whether an index list is set for the specific date;

[0173] If the current date is a specific date and an index list is set for the specific date, the index list of the specific date is used as the target index list;

[0174] Otherwise, get the index list of common dates as the target index list.

[0175] Optionally, the apparatus further includes a connection module, which, before obtaining the list of enabled indexes that meet the fast index access enabling condition, is configured to:

[0176] Read and parse the parameter configuration file, obtain the system information of the relational database management system in the host, and connect with the relational database management system in the host based on the system information.

[0177] The system table management device for a relational database management system provided in an embodiment of the present invention can execute the system table management method for a relational database management system provided in any embodiment of the present invention, and has functional modules and beneficial effects corresponding to the execution method.

[0178] Example 4

[0179] Figure 5A schematic diagram of the structure of an electronic device provided for embodiment four of the present invention. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device may also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices (such as helmets, glasses, watches, etc.) and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present invention described and / or required herein.

[0180] like Figure 5 As shown, the electronic device 40 includes at least one processor 41 and a memory, such as a read-only memory (ROM) 42, a random access memory (RAM) 43, etc., which is communicatively connected to the at least one processor 41. The memory stores a computer program that can be executed by the at least one processor, and the processor 41 can perform various appropriate actions and processes according to the computer program stored in the read-only memory (ROM) 42 or the computer program loaded from the storage unit 48 into the random access memory (RAM) 43. Various programs and data required for the operation of the electronic device 40 can also be stored in the RAM 43. The processor 41, ROM 42, and RAM 43 are connected to each other via a bus 44. An input / output (I / O) interface 45 is also connected to the bus 44.

[0181] Multiple components in the electronic device 40 are connected to the I / O interface 45, including an input unit 46, such as a keyboard, a mouse, etc.; an output unit 47, such as various types of displays, speakers, etc.; a storage unit 48, such as a magnetic disk, an optical disk, etc.; and a communication unit 49, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 49 allows the electronic device 40 to exchange information / data with other devices via a computer network such as the Internet and / or various telecommunication networks.

[0182] Processor 41 can be various general-purpose and / or specialized processing components with processing and computing capabilities. Some examples of processor 41 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various specialized artificial intelligence (AI) computing chips, various processors that run machine learning model algorithms, a digital signal processor (DSP), and any appropriate processor, controller, microcontroller, etc. Processor 41 executes the various methods and processes described above, such as the system table management method of a relational database management system.

[0183] In some embodiments, the system table management method for a relational database management system can be implemented as a computer program that is tangibly contained in a computer-readable storage medium, such as the storage unit 48. In some embodiments, part or all of the computer program can be loaded and / or installed on the electronic device 40 via the ROM 42 and / or the communication unit 49. When the computer program is loaded into the RAM 43 and executed by the processor 41, one or more steps of the system table management method for the relational database management system described above can be performed. Alternatively, in other embodiments, the processor 41 can be configured to execute the system table management method for the relational database management system by any other appropriate means (e.g., by means of firmware).

[0184] Various embodiments of the systems and techniques described herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), system-on-chip systems (SOCs), programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments can include being implemented in one or more computer programs that are executable and / or interpreted on a programmable system that includes at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.

[0185] Computer programs for implementing the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when the computer program is executed by the processor, the functions / operations specified in the flowcharts and / or block diagrams are implemented. The computer program may be executed entirely on the machine, partially on the machine, as a stand-alone software package, partially on the machine and partially on a remote machine, or entirely on a remote machine or server.

[0186] In the context of the present invention, computer-readable storage media can be tangible media that can contain or store a computer program for use with an instruction execution system, device or equipment or used in combination with an instruction execution system, device or equipment. Computer-readable storage media can include but are not limited to electronic, magnetic, optical, electromagnetic, infrared or semiconductor systems, devices or equipment, or any suitable combination of the foregoing. Alternatively, computer-readable storage media can be machine-readable signal media. More specific examples of machine-readable storage media can include electrical connections based on one or more lines, portable computer disks, hard disks, random access memories (RAM), read-only memories (ROM), erasable programmable read-only memories (EPROM or flash memory), optical fibers, portable compact disk read-only memories (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination of the foregoing.

[0187] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user can provide input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, the feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including acoustic input, voice input, or tactile input).

[0188] The systems and techniques described herein can be implemented in a computing system that includes back-end components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes front-end components (e.g., a user computer with a graphical user interface or web browser through which a user can interact with implementations of the systems and techniques described herein), or a computing system that includes any combination of such back-end components, middleware components, or front-end components. The components of the system can be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.

[0189] A computing system may include clients and servers. The clients and servers are typically remote from each other and typically interact via a communication network. This client-server relationship arises through computer programs running on the respective computers, creating a client-server relationship. The server may be a cloud server, also known as a cloud computing server or cloud host. This server is a hosting product within the cloud computing service ecosystem that addresses the management difficulties and limited scalability of traditional physical hosting and VPS services.

[0190] An embodiment of the present invention further provides a computer program product, including a computer program. When the computer program is executed by a processor, the computer program implements the system table management method of the relational database management system provided in any embodiment of the present invention.

[0191] The computer program product may be implemented by writing computer program code for performing the operations of the present disclosure in one or more programming languages, or a combination thereof, including, but not limited to, object-oriented programming languages ​​such as Java, Smalltalk, C++, and conventional procedural programming languages ​​such as "C" or similar programming languages. The program code may be executed entirely on the user's computer, partially on the user's computer, as a stand-alone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving a remote computer, the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., through the Internet using an Internet service provider).

[0192] It should be understood that the various forms of the processes shown above can be used to reorder, add, or delete steps. For example, the steps described in the present invention can be performed in parallel, sequentially, or in a different order, as long as the desired results of the technical solution of the present invention can be achieved. This is not limited herein.

[0193] The above specific embodiments do not limit the scope of protection of the present invention. Those skilled in the art will appreciate that various modifications, combinations, sub-combinations, and substitutions may be made based on design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention are intended to be included within the scope of protection of the present invention.

Claims

1. A system table management method for a relational database management system, characterized in that: include: Receiving a first selection operation input by a user, obtaining candidate indexes that meet a fast index access enabling condition from a database of a host relational database management system, wherein the first selection operation is selecting an index list recommendation function to enable a fast index access function in a presented system table management interface; Enable database replay to determine the number of synchronous reads reduced per unit time after the fast index access function is enabled for each candidate index; Determining the index candidate weight of each candidate index according to the number of synchronous reads reduced per unit time after the fast index access function is enabled for each candidate index and the memory space of each candidate index when the fast index access function is enabled; According to the weight of each index candidate, determine the target index for enabling the fast index access function from each candidate index to form an index list, and determine the function enabling start time and function enabling end time of each target index, and present them in the system table management interface; A date setting operation input by a user is received, and an activation date of the index list is set. The date setting operation is the setting of the activation date of the index list.

2. The method according to claim 1, characterized in that Determining the reduction in the number of synchronous reads per unit time after the fast index access function is enabled for each candidate index includes: Determining the memory space occupied by each candidate index when a fast index access function is enabled; Enable database playback to obtain the number of first synchronous reads per unit time when the fast index access function is not enabled for each candidate index; Determining, based on the available memory space of the relational database management system for enabling the fast index access function and the memory space occupied by each candidate index when the fast index access function is enabled, multiple groups of candidate indexes for simulating enabling the fast index access function, each group of candidate indexes including multiple candidate indexes; Enable database playback to obtain the number of second synchronous reads per unit time after the fast index access function is enabled for each candidate index; For each candidate index, the first synchronous read quantity of the candidate index is subtracted from the second synchronous read quantity to obtain the reduced number of synchronous reads per unit time after the fast index access function is enabled for the candidate index.

3. The method according to claim 1, characterized in that The step of determining target indexes for enabling the fast index access function from the candidate indexes according to the weights of the candidate indexes to form an index list, and determining a function enabling start time and a function enabling end time for each target index, includes: Arrange the weights of the candidate indexes from large to small, use a greedy algorithm to select target indexes with a fast index access function enabled from the candidate indexes, and form the index list with the target indexes; Database playback is enabled to obtain a random request number for each target index in the index list, and a function enabling start time and a function enabling end time for each target index are determined based on each random request number.

4. The method according to claim 1, wherein The date setting operation is to input any date as the activation date of the index list, or to input any specific date as the activation date of the index list; or to not input a date.

5. The method according to claim 1, characterized in that Also includes: Receive a second selection operation input by the user, and present an index list with the quick index access function enabled, wherein the second selection operation is to select an index list setting function with the quick index access function enabled in the presented system table management interface; receiving an index adjustment operation input by a user, wherein the index adjustment operation is adjusting a function activation start time and a function activation end time of any index in the presented index list; Determine whether the adjusted index list meets the conditions for enabling fast index access; If the conditions are met, the user's list saving operation is received, and the list saving operation triggers the presentation of a save control; If not, the process returns to executing the index adjustment operation of receiving the user input until the adjusted index list meets the fast index access enabling condition.

6. The method according to claim 1, characterized in that Also includes: receiving a third selection operation input by the user, and obtaining a target index list from an index file with a fast index access function enabled, wherein the third selection operation is selecting a function for setting system table data in a relational database management system in a presented system table management interface; Setting an index having only one period in which the fast index access function is enabled, and a first enabled period of an index having at least two periods in which the fast index access function is enabled, into a system table of the relational database management system; For an index having at least two periods of enabling the fast index access function, the current time is periodically detected, and the fast index access function enabling period whose last enabling end time is less than the current time and whose next enabling start time is greater than the current time is searched from the index, and the fast index access function enabling period whose next enabling start time is greater than the current time is set to the system table of the relational database management system.

7. The method according to claim 6, characterized in that The step of obtaining a target index list from an index file with the fast index access function enabled includes: Iterate through the activation date of each index list that has enabled the fast index access function; If there is an activation date that is the same as the current date, the index list of the activation date is used as the target index list; If there is no activation date that is the same as the current date, determining whether the current date is a specific date and whether an index list is set for the specific date; If the current date is a specific date and an index list is set for the specific date, the index list of the specific date is used as the target index list; Otherwise, an index list of common dates is obtained as the target index list.

8. The method according to claim 1, characterized in that Before obtaining the list of enabled indexes that meet the fast index access enabling conditions, the method further includes: The parameter configuration file is read and parsed to obtain the system information of the relational database management system in the host, and a connection is established with the relational database management system in the host based on the system information.

9. A system table management device for a relational database management system, characterized in that: include: An index acquisition module, configured to receive a first selection operation input by a user and acquire candidate indexes that meet the fast index access enabling condition from a database of a host relational database management system, wherein the first selection operation is selecting an index list recommendation function to enable the fast index access function in a presented system table management interface; A quantity determination module is used to enable database replay and determine the number of synchronous reads reduced per unit time after the fast index access function is enabled for each candidate index; A weight determination module, configured to determine an index candidate weight of each candidate index based on a reduction in the number of synchronous reads per unit time after each candidate index enables the fast index access function, and a memory space when each candidate index enables the fast index access function; An index recommendation module is used to determine, based on the weight of each index candidate, a target index for enabling the fast index access function from each candidate index to form an index list, and to determine the function enabling start time and function enabling end time of each target index, and present the result in the system table management interface; The date setting module is used to receive a date setting operation input by a user and set the activation date of the index list. The date setting operation is to set the activation date of the index list.

10. An electronic device, characterized in that: include: at least one processor; as well as a memory communicatively connected to the at least one processor; wherein, The memory stores a computer program executable by the at least one processor. The computer program is executed by the at least one processor to enable the at least one processor to execute the system table management method of the relational database management system according to any one of claims 1 to 8.

Images