iAnywhere.com - Upgrading your consolidated database

Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere 10 - Changes and Upgrading » Upgrading to SQL Anywhere 10 » Upgrading MobiLink

Upgrading your consolidated database

Before you can use the new MobiLink server with a pre-existing consolidated database, you must run upgrade scripts that install new system objects. The upgrade scripts must be run by the owner of the currently installed MobiLink system tables.

Notes

If you have authenticate_user_hashed scripts that were created prior to version 10.0.0, you must change them to accept BINARY(32) instead of BINARY(20), using the binary equivalent type of your RDBMS.

Upgrading SQL Anywhere version 10.0.0

See Upgrading version 10.0.0 databases.

Upgrading SQL Anywhere prior to version 10.0.0

Prior to version 10.0.0, MobiLink system tables were owned by DBO. To run the setup scripts for a SQL Anywhere database, you must be logged in to the consolidated database as the owner of the MobiLink system tables. It is not sufficient to run these scripts as a user with permission to change the tables. To run the upgrade scripts, you can use the SETUSER SQL statement to impersonate DBO. For example:
```
SETUSER "dbo";
```
To upgrade a consolidated database in Sybase Central, you should use the GRANT CONNECT statement to create a password for DBO and then connect as DBO. For example:
```
GRANT CONNECT TO "dbo" IDENTIFIED BY 'password';
```
In the latter case, after you have upgraded you should use GRANT CONNECT to remove the DBO password. For example:
```
GRANT CONNECT TO "dbo";
```
If you have set up a SQL Anywhere consolidated database but never synchronized with it, then you must run the setup script (not the upgrade script). This only applies to SQL Anywhere consolidated databases.

To upgrade a consolidated database (SQL Anywhere prior to 10.0.0)

If you are upgrading a SQL Anywhere consolidated database that is prior to version 10.0.0, you must first upgrade the database to version 10:
1. Shut down the database server.
2. Upgrade the database to version 10.
  
  For instructions, see Upgrading version 9 and earlier databases.
3. Start the database server, logging in as DBA.
  
  Note: You must log in as DBA to upgrade.
If you are upgrading from version 6.0.x, run the MobiLink setup script, located in the MobiLink\setup subdirectory of your SQL Anywhere installation. Do not run the setup script if you are upgrading from a later version.

For more information about setup scripts, see MobiLink Consolidated Databases.
Run the appropriate upgrade script for the version you are upgrading from.

The upgrade scripts are called upgrade_asa.sql. They are located under your SQL Anywhere installation in MobiLink\upgrade\version, where version is the SQL Anywhere version you are upgrading from.

To run the upgrade scripts, you must impersonate the DBO user. You can do this with the SETUSER SQL statement.

For example, to upgrade a SQL Anywhere version 9.0.2 consolidated database, connect to the database in Interactive SQL and run the following command:
```
SETUSER "dbo";
READ 'c:\Program Files\SQL Anywhere 10\MobiLink\upgrade\9.0.2\upgrade_asa.sql'
```
Remove the DBO password. For example:
```
GRANT CONNECT TO "dbo"
```

If you are running the MobiLink server as a user other than DBA, you must grant execute permission for that user to the new MobiLink system objects. Which system objects are new depends on which version you are upgrading from. The following code grants the necessary permissions to all MobiLink system objects. Before executing the code, you must change the user name my_user to the name of the user who is running the MobiLink server.

GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_column to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_connection_script to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_database to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_device to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_device_address to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_listening to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_property to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_qa_clients to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_qa_delivery to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_qa_global_props to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_qa_notifications to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_qa_repository to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_qa_repository_props to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_qa_repository_staging to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_qa_status_history to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_qa_status_staging to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_script to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_script_version to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_scripts_modified to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_sis_sync_state to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_subscription to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_table to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_table_script to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_user to my_user;
GRANT EXECUTE ON dbo.ml_qa_get_agent_network_property to my_user;
GRANT EXECUTE ON dbo.ml_qa_get_agent_object_property to my_user;
GRANT EXECUTE ON dbo.ml_qa_get_agent_property to my_user;
GRANT EXECUTE ON dbo.ml_qa_get_message_property to my_user;
GRANT EXECUTE ON dbo.ml_add_column to my_user;
GRANT EXECUTE ON dbo.ml_add_connection_script to my_user;
GRANT EXECUTE ON dbo.ml_add_dnet_connection_script to my_user;
GRANT EXECUTE ON dbo.ml_add_dnet_table_script to my_user;
GRANT EXECUTE ON dbo.ml_add_java_connection_script to my_user;
GRANT EXECUTE ON dbo.ml_add_java_table_script to my_user;
GRANT EXECUTE ON dbo.ml_add_lang_conn_script_chk to my_user;
GRANT EXECUTE ON dbo.ml_add_lang_connection_script to my_user;
GRANT EXECUTE ON dbo.ml_add_lang_table_script to my_user;
GRANT EXECUTE ON dbo.ml_add_lang_table_script_chk to my_user;
GRANT EXECUTE ON dbo.ml_add_property to my_user;
GRANT EXECUTE ON dbo.ml_add_table_script to my_user;
GRANT EXECUTE ON dbo.ml_add_user to my_user;
GRANT EXECUTE ON dbo.ml_delete_device to my_user;
GRANT EXECUTE ON dbo.ml_delete_device_address to my_user;
GRANT EXECUTE ON dbo.ml_delete_listening to my_user;
GRANT EXECUTE ON dbo.ml_delete_sync_state to my_user;
GRANT EXECUTE ON dbo.ml_delete_sync_state_before to my_user;
GRANT EXECUTE ON dbo.ml_delete_user to my_user;
GRANT EXECUTE ON dbo.ml_qa_add_delivery to my_user;
GRANT EXECUTE ON dbo.ml_qa_add_message to my_user;
GRANT EXECUTE ON dbo.ml_qa_handle_error to my_user;
GRANT EXECUTE ON dbo.ml_qa_stage_status_from_client to my_user;
GRANT EXECUTE ON dbo.ml_qa_staged_status_for_client to my_user;
GRANT EXECUTE ON dbo.ml_qa_upsert_global_prop to my_user;
GRANT EXECUTE ON dbo.ml_reset_sync_state to my_user;
GRANT EXECUTE ON dbo.ml_set_device to my_user;
GRANT EXECUTE ON dbo.ml_set_device_address to my_user;
GRANT EXECUTE ON dbo.ml_set_listening to my_user;
GRANT EXECUTE ON dbo.ml_set_sis_sync_state to my_user;
GRANT EXECUTE ON dbo.ml_upload_update_device_address to my_user;
GRANT EXECUTE ON dbo.ml_upload_update_listening to my_user;

Upgrading Adaptive Server Enterprise, Oracle, or Microsoft SQL Server

You only need to upgrade your Adaptive Server Enterprise, Oracle, or Microsoft SQL Server consolidated database if your version of the MobiLink server is prior to version 10.0.0.

To upgrade a consolidated database (Adaptive Server Enterprise, Oracle, or Microsoft SQL Server)

If you are upgrading from version 6.0.x, run the MobiLink setup script, located in the MobiLink\setup subdirectory of your SQL Anywhere installation. Do not run the setup script if you are upgrading from a later version.

For more information about setup scripts, see MobiLink Consolidated Databases.
For Adaptive Server Enterprise databases, you must set "select into" permission. Run the following command in Sybase Interactive SQL:
```
USE MASTER
go
sp_dboption your-database-name, "SELECT INTO", true
go
USE your-database-name
go
checkpoint
go
```
Run the appropriate upgrade script for the version you are upgrading from.

The upgrade scripts are called upgrade_XXX.sql, where XXX indicates the RDBMS of your consolidated database. They are located under your SQL Anywhere installation in MobiLink\upgrade\version, where version is the SQL Anywhere version you are upgrading from.

For example:
```
READ 'c:\Program Files\SQL Anywhere 10\MobiLink\upgrade\902\upgrade_asa.sql'
```

Upgrading DB2

You only need to upgrade your DB2 consolidated database if your version of the MobiLink server is prior to version 10.0.0.

To upgrade a DB2 consolidated database

Copy the MobiLink\setup\SyncDB2Long.class file to the SQLLIB\FUNCTION directory on the DB2 server computer. You probably need to restart the instance of DB2. For details, see your DB2 documentation.
If you are upgrading from MobiLink version 6, create the MobiLink system tables and stored procedures by running the setup SQL script MobiLink\setup\syncdb2long.sql.

For information about how to run the DB2 setup script, see IBM DB2 UDB consolidated database.
Locate the DB2 upgrade script.

The upgrade script is called upgrade_db2tolong.sql and is held in the MobiLink/upgrade/version subdirectory of your SQL Anywhere installation. The version directory refers to the version of MobiLink from which you are upgrading.
Copy upgrade_db2tolong.sql and modify the copy. Change the CONNECT statement at the start of the script so it will work with the instance you want to connect to. Apply the copied SQL script to the consolidated database.