A method and system for changing table structure online

Abstract

The present invention provides a method and system for online table structure modification. The method comprises: responding to a table structure modification instruction, entering a modification initialization phase, initializing the target table, staging table, and source table triggers required for data movement during the table modification process; copying data from the source table to the target table in batches based on preset optimal parameters and modification information; and after the rewrite is completed or a rewrite threshold is reached, taking the source table offline and, through a distributed database exchange of the source and target table names, bringing the target table online to complete the table structure modification. The present invention utilizes the AMT feature of the distributed database DB2 to implement online table structure modification. Through machine learning, configuration parameter data is automatically generated, optimizing and reducing the time required for database table structure modification operations, reducing the time spent on critical operations, improving system business continuity and system availability, and reducing the impact of lengthy table modification times on core systems.

Description

Technical Field

[0001] The present invention relates to the technical field of data storage, and in particular to a method and system for online changing table structure. Background Art

[0002] The rapid growth of core system services has led to a rapid increase in the size of core system databases. The continuous increase and adjustment of business requirements has increased the frequency of core system changes. This rapid growth and frequent changes have further increased the window for core maintenance downtime, the complexity of releases, and the risk of changes, placing significant pressure on the business continuity of core systems.

[0003] Through in-depth research and analysis of the core system release process, we found that operations that cause extended downtime due to releases are primarily related to database table structure operations, including creating and splitting tables, adding and modifying columns, and other operations (see the change rules for details). Therefore, optimizing and reducing the time required for database table structure changes through innovative technical means, and reducing the time spent on critical operations, has become a key factor in improving business continuity and system availability for core systems. Summary of the Invention

[0004] The present invention provides a method and system for online changing table structure, which are used to solve the problem that the existing database table changes take a long time.

[0005] To achieve the above object, the present invention adopts the following technical solutions:

[0006] A first aspect of the present invention provides a method for online changing a table structure, the method comprising the following steps:

[0007] In response to the table structure change instruction, the change initialization phase begins, initializing the target table, staging table, and source table triggers required for data movement during the table change process.

[0008] Based on the preset optimal parameters and change information, the data in the source table is copied to the target table in batches;

[0009] After the rewrite is completed or the rewrite threshold is reached, the source table is taken offline, the names of the source and target tables are exchanged through the distributed database, and the target table is brought online to complete the table structure change.

[0010] Furthermore, the method further comprises the steps of:

[0011] If an operation on the database table data occurs during the replication process, the operation instruction is captured through the source table trigger and the operation is saved in the staging table;

[0012] Rewrite the data in the staging table to the target table.

[0013] Furthermore, before entering the change initialization phase, the following steps are also included:

[0014] The change information is checked, where the change information includes the change semantics, change syntax, and change rules of the change file.

[0015] Furthermore, the change rule is a three-part string, which consists of the change name, object type and rule description; the change semantics include the table creation statement of the target table in the database change; the change syntax includes the creation statements of indexes, references and constraints in the database change table.

[0016] Furthermore, before entering the change initialization phase, the DDL for creating a table is checked, specifically:

[0017] If it has not been created, create the table DDL statement. If it has been created, modify the table creation statement to create a new table.

[0018] Determine the change scenario based on the change field type of the change information.

[0019] Furthermore, the optimal parameters are predicted by a random forest regression model.

[0020] Furthermore, the process of predicting the optimal parameters by the random forest regression model is specifically as follows:

[0021] Constructing a training data set, wherein the data set is represented as an N×M matrix, where N represents the number of training set samples, M represents (M-1) eigenvalues ​​and 1 parameter value column, and the parameter value represents the value of the optimal parameter;

[0022] Constructing a model: Randomly extracting several sample data from the dataset to construct an initial data subset; Randomly selecting (M-2) features from all (M-1) feature columns to be selected in the initial data subset as input features of the decision tree to obtain an updated data subset; Decision trees are obtained based on each updated data subset, and multiple decision trees form a random forest;

[0023] The feature combination is used as the input of the random forest model, the parameter value column is used as the output, and the mean of the output values ​​is used as the predicted value of the optimal parameter.

