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 on 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 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 on 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.
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 the same as is used in download_cursor scripts. It is partly generated for you.
In Model mode, open the Mappings tab.
In the Table Mappings pane, select a remote table.
In the Dnld Sub. 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 in the consolidated table that contains the MobiLink user names or remote IDs, or to enter a join of tables to obtain the MobiLink user names or remote IDs.
If you choose 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 on 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 WHERE condition that specifies the join and the download 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 Dnld. Type column and None for the Dnld. sub column. Right-click the table and choose 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 Dnld. sub 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 choose Go To Events. The download_cursor for the table now 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", "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} |
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 user names.
In the Mappings tab, Choose Timestamp-based for the Dnld. Type column and Custom for the Dnld. Sub column of the Customer table. 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 |
Right-click the table in the top pane and choose 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. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |