SQL Anywhere DB migration from 12 to 16

[Former Member]

Hi All,

We have a Sql Anywhere Version 12 database which we are trying to migrate to Version 16.

As the first step, I am trying to create the Unload.SQL file.

I provided the user name, pass word, file location as the connection parameters. And left Encryption Key as empty as I do not know it.

However the unloading operation is failing with the attached message.

The user name, password are correct. I could connect to the file using these.

Can you please help.

Regards

Kiran.

[chris_keating · Accepted solution]

There are two database server types in SQL Anywhere - the personal server which is dbengX and the network server which is dbsrvX where X is a major version.  You will need one of those to host the database file. By default, the utilities use the personal server to host the database in the absence of a running server. if you do not have the dbengX executable, you will need to use the network server.

Steps

1) Start the database manually

dbsrv16 -n MyRebuildSrv <your database file> (NOTE: MyRebuildSrv can be any value)

2) Connect to the database in Sybase Central. Press F11 and slection Connect with SQL Anywhere 16

3) Set the UID/PWD and set the ServerName to the value of -n in step 1 i.e., MyRebuildSrv

4) Open the Unload Database Wizard and select "Unload a database running on a current version of the server"

This should allow you to unload without the dbeng16 being available.

[Former Member · Accepted solution]

Hi Chris,

Your inputs helped me halfway :-), let me post my current problem.

I have the Personal Server for version 11, a DB file for the same version, and Sql Anywhere 16 installation which looks like Nerwork Server.

I want to migrate the DB file from 11 to 16.

I created the reload.sql and unload files, using Personal Server 11, with option "unload a database running on a current version of the server".

I am now trying to create a blank database in Sql Anywhere 16, Sybase Central using Create Database option but I get the below error.

Can you please let me know if I need a different type of installation. My requirements are below.

1. Perform the migration from 11 to 16.

2. The migrated file would be placed in few usr machines, so should work as a Personal Server.

3. The migrated file will be put on a Server for shared use as well so it should work as a Network Server.

Thanks in advance.

[Former Member · Accepted solution]

you have to install at least SAP SQL Anywhere developer Edition:

SAP Sybase SQL Anywhere 16

There is no database engine or server in client software.

[Former Member]

Hi All,

Now I could migrate my version 11 DB files to version 16.
But on comparison of the data , I noticed that the Double type of data is slightly different.

Ex:

Version 11	Version 16
49314535817.481800	49314535817.481700
30580735450.375000	30580735450.374900
62729866459.480000	62729866459.479900
15340628634.045000	15340628634.044900
27209815621.285000	27209815621.284900
30528340638.455000	30528340638.454900
106673967142.170000	106673967142.169000

You can see that, in version 16 the data has more precision.

Even though the difference is very slight, if your application rounds up this data , the difference can increase, like 27209815621.285000 becomes 27209815621.29 but 27209815621.284900 is 27209815621.28.

Can any one tell me if this behavior is expected with the migration and any work arounds.

Thanks in advance.

Regards,

Kiran

[former_member188493]

Short story: It's a bug in V11 and V16 UNLOAD TABLE where a different value is written to the text file than is stored in the table; e.g., 4.93145358174818E10 is written as 49314535817.481792, which is reloaded as 4.931453581748179E10.

Long story: Experimentation shows that V11 and V12 both store 49314535817.481800 in a DOUBLE as 4.93145358174818E10, but the dbunload-load-in-place conversion from V11 to V16 changes the value to 4.931453581748179E10.

That is a larger difference than the "rounding errors beyond the fifteenth digit" mentioned in the Help.

Further experimentation reveals that both V11 and V16 UNLOAD TABLE statements unload a DOUBLE containing 4.93145358174818E10 as 49314535817.481792, and a subsequent LOAD TABLE results in a value of 4.931453581748179E10 being stored.

-- V11 database via V11 dbisql

CREATE TABLE t ( pkey INTEGER DEFAULT AUTOINCREMENT PRIMARY KEY, d DOUBLE );
INSERT t ( d ) VALUES ( 49314535817.481800 ); -- 49314535817.481700     
INSERT t ( d ) VALUES ( 30580735450.375000 ); -- 30580735450.374900     
INSERT t ( d ) VALUES ( 62729866459.480000 ); -- 62729866459.479900     
INSERT t ( d ) VALUES ( 15340628634.045000 ); -- 15340628634.044900     
INSERT t ( d ) VALUES ( 27209815621.285000 ); -- 27209815621.284900     
INSERT t ( d ) VALUES ( 30528340638.455000 ); -- 30528340638.454900     
INSERT t ( d ) VALUES ( 106673967142.170000 );  -- 106673967142.169000     
COMMIT;
SELECT @@VERSION, d, CAST ( d AS NUMERIC ( 31, 6 ) ) FROM t ORDER BY pkey;

