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
Choose the source database type and you can change the name of the resultant physical model if desired.
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.
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.
2 Comments
