Filtering data at the user level

Horizontal filtering at the subsription 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. 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.

  2. Complete and close the Create Subscription notebook.
  3. 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.
  4. On the Data filter page of the Group notebook, click Add.
  5. Type :fcity in the Parameter name field.
  6. Type a default value in the Default value field. For example: 
    Los Angeles, CA 90061
  7. Click OK.
  8. Click Add again to add the second parameter.
  9. Type :fid. in the Parameter name field.
  10. Click OK.
  11. Complete and close the Group notebook.
  12. 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.
  13. On the Data filter page of the Group notebook, select the :fid parameter and click Change.
  14. 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
  15. Click OK.

The user will only receive records with a city column value of Los Angeles, CA 90061 and an id column value of 900000401. There is one record in VNPERSON with this value. Other users assigned to the same group will only receive rows from the VNPERSON table with a city column value equal to Los Angeles, CA 90061 and the user column value set by their data filters. Other groups will receive the entire VNPERSON table or a specific subset of the VNPERSON table, depending on the filters configured.

Related tasks

Related reference