November 06, 2015

One of the most important parts of database migration is having a full understanding of the current systems and the business applications that they support. Without this fundamental knowledge, you will not only run the risk of missing key components in the system, but you will also not be able to strategically improve the performance of the database.

Oracle to Sybase Migration Overview

In order for your oracle to sybase migration to be successful, it is best to follow the suggestions that are outlined below:

  • Review your requirements, standards, best practices, and business preferences to develop a comprehensive strategic plan. Make sure this plan has been created or at least validated by experts in both Oracle and Sybase systems.
  • Ensure that you have full access to all Oracle PL/SQL code being used in both client applications and the database applications.
  • Sybase ASE expertise is needed for performance tuning and optimization.
  • Have a solid testing process and way to validate the accuracy and efficiency of the database system.
  • Have a plan and strategy for validating the migrated components.
  • Use the Sybase Power Designer to reverse-engineer the Oracle schema and convert it to the Sybase ASE equivalent.

In migrating Oracle PL/SQL code to the Sybase Transact – SQL you will need to manage this for the stored procedures, triggers, SQL Functions as well as the code in the client applications.

The maintenance, administration, and monitoring tasks are highly specific for each database system and a simple migration will not work for these items. Use a seasoned expert in each database brand to avoid issues with your migration.

One of the first things that you will need to evaluate and determine is if the database migration will require the following:

  • Simple Conversion – example Oracle VARCHAR2 to Sybase VARCHAR
  • Partial Rewrite – example Oracle Sequence to Sybase ASE Identity Columns
  • A Major Rewrite – systems that don’t have a direct equivalent Sybase ASE feature like Oracle Flashback.

Oracle to Sybase Migration Table

Create a table of the systems you will need to implement to replicate or improve the database functionality.

Oracle Sybase
Oracle Database Server Sybase ASE (Adaptive Server Enterprise)
Oracle OLAP and DW Sybase IQ
Oracle RAC Sybase ASE Cluster Edition
Oracle Times Ten Sybase ASE In-Memory Database
Oracle Golden Gate Sybase Replication Server

Useful tips to use in your migration:

  • In order to obtain the Oracle schema, there are two methods that can be used, reverse engineer the schema from the actual database or use existing DDL scripts from which the schema was created in the past.
  • Before starting any migration be sure to identify tables that have chained rows. To find chained rows in Oracle, run this query:
    • SELECT owner, table_name, chain_cnt
    • FROM dba_tables WHERE chain_cnt > 0
  • Then, to look up the chained rows, you can input the following command:
    • ANALYZE TABLE table-name LIST CHAINED ROWS INTO chained-row-table
  • Make sure to check for reserved words before you migrate your Oracle schema, stored procedure, function or trigger for problematic or non-migratable keywords. For example, Oracle allows SQL keywords to be used as identifiers and ASE does not.