ORACLE TO SYBASE ASE MIGRATION GUIDE

(1)

O

RACLE TO

S

YBASE

ASE

(2)

Introduction 2

1 INTRODUCTION

This Migration Guide aims to provide guidance and assistance with the migration process from an Oracle database to Sybase ASE (Adaptive Server Enterprise). By "migration" we mean the process of changing a client-server application currently using the Oracle database as its RDBMS, such that it uses the Sybase ASE database instead.

This Migration Guide has as its primary focus to migrate functionality from Oracle to Sybase ASE. Performance-related aspects of Sybase ASE are not covered (also see section 2.4).

1.1 Intended Audience

This Migration Guide is intended for anyone involved in migrating an Oracle database to Sybase Adaptive Server Enterprise (ASE).

1.2 What You Should Already Know

The reader is expected to be familiar with relational database concepts, and with Oracle in particular. In addition, introductory knowledge of the Sybase ASE RDBMS is required.

For a database migration to be successful, there should be a detailed understanding of the current Oracle-based system, including its high- and low-level architecture, as well as the interaction between the client application and the Oracle database.

1.3 About Sybase ASE

Sybase ASE is the database that powers Wall Street. ASE has been delivering rock-solid reliability and top-level performance for the past 25 years. Sybase ASE has a lower total cost of ownership than Oracle, and delivers better performance on the same hardware. Sybase ASE is ready to be the database in any application that runs on Oracle today.

1.4 Oracle systems targeted by this Guide

This Migration Guide can be used for migrations of any type of Oracle-based system. While it does not focus on a specific type of application, workload or system design, the majority of Oracle-based migration candidate systems are expected to be transactional systems.

This Migration Guide specifically does not aim at migrating SAP Business Suite installations currently running on Oracle, to run on Sybase ASE instead. Since such migrations are covered by product and service offerings by SAP, interested customers should contact SAP directly.

1.5 Oracle products vs. Sybase products

Both Oracle and Sybase provide a range of database-related products. The following list illustrates how the main high-level Oracle products compared to Sybase products. While this list is deliberately kept brief, it provides some basic guidance on how Oracle and Sybase can be aligned.

The focus of this Migration Guide is on migration from Oracle Database Server to Sybase ASE. These are usually expected to be OLTP-oriented systems, though this is not required.

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 Streams Sybase Replication Server

(5)

Introduction 5

1.6 Oracle / Sybase database versions covered

This document pertains to Oracle versions 9i, 10g and 11g.

The migration target is assumed to be Sybase ASE version 15.7 (or later). Migration to earlier ASE versions is not recommended and not covered by this Migration Guide.

If not otherwise specified all references to "ASE" or "Adaptive Server" are considered references to "Sybase Adaptive Server Enterprise".

1.7 Sybase ASE documents and references

For more detailed information about Sybase ASE , see http://www.sybase.com/ase for general documents and whitepapers.

See http://www.sybase.com/support/techdocs/migration for resources specifically focused at (different types of) migrations. These include the document you are currently reading, as well as “Migrating an Oracle Database to SAP Sybase ASE with PowerDesigner and ECDA (A Step-By-Step Practical Guide)” .

For ASE documentation and product manuals, see http://infocenter.sybase.com . Specifically, the following ASE documents are relevant:

 Transact SQL User's Guide  Reference Manual

 System Administration Guide  Utility Guide

 Performance and Tuning Guide

In addition, Sybase provides technical training for ASE. For details on courses and availability, see http://www.sybase.com/education.

(6)

How to use this Migration Guide 6

2 HOW TO USE THIS MIGRATION GUIDE

The focus of this Migration Guide is on the database-specific technical aspects of an Oracle to Sybase database migration project. In particular, it aims to help identify and assess the complexity of the migration when scoping out a migration project, so as to avoid overlooking or underestimating potentially difficult aspects of the system to be migrated. In addition, it helps establish a migration approach by providing and suggesting technical options for various aspects of the migration process.

2.1 Migration process outline

This Migration Guide recommends a phased approach towards migrating from Oracle to Sybase ASE. The following phases can be identified, in order of importance and priority:

1. Before starting the actual migration project, assess the complexity of the migration using the checklist in chapter 3. This activity involves identifying specific Oracle features used in the current system which may not have a direct Sybase equivalent.

It is strongly recommended to pay sufficient attention to this activity, as this helps to avoid overlooking or underestimating the most difficult parts of a migration.

2. Migrating the database schema is the necessary first step of an actual migration (described in chapter 4). This Migration Guide recommends using Sybase PowerDesigner to reverse-engineer the Oracle schema and convert it to the Sybase ASE equivalent.

3. Migrating server-level aspects such as users (described in chapter 5).

4. Migrating the data itself (described in chapter 6). The approach chosen to perform the data migration is usually driven by the maximum tolerable downtime allowed for the application.

It is recommended to consider using 3rd_{-party tools for extracting data from Oracle. If minimal application} downtime is crucial, consider Sybase Replication Server to reduce this downtime to minutes rather than hours. 5. Migrating Oracle PL/SQL code to Sybase Transact-SQL (also see chapter 7). This needs to be performed both

for SQL located in the database (i.e. stored procedures, triggers, SQL functions) as well as for SQL code in client applications. This step tends to be the most complex part of a migration.

To assist with this migration step, chapter 11 contains cross-reference between Oracle features and their Sybase ASE equivalent, in the three categories "Simple conversion possible", "Partial rewrite required" and "Major rewrite required". This cross-reference is an extended version of the Oracle checklist in chapter 3.

6. Migration of vendor-specific infrastructural components, such as JDBC drivers (see section 9.5).

7. Convert the maintenance, administration and monitoring tasks. Since these aspects are highly specific for each database brand, "migration" would be a misnomer.

Chapter 10 contains a cross-reference of some common DBA aspects. This is however not sufficient for performing a migration, and specific DBA skills, both for Oracle and Sybase, will be required.

8. The primary focus of this Migration Guide is to help achieve functional equivalence of the Oracle system after being migrated to Sybase ASE.

As a next step, Sybase ASE-specific optimization and tuning will likely be required in order to achieve desired performance levels. Sybase ASE-specific tuning is not covered by this Migration Guide; see section 2.4.

2.2 Success factors

Database migrations can be complex, and costly migration failures need to be avoided. The following success factors apply to any Oracle-to-Sybase database migration project:

(7)

How to use this Migration Guide 7  Domain knowledge of the business application(s), system and environment. It is essential to have a full and

complete understanding of all applications that access the Oracle database being migrated. This includes the client applications that connect to the Oracle database directly, but also applications that indirectly access the database, for example through an application server.

