Use stored routines to change data

Usage notes

Stored functions and stored procedures are the only programmable objects that DMS allows you to use to change data.

Procedure

1. Go to the DMS console V5.0.
2. In the top navigation bar, click Database Development. In the left-side navigation pane, choose Data Change > Programmable Object.
3. On the Data Change Ticket Application tab, set the parameters that are described in the following table. Then, click Submit.
   

   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.
   Database	Required. The database on which you have the change permission. You cannot submit a data change ticket if you have only 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 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 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.
   Note If the verification fails, you can perform operations based on the prompts. For example, you can apply for the required permissions or modify the SQL scripts in the ticket, and then submit the ticket again. Repeat this process until the ticket passes the verification.
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. Set the Execute Immediately parameter and click Confirm Execution.
   
7. Wait until the task is run.
   Note If you set the Execute Immediately parameter to Running immediately, the task is immediately run after the task is generated. If you set the Execute Immediately parameter to Schedule and specify a point in time, the task will be run at the specified point in time.
8. After the task is run, click Details to view operations logs. The operations logs contain detailed information such as SQL statements, execution duration, and scheduling details.
   Note

   • Make sure that the business logic of the stored routines that you want to use meets your business requirements. DMS checks the key elements but not the business logic of the stored routines.
   • When you use a stored routine to change data, you cannot specify characteristics for SQL transactions in the stored routine. Before you use a stored routine to change data in a database, you cannot back up the database by using an image.