SQL Anywhere database - Convert 9.02 to Oracle 10g

Author

Convert 9.02 to Oracle 10g

Peter Brooks

2005-06-19, 7:23 am

We have been using SQLAnywhere right from 5.5 and our main database is
at SQLAnywhere 9.02 version.
All of our sitesuse a combination of 7.04, 8.02 or 9.02 databases. We
have recently been approached by a prospective client to convert our
database to Oracle 10g as they are an Oracle shop or MSSQL site only.
From the brief exposure that I have had wth Oracle and MSSQL, I think
that the conversion to Oracle 10g from SQLAnywhere 9.02 may be easier
- I am however happy to be persuaded otherwise. The database schema is
fairly large (690 tables, 1500 views) and we make extensive use of
stored procedures as well as before and after triggers. Could anyone
please advise on the specific differences that may exist between 9.02
and 10g and suggested ways around the problems. A few problems that I
have identified so far are: * 10g lack of autoincrement columns.
* no support for temporary tables in stored procedures.
* how do I get the connection id (ie connection_property(
'number') in
9.02);
* 10g char is fixed length vs 9.02 char is variable length.

Any comments , suggestions or reference sites that I could access
would be most welcome - I feel like a real novice again not knowing
where to even start with this type of conversion.

Thank in advance.

Petre Brooks

Glenn Paulley

2005-06-19, 9:23 am

Peter Brooks <PBrooks@Infobase.com.au> wrote in
news:gjbab113uf2j3l0
0iq8ap1o9qh9a2o5dgr@
4ax.com:

> We have been using SQLAnywhere right from 5.5 and our main database is
> at SQLAnywhere 9.02 version.
> All of our sitesuse a combination of 7.04, 8.02 or 9.02 databases. We
> have recently been approached by a prospective client to convert our
> database to Oracle 10g as they are an Oracle shop or MSSQL site only.
> From the brief exposure that I have had wth Oracle and MSSQL, I think
> that the conversion to Oracle 10g from SQLAnywhere 9.02 may be easier
> - I am however happy to be persuaded otherwise. The database schema is
> fairly large (690 tables, 1500 views) and we make extensive use of
> stored procedures as well as before and after triggers. Could anyone
> please advise on the specific differences that may exist between 9.02
> and 10g and suggested ways around the problems. A few problems that I
> have identified so far are: * 10g lack of autoincrement columns.
> * no support for temporary tables in stored procedures.
> * how do I get the connection id (ie connection_property(
'number') in
> 9.02);
> * 10g char is fixed length vs 9.02 char is variable length.
>
> Any comments , suggestions or reference sites that I could access
> would be most welcome - I feel like a real novice again not knowing
> where to even start with this type of conversion.
>
> Thank in advance.
>
> Petre Brooks
>
>

I don't have any magic for you. It might be helpful to export/import your
schema through a tool such as PowerDesigner which is largely database
agnostic, and once the schema is output you can then tweak what's
necessary. Certainly other regular contributors to this forum have a much
greater amount of experience in doing this kind of porting than I do, and
may be able to give you some advice about doing this.

Other issues that will complicate your porting effort to Oracle are:

1) Oracle's PL/SQL dialect is considerably different than that of SQL
Anywhere's native Watcom dialect, or Transact-SQL. Most if not all of
your procedures and triggers will likely require extensive reworking.

2) Oracle transactions use "snapshot isolation" all the time, which have
different semantics from the standard isolation levels (0-3) that SQL
Anywhere and MSSQL support. In a nutshell, snapshot isolation means that
a transaction cannot "see" any changes to the database, except its own,
that occur after the transaction starts. This makes it impossible, as
just one example, to develop an application that desires the use of a
KEYSET cursor to ensure the application is sensitive to updates.

3) By default, SQL Anywhere string attributes are NOT blank-padded, and
use case-insensitive string comparisons. Oracle supports only ANSI
standard behaviour: blank-padded strings and case sensitive searching.

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Breck Carter [TeamSybase]

2005-06-19, 8:23 pm

Converting to MSS may be easier than Oracle. I rank MSS easiest, ASE
next, Oracle third, and DB2 in tenth place (since DB2 procedural SQL
is so weak)... I've worked on them all, in both directions, and have
the scars to prove it :)

Even if you use PowerDesigner on the schema, you must check all the
results, and you must understand all the data type differences. Some
folks, myself included, prefer to run dbunload -n and then just edit
the reload.sql by hand and/or by script... for MSS especially. For one
client this process was completely automated to maintain MSS in
parallel with ASA, but they didn't need the procedures or triggers on
the MSS side.

FWIW Oracle supports autoincrementing via sequences; in some respects,
sequences are more awkward, in other respects they are far more
flexible. Which is a code word I sometimes use for "bizarre".

Oracle also supports GLOBAL TEMPORARY tables which might work for some
situations. However, as Glenn points out, Oracle's stored procedure
SQL is so vastly different you will be recoding everything, not just
line by line, but with a different architecture probably involving
"packages".

And with Oracle triggers, you will have to deal with the dreaded error
"ORA-04091: table XXXX is mutating"... do a Google search on that when
you need a dose of depression :)

There have been other discussions of this topic; do a Google Groups
search with this operator included:

group:sybase.public.sqlanywhere.*

Breck

On 19 Jun 2005 01:57:34 -0700, Peter Brooks <PBrooks@Infobase.com.au>
wrote:

>We have been using SQLAnywhere right from 5.5 and our main database is
>at SQLAnywhere 9.02 version.
>All of our sitesuse a combination of 7.04, 8.02 or 9.02 databases. We
>have recently been approached by a prospective client to convert our
>database to Oracle 10g as they are an Oracle shop or MSSQL site only.
>From the brief exposure that I have had wth Oracle and MSSQL, I think
>that the conversion to Oracle 10g from SQLAnywhere 9.02 may be easier
>- I am however happy to be persuaded otherwise. The database schema is
>fairly large (690 tables, 1500 views) and we make extensive use of
>stored procedures as well as before and after triggers. Could anyone
>please advise on the specific differences that may exist between 9.02
>and 10g and suggested ways around the problems. A few problems that I
>have identified so far are: * 10g lack of autoincrement columns.
>* no support for temporary tables in stored procedures.
>* how do I get the connection id (ie connection_property(
'number') in
>9.02);
>* 10g char is fixed length vs 9.02 char is variable length.
>
>Any comments , suggestions or reference sites that I could access
>would be most welcome - I feel like a real novice again not knowing
>where to even start with this type of conversion.
>
>Thank in advance.
>
>Petre Brooks

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Sponsored Links