For all these applications, it needs to be understood which data the application accesses in the database, and how it modifies such data. Any SQL code submitted to the database by the application must be identified, as well as how such SQL code can be changed.

 Availability of sufficient Oracle expertise to analyze all aspects of the database is an absolute requirement. A key activity is to identify which specific Oracle features are used (as per the checklists in chapter 3), especially those which do not have a direct Sybase equivalent.

 Full access to all Oracle PL/SQL code being used, both in the database and in all client applications.  As a minimum, sufficient understanding of Sybase ASE in order to create a functionally working migrated

database system. At a later stage in the migration project, more specialized Sybase expertise will likely be needed for Sybase ASE-specific performance tuning and optimization. Having such expertise available at an early stage may be helpful.

 A comprehensive testing process and production-like environment for validating the migration approach and the affected software applications against the migrated Sybase database. For best results, it is highly

recommended to use a copy of production data (as close as possible) as well as hardware which is similar in size to production.

2.3 Not covered by this guide: Project aspects

This Migration Guide does not prescribe or suggest how to organize a migration project in terms of preparation, setting up testing procedures, validating the migrated components, etc. These aspects of a migration project are left to

requirements, standards, best practices and preferences of the organization undertaking the emigration effort. Please note that the absence of specific recommendations for testing and validation of migrated components does not mean that such activities should not be performed. On the contrary, these activities are essential, and it is recommended to follow generally accepted best practices with respect to software testing and validation.

2.4 Not covered by this guide: Sybase ASE-specific tuning

The primary purpose of this Migration Guide is to assist in creating a functionally equivalent Sybase ASE-based system compared with the original Oracle-based system. The purpose of this Migration Guide is not to provide guidance for arriving at an optimally tuned Sybase ASE system; while Sybase ASE-specific tuning will likely be necessary as part of a migration project, this Migration Guide deliberately makes no attempt to cover such tuning aspects.

Since ASE-specific tuning is considered to be mostly unrelated to any Oracle-specific aspects or considerations, the reader is referred to the Sybase ASE documentation for background and recommendations about Sybase ASE tuning., specifically the System Administration Guide and the Performance and Tuning manuals.

(8)

Pre-migration complexity assessment 8

3 PRE-MIGRATION COMPLEXITY ASSESSMENT

For a database migration project, it is crucial to have an accurate assessment of the complexity of the migration ahead of time. Here, "complexity" refers to how Oracle-specific features can be mapped to the feature set of Sybase ASE. Before starting the actual migration effort, the current Oracle system should be closely inspected and a list should be drawn up of all types of Oracle-specific features being used, and how many times these occur.

For each feature used, it should be determined in which of the following three categories it falls:  Simple conversion possible

An Oracle feature or statement can be mapped and converted directly to a (nearly) identical Sybase ASE feature, requiring no syntax changes or only simple, local syntax changes only.

Examples: most datatype mappings (Oracle VARCHAR2  Sybase VARCHAR); simple SELECT statements  Partial rewrite required

An Oracle feature or statement can be mapped to a partly equivalent Sybase ASE feature, requiring potentially significant syntax changes and possibly partial rewriting of algorithms.

Example: Oracle sequences  Sybase ASE identity columns  Major rewrite required

An Oracle feature or statement has no directly equivalent Sybase ASE feature, requiring rewriting or redesigning of algorithms or parts of applications.

Example: Oracle Flashback; Oracle row-level triggers.

Categorizing the Oracle features used by the system being migrated helps to identify the areas where most migration complexity is likely to occur. Before deciding to start the migration project, there should be a clear view of the number of occurrences of the features in the categories "Partial rewrite required" and "Major rewrite required" above, and of the effort to migrate these, especially those in the Major rewrite required" category.

To assist with this complexity assessment, below are three checklists, corresponding to the categories above, listing a range of Oracle features. Note that additional Oracle features may occur in your system that are not in these checklists; these should be taken into account just as well.

The checklists below list the Oracle features only very briefly. Chapter 11 contains extended versions of these checklists with the corresponding Sybase ASE equivalent for each Oracle feature.

3.1 Oracle checklist: datatypes

Verify the datatypes used in the current Oracle application; see section 4.7. Also see:

 section 4.7.1 for considerations that apply when migrating data rows whose length exceed an Oracle disk block;  section 6.5 for considerations that apply when migrating particular datatypes.

3.2 Oracle checklist: category "Simple Conversion"

#cases found Oracle checklist: category "Simple Conversion"

Connecting to an Oracle schema

The Oracle SQL*Plus “slash” character sends preceding PL/SQL text to the Oracle server. Semicolon (as a statement delimiter in PL/SQL)

(9)

The Oracle DUAL table

SET SAVEPOINT savepoint-name

Variable/Parameter declarations; naming syntax Assign default value in variable declaration

Multiple variable declarations with a single DECLARE keyword

Declarations without DECLARE keyword in declaration section of stored procedures/functions Variable assignment

Transferring table data into a variable Constants

%TYPE denotes the datatype of a column in an existing table Dynamic SQL (Execute-immediate)

Loops with LOOP/END LOOP

FOR loops CURSOR loops Oracle Outer join syntax

SET TRANSACTION READ WRITE

ALTER TABLE mytable TRUNCATE PARTITION partition_name CREATE OR REPLACE PROCEDURE (or FUNCTION) ALTER PROCEDURE (or FUNCTION)

CREATE PROCEDURE… IS…

Stored procedure execution with named parameters (param => value) Stored procedure execution with positional parameters (:var)

Stored procedure execution

SQL Function declaration with DETERMINISTIC keyword Execution of a SQL Function

DECLARE CURSOR cursor-name IS… Oracle cursors

(10)

Cursor Attribute %ISOPEN

Cursor Attributes %FOUND, %NOTFOUND Cursor Attribute %ROWCOUNT

AFTER triggers (on statement level) INSTEAD OF triggers (on views)

SQL%ROWCOUNT

BOOLEAN datatype (for PL/SQL variables only) MERGE statement

Partitioned tables with composite partitioning

Performance-optimized native PL/SQL datatypes (for PL/SQL variables only) BINARY_INTEGER

BINARY_DOUBLE BINARY_FLOAT IF-THEN-ELSE

Multiple statements in an IF-THEN-ELSE branch Conditional test based on EXISTS subquery String concatenation operator: ||

userenv('sessionid')

MOD(X,Y) CEIL()

TRUNC(number) SUBSTR()

SUBSTR() function with two parameters

LENGTH() CHR() REPLACE()

TO_CHAR(expression)

(11)

TO_CHAR(expression, format-string) TO_NUMBER(expression)

Date/time functions and calculations

SYSDATE, SYSTIMESTAMP TRUNC(date/time [,unit])

LAST_DAY() NVL() function

Inconsistent use of upper/lowercase for identifiers (Oracle is case-insenstive for identifiers) Identifiers that are Sybase ASE reserved words (see section 4.8)

INSTR() function with two parameters

Derived tables (also known as "inline views") without correlation name ALTER TABLE … SPLIT PARTITION…

ALTER TABLE … MERGE PARTITIONS…

Quoted identifiers. Oracle allows using quoted identifiers by enclosing an identifier in double quotes.

