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


Horizontal filtering at the subscription level, group level, or user 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. 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.

Defining simple horizontal filters

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

  1. On the rows page of the subscription notebook, 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, type:
    city=':fcity.'
    

    where :fcity. is a parameter for the column value.

  2. Open a Create or Edit Group notebook for the group that you want to filter. The subscription that you created with the filter parameter must be assigned to the group you are editing.
  3. On the Data filter page of the Group notebook, Click Add.
  4. Type :fcity. in the Parameter name field.

    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 (.).

  5. Type a default value in the Default value field. For example:
    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.

  6. Click OK.

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

  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.

Defining complex horizontal filters at the group level or user level

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.


Table 4. Ziptab table

City Zipcode
San Jose 95141
San Jose 95123
Los Angeles 93002

Table 5. Business table

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

Syntax for filters

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:

Syntax

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--'
 

Description

Operators
You can use any of the following operators:

=
Equal to.

<>
Not equal to.

<
Less than.

>
Greater than.

<=
Less than or equal to.

!>
Less than or equal to.

>=
Greater than or equal to.

!<
Greater than or equal to.

LIKE
Matches one character string. Use a single-byte character-set (SBCS) underscore to refer to one SBCS character. Use a double-byte character-set (DBCS) underscore to refer to one DBCS character. For example, a condition WHERE PART_NUMBER LIKE '_0' (here the underscore is in SBCS) returns all 2-digit part numbers ending in 0 (20, 30, and 40, for example). Use a percent (either SBCS or DBCS) to refer to a string of zero or more SBCS or DBCS characters. For example, a condition WHERE DEPT_NUMBER LIKE '2%' returns all department numbers beginning with the number 2 (20, 27, or 234, for example).


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