Consider an example of migrating a Sybase JDBC Application, and the information contained in the migration reports: how it may be used to tune the migration for optimal results.
5.1 Migrating a Sybase JDBC Application
Figure 5-1 illustrates how an application that is coded to query a Sybase database may use SQL Translation Framework to query information stored in Oracle Database instead.
Figure 5-1 Sybase Application Running Against Oracle Database
Description of "Figure 5-1 Sybase Application Running Against Oracle Database"
5.1.1 Application Overview
The Sybase database used in this example has three tables and five procedures and includes the following features:
INSERTstatements into tables with
VARCHARcolumns with size greater than
Multiple implicit result sets returned from procedures
A Java application connects to this Sybase database using JDBC.
5.1.2 Setting Up Migration
The migration process has four phases - Capture, Convert, Generate, and Data Move. It is best practice to complete each phase of the migration process, review any issues on the Summary page, and then continue to the next phase. The Migration Wizard enables you to complete each step in turn and then return back to the wizard to complete further steps. To do this, after completing each phase, select the Proceed to Summary Page check box and click Next.
Perform the following steps to set up migration:
Download the JDBC driver JTDS 1.2.
Add JTDS as a third-party JDBC driver as follows:
Select Preferences from the Tools menu.
Select Third Party JDBC Driver from the Database option on the right panel, as shown in Figure 5-2.
Figure 5-2 Setting JTDS JDBC Driver
Click Add Entry.
The Select Path Entry box is displayed.
jtds-1.2.jarfile and click Select.
Connect to the Oracle Database where you want to migrate the information.
Verify that the connection is using Oracle Database 12c JDBC drivers, with the following command:
Create a new user
migrepin Oracle database, for the migration repository, with the following command:
GRANT CONNECT,RESOURCE,CREATE VIEW to migrep INDENTIFIED BY migrep; ALTER USER migrep QUOTA UNLIMITED to users;
Connect to the database as the
migrepuser and associate the migration repository with the user, as shown in Figure 5-3.
Figure 5-3 Associating a User with Migration Repository
Create a connection to the Sybase database, in this example,
simpledemo12c, as shown in Figure 5-4.
Figure 5-4 Creating a Connection to the Sybase Database
5.1.3 Capturing Migration
Perform the following steps to capture migration:
- Right-click on the
simpledemo12c Sybasedatabase and select the Migrate to Oracle option, as shown in Figure 5-5.
Figure 5-5 Starting Capture Phase of Migration Process
This opens the Migration Wizard, as shown in Figure 5-6.
Figure 5-6 Migration Wizard Introduction Screen
- Choose the Migration Repository, as shown in Figure 5-7.
Figure 5-7 Choosing the Migration Repository
- Enter a project name and specify an output directory to place files, as shown in Figure 5-8.
Figure 5-8 Specifying Project Name and Output Directory
- Select the database connection and the mode, as shown in Figure 5-9.
Figure 5-9 Selecting the Database Connection and Mode
- Select the database, in this case,
simpledemo12c, by moving it from Available Databases to Selected Databases, as shown in Figure 5-10.
Click Proceed to Summary Page to review the Capture phase before moving to the next phase of the migration process.
Figure 5-10 Selecting the Database to be Migrated
The capture phase saves a snapshot of the selected database at this point of time. Only the object definitions are captured, not the actual table data. This captured snapshot can be viewed in the Migration Projects navigator.
Note that the snapshot is not a connection to the database, and it only enables you to browse through the information saved in the Migration Repository.
5.1.4 Setting Migration Preferences
- From the Tools menu, select Preferences, then Migration, and then Translators. Select the Generate Compound Triggers option.
Figure 5-11 Setting Migration Preferences
- From the Tools menu, select Preferences, then Migration, and then Generation Options. Select the Use all Oracle Database 12c features in Migration option.
Figure 5-12 Setting Migration Preferences
5.1.5 Converting Migration
- Right-click the Capture Model node and choose Convert, as shown in Figure 5-13.
Figure 5-13 Starting Convert Phase of Migration Process
The Migration Wizard is opened at the Convert phase, as shown in Figure 5-14.
Figure 5-14 Converting the Migrated Data
- Select Proceed to Summary Page and click Next.
- Click Finish.
During the convert phase, object names are resolved to valid Oracle names. Data types are converted to Oracle Database types and T-SQL defined objects like stored procedures, views, and so on are converted to Oracle PL/SQL. A converted model is created that can be browsed in the Migration Projects navigator. The converted procedures can be reviewed in the converted model.
Note that the converted model is not an actual Oracle database, but a prototype of an Oracle Database. The information is still stored only in the Migration Repository tables.
5.1.6 Generating a Migration
The migration generation phase creates the objects in the target Oracle Database. A script is created and it is run against a selected Oracle connection in the following two ways:
offlinemode, the script is opened in a SQL Worksheet and you have to select the connection and run it manually.
onlinemode, you must provide the target connection in the wizard and the wizard runs the script automatically.
The following steps demonstrate how to perform the generate phase of the migration process in
- Right-click on Converted Database Objects in the Migration Projects panel and select Generate Target.
offlineas the database mode in the Migration Wizard, as shown in Figure 5-15.
Figure 5-15 Selecting the Database Mode
- Choose a connection in the target Oracle Database, as shown in Figure 5-16.
Figure 5-16 Creating Oracle Database Connection for Target User dbo_simpledemo12c
The database objects are not created under the connection selected in this step. However, this connection must have enough privileges to create other users and objects.
22.214.171.124 Creating a Target Oracle User
Create a connection to the newly created user (described in step 3), as shown in Figure 5-17. At this point, the Sybase database objects are migrated to Oracle Database, but the data is not migrated till now.
Figure 5-17 Targeting an Oracle User
Description of "Figure 5-17 Targeting an Oracle User"
5.1.7 Moving the Data
Perform the following steps to move the data to Oracle Database:
- Right-click the Converted Database Objects node and select Move Data, as shown in Figure 5-18.
Figure 5-18 Moving the Data from Sybase Database to Oracle Database
onlineas the data move mode in the Move Data screen.
You can select
offlineas the data move mode if the migration process involves large amount of data.
- Click Next. The Summary screen appears.
- Click Finish.
You can browse the database objects to verify the data is moved to Oracle database.
See Also:Oracle SQL Developer User's Guide
5.2 Generating Migration Reports
Oracle SQL Developer provides a number of reports on the migration process to help identify tasks and issues to resolve. Click or double-click on the migrated project in the Migration Projects navigator. A report will appear on the right panel with a number of tabs and children reports, as shown in Figure 5-19.
Figure 5-19 Generating Migration Reports
The Analysis report provides information about the size of the migrated database like the number of objects, line sizes, and so on, as shown in Figure 5-20.
Figure 5-20 Migration Analysis Report
The Target Status report provides information about the status of the migrated objects in the Target database. First, select a target connection with enough privileges to view the status of other schema objects and then select refresh. Objects that are present in the converted model, but are missing from the target Oracle Database, are listed as missing. These objects can be either valid or invalid.
Figure 5-21 Target Status Report
The Data Quality tab provides information about the number of rows in the target Oracle Database compared with the source database. Perform the following steps to compare the databases:
- Select a converted model, a source connection, and a target connection.
- Click Analyse.
- Click Refresh.
This performs a
count(*)function on each table in the source and the target database. So, it is advisable not to perform this operation on production data.