Consolidated database upgrades

Before you can use the new MobiLink server with an 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. You can also use the following methods to update the MobiLink system setup:

In the MobiLink plug-in for Sybase Central, click MobiLink 12 » Project » Consolidated Databases and right-click the database name and click Check MobiLink System Setup. If your database requires setup, you are prompted to continue.
When you use the Deploy Synchronization Model Wizard, system setup is checked when you connect to your server database. If your database requires setup, you are prompted to continue. See Synchronization models.

The MobiLink upgrade scripts for 6.0.x have been removed. If you require this upgrade, contact Technical Support ( [external link] http://www.sybase.com/support).

Notes

Use the ml_add_missing_dnld_scripts stored procedure to fix missing download_cursor and/or download_delete_cursor scripts. Invoking this procedure with a script version name defines the missing download_cursor and/or download_delete_cursor scripts as ignored scripts for every synchronization table used by the given script version.
If you have authenticate_user_hashed scripts that were created earlier than version 10.0.0, you must change them to accept VARBINARY(32) instead of VARBINARY(20), using the binary equivalent type of your RDBMS.

Upgrading consolidated databases

Upgrade a consolidated database (SQL Anywhere 10.0.0 and later)

Upgrade the SQL Anywhere software.

See Upgrading version 10 and later databases.

Upgrade the MobiLink system set up by running the appropriate upgrade script for the version you are upgrading from.

The upgrade script is called upgrade_sa.sql. It is located under your SQL Anywhere installation in MobiLink\upgrade\version, where version is the SQL Anywhere version you are upgrading from.

For example, connect to the database in Interactive SQL and execute the following statement:

READ "C:\Program Files\SQL Anywhere 12\MobiLink\upgrade\10.0.x\upgrade_sa.sql"

Upgrade a consolidated database (Adaptive Server Enterprise, Oracle, MySQL, or Microsoft SQL Server)

You only need to upgrade the MobiLink system objects in your Adaptive Server Enterprise, Oracle, MySQL, or Microsoft SQL Server consolidated database if your version of the MobiLink server is earlier than version 12.0.1.

For Adaptive Server Enterprise databases, you must set "select into" permission. Execute the following statement 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 MobiLink version you are upgrading from.

For example, to upgrade a Microsoft SQL Server database on which the MobiLink system tables from version 9.0.2 have been applied, run the following command:

osql -S server_name -U user_name -P password -I
"C:\Program Files\SQL Anywhere 12\MobiLink\upgrade\9.0.2\upgrade_mss.sql"

Upgrade a IBM DB2 LUW consolidated database

You only need to upgrade your IBM DB2 LUW consolidated database if your version of the MobiLink server is earlier than version 12.0.1.

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

Locate the IBM DB2 LUW upgrade script.

The upgrade script is called upgrade_db2.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_db2.sql and modify the copy. Change the CONNECT statement at the start of the script so it works with the instance you want to connect to. Apply the copied SQL script to the consolidated database.

Upgrade a consolidated database (SQL Anywhere earlier than 10.0.0)

Before SQL Anywhere 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 enough 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 ALTER USER to remove the dbo password. For example:
ALTER USER TO dbo IDENTIFIED BY "";
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 step only applies to SQL Anywhere consolidated databases. See SQL Anywhere consolidated database.

If you are upgrading a SQL Anywhere consolidated database that is earlier than version 10.0.0, you must first upgrade the database to version 12:
1. Shut down the database server.
2. Upgrade the database to version 12.
  
  For instructions, see:
3. Start the database server, logging in as DBA.
  
  You must log in as DBA to upgrade.

Run the appropriate upgrade script for the version you are upgrading from.

The upgrade script is called upgrade_asa.sql. It is 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 execute the following statement:

SETUSER "dbo";
READ 'C:\Program Files\SQL Anywhere 12\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 on 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_passthrough to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_passthrough_repair to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_passthrough_script to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_passthrough_status to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_primary_server to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_property 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_delivery_archive 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_archive 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_props_archive 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_history_archive 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_ra_agent to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_ra_agent_property to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_ra_agent_staging to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_ra_deployed_task to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_ra_event to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_ra_event_staging to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_ra_managed_remote to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_ra_notify to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_ra_remote_db_class to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_ra_task to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_ra_task_command to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_ra_task_command_property to my_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.ml_ra_task_property 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_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_missing_dnld_scripts;
GRANT EXECUTE ON dbo.ml_add_passthrough to my_user;
GRANT EXECUTE ON dbo.ml_add_passthrough_repair to my_user;
GRANT EXECUTE ON dbo.ml_add_passthrough_script 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_passthrough to my_user;
GRANT EXECUTE ON dbo.ml_delete_passthrough_repair to my_user;
GRANT EXECUTE ON dbo.ml_delete_passthrough_script to my_user;
GRANT EXECUTE ON dbo.ml_delete_remote_id 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_delete_user_state to my_user;
GRANT EXECUTE ON dbo.ml_lock_rid 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_ra_add_agent_id to my_user;
GRANT EXECUTE ON dbo.ml_ra_assign_task to my_user;
GRANT EXECUTE ON dbo.ml_ra_cancel_notification to my_user;
GRANT EXECUTE ON dbo.ml_ra_cancel_task_instance to my_user;
GRANT EXECUTE ON dbo.ml_ra_clone_agent_properties to my_user;
GRANT EXECUTE ON dbo.ml_ra_delete_agent_id to my_user;
GRANT EXECUTE ON dbo.ml_ra_delete_events_before to my_user;
GRANT EXECUTE ON dbo.ml_ra_delete_task to my_user;
GRANT EXECUTE ON dbo.ml_ra_get_agent_events to my_user;
GRANT EXECUTE ON dbo.ml_ra_get_agent_ids to my_user;
GRANT EXECUTE ON dbo.ml_ra_get_agent_properties to my_user;
GRANT EXECUTE ON dbo.ml_ra_get_latest_event_id to my_user;
GRANT EXECUTE ON dbo.ml_ra_get_orphan_taskdbs to my_user;
GRANT EXECUTE ON dbo.ml_ra_get_remote_ids to my_user;
GRANT EXECUTE ON dbo.ml_ra_get_task_results to my_user;
GRANT EXECUTE ON dbo.ml_ra_get_task_status to my_user;
GRANT EXECUTE ON dbo.ml_ra_int_cancel_notification to my_user;
GRANT EXECUTE ON dbo.ml_ra_int_move_events to my_user;
GRANT EXECUTE ON dbo.ml_ra_manage_remote_db;
GRANT EXECUTE ON dbo.ml_ra_notify_agent_sync to my_user;
GRANT EXECUTE ON dbo.ml_ra_notify_task to my_user;
GRANT EXECUTE ON dbo.ml_ra_reassign_taskdb to my_user;
GRANT EXECUTE ON dbo.ml_ra_set_agent_property to my_user;
GRANT EXECUTE ON dbo.ml_ra_ss_agent_auth_file_xfer to my_user;
GRANT EXECUTE ON dbo.ml_ra_ss_download_ack to my_user;
GRANT EXECUTE ON dbo.ml_ra_ss_download_prop to my_user;
GRANT EXECUTE ON dbo.ml_ra_ss_download_remote_dbs to my_user;
GRANT EXECUTE ON dbo.ml_ra_ss_download_task to my_user;
GRANT EXECUTE ON dbo.ml_ra_ss_download_task2;
GRANT EXECUTE ON dbo.ml_ra_ss_download_task_cmd to my_user;
GRANT EXECUTE ON dbo.ml_ra_ss_end_upload to my_user;
GRANT EXECUTE ON dbo.ml_ra_ss_upload_prop to my_user;
GRANT EXECUTE ON dbo.ml_ra_unmanage_remote_id 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;

Discuss this page in DocCommentXchange.