FAQ

FAQ


The following is a list of commonly asked questions about the SQLWays software program.

A.  Can SQLWays work under Unix like systems?

There is a tool that is not included into the main SQLWays installation package which can be run on Unix like systems. This tool supports the migration of data only.

It supports the following databases:

  1. Sybase ASE
  2. MySQL
  3. DB2


B.  How does SQLWays manage errors?

  • There are three types of errors that might occur during the migration:
    1. Export error – when our tool cannot recognize some part of the source code. In this case the error is raised on the export and depicted in the sqlways.logfile after the migration.

    2. Import error – when our tool made the conversion incorrectly or it doesn’t correspond to the syntax supported in the database. In this case the error is raised on the import and depicted in the sqlways_imp.log after the migration.

    3. BL (Business Logics) error – sometimes the target (result) syntax is correct, but it works incorrectly. The object is created but returns another result different from the result that returns the source SQL Code or simply returns the error. BL error is the most difficult to find error as it raised only when you check the work of the target database manually or through the application using the converted database.


C.  How do you use SQLWays to Migrate in to an existing database?

   

      1.  Overwriting Existing Table Structures.

      If you want to overwrite the table definitions, you can specify to option to generate DROP TABLE statements in SQLWays, see the "generate the Drop Table statements" option.

     2.   Loading the Data into and Existing Table Structure

     To load the data into existing table structure without recreating it, uncheck the "Generate DDL"  option. In this case, SQLWays will only transfer data.


D.   Parameterize Login information after Import Script Generation

You can use the environment variables to specify the SERVICEPASSWORD and USER for the complete program run in sqlways_all.bat. However first you need to specify those environment variables in the SQLWays tool, so that they are used in all the bat files with the commands, like:

The Password is specified as %PASSWORD% here

So, as the result you will have bat files for data and ddl loading with the commands like, e.g. for Oracle:

sqlplus %USER%/%PASSWORD%@%SERVICE% @calendar_ddl.sql

All the bat files are run using the sqlways_all.bat file with the command like:

sqlways_imp.exe /bat=sqlways_all.bat

So, all that is left to do is to update the sqlways_all.bat file with the following SET commands before the CALL statements:

set USER=ora
set PASSWORD=pass
set SERVICE=ora

As the result the SQLPLUS command will be executed as follows:

sqlplus ora/pass@ora @calendar_ddl.sql

So, just by changing three values in the sqlways_all.bat file, you can control which user, password and service name must be used for the whole load.


E.   How do I change a Decimal Point character?

1. In SQLWays wizard Go over the wizard to page “Specify DDL and DATA options” and change the character in the "Decimal point" textbox.

2. In SQLWays Command Line. Set a DecPoint value in the command line. Use the following syntax: /DECPT=DecPoint. A decimal_point= value can be changed in the sqlways.ini initialization file in the [Data] subsection.

For example, 0x2c decimal point character specifies a comma.


F.   What Export File Formats are supported by SQLWays?

SQLWays exports data from the source database to the ASCII text files (export files). Depending on the selected target database, the most suitable export file formats for data transferring are:

Column-delimited (CSV) output format, Fixed length output format (FIX), TAB-delimited output format (TAB), SQL INSERT statements (INS), XML output format (XML) or Btrieve ASCII output format (BTR).

If the target database is Oracle, IBM DB2 or not specified, by default, the output format of the text file is the CSV format.

If the target database is Microsoft SQL Server, Sybase or MySQL, by default, the output format of the text file is the TAB format.

If the target database is Pervasive, by default, the output format of the text file is BTR.


G.   How do I ensure that Foreign Keys are converted successfully from MySQL to MSSQL Server?

