SCA Command Options
SCA is a command line tool. All functions can be realized through single executable program.
General Options
General options may be used in all three types of tasks.
--help (Help Information)
Before using the analysis program, you are advised to read help information carefully. (Command: ./sca_linux_x86_64 --help):
Introduction:
SCA is the tool used to do SQL life-cycle inspector and simulation from other DB to MogDB.
Now we support source databases of Oracle, MySQL and DB2.
So there are 10 type of tasks:
1. [ I] Init SCA repository
2. [ L] Apply for License
3. [ OC] Oracle Collection, collect Oracle basic information and executed SQLs
4. [ MC] MySQL Collection, collect MySQL basic information and executed SQLs
5. [ DC] DB2 Collection, collect DB2 basic information and executed SQLs
6. [ OI] Oracle Inspection, used to do Oracle SQL compatible analysis
7. [ MI] MySQL Inspection, used to do MySQL SQL compatible analysis
8. [ DI] DB2 Inspection, used to do DB2 SQL compatible analysis
9. [ OS] Oracle Simulation, used to do Oracle SQL performance analysis like Oracle build-in SPA tool
10. [OIS] Oracle Inspection & Simulation, for both SQL compatible and performance analysis
Options:
--[ Overall ]--
--help : Show help message
-v, --version : Show SCA version
-T, --type : Run type:
: I = Init SCA repository
: L = Apply for license
: ----------------[Analysis: Target required]---------------
: OI = Oracle Inspection, this is default type
: MI = MySQL Inspection
: DI = DB2 Inspection
: OS = Oracle Simulation, just like Oracle SPA
: OIS = Oracle Inspection & Simulation
: ------------[Collection: No need of target DB]------------
: OC = Oracle Collection
: MC = MySQL Collection
: DC = DB2 Collection
-d, --data : Unzipped data directory for analyzer, or directory for collection
-D, --data-id : Use data with data id existed in the repository
-w, --workers : Parallel workers for tasks, default: 10
-x, --debug : Enable debug mode
-l, --logfile : Output to both logfile (without progress-bar) and screen (with progress-bar)
-L, --log-only : Output to only logfile (without progress-bar)
-F, --force : Force mode in REPO Creation, drop old objects before create it
-r, --report : Final report file location, default in data directory with name 'report'
--license : License file, default is [./license.json]
--sql-transformer : Regular rules for SQL transformation (for internal use)
: Format: [{"name": "xxx"
: "source": "xxx",
: "target": "xxx",
: "comment": "xxx"}, ...]
--[ Repository Connection ]--
-H, --repo-host : Repository DB Server host address, default: 127.0.0.1
-P, --repo-port : Repository DB server port, default: 5432
-N, --repo-name : Repository database, default: sca_db
-U, --repo-user : Repository user, default: sca_repo
-E, --repo-password : Repository password, default: SCA@password
--user : Administrator used to create repository DB and user, default: mogdb
--password : Password for Administrator, default: mogdb
--[ Source & Target Connection ]--
-h, --db-host : Source & Target DB Server host address, default same as -H
-p, --db-port : Source & Target DB server port, default same as -P
-n, --db-name : Source & Target database, default same as -N
-u, --db-user : Source & Target user, default same as -U
-e, --db-password : Source & Target password, default same as -E
--target-type : Target database type in analysis tasks, default: MOGDB
: Valid type: ORACLE, MOGDB, OPENGAUSS, POSTGRESQL, MYSQL, DB2
--[ Collection Options ]--
-q, --sql-days : How many days for session sql data, default: 7
-Q, --sql-interval : SQL collect interval in seconds, default: 600
-s, --schema-include : Users/Schemas included in data collection, default: ''
-S, --schema-exclude : Users/Schemas excluded in data collection
: Default: <<depends on DB type>>
-m, --enable-monitor : Starting background monitor process in SQL Collection
: Valid values: 1/on/true/t = ENABLE, default: on
: 0/off/false/f = DISABLE
--slow-log : MySQL slow-log for client data collection
--sql-csv : SQL file in csv format for SQL Inspection (@todo)
Usage:
0. Apply for license
./sca_linux_x86_64 -T L
1. Init repository (used for first running)
./sca_linux_x86_64 -T i -H <host> -P <port> -N <repo-database> -U <repo-user> -E <repo-password> --user <super_user> --password <super_password>
2. Oracle data collection
# Notice: "-q 0.001 -Q 60" means gather Session SQL only once
# "-m off" means do not monitor system status (CPU Idle and Disk Free)
./sca_linux_x86_64 -T OC -s SCOTT -h <host> -p <port> -n '<target-db>' -u <oracle-user> -e <oracle-password> -q 0.001 -Q 60 -m off
./sca_linux_x86_64 -T OC -s SCOTT -h <host> -p <port> -n '<target-db>' -u <oracle-user> -e <oracle-password>
3. MySQL data collection using slow-log file
./sca_linux_x86_64 -T MC -d <report-directory> --slow-log=<slow-log-file>
4. Oracle SQL compatible analysis (Required: Repository, Target DB)
Note: use [H/P/N/U/E] options to assign the repository
use [h/p/n/u/e] options to assign the target database
./sca_linux_x86_64 -T OI -d <unzipped data directory>
5. Oracle SQL performance simulation (Required: Repository, Target DB)
Note: use [H/P/N/U/E] options to assign the repository
use [h/p/n/u/e] options to assign the target database
./sca_linux_x86_64 -T OS -d <unzipped data directory>
6. MySQL SQL compatible analysis (Required: Repository, Target DB)
Not e: use [H/P/N/U/E] options to assign the repository
use [h/p/n/u/e] options to assign the target database
./sca_linux_x86_64 -T MI -d <unzipped data directory> -H <host> -P <port> -n <target-db>-v, --version (Version)
View the current version of SCA:
hongyedba@localhost ~ % ./sca_linux_x86_64 -v
SCA version: 5.0.0-T, --type (Task Type)
Default value: OI
Specifies the task type. Currently, SCA supports the following types of tasks:
I [Initialize]: Initialize, used to initialize the SCA repositoryL [Apply License]: Apply license, task for license applicationOC [Oracle Collection]: Oracle collection, used to collect the business SQL executed in Oracle database, need long time to collectMC [MySQL Collection]: MySQL collection, used to collect the business SQL executed in MySQL database, need to configure the slow log in advance, and then collect at onceDC [DB2 Collection]: DB2 collection, used to collect the business SQL executed in DB2 database, need long time to collectOI [Oracle Inspection]: Oracle compatibility evaluation, used to evaluate the actual compatibility of business SQL collected in Oracle on the source side in MogDB on the target sideMI [MySQL Inspection]: MySQL compatibility evaluation, used to evaluate the actual compatibility of business SQL collected in MySQL on the source side in MogDB on the target sideDI [DB2 Inspection]: DB2 compatibility evaluation, used to evaluate the actual compatibility of business SQL collected in DB2 on the source side in MogDB on the target sideOS [Oracle Simulation]: Oracle performance evaluation, used to evaluate the execution performance of business SQL collected in Oracle on the source side in MogDB on the target sideOIS [Oracle Inspection & Simulation]: Oracle compatibility and performance evaluation, equivalent to OI + OS two tasks at the same time
-d, --data (Data Directory)
The -d data directory can be specified for all task types except repository initialization (-T I).
The collection task writes the collected data to the data directory specified by -d.
The analysis task reads data from the data directory, inserts the data into the repository table, and the final generated report results are also written to the data directory by default.
-D, --data-id (Data ID)
Specify the data ID and then read the data with the specified ID directly from the repository instead of re-reading and loading the data from the data directory.
Specifying the -D option skips the step of loading data and performs the relevant analysis task directly.
-w, --workers (Degree of Parallelism)
The default degree of parallelism is 10.
Specifies the degree of parallelism for running tasks. Appropriate degree of parallelism can improve the running speed of each task.
Applicable to: file data loaded to a data repository, SQL compatibility assessment, SQL complexity assessment, SQL performance simulation, and other operations
Note:
- In SQL simulation tasks, a greater degree of parallelism may lead to degradation of the execution efficiency of single SQL. Therefore, an appropriate degree of parallelism needs to be chosen according to the loading of the actual production environment and is generally set to the average number of active sessions of a production database.
-x, --debug (Debug Mode)
Enabling the debug mode does not affect normal analysis logic but will output a large number of logs. The debug mode is usually used for assisting the analysis program in running exceptions itself.
-l, --logfile (Log File)
Specifies the log file of a program. The program will output data to both the terminal command line and log file.
-L, --log-only (Log File)
Specifies the log file of a program. The program will output data only to the log file.
-F, --force (Forcible Mode)
Specifies whether to enable the forcible mode. This mode takes effect in the data repository initialization scenarios.
During the initialization of a data repository, if the forcible mode is enabled, the data repository will be deleted first and then re-created.
-r, --report (Report Directory)
Default value: <DATA_DIR>/report
Specifies the report directory. The report here includes the compatibility evaluation and SQL simulation reports in the HTML format, which can be viewed offline.
Also, for the compatibility evaluation task, a sql_detail_list.csv file is generated to record the evaluation results of all SQL and possible rewriting options.
--license (License File)
Default value: ./license.json
The location of the License file.
--sql-transformer (SQL transform rules)
[Advanced Usage] Specify the SQL transform rules from the source database to the target database, and use regular matching for transform.
Repository Options
-H, --repo-host (Repository IP Addresses)
Default value: 127.0.0.1
Repository database IP Addresses.
-P, --repo-port (Repository Port)
Default value: 5432
Repository database port.
-N, --repo-name (Repository Name)
Default value: sca_db
Repository database name.
-U, --repo-user (Repository User)
Default value: sca_repo
Repository database login user.
-E, --repo-password (Repository Password)
Default value: SCA@password
Repository database login password.
--user (Administrator User)
Repository database administrator user, used to create repository user and repository database during the initialization of the repository.
--password (Administrator Password)
Repository administrator user login password, used to create repository user and repository database during the initialization of the repository.
Source or Target Database Connection
-h, --db-host (Source or Target Database IP Addresses)
Source or target database IP Addresses, inherits the -H option value by default.
-p, --db-port (Source or Target Database Port)
Source or target database port, inherits the -P option value by default.
-n, --db-name (Source or Target Database Name)
Source or target database Name, inherits the -N option value by default.
In the SQL Analysis task, specify the target database name, which is usually the target database created on the MogDB side when using MTK or other data migration tools for database structure and data migration.
Note that the repository user needs to have full operational privileges to the target database by default, and the default repository is administrator privileges.
- In the compatibility assessment, only the target database needs to have an object structure
- In SQL simulation, the target database needs to have both object structure and real peer-to-peer full production data, otherwise the performance simulation results are not informative
-u, --db-user (Source or Target Database User)
Source or target database login user, inherits the -U option value by default.
-e, --db-password (Source or Target Database Password)
Source or target database login password, inherits the -E option value by default.
--target-type (Target Database Type)
Specify the target database type in the analysis task, the default is MOGDB.
Currently this parameter is not perfect, only MOGDB/POSTGRESQL is supported.
Data Collection Options
-q, --sql-days (Number of Days for SQL Collection)
Specifies the total number of days for SQL-related data collection. Data miss may occur in collecting the executed SQL data from session cache GV$SQLAREA. You can prolong the number of collection days to reduce the data collection miss possibility.
By default, SQL data of a week will be collected.
-Q, --sql-interval (SQL Collection Interval)
Specifies the SQL-related data collection interval. By default, the SQL data is collected every 10 minutes.
The SQL data collected every time will be compared with the collected data to filter out the repeated data. This can prevent the data file from being too large.
-s, schema-include (Schema Whitelist)
Specifies the schema whitelist for data collection. Only schema-related data listed in the whitelist is collected.
-S, schema-exclude (Schema Blacklist)
Specifies the schema blacklist for data collection. Schema-related data listed in the blacklist is not collected.
By default, Oracle system users are listed in the schema blacklist.
-m, enable-monitor (Resource Monitoring)
Specifies whether to enable the resource monitoring process in the background. This process is enabled by default.
The resource monitoring process queries the CPU usage of the current server every 3s and the remaining space of the file system where the data directory is located.
When the CPU usage is greater than 90% or the remaining space of the file system is lower than 100 MB, the monitoring sub-process will stop collecting data from the primary process so that the server will not become faulty due to resource problems.
--slow-log (Slow Query Log)
Specifies the MySQL slow query log file.
When the collection program cannot access the target MySQL database and the collection program is not running on the MySQL server, you can manually retrieve the MySQL slow log from the target database and use the current option to specify the slow log to parse and generate the corresponding collection data, which can be used for subsequent MySQL SQL compatibility evaluation tasks.