(12)

3.3 Oracle checklist: category "Partial Rewrite"

For the Oracle features listed below, migration to partly equivalent Sybase ASE features is possible, although potentially significant syntax changes and possibly partial rewriting of algorithms may be required.

#cases found Oracle checklist: category "Partial Rewrite"

Database links External tables Sequences

Table-valued User-defined SQL Functions Pipelined Table Functions

Synonyms

Comments on database objects Bitmap indexes

Temporary tables

IS TABLE OF, AS VARRAY(n)OF Nested tables

Object tables %ROWTYPE

Define a PL/SQL record type by enumerating the fields with IS RECORD OF or TYPE…IS RECORD

Non-integer RETURN value in stored procedure User-defined Packages

Overloaded stored procedures

PL/SQL Exception handling; defining exception handlers

SQLCODE, SQLERRM

RAISE_APPLICATION_ERROR

Column Encryption LOB locators

(13)

Data compression

Retrieving data to the client in stored procedures using

DBMS_OUTPUT package

DBMS_*, UTL_* package calls (excl. DBMS_OUTPUT)

SDO_* package calls SQL*Loader (sqlldr)

Materialized Views

Global variables (in a PL/SQL package) INTERSECT construct MINUS construct Specific SQL clauses AS OF AS OF TIMESTAMP CONNECT BY DIMENSION DIMENSION BY EXCLUDE GROUPING SETS INCLUDE MEASURES RETURN ALL ROWS RETURN UPDATED ROWS PARTITION BY REFERENCE SYSTIMESTAMP CROSS CUBE FOR KEEP MAIN MODEL NAV NOCYCLE NOWAIT ON ONLY RULES SAMPLE SEED SKIP IGNORE ITERATE NATURAL NULLS NULLS FIRST NULLS LAST ROLLUP SIBLINGS SINGLE REFERENCE LOCKED START WITH UNIQUE UNPIVOT WAIT INITCAP( string-expression ) INSTR() function with three or four parameters

NVL2() function

DECODE() function

Primary key and foreign key with different datatypes, different precision/scale (for numeric datatypes) or different length (for character datatypes)

(14)

SQL functions where the last statement is not RETURN Derived tables (also known as "inline views") using "with" syntax UNIONs in cursors

PRAGMA directives Autonomous transactions

ON DELETE CASCADE constraints XMLTYPE (XML data type)

XML functions extract(), existsnode(), xmlexists(), etc ROWID

(15)

3.4 Oracle checklist: category "Major Rewrite"

For the Oracle features listed below, no direct equivalent is available in Sybase ASE. Consequently, rewriting or redesigning algorithms or parts of applications will be required.

#cases found Oracle checklist: category "Major Rewrite"

Oracle MVCC (Multi-Version Concurrency Control; "writers don‟t block readers, readers don't block writers")

Relevant aspects:

 Applications or queries relying on non-blocking MVCC  Long-running transactions

 DDL in transactions

 SET TRANSACTION READ ONLY  SQL*Plus autocommit/commit-on-exit

SQL*Plus

BEFORE triggers

Triggers on row level (BEFORE and AFTER)

Multiple triggers for a DML type on a table

REF CURSOR

Regular Expressions; functions REGEXP_LIKE(), REGEXP_SUBSTR(), REGEXP_REPLACE(), REGEXP_INSTR()

Windowing queries (SELECT…OVER(…) …)

SQL function OUT/IN OUT parameters

Non-deterministic SQL Functions (functions whose result may be independent of the function input parameters)

SQL Aggregate Functions

BFILE datatype

Oracle Streams; Oracle Data Guard

(16)

#cases found Oracle checklist: category "Major Rewrite"

Oracle Flashback

Oracle Snapshot Standby

Oracle SQL Plan Management

AWR (Automatic Workload Repository) Oracle Advanced Queuing

Packages for PL/SQL web access

OWA_CUSTOM, OWA_CX, OWA_OPT_LOCK, OWA_SEC, OWA_TEXT, OWA_UTIL

(17)

Database Schema Migration 17

4 DATABASE SCHEMA MIGRATION

The first step in migrating an Oracle database to Sybase ASE is to migrate the database schema. Here, "database schema" refers to the physical data model. In other words, to the definition of the database structure, specifically of the tables, columns, indexes, views, datatypes, etc., typically expressed in SQL DDL (Data Definition Language), for example as e.g. 'create table' statements.

There is some potential for terminology clash around the term "schema":

 As a generic database concept, "schema" is the definition of the database structure as described above, regardless of which database user owns the object(s).

 In Oracle, a "schema" is an central concept. It is a collection of database objects (tables, views, stored procedures, triggers, etc) owned by a particular user. A decision will need to be made as to how to map an Oracle schema to an ASE schema; see section 4.5 for details.

 In Sybase ASE, a "schema" is usually understood to refer to the generic concept of database schema.  NB: For completeness, ASE also has a command create schema authorization which creates a number of

tables and views plus associated permission settings as a transactional unit. This command is however rarely used in ASE and it is not used or discussed further in this Migration Guide.

For clarity, this Migration Guide will use "Oracle schema" when referring to the Oracle-specific interpretation of "schema". In all other cases, "schema" refers to the generic concept of "database schema" as above.

Please note: none of the methods describes in this chapter converts Oracle's PL/SQL code into Sybase's Transact-SQL, which is needed when converting stored procedures, triggers and SQL functions. 3rd_{-party tools which such} capabilities exist; see section 7.2 for more information.

4.1 Obtaining the Oracle schema definition

When migrating the database schema from Oracle to ASE, we first need to obtain the Oracle schema, and then convert this to a format and syntax that can be used in Sybase ASE.

In principle there are two methods to obtain the Oracle schema:

 Use existing DDL scripts from which the Oracle schema was created in the past; typically, in well-organized environments, such scripts are kept in a source code repository under version control.

 Reverse-engineer the Oracle schema from the actual Oracle database.

4.1.1 Using existing DDL scripts

If not using a tool to reverse engineer and migrate the schema, then using existing DDL scripts would be the ideal starting point, since no further work is required to obtain the Oracle schema. However, the question is whether it can be guaranteed that such scripts are up-to-date and identical to the actual Oracle database. It is not uncommon to see that changes to the database schema have been made without updating the DDL scripts in the repository. Clearly, basing oneself on incorrect DDL scripts will cause problems later in the migration process.

When existing Oracle DDL scripts are available, the next step is to convert the datatypes to Sybase ASE. Section 4.7 describes the mapping from Oracle datatypes to Sybase ASE. In addition, some aspects of the Oracle schema require special attention; see section 4.4.

4.1.2 Reverse-engineering the existing schema

The alternative to using existing scripts is to reverse-engineer the Oracle schema from the actual Oracle database. This is more work, and may require special tools, but it has the advantage that the generated DDL is correct.

