Change common data

Procedure

1. Log on to the DMS console.
2. In the top navigation bar, move the pointer over the More icon and choose Data Plans > Normal Data Modify.
3. In the Data Change Ticket Application dialog box, 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 of the data change. All specified stakeholders can view the ticket details and assist in the approval process. Irrelevant users other than DMS administrators and database administrators (DBAs) are not allowed to 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 permissions 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 tab.
   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 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 change the settings and schedule that you have configured. You are not allowed 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 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. Running immediately: DMS immediately runs the task after you submit the task. Schedule: DMS runs the task at a specified time. Default value: Running immediately.
   Transaction Control	Specifies whether to enable transaction control. on: If an SQL statement fails to be executed, all the executed data manipulation language (DML) statements are 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 stops running. The executed statements are not rolled back. Default value: off.
   Data Backup	Specifies whether to back up data. 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. Default value: on.

7. After the task is run, you can click Details to view operations logs. The operations logs contain detailed information such as SQL statements, execution duration, and scheduling details.
   
8. If the data change does not work out 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 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.
2. In the top navigation bar, move the pointer over the More icon and choose System > Configuration.
   Change valid values of the Reason Category parameter
   1. Find the Data Change reason classification parameter and click Change on the right.
      
   2. 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 in the backend","value":"Value to be displayed in the console"}.

      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.
   Change valid values of the Execution Method parameter
   1. Find the How data changes are performed parameter and click Change on the right.
      
   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 any 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 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 > Security > Security Rules in the top navigation bar of the DMS console to set 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 does not work out 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 table.
  • Assume that 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.
  • Assume that you do not configure a routing algorithm, no 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 is executed on each table in each database one by one. This process takes a longer period of time.