Filtering data vertically

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.

Procedure

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.

Related tasks

Related reference

Defining the default value data-filter parameters

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.

Procedure

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 :PARAMINTZ:
    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 will receive only the 3 subscribed 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.

Related tasks

Related reference

Creating an AgentAdapter subscription

Procedure

  1. Open the Mobile Devices Administration Center.
  2. Select the Subscriptions folder of the Mobile Devices Administration Center.
  3. Right-click on the Subscriptions folder of the Mobile Devices Administration Center and select Create Custom Subscription.
  4. Type a name for the subscription in the Name field.
  5. Select the AgentAdapter in the Adapter field.
  6. Click the Launch Customizer button. The Source database window opens.
  7. Type a DB2 user ID with access privileges to the database into the User ID field
  8. Type the password for the user ID in the Password and Verify password fields.
  9. In the Other field, type the following line: 
    dbname=DATABASE;procname=PROCEDURE

    where DATABASE is the name of the database used by the stored procedure, and PROCEDURE is the name of the stored procedure. For example to use stored procedure SP1 from data source DS1 and stored procedure SP2 from data source DS2, the following string is used: 

    dbname=DS1;procname=SP1;dbname=DS2;procname=SP2
  10. Click OK to close the Source database window. Click OK to close the Create Custom Subscription notebook.

Related concepts

Creating a data source using the sample application

This example uses a DB2 database named MYSAMPLE. You need to manually create the MYSAMPLE database.

Procedure

To create the MYSAMPLE database, enter the following statements at the DB2 command prompt: 

CREATE table db2e.MYACCOUNT ( Name char(16), Saving int, Checking int)
INSERT into db2e.MYACCOUNT values('Michael', 5000, 5000)
INSERT into db2e.MYACCOUNT values('Frank', 5000, 5000)

After creating the database, create a stored procedure to modify the data in the database.

Related concepts

Related tasks

Related reference