Modifying the download subset

Each MobiLink remote database can synchronize a subset of the data in the consolidated database. You can customize the download subset for each table.

The download subset options are:

User Choose this option to partition data by MobiLink user name, which downloads different data to different registered MobiLink users.
To use this option, the MobiLink user names must be in your consolidated database. You choose your MobiLink user names when you deploy, so you can choose names that match existing values on your consolidated database. (The column you use for MobiLink user names must be of a type that can hold the values you are using for the user name.) If the MobiLink user names are in a different table from the one you are subsetting, you must join to that table.
Remote ID Choose this option to partition data by remote ID, which downloads different data to different remote databases.
To use this option, the remote IDs must be in your consolidated database. Remote IDs are created as GUIDs by default, but you can set the remote IDs to match existing values on your consolidated database. (The column you use for remote IDs must be of a type that can hold the values you are using for the remote IDs.) If the remote IDs are in a different table from the one you are subsetting, you must join to that table.

Note

It is usually better to partition by user or by authentication parameter than by remote ID, because the remote ID can change if the remote computer is reset or replaced.
Custom Choose this option to use a SQL expression that determines which rows are downloaded. Each synchronization only downloads rows where your SQL expression is true. This SQL expression is added to the WHERE clause of the generated download_cursor script. You can use MobiLink named parameters in the expression. You can also refer to other tables. If you refer to other tables, you must list the other tables in the field above the expression, and include the join condition in your expression.

Change the download subset

In the Folders view of Sybase Central, expand MobiLink 12, your MobiLink project name, Synchronization Models, and then select your synchronization model name.
Open the Mappings tab in the right pane.
In the Table Mappings pane, select a remote table.
In the Download Subset dropdown list, choose one of the following download subsets: None, User, Remote, or Custom.
If you chose User, Remote, or Custom, open the Download Subset tab in the lower pane.
If you chose User or Remote, the Download Subset tab allows you to identify the column that contains the MobiLink user names or remote IDs, either in the synchronized table or in a joined table. With a joined table, you must specify the columns for the join condition.
If you chose Custom, the Download Subset tab has two text boxes where you add information to construct a download_cursor script. You do not have to write a complete download_cursor. You only need to add extra information to identify the join and other restrictions for the download subset.
- In the first text box (Tables To Add To The Download Cursor's FROM Clause), enter the table name(s) if your download_cursor requires a join to other tables. If the join requires multiple tables, separate them with commas.
- In the second box (SQL Expression To Use In The Download Cursor's WHERE Clause), enter a SQL expression to be added to the generated WHERE clause that specifies the download subset condition and join condition. You can use MobiLink named parameters, including authentication parameters, in the expression. By default, the same expression and joined tables are used for the download delete subset. If you are using a shadow table to track deletes and want to use the same expression, avoid using the base table name in the expression. If that is not possible, use a custom download delete subset.

For example, the ULOrder table in CustDB can be shared between users. By default, orders are assigned to the employee who created them, but there are times when another employee needs to see orders created by someone else. For example, a manager may need to see all the orders created by employees in their department. The CustDB database has a provision for this via the ULEmpCust table. It allows you to assign customers to employees. They download all orders for that employee customer relationship.

To see how this is done, first view the download_cursor script for ULOrder without download subsetting. Select the ULEmpCust table in the Mapping tab. Choose Timestamp-based for the Download Type column and None for the Download Subset column. Right-click the table and click Go To Events. The download_cursor for the table looks like this:

SELECT "DBA"."ULOrder"."order_id",
    "DBA"."ULOrder"."cust_id",
    "DBA"."ULOrder"."prod_id",
    "DBA"."ULOrder"."emp_id",
    "DBA"."ULOrder"."disc",
    "DBA"."ULOrder"."quant",
    "DBA"."ULOrder"."notes",
    "DBA"."ULOrder"."status"
FROM "DBA"."ULOrder"
WHERE "DBA"."ULOrder"."last_modified" >= {ml s.last_table_download}

Now go back to the Mappings tab. Change the Download Subset column for ULOrder to User. Open the Download Subset tab in the lower pane. Select Use A Column In A Joined Relationship Table. For the table to join, select ULEmpCust. For the column to match, select emp_id. The join condition should be emp_id = emp_id.

Right-click the table in the top pane and click Go To Events. The download_cursor for the table now looks like this (the new lines are shown in bold):

SELECT "DBA"."ULOrder"."order_id",
    "DBA"."ULOrder"."cust_id",
    "DBA"."ULOrder"."prod_id",
    "DBA"."ULOrder"."emp_id",
    "DBA"."ULOrder"."disc",
    "DBA"."ULOrder"."quant",
    "DBA"."ULOrder"."notes",
    "DBA"."ULOrder"."status"
FROM "DBA"."ULOrder", "DBA"."ULEmpCust"
WHERE "DBA"."ULOrder"."last_modified" >= {ml s.last_table_download} 
AND "DBA"."ULOrder"."emp_id" = "DBA"."ULEmpCust"."emp_id" 
AND "DBA"."ULEmpCust"."emp_id" = {ml s.username}

Example (Custom)

For example, assume you want to subset the download of a table called Customer by MobiLink user and you also want to only download rows where active=1. The MobiLink user names do not exist in the table you are subsetting, so you need to create a join to a table called SalesRep, which contains the MobiLink user names.

In the Mappings tab, click Timestamp-based for the Download Type column and Custom for the Download Subset column of the Customer table mapping. Open the Download Subset tab in the lower pane. In the first box (Tables To Add To The Download Cursor's FROM Clause), type:

SalesRep

In the second box (SQL Expression To Use In The Download Cursor's WHERE Clause), type:

SalesRep.ml_username = {ml s.username} 
   AND Customer.active = 1
   AND Customer.cust_id = SalesRep.cust_id

Note that both tables have a cust_id column, so references to those columns have to be prefixed with the table name in the expression. If you use a shadow table for tracking deletes to be downloaded, you need to use None or Custom in the Download Delete Subset column for the Customer table mapping, since the shadow table is called Customer_del instead of Customer.

Right-click the table in the top pane and click Go To Events. The download_cursor for the table now looks like this:

SELECT "DBA"."Customer"."cust_id",
 "DBA"."Customer"."cust_name"
FROM "DBA"."Customer", SalesRep
WHERE "DBA"."Customer"."last_modified" >= {ml s.last_table_download}
 AND SalesRep.ml_username = {ml s.username}
 AND Customer.active = 1
 AND Customer.cust_id = SalesRep.cust_id

The final line of the WHERE clause creates a key join of Customer to SalesRep.

Discuss this page in DocCommentXchange.

Modifying the download subset

Note

Change the download subset

See also

Example (User)

Example (Custom)