[0024] Furthermore, the value of M is 4, and the characteristic values ​​include the existence of LOB type fields in the table, the number of columns in the table, and the selection of whether compression is enabled for the table.

[0025] A second aspect of the present invention provides a system for online changing a table structure, the system comprising:

[0026] The initialization module, in response to the table structure change instruction, enters the change initialization phase and initializes the target table, staging table, and source table triggers required for data movement during the table change process;

[0027] A data replication module, based on preset optimal parameters and change information, copies the data in the source table to the target table in batches;

[0028] The table interaction module takes the source table offline after the rewrite is completed or the rewrite threshold is reached, exchanges the names of the source and target tables through the distributed database, and brings the target table online to complete the table structure change.

[0029] Furthermore, the system also includes a rewrite module. When an operation on the database table data occurs during the data replication process of the data replication module, the operation instruction is captured through the source table trigger, the operation is saved in the staging table, and the rewrite module is called to rewrite the data in the staging table into the target table.

[0030] The system for online changing table structure of the second aspect of the present invention can implement the method of the first aspect and various implementations of the first aspect, and achieve the same effect.

[0031] The effects provided in the summary of the invention are only the effects of the embodiments, not all the effects of the invention. One of the above technical solutions has the following advantages or beneficial effects:

[0032] The present invention utilizes the AMT (ADMIN_MOVE_TABLE) feature of the distributed database DB2 to implement online table structure changes. Through machine learning, it automatically generates configuration parameter data, optimizes and reduces the time required for database table structure change operations, reduces the time spent on operations on critical paths, improves system business continuity and system availability, and reduces the impact of time-consuming table changes on core systems. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without any creative work.

[0034] Figure 1 1 is a flow chart of a table changing process in an embodiment of the method of the present invention;

[0035] Figure 2 It is a structural diagram of an embodiment of the system of the present invention. DETAILED DESCRIPTION

[0036] In order to clearly illustrate the technical features of this solution, the present invention is described in detail below through specific implementation methods and in conjunction with the accompanying drawings. The disclosure below provides many different embodiments or examples for realizing different structures of the present invention. In order to simplify the disclosure of the present invention, the components and settings of specific examples are described below. In addition, the present invention may repeat reference numbers and / or letters in different examples. This repetition is for the purpose of simplicity and clarity and does not itself indicate the relationship between the various embodiments and / or settings discussed. It should be noted that the components illustrated in the accompanying drawings are not necessarily drawn to scale. The present invention omits descriptions of well-known components and processing technologies and processes to avoid unnecessary limitations on the present invention.

[0037] The present application provides a process for online table structure changes using the AMT (ADMIN_MOVE_TABLE) feature of a DB2 (DB2 is a distributed database from IBM) database, enabling the following online change operations: changes include generating columns, splitting columns, converting non-partitioned tables to partitioned tables, adding columns, deleting columns, renaming columns, adjusting field lengths, parent-child tables, creating new tables, and migrating new table data, among other operational and maintenance scenarios. The present application automatically generates optimal parameter data through the database online table structure change program, optimizing and reducing the time required for database table structure changes, reducing the time spent on critical operations, and improving system business continuity and availability.

[0038] The main steps are as follows:

[0039] Step 1: Check the change semantics, change syntax, and change rules of the file directory change file.

[0040] The system defines Rul, Sql and Alk types of files. The Rul file stores the parameter files required for database changes, including the change object, change type, specific change description and other information.

[0041] The table change rules are shown in the following table:

[0042]

[0043] A rule is a three-part string, each part separated by a colon ":". The first part is a fixed rule, the second part is the rule type, and the third part is the specific rule. The third part can be empty.

[0044] An Sql file is a text-formatted file with the suffix sql. It stores the DDL (data definition language) table creation statements of the target table in the database change, forming the change semantics.

[0045] Alk files are text files with the suffix alk. They store creation statements for indexes, references, constraints, etc. in database changes, forming the change syntax.

[0046] Step 2: Check the table creation DDL. If the table has not been created, the program executes the table creation DDL. If the table has been created, the program modifies the table creation statement to create a new table, and changes the field type according to the change rules specified in the RUL file to determine the change scenario.

