Demo presentation text:
This is a demo about the migration tools provided by Ispirer Systems for the Adaptive Server Anywhere to Oracle migration.
First of all I’d like to mention that Ispirer Systems provides tools and services for automating the databases and applications migration processes for a variety of conversion directions. The list of tools supplied for such kind of automation is as follows:
• SQLWays Wizard – a GUI tool that is designed to simplify the database conversion process tuning for the sqlways command-line tool. Works with the source database using the ODBC connection.
• SQLWays Studio – a GUI tool that is designed to simplify the database or application script conversion process tuning for the sqlways command-line tool. Works with one script at a time.
• SQLWays Commander - a GUI tool that is designed to simplify the database or application scripts conversion process tuning for the sqlways command-line tool. Works with several scripts at a time.
• Sqlways.exe – a command line tool that can be run in batch mode and can perform the automatic conversion of the database using the ODBC connection or the database and application code provided in scripts.
In this demo, I will show you the Adaptive Server Anywhere to Oracle migration using SQLWays Wizard, SQLWays Studio and SQLWays Commander Tools mentioned in the list provided.
First let’s start from the SQLWays Wizard that is designed to work with source database through ODBC connection and it works with target database through native utilities (sqlplus, sqlldr in our case).
On the first screen there’s an option “Select a project directory” where all settings and options are stored.
On this page we select the ODBC to connect to our source Adaptive Server Anywhere database from the list of available ODBC connections.
Also credentials need to be specified.
On the next screen we specify our target migration direction – Oracle, port number, TNS service name, credentials.
On the “Advanced” tab it’s recommended to specify path to BIN directory where all Oracle native utilities are stored. Also some other options are available on this page.
On the next page we see all our database objects in a tree view representation: tables, views, stored procedures.
On the right side there are objects selected for our demo conversion. We use these arrow buttons to select and remove objects for conversion.
Also mask for all the types of objects selection is available.
On the next screen several conversion options are available. For example, to generate drop table in the statement.
Among the most useful ones is data type mapping. If we select “Tables” node in a tree view we see “Global Datatype Mapping” where on the left column all source datatypes are represented and on the right column - their default Oracle equivalents. We can change this default behavior by changing target datatypes according to our preferences, if necessary.
Also there’s a possibility to separate processes of migrating schema and data. For this purpose we may uncheck “Export Data” option on “Data Options” tab – only schema would be migrated or vice versa we may uncheck “Generate DDL” option on “SQL Scripts” page (in this case only data would be migrated). Let’s migrate both schema and data in our presentation.
On the next screen there’s an Export Directory option. This is the folder where all generated files are stored. Also “Run in Trace mode” is quite a useful option. Extended trace information helps to track errors and other conversion issues if there’re any.
Moving to the next screen we see “Migrate Data” confirmation dialog.
On the next screen there’s “Start import automatically” option. We may either start Import automatically right after the Export process is completed or firstly review the results of Export and then launch Import by clicking “Start Import” button. Also there’s an option to start import from a remote machine.
And finally this is the last screen with summary information on the conversion process. By clicking the Next button we launch the Export.
Now we can review the generated files going to Export Directory (click “View Export”).
In Export directory we can see all our generated files. For each object batch files are generated.
For tables batch file contains sqlplus native utility call which loads table DDL to target database.
Then sqlldr utility command loads data to this table.
Also control file for each table is generated,
DDL file with table definition. Here is the commented source DDL, then goes the converted Oracle definition.
Text file contains data for this table.
Table with autoincrement column (t_def_auth) is converted to table with additional trigger and sequence to implement the autoincrement column behavior in Oracle.
Among the other generated scripts there are log files with some statistics information on the conversion process.
This is sqlways.log file that contains all the information on the Export process.
At the bottom there’s summary information on the conversion: how many object were migrated, the number of objects that were converted successfully, number of lines of code, rows of data, how much time the process took, etc.
Now let’s get back to SQLWays Wizard. As the process of Export is completed we launch Import process by clicking “Start Import”. On this step all converted objects are being loaded to target Oracle database.
Now let’s view sqlways_imp.log report. We see that all objects are successfully created in target database.
Now we open Sybase and Oracle clients to review that all objects were really loaded to Oracle database. Here are Adaptive Server anywhere database objects and here are Oracle loaded objects. So all objects were loaded successfully.
Now let’s execute one of the functions in both databases and compare results.
Firstly let’s execute function in Adaptive Server Anywhere, then in Oracle.
So as we can see the returned results are the same.
That’s all about SQLWays Wizard, now we’ll review 2 other SQLWays components – SQLWays Studio and SQLWays Commander. They are both designed for the script conversion.
We’ll start from SQLWays Commander that is used for multiple scripts conversion.
We specify Source database (Sybase Adaptive Server Anywhere) direction and target – Oracle. Then we select the bunch of scripts we’d like to migrate and on the right sight we select the directory where the converted scripts will be stored. By clicking start button the conversion is launched. At the bottom window we can see the progress information – which script is being converted, number of lines of code.
Now as the conversion is done let’s compare source and target.
Here is the source procedure on the left, dateadd function was converted to arithmetic operation with interval.
In cases where there’s no direct equivalent for some built-in functions, as for example isdate function, isnumeric function SQLWays converts them to user-defined functions (UDF) – SWF_isdate, SWF_isnumeric.
Here is, for example, the definition of SWF_isnumeric function.
Now let’s get to SQLWays Studio which is mainly used for single script conversion.
We need to specify source and target direction of migration also we may select the fragment of code we’d like to convert.
Here is our converted procedure below, locate function was converted to instr function.
Now let’s convert the following view.
DATEPART function was converted to EXTRACT function, IFNULL was converted to NVL2.
That’s all that I wanted to tell you in this Demo. The information about other tools that Ispirer Systems provides for database migration support will be described in our next Demos or can be found on our website. I hope this Demo was useful. Thank you for your attention. Bye