ORACLE TO SYBASE ASE MIGRATION GUIDERev.1.1Table of Contents1Introduction . Audience . 4What You Should Already Know . 4About Sybase ASE . 4Oracle systems targeted by this Guide . 4Oracle products vs. Sybase products . 4Oracle / Sybase database versions covered . 5Sybase ASE documents and references . 5How to use this Migration Guide. process outline . 6Success factors . 7Not covered by this guide: Project aspects . 7Not covered by this guide: Sybase ASE-specific tuning . 7Pre-migration complexity assessment . checklist: datatypes . 8Oracle checklist: category "Simple Conversion" . 8Oracle checklist: category "Partial Rewrite" . 12Oracle checklist: category "Major Rewrite" . 15Database Schema Migration . 174.1Obtaining the Oracle schema definition . 17Using existing DDL scripts . 17Reverse-engineering the existing schema . 174.2Using Sybase PowerDesigner for database schema migration . 184.2.1PowerDesigner schema conversion steps . 184.3Reverse-engineering the Oracle schema without Sybase PowerDesigner . 194.4Special cases in schema migration . 194.5Mapping the Oracle schema to Sybase ASE databases . 204.6Schema-related Oracle-Sybase terminology . 214.7Mapping Oracle Datatypes to Sybase ASE . 224.7.1Chained Oracle data rows . 234.8Search for Sybase ASE reserved words and keywords in Oracle . 244.9Choosing a lock scheme for Sybase ASE tables . 244.10The Oracle DUAL Table . server-level aspects . set . 26Database server case sensitivity ('sort order') . 26Server configuration parameters . 27Storage. 27Migrating the User Logins . 275.5.1User passwords . 285.6Permissions . 286Data Migration . 3Unload Oracle data into ASCII files; load into ASE with "bcp" utility . 30Loading into ASE with "bcp" . 30Unloading from Oracle: FACT (3rd-party tool) . 30Unloading from Oracle: Roll-your-own PL/SQL utility to export Oracle data. 31Unloading from Oracle: use Oracle SQL Developer . 31Use Sybase's Enterprise Connect Data Access (ECDA) Option for Oracle . 31ECDA Example . 31Use Sybase Replication Server Heterogeneous Edition (RSHE) for Oracle . 32Minimal migration downtime with Replication . 32Initial materialization for the replication setup . 32Other considerations . 33Introduction2

ORACLE TO SYBASE ASE MIGRATION GUIDE6.46.57Rev.1.1Use a 3rd-party ETL tool that supports both Oracle and Sybase ASE . 33Oracle datatypes requiring special attention for migration . 33Migrating PL/SQL to Transact-SQL . 357.17.28Locations of PL/SQL code . 35SwisSQL – assistance for PL/SQL migration to T-SQL . 35Transactions and Locking, Oracle vs. Sybase . 378.18.28.3Oracle MVCC vs. Sybase locking . 37Transaction-related migration issues . 37Using ASE implicit/chained transaction mode . 388.3.1Transactional DDL . 388.3.2Transaction processing in stored procedures . 388.4Using ASE explicit/unchained transaction mode . 388.5Using ASE transactional concurrency enhancements . 388.6Other transactional aspects . 399Miscellaneous migration aspects. 419. . 41Sequences . 41Error/Exception handling . 42Outer join limitations . 42Migrating JDBC/ODBC/ Applications . 439.5.1JDBC . 439.6Oracle Forms . 431011DBA Tasks Cross-Reference . 44Oracle-to-Sybase Migration Cross-Reference . 4811.111.211.3Oracle-to-Sybase ASE migration: category "Simple Conversion" . 48Oracle-to-Sybase ASE migration: category "Partial Rewrite" . 56Oracle-to-Sybase ASE migration: category "Major Rewrite" . 63Revision history:Rev.1.0: September 2011: initial versionRev.1.1: November 2011: expanded the topic on case-sensitivity; various other additions 2011 Sybase, Inc.Sybase, Transact-SQL, Adaptive Server Enterprise and Replication Server are registered trademarks of Sybase, Inc.Other product or brand names may be (registered) trademarks of their respective owners.Introduction3