[0047] Step 3: Before making any changes, check and confirm the table dependencies, constraints, and locks. Generate the optimal parameter (COMMIT_AFTER_N_ROWS) value. The optimal parameter indicates the number of rows copied in one data commit.

[0048] In DB2 databases, the ADMIN_MOVE_TABLE stored procedure allows for the creation of new tables and the migration of newly created table data. During the data migration process, the optimal parameter (COMMIT_AFTER_N_ROWS) determines the number of rows submitted in batches. If the optimal parameter value is small, fewer rows are submitted per batch, and the number of commits increases. Conversely, if the optimal parameter value is large, the number of commits decreases, but the number of rows submitted per batch increases. Both of these situations are detrimental to the normal progress of data migration. When the data scale is small, the optimal parameter value has little impact; however, when the data scale is large, the optimal parameter value becomes particularly critical.

[0049] There are many factors that affect the optimal parameters, among which the following three factors are more important:

[0050] 1) Whether the table contains LOB type fields: LOB fields are field types used by the database to store large data objects, including BLOB, CLOB, etc.

[0051] 2) Number of rows in the table: the number of rows in the database containing records;

[0052] 3) Whether compression is enabled in the table: Repeated characters and strings are stored in a compression dictionary, and the actual data in the table is replaced with a replacement symbol corresponding to the data string stored in the dictionary, thereby achieving the purpose of compressing the data.

[0053] Specific implementation process of determining the optimal parameters:

[0054] 1) Construct a training dataset T. The dataset can be represented as an N×M matrix, where N represents the number of training set samples, M represents (M-1) feature columns and 1 parameter value column. In this embodiment, M is 4, and the feature columns are lob, l, de, and rows. Lob indicates whether the table contains LOB type fields, and the value is yes or no; l represents the number of rows in the table, and the value is a positive integer; de indicates whether compression is enabled for the table, and the value is yes or no; and rows represents the value of the optimal parameter.

[0055] 2) Model construction

[0056] The model is constructed using the random forest regression model in Java language: ① N sample data are randomly selected from the data set T each time with replacement, and the data subset is constructed. The sampling process is carried out for a total of k rounds, and k data subsets T1, T 2, …,T k ;②Each data subset T1,T2,…,T k Randomly select 2 features from all 3 feature columns to be selected as input features of the decision tree, discard the unselected 1 feature, and update the data subset T1, T2, ..., T k ; ③ According to each data subset T1, T2, ..., T k Get each decision tree T R1 ,T R2 ,…,T Rk , which is composed of multiple decision trees to form a random forest; ④Finally, for the feature combination of the test sample (lob new ,ln ew ,de new ), where lo bnew 、ln ew 、de new Represent the lob, l, and de feature values ​​of the test samples respectively, and all decision trees T R1 ,T R2, …,T Rk The values ​​of the predicted optimal parameters are recorded as rows1, rows2,…, rows k , calculate the average value of the prediction value of each decision tree according to the average method Return as the final result.

[0057] In the random forest regression model, the number of trees is assigned to 10 and the minimum number of samples for a leaf node is 2.

[0058] Change scenarios include generating columns, splitting columns, changing non-partitioned tables to partitioned tables, adding columns, deleting columns, renaming columns, adjusting field lengths, parent-child tables, creating new tables, and migrating new table data.

[0059] Step 4: Changes are implemented in stages, such as Figure 1 As shown, the four stages of admin_move_table (administrator move table) can be independently controlled to achieve online change of table structure. The four stages are:

[0060] ① INIT (initialization) phase. This phase verifies whether data movement can occur and initializes various data required for subsequent data movement, such as the target table, staging table, and source table triggers. For example, when executing the online change procedure page, the DB2 database create table and create trigger operations are invoked to prepare metadata for the column addition operation.

[0061] ②COPY phase. This phase copies all data from the source table to the target table created during the INIT phase. The algorithmically generated optimal parameters play a key role in this phase, improving efficiency and reliability. Furthermore, if a user executes DML statements (Data Manipulation Language, used to insert, delete, or update data in database tables) on the source table during this phase, triggers created during the INIT phase will capture these statements and save any updates, deletes, and inserts to the staging table. After the copy phase completes, if the COPY_WITH_INDEXES option is not used, new indexes will be created on the target table. Furthermore, if necessary, auxiliary indexes will be created on both the source and target tables to improve performance during the REPLAY phase. The COPY phase can only begin after the INIT phase completes. For example, in the scenario of adding a column, the program's online change program automatically calculates the optimal number of committed rows based on the original table's data volume through an algorithm. The backend then uses a load or export process to complete the data copy.