@@VERSION,d,d
'11.0.1.3158',4.93145358174818E10,49314535817.481800
'11.0.1.3158',3.0580735450375E10,30580735450.375000
'11.0.1.3158',6.272986645948E10,62729866459.480000
'11.0.1.3158',1.5340628634045E10,15340628634.045000
'11.0.1.3158',2.7209815621285E10,27209815621.285000
'11.0.1.3158',3.0528340638455E10,30528340638.455000
'11.0.1.3158',1.0667396714217E11,106673967142.170000

-- Same V11 database via V16 dbisql

SELECT @@VERSION, d, CAST ( d AS NUMERIC ( 31, 6 ) ) FROM t ORDER BY pkey;

@@VERSION,d,d
'11.0.1.3158',4.93145358174818E10,49314535817.481800
'11.0.1.3158',3.0580735450375E10,30580735450.375000
'11.0.1.3158',6.272986645948E10,62729866459.480000
'11.0.1.3158',1.5340628634045E10,15340628634.045000
'11.0.1.3158',2.7209815621285E10,27209815621.285000
'11.0.1.3158',3.0528340638455E10,30528340638.455000
'11.0.1.3158',1.0667396714217E11,106673967142.170000

-- Unloaded V11-to-V16 database via V16 dbisql.

SELECT @@VERSION, d, CAST ( d AS NUMERIC ( 31, 6 ) ) FROM t ORDER BY pkey;

@@VERSION,d,d
'16.0.0.2052',4.931453581748179E10,49314535817.481800
'16.0.0.2052',3.0580735450374996E10,30580735450.375000
'16.0.0.2052',6.2729866459479996E10,62729866459.480000
'16.0.0.2052',1.5340628634044998E10,15340628634.045000
'16.0.0.2052',2.7209815621284996E10,27209815621.285000
'16.0.0.2052',3.0528340638454998E10,30528340638.455000
'16.0.0.2052',1.0667396714216998E11,106673967142.170000

-- V16 database via V16 dbisql

DROP TABLE t;
CREATE TABLE t ( pkey INTEGER DEFAULT AUTOINCREMENT PRIMARY KEY, d DOUBLE );
INSERT t ( d ) VALUES ( 49314535817.481800 ); -- 49314535817.481700     
INSERT t ( d ) VALUES ( 30580735450.375000 ); -- 30580735450.374900     
INSERT t ( d ) VALUES ( 62729866459.480000 ); -- 62729866459.479900     
INSERT t ( d ) VALUES ( 15340628634.045000 ); -- 15340628634.044900     
INSERT t ( d ) VALUES ( 27209815621.285000 ); -- 27209815621.284900     
INSERT t ( d ) VALUES ( 30528340638.455000 ); -- 30528340638.454900     
INSERT t ( d ) VALUES ( 106673967142.170000 );  -- 106673967142.169000     
COMMIT;
SELECT @@VERSION, d, CAST ( d AS NUMERIC ( 31, 6 ) ) FROM t ORDER BY pkey;

@@VERSION,d,d
'16.0.0.2052',4.93145358174818E10,49314535817.481800
'16.0.0.2052',3.0580735450375E10,30580735450.375000
'16.0.0.2052',6.272986645948E10,62729866459.480000
'16.0.0.2052',1.5340628634045E10,15340628634.045000
'16.0.0.2052',2.7209815621285E10,27209815621.285000
'16.0.0.2052',3.0528340638455E10,30528340638.455000
'16.0.0.2052',1.0667396714217E11,106673967142.170000

-- V11 UNLOAD TABLE output file

UNLOAD TABLE t TO 'V11_unload_table.txt';

1,49314535817.481792
2,30580735450.3749967
3,62729866459.4799995
4,15340628634.0449989
5,27209815621.2849975
6,30528340638.4549975
7,106673967142.169982

-- V11 LOAD TABLE

TRUNCATE TABLE t;
LOAD TABLE t from 'V11_unload_table.txt';
SELECT @@VERSION, d, CAST ( d AS NUMERIC ( 31, 6 ) ) FROM t ORDER BY pkey;

@@VERSION,d,d
'11.0.1.3158',4.931453581748179E10,49314535817.481800
'11.0.1.3158',3.0580735450374996E10,30580735450.375000
'11.0.1.3158',6.2729866459479996E10,62729866459.480000
'11.0.1.3158',1.5340628634044998E10,15340628634.045000
'11.0.1.3158',2.7209815621284996E10,27209815621.285000
'11.0.1.3158',3.0528340638454998E10,30528340638.455000
'11.0.1.3158',1.0667396714216998E11,106673967142.170000

