Data Management (DMS) allows you to use stored routines to change programmable objects. DMS also provides a standardized management process to control the change operations. This topic describes how to change programmable objects by using stored routines in DMS.

Prerequisites

The following types of databases are supported:
  • MySQL: ApsaraDB RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, and third-party MySQL databases
  • SQL Server: ApsaraDB RDS for SQL Server, MyBase for SQL Server, and third-party SQL Server databases
  • PostgreSQL: ApsaraDB RDS for PostgreSQL, PolarDB for PostgreSQL, MyBase for PostgreSQL, AnalyticDB for PostgreSQL, and third-party PostgreSQL databases
  • MariaDB: ApsaraDB for MariaDB TX and third-party MariaDB databases
  • PolarDB for Oracle

Limits

Stored functions and stored procedures are the only programmable objects you can change in DMS.

Procedure

  1. Log on 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. In the Apply step on the Data Change Tickets page, set the parameters for submitting a data change ticket. The following table describes the parameters.
    Note After you select a database, the parameters including Associated Iterations, Reason Category, Business Background, and Execution Method appear.
    Parameter Description
    Database The database for which you want to submit the ticket. You must select a database on which you have the change permissions.
    Associated Iterations The iteration to be associated with the project. To select an iteration, click Select/Change Iteration. In the dialog box that appears, find the desired iteration and click Associate in the Actions column.
    Reason Category The reason for the data change. This helps you find the ticket in subsequent operations.
    Business Background The purposes or objectives of the change operation. This reduces unnecessary communication.
    Execution Method The execution method of the ticket. Valid values:
    • After Audit Approved, Order Submitter Execute
    • After Audit Approved, Auto Execute
    • Last Auditor Execute
    SQL Statements for Change The SQL statements used for the change operation. DMS checks whether the syntax of the SQL statements is valid when you submit the ticket. If the syntax is invalid, you cannot submit the ticket.
    SQL Statements for Rollback
    • Text: Enter the SQL statements used for rollback in the code editor.
    • Attachment: Click Upload a file to upload the file that stores the SQL statements used for rollback.
      Note
      • The file can be a TXT, ZIP, or SQL file and must not exceed 15 MB in size.
      • The SQL statements that you specify for this parameter are used to roll back the change operation.
    Change Stakeholder The stakeholders involved in the change operation. All the specified stakeholders can view the ticket details and participate in the approval process. Irrelevant users except for DMS administrators and database administrators (DBAs) have no access to the ticket details.
    Attachments The file used as an attachment of the ticket to provide additional information about the change.
  4. Click Submit.
    DMS prechecks the ticket settings.
    Note If the ticket fails the precheck, modify the ticket as prompted and submit the ticket again. Repeat this process until the ticket passes the precheck.
  5. After the ticket passes the precheck, click Submit for Approval.
    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 reviewed by DBAs. For more information about the approval rules for data change tickets, see SQL Correct.
  6. Optional:After the ticket is approved, click Execute Change.
    In the Task Settings dialog box, set the Execution Strategy parameter.
    • Running immediately: After you click Confirm Execution, the task is immediately run.
    • Schedule: You can specify the time when the task starts to be run in DMS.
  7. After the task is run as expected, click Details to view the operation logs. The operation logs contain the 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 programmable objects, you cannot specify characteristics for SQL transactions in the stored routine. Before you use a stored routine to change programmable objects in a database, you cannot back up the database by using an image.