Writing download_delete

Writing download_delete_cursor scripts

You write download_delete_cursor scripts to delete rows from your remote database. You must write one of these scripts for each table in the remote database from which you want to delete rows during synchronization.

You cannot just delete rows from the consolidated database and have them disappear from remote databases. You need to keep track of the primary keys for deleted rows, so that you can select those primary keys with your download_delete_cursor. There are two common techniques for achieving this:

Logical deletes Do not physically delete the row in the consolidated database. Instead, have a status column that keeps track of whether rows are valid. This simplifies the download_delete_cursor. However, the download_cursor and other applications may need to be modified to recognize and use the status column. If you have a last modified column that holds the time of deletion, and if you also keep track of the last download time for each remote, then you can physically delete the row once all remote download times are newer than the time of deletion.
Shadow table For each table for which you want to track deletes, create a shadow table with two columns, one holding the primary key for the table, and the other holding a timestamp. Create a trigger that inserts the primary key and timestamp into the shadow table whenever a row is deleted. Your download_delete_cursor can then select from this shadow table. As with logical deletes, you can delete the row from the shadow table once all remote databases have downloaded the corresponding data.

The MobiLink server deletes rows in the remote database by selecting primary key values from the consolidated database and passing those values to the remote database. If the values match those of a primary key in the remote database, then that row is deleted.

Each download_delete_cursor script must contain a SELECT statement or a call to a stored procedure that returns a result set. The MobiLink server uses this statement to define a cursor in the consolidated database.
This statement must select all the columns that correspond to the primary key columns in the table in the remote database. The columns in the consolidated database can have different names than the corresponding columns in the remote database, but they must be of compatible types.
The values must be selected in the same order as the corresponding columns are defined in the remote database. That order is the order of the columns in the CREATE TABLE statement used to make the table, not the order they appear in the statement that defines the primary key.
If you delete a parent record, the child records are automatically deleted as well.

For more information about deleting child records, see Referential integrity and synchronization.

While each download_delete_cursor script must select all the column values present in the primary key of the corresponding remote table, it may also select all the other columns. This feature is present only for compatibility with older clients. Selecting the additional columns is less efficient, as the database server must retrieve more data. Unless the client is of an old design, the MobiLink server discards the extra values immediately. The extra values are downloaded only to older clients.

Deleting all the rows in a table

When MobiLink detects a download_delete_cursor with a row that contains all nulls, it deletes all the data in the remote table. The number of nulls in the download_delete_cursor can be the number of primary key columns or the total number of columns in the table.

For example, the following download_delete_cursor SQL script deletes every row in a table in which there are two primary key columns. This example works for SQL Anywhere, Adaptive Server Enterprise, and Microsoft SQL Server databases.

SELECT NULL, NULL

In IBM DB2 LUW and Oracle consolidated databases, you must specify a dummy table to select null. For IBM DB2 LUW 7.1, you can use the following syntax:

SELECT NULL, NULL FROM SYSIBM.SYSDUMMY1

For Oracle consolidated databases, you can use the following syntax:

SELECT NULL, NULL FROM DUAL

Examples

The following example is a download_delete_cursor script for a remote table that holds employee information. The MobiLink server uses this SQL statement to define the delete cursor. This script deletes information about all employees who are both in the consolidated and remote databases at the time the script is executed.

SELECT emp_id
FROM employee

The download_delete_cursor accepts the parameters last_download and ml_username. The following script shows how you can use each parameter to narrow your selection.

SELECT order_id
FROM ULOrder
WHERE last_modified >= {ml s.last_table_download}
 AND status = 'Approved'
 AND user_name = {ml s.username}

Note

For some consolidated databases, you may need to cast to the appropriate data type. See CAST function [Data type conversion].

These examples could be inefficient in an organization with many employees. You can make the delete process more efficient by selecting only rows that could be present in the remote database. For example, you could limit the number of rows by selecting only those people who have recently been assigned a new manager. Another strategy is to allow the client application to delete the rows itself. This method is possible only when a rule identifies the unneeded rows. For example, rows might contain a timestamp that indicates an expiry date. Before you delete the rows, use the STOP SYNCHRONIZATION DELETE statement to stop these deletes being uploaded during the next synchronization. Be sure to execute START SYNCHRONIZATION DELETE immediately afterwards if you want other deletes to be synchronized in the normal fashion.

Notes

The download_delete_cursor script must contain primary key columns in the same order as they are defined in the remote database.