SQL Anywhere database - Migration from ASA to Oracle?

Author

Migration from ASA to Oracle?

2005-05-27, 11:23 am

I have an app deployed to a site and for years sat on one desktop and the IT
world that owned it was happy. Now, the users want to upscale into a typical
C/S setup with the db on one of their servers. Straight forward so we
thought. However, the IT brass say they are a Oracle "only" shop and do not
want to introduce anything other than an Oracle db into the server(s). I
guess for fear that ASA may disrupt their setup.

At this stage of discussion it appears that they are not going to budge on
this issue and are insisting that I migrate the ASA over to Oracle.
Migration is option Z as far as I'm concerned but will investigate it abit.
So, has anybody had experience doing this? What tool would I need and is
there any bumps to watch out for ie data type conversion etc?

Also, I'm ASA all the way so to counter their concerns are there any good
documents that explain how ASA runs fine beside other systems?

Any thoughts on this would be appreciated

TIA

Greg

Breck Carter

2005-05-27, 8:23 pm

An ASA server runs just fine on the same box as other DBMS
products, and it has a much small footprint than Oracle.

Depending on the load, there will be some competition for
CPU cycles and RAM, but one presumes if there's no capacity
left you will supply more hardware.

The difficulty of converting a database from ASA to Oracle
almost entirely depends on how many stored procedures and
triggers have been written and how many advanced features of
WATCOM SQL have been used. Nevertheless, Oracle PL/SQL is
quite powerful and you should not run into any showstoppers;
not like DB2, for example, which can't support many simple
SQL Anywhere features at all.

Datatypes are an issue; everything's convertible, but you
must understand how the base datatypes work. Both products
support bogus "domain types" that simply confuse the issue;
e.g., MONEY in SQL Anywhere, INTEGER in Oracle, etc...
"trust the base, Luke!" :)

CHAR is an challenge; convert it to Oracle's VARCHAR, not
CHAR.

You can use a tool like PowerDesigner to reverse-engineer
ASA and forward-engineer to Oracle, but IMO you will spend a
lot of time fixing the mistakes it makes. Better to dbunload
the schema to a *.SQL file and manually edit it. If you have
1,000 tables, you might write some simple scripts or
programs to help the editing (or go ahead and try
PowerDesigner). But you have to examine each and every
table, column and constraint to make sure it gets converted
correctly.

The biggest difficulty is often the Oracle DBAs; as a class,
they tend to be arrogant, narrow-minded, controlling and
absolutely ignorant of the world outside Oracle. However, I
am sure that is NOT the case in your organization :)

I sometimes wish I was an Oracle consultant, I could make
sacks more money. But SQL Anywhere is such a dream to work
with, that's not an option.

Breck

> I have an app deployed to a site and for years sat on one
> desktop and the IT world that owned it was happy. Now,
> the users want to upscale into a typical C/S setup with
> the db on one of their servers. Straight forward so we
> thought. However, the IT brass say they are a Oracle
> "only" shop and do not want to introduce anything other
> than an Oracle db into the server(s). I guess for fear
> that ASA may disrupt their setup.
>
> At this stage of discussion it appears that they are not
> going to budge on this issue and are insisting that I
> migrate the ASA over to Oracle. Migration is option Z as
> far as I'm concerned but will investigate it abit. So,
> has anybody had experience doing this? What tool would I
> need and is there any bumps to watch out for ie data type
> conversion etc?
>
> Also, I'm ASA all the way so to counter their concerns are
> there any good documents that explain how ASA runs fine
> beside other systems?
>
> Any thoughts on this would be appreciated
>
> TIA
>
> Greg
>
>

Daigo Moriwaki

2005-05-30, 3:23 am

Breck Carter wrote:
> Datatypes are an issue; everything's convertible, but you
> must understand how the base datatypes work. Both products
> support bogus "domain types" that simply confuse the issue;
> e.g., MONEY in SQL Anywhere, INTEGER in Oracle, etc...
> "trust the base, Luke!" :)
>
> CHAR is an challenge; convert it to Oracle's VARCHAR, not
> CHAR.

I add another issue.
Oracle can not have empty string values; empty string is NULL.

Regards,
Daigo

--
Daigo Moriwaki
iAnywhere Solutions K.K. [Tokyo]
daigo.moriwaki@ianywhere.com

2005-05-30, 9:23 am

Thanks to both for the pointers

If I were to proceed the thing I do have going for me is the db would be
empty, no data to migrate, no SP's etc
Still.....a road I'd rather not walk

Anyone know of any reputable sites where ASA shares the same bed with
Oracle, etc? Maybe if I can ease their anxiety that might help grease the
wheels to get ASA approved. I did provide tech specs to them but I think
their using that for toilet paper<g>

Thanks again

Greg
..
"Daigo Moriwaki" <daigo.moriwaki@ianywhere.com> wrote in message
news:gi6qm2-hb9.ln1@hermes.sybase.com...
> Breck Carter wrote:
>
> I add another issue.
> Oracle can not have empty string values; empty string is NULL.
>
> Regards,
> Daigo
>
> --
> Daigo Moriwaki
> iAnywhere Solutions K.K. [Tokyo]
> daigo.moriwaki@ianywhere.com

Paul Horan[TeamSybase]

2005-05-30, 11:23 am

<GW> wrote in message news:429b2273@forums
-1-dub...
> Thanks to both for the pointers
>
> If I were to proceed the thing I do have going for me is the db would be empty, no data to migrate, no SP's etc
> Still.....a road I'd rather not walk
>
> Anyone know of any reputable sites where ASA shares the same bed with Oracle, etc? Maybe if I can ease their anxiety
> that might help grease the wheels to get ASA approved. I did provide tech specs to them but I think their using that
> for toilet paper<g>
>
> Thanks again
>
> Greg
> .

NBC Universal (CNBC, MSNBC, Bravo, USA Network, SciFi Channel, Trio) is a huge Oracle shop, but runs nearly all of their
sales, traffic, and billing operations for airtime revenue through a single ASA database.

--
Paul Horan[TeamSybase]

Greg Fenton

2005-05-30, 11:23 am

GW wrote:
>
> Anyone know of any reputable sites where ASA shares the same bed with
> Oracle, etc? Maybe if I can ease their anxiety that might help grease the
> wheels to get ASA approved.
>

You might consider contacting someone in iAnywhere sales and see if they
could provide a technical resource to address your co-workers' concerns.

Or, could you draw up a list of the concerns and post them here so that
we can address the points directly?

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Sponsored Links