Normal Data Modify

Data Management Service (DMS) allows you to submit data change tickets to initialize data for a newly published project, clean up historical data, fix bugs, or run a test. This topic describes the Normal Data Modify feature provided by DMS, including the insert, update, delete, and truncate operations that you can perform to change data.

Procedure

1. Log on to the .

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 easily find the ticket in subsequent operations.
   Business Background (Required)	The purpose or objective of this data change. This helps reduce communication costs 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 the DMS administrator and database administrator (DBA), 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 databases 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 in the SQLConsole.
   SQL Statements for Change (Required)	The executable SQL statements for changing data. DMS automatically checks whether the syntax of the SQL statements is correct when you submit the ticket. If the syntax is incorrect, 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 as configured.

   • Before you submit the ticket for approval, you can change the settings and schedule that you have configured. You will not be able to change these configurations after you submit the ticket for approval.
   • By default, data change tickets are approved by database administrators (DBAs). For more information about approval rules for data change tickets, see SQL changes.

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 will be 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 have been executed will all be rolled back. Note that only data manipulation language (DML) statements can be rolled back and 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 have been executed will not be 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 SELECT clause that represents the affected rows, and the INSERT clause that is used for rollback. off: No backup file will be created. Default value: on.

   If you set Execution Method 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.

   To restore data, use the rollback statements in the downloaded backup file to submit another data change ticket to restore data.

   A backup file contains the following information:

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

Tips

• After you submit a data change ticket for approval, you can close the ticket no matter whether the ticket is approved or rejected. This helps avoid inadvertent execution after the ticket is approved.
• Approval rules are set by the DMS administrator and DBAs. If you are the 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. In 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 may want to perform database sharding and table partitioning. If you have configured logical databases, logical tables, and a routing algorithm in DMS, you only need to submit one ticket to perform database sharding and table partitioning. You do not need to submit a ticket for each physical table.
  • If you have configured a routing algorithm and you include the routing field in the SQL statement to perform database sharding and table partitioning, the routing algorithm will route the statement to the corresponding physical table for execution.
  • If you have not configured a routing algorithm, 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, the SQL statement will be executed on each table in each database one by one. This will take a longer period of time.