-- V16 UNLOAD TABLE output file

UNLOAD TABLE t TO 'V16_unload_table.txt';

1,49314535817.481792
2,30580735450.3749967
3,62729866459.4799995
4,15340628634.0449989
5,27209815621.2849975
6,30528340638.4549975
7,106673967142.169982

-- V16 LOAD TABLE

TRUNCATE TABLE t;
LOAD TABLE t from 'V16_unload_table.txt';
SELECT @@VERSION, d, CAST ( d AS NUMERIC ( 31, 6 ) ) FROM t ORDER BY pkey;

@@VERSION,d,d
'16.0.0.2052',4.931453581748179E10,49314535817.481800
'16.0.0.2052',3.0580735450374996E10,30580735450.375000
'16.0.0.2052',6.2729866459479996E10,62729866459.480000
'16.0.0.2052',1.5340628634044998E10,15340628634.045000
'16.0.0.2052',2.7209815621284996E10,27209815621.285000
'16.0.0.2052',3.0528340638454998E10,30528340638.455000
'16.0.0.2052',1.0667396714216998E11,106673967142.170000

[Former Member]

Hi All,

We spoke with the SAP representative about our licence to a Developer Edition and also the Engine for Personal Server.

The representative told us that the Personal Server is no longer available with the SQL Anywhere 16 version, and only the Network Server is available with version 16. Is it true or is there a misunderstanding.

Your responses are much appreciated.

[JasonHinsperger]

The personal server still exists, and is included with the (free) developer edition.

For deployment, as an end user, the only editions available for purchase are the workgroup, standard and advanced editions, but they all include both the personal and network server.

If you are an OEM, you deploy the network server or the personal server, depending on your specific OEM agreement.

Please feel free to include me in conversations with your SAP representative if you need further clarification.

I hope this helps,

--Jason Hinsperger

Product Manager

SQL Anywhere

[chris_keating]

It is a misunderstanding. I suspect that they are referring to the Runtime Edition. The Runtime Edition (rtengX) is no longer available but that differs from the Personal Server (dbengX) where X maps to a major version. The Personal Server is still available.

[Former Member]

Hi Kiran

Are you able to start your server (either in console window or through Sybase Central)? If you can, can you validate the databse? It may be a corrupt system table preventing your database start-up process.

Jinwoo Park

Active Global Support

[Former Member]

Hi Jinwoo,

Thanks for responding.

I could connect to the DB file through Sybase Central.

I performed a Validate, but still same issue.

[Former Member]

Hi,

error message says - cannot start server. What is your connect string while starting unload?

As you write the server is already running you shold not try to start it again.

[Former Member]

Hi,

The unload process asks only 4 connection parameters.

User name, password, DB file location and encryption key.

It does not ask for a connect string.

I provided the correct values for first 3, and the file is not encrypted.

Also, I made sure that the DB is disconnected during the unload process.

[Former Member]

If there was an assertion error message, for sure we'd know your db table is corrupt. For now, you can exclude the table throwing error in your dbunload process, either from Unload Database Wizard or running dbunload -c "uid=*;pwd=*;...." -e table1, table2 (if there are more tables with error)

Jinwoo Park

SAP Active Global Support

[Former Member]

if you get this parameters it means you try to unload a database file you are not connected to.

If you have a server running with the same db file and dafault name (same as db name) it won´t work, as you cannot start two databases with same name. Try to connect in Sybase Central to you db server and then unload running database.

Otherwise you have to privide a new db name within unload like:

dbunload -v -c "UID=user;PWD=***;DBF=C:\temp\data.db" -n DBNAME -r "C:\temp\reload.sql" -ii "C:\temp\unload"

[Former Member]

Hi All,

For a better understanding, I attched the screen shots of Unload window and subsequent windows.

I have a Sqlanywhere 12 DB file, which i dont think is corrupt, because users could update it in their machines.

I provide the correct user id, password and the path to the DB file.

This DB is not started and running, it is in my machine.

Please note , I get same error even if I select other options.

[Former Member]

then you must have a problem with your environment. Normaly dbunload starts dbeng12 (or dbeng16) for you, if no start parameter is given (in Sybase centryl you do not have this possibility). check you path settings.

You can try to type in cmd box "dbeng9 xxxxx.db".

[Former Member]

Now this is getting interesting :-).

I know that we need dbengxx.exe to for personal DB server.

But I do not find this in my installation c:\program files\sql anywhere 16.

Did I miss some thing during the installation, I am new to this...

[Former Member]

it has to in in folder %SQLANY16%\bin64 or bin32 - so the full path would be

c:\program files\sql anywhere 16\bin64\dbeng16.exe

or

c:\program files\sql anywhere 16\bin32\dbeng16.exe