All Products
Search
Document Center

Data Management:Change programmable objects by using stored routines

Last Updated:Apr 22, 2024

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 and third-party MariaDB databases.

  • PolarDB for PostgreSQL(Compatible with 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, choose Database Development > Data Change > Programmable Object Permissions.

    Note

    If you use the DMS console in simple mode, move the pointer over the 2023-01-28_15-57-17.png icon in the upper-left corner of the console and choose All Features > Database Development > Data Change > Programmable Object Permissions.

  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. Some parameters are described as follows:

    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.

    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. 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.