[0062] ③REPLAY phase. All data saved to the staging table during the COPY phase is replayed to the target table during this phase. The REPLAY phase can only begin after the COPY phase completes. For example, in the scenario of adding a column, if the database is busy, the replay operation can be paused programmatically and resumed when the database is idle.

[0063] ④SWAP phase (swapping partitions). After the Replay phase completes or the REPLAY_THRESHOLD value stored in the protocol table is reached, the source table is taken offline. DB2 then exchanges the source and target table names and brings the tables back online. The SWAP phase can be initiated after the COPY call completes, but ideally, after the REPLAY call completes.

[0064] like Figure 2 As shown, an embodiment of the present invention further provides a system for changing a table structure online, wherein the system includes an initialization module, a data replication module, a rewriting module and a table interaction module.

[0065] In response to table structure change instructions, the initialization module enters the change initialization phase, initializing the target table, staging table, and source table triggers required for data movement during the table change process. The data replication module copies the data from the source table to the target table in batches based on preset optimal parameters and change information. After the rewrite is completed or the rewrite threshold is reached, the table interaction module takes the source table offline and, through the distributed database interaction of the source and target table names, brings the target table online to complete the table structure change. If operations on database table data occur during the data replication process in the data replication module, the operation instructions are captured by the source table triggers, saved in the staging table, and the rewrite module is invoked to rewrite the data in the staging table to the target table.

[0066] The following is an example of the table change process, combined with a specific implementation example:

[0067] Check the semantics, syntax, and rules of file changes in the file directory.

[0068] Rul rule file example:

[0069] In a change operation, a column TOTAL_MOUNT is added to the DB2INST1.ACCOUNT table. The rule file should be as follows:

[0070] COLUMN ACCOUNT.TOTAL_MOUNT RULE:NEWCOL:

[0071] Sql file example case:

[0072]

[0073] Alk file example:

[0074]

[0075] Create table DDL check. During the pre-change check of a new table, the syntax of the SQL file and the ALK file is checked, and whether the target table already exists in the database is checked.

[0076] Before making any changes, check and confirm the table's dependencies, constraints, and locks, and generate the optimal parameter value (COMMIT_AFTER_N_ROWS).

[0077] 1) Construct the training data set T as:

[0078] lob l de rows yes 1000000 yes 8000 yes 1100000 yes 80000 yes 1500000 yes 80000 no 10000 yes 9000 no 100000 yes 9000 no 2000000 yes 90000 no 10000000 90000 yes 10000 yes 9000 no 900000 no 10000 no 1000000 no 10000 no 1100000 no 100000 no 1500000 no no 100000

[0079] 2) Create a random forest regression model based on T;

[0080] 3) Given a test sample, the corresponding eigenvalues ​​are lob new =no,l new =1200000,de new =no, the random forest regression model created can predict the optimal parameter value to be 68740.0000.

[0081] Change scenarios include generating columns, splitting columns, changing non-partitioned tables to partitioned tables, adding columns, deleting columns, renaming columns, adjusting field lengths, parent-child tables, creating new tables, and migrating new table data.

[0082] Changes are performed in stages, and the four stages of admin_move_table can be independently controlled to achieve online changes to the table structure. The four stages are:

[0083] ① INIT phase. Executes the AMT INIT process and displays the results. The INIT process is the first phase of AMT and initializes the entire process. After INIT completes, the user is presented with the status of the INIT completion. The user can manually terminate the process.

[0084] ②COPY stage. Execute the COPY process of AMT and display the results of COPY. The COPY process is the second stage of AMT. Use COPY to copy the contents of the original database table to the new database table (target table) and display the COPY progress in real time. After the COPY is completed, the status will be displayed to the user, and the user can manually terminate the entire process. After the COPY is completed, determine whether the target table is a partitioned table. If it is a partitioned table, perform a partition skew check on the partition table, display the results of the check, and wait for the user's confirmation. After the user confirms, continue to execute subsequent operations.