Foreign keys in MySQL and in MSSQL have the same structure. The structure of the Foreign key in the MSSQL is:

  [ FOREIGN KEY ] 
      REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ] 
      [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
      [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 

The structure of the Foreign key in the MySQL is:

  FOREIGN KEY [id] (index_col_name, ...)
      REFERENCES tbl_name (index_col_name, ...)
      [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
      [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

The only difference is the RESTRICT word in MySQL database, which has no equivalent in MSSQL database. But in MySQL, the Foreign key constraints are checked immediately, so NO ACTION and RESTRICT are the same. Therefore during an import no errors should arise. To check whether conversion of the actions of the Foreign keys were successful you should check it in the database.


H.   Why does a group of Invalid Objects with Garbage Names appear in Oracle?

Prior to Oracle 10g, a DROP command permanently removed objects from the database. In Oracle 10g a DROP command places the object in the recycle bin. The extents allocated to the segment are not reallocated until you purge the object. You can restore the object from the recycle bin at any time.

A recycle bin contains all the dropped database objects until,

  • You permanently drop them with the PURGE command.
  • Recover the dropped objects with the UNDROP command.
  • There is no room in the tablespace for new rows or updates to existing rows.
  • The tablespace needs to be extended.

The usage of recycle bin can be turned on/off in the following way:

ALTER SESSION SET recyclebin = ON/OFF; -- for the current session
ALTER SYSTEM SET recyclebin = ON/OFF;  -- for all sessions

You can view the dropped objects in the recycle bin from two dictionary views:

  • user_recyclebin - lists all dropped user objects
  • dba_recyclebin - lists all dropped system-wide objects 

For example you drop your table:

DROP TABLE drop_test;

Now you see the table with strange name like ”BIN$njeSgciWSy+6io3ZQS+Bug==$0

It is your table but with the new name. You even can see its content using:

SELECT * FROM "BIN$njeSgciWSy+6io3ZQS+Bug==$0"

To reinstate the table you are able to use this command:

FLASHBACK TABLE drop_test TO BEFORE DROP;

Now you can see the latest version of your table again. 

If you want to drop table permanently you should use:

DROP TABLE drop_test PURGE;

And to empty all users recycle bin command

PURGE RECYCLEBIN;

should be used.

NOTE: All triggers for the dropped table are also put into the recycle bin and their names are changed in the same way.


I.   How do I generate DDL Script without Data Export?

In SQLWays you can export and convert DDL only. For this purpose please uncheck the "Export Data" option.


J.   How do I schedule the Migration Process?

After you ran the conversion with the sql file specification, (as detailed in the "Specify database Objects or Query Page" article),  you will find the sqlways_exp_imp.bat file in the export directory, which you can use for scheduling the migration process. You can simply use the batch file or alternately each of the commands from this file individually.

This file contains the following commands:

cd "S:\Sqlways\Bin\Release"

Go to the folder where the sqlways.exe resides.

SQLWays.exe  /D=DB2_LEONOV_SAMPLE /U=db2admin /P=Ispirer_1999 /SF=S:\support\Ispirer\department.sql /SROW=1 /TARGET=MySQL /TH=localhost /TD=itest /TU=root /TP=root /MIGS=Clean /IMPS=Windows /DIR=S:\support\test\Export7 /R=1000 /INI=sqlways_wzd.ini /PRJF=sqlways.xml /NSTOP /GCMD=sqlways_all.bat /RC /LOG=sqlways.log /RPT=sqlways_report.html /TRACE

Runs the sqlways.exe command line tool with all the options specified during the Wizard run. As you will see there is the /SF option that lets you specify the SELECT statement for the conversion. 

"S:\support\test\Export\sqlways_all.bat"

Runs the import into the target database.


K.   How do I optimise the Migration Process for Large Tables?

With regards to migration time there are several actions that can be taken in order to speed up the migration.
1. If there were no problems during export then you can turn off trace mode. This will avoid trace generation and thus reduces the migration time.


2. With SQLWays you are able to split the migration of data into parts and run them simultaneously. This can be done in two ways:
    a. When you select the “Data Options” tab for tables you’ll see Edit boxes for the start row and the number of rows to be exported (as below). By varying these numbers in separate instances of the Wizard (like 1-5000, 5001-10000, etc.) you can run the migration on different parts of data simultaneously.


Please also note that you should uncheck the “Generate DDL” option for processes other than first one in order to avoid the error of table existence. In this case only data will be migrated.


b. You can export data according to unique index or primary key or any other query condition. To perform this in the Wizard  select “Use Query” in combo-box as below. With this option you can write your own query to select data. DDL statement will be generated according to selected columns. Table name will be the same as in the FROM clause. However you are strongly advised to create an empty table first and then do the migration of data this way. Otherwise you’ll get errors when trying to create the table but the data will be loaded correctly.


For both of these solutions please make sure that “Generate the DROP TABLE statement” check-box is unchecked in the options page. Please also use separate project directories for each migration to prevent the recreation of project files.













© All Rights Reserved CU2 Global