Filtering data at the group level

Horizontal filtering at the subscription level, group level, or user level uses an SQL clause to select data from the source data source. The contents of the SQL clause are controlled by the Rows page of the Advanced Subscription Definition notebook for DB2 DataPropagator subscriptions and the Rows page of Advanced Replication Subscription notebook for JDBC subscriptions.

Procedure

  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.
  4. On the Rows page, type an SQL clause in the Subset of rows for individual users box. Do not type WHERE at the beginning of the SQL clause. WHERE is automatically appended to the clause typed in the box. Instead of using a value in the SQL clause, insert a parameter. For example, to filter the VNPERSON table based on the value of the parameter :fcity for the city column and the value of the parameter :fid for the id column, type: 
    city=':fcity' and id=':fid'
    where :fcity is a parameter for the city column value and :fid is a parameter for the id column value.
  5. Complete and close the Create Subscription notebook.
  6. Open a Create or Edit Group notebook for the group of users you want to filter. The subscription you created with the filter parameters must be assigned to the group you are editing.
  7. On the Data filter page of the Group notebook, click Add.
  8. Type :fcity in the Parameter name field.
  9. Type a default value in the Default value field. For example: 
    Los Angeles, CA 90061
  10. Click OK.
  11. Click Add again to add the second parameter.
  12. Type :fid. in the Parameter name field.
  13. Click OK.
  14. Complete and close the Group notebook.
  15. 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.
  16. On the Data filter page of the Group notebook, select the :fid parameter and click Change.
  17. Type a value in the User override field. For example, to override the value of :fid for this user and set it to 900000401, type: 
    900000401
  18. Click OK.
Note:
Horizontal filtering only occurs from server to client. If a client inserts a row that is not be included in the filter, the rows will be inserted into the source.

Related tasks

Related reference