Lesson 18: Change the remote schema

In this lesson, you change the schema of the remote database. For our purposes, a schema change occurs whenever we 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 schema. Some examples of changes that would affect Execute SQL commands, and hence require a remote schema name change, are adding or remove 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.

 To change a remote schema

  1. Return to the Folders view in Sybase Central. Under Central Admin Tutorial, right-click Remote Schema Names and choose New » Remote Schema Name. The Create Remote Schema Name Wizard appears.

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

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

  4. The next step is to create a new remote task. In the Folders view of Sybase Central under Central Admin Tutorial, right-click Remote Tasks and choose 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, select 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 will be Tutorial Application v2.0. Any tasks on the remote device that are associated with the old remote schema name, Tutorial Application v1.0 will no longer be able to run and will be discarded by the Agent. In order 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 tasks 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. In order 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 choose Go to Task.

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

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

  13. Next, you need to 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 1 command and it only depends on the normal_sync synchronization profile, which you have not modified with this schema change.

  14. You must now mark the task as being associated with the new remote schema name. Right-click the Sync every hour v2 task and select Properties. On the General page of the property sheet, select 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 choose Deploy. Click Next.

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

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

  18. From the Recipients dropdown, choose Specific Agents and assign the task to agent AID_JOHN. Click Next then 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.

Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0