[0085] ③REPLAY phase. Execute the AMT REPLAY process and display the results. The REPLAY process is the third phase of AMT. It uses REPLAY to copy the contents of the staging table to the new database table (the target table) and displays the REPLAY progress in real time. REPLAY can be called multiple times.

[0086] ④SWAP phase. Execute the final SWAP step of the AMT process. At this point, the table has been successfully modified and the results are displayed to the user.

[0087] Although the above describes the specific embodiments of the present invention in conjunction with the accompanying drawings, it is not intended to limit the scope of protection of the present invention. Those skilled in the art should understand that various modifications or variations that can be made by those skilled in the art on the basis of the technical solution of the present invention without any creative work are still within the scope of protection of the present invention.

Claims

1. A method for changing a table structure online, characterized in that: The method comprises the following steps: Step 1: Check the change semantics, change syntax, and change rules of the file directory change file; specifically: Define files of Rul, Sql, and Alk types. Rul files store parameter files required for database changes, including the change object, change type, and specific change description. The table change rule corresponding to the Rul file is a three-part string, each part separated by a colon (':'). The first part is the fixed rule, the second part is the rule type, and the third part is the specific rule. The third part is empty. Sql files are text files with the suffix sql. They store the DDL table creation statements of the target table in the database change, forming the change semantics. Alk files are text files with the suffix alk. They store the creation statements of indexes, references, and constraints in the database change, forming the change syntax. Step 2: Check the table creation DDL. If the table has not been created, the program will execute the table creation DDL. If the table has been created, the program will modify the table creation statement to create a new table. At the same time, it will change the field type according to the change rules specified in the RUL file and determine the change scenario. Step 3: Before making the change, check the table dependencies, constraints, and lock information. At the same time, generate the optimal parameters, which represent the number of rows to be replicated during each data commit. The specific implementation process of determining the optimal parameters: 1) Construct a training dataset T, which is represented as The matrix of represents the number of training set samples, express feature columns and 1 parameter value column, where The value is 4, and the feature columns are lob, l, de, and rows. lob indicates whether the table contains LOB type fields, and the value is yes or no; l indicates the number of rows in the table, and the value is a positive integer; de indicates whether the table is compressed, and the value is yes or no; rows indicates the value of the optimal parameter; 2) The model was constructed using the random forest regression model in Java. In the random forest regression model, the number of trees was assigned to 10 and the minimum number of samples per leaf node was 2. Step 4: The change is executed in stages. Specifically, the four stages of admin_move_table are independently controlled to achieve online table structure changes. The four stages are: INIT phase: This phase verifies whether data movement can occur and initializes various data required for subsequent data movement; COPY phase: This phase copies all data from the source table to the target table created in the INIT phase. The optimal parameters generated by the algorithm play a key role in this phase. If a user executes DML statements on the source table during this phase, the triggers created in the INIT phase will capture these statements and save any update, delete, and insert operations to the staging table. REPLAY phase: All data saved to the staging table in the COPY phase will be replayed to the target table in this phase; SWAP phase: After the Replay phase is completed or when the REPLAY_THRESHOLD value set in the protocol table is reached, the source table will be taken offline, and then DB2 exchanges the names of the source and target tables and brings the tables back online.

2. A system for changing table structure online, characterized in that: The system is used to implement the method according to claim 1, and the system includes: The initialization module, in response to the table structure change instruction, enters the change initialization phase and initializes the target table, staging table, and source table triggers required for data movement during the table change process; A data replication module, based on preset optimal parameters and change information, copies the data in the source table to the target table in batches; The table interaction module takes the source table offline after the rewrite is completed or the rewrite threshold is reached, exchanges the names of the source and target tables through the distributed database, and brings the target table online to complete the table structure change.

3. The system for changing table structure online according to claim 2, characterized in that: The system also includes a rewrite module. When an operation on the database table data occurs during the data replication process of the data replication module, the operation instruction is captured through the source table trigger, the operation is saved in the staging table, and the rewrite module is called to rewrite the data in the staging table into the target table.

Images