SQL Anywhere database - Upgrade 7 to 9: domains not handled properly

Author

Upgrade 7 to 9: domains not handled properly

Peter Simonsen

2005-10-27, 7:41 am

ASA 7.0.4.3538
ASA 9.0.2.3198
Win XP

I'm trying to prepare an upgrade from ASA7 to ASA9.
The database contains 50 domains or so.

When I use unload, the reload.sql doesn't include
the domains. So the create tables fail.

So I tried unload -n to unload structure only.
That creates a file with the following
sections before 'Create tables':
Create userids and grant user permissions
Create removed system userids and grant permissions
Create user-defined types
Install user-defined classes
Grant user memberships
Create external servers

All these sections are missing from the full unload!!

So, having extracted the full structure, I tried
to unload data only. However when reloading the data,
the order is not constraint ordered, so foreign key
constraints block reloading data.

Any ideas, besides dropping FK contraints and adding them
adding afterwards?

TIA

regards/Peter Simonsen

Greg Fenton

2005-10-27, 7:41 am

Peter Simonsen wrote:
>
> When I use unload, the reload.sql doesn't include
> the domains. So the create tables fail.
>

Which version of dbunload are you using? You should be using the 9.x
version.

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

Peter Simonsen

2005-10-27, 7:41 am

Greg Fenton wrote:
> Which version of dbunload are you using? You should be using the 9.x
> version.

I am. I set CD to the ..\SQL Anywhere 9\win32\ folder at start.

I also tried using the 7 version of unload with same result.

regards/Peter Simonsen

Breck Carter [TeamSybase]

2005-10-27, 7:41 am

Please show us the full dbunload command line.

Did you use the -e or -t options? AFAIK they do not behave as one
might *expect* them to, the age-old debate about features versus bugs
:)

Breck

On 26 Oct 2005 06:46:39 -0700, Peter Simonsen <"Peter Simonsen">
wrote:

>ASA 7.0.4.3538
>ASA 9.0.2.3198
>Win XP
>
>I'm trying to prepare an upgrade from ASA7 to ASA9.
>The database contains 50 domains or so.
>
>When I use unload, the reload.sql doesn't include
>the domains. So the create tables fail.
>
>So I tried unload -n to unload structure only.
>That creates a file with the following
>sections before 'Create tables':
> Create userids and grant user permissions
> Create removed system userids and grant permissions
> Create user-defined types
> Install user-defined classes
> Grant user memberships
> Create external servers
>
>All these sections are missing from the full unload!!
>
>So, having extracted the full structure, I tried
>to unload data only. However when reloading the data,
>the order is not constraint ordered, so foreign key
>constraints block reloading data.
>
>Any ideas, besides dropping FK contraints and adding them
>adding afterwards?
>
>TIA
>
>regards/Peter Simonsen

--
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

Peter Simonsen

2005-10-27, 7:41 am

Breck Carter [TeamSybase] wrote:
> Please show us the full dbunload command line.
>
> Did you use the -e or -t options? AFAIK they do not behave as one
> might *expect* them to, the age-old debate about features versus bugs

The first time around I use this unload:

dbunload -ii -c "dbn=old;uid=..;pwd=..;eng=unex" -o c:\db\reload.log -r c:\db\reload.sql c:\db

I couldn't reload from that, so for further testing I tried:

dbunload -e tb1,tb2,tb3 -ii -c "dbn=old;uid=..;pwd=..;eng=unex" -o c:\db\reload.log -r c:\db\reload.sql c:\db

because three of tables were very large and testing took too long.

Although I haven't double checked. I'm pretty sure I had the mentioned issue before using the -e option.
But it takes ages for me to verify.

However, I did find work around:
dbunload -ii -n -c "dbn=old;uid=..;pwd=..;eng=unex" -o c:\db\str.log -r c:\db\str_reload.sql c:\db
dbunload -ii -d -c "dbn=old;uid=..;pwd=..;eng=unex" -o c:\db\dat.log -r c:\db\dat_reload.sql c:\db

I then manually replace the empty "Reload data" section in str_reload.sql with the one in dat_reload.sql.
And reload with

dbisql -c "dbn=new;uid=..;pwd=.. ;eng=unex;charset=cp
1252" -codepage 1252 c:\db\str_reload.sql

I tested it yesterday, and it works fine. So I'm going with that.

Thanks.

regards/Peter Simonsen

Kurt Lichtner

2005-10-28, 1:23 pm

Peter,

I'm not able to reproduce that particular dbunload behaviour here. How
large is your database? Would you be willing to send a copy so we can figure
out exactly what the problem is?

Kurt
--
Kurt Lichtner
iAnywhere Solutions R&D
Core Server

"Peter Simonsen" <"Peter Simonsen"> wrote in message
news:4360975d$1@foru
ms-1-dub...
> Breck Carter [TeamSybase] wrote:
>
> The first time around I use this unload:
>
> dbunload -ii -c "dbn=old;uid=..;pwd=..;eng=unex" -o c:\db\reload.log -r
> c:\db\reload.sql c:\db
>
> I couldn't reload from that, so for further testing I tried:
>
> dbunload -e tb1,tb2,tb3 -ii -c "dbn=old;uid=..;pwd=..;eng=unex" -o
> c:\db\reload.log -r c:\db\reload.sql c:\db
>
> because three of tables were very large and testing took too long.
>
> Although I haven't double checked. I'm pretty sure I had the mentioned
> issue before using the -e option.
> But it takes ages for me to verify.
>
> However, I did find work around:
> dbunload -ii -n -c "dbn=old;uid=..;pwd=..;eng=unex" -o c:\db\str.log -r
> c:\db\str_reload.sql c:\db
> dbunload -ii -d -c "dbn=old;uid=..;pwd=..;eng=unex" -o c:\db\dat.log -r
> c:\db\dat_reload.sql c:\db
>
> I then manually replace the empty "Reload data" section in str_reload.sql
> with the one in dat_reload.sql.
> And reload with
>
> dbisql -c "dbn=new;uid=..;pwd=.. ;eng=unex;charset=cp
1252" -codepage 1252
> c:\db\str_reload.sql
>
> I tested it yesterday, and it works fine. So I'm going with that.
>
> Thanks.
>
> regards/Peter Simonsen

Sponsored Links