When existing scripts cannot be used or relied upon, this Migration Guide recommends using Sybase PowerDesigner for reverse-engineering and migrating the database schema. Since PowerDesigner can reverse-engineer all tables, indexes,

(18)

Database Schema Migration 18 etc, and automatically convert the Oracle datatypes into their ASE equivalent, this is the fastest and most efficient schema migration method available.

 Section 4.2 describes how to use PowerDesigner for this purpose.

Section 4.3 describes a possible approach to reverse-engineer the schema without PowerDesigner.

4.2 Using Sybase PowerDesigner for database schema migration

Sybase PowerDesigner is arguably the most advanced data modeling tool in the market. It is a stand-alone tool, running on Windows. PowerDesigner supports over 30 database types, including Oracle and Sybase ASE.

For more information on PowerDesigner, see http://www.sybase.com/powerdesigner .

With PowerDesigner it is relatively straightforward to reverse-engineer most of the Oracle schema and convert it to Sybase ASE. The central concept used by PowerDesigner is the PowerDesigner Physical Data Model (PDM). This is a database-independent model which can be converted to the SQL DDL dialect of each supported database.

4.2.1 PowerDesigner schema conversion steps

For detailed, step-by-step instructions on how to use PowerDesigner to convert the database schema from Oracle to Sybase ASE, see the document “Migrating an Oracle Database to SAP Sybase ASE with PowerDesigner and ECDA (A Step-By-Step Practical Guide)” at http://www.sybase.com/support/techdocs/migration.

Once the schema is reverse-engineered, run the completed DDL script in Sybase ASE and check for any errors. Note that some aspects of schema migration cannot be handled by PowerDesigner and will have to be handled differently. These aspects are described in section 4.4.

4.3 Reverse-engineering the Oracle schema without Sybase PowerDesigner

Without using Sybase PowerDesigner, reverse-engineering the schema can be done in a number of ways:

 Use the Oracle SQL*Plus DESC command on all database objects, and process the output so that they are valid DDL statements. This is likely to require significant manual script coding.

 Use the Oracle DBMS_METADATA package to extract DDL for the Oracle objects. This involves SQL statements such as the following (for Oracle table 'MY_TABLE', in schema/user 'SALESAPP'). Note that these are only examples, this is not a complete list of all statement required to perform full reverse-engineering:

SELECT DBMS_METADATA.GET_DDL('TABLE', 'MY_TABLE', 'SALESAPP') FROM DUAL; SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX', 'MY_TABLE', 'SALESAPP') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'SALESAPP') FROM DUAL;

 Use Oracle SQL Developer (a free Java-based tool, downloadable from oracle.com). This uses the DBMS_METADATA package (see previous bullet).

 Use TOAD (a low-cost tool, commonly used in many Oracle environments) to extract the object definitions, and then manually convert the Oracle datatypes into their ASE equivalent. This could be cumbersome when large numbers of tables are involved.

Once the Oracle schema has been reverse-engineered, the Oracle DDL needs to be converted to Sybase ASE syntax, including conversion from the Oracle datatypes to Sybase ASE datatypes. Section 4.7 describes the mapping from Oracle datatypes to Sybase ASE.

In addition, some aspects of the Oracle schema require special attention; see section 4.4.

4.4 Special cases in schema migration

(19)

