Change programmable objects by using stored routines

Limits

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

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. Complete the Data Change Ticket Application wizard. The following table describes the parameters involved.

   Parameter	Description
   Database	The 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 required iteration and click Associate in the Actions column.
   Reason Category	The reason for the change operation. 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. 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.
   Attachments	The file used as an attachment of the ticket to add additional information about the change.

4. Click Submit.
   DMS prechecks the ticket settings.

   Note If the precheck fails, modify the ticket as prompted and then submit the ticket again. Repeat this process until the ticket passes the precheck.
5. After the precheck is passed, 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 application 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 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 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.