Assess the compatibility with SQL statements|V4.2.4|OceanBase Migration Assessment| docs|Distributed Database

OceanBase Migration Assessment (OMA) allows you to assess the compatibility with SQL statements and PL/SQL statements.

Background

Run start.sh with reference to the sample command in this topic. For more information about the parameters in the script, see Product form. In Microsoft Windows, replace sh bin/start.sh with start.bat.

Connect to an Oracle database to assess the compatibility with its SQL statements

Before you connect to an Oracle database and assess the compatibility with SQL statements, if you use OMA V3.2.1-BP9 and later, you can configure the OracleCheckerConfig.json configuration file in the config/ directory of the OMA installation directory to specify the schemas and tables to be skipped. The following example shows the content of a OracleCheckerConfig.json file:

{
  "skipSchemaPattern": "",
  "skipSchemaList": [],
  "skipTablePattern": "SYS_EXPORT*|ET\\$*|GV\\$*|V\\$",
  "skipTableList": ["x$kccic", "user$"],
  "skipHintPattern":"",
  "skipHintList": []
}

Parameter	Description
skipSchemaPattern	The regular expression of schemas to be skipped.
skipSchemaList	The list of schemas to be skipped.
skipTablePattern	The regular expression of tables to be skipped.
skipTableList	The list of tables to be skipped.
skipHintPattern	The hints. SQL statements that contain the specified hints are skipped. Usually, these SQL statements are executed by Oracle database tools.
skipHintList	The hints. SQL statements that contain the specified hints are skipped. Usually, these SQL statements are executed by Oracle database tools.

The searching and matching process is not case-sensitive. If an SQL statement contains the specified schema, table, or hint, OMA does not assess the statement and tags it as compatible.

At present, OMA pulls SQL and PL/SQL statements from the V$SQL view of the Oracle database for compatibility assessment. The assessment process and reports are the same as those for Oracle objects.

sh bin/start.sh \
# The task name, which can be set to any value.
--name test_11g \           
# The analysis mode.    
--mode ANALYZE \          
# The data source. COLLECT indicates that the data is collected from a database.                      
--from-type COLLECT \    
# The assessment method. For an Oracle database, use the SOURCE_TARGET mode.                       
--evaluate-mode SOURCE_TARGET \             
# The type of the source database.    
--source-db-type ORACLE \           
# The version of the source database.            
--source-db-version 11g \      
# The address of the source database.                 
--source-db-host xxx.xxx.xxx.xxx \   
# The port of the source database.              
--source-db-port port \         
# The username of the source database.                
--source-db-user username \         
# The password of the source database.               
--source-db-password password \    
# The service name of the source database. This parameter can be replaced with --source-db-sid, which indicates the SID.                
--source-db-service-name orcl11g.us.oracle.com \             
# The type of the destination database.               
--target-db-type OBORACLE \                     
# The version of the destination database. 2.2.50 indicates V2.2.5x, 2.2.70 indicates V2.2.7x, and 3.1.20 indicates V3.1.x., 4.2.x supports 4.2.0, 4.2.1, and 4.2.2.
--target-db-version 4.2.0 \            
# The SQL statements to be collected. You can specify whether to collect SQL statements from the V$SQL or SQLArea view.        
--scan-sql "\"SELECT sql_fulltext FROM v\\\$sql where rownum < 10\""

Notice

When you use the syntax --scan-sql ""SELECT sql_fulltext FROM v\$sql where rownum < 10"", note the following items:

You must have the required privileges on the corresponding view.
The content specified in the sql_fulltext field will be collected. Therefore, SELECT sql_fulltext is required.
The dollar sign ($) in v$sql will be escaped multiple times. Therefore, enter three backslashes (\\\) before it.

Assess the compatibility with SQL statements in a TEXT file

OMA can obtain SQL statements from a TEXT file for assessment. The assessment process and reports are the same as those for Oracle objects.

Multiple SQL statements in a TEXT file must be separated with double dollar signs ($$). Example:

select biz_param_code , biz_param_val , biz_param_chn  FROM biz_parameter WHERE biz_param_code = 'b0';
$$
select biz_param_code , biz_param_val , biz_param_chn INTO :b0 , :b1 , :b2 FROM biz_parameter WHERE biz_param_code = :b0
$$
select biz_param_code , biz_param_val , biz_param_chn  FROM biz_parameter WHERE biz_param_code = 'b0';
$$

Assessment in Oracle mode

sh bin/start.sh \
# The task name, which can be set to any value.
--name test \
# The analysis mode.
--mode ANALYZE \
# The data source. You can also set this parameter to MYBATIS or IBATIS.
--from-type TEXT \  
# The assessment mode.           
--evaluate-mode SOURCE_TARGET \
# The path of the source file.
--source-file "/home/admin/oma/1table.sql" \
# The type of the source database.
--source-db-type ORACLE \
# The version of the source database.
--source-db-version 11g \
# The schema to be assessed.
--schemas DEFAULT \
# The type of the destination database.
--target-db-type OBORACLE \
# The version of the destination database
# 2.2.50 indicates V2.2.5x, 2.2.70 indicates V2.2.7x, 3.1.20 indicates V3.x, and 3.2.1 indicates V3.2.x.
--target-db-version 2.2.70 \   
# The number of parallel threads in the assessment task.
--process-thread-count 5

Assessment in MySQL mode

sh bin/start.sh \
# The task name, which can be set to any value.
--name test \
# The analysis mode.
--mode ANALYZE \
# The data source. You can also set this parameter to MYBATIS or IBATIS.
--from-type TEXT \    
# The assessment mode.
--evaluate-mode SOURCE_TARGET \  
# The path of the source file.
--source-file "/home/admin/oma/a.sql" \
# The type of the source database.
--source-db-type MYSQL \
# The version of the source database.
--source-db-version 5.7 \
# The schema to be assessed.
--schemas DEFAULT \
# The type of the destination database.
--target-db-type OBMYSQL \
# The version of the destination database
--target-db-version 2.2.x \
# The number of parallel threads in the assessment task.
--process-thread-count 5

Assess the compatibility with SQL statements in a MyBatis file

--source-file configuration method

OMA can scan your MyBatis SqlMap configuration file to directly extract the SQL statements executed by the applications from the configuration file, and assess the compatibility with the SQL statements. You can use the --source-file parameter to specify a single file to be assessed or the directory that stores multiple files to be assessed. The specified directory can be an absolute path or a relative path, provided that OMA can access your SqlMap configuration file through the path. Example:

sh bin/start.sh \
# The task name, which can be set to any value.
--name textTest \
# The analysis mode.
--mode ANALYZE \
# The data source.
--from-type MYBATIS \
# The assessment mode.
--evaluate-mode SOURCE_TARGET \
# The path of the source file.
--source-file ./oma/mybatis/ \
# The type of the source database.
--source-db-type ORACLE \
# The version of the source database.
--source-db-version 11g \
# The schema to be assessed.
--schemas DEFAULT \
# The type of the destination database.
--target-db-type OBORACLE \
# The version of the destination database
--target-db-version 4.2.0

--mapper-config configuration method

Sometimes you may reference external variables in your SqlMap configuration file. For example, you may reference the petColumnList variable defined in the B.xml configuration file for your A.xml configuration file, as shown in the following example:

<!-- Fragment of the A.xml configuration file -->
    <!-- Referencing a sql fragment and a result map in another file -->
    <select id="selectPet" parameterType="java.lang.Integer"
        resultMap="com.alipay.oceanbase.apache.ibatis.submitted.xml_external_ref.PetMapper.petResult">
        SELECT
        <include
            refid="com.alipay.oceanbase.apache.ibatis.submitted.xml_external_ref.PetMapper.petColumnList"
        />
        FROM pet
        WHERE pet_id = #{id}
    </select>

<!-- Fragment of the B.xml configuration file -->
    <sql id="petColumnList">
        pet_id, owner_id, pet_name
    </sql>

In this scenario, you can specify another SqlMap configuration file C.xml by using the --mapper-config parameter. The C.xml file contains information about the referencing statements and referenced XML files. Specify the absolute path of the SqlMap configuration file in URL format. Example:

<!DOCTYPE configuration
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>

  <mappers>
    <mapper url="file:///home/admin/oma/mybatis/A.xml"/>
    <mapper url="file:///home/admin/oma/mybatis/B.xml"/>
  </mappers>

</configuration>

Assume that the name of the SqlMap configuration file C.xml is MapperConfig.xml. Execute the following statement to run OMA:

sh bin/start.sh \
# The task name, which can be set to any value.
--name mybatisTest \
# The analysis mode.
--mode ANALYZE \
# The data source.
--from-type IBATIS \
# The assessment mode.
--evaluate-mode SOURCE_TARGET \
# The name of the SqlMap configuration file.
--mapper-config MapperConfig.xml \
# The type of the source database.
--source-db-type ORACLE \
# The version of the source database.
--source-db-version 11g \
# The schema to be assessed.
--schemas DEFAULT \
# The type of the destination database.
--target-db-type OBORACLE \
# The version of the destination database
--target-db-version 2.2.70 \
# The number of parallel threads in the assessment task.
--process-thread-count 5

When you specify an SqlMap configuration file by using the --mapper-config parameter, you can use either the absolute path or relative path of the file, provided that OMA can access the file through the path.

Assess the compatibility with SQL statements in an iBatis file

OMA can scan your iBatis SqlMap configuration file to directly extract the SQL statements executed by the applications from the file, and assess the compatibility with the SQL statements.

Set the --source-file parameter to the directory where your SqlMap configuration file is stored. The directory can be an absolute path or a relative path, provided that OMA can access your SqlMap configuration file through the path. Example:

sh bin/start.sh \
# The task name, which can be set to any value.
--name textTest \
# The analysis mode.
--mode ANALYZE \
# The data source.
--from-type IBATIS \
# The assessment mode.
--evaluate-mode SOURCE_TARGET \
# The path of the source file.
--source-file ./oma/ibatis/ \
# The type of the source database.
--source-db-type ORACLE \
# The version of the source database.
--source-db-version 11g \
# The schema to be assessed.
--schemas DEFAULT \
# The type of the destination database.
--target-db-type OBORACLE \
# The version of the destination database
--target-db-version 4.2.0

Enterprise Edition

Assess the compatibility with SQL statements