Database Schema Migration 19  Oracle allows more columns per table than Sybase ASE (the limit depends on the ASE server's page size and

on the table's lock scheme). If the limit in Sybase ASE is exceeded, an error will be raised when trying to create the table. If this occurs, either the ASE server's page size will need to be increased, or the table needs to be split vertically into multiple tables and all queries referencing the table likely have to be modified accordingly  If the length of a column exceeds the maximum allowed length in Sybase ASE (the limit depends on the ASE

server's pagesize and on the table's lock scheme), such columns will have to be split into multiple columns and placed in additional tables. All queries referencing the column likely have to be modified accordingly.

 PowerDesigner converts the Oracle BFILE datatype to the Sybase ASE image datatype. Since BFILE is a datatype used to store a locator (link) to an external binary file stored outside of the database, this is not functionally equivalent so application changes may be required. If a different ASE datatype is required, for example, to hold the name of an externally stored file, change it manually.

 PowerDesigner 15.x cannot automatically convert the Oracle timestamp datatype to bigdatetime in ASE, so this needs to be done manually. PowerDesigner 16.0 (release expected in August 2011) does not have this limitation and will perform the conversion automatically.

 PowerDesigner 15.x cannot reverse-engineer Oracle users or security details (permissions). PowerDesigner 16.0 (release expected in August 2011) does not have this limitation and is capable of handling these aspects.  Since the SQL reserved words are different between Oracle and Sybase ASE, before attempting a database

schema migration, all Oracle objects need to be checked against the Sybase ASE reserved words. Any Oracle identifiers that are also Sybase ASE reserved words, need to be changed first. For a complete list of reserved words in Sybase ASE, see “Adaptive Server Enterprise->Reference Manual: Building Blocks->Reserved Words”. Also see section 4.8 for queries that can be used to search for the occurrence of keywords in the Oracle database.

 The mapping of Oracle user-defined datatypes to ASE can be difficult and may require extensive manual intervention. The key to user-defined datatype migration is to fully understand the underlying base datatype. Note that user-defined datatypes can be nested. For Oracle, user-defined datatypes is an add-on option to the database and is not widely used.

4.5 Mapping the Oracle schema to Sybase ASE databases

Sybase ASE does not have an identical interpretation of the concept of "schema" as the "Oracle schema". When migrating an Oracle schema to Sybase ASE, there are two basic options to map the Oracle schema to Sybase ASE. For the sake of example, let's assume there are two Oracle users john and bill who own an Oracle schema, and each schema has a table named salesdetails.

The options are:

 Perhaps the most straightforward way to migrate, is to map each Oracle schema to a separate ASE database, where each database is owned ('dbo') by the corresponding user. This would result in two ASE databases named john_db and bill_db (different names may of course be chosen), owned by ASE logins john and

bill respectively; each database has table named salesdetails, owned by the dbo database user (the full table name would be dbo.salesdetails).

However, this results in as many ASE databases as there are users owning an Oracle schema, of which there might be many. While an ASE server can hold up to 32786 databases, it is highly impractical from a DBA perspective to have more than 20-50 databases.

 Map all Oracle schemas to a single ASE database with a multi-tenancy model. This means that the ASE database user (which is linked to the ASE server login, which is the equivalent of an Oracle user) is used within the database to identify each object's owner. This will result in a more manageable ASE system since there will be less ASE databases.

(20)

Database Schema Migration 20 and bill have been added as database users. Under each user, a salesdetails table is created, which will have the full name john.salesdetails and bill.salesdetails.

Either option is possible; technically ASE does not favor one over the other, but the multi-tenancy model fits best with ASE's methods for backup and restore.

It should be noted that multi-tenancy models are sometimes incorrectly seen as security weaknesses since it would be easier for user bill to access john's tables, since they are located in the same ASE database. This is however not justified: if standard best practices around ASE security are followed, then security can be fully guaranteed.

One consideration around multi-tenancy databases is that a backup of a database contains the data from all users in that database. If this is undesirable, for example because each user wants to have a backup copy of his own database, then the first option above (separate ASE databases for each user) should be followed instead.

Lastly, it may also be the case that there is only one Oracle schema. In that case, there is no need to qualify the ASE tables with the owner name since they will all be owned by the dbo user.

4.6 Schema-related Oracle-Sybase terminology

Following is the high-level terminology mapping of Oracle concepts to Sybase concepts. This table is not intended to be used for direct migration purposes, but only as high-level terminology guidance.

Oracle Sybase ASE

Database Database Server

Schema Database and objects owned by the same user.

Tablespace Aspects of ASE databaseand/or database device and/or segment

(system/sysaux tablespaceASE master database; temporary tablespaceASE tempdb database;

user-defined tablespacedatabase device and/or segment) Segment A database object that has space allocated (table, index,

materialized view) Undo/rollback tablespace Transaction log

Online redo logs Transaction log

User User, Login (see section 5.5)

Role Role

Table Table

Temporary table Temporary table

View View

Materialized View No direct equivalent

Cluster No direct equivalent

Index Non-unique index

Index-organized table Table with clustered index Column-level check constraint Column-level check constraint

(21)

Oracle Sybase ASE

Column default Column default

Unique key Unique key or identity property for a column

Primary key Primary key

Foreign key Foreign key

Constraints Constraints

Collections In PL/SQL, a collection is an ordered group of elements of the same type, such as VARRAYs or nested tables. PL/SQL Procedure Transact-SQL stored procedure

PL/SQL Function T-SQL user-defined SQL function (SQL UDF)

Triggers Triggers

Package No direct equivalent

Sequences Partly covered by the identity property for a column or dedicated key value table

Snapshot No direct equivalent

Database links, External tables Proxy Tables and Remote Servers

Procedure Stored procedure

Synonym Similar functionality with views for table and view synonyms. All other synonym references must be replaced with fully qualified object strings (database.owner.object) or proxy tables (for synonyms to remote objects).

4.7 Mapping Oracle Datatypes to Sybase ASE

The table below describes how Oracle datatypes can be mapped to Sybase ASE datatypes. In most cases the mapping of datatypes is straightforward.

For the Oracle datatypes CHAR, VARCHAR2 and RAW, the ASE server page size determines whether or not the mapping can take place; the technical background is that ASE requires a row, and therefore every column, to fit on an ASE database page. By default, ASE uses a 2KB server page size, but 4KB, 8KB and 16KB are also possible. The maximum allowed column length for a column for each ASE server page size depends on various factors such as whether the column is fixed- or variable length and the ASE table's lock scheme. To display full details, run the command dbcc serverlimits in ASE.

Oracle Description Sybase ASE Comments / When to use

NUMBER(x) Oracle NUMBER(x) datatypes with 0 decimals can be converted into an equivalent Sybase ASE datatypes.

BIGINT length of NUMBER datatype > 10

INTEGER length of NUMBER datatype between 6 and 10 and data values <= 2 billion SMALLINT length of NUMBER datatype is

(22)

32767

TINYINT length of NUMBER datatype between 2 and 3 and data values <= 255 BIT length of NUMBER datatype = 1 NUMBER(x,y) alternatively to the mapping path

above, these Sybase ASE datatypes can be used.

NUMERIC(x,y) DECIMAL(x,y)

translates the Oracle NUMBER datatype one-to-one.

MONEY SMALLMONEY

MONEY and SMALLMONEY store monetary data.; 4 digits of precision to the right of the decimal point, and 16 / 6 digits to the left for MONEY / SMALLMONEY respectively. FLOAT maximum FLOAT precision in

Oracle is approx. 38

DOUBLE precision of actual values > 15

FLOAT precision of actual values <= 15 CHAR(x) maximum CHAR size in Oracle

is 2000 bytes

CHAR(x) if ASE page size is 4kb or greater; and if ASE page size is 2kb and x <= 1958 TEXT if none of the above conditions apply VARCHAR2(x) maximum VARCHAR2 size in

Oracle is 4000 bytes for columns (for PL/SQL variables, the max. size is 32767)

VARCHAR(x) if ASE page size is 8kb or greater; if ASE page size is 4kb and x <= 3988; if ASE page size is 2kb and x <= 1948

TEXT if none of the above conditions apply DATE date/ time precision in Oracle is

up to one second.

DATETIME Sybase ASE‟s DATETIME has a precision of 1/300th_{of a second.} TIMESTAMP [WITH [LOCAL] TIME ZONE] precision of Oracle‟s TIMESTAMP is 1/100000000th of a second

BIGDATETIME Sybase ASE‟s BIGDATETIME has a precision of 1 microsecond. ASE does not support time zones.

ROWID a pseudo column in Oracle, does not represent a true datatype

NUMERIC IDENTITY

Also see ROWID on page 62

CLOB Oracle‟s max. storage capacity for CLOB is 128TB

TEXT Sybase ASE can hold a max. of 2GB per column; IQ can hold up to 2PB NCLOB Oracle‟s max. storage capacity for

NCLOB is 128TB

UNITEXT Sybase ASE can hold a max. of 2GB per column; IQ can hold up to 2PB BLOB Oracle‟s max. storage capacity for

BLOB is 128TB

IMAGE Sybase ASE can hold a max. of 2GB per column; IQ can hold up to 2PB LONG Oracle‟s max. storage capacity for

LONG is 2GB

TEXT

RAW(x) the RAW datatype in Oracle has a max precision of 2000 bytes

BINARY(x) VARBINARY(x)

if ASE page size is 4kb or greater; and if ASE page size is 2kb and x <= 1954 IMAGE if none of the above conditions apply

(23)

LONG RAW Oracle‟s max. storage capacity for LONG RAW is 2GB

IMAGE

CHAR(1) if this is a packed bit column maintained by a PL/SQL function set / unset / retrieve / query on them.

BIT

BFILE BFILE stores a locator (link) to a binary file outside of the database

no direct equivalent

4.7.1 Chained Oracle data rows

Oracle allows long data rows to exceed the size of a disk block. This is known as 'chained rows'. It is possible that such chained data rows, if they exist in the Oracle database, are too long to be stored in Sybase ASE, which requires that a data rows fits on a data page (which is 2KB, 4KB, 8KB or 16KB; use dbcc serverlimits to find the net max row length allowed in ASE). Also, for tables with more than 255 columns, the rows will always be chained.

It is important to identify tables that have chained rows before starting the migration. To find how many chained rows occur in a table, run this Oracle query:

SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0

If chained rows are found, the Oracle command ANALYZE TABLE table-name LIST CHAINED ROWS

INTO chained-row-table can be used to identify the actual chained rows.

If chained rows are found, it may be needed to modify the data model to ensure that rows are short enough to fit on an ASE page.

4.8 Search for Sybase ASE reserved words and keywords in Oracle

Before you can migrate an Oracle schema or Oracle stored procedure, function or trigger, there needs to be a check for reserved words (keywords) that are already identified as either problematic or non-migratable. Oracle allows SQL keywords to be used as identifiers whereas this is not allowed in ASE. For example, the following is valid PL/SQL:

CREATE TABLE case (begin VARCHAR2(100), when INT)

The following query finds all object names within the Oracle database that are ASE keywords:

select owner, object_name, object_type FROM sys.dba_objects

WHERE object_name = UPPER('<ASE-keyword>')

The following query scans any PL/SQL object within the Oracle database for certain keywords and returns the name and owner of the object as well as the object type for objects containing ASE keywords. This query retrieves the exact code and line number of the occurrence within a stored procedure, function or trigger. Note that this could potentially return a lot of output since the 'line' column may be long. Also note that these keywords could be part of comments or string constants, in which case they can be ignored:

SELECT owner, name, type, line, text FROM sys.dba_source

WHERE instr(UPPER(text), UPPER('<ASE-keyword>')) > 0

The queries above should be run for all Sybase ASE reserved words and keywords. The most practical way of running these queries for all ASE keywords is to insert the ASE keywords in an Oracle table, and then run the above queries as a join with this table.

For a complete list of reserved words and keywords in Sybase ASE, see “Adaptive Server Enterprise->Reference Manual: Building Blocks->Reserved Words”. Reserved words can also be displayed with the following ASE query (but check completeness against the ASE documentation!):

(24)

SELECT name FROM master..spt_values WHERE type = 'W'

4.9 Choosing a lock scheme for Sybase ASE tables

ASE offers a choice of three lock schemes for each database table: allpages, datapages or datarows.

allpages is the oldest lock scheme, as well as the out-of-the-box ASE default. It is slightly more efficient for some types of operations. The datapages, and especially datarows, lock schemes provide fundamentally better concurrency characteristics. The concurrency benefits are likely to be relevant when migrating from Oracle to Sybase ASE due to the difference in transaction handling (as described in chapter 8).

It is recommended to configure datapages or datarows as the default lock scheme in Sybase ASE. datapages is more efficient, but datarows provides better concurrency (datarows locking is also known as row-level locking). Changing between datarows and datapages for an existing table is instantaneous. In contrast, large tables with the

allpages lock scheme may require long downtimes to if their lock schemes need to be changed to datarows or

datapages since this requires a full conversion of the table and all its indexes.

4.10 The Oracle DUAL Table

In Oracle, a SELECT statement must always be executed against a table, even when retrieving system information, such as the current date/time. For this purpose, Oracle created the DUAL table. Retrieving the system date via SQL looks like this in Oracle:

SELECT sysdate FROM DUAL

Sybase ASE supports SELECT statements that do not have a FROM clause. The same query in Sybase ASE would look like this:

SELECT getdate()

To avoid rewriting existing SELECTs that use the DUAL table, it is possible to create a table named DUAL in ASE, which must always contain one and only row:

create table DUAL (dummy_col char(1) unique check (dummy_col='X')) insert DUAL values ('X')

go

If Sybase ASE is created case-sensitive (see section 5.2), you may need to create additional tables named dual, Dual, etc, depending on how disciplined the Oracle developers were in using a consistent spelling for the DUAL table. Alternatively, consider editing the Oracle PL/SQL source code to use only "DUAL", or to remove all references to DUAL completely.

(25)

Migrating server-level aspects 25

5 MIGRATING SERVER-LEVEL ASPECTS

The architecture of the database server, and the way it is configured and managed, are quite different between Oracle and Sybase ASE. This chapter lists some migration aspects that require attention, but without claim for completeness. The reader is urged to consult the Sybase documentation, specifically the "System Administration Guide", for full details.

5.1 Character set

When creating a new Sybase ASE server, the character set to be used by the ASE server must be chosen. It is recommended to use the same character for ASE, as is being used for the Oracle database.

While the character set in ASE can be changed at a later point in time, it is strongly recommended to avoid this, and to pick the right character set before migrating any Oracle aspects to ASE.

5.2 Database server case sensitivity ('sort order')

A difference between Oracle and Sybase ASE is that Oracle is not case-sensitive, whereas Sybase ASE is case-sensitive by default. ASE can be configured to be case-insensitive, by installing a case-insensitive 'sort order'.

Moreover, there is also a difference in the scope of case-insensitivity between Oracle and ASE:

 In a case-insensitive ASE server, case-insensitivity applies to both identifiers and to data comparisons; SQL keywords are always case-insensitive in ASE.

 In Oracle, case-insensitivity applies only to identifiers (table names, column names, etc), but, by default, not to data comparisons; it is likely that existing Oracle systems use this default. Note that the above applies only to unquoted identifiers; quoted identifiers are case-sensitive in Oracle, though these are not used often.

As a result, the following two queries will retrieve different data in a case-insensitive Oracle system, but retrieve the same data in a case-insensitive Sybase ASE:

select * from Employees where Name = 'Johnson' select * from Employees where Name = 'JOHNSON'

Also, existing Oracle SQL code refers to the table TEST in different ways - the following all refer to the same table. Inconsistent use of upper- and lower-case spelling for identifiers is not uncommon to occur in practical Oracle systems:

select * from TEST select * from Test select * from test

When using a case-insensitive sort order for Sybase ASE, such SQL statements do not need to be changed. When using the default case-sensitive ASE sort order, all references to a table must use the exact same upper/lowercase spelling, or "table not found" errors will result.

Whether the ASE server should be case-sensitive or case-insensitive is a decision to be made. For ASE, there is no overriding technical advantage to either option.

In practice, the decision probably depends on whether query results may be affected by using a case-insensitive ASE server. If this is the case, then the default case-sensitive ASE configuration should be used, and any Oracle SQL statements referring to identifiers in mixed-case spelling (i.e. TEST and Test) should be changed to use one consistent spelling for the identifiers.

(26)

Migrating server-level aspects 26

5.3 Server configuration parameters

In Oracle, the configuration parameters for the server and database are stored in the initialization file (init.ora) or server parameter file (spfile). These parameters cover a diverse set of resources, such as memory, processes, network, disk, I/O, connections, files, character set, and so on.

It is unlikely that Oracle configuration parameters can be mapped directly to corresponding configuration parameters for Sybase ASE. It may however be useful to be aware of Oracle-specific configuration settings since in some cases some kind of Sybase ASE equivalent could be required.

The non-default values of the Oracle parameters can be obtained using one of the following options:  Convert the server parameter file (spfile) to an initialization parameter file as follows:

CREATE pfile FROM spfile

 Query the database by executing the following statement: SELECT name, value FROM sys.v$spparameter WHERE isspecified = 'TRUE'

5.4 Storage

Most Oracle installations enlist the help of Oracle‟s Automated Storage Manager (ASM). Sybase ASE does not have the equivalent of ASM. Storage must be managed through T-SQL commands, Sybase Control Center , or via Sybase Central (the Sybase database admin GUI tool).

Generally speaking, Sybase ASE recommends the following high-level guidelines for storage:

 For user databases, use raw devices or filesystem devices with directio=true. Never use filesystem devices with dsync=false for user databases; filesystem devices with dsync=true can be used but carry a potentially

significant performance penalty

 For temporary databases, filesystem devices with dsync=false are generally recommended.

 For the underlying storage layer, RAID 0+1 or RAID 1+0 is recommended. Avoid RAID 5 for write-intensive purposes such as the ASE transaction log, unless the storage solution provides a non-volatile write cache to buffer the writes.

 To achieve maximum disk I/O bandwidth, read- and write-intensive data should preferably be spread over as many physical spindles as possible.

Many additional considerations with respect to storage configuration apply. Please refer to the Sybase ASE "System Administration Guide" for details.

5.5 Migrating the User Logins

There are some differences in terminology between Oracle and Sybase ASE around the concept of a "user".

 In Oracle, on instance level: a user is used for authentication, and can also be a schema owner (and thus own database objects, and have permissions on database objects)

 In Sybase ASE, on server level: a loginis used for authentication, but does not own any objects or have object access permission.

A special ASE login is sa - this is the 'super user' in Sybase ASE, comparable to the SYS account in Oracle. This user has access permissions on all database objects and should be restricted to the DBA. For security reasons, applications should never use the sa login to connect to the ASE server.

(27)

Migrating server-level aspects 27  In Sybase ASE, on database level: a user, which maps to a login, can own database objects and have

permissions on database objects.

When migrating from Oracle to Sybase ASE, the most likely scenario is to migrate all Oracle application users to an identically named Sybase ASE login. For each ASE login, a corresponding database user (typically with the same name as the login) is then created to allow that login to access an ASE user database. A login can be given access to multiple ASE databases by creating a corresponding database user in each ASE database.

Alternatively, the guest database user can be created in each ASE user database. However, related security implications should be carefully assessed first.

The resulting structure of ASE logins and database users depends on decisions about how an Oracle schema is migrated to ASE (see section 4.5).

5.5.1 User passwords

Each Oracle user has a password. In ASE, a login has a password. If the Oracle user passwords are known, they can be set identically in ASE; otherwise, new passwords must be set for the ASE logins. ASE login passwords cannot be set to blanks.

5.6 Permissions

It is recommended to use PowerDesigner 16 to reverse-engineer the permissions for accessing (objects in) the Oracle database. If PowerDesigner 16 cannot be used, the permissions will likely have to be converted manually to the Sybase ASE equivalent.

(28)

Data Migration 28

6 DATA MIGRATION

This section describes the methods for migrating data from Oracle to Sybase ASE. It is assumed that the schema has already been migrated.

The main complicating factor is that Oracle provides no tools to unload a table to a flat file in a format that can be read by non-Oracle tools.

Data migration can be performed in a number of ways. Therefore, when choosing an approach, various factors need to be considered, including:

- the complexity of the chosen solution

- the volume of data being migrated

- the available system downtime to perform the data migration during cutover

- the need to become familiar with new software or tools for the purpose of migrating the data

- additional software license costs

In essence, the following options are available for data migration:

 Unload Oracle data into ASCII-formatted flat files, and load these files into ASE with the Sybase "bcp" utility.

If Oracle data can be exported into an ASCII-formatted flat file, then ASE's high-speed loading tool "bcp" can load it into ASE. Since Oracle does not provide a way to achieve this, the user must either use a 3rd_{-party tool}

for this purpose, or create his own PL/SQL utility to essentially spool the data from the database into a flat file. Considerations: This option is often seen as attractive due to the transparency of the migration process: all steps are clearly visible and can be individually developed and tested. Developing your own PL/SQL tool to unload Oracle data is simple, but will perform slowly, thus making it unsuitable for anything but relatively small data volumes. Using a 3rd_{-party tool adds software license costs.}

 Use Sybase's Enterprise Connect Data Access (ECDA) Option for Oracle.

ECDA is a connectivity product by Sybase that enables direct connections from an ASE database into an Oracle database, making it possible to transfer Oracle data directly into ASE. ECDA hooks into the ASE mechanism of "proxy tables".

Considerations: This option can be used when the data volume is such that the data can be transferred in the available migration window. It is unlikely to be suitable for very large data volumes. An advantage is that ECDA takes care of mapping Oracle datatypes to ASE datatypes, and that the migration can be fully performed through SQL.

Using this option requires purchasing Sybase's ECDA product.

 Use Sybase Replication Server Heterogeneous Edition (RSHE) for Oracle

Sybase Replication Server captures database transactions in Oracle and applies these to ASE, thus keeping the ASE database continuously up-to-date. In addition, Replication Server can also initially copy the full contents of the Oracle tables into ASE, in order to initialize the data replication ("materialization of the replication system").

Considerations: Using transactional replication is the only data migration solution where activity on the Oracle database can continue while the data migration is in progress. This means that the migration downtime, during which applications are not available because they must switch from the Oracle database to the ASE database, is independent of the data volume being migrated; this downtime could potentially be very short (e.g. minutes rather than hours).

Using this option requires purchasing Sybase's Replication Server product, as well as learning how to use Replication Server.

Letting Replication Server perform the initial data copy from Oracle to ASE may not be realistic for large data volumes. In this case, the initial materialization of the replication system might be better performed with one of the other options mentioned here.

(29)

Data Migration 29 Considerations: This option is most attractive if the ETL tool is already in use so that no additional software needs to be purchased for the migration alone.

an be used when the data volume is such that the data can be transferred in the available migration window. It can be used for very large data volumes, but a sizeable migration window may be required.

6.1 Unload Oracle data into ASCII files; load into ASE with "bcp" utility

ASE's high-speed data loading utility "bcp" is capable of loading almost any type of appropriately formatted ASCII data file into ASE. However, since Oracle does not provide any tools to export Oracle data into an ASCII-formatted file, the user must either use a 3rd-party tool for this purpose, or create his own PL/SQL utility to essentially spool the data from the database into a flat file. FACT is an example of such a 3-rd party tool.

6.1.1 Loading into ASE with "bcp"

This is an example of loading data from an ASCII file into an ASE table (named mydb..mytable) with bcp: bcp mydb..mytable in mytable.txt –Ulogin –Ppassword –Sserver –c

In practical situations, bcp should also specify which row- and column delimiters are used (bcp -r and -t options) since the defaults (CR and tab) could also occur in the actual data file (which is ASCII, after all). When unloading data into flat ASCII files, proper delimiters should be chosen.

Bcp-in performance is best when all indexes on the tables being loaded, are dropped first. Of course, , depending on the size and number of indexes and the width of the base tables, recreating them afterwards could take a long time on large tables, so this may not be realistic for all cases.

It is usually best to use a large network packet size with bcp (the –A option; also requires configuring the network packet size on the ASE server).

For large tables, it may be advisable to use the bcp –b option to break the load into multiple database transactions. This is typically combined with enabling the "trunc log on checkpt" database option in ASE to avoid the transaction log filling up.

To load only part of a data file, or to load columns in a different order than in the file, a so-called "bcp format file" may be used. For more information on format files, as well as on bcp in general, see the Utility Guide in the ASE

documentation set (http://tinyurl.com/6883kx4).

It is highly recommended to perform multiple bcp operations in parallel (one for each table being loaded). The optimal number of concurrent bcp operations will be determined by the hardware capabilities. If there is only one (or few) large tables that need to be loaded, these can still be loaded using in multiple BCP operations by adding partitioning the table using round robin partitioning and specifying the start and last rows of the data file being loaded into a particular partition number of the table.

Lastly, note that, on Unix/Linux, bcp can read from a "named pipe" (created with the "mkfifo" command). If the utility that extracts the data into a file can write to a named pipe as well, then a lot of time can potentially be saved as follows:

1. Create a named pipe with the Unix/Linux "mkfifo" command 2. Extract the data from Oracle, writing it to the named pipe.

3. Without waiting for the data extraction to complete, start bcp to load the data from the same named pipe. Bcp will read data from the named pipe once it is delivered by the extraction utility, and immediately insert it into ASE.

Instead of first extracting the data and then loading it, the time to transfer the data is now reduced to the longer of (extracting the data, loading the data). This can represent significant time gain.

For more information on bcp and named pipes, please refer to http://tinyurl.com/5urcfrt .

6.1.2 Unloading from Oracle: FACT (3rd-party tool)

FACT ("Fast Extract") is a 3rd_{-party high speed Oracle data export tool that allows ASCII flat file creation, also in} parallel mode. These files can be used as input for the Sybase ASE utility bcp.

(30)

Data Migration 30 For more information about FACT, see http://www.iri.com/products/FACT.

6.1.3 Unloading from Oracle: Roll-your-own PL/SQL utility to export Oracle data

If you want to unload data from Oracle tables into ASCII flat files using only Oracle features, you must create your own PL/SQL utility that essentially spools the data from the database into a flat file. This uses the DBMS_OUTPUT.put_line command in PL/SQL. Here's an example of exporting two columns of table emp using "~" as a column delimiter and CR as a row delimiter. The output from this PL/SQL code should be captured in a flat file:

DECLARE CURSOR emp_cur IS SELECT ename, sal FROM emp;

BEGIN

FOR emp_rec IN emp_cur LOOP

DBMS_OUTPUT.PUT_LINE (emp_rec.ename || '~' || TO_CHAR (emp_rec.sal) ); END LOOP;

END; /

The downside is that this method is likely to be very slow, making it unsuitable for anything but relatively small data volumes. In addition, care must be taken to correctly format/convert each column datatype management.

6.1.4 Unloading from Oracle: use Oracle SQL Developer

Oracle SQL Developer is a free Java-based tool, downloadable from oracle.com. This can be used to create a logical export of the data, whereby a SQL INSERT statement is created for every row.

The downside is that this method is likely to be relatively slow in exporting as well as importing the extracted data, since this is all done on a single-row basis. This may make it unsuitable for large data volumes.

6.2 Use Sybase's Enterprise Connect Data Access (ECDA) Option for Oracle

ECDA is a connectivity product by Sybase that acts as a gateway between Oracle and Sybase ASE. With ECDA, direct connections can be made from an ASE database into an Oracle database, making it possible to transfer Oracle data directly into ASE using only SQL.

The ECDA functionality is exposed as an ASE "proxy table", which maps to the actual Oracle table. By selecting from the proxy table, data is retrieved from the Oracle table and can be inserted directly into an ASE table. Also, it is possible to do things like joining Oracle tables (though their proxy table) with tables in Sybase ASE.

The main advantage of using ECDA is that takes care automatically of the datatype conversions from Oracle to Sybase ASE when the data is retrieved. It also offers the flexibility and control of using the SQL language to access to proxy tables.

ECDA involves starting a separate process outside the ASE server.

6.2.1 ECDA Example

For examples of how to use ECDA in an Oracle migration context, see the document “Migrating an Oracle Database to SAP Sybase ASE with PowerDesigner and ECDA (A Step-By-Step Practical Guide)” at

http://www.sybase.com/support/techdocs/migration.

6.3 Use Sybase Replication Server Heterogeneous Edition (RSHE) for Oracle

Sybase Replication Server is often used by Sybase customers to facilitate migrations between databases. The main attraction is that the required downtime for curring over from the "old" to the "new" database can in principle be very short as far as the database side of things is concerned.

6.3.1 Minimal migration downtime with Replication

Replication Server captures database transactions in Oracle by reading the Oracle redo logs, and then applies these transactions to ASE, thus keeping the ASE database continuously up-to-date. In addition, Replication Server can also

ORACLE TO SYBASE ASE MIGRATION GUIDE

O

RACLE TO

S

YBASE

ASE

Table of Contents

1

INTRODUCTION

1.1

Intended Audience

1.2

What You Should Already Know

1.3

About Sybase ASE

1.4

Oracle systems targeted by this Guide

1.5

Oracle products vs. Sybase products

1.6

Oracle / Sybase database versions covered

1.7

Sybase ASE documents and references

2

HOW TO USE THIS MIGRATION GUIDE

2.1

Migration process outline

2.2

Success factors

2.3

Not covered by this guide: Project aspects

2.4

Not covered by this guide: Sybase ASE-specific tuning

3

PRE-MIGRATION COMPLEXITY ASSESSMENT

3.1

Oracle checklist: datatypes

3.2

Oracle checklist: category "Simple Conversion"

3.3

Oracle checklist: category "Partial Rewrite"

3.4

Oracle checklist: category "Major Rewrite"

4

DATABASE SCHEMA MIGRATION

4.1

Obtaining the Oracle schema definition

4.2

Using Sybase PowerDesigner for database schema migration

4.3

Reverse-engineering the Oracle schema without Sybase PowerDesigner

4.4

Special cases in schema migration

4.5

Mapping the Oracle schema to Sybase ASE databases

4.6

Schema-related Oracle-Sybase terminology

4.7

Mapping Oracle Datatypes to Sybase ASE

4.8

Search for Sybase ASE reserved words and keywords in Oracle

4.9

Choosing a lock scheme for Sybase ASE tables

4.10

The Oracle DUAL Table

5

MIGRATING SERVER-LEVEL ASPECTS

5.1

Character set

5.2

Database server case sensitivity ('sort order')

5.3

Server configuration parameters

5.4

Storage

5.5

Migrating the User Logins

5.6

Permissions

6