Lesson 17: Changing the remote schema

This lesson assumes you have completed all preceding lessons. See Lesson 1: Creating a consolidated database.

In this lesson, you change the schema of the remote database. For the purposes of this tutorial, a schema change occurs whenever you change the remote schema name of the database. You are never forced to change the remote schema name, it is always left up to your discretion.

You should try to ensure that any remote task that you can execute against one remote database can be executed against any other remote database with the same remote schema name. You should change a database's remote schema name whenever you change the database in a way that would make a task fail or succeed. The only commands within a task that are affected by the state of the remote database are the Synchronize and Execute SQL commands.

Synchronize commands depend on the presence of synchronization profiles in the remote database, so you should always change remote schema names when you add or remove a synchronization profile.

Execute SQL commands depend on the state of many database objects that you would normally consider to be part of the schema. Some examples of changes that would affect Execute SQL commands, and hence require a remote schema name change, are adding or removing tables from the database, altering the definition of tables in the database, and adding or removing stored procedures.

In this tutorial, you alter the schema of the remote database by adding a new table to it.

 Change a remote schema

  1. Return to the Folders view in Sybase Central. Under MobiLink 12, expand Central Admin Tutorial, right-click Remote Schema Names and click New » Remote Schema Name.

    The Create Remote Schema Name Wizard appears.

  2. Type Tutorial Application v2.0 for the schema name.

  3. Choose SQL Anywhere as the database type and click Finish.

  4. Create a new remote task. In the Folders view of Sybase Central under Central Admin Tutorial, right-click Remote Tasks and click New » Remote Task. The Create Remote Task Wizard appears.

  5. On the Welcome page, type Schema Upgrade in the Name field.

  6. Check This Task Requires Or Creates A Remote Database and set the Remote Schema Name to Tutorial Application v1.0.

  7. Check This Task Upgrades The Schema Of The Managed Remote Database and set New Remote Schema Name to Tutorial Application v2.0. Click Finish.

  8. On the Commands tab, choose Execute SQL from the Command Type dropdown list. In the SQL field, type the following: 

    CREATE TABLE  product (
    prod_id            integer primary key,
    name               varchar( 100 )
);

    The schema change task is now complete.

    Before you deploy the new schema change task, you must consider any tasks already assigned to the remote device. After the Schema Upgrade task completes, the remote schema name for the database is Tutorial Application v2.0. Any tasks on the remote device that are associated with the old remote schema name, Tutorial Application v1.0 can no longer run and are discarded by the Agent. To maintain the functionality provided by these tasks, you must create new versions of the tasks and associate them with the new remote schema name.

  9. In the Folders view, under Central Admin Tutorial » Consolidated Databases » Tutorial » Agents, click AID_JOHN. Select the Tasks tab in the right pane. Only active tasks are still being executed by the Agent. These are the only tasks that you may need to create new versions of. In this case, the only active task is the Sync every hour task.

    You can determine if this task is associated with the old remote schema name by checking the Remote Schema Name column on the Tasks tab. This task shows that the Remote Schema Name of the Sync every hour task is Tutorial Application v1.0, so it is associated with the old remote schema name. To continue synchronization after the schema change, you need to create a new version of this task and assign it to the Agent.

  10. Right-click the Sync every hour task and click Go To Task.

  11. Right-click the deployed task Sync every hour and choose Copy.

  12. Right-click Remote Tasks and click Paste. When you are asked for a name for the copied task, type Sync every hour v2 and click OK.

  13. Consider whether commands in the task require any changes to continue working with the new schema. In this case, the answer is no. There is only one command and it only depends on the tutorial1_JOHN synchronization profile, which you have not modified with this schema change.

  14. Mark the task as being associated with the new remote schema name. Right-click the Sync every hour v2 task and choose Properties. On the General page of the properties window, choose Tutorial Application v2.0 for the Remote Schema Name and click OK.

  15. To deploy the new task, right-click the Sync every hour v2 task and click Deploy. Click Next.

  16. For Recipients, click Specific Agents and then select agent AID_JOHN. Click Next and then click Finish.

  17. Right-click the Schema Upgrade task and click Deploy. Click Next.

  18. From the Recipients dropdown list, click Specific Agents and assign the task to agent AID_JOHN. Click Next and then click Finish.

    You should see the Schema Upgrade task execute successfully. After that, the Sync every hour v2 task should start executing each hour and the Sync every hour task should stop executing.

  19. Proceed to Lesson 18: Querying the remote database.

Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1