Data Management (DMS) allows you to use stored routines to manage data. A stored routine defines a standard process that is used to manage data. This topic describes how to change data by using stored routines in DMS.

Prerequisites

A MySQL database is added to DMS.
Note More types of databases are planned to support this feature.

Usage notes

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

Procedure

  1. Log on to the DMS console.
  2. In the top navigation bar, choose Data Plans > Data Changes > Programmable Object.
    Top navigation bar
  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. This 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 method that you want to use to submit the ticket 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 in the SQLConsole of the database.
    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.
    Note If the verification fails, you can perform operations based on the prompts. For example, you can apply for the required permission or modify the SQL script 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 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 Execute Immediately parameter and click Confirm Execution.
    Note If you set the Execution Method parameter to After Audit Approved, Auto Execute in Step 3, this step is automatically skipped.
  7. A task is generated for the ticket. Wait until the task is run.
    Note If you set the Execute Immediately parameter to Running immediately, the task is run immediately 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, 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.
    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.