Advanced Database & Application Migration (ADAM) SQL Adapter is a forward proxy service developed based on the PostgreSQL communication protocol. This topic describes how to use SQL Adapter to convert incompatible SQL statements.

Description

SQL Adapter is provided to convert incompatible SQL statements that are migrated from Oracle databases to PolarDB for Oracle or PolarDB for PostgreSQL clusters. You can use SQL Adapter in the following scenarios:
  • Convert incompatible SQL statements that are migrated from Oracle databases to PolarDB for Oracle or PolarDB for PostgreSQL clusters in real time.
  • Asynchronously record all incompatible SQL statements during data migration.
  • Modify SQL statements that cannot be automatically converted.

Prerequisites

Procedure

  1. Apply for the permissions to use ADAM SQL Adapter.
    1. Log on to the Data Management (DMS) console V5.0.
    2. In the top navigation bar, move the pointer over DTS. Choose Heterogeneous Database Migration(ADAM) > Migration Lab.
    3. In the ADAM SQL Adapter section, click Apply.
    4. On the page that appears, specify parameters such as Company Name, Phone Number, and Application Purpose. Then, click Submit.
  2. Create an adapter.
    1. Log on to the Data Management (DMS) console V5.0.
    2. In the top navigation bar, move the pointer over DTS. Choose Heterogeneous Database Migration(ADAM) > Migration Lab.
    3. In the ADAM SQL Adapter section, click Details.
    4. On the ADAM Adapter page, click Create Adapter in the upper-left corner.
    5. Configure the parameters described in the following table.
      ParameterDescription
      VPC RegionThe region where the VPC is deployed. The adapter is created in the VPC.
      Valid values:
      • cn-hangzhou
      • cn-beijing
      VPC ZoneThe zone where the VPC is deployed. The adapter is created in the VPC.
      Valid values:
      • cn-hangzhou-b: The VPC is deployed in zone B of the China (Hangzhou) region.
      • cn-beijing-b: The VPC is deployed in zone B of the China (Beijing) region.
      Note ADAM SQL Adapter is available only in zone B.
      vSwitch NameThe vSwitch that is created in the zone within the region where the VPC is deployed. If no vSwitches are available, create one in the VPC console. For more information, see Create and manage a vSwitch.
      Source Database ProfileThe source database profile. If no profiles are available, create one in the ADAM console. For more information, see Manage a database profile.
    6. Click Create.
  3. Configure the destination database.
    1. On the ADAM Adapter page, find the adapter and click Configure Destination Database in the Actions column.
    2. Configure the parameters described in the following table.
      ParameterDescription
      Instance RegionThe region where the destination cluster resides. The value of this parameter cannot be changed. It is the same as that of the adapter that you create.
      VPCThe VPC where the destination PolarDB cluster resides.
      Instance IDThe ID of the destination PolarDB cluster.
      Hostname or IP AddressThe IP address that is used to connect to the destination cluster. The value of this parameter is automatically acquired and cannot be changed.
      Port NumberThe service port number of the destination cluster. The value of this parameter is automatically acquired and cannot be changed.
      Database NameThe name of the destination database in the PolarDB cluster.
      Database AccountThe account of the destination database.
      Database PasswordThe password of the destination database.
      CurrentSchemaThe schema name of the destination database.
    3. Click Test Connectivity. After the Connection test succeeded message is displayed, click Create.
  4. Obtain the IP address and port number of the adapter.
    1. Log on to the Data Management (DMS) console V5.0.
    2. In the top navigation bar, move the pointer over DTS. Choose Heterogeneous Database Migration(ADAM) > Migration Lab.
    3. In the ADAM SQL Adapter section, click Details.
    4. On the ADAM Adapter page, view the IP address and port number of the adapter.
  5. Modify the URL of the database to which your application is connected.
    jdbc:polardb://172.18.100.6:8888/polardb_test
    ParameterExampleDescription
    URL Prefixjdbc:polardb://The prefix of the URL. All URLs of PolarDB databases are prefixed with jdbc:polardb://.
    IP Address172.18.100.6The IP address of the adapter. For more information about how to obtain the IP address, see the "Obtain the IP address and port number of the adapter" section of this topic.
    Port Number8888The port number of the adapter. For more information about how to obtain the port number, see the "Obtain the IP address and port number of the adapter" section of this topic.
    Database Namepolardb_testThe name of the database to which your application is connected.
    Note You do not need to modify the database account or password.
  6. View the SQL compatibility of the adapter and the information about SQL statement conversion.
    1. On the ADAM Adapter page, find the adapter and click Details in the Actions column.
    2. View the SQL compatibility of the adapter and the information about SQL statement conversion.
  7. Configure custom rules for SQL statement conversion.
    For SQL statements with which ADAM SQL Adapter is not compatible, click Create Rule in the upper-left corner.
    ParameterDescription
    Rule TypeThe type of the custom SQL conversion rule.
    Valid values:
    • Text Replacement
    • Regular Expression Replacement
    Note If you select Regular Expression Replacement, you must use regular expressions with caution. Malicious regular expressions may exhaust the resources of the adapter. ADAM SQL Adapter does not ensure high availability.
    Text to MatchThe text or regular expression to be replaced.
    Text After ReplacementThe text or regular expression after replacement.
    Effective ScopeThe scope to which the rule applies.
    Valid values:
    • All SQL Statements
    • Specified SQL Statements
    Incompatible SQL StatementsThe specified SQL statements to which the rule applies. This parameter is displayed only when you set Effective Scope to Specified SQL Statements. Specify SQL statements to which the rule applies.