Filtering the data that will be replicated during synchronization

When you create a subscription to a particular table (the PATIENTS table for your group of visiting nurses, for example), performance considerations might force you to replicate only a subset of the data in the table. You specify this subset by choosing individual rows or columns to be replicated for each table in the subscription.

For example, you might decide that the only columns relevant for visiting nurses using the PATIENTS table are the PATIENT_NAME, ADDRESS, PHONE, and DOCTOR fields. You might then decide to further filter the data by defining a WHERE clause that filters data for nurses working for a particular doctor.

You can combine data filters for the subscription with filters set for the group or for individual users to pare down the data even further.

This task is part of the larger task of creating a JDBC subscription. When you have completed these steps, return to Creating a JDBC subscription.

Procedure

To filter data that will be replicated during synchronization:

  1. From the Define Replication Subscription window, click Advanced. The Advanced Subscription Definition notebook opens.
    ΑΦ:
    The source table primary key columns must be subscribed. If the source table has columns which are not part of the primary key, at least one of those columns must be subscribed.
  2. Optional: Select the columns to use in the subscription.
    1. Go to the Target Columns page of the Advanced Subscription Definition notebook.
    2. Select the columns to create on the mobile device using the Subscribe checkbox. Columns not selected will not be created on the mobile device.
    3. Select the columns to replicate to the mirror database using the Replicate checkbox. You may need to replicate a column to the mirror database, but not want to create it on the handheld. For example, if a NOT NULLABLE column without a DEFAULT VALUE exists on the server and you are not creating it on the mobile device, you must replicate it to the mirror database and supply a default value using the Default value data filter. The Sync Server will replicate any rows created by the mobile device to the source using the DEFAULT VALUE specified in the Default value data filter to complete the contents of the NOT NULLABLE source column that does not have a default value on the server.
    4. Click Change to modify a target column name, Default value data filter, or constraint.
    5. Click Close to close the Create Index window.
  3. Optional: Define indexes for the target table.
    1. Go to the Indexes page of the Advanced Subscription Definition notebook.
    2. Click Add to open the Create Index window. The Create Index window opens.
    3. Type the name index in the Name field.
    4. Select the columns you want to include in the index using the >, >>, <, and << push buttons.
    5. Order the index using the Move Up and Move Down push buttons.
    6. Select Ascending or Descending index.
    7. Click Add to add the index.
    8. Click Close to close the Create Index window.
  4. Optional: Define a WHERE clause that selects individual rows to be included in the subscription. You might decide that you want only certain rows to be part of the subscription even if all columns appear in the replica. To display sample WHERE clauses, click Examples.
    1. Go to the Rows page of the Advanced Subscription Definition window.
    2. In the All rows needed field, type a WHERE clause that defines the selection of rows that you want to appear in the subscription. You can only select rows from the table that you selected in Step 1.

      For example, suppose you want your visiting nurses to see only the rows of data in the PATIENTS table where the value of the JOBCODE column is VNURSE. You would enter the following WHERE clause: 

      JOBCODE='VNURSE'

      It is not necessary to type WHERE at the beginning of the clause.

    3. In the Subset of rows for individual users field, you can type a clause similar to a SQL WHERE clause to further limit the rows that an individual user can see. Ensure you do not type "WHERE" at the beginning of the clause. You might use a parameter, whose value varies by the user. For detailed information about defining a filter at the group level or user level, see Filtering data at the subscription level.

      For example, suppose that you want users to see only data related to their own job codes. Instead of hard-coding a value for the JOBCODE parameter for the entire subscription, define a variable that references the data filter value defined for each individual user: 

      JOBCODE=':JOB'

      In the data filters for individual users, you could then set the :JOB parameter to each user's job code. See Filtering the data available to the user and Filtering data at the subscription level for more information on filtering data for individual users.

    4. Click OK to close the Advanced Subscription Definition window.
  5. Click OK to return to the Define Replication Subscription window.

ΑΦ:
A series of changes made to a single row in a DB2 Everyplace table on the user's device between synchronizations will be communicated to the Sync Server as a single cumulative change. As a result, a DELETE operation followed by an INSERT operation for the same primary key will be communicated to the Sync Server as an UPDATE operation if the operations occur with no intervening synchronizations. If they occur with an intervening synchronization, then they are communicated separately. Doing a series of changes to a single row can lead to an inconsistency because the subscription does not transfer all columns between the source table and the user table. An UPDATE operation will retain the current values in the unsubscribed column(s), while the INSERT will set the unsubscribed columns to default values.

Related concepts

Related tasks