e-dokumen.com

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