Syntax Migration Using DSC

You can use DSC to migrate syntax.

Precautions

Before starting the migration program, specify the path of the output folder and separate the input folder path, output folder path, and log path with spaces. By default, these paths are in the root directory of the tool. The input folder path cannot contain spaces, or DSC will fail to be executed. For details, see Troubleshooting.

If the output folder contains subfolders or files, DSC deletes or overwrites them based on parameter settings in the application.properties configuration file in the config folder before the migration. Once they are deleted or overwritten, they cannot be restored using DSC.
If migration tasks are performed concurrently on a given server (Using the same or different DSCs), ensure that they use different output folder paths and log paths.
Specify a log path by configuring optional parameters. If the path is not specified, DSC automatically creates a log folder under TOOL_HOME. For details, see DSC Log Reference.
A single SQL statement can be up to 20 KB. If it is larger, it might run slowly or fail to be converted.
DSC controls access to its files and folders for security. You need the required permissions to access them. For example, you need the permission 600/400 to access target files and log files, and the permission 700 to access target folders and log folders. The tool does not save sensitive data in logs to ensure data security.
Ensure that the file or folder specified in --input-folder have no write permissions to GROUP and OTHERS, or the tool will not run.
Do not install or run DSC in Linux as the root user.
Keep the default umask value in the DSC.jar file as it controls file permissions. Changing it may alter these permissions.

DSC operates independently as a standalone application, requiring no connection to networks or databases. It functions seamlessly on machines completely disconnected from any network.

Preparations

Create an input folder and an output folder before starting the migration. Copy all SQL scripts into the input folder. The following takes Linux as an example.

Create an input folder and an output folder. You can create a folder anywhere based on your preferences, or use the default folder which is provided as part of package.
1 2

mkdir input mkdir output
DSC processes the input folder in random batches. Once migration begins, avoid modifying the folder or its files. Any changes may impact the tool's results.
Copy all SQL scripts to be migrated to the input folder.
- If the encoding format of source files is not UTF-8, perform the following steps:
  1. Open the application.properties file in the config folder.
  2. Change the value of encodingFormat in the application.properties file to the required encoding format.
  The value of encodingFormat is case-insensitive and can be UTF-8, ASCII, or GB2312 in DSC.
- To obtain the encoding format of a source file in Linux, run the following command on the server where the source file is located:
  1
  
  file -bi <Input file name>

Environment Setup and Restoration (Databases and Database Users)

Creating a DWS database and schema

gsql -p <port> -d postgres 
drop database <database name>;
create database <database name>;
\c <database name>
GRANT ALL PRIVILEGES ON DATABASE <database name> TO <user>;
grant database to <user>;\q
gsql -p <port> -d <database name> -U <user> -W <password> -h <IP> -f
drop database <database name>;
create database <database name>;
\c <database name>;
GRANT ALL PRIVILEGES ON DATABASE <database name> TO <user>;
gsql -p <port> -d <database name> -U <user> -W <password>  -f

Run all the files in the Setup directory.

Commands:

sh runDSC.sh -S oracle -M blogic -I <input path> 
sh runDSC.sh -I input/ -S oracle -M ddl -L log_temp -P input/bulk/1_table/

Migration Methods

You can run the runDSC.sh or runDSC.bat command in Windows or Linux to perform migration. For details about the commands in different migration scenarios, see Table 1. The parameters in square brackets ([]) are optional.

Table 1 Migration on Windows and Linux

Migration

CLI Parameter

Linux

     > ./runDSC.sh
 --source-db Teradata
[ --input-folder <input-script-path> ]
[--output-folder <output-script-path> ]
[--log-folder <log-path>]
 
 
  

Windows

     > runDSC.bat
--source-db Teradata
[ --input-folder <input-script-path> ]
[--output-folder <output-script-path> ]
[--log-folder <log-path>]
 
 
  

The CLI parameters are described as follows:
- Acronym
  --source-db: -S
  
  --input-folder: -I
  
  --output-folder: -O
  
  --log-folder: -L
- source-db specifies the source database. The value can be:
  mysql
  
  bigquery
  
  teradata
  
  greenplum
  
  netezza
  
  oracle
  
  postgres
Command output description:
Migration process start time indicates the migration start time and Migration process end time indicates the migration end time. Total process time indicates the total migration duration, in milliseconds. In addition, the total number of migrated files, total number of processors, number of used processors, log file path, and error log file path are also displayed on the console.

Examples

Example: Migrate the SQL files of the Teradata database to the SQL scripts of DWS on Linux.

     ./runDSC.sh --source-db teradata 
or
./runDSC.sh -S teradata

     ./runDSC.sh --source-db teradata --input-folder D:\test\conversion\input --output-folder D:\test\conversion\output  --log-folder D:\test\conversion\log

Example: Migrate the SQL file of the Teradata database to the SQL script of DWS on Windows.

     runDSC.bat --source-db teradata 
or
runDSC.bat -S teradata

     runDSC.bat --source-db teradata --input-folder D:\test\conversion\input --output-folder D:\test\conversion\output --log-folder D:\test\conversion\log

Migration details are displayed on the console (including the progress and completion status):

********************** Schema Conversion Started *************************  
DSC process start time : Mon Jan 20 17:24:49 IST 2020  
Statement count progress 100% completed [FILE(1/1)]    
Schema Conversion Progress 100% completed  
**************************************************************************   
Total number of files in input folder : 1  
Total number of valid files in input folder : 1  
**************************************************************************   
Log file path :....../DSC/DSC/log/dsc.log  
Error Log file :    
DSC process end time : Mon Jan 20 17:24:49 IST 2020  
DSC total process time : 0 seconds  
********************* Schema Conversion Completed ************************

Output File Verification

Once the migration finishes, use a comparison tool like BeyondCompare® to check differences between the input and output files. You can format the input SQL files to simplify this process.

Run the following command in Linux and view migration files in the output folder. Operations in Windows are not described here.

cd OUTPUT
ls

Information similar to the following is displayed.

formattedSource    output
user1@node79:~/Documentation/DSC/OUTPUT> cd output
user1@node79:~/Documentation/DSC/OUTPUT/output> ls
in_index.sql    input.sql    Input_table.sql    in_view.sql    MetadataInput.sql
user1@node79:~/Documentation/DSC/OUTPUT/output>

Compare the output files with the input files using the comparison tool. Check whether the keywords in the migrated SQL files align with the target database's requirements. If not, contact technical support.