Change regular data

Procedure

1. Log on to the DMS console V5.0.
   Note To switch to the previous version of the DMS console, click the icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
2. In the top navigation bar, click Database Development. In the left-side navigation pane, choose Data Change > Normal Data Modify.
   Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose Data Plans > Change > Normal Data Modify.
3. On the Data Change Ticket Application page, set the parameters in the Apply step as required and click Submit. The following table describes the parameters.
   

   Table 1. Parameters for data change

   Parameter	Description
   Reason Category	Required. The reason for the data change. This helps you find the ticket in subsequent operations.
   Business Background	Required. The purpose or objective of the data change. This reduces unnecessary communication.
   Change Stakeholder	Optional. The stakeholders involved in the data change. All the specified stakeholders can view the ticket details and assist in the approval process. Irrelevant users other than DMS administrators and database administrators (DBAs) cannot view the ticket details.
   Execution Method	Required. The way in which you want the ticket to be submitted for execution. Valid values: After Audit Approved, Order Submitter Execute, After Audit Approved, Auto Execute, and Last Auditor Execute. Default value: After Audit Approved, Order Submitter Execute.
   Database	Required. The database whose data you want to change. Select a database on which you have change permissions. You cannot submit a data change ticket if you have only the permissions to query data in the database or change data in tables.
   Affected Rows	Required. The estimated number of data rows that may be affected by this data change. To obtain the actual number of affected rows, you can use the COUNT function in SQL statements and execute the SQL statements on the SQLConsole tab.
   SQL Statements for Change	Required. The SQL statements that can be executed to change 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 SQL statements that can be executed to roll back the data change.

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

   • Before you submit the ticket for approval, you can modify the settings and schedule that you have configured. You cannot modify these configurations after you submit the ticket for approval.
   • By default, data change tickets are approved by DBAs. For more information about the approval rules for data change tickets, see SQL Correct.
6. Click Execute Change. In the Task Settings dialog box, set the parameters as required and click Confirm Execution. The following table describes the parameters.
   Note If you set the Execution Method parameter to After Audit Approved, Auto Execute in the Apply step, this step is automatically skipped.

   Parameter	Description
   Execute Immediately	Specifies whether to run the task immediately or at a scheduled time. Valid values: Running immediately: DMS runs the task immediately after you submit the task. Schedule: DMS runs the task at the scheduled time that you specify. Default value: Running immediately.
   Transaction Control	Specifies whether to enable transaction control. Valid values: on: If an SQL statement fails to be executed, all the executed DML statements in the same transaction are rolled back. DDL statements cannot be rolled back. off: One SQL statement is executed at a time. If an SQL statement fails to be executed, the transaction is stopped. The other executed SQL statements in the same transaction are not rolled back. Default value: off.
   Data Backup	Specifies whether to back up data. Default value: on. Valid values: on: DMS generates INSERT scripts to back up the data that will be affected when UPDATE or DELETE statements are executed. off: DMS does not generate backup files. Note Data backup is supported only for UPDATE and DELETE statements. You cannot back up data for MongoDB and Redis databases.

7. After the task is run as expected, click Details to view operation logs. The operation logs contain detailed information such as SQL statements, execution duration, and scheduling details.
   
8. If the data change is not as expected and you want to restore data, click Download Backup. 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

Change the valid values of parameters in a data change ticket

To customize the approval process, you can change the valid values of the Reason Category and Execution Method parameters in a data change ticket.

1. Log on to the DMS console V5.0.
   Note To switch to the previous version of the DMS console, click the icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
2. Change the valid values of the Reason Category parameter.
   1. Find the Data Change reason classification parameter and click Change in the Actions column.
   2. Enter one or more valid values for the Value parameter as required and click Confirm Change.
      Note Each valid value must be in the following format: {"key":"Value in the backend","value":"Value to be displayed in the console"}.

      For example, you can enter two valid values for the Value parameter, 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.
3. Change the valid values of the Execution Method parameter.
   1. Find the How data changes are performed parameter and click Change in the Actions column.
   2. Enter one or more valid values in the Value field and click Confirm Change.
      

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

      • COMMITOR: After a data change ticket is approved, the user who submits the ticket runs the data change task.
      • AUTO: After a data change ticket is approved, the data change task is automatically run.
      • LAST_AUDITOR: After a data change ticket is approved, the last approver runs the data change task.

      The Value field can be set only to the preceding values for the Execution Method parameter. You can use a combination of these values.

Tips

• After you submit a data change ticket for approval, you can close the ticket when the ticket is approved or rejected. This helps prevent accidental execution after the ticket is approved.
• Approval rules are configured by DMS administrators and DBAs. If you are a DMS administrator or a DBA, click the Security and Specifications tab in the top navigation bar of the DMS console. Then, click Security Rules in the left-side navigation pane to configure approval rules for data change tickets. We recommend that you submit 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. This allows you to restore data when the data change is not as expected. To ensure high R&D efficiency, you can specify that no approval is required for data change tickets in the test environment.
• You can shard databases and partition tables. If you have configured logical databases, logical tables, and routing algorithms in DMS, you can submit one ticket to shard databases and partition tables at the same time. You do not need to submit a ticket for each physical database or table.
  • If you configure a routing algorithm and include a routing field in the SQL statement that is used to shard databases and partition tables, the routing algorithm routes the statement to the corresponding physical table for execution.
  • If you do not configure a routing algorithm, no routing field is 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, the SQL statement is executed on each table in each database one by one. This process takes a longer period of time.