dbunload failure - Upgrading ASA 9 DB to ASA 12

[Former Member]

When running a dbunload command (dbunload -v -c "DBF=mydb.db;UID=dba;PWD=mypass" -an /path/to/new/db/file) to upgrade an ASA9 database to ASA12 on our Linux Database box I recieve the following error:

SQL Anywhere Unload Utility Version 12.0.0.2483
Connecting and initializing
***** SQL error: Communication error
Aborted

Further investigation of /var/log/messages gives this information(some info changed in copy for privacy):

Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 SQL Anywhere Personal Server Version 12.0.0.2483
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 Standard Edition
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 Copyright (c) 2001-2010, iAnywhere Solutions, Inc.
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 Portions copyright (c) 1988-2010, Sybase, Inc. All rights reserved.
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 Use of this software is governed by the Sybase License Agreement.
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 Refer to http://www.sybase.com/softwarelicenses.
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 Connection limit (Personal Server😞 10
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 Processors detected: 2 (containing 4 logical processors)
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 Processor limit (Personal Server😞 1
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 Maximum number of physical processors the server will use: 1
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 This server is licensed to:
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞     My Name
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞     My Company
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 Running Linux 2.6.27.19-5-xen #1 SMP 2009-02-28 04:40:21 +0100 on X86_64
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 Server built for X86_64 processor architecture
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 4683952K of memory used for caching
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 Minimum cache size: 4683952K, maximum cache size: 11177616K
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 Using a maximum page size of 8192 bytes
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 Multiprogramming level: 20
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 Database server started at Thu Jun 30 2011 11:36
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 Trying to start SharedMemory link ...
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞     SharedMemory link started successfully
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 Now accepting requests
Jun 30 11:36:03 myserver SQLAnywhere(mydb😞 Database server shutdown requested by DBSTOP
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 SQL Anywhere Personal Server Version 12.0.0.2483
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Standard Edition
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Copyright (c) 2001-2010, iAnywhere Solutions, Inc.
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Portions copyright (c) 1988-2010, Sybase, Inc. All rights reserved.
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Use of this software is governed by the Sybase License Agreement.
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Refer to http://www.sybase.com/softwarelicenses.
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Connection limit (Personal Server😞 10
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Processors detected: 2 (containing 4 logical processors)
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Processor limit (Personal Server😞 1
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Maximum number of physical processors the server will use: 1
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 This server is licensed to:
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞     My Name
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞     My Company
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Running Linux 2.6.27.19-5-xen #1 SMP 2009-02-28 04:40:21 +0100 on X86_64
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Server built for X86_64 processor architecture
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 4683952K of memory used for caching
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Minimum cache size: 4683952K, maximum cache size: 11112080K
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Using a maximum page size of 8192 bytes
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Multiprogramming level: 20
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Database server started at Thu Jun 30 2011 11:36
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Trying to start SharedMemory link ...
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞     SharedMemory link started successfully
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Now accepting requests
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Starting database "utility_db" (utility_db) at Thu Jun 30 2011 11:36
Jun 30 11:36:04 myserver SQLAnywhere(dbunload_engine😞 Database "utility_db" (utility_db) started at Thu Jun 30 2011 11:36
Jun 30 11:36:04 myserver SQLAnywhere(mydb😞 Database server stopped at Thu Jun 30 2011 11:36
Jun 30 11:36:09 myserver SQLAnywhere(dbunload_engine😞 Disconnecting shared memory client, process id not found
Jun 30 11:36:09 myserver SQLAnywhere(dbunload_engine😞 Disconnected SharedMemory client's AppInfo: IP=xxx.xxx.xxx.xxx;HOST=myserver;OSUSER=root;OS='Linux 2.6.27.19-5-xen #1 SMP 2009-02-28 04:40:21 +0100 x';EXE=/opt/sqlanywhere12/bin64/dbunload;PID=0x12c6;THREAD=0x7f52d738d730;VERSION=12.0.0.2483;API=DBLIB;TIMEZONEADJUSTMENT=-300
Jun 30 11:36:09 myserver SQLAnywhere(dbunload_engine😞 Database "utility_db" (utility_db) stopped at Thu Jun 30 2011 11:36
Jun 30 11:36:09 myserver SQLAnywhere(dbunload_engine😞 Database server shutdown automatically after last database stopped
Jun 30 11:36:10 myserver SQLAnywhere(dbunload_engine😞 Database server stopped at Thu Jun 30 2011 11:36

Any information or help would be greatly appreciated.

[Former Member]

Hi, I think this is pretty straight forward answer. You cannot unload db from v9 with dbunload utility of v12. Take a look here in the docs> http://dcx.sybase.com/index.html#1201/en/dbadmin/dbunload.html

..and these sections> 1, "Upgrading to version 12 For information about rebuilding an existing database into a version 12 database, see Upgrading SQL Anywhere. When using dbunload with a version 10.0.0 or later database, the version of dbunload used must match the version of the database server used to access the database. If an older version of dbunload is used with a newer database server, or vice versa, an error is reported."

2, Note Version 9 and earlier databases that require recovery cannot be reloaded with version 10 or later of the

So try to unload it using v9 dbunload utility and load into v12. Hope is helped.

[VolkerBarth]

IMHO, that's wrong. You definetely should unload a v9 database with v12 when you're about to migrate to v12. The v12 dbunload will start a particular database engine (the "database unload support engine dbunlspt.exe") that can handle the old database format.

However, I would use a current 12.0.0 (or 12.0.1) EBF - you are using the 12.0.0 GA version, and newer EBFs might contain fixes for unloads.

Another option would be to test with a reload file (instead of the -an option).

[reimer_pods]

I agree with Volker. To upgrade databases from older versions to SA12 I use the wizard in Sybase Central 6.1, but DBUNLOAD would do it as well. Today I successfully rebuilt a datebase originally created with ASA7.

[VolkerBarth]

To clarify both of Marchello's quotes:

"...the version of dbunload used must match the version of the database server..."

That does relate to the version of the unload program and the database server - they both must match. I.e. you cannot use a 12.0.1.3152 unload with a 12.0.1.3324 database engine. However, it does not refer to the version of the database itself.

"Note Version 9 and earlier databases that require recovery cannot be reloaded with version 10 or later."

I don't find an according quote from the EBF readmes, but I think I remember that this restriction has been resolved. - To omit this problem, you have to close down the v9 database normally and use copies of these files (instead of copies of backups) to do the upgrade. A normally shutdown database does not require recovery.

[Former Member]

Hi guys, thanks for the clarification, its more clear to me now. hopefully mkmaster solved it out already 😉

[VolkerBarth]

As already stated in a comment, I would use a current 12.0.0 (or preferable 12.0.1) EBF - you are using the 12.0.0 GA version, and newer EBFs might contain fixes for unloads.

I would further add the -v option to get verbose output and the -o <logfile> option to store the output. That could give more error information.

Additionally, the docs give more help for failed -an/-ar unloads:

If a failure occurs during an internal rebuild of a database using -ar or -an, after the table data has been reloaded and any indexes on the table have been rebuilt, dbunload creates a file named unprocessed.sql in the current directory. This file contains all the statements that were not executed as a result of the failure, and also includes the statement that caused the failure as a comment.

Another option would be to test with an external reload (i.e. with reload.sql script file) instead of the -an option. This gives more control over the unload/reload process.