ORACLE TO SYBASE ASE MIGRATION GUIDERev.1.11 INTRODUCTIONThis Migration Guide aims to provide guidance and assistance with the migration process from an Oracle database toSybase ASE (Adaptive Server Enterprise). By "migration" we mean the process of changing a client-server applicationcurrently 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-relatedaspects of Sybase ASE are not covered (also see section 2.4).1.1Intended AudienceThis Migration Guide is intended for anyone involved in migrating an Oracle database to Sybase Adaptive ServerEnterprise (ASE).1.2What You Should Already KnowThe 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 Oracledatabase.1.3About Sybase ASESybase ASE is the database that powers Wall Street. ASE has been delivering rock-solid reliability and top-levelperformance for the past 25 years. Sybase ASE has a lower total cost of ownership than Oracle, and delivers betterperformance on the same hardware. Sybase ASE is ready to be the database in any application that runs on Oracle today.1.4Oracle systems targeted by this GuideThis Migration Guide can be used for migrations of any type of Oracle-based system. While it does not focus on aspecific type of application, workload or system design, the majority of Oracle-based migration candidate systems areexpected to be transactional systems.This Migration Guide specifically does not aim at migrating SAP Business Suite installations currently running onOracle, 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.5Oracle products vs. Sybase productsBoth Oracle and Sybase provide a range of database-related products. The following list illustrates how the main highlevel Oracle products compared to Sybase products. While this list is deliberately kept brief, it provides some basicguidance 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 usuallyexpected to be OLTP-oriented systems, though this is not required.OracleSybaseOracle Database ServerSybase ASE (Adaptive Server Enterprise)Oracle OLAP and DWSybase IQOracle RACSybase ASE Cluster EditionOracle Times TenSybase ASE In-Memory DatabaseOracle StreamsSybase Replication ServerIntroduction4

ORACLE TO SYBASE ASE MIGRATION GUIDE1.6Rev.1.1Oracle / Sybase database versions coveredThis 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 notrecommended and not covered by this Migration Guide.If not otherwise specified all references to "ASE" or "Adaptive Server" are considered references to "Sybase AdaptiveServer Enterprise".1.7Sybase ASE documents and referencesFor more detailed information about Sybase ASE , see for general documents andwhitepapers.For ASE documentation and product manuals, see . Specifically, the following ASEdocuments are relevant: Transact SQL User's Guide Reference Manual System Administration Guide Utility Guide Performance and Tuning GuideIn addition, Sybase provides technical training for ASE. For details on courses and availability, see

ORACLE TO SYBASE ASE MIGRATION GUIDERev.1.12 HOW TO USE THIS MIGRATION GUIDEThe focus of this Migration Guide is on the database-specific technical aspects of an Oracle to Sybase databasemigration project. In particular, it aims to help identify and assess the complexity of the migration when scoping out amigration project, so as to avoid overlooking or underestimating potentially difficult aspects of the system to bemigrated. In addition, it helps establish a migration approach by providing and suggesting technical options for variousaspects of the migration process.2.1Migration process outlineThis Migration Guide recommends a phased approach towards migrating from Oracle to Sybase ASE. The followingphases 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 inchapter 3. This activity involves identifying specific Oracle features used in the current system which may nothave a direct Sybase equivalent.It is strongly recommended to pay sufficient attention to this activity, as this helps to avoidoverlooking 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 andconvert 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 usuallydriven 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 applicationdowntime 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 bothfor SQL located in the database (i.e. stored procedures, triggers, SQL functions) as well as for SQL code inclient applications. This step tends to be the most complex part of a migration.It is recommended to evaluate the use of SwisSQL (a 3rd-party product; see section 7.2) to assist with PL/SQLmigration.To assist with this migration step, chapter 11 contains cross-reference between Oracle features and their SybaseASE equivalent, in the three categories "Simple conversion possible", "Partial rewrite required" and "Majorrewrite 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 eachdatabase brand, "migration" would be a misnomer.Chapter 10 contains a cross-reference of some common DBA aspects. This is however not sufficient forperforming 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 afterbeing migrated to Sybase ASE.As a next step, Sybase ASE-specific optimization and tuning will likely be required in order to achieve desiredperformance levels. Sybase ASE-specific tuning is not covered by this Migration Guide; see section 2.4.How to use this Migration Guide6

