The SQL mapping feature of Advanced Database & Application Migration (ADAM) can automatically capture SQL statements that do not conform to the PolarDB for Oracle syntax in a PolarDB for Oracle database. Then, the SQL mapping feature can intelligently convert the incorrect SQL statements by mapping the incorrect SQL statement to correct SQL statements. For SQL statements that cannot be intelligently converted, you can manually configure mapping to convert these SQL statements. This feature can prevent a PolarDB for Oracle database from returning incorrect results to applications due to incorrect SQL statements. This topic describes how to create an SQL mapping task.

Prerequisites

  • The database for which you want to enable the SQL mapping feature is a PolarDB for Oracle database.
  • The polar_sql_mapping extension is created in the database. For more information, see polar_sql_mapping.
  • The database is added to ADAM by using the database management feature. For more information, see Add a PolarDB for Oracle database to ADAM.

Feature description

The SQL mapping feature can not only capture existing incorrect SQL statements sent by an application but can also capture incremental incorrect SQL statements that are generated after the application runs for a period of time. You must enable SQL mapping again to capture and convert incremental incorrect SQL statements.

Scenarios

In scenarios in which an application sends an incorrect SQL statement and the statement cannot be immediately modified, you can use the SQL mapping feature to map the incorrect SQL statement to a correct statement.

Procedure

  1. Log on to the ADAM console.
  2. Create an SQL mapping task.
    1. In the left-side navigation pane, click SQL Mapping.
    2. Click New SQL-Mapping task.
    3. In the New SQL-Mapping task panel, configure the following parameters.
      ParameterDescription
      Task NameEnter a name for the SQL mapping task. We recommend that you specify a descriptive name that is easy to identify.
      PolarDB-O InstanceSelect the PolarDB for Oracle database for which you want to create the SQL mapping task.
      Source Database InstanceYou can select only Oracle.
    4. Click Create.
      After the SQL mapping task is created, you are navigated to the SQL Mapping page, on which the SQL mapping task is displayed. The task is in the Pending state. You need to wait about 3 seconds until the task is complete and refresh the page. The task enters the Successful state.
      Note When the SQL mapping task is running, incorrect SQL statements are automatically captured.
  3. After the task is complete, click Details in the Actions column to view the details of the incorrect SQL statements that are captured and configure mapping.
  4. Optional:Click the Error SQL List tab to view the details of the incorrect SQL statements, including the statements, error messages, and the number of times that each statement is executed.
  5. Click the SQL Mapping Information List tab.
  6. Perform SQL mapping.
    1. On the tab, click Start Intelligent Conversion. The conversion results are displayed, and the converted SQL statements are marked.
      Note ADAM can automatically convert SQL statements that are incompatible with the specified database into compatible SQL statements and create mapping information.
    2. Optional:If an incorrect SQL statement cannot be intelligently converted, you can click Configure Mapping to manually convert the incorrect SQL statement.
      1. Click Configure Mapping in the Actions column.
      2. In the Configure Mapping Information dialog box, change the incorrect SQL statement to a correct SQL statement.
      3. Click Submit.

Add a PolarDB for Oracle database to ADAM

  1. Log on to the ADAM console.
  2. In the left-side navigation pane, click Database Management.
  3. Click Create Database.
  4. In the Create Database Record panel, configure the following parameters.
    SectionParameterRequiredDescription
    N/AArchive NameYesEnter a name for the database. We recommend that you specify a descriptive name that is easy to identify.
    Database TypeYesSelect PolarDB-O Engine from the drop-down list.
    Destination DatabasePOLARDB-OInstance RegionYesSelect the region in which your PolarDB for Oracle cluster resides.
    POLARDB-OConnected VPCYesSelect the VPC in which your PolarDB for Oracle cluster resides You can log on to the PolarDB console and view the VPC information on the details page of the cluster.
    POLARDB-OInstancesYesSelect your PolarDB for Oracle cluster from the drop-down list. .After you configure the POLARDB-OConnected VPC parameter, the system searches for the clusters that reside in the VPC and displays the clusters in the drop-down list.
    POLARDB-OHost IP AddressYesYou do not need to configure this parameter. After you select a PolarDB for Oracle cluster, the IP address is automatically obtained.
    Database NameYesEnter the name of the database that you want to add to ADAM.
    Encoding MethodNoSelect the encoding method that corresponds to the character set of the database.
    Port NumberYesYou do not need to configure this parameter. After you select a PolarDB for Oracle cluster, the port number is automatically obtained.
    UsernameYesEnter the username of the database account.
    Security TokenYesEnter the password of the database account.
  5. Click Test Connectivity.
  6. If the test is successful, click Create.