Updated on 2022-08-16 GMT+08:00

Prerequisites

Executing Custom DB Scripts

Custom scripts are executed to support input keywords that do not exist in certain versions of the target database. These scripts must be executed in each target database before the migration.

Table 1 describes the custom scripts in the DSC/scripts/ directory. For details about how to execute custom scripts, see Custom DB Script Configuration.

Table 1 Custom DB scripts

Script

Description

date_functions.sql

Custom DB script for Oracle date functions

environment_functions.sql

Custom DB script for Oracle environment functions

string_functions.sql

Custom DB script for Oracle string functions.

pkg_variable_scripts.sql

Custom DB script for Oracle package variable functions

sequence_scripts.sql

Custom DB script for Oracle sequence functions

mig_fn_get_datatype_short_name.sql

Custom DB script for Teradata functions

mig_fn_castasint.sql

Custom DB script for migration of CAST AS INTEGER

vw_td_dbc_tables.sql

Custom DB script for migration of DBC.TABLES

vw_td_dbc_indices.sql

Custom DB script for migration of DBC.INDICES

Table 2 Custom DB scripts (Oracle to GaussDB T)

Script

Description

create_user_and_temptable_enable.sql

Custom DB script for create user and local temporary table is used to address Oracle Package feature.

pkg_variable_scripts.sql

Custom DB script for Oracle package variable functions

Follow the steps to execute custom DB scripts:

  1. Use any of the following methods to execute the required scripts in all target databases for which migration is to be performed:

    • Use gsql.
      • Use gsql to connect to the target database and paste all content in the SQL file to gsql, which will automatically execute the pasted contents.

        Run the following command to connect to the database:

        gsql -h <host_addr_xxx.xxx.xxx.xxx> -d <database_name> -U <user_name> -W <password> -p <port_number> -r
      • Use gsql to connect to the target database and execute a SQL file.

        Run the following command to connect to the database and run the SQL file:

        gsql -h <host_addr_xxx.xxx.xxx.xxx> -d <database_name> -U <user_name>  -W <password>  -p <port_number> -f <filename.sql> -o <output_filename> -L <log_filename.log>  -r
    • Use Data Studio.

      Use Data Studio to connect to the target database, and then open and run the SQL file in Data Studio.

Custom DB Script Configuration

Custom scripts are SQL files used to migrate from Teradata/Oracle the input keywords that do not exist in the target database.

These scripts must be executed in each target database before the migration.

Open the scripts folder in the release package. Table 3 lists the folders and files in the scripts folder.

The SQL files contain the scripts for the custom migration functions. These functions are required to support the specific features of Teradata and Oracle.

Table 3 Custom DB scripts for DSC

Folder

Script File

Description

-- scripts

-

Folder: all scripts

------ oracle

-

Folder: Oracle functions and scripts

-------- sequence

-

Folder: scripts to configure Oracle sequences

-

sequence_scripts.sql

Script: used to enable migration of Oracle sequence

-------- package

-

Folder: scripts to configure Oracle package variables

-

pkg_variable_scripts.sql

Script: used to enable migration of Oracle package variables

-------- function

-

Folder: scripts to configure Oracle system functions

-

date_functions.sql

Script: used to enable migration of Oracle date functions

-

environment_functions.sql

Script: used to enable migration of Oracle environment functions

-

string_functions.sql

Script: used to enable migration of Oracle string functions

------ teradata

-

Folder: Teradata functions and scripts

-------- view

-

Folder: scripts to configure views

-

vw_td_dbc_tables.sql

Script: used to enable migration of Teradata DBC.TABLES

-

vw_td_dbc_indices.sql

Script: used to enable migration of Teradata DBC.INDICES

-------- function

-

Folder: scripts to configure Teradata system functions

-X

mig_fn_get_datatype_short_name.sql

Script: used to enable migration of Teradata DBC.COLUMNS

-

mig_fn_castasint.sql

Script: used to enable migration of CAST AS INTEGER

--------db_scripts

-

Folder: scripts to enable Teradata custom functions

-

mig_fn_get_datatype_short_name.sql

Script: used to enable migration of Teradata DBC.COLUMNS

--------core

-

Folder: Teradata core scripts

-

teradatacore.pm

Script: used to perform Perl migration

Configuring DSC and Migration Properties

To configure DSC, configure parameters in the configuration files in the config folder of DSC. Table 4 describes the parameters.

Table 4 Parameters for configuring DSC

Scenario

Configuration File

Parameter

Teradata SQL Migration

deleteToTruncate=True/False
distributeByHash=one/many
extendedGroupByClause=True/False
inToExists=True/False
rowstoreToColumnstore=True/False
session_mode=Teradata/ANSI
tdMigrateDollar=True/False
tdMigrateALIAS=True/False
tdMigrateNULLIFZero=True/False
tdMigrateZEROIFNULL=True/False
volatile=local temporary/unlogged

Oracle SQL Migration

exceptionHandler=True/False
TxHandler=True/False
foreignKeyHandler=True/False
globalTempTable=GLOBAL/LOCAL
onCommitDeleteRows=Delete/Preserve
maxValInSequence=0..9223372036854775807
mergeImplementation=WITH/SPLIT
RemoveHashPartition=True/False
RemoveHashSubPartition=True/False
RemoveListPartition=True/False
RemoveListSubPartition=True/False
RemoveRangeSubPartition=True/False
MigSupportSequence=True/False

Teradata Perl Migration

add-timing-on=True/False
db-bteq-tag-name=bteq
db-tdsql-tag-name=sql_lang
logging-level=error/warning/info
migrate-variables=True/False
remove-intermediate-files=True/False
target_files=overwrite/cancel
migrate-executequery=True/False

MySQL SQL Migration

table.databaseAsSchema=true
table.defaultSchema=public
table.schema=
table.orientation=ROW
table.type=HASH
table.partition-key.choose.strategy=com.huawei.hwclouds.scs.dws.DWSPartitionKeyChooserStrategy
table.partition-key.name=
table.compress.mode=NOCOMPRESS
table.compress.level=0
table.compress.row=NO
table.compress.column=LOW
table.database.template=template0

Netezza SQL Migration

rowstoreToColumnstore=false

DB2 Syntax Migration

DSC: application.properties

exceptionHandler=True/False
TxHandler=True/False
foreignKeyHandler=True/False
globalTempTable=GLOBAL/LOCAL
onCommitDeleteRows=Delete/Preserve
maxValInSequence=0..9223372036854775807
mergeImplementation=WITH/SPLIT
RemoveHashPartition=True/False
RemoveHashSubPartition=True/False
RemoveListPartition=True/False
RemoveListSubPartition=True/False
RemoveRangeSubPartition=True/False
MigSupportSequence=True/False