ORACLE TO SYBASE ASE MIGRATION GUIDE2.2Rev.1.1Success factorsDatabase migrations can be complex, and costly migration failures need to be avoided. The following success factorsapply to any Oracle-to-Sybase database migration project:2.3 Domain knowledge of the business application(s), system and environment. It is essential to have a full andcomplete understanding of all applications that access the Oracle database being migrated. This includes theclient applications that connect to the Oracle database directly, but also applications that indirectly access thedatabase, for example through an application server.For all these applications, it needs to be understood which data the application accesses in the database, andhow it modifies such data. Any SQL code submitted to the database by the application must be identified, aswell as how such SQL code can be changed. Availability of sufficient Oracle expertise to analyze all aspects of the database is an absolute requirement. Akey activity is to identify which specific Oracle features are used (as per the checklists in chapter 3), especiallythose 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 migrateddatabase system. At a later stage in the migration project, more specialized Sybase expertise will likely beneeded for Sybase ASE-specific performance tuning and optimization. Having such expertise available at anearly stage may be helpful. A comprehensive testing process and production-like environment for validating the migration approach andthe affected software applications against the migrated Sybase database. For best results, it is highlyrecommended to use a copy of production data (as close as possible) as well as hardware which is similar in sizeto production.Not covered by this guide: Project aspectsThis Migration Guide does not prescribe or suggest how to organize a migration project in terms of preparation, settingup testing procedures, validating the migrated components, etc. These aspects of a migration project are left torequirements, 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 notmean that such activities should not be performed. On the contrary, these activities are essential, and it is recommendedto follow generally accepted best practices with respect to software testing and validation.2.4Not covered by this guide: Sybase ASE-specific tuningThe primary purpose of this Migration Guide is to assist in creating a functionally equivalent Sybase ASE-based systemcompared with the original Oracle-based system. The purpose of this Migration Guide is not to provide guidance forarriving at an optimally tuned Sybase ASE system; while Sybase ASE-specific tuning will likely be necessary as part of amigration 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, thereader 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.How to use this Migration Guide7

ORACLE TO SYBASE ASE MIGRATION GUIDERev.1.13 PRE-MIGRATION COMPLEXITY ASSESSMENTFor a database migration project, it is crucial to have an accurate assessment of the complexity of the migration ahead oftime. 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 bedrawn 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 possibleAn Oracle feature or statement can be mapped and converted directly to a (nearly) identical Sybase ASEfeature, requiring no syntax changes or only simple, local syntax changes only.Examples: most datatype mappings (Oracle VARCHAR2 Sybase VARCHAR); simple SELECT statements Partial rewrite requiredAn Oracle feature or statement can be mapped to a partly equivalent Sybase ASE feature, requiring potentiallysignificant syntax changes and possibly partial rewriting of algorithms.Example: Oracle sequences Sybase ASE identity columns Major rewrite requiredAn Oracle feature or statement has no directly equivalent Sybase ASE feature, requiring rewriting orredesigning 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 migrationcomplexity is likely to occur. Before deciding to start the migration project, there should be a clear view of the numberof occurrences of the features in the categories "Partial rewrite required" and "Major rewrite required" above, and of theeffort 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 arange 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 checklistswith the corresponding Sybase ASE equivalent for each Oracle feature.3.1Oracle checklist: datatypesVerify the datatypes used in the current Oracle application; see section 4.7.Also see:3.2 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.Oracle checklist: category "Simple Conversion"#cases foundOracle checklist: category "Simple Conversion"Connecting to an Oracle schemaThe Oracle “slash” is contained at the end of some of the procedures examined.Semicolon (as a statement delimiter in PL/SQL)Pre-migration complexity assessment8

ORACLE TO SYBASE ASE MIGRATION GUIDE#cases foundRev.1.1Oracle checklist: category "Simple Conversion"The Oracle DUAL tableSET SAVEPOINT savepoint-nameVariable/Parameter declarations; naming syntaxAssign default value in variable declarationMultiple declarations with a single DECLARE keywordDeclarations without DECLARE keyword in declaration section of stored procedures/functionsVariable assignmentTransferring table data into a variableConstants%TYPE denotes the datatype of a column in an existing tableDynamic SQL (Execute-immediate)Loops with LOOP/END LOOPFOR loopsCURSOR loopsOracle Outer join syntaxSET TRANSACTION READ WRITEALTER TABLE mytable TRUNCATE PARTITION partition nameCREATE OR REPLACE PROCEDURE (or FUNCTION)ALTER PROCEDURE (or FUNCTI

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.File Size: 1MB