All Products
Search
Document Center

Data Transmission Service:SQL mapping

Last Updated:Nov 21, 2023

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 statements 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 the Add a PolarDB for Oracle database to ADAM section of this topic.

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.

      Parameter

      Description

      Task name

      The name of the SQL mapping task. We recommend that you specify a descriptive name that makes it easy to identify the task.

      PolarDB-O Instance

      The PolarDB for Oracle database for which you want to create the SQL mapping task.

      Source Database Type

      You 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 in progress, 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. On the page that appears, click Create Database.

  4. In the Create Database Record panel, configure the following parameters.

    Section

    Parameter

    Required

    Description

    N/A

    Archive Name

    Yes

    The name of the database. We recommend that you specify a descriptive name that makes it easy to identify the database.

    Database Type

    Yes

    Select PolarDB-O Engine from the drop-down list.

    Destination Database

    POLARDB-OInstance Region

    Yes

    The region in which your PolarDB for Oracle cluster resides.

    Instance Type

    Yes

    This parameter is displayed only if you set the POLARDB-OInstance Region parameter to a specific value such as UK (London) or Japan (Tokyo). Set the Instance Type parameter to Database with Public IP Address.

    POLARDB-OConnected VPC

    Yes

    The virtual private cloud (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-OInstances

    Yes

    Select 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 Address

    Yes

    You do not need to specify this parameter. After you select a PolarDB for Oracle cluster, the IP address is automatically obtained.

    Database Name

    Yes

    The name of the database that you want to add to ADAM.

    Encoding Method

    No

    Select the encoding method that corresponds to the character set of the database.

    Port Number

    Yes

    You do not need to specify this parameter. After you select a PolarDB for Oracle cluster, the port number is automatically obtained.

    Username

    Yes

    The username of the database account.

    Security Token

    Yes

    The password of the database account.

  5. Click Test Connectivity.

  6. If the test is successful, click Create.