Updating Statements in Batches

Scenarios

Bulk Statement Update allows you to quickly modify the scripts or SQL statements that fail to be migrated in batch. You can search for objects in batches by text or regular expression.

• You can also view the modification results or roll back the last modification. For details, see Viewing History Records.
• You can also change the scripts or SQL syntax that fail to be converted or migrated one by one. For details, see Modifying Objects.

The modified statements will run on the target database. Do not use statements that cause the drop of other tables and object types, or statements that cause malicious activities such as consuming too much CPU, memory and other side effects over target database.

Prerequisites

The migration and verification is complete, and some items fail to be verified.

Procedure

1. Log in to the UGO console.
2. In the navigation pane on the left, choose Schema Migration > Object Migration. The migration project list is displayed.
3. Locate the project to be migrated and click Migrate in the Operation column. On the displayed page, click the Object Correction tab.
4. Click Bulk Statement Update.
5. Locate an object and click View SQL on the Operation column. The object details page is displayed.

   You can view the schema name, object type, object name, split object name, and migration status on the page.

6. View the error information. If any, modification suggestions are displayed.
   Figure 1 Object details
   

7. Click the Object Correction tab. The SQL statements of the source and target databases are displayed.
   Click next to Source or Target to copy the SQL statements to the clipboard.

   Figure 2 Object comparison
   

8. Copy a SQL keyword or character string, return to the batch modification page, and configure parameters as needed/
   Figure 3 Batch modification
   

   Table 1 Parameter description

   Parameter	Mandatory	Description
   Search Type	Yes	Normal: simple search text type Regular Expression: regular expression search text type NOTE: Incorrect use of regular expressions may cause unpredictable changes to SQL statements. Therefore, exercise caution when using regular expressions. For details about regular expression specifications, see Java Regex.
   SQL Key Words	Yes	Search for keywords obtained in the Object Details page. Click View SQL in the Operation column to go to the Object Details page. For example, if you select Regular Expression and enter DEFAULT* as a keyword, the system searches for all statements with the structure DEFAULT Any SQL statement.
   Error String 1	No	Search for migration error messages obtained in the Object Details page. Click View SQL in the Operation column to go to the Object Details page.
   Error String 2	No	Further search for migration error messages obtained in the Object Details page. Click View SQL in the Operation column to go to the Object Details page.

   

   • After the parameters are configured, the objects that meet search criteria are displayed in the right pane.
   • All keywords can be viewed in the code display area, as shown in Figure 2. All error strings can be viewed in Migration Errors area, as shown in Figure 1.
   • If the user connected to the target database is different from the schema owner, UGO will change the schema owner name to be the same as the user name.

9. Click Search. The objects that meet the search criteria are displayed on the right pane.
10. Enter the modified SQL statement in the Replace with text box and click Replace.
    

    The modified statements will run on the target database. Do not use statements that cause the drop of other tables and object types, or statements that cause malicious activities such as consuming too much CPU, memory and other side effects over target database.

11. After the replacement is complete, view the modified SQL information by referring to Viewing History Records.
    

    • On the Object Correction page, locate an object whose Migration Status is Manual and click View SQL to check whether the modification is successful.
    • You can roll back to the latest modification record. For details, see Viewing History Records.

Viewing History Records

1. Log in to the UGO console.
2. In the navigation pane on the left, choose Schema Migration > Object Migration. The migration project list is displayed.
3. Locate the project to be migrated and click Migrate in the Operation column. On the displayed page, click the Object Correction tab.
4. Choose Batch Statement Update > History.
   Figure 4 Viewing history records
   

   Table 2 Parameter description

   Parameter	Description
   History ID	The number of modification times is displayed in descending order.
   Start Time	Start time of the modification Example: 2021/09/15 16:28:15 GMT+08:00.
   End Time	End time of the modification Example: 2021/09/15 16:28:16 GMT+08:00.
   SQL Key Words	SQL keywords to be searched for. Place the cursor on to view the search criteria.
   Replacing SQL	Scripts or keywords to replace the original SQL statements.
   Operations	Roll Back: You can roll back the last modification. View List: You can view the history ID, schema, object name, and object type. Click View SQL to view the modified SQL statement.