Common data change

Procedure

1. Log on to the DMS console.
2. In the top navigation bar, choose Data Plans > Data Changes > Normal Data Modify.
3. On the Data Change Ticket Application page, set the parameters as described in the following table. Then, click Submit.
   

   Parameter	Description
   Reason Category	Required. The reason for this data change, which helps you find the ticket in subsequent operations.
   Business Background	Required. The purpose or objective of this data change. This helps reduce the cost of communication and accelerate the approval process.
   Change Stakeholder	Optional. The stakeholders of the data change. All stakeholders can view the ticket details and assist R&D engineers in advancing the approval process. Except for DMS administrators and database administrators (DBAs), users irrelevant to the ticket are not allowed to view the ticket details.
   Execution Method	Required. The way that you want the ticket to be submitted for execution. The default value is After Audit Approved, Order Submitter Execute. You can also set this parameter to After Audit Approved, Auto Execute or Last Auditor Execute as needed.
   Database	Required. The database on which you have the change permission. You cannot submit a data change ticket if you only have the permission to query data in the database or change data in tables.
   Affected Rows	Required. The estimated number of data rows to be affected by this data change. To obtain the actual number of affected rows, you can use the COUNT function in SQL statements on the SQL Console page.
   SQL Statements for Change	Required. The executable SQL statements for changing data. DMS checks whether the syntax of the SQL statements is correct when you submit the ticket. If the syntax is invalid, you cannot submit the ticket.
   SQL Statements for Rollback	Optional. The executable SQL statements for rolling back the data change that is performed by the SQL statements for changing data.

4. After you submit the ticket, wait until the system verifies that your configurations are valid.
5. Submit the ticket for approval. After the ticket is approved, a task is generated based on the ticket.
   Note

   • Before you submit the ticket for approval, you can change the settings and schedule that you configured. You are not able to change these configurations after you submit the ticket for approval.
   • By default, data change tickets are approved by DBAs. For more information about approval rules for data change tickets, see SQL Correct.
6. Click Execute Change. In the Task Settings dialog box, set the control items as described in the following table. Then, click Confirm Execution.

   Control item	Control value	Description
   Execute Immediately	on or off (If you set this item to off, you must specify a time for the task to be run.)	Valid values: on: The task is immediately run after the task is submitted. off: The task is run at the specified time. Default value: on.
   Transaction Control	on or off	Valid values: on: If an SQL statement fails to be executed, the statements that are executed are rolled back. Note that only data manipulation language (DML) statements can be rolled back. Data definition language (DDL) statements cannot be rolled back. off: The SQL statements are executed one by one. If an SQL statement fails to be executed, the task execution is stopped. The statements that are executed are not rolled back. Default value: off.
   Data Backup	on or off	Valid values: on: The backup file to be created contains the update and delete operations that are performed during this data change. The backup file also contains the `SELECT` clause that represents the affected rows and the INSERT clause that is used for rollback. off: No backup file is created. Default value: on.

   Note If you set the Execution Method parameter to After Audit Approved, Auto Execute in Step 3, this step is automatically skipped.
7. After the task is run, you can click Details in the Actions column to view operations logs that contain detailed information such as SQL statements, execution duration, and scheduling details.
   
8. If this data change does not meet your expectation and you want to restore data, click Download Backup in the Actions column. The backup file is downloaded.
   Note To restore data, use the rollback statements in the downloaded backup file to submit another data change ticket.

   A backup file contains the following information:

   • The original SQL statements that are executed to change data.
   • The SELECT clause in the original SQL statements.
   • The rollback statements that are used to roll back data changes.

Modify valid values of parameters in a data change ticket

You can modify valid values of the Reason Category and Execution Method parameters in a data change ticket. To modify the values, perform the following steps:

1. Log on to the DMS console.
2. In the top navigation bar, choose System Management > Configuration.
   Modify valid values of the Reason Category parameter
   1. On the Configuration page, find the Data Change reason classification parameter and click Change in the Actions column.
      
   2. In the Change Parameter Configuration dialog box, enter one or more valid values in the Value field and click Confirm Change.
      Note Each valid value that you enter must be in the following format: {"key":"Value name","value":"Content to be displayed"}.

      For example, you can enter two valid values in the Value field, as shown in the following figure.When you set the Reason Category parameter in a data change ticket, you can select one of the two valid values, as shown in the following figure.
   Modify valid values of the Execution Method parameter
   1. On the Configuration page, find the How data changes are performed parameter and click Change in the Actions column.
      
   2. In the Change Parameter Configuration dialog box, enter one or more valid values in the Value field and click Confirm Change.
      

      Note You can enter the following values for the Execution Method parameter:

      • COMMITOR, which is displayed as "After Audit Approved, Order Submitter Execute".
      • AUTO, which is displayed as "After Audit Approved, Auto Execute".
      • LAST_AUDITOR, which is displayed as "Last Auditor Execute".

      You can use any combination of these values, but you cannot define custom values.

Tips

• After you submit a data change ticket for approval, you can close the ticket whether the ticket is approved or rejected. This helps avoid accidental execution after the ticket is approved.
• Approval rules are set by DMS administrators and DBAs. If you are a DMS administrator or a DBA, choose System Management > Security > Security Rules in the top navigation bar of the DMS console. On the page that appears, you can set approval rules for data change tickets. We recommend that you also use tickets for data changes in the test environment. This way, affected rows can be checked and a backup file can be created for each data change. To ensure high R&D efficiency, you can specify that data change tickets do not require approval in the test environment.
• You can perform database sharding and table partitioning. If you have configured logical databases, logical tables, and a routing algorithm in DMS, you can submit one ticket to perform database sharding and table partitioning. You do not need to submit a ticket for each physical table.
  • Assume that you configure a routing algorithm and you include the routing field in the SQL statement to perform database sharding and table partitioning. In this case, the routing algorithm routes the statement to the corresponding physical table for execution.
  • Assume that you do not configure a routing algorithm, or the routing field is not included in the SQL statement, or the data type of the routing field does not match the data type that is specified in the routing algorithm. In these cases, the SQL statement is executed on each table in each database one by one. This process takes a longer period of time.