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. To open either of these notebooks:
To filter data at the subscription level
On the rows page of the subscription notebook, type an SQL clause in the All rows needed box. The entire SQL clause must be typed on one line using the following format:
city='myValue'
Do not type WHERE at the beginning of the SQL clause. WHERE is automatically appended to the clause typed in the box. For example, the following clause will synchronize only columns that have a city column value equal to Los Angeles:
city='Los Angeles, CA 90061'
To filter data at the group level
city=':fcity.'
where :fcity. is a parameter for the column value.
We recommend that you uniquely identify your parameter names with additional text. For example, start the parameter name with a colon (:) and end it with a period (.).
Los Angeles, CA 90061
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 IDs in a specific field.
Each user assigned to this group will receive only rows from the VNPERSON table with a city column value equal to Los Angeles, CA 90061. Other groups will receive the entire VNPERSON table or a specific subset of the VNPERSON table, depending on the filters configured.
To filter data at the user level
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.
Los Angeles, CA 90061
900000401
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.
Some situations in database and user management call for the definition of a complex SQL WHERE clause in Subset of rows for individual users field in order to limit the rows that a group or a user can see. When defining the filter, you might refer to a table in the mirror database, use parameters for the group or the user, or use a combination of these methods. For the basic steps in creating a simple horizontal filter, see Defining simple horizontal filters.
DB2 Everyplace Sync Server provides a subset of the standard SQL WHERE implementation to use with the filter. For the syntax of the subset, see Syntax for filters.
The following example demonstrates how you may refer to other tables in the mirror database and use a parameter for horizontal filtering.
Suppose you manage two tables named Ziptab and Business, both of which are in the mirror database. Ziptab contains ZIP codes for certain cities (see Table 4), while Business keeps track of some companies and their ZIP codes (see Table 5). You want users in San Jose, California to view only the businesses in their city. You have created a group and assigned these users to the group.
City | Zipcode |
---|---|
San Jose | 95141 |
San Jose | 95123 |
Los Angeles | 93002 |
Business | Zip |
---|---|
IBM | 95141 |
My Company | 95123 |
Your Company | 93002 |
Another Company | 94888 |
You would type the following clause in the Subset of rows for individual users field of the Rows page:
(Zip) IN (SELECT Zipcode FROM Ziptab WHERE City = ':fcity.'):fcity. is a parameter for the City column value, which you set to San Jose for the group.
Then complete and close the Create Subscriptions notebook. As a
result, after synchronization, the users will only see the following rows in
Business table on their devices:
Table 6. Rows that the users will see on their devices
Business | Zip |
---|---|
IBM | 95141 |
My Company | 95123 |
You can use a clause similar to the WHERE clause in SQL to filter for individual users when you create a replication subscription in the Mobile Devices Administration Center (MDAC). Enter the clause in the Subset of rows for individual users field of the Advanced Replication Subscription notebook for DataPropagator subscriptions, or Advanced Replication Subscription notebook for JDBC subscriptions.
There are limitations when you use the syntax diagram below. These limitations include:
Filter .-logic operator AND or OR. V | >>----| dimension |---- +-------------------------------->< dimension |---+-column-name--IN--| subselect |----------------+-----------| | .-,--------------. | | V | | +-(-----column-name---+---)--IN--| subselect |--+ '-| simpleUnaryFilter |-------------------------' subselect |---(--| selectClause |--| fromClause |----+-----------------+--> '-| whereClause |-' >-----)---------------------------------------------------------| selectClause .-,--------------. V | |---SELECT-------column-name---+--------------------------------| fromClause |---FROM--| tableReference |------------------------------------| whereClause |---WHERE--| simpleFilter |-------------------------------------| tableReference |---+------------------+--table-name----------------------------| '-schema-name--.---' simpleFilter .-logic operator AND or OR-. V | |------| simpleUnaryFilter |---+--------------------------------| simpleUnaryFilter |---+-NOT--| simpleUnaryFilter |--+-----------------------------| +-(--| simpleFilter |--)------+ '-| simplePredicate |---------' simplePredicate |---| expression |--| simplePredicateEnd |----------------------| expression |---+-| function |-+--------------------------------------------| +-column-name--+ '-| constant |-' function |---+-----------------+---function-name--(----------------------> '-schema-name--.--' >-----+------------------------+--)-----------------------------| | .-,----------------. | | V | | '----| expression |---+--' simplePredicateEnd |---+--+-| operator |-+---| expression |-----------------------+-> | '-IN-----------' | +-IS--+------+---NULL--------------------------------------+ | '-NOT--' | '-+------+---BETWEEN--| expression |--AND--| expression |--' '-NOT--' >---------------------------------------------------------------| operator |---+-=-----+---------------------------------------------------| +-<>----+ +->-----+ +-<-----+ +->=----+ +-<=----+ +-!=----+ +-!<----+ +-!>----+ '-LIKE--'