IBM DB2 Everyplace Sync Server Administration Guide Version 7 Release 2 Modification 1


Vertical filtering

Vertical filtering uses the Target columns page of the Advanced Subscription Definition notebook for DB2 DataPropagator subscriptions and the Rows page of Advanced Replication Subscription notebook for JDBC subscription. To open either of these notebooks:

  1. Create or edit a JDBC subscription or DataPropagator subscription.
  2. Click Define Subscription. The Define Replication Subscription window opens.
  3. Click Advanced. For DB2 DataPropagator subscriptions, the Advanced Subscription Definition notebook opens. For JDBC subscriptions, the Advanced Replication Subscription notebook opens.

This section uses a sample table with 7 columns. The characteristics of this sample table are:

Column name    data type  nullable?    default value
NOTNULLINT1    INTEGER    NO
NOTNULLCHAR1   CHAR       NO
NOTNULLDATE1   DATE       NO
DEFAULTINT1    INT        YES          1000
DEFAULTCHAR1   CHAR       YES          'a'
DEFAULTDATE1   DATE       YES          '2001-06-29'
ID             INTEGER    NO           

The ID column is the primary key.

To vertically filter data

  1. On the Target Columns page, unsubscribe the columns you do not want to synchronize to the client. To unsubscribe a column, clear the Subscribe check box. For example using the sample table, clear the Subscribe check box for the first four columns: NOTNULLINT1, NOTNULLCHAR1, NOTNULLDATE1, and DEFAULTINT1.
  2. Select each unsubscribed column, and click Change to add a default value data filter. The Change Column window opens.
  3. Type a default value data filter parameter in the Default value data filter field. Repeat for all unsubscribed columns. The example table uses the parameters: :PARAMINT1., :PARAMCHAR1., :PARAMDATE1., and :PARAMINT2..
  4. Close the Subscription notebook.

The default value data-filter parameters are used by the group to provide a default value for the unsubscribed columns. Data inserted to the data source from the client will be composed of the columns of data on the client combined with default column values defined for the group to complete the rows of data inserted to the data source. The next step is to define the default value data filter parameter values.

Defining the default value data-filter parameters

  1. Open a Create or Edit Group notebook for the group you want to filter. The subscription you created with the filter variable must be assigned to the group you are editing.
  2. On the Data filter page of the Group notebook, Click Add.
  3. Type the default value data-filter parameter name in the Parameter name field.
  4. Type a default value in the Default value field. For example, for the default value data-filter parameter, :PARAMINT1., type:
    100
    

    A special value can also be used. The value $USERNAME allows a Sync Server user ID to be inserted as the value of the parameter. This allows you to have rows created by specific users inserted into the data source with their Sync Server user ID in a specific field. For example, for :PARAMCHAR1. type:

    $USERNAME
    

    Now all rows inserted into the sample table will have the Sync Server user ID inserted into the NOTNULLCHAR1 column. For the example table, add two more parameters:

    :PARAMDATE1.    '2001-06-29'
    :PARAMINT2.     15
    
  5. Complete and close the Group notebook.
  6. Open a Create or Edit User notebook for the user you want to filter. The user must be assigned to the group with the filter parameters.
  7. On the Data filter page of the User notebook, you can override specific default values for this user. If a $USERNAME filter is in use, the user ID for this user is automatically inserted into the User override column. For example, to override the value of :PARAMINT2.:
    1. Click Change.
    2. Type a value in the User override field. For example, to override the value of :PARAMINT2. for this user and set it to 20, type 20.
    3. Click OK.
  8. Complete and close the User notebook.

Every user assigned to the filtered group will receive only 3 of the 7 columns of the sample table. When a user inserts a new row in the table, it is synchronized to the data source using default values for the group in each of the unsubscribed columns. If the $USERNAME keyword is used in the data filter, the user ID of the user who inserted the row will be inserted for the value of that filtered column. Also, individual users may have specific default values that differ from the group default value configured on a user basis.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]