Sybase IQ to Oracle Online Presentation

Demo presentation text:


today we’ll demonstrate the migration from Sybase IQ to Oracle using SQLWays from Ispirer Systems. We’ll start from 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 Sybase IQ 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. Each type of objects in its own node – tables, views, procedures, functions, etc. On the right side there are objects selected for our demo conversion. We use these arrow buttons to select and remove objects for conversion.

On the next screen several conversion options are available. “Generate drop table statement” – firstly table will be dropped and then recreated again if this option is specified. 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 “Generate DDL” option on “SQL Scripts” page (in this case only data would be migrated), or vice versa we may uncheck “Export Data” option on “Data Options” tab – only schema would be migrated.

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 we see some import options. Also there’s an option to start import from a remote machine. We can select either Windows or Unix operation system. If we select Unix shell files will be generated instead of Windows batch files. So in our case we’re running import from this local machine.

And finally this is the last screen with summary information on the conversion process. By clicking the Next button we launch the Export. On this step SQLWays connects to the source database, extracts all information on source objects and converts it to Oracle DDLs.

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 with some load options. Text file contains data for this table. DDL file with table definition. Here is the commented source DDL the then below goes the converted Oracle definition. Also if table has some constraints, primary keys this information is also extracted and converted.

Among the other generate 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 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 object 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 let’s review all loaded objects in our target Oracle database. We can see that all our objects (tables, views, procedures… ) were loaded successfully to target database.

Let’s review the definition of one of stored procedures. This is Sybase IQ definition with KEY OUTER JOIN syntax. It was converted to LEFT OUTER JOIN where the information on primary key – foreign key relationship was extracted from source database.

Now lets execute this procedure in Sybase IQ server, then in Oracle. So we can see that the results of this query are identical.

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 IQ) 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, and whether it was converted successfully.

Now as the conversion is done let’s compare source and target. This is Sybase IQ procedure with resultset where 2 select statements were converted to Oracle SYS_REFCURSORS as OUT parameters. So resultsets are returned in refcursors.

In this trigger we can see EXCEPTION declared for the following SQLSTATE for NOT_FOUND condition and there’s a LEAVE statement in a LOOP. In Oracle it was converted to EXIT loop WHEN %NOTFOUND.

And finally we’ll review SQLWays Studio which is mainly used for single script conversion. So here is our source script, also we need to specify source and target direction of migration. On the right sight our converted procedure appears, also exception for SQLSTATE ‘02000’ was converted to %NOTFOUND condition.

That’s all about SQLWays from Ispirer Systems. If you have any questions you’re welcome to contact us.

Thanks for your attention.