Skip to Content

Our Migration Roadmap so far ……..

1.  Planning the migration
2.  Designing architecture migration
->  Implementing schema migration
4. Implementing data migration
5. Implementing application migration
6. Implementing operation migration
7. Validating the migration

This is the third installment for best practices for migrating to SAP SYBASE ASE server.  Regardless of which database vendor you are moving from, the steps outlined below will form the overall guide for our talk.  We are now onto the third step implementing the schema unto the target system.    We will also target our talk mainly to the non-SAP Applications. 

To recap: at this stage we have the internal design of the SAP Sybase ASE server completed, at least a start of it.  We should have the number of user databases mapped out and where the schema or schemas are located.  That’s an important point to hammer down at this stage as this will allow the code migration team to start to change the SQL statements to reflect this new structure.  (Refer to previous talks where the SQL code references need to be aware of internal changes: select * from <schema>..<table> as one possible design.)

To start, our tools need to connect to the original source database server using an ODBC connection.  The simplest method is to use SAP Sybase PowerDesigner with the native ODBC database drivers.  As a side note we can also use JDBC drivers as well.
 
In the main screen of Powerdesigner : Choose File | Reverse engineer| Database

Best Practises for Migration blog.jpg

Choose the source database type and you can change the name of the resultant physical model if desired.

Best Practises for Migration blog.jpg

Choose your objects.  In our example I am choosing all tables for all schemas.  If you had multiple schemas that you want to convert to be in a specific “schema” user database, a recommended action would be to choose or select only those objects you are interested in.  This will allow you to group SQL by user database and make the build process far easier.

Best Practises for Migration blog.jpg

We have alternate methods for converting compiled code such as procedures and triggers outside of the PowerDesigner tool.  PowerDesigner gives us the option of automatically changing datatypes and ‘create table’ syntax by simply choosing the target database using a drop down list.  Its quite simple and this gathering, analysis and conversion of table objects only took a couple of clicks and menu options in the Powerdesigner tool.

At this point we have created the base architecture for the ASE server: we have the databases created and we have used PowerDesigner to automatically create the users (aka logins) and the create table and index statements.  It is recommended to create the tables/indexes and logins/users first.  Permissions need to be assigned to match the original permissions from the source database server. 

In the migration process a Systems Security Officer (SSO) should be assigned from your group to create and administer the login passowrds and object permissions.

If your source database server was 100% ANSI compliant then your journey in schema migration would be done.  In reality this is probably not the case and we now turn our attention to converting source SQL to target SAP Sybase SQL.   There are several third-party and SAP Sybase tools that are used by SAP Global Technology Services as part of their migration toolkit.  To simplify, what existing back-end code that can be automated, will be parsed and automated using the tools by SAP Sybase Services.  To help you understand the code automation process the following table details code templates (aka code migration design) that are typical of Back-end or database layer compiled code.  Migration solutions range from automated examples to slight code modifications.

Sample source code on
Back-end Oracle featured.
Equivalent ASE code

CREATE PROCEDURE p

(a IN number, b OUT number, c IN OUT number)

  IS …

CREATE PROCEDURE p

@a int, @b int output, @c int output

AS …

CREATE PROCEDURE p1

AS

BEGIN

…statements…

END;

CREATE PROCEDURE p2

AS

BEGIN

p1;

END;

CREATE PROCEDURE p1

AS

BEGIN

…statements…

END

go

CREATE PROCEDURE p2

AS

BEGIN

EXECUTE p1

END

g

AFTER triggers (on statement level) After event trigger code instance supported.
select myfunc(123);

In Sybase ASE, the name of the executed SQL function must always be preceded by the owner’s name (aka schema name)

select dbo.myfunc(123)

select jsmith.yourfunc(456)

Of course we also have complied code in the Application layer and other related systems.  These code conversions will be featured later.

In sumary, the take-away points to focus on are:

• Use SAP Sybase PowerDesigner for the migration of the table/index and login objects.
• Use SAP Sybase Global Services as they have expercise and tools to automate the code conversion.
• How the ASE System is architected affects the amount of syntax code changes.  This step sets the stage for every code change to follow.  Using SAP Sybase Global Services Architects will mitigate this risk.

That’s it for now, next blog I will focus on the next step in our migration high-level steps: Implementing data migration…featuring methods for moving data from Oracle or Microsoft (or others) to SAP Sybase.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Sheila Wood

    Hi Jonathan,

    Can you recommend online education materials (or a specific SAP Education course) to enable the reader to perform these reverse-engineering and SQL code generation tasks in SAP Sybase PowerDesigner?

    Thanks,

    Sheila

    (0) 
    1. Jonathan Wesley-James Post author

      Well there seems to be quite an extensive list of courses offered on Powerdesigner.  From data modeling to using Powerdesigner as an aid to migration.   The reverse engineering you are referring to is best covered in the DEV355: Data Modeling with Powerdesigner 16.   While we think of Powerdesigner as a physical modeling tol for tables, indexes, etc.  there is conceptual and logical modeling facilities as well as OO modeling.

      Remote and on-site  Classes on these aspects are available and all interested parties should reference these courses on the following URL: https://training.sap.com/ca/en/courses-and-curricula/sybase

      (0) 

Leave a Reply