This topic provides example for the following:
This example shows the XML Script to define source (master) database
and mirror database information and create a jdbc subscription using these
two databases.
The referenced XML DTD file is normally picked up from the classpath:
file:/com/ibm/mobileservices/xmlscripting/mdas3.dtd.
<AddJdbcMaster> tag defines source database connection information.
The jdbc URL, driver, user id and password are required.
<AddJdbcMirror> tag defines mirror database connection information.
The jdbc URL, driver, user id and password and the sync window
(replication frequency) are required.
Jdbc master and mirror information must be defined before a JDBC
subscription can reference such databases.
<AddJdbcSubscription> tag defines a JDBC subscription. When
Replicate="TRUE" is specified, replication will be performed to
populate the new mirror tables with data from source to
get the mirror ready for synchronization with mobile devices.
If Replicate="FALSE" is specified, synchronization to this mirror will
remain blocked until the first replication of this mirror completes.
<AddJdbcSubscription> tag must contain 1 or more <AddTable> tags.
<AddTable> tags should list all the columns of the source table to specify
replication and synchronization options for each column using <AddColumn> tag.
<SourceSchema> and <SourceTable> identify a table in the source database that
we are subscribing to.
<TargetSchema> and <TargetTable> can be anything that will be unique in the mirror
database. These names will also be used on the mobile devices.
<AddTable> tags may specify indexes for the table columns using <AddIndex> tag.
Index names need to be unique for the whole system for the sake of simplicity.
Below example creates a JDBC subscription named 'JDBCSUB_2' which uses VNURSE
database as the source and M_VN2 as the mirror.
This subscription has only 1 table that has multiple columns and 1 ascending
index defined
(the '+' before the index column means ascending, '-' means descending).
Multiple columns can be specified like "+COL1;-COL2;+COL3" for an index.
<?xml version="1.0"?>
<!DOCTYPE Root SYSTEM "file:/com/ibm/mobileservices/xmlscripting/mdas3.dtd">
<Root>
<AddJdbcMaster>
<Database>jdbc:db2:VNURSE</Database>
<Driver>COM.ibm.db2.jdbc.app.DB2Driver</Driver>
<UserId>db2admin</UserId>
<Password>db2admin</Password>
</AddJdbcMaster>
<AddJdbcMirror>
<Database>jdbc:db2:M_VN2</Database>
<Driver>COM.ibm.db2.jdbc.app.DB2Driver</Driver>
<UserId>db2admin</UserId>
<Password>db2admin</Password>
<SyncWindow>60</SyncWindow>
</AddJdbcMirror>
<AddJdbcSubscription Replicate="TRUE">
<Name>JDBCSUB_2</Name>
<SubAdapter>DSYJDBC</SubAdapter>
<EncryptionLevel>0</EncryptionLevel>
<Block>N</Block>
<MasterDb>jdbc:db2:VNURSE</MasterDb>
<MasterDriver>COM.ibm.db2.jdbc.app.DB2Driver</MasterDriver>
<MirrorDb>jdbc:db2:M_VN2</MirrorDb>
<MirrorDriver>COM.ibm.db2.jdbc.app.DB2Driver</MirrorDriver>
<AddTable>
<TargetSchema>DSYSAMPLE</TargetSchema>
<TargetTable>VNPERSON</TargetTable>
<SourceSchema>DSYSAMPLE</SourceSchema>
<SourceTable>VNPERSON</SourceTable>
<DeleteAuth>Y</DeleteAuth>
<InsertAuth>Y</InsertAuth>
<UpdateAuth>Y</UpdateAuth>
<SelectAuth>Y</SelectAuth>
<WhereClauseMirrorToMobile>CITY=:CITY</WhereClauseMirrorToMobile>
<WhereClauseMasterToMirror></WhereClauseMasterToMirror>
<JoinFilter>G</JoinFilter>
<Order>1</Order>
<Encrypt>N</Encrypt>
<AddColumn>
<SourceColumn>ID</SourceColumn>
<TargetColumn>ID</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>Y</Key>
</AddColumn>
<AddColumn>
<SourceColumn>NAME</SourceColumn>
<TargetColumn>NAME</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>ADDRESS</SourceColumn>
<TargetColumn>ADDRESS</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>CITY</SourceColumn>
<TargetColumn>CITY</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>HOMEPHONE</SourceColumn>
<TargetColumn>HOMEPHONE</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>WORKPHONE</SourceColumn>
<TargetColumn>WORKPHONE</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>MOBILEPHONE</SourceColumn>
<TargetColumn>MOBILEPHONE</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddIndex>
<Name>IDX_CITY</Name>
<ColNames>+CITY;</ColNames>
</AddIndex>
</AddTable>
</AddJdbcSubscription>
</Root>
Example of removing tables from a jdbc subscription:
This example removes two tables from the 'JDBCSUB_2' subscription
(assuming subscription had many tables).
Tables of a subscription are identified by their target schema and target
table names. When tables are excluded from a subscription, these tables in the
mirror database will be dropped. This may cause data loss if these tables still
have pending synchronization data that is not yet replicated to the source
database. Replicate="TRUE" attribute tells the xml tool to replicate existing data
before dropping these tables. In some cases, administration may know that there
is no data to replicate. Then it is fine to use Replicate="FALSE" to make the script
run faster. Also, certain problems that prevent successful
replication can be skipped by providing Replicate="FALSE".
<?xml version="1.0"?>
<!DOCTYPE Root SYSTEM "file:/com/ibm/mobileservices/xmlscripting
/mdas3.dtd">
<Root>
<AlterJdbcSubscription Replicate="TRUE">
<Name>JDBCSUB_2</Name>
<JdbcSubscription>
<RemoveTable>
<TargetSchema>DSYSAMPLE</TargetSchema>
<TargetTable>VNPERSON</TargetTable>
</RemoveTable>
<RemoveTable>
<TargetSchema>DSYSAMPLE</TargetSchema>
<TargetTable>VNMEDICALRECORD</TargetTable>
</RemoveTable>
</JdbcSubscription>
</AlterJdbcSubscription>
</Root>
Adding tables to a JDBC subscription
This example shows how to add tables to an existing subscription.
The <Order> tag tells the XML Scripting Tool to make this VNMEDICALRECORD
table the 2nd table in this subscription. If there are already more than
1 table, this change will push other tables to 3rd, 4th, 5th place.
The devices that are effected by this change will receive new configuration
information and will adjust accordingly. If there are less than 2 tables in this
subscription, this table will become the last table.
Replicate="FALSE" flag tells the XML Scripting tool not to replicate this
new table after it is created in the mirror. This will cause the mirror table
to be empty and there won't be any data to sync. Therefore, the subscription
will remain blocked (will not be syncable) until next successful replication
that will fill this table with data from source and make it ready for
synchronization. While adding a new table to an existing or a new subscription,
all columns of that source table must be listed with their synchronization
and replication settings.
<?xml version="1.0"?>
<!DOCTYPE Root SYSTEM "file:/com/ibm/mobileservices/xmlscripting
/mdas3.dtd">
<Root>
<AlterJdbcSubscription Replicate="FALSE">
<Name>JDBCSUB_2</Name>
<JdbcSubscription>
<AddTable>
<TargetSchema>DSYSAMPLE</TargetSchema>
<TargetTable>VNMEDICALRECORD</TargetTable>
<SourceSchema>DSYSAMPLE</SourceSchema>
<SourceTable>VNMEDICALRECORD</SourceTable>
<DeleteAuth>Y</DeleteAuth>
<InsertAuth>Y</InsertAuth>
<UpdateAuth>Y</UpdateAuth>
<SelectAuth>Y</SelectAuth>
<WhereClauseMirrorToMobile></WhereClauseMirrorToMobile>
<WhereClauseMasterToMirror></WhereClauseMasterToMirror>
<JoinFilter>G</JoinFilter>
<Order>2</Order>
<Encrypt>N</Encrypt>
<AddColumn>
<SourceColumn>RECORDID</SourceColumn>
<TargetColumn>RECORDID</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>Y</Key>
</AddColumn>
<AddColumn>
<SourceColumn>DATE_C</SourceColumn>
<TargetColumn>DATE_C</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>TIME_C</SourceColumn>
<TargetColumn>TIME_C</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>PATIENTID</SourceColumn>
<TargetColumn>PATIENTID</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>BLOODPRESSURE</SourceColumn>
<TargetColumn>BLOODPRESSURE</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>PULSERATE</SourceColumn>
<TargetColumn>PULSERATE</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>TEMPERATURE</SourceColumn>
<TargetColumn>TEMPERATURE</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>WEIGHT</SourceColumn>
<TargetColumn>WEIGHT</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>COMMENT</SourceColumn>
<TargetColumn>COMMENT</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
</AddTable>
</JdbcSubscription>
</AlterJdbcSubscription>
</Root>
Changing existing tables of a JDBC Subscription
This example shows how to change the details of existing tables of existing
subscriptions: This script modifies the where clauses that do horizontal
filtering for replication and synchronization. Since Replicate="TRUE"
is specified, before any change is made, existing mirror will be replicated
to the source database. Then changes will be applied and another replication
will be attempted again. This second replication may fail if you have an
error in the where clause. You will then need to revert your changes with another
'AlterJdbcSubscription' tag with Replicate="FALSE" flag.
You can also replicate manually using command line dsyreplicate.bat
after your changes are applied.
<?xml version="1.0"?>
<!DOCTYPE Root SYSTEM "file:/com/ibm/mobileservices/xmlscripting
/mdas3.dtd">
<Root>
<AlterJdbcSubscription Replicate="TRUE">
<Name>JDBCSUB_2</Name>
<JdbcSubscription>
<AlterTable>
<TargetSchema>DSYSAMPLE</TargetSchema>
<TargetTable>VNPERSON</TargetTable>
<Table>
<WhereClauseMirrorToMobile>NAME like :PREFIX</WhereClauseMirrorToMobile>
<WhereClauseMasterToMirror>NAME like 'A%'</WhereClauseMasterToMirror>
</Table>
</AlterTable>
</JdbcSubscription>
</AlterJdbcSubscription>
</Root>
Adding and Removing Indexes
This example adds a new index (IDX1_a) to an existing table and removes
an existing index (IDX1_b) from this table. Since MirrorOnly="TRUE" is
not specified for the index, this index will also be created on target
devices which subscribe to this subscription as well.
<?xml version="1.0"?>
<!DOCTYPE Root SYSTEM "file:/com/ibm/mobileservices/xmlscripting
/mdas3.dtd">
<Root>
<AlterJdbcSubscription Replicate="FALSE">
<Name>JDBCSUB_2</Name>
<JdbcSubscription>
<AlterTable>
<TargetSchema>DSYSAMPLE</TargetSchema>
<TargetTable>VNPERSON</TargetTable>
<Table>
<AddIndex>
<Name>IDX1_a</Name>
<ColNames>+NAME;</ColNames>
</AddIndex>
<RemoveIndex>
<Name>IDX1_b</Name>
</RemoveIndex>
</Table>
</AlterTable>
</JdbcSubscription>
</AlterJdbcSubscription>
</Root>
<AddReplMaster> tag defines source database connection information.
The system, instance, database and password are required.
<AddReplMirror> tag defines mirror database connection information.
The database, user id, password and the sync window
(replication frequency) are required.
Replication (DPropR) master and mirror information must be defined
before a DPropR subscription can reference such databases.
<AddDpropRSubscription> tag defines a DPropR subscription for Sync Server.
The replication mechanism between the source and the mirror database
must already be setup using Data Propagator tools as if XML Tool or
Sync Server never existed. XML Tool picks up from the point where
Sync Server specific setup is needed. Refer to Data Propagator documentation
to find out how to setup
replication sources and a replication subscription to
replicate your source and mirror. The mirror tables must already
be created before you can use
XML Tool to introduce your DPropr replication system to the
Sync Server via Sync Server DPropR subscription you are creating here.
<AddDProprSubscription> tag must contain 1 or more <AddTable> tags.
<AddTable> tags should list all the columns of the mirror table to specify
replication and synchronization options for each column using <AddColumn> tag.
XML Tool gets table descriptions from the mirror database in case of
DPropR subscriptions, unlike JDBC subscriptions which use the source database
for this.
<SourceSchema> and <SourceTable> identify a table in the source database
that we are subscribing to.
<TargetSchema> and <TargetTable> identify a table in the mirror database
that we are subscribing to.
These names will also be used on the mobile devices.
<AddTable> tags may specify indexes for the table columns using
<AddIndex> tag. Index names need to be unique for the whole system for
the sake of simplicity.
Below example creates a DPropR subscription named 'DPSUB1' which
uses VNURSE database as the source and M_VNURSE as the mirror.
This subscription has only 1 table that has multiple columns and 1
ascending index defined
(the '+' before the index column means ascending, '-' means descending).
Multiple columns can be specified like "+COL1;-COL2;+COL3" for an index.
<?xml version="1.0"?>
<!DOCTYPE Root SYSTEM "file:/com/ibm/mobileservices/xmlscripting
/mdas3.dtd">
<Root>
<AddReplMaster>
<System>SAD_LPT</System>
<Instance>DB2</Instance>
<Database>VNURSE</Database>
<UserId>db2admin</UserId>
<Password>db2admin</Password>
<DatabaseAlias>VNURSE</DatabaseAlias>
</AddReplMaster>
<AddReplMirror>
<Database>M_VNURSE</Database>
<UserId>db2admin</UserId>
<Password>db2admin</Password>
<SyncWindow>3600</SyncWindow>
</AddReplMirror>
<AddDproprSubscription Replicate="FALSE">
<Name>DPSUB1</Name>
<SubAdapter>DSYDPROPR</SubAdapter>
<EncryptionLevel>0</EncryptionLevel>
<Block>N</Block>
<MasterSystem>SAD_LPT</MasterSystem>
<MasterInstance>DB2</MasterInstance>
<MasterDb>VNURSE</MasterDb>
<MirrorDb>M_VNURSE</MirrorDb>
<AddTable>
<TargetSchema>DSYSAMPLE</TargetSchema>
<TargetTable>VNMEDICALRECORD</TargetTable>
<SourceSchema>DSYSAMPLE</SourceSchema>
<SourceTable>VNMEDICALRECORD</SourceTable>
<DeleteAuth>Y</DeleteAuth>
<InsertAuth>Y</InsertAuth>
<UpdateAuth>Y</UpdateAuth>
<SelectAuth>Y</SelectAuth>
<WhereClauseMirrorToMobile></WhereClauseMirrorToMobile>
<WhereClauseMasterToMirror></WhereClauseMasterToMirror>
<JoinFilter>G</JoinFilter>
<Order>1</Order>
<Encrypt>N</Encrypt>
<AddColumn>
<SourceColumn>RECORDID</SourceColumn>
<TargetColumn>RECORDID</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>Y</Key>
</AddColumn>
<AddColumn>
<SourceColumn>DATE_C</SourceColumn>
<TargetColumn>DATE_C</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>TIME_C</SourceColumn>
<TargetColumn>TIME_C</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>PATIENTID</SourceColumn>
<TargetColumn>PATIENTID</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>BLOODPRESSURE</SourceColumn>
<TargetColumn>BLOODPRESSURE</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>PULSERATE</SourceColumn>
<TargetColumn>PULSERATE</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>TEMPERATURE</SourceColumn>
<TargetColumn>TEMPERATURE</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>WEIGHT</SourceColumn>
<TargetColumn>WEIGHT</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
<AddColumn>
<SourceColumn>COMMENT</SourceColumn>
<TargetColumn>COMMENT</TargetColumn>
<Subscribe>Y</Subscribe>
<Replicate>Y</Replicate>
<Key>N</Key>
</AddColumn>
</AddTable>
</AddDproprSubscription>
</Root>
<AddGroup> tag defines a Group for Sync Server. Users that can
synchronize data with Sync Server must be associated with existing Groups.
The Group is identified by it's name. It can be enabled or disabled for
synchronization. A Group can have 1 or more data filters defined
using <AddDataFilter> tag.
These data filters are used in where clauses of subscribed tables
to do horizontal filtering. For example, a Group can have a data filter
named ':CITY'. The where clause of a table may specify
a condition like MYSCHEMA.MYTABLE.CITYCOLUMN=:CITY. Then, in case of
this example, if we were to create this Group and synchronize it's users,
we would be selecting rows where MYSCHEMA.MYTABLE.CITYCOLUMN='SAN JOSE'
by default. Note that, individual users can override a data filter of their
Group to specify a different data filter value.
A user cannot override a data filter that it's Group does not provide.
In the example below, the User 'nurse_2' is created and assigned to 'GROUP_2'.
It overrides data filter :CITY to provide a different city name specific to
this 'nurse_2'. However, this user does not override
the data filter ':COUNTRY'. It inherits the ':COUNTRY' data filter
from it's group 'GROUP_2' with it's value 'USA'.
At last, an existing user named 'nurse_3' is assigned to this
new group 'GROUP_2'.
<?xml version="1.0"?>
<!DOCTYPE Root SYSTEM "file:/com/ibm/mobileservices/xmlscripting
/mdas3.dtd">
<Root>
<AddGroup>
<Name>GROUP_2</Name>
<Description>A test group</Description>
<Enabled>Y</Enabled>
<AddDataFilter>
<Name>:CITY</Name>
<Value>'SAN JOSE'</Value>
</AddDataFilter>
<AddDataFilter>
<Name>:COUNTRY</Name>
<Value>'USA'</Value>
</AddDataFilter>
<IncludeSubscriptionSet>
<Name>SUBSET_2</Name>
</IncludeSubscriptionSet>
</AddGroup>
<AddUser>
<Name>nurse_2</Name>
<UserGroup>GROUP_2</UserGroup>
<Description>A test user</Description>
<Password>nurse_2</Password>
<OverrideDataFilter>
<Name>:CITY</Name>
<Value>SANTA CLARA</Value>
</OverrideDataFilter>
</AddUser>
<AlterUser>
<Name>nurse_3</Name>
<User>
<UserGroup>GROUP_2</UserGroup>
</User>
</AlterUser>
</Root>
Another example of changing the details of a synchronization user:
This script changes the description of a user named nurse_5, sets a new
password and overrides a data filter :DF1 that the user inherits from it's
assigned Group whatever that might be.
If the current group of the user does not have a data filter called :DF1,
this script will fail and no changes will be applied for this <AlterUser> tag.
<?xml version="1.0"?>
<!DOCTYPE Root SYSTEM "file:/com/ibm/mobileservices/xmlscripting/mdas3.dtd">
<Root>
<AlterUser>
<Name>nurse_5</Name>
<User>
<Description>A new description.</Description>
<Password>nurse5pass</Password>
<OverrideDataFilter>
<Name>:DF1</Name>
<Value>-1</Value>
</OverrideDataFilter>
</User>
</AlterUser>
</Root>
<AddSubscriptionSet> tag defines a new Subscription Set for synchronization
purposes. <IncludeSubscription> tag is used to associate existing subscriptions
with this subscription set. In this example, contents of Subscription
Set SUBSET_2 are JDBCSUB_2 and FILESUB_3 where this example assumes
that JDBCSUB_2 and FILESUB_3 are existing subscriptions. The order of the
subscriptions inside a subscription set is by default the order that they
appear in the XML. In this example, JDBCSUB_2 will
be synchronized before FILESUB_3. <Order> tag can be specified
inside <IncludeSubscription> tag to
explicitly specify relative ordering.
<?xml version="1.0"?>
<!DOCTYPE Root SYSTEM "file:/com/ibm/mobileservices/xmlscripting
/mdas3.dtd">
<Root>
<AddSubscriptionSet>
<Name>SUBSET_2</Name>
<IncludeSubscription>
<Name>JDBCSUB_2</Name>
</IncludeSubscription>
<IncludeSubscription>
<Name>FILESUB_3</Name>
</IncludeSubscription>
</AddSubscriptionSet>
</Root>
Example of changing an existing subscription set:
This example adds a subscription named 'JDBCSUB_4'
(assuming JDBCSUB_4 already exists)
into the subscription set 'SUBSET_2' and makes this new susbcription
the 1st subscription in this subscription set.
<?xml version="1.0"?>
<!DOCTYPE Root SYSTEM "file:/com/ibm/mobileservices/xmlscripting
/mdas3.dtd">
<Root>
<AlterSubscriptionSet>
<Name>SUBSET_2</Name>
<SubscriptionSet>
<IncludeSubscription>
<Name>JDBCSUB_4</Name>
<Order>1</Order>
</IncludeSubscription>
</SubscriptionSet>
</AlterSubscriptionSet>
</Root>
Související koncepce
Související úlohy
Související odkazy