My colleague and I are trying to find the best query to sync up an Oracle database with that of a SQL server. There are about 80k+ rows with ~19 columns of data in each row. We have a linked server setup between the two servers and we have a query that works but for 80k records, the query took 10 hours to copy the records over. I can post the query we used but I would like to have a fresh set of eyes. This is a new process so we aren't trying to retrofit a solution to existing code. LIke I said before, permissions aren't an issue, it is just a matter of getting the data from Point A to Point B in the quickest time. This is to be used on a coldfusion supported web site and the client would like to click a buttton to sync up the data but again, this is just 'wish list' of requirements we are working with.
Additional Thoughs I'd like to add:We have tried openquery and using linked server but both took about the same time to complete. Most are varchar(64 bytes), a couple of varchar(128) and a couple of varchar(12 bytes). One suggestion someone else made was to write the data to a flat file, ftp the flat file to Point B and then import it. That is a viable solution but the more steps we include, the more chances there are of something breaking.
Thanks in advance. I look forward to seeing y'alls solutions.
HPWDHPWD
1,2342 gold badges25 silver badges52 bronze badges

3 Answers

I've had more success with an SSIS package than linked servers. If you use the Oracle DLL's, it's not too bad.
SQLMasonSQLMason
Have you looked at Oracle Transparent Gateway? Here is the reference manual. It drives SQL Server from Oracle instead of the other way around.
DCookieDCookie
35.8k11 gold badges65 silver badges82 bronze badges
Zidsoft CompareData you can set up the sync task visually and also scheduling it to run via the commandline. Disclosure: I am the developer of this product.
Farid ZFarid Z
4561 gold badge5 silver badges11 bronze badges

Not the answer you're looking for? Browse other questions tagged sql-serveroraclecoldfusion or ask your own question.

You can connect from ArcGIS clients and work with the data in the database management systems or data warehouse appliances listed here. To see a description of functionality available when working with databases from ArcGIS, see Databases and ArcGIS.
Database management systems differ from each other in how they are implemented and what functionality they provide. As a result, there are sometimes caveats or rules you need to be aware of when working with each database type in ArcGIS. These are listed in the following sections.

Altibase

Note:

Dameng

Spatial data is stored in the Dameng geometry type. A typical installation of Dameng includes a spatial type; however, you must initialize it to use it.

See what versions of Dameng are supported with ArcGIS 10.7.1.

IBM Db2

Db2 authenticates connections to the database using operating system logins. However, when you connect to Db2 from the Database Connections dialog box or the Create Database Connection geoprocessing tool, you can specify the Database authentication option. This allows you to save the specific user name and password of the login, which is required if you will register the database connection with ArcGIS Server.

ArcGIS requires a unique identification field to render features in a map. ArcGIS relies on the database to insert values to the unique identification field when new features are created. Since Db2 z/OS does not have a data type or built-in mechanism to populate values in an ID field, ArcGIS tools that create new rows in a database table cannot be used with databases in Db2 z/OS. Also, since you cannot insert features to a Db2 z/OS table from ArcGIS, you cannot publish a feature service that contains data from a Db2 z/OS database.

If you require functionality such as attachments, archiving, topology, geodatabase versions for simultaneous editing, the ability to replicate data, or you want to publish feature services containing data from Db2 z/OS, you can enable a geodatabase in your Db2 database.

See what versions of Db2 are supported with ArcGIS 10.7.1.

IBM Informix

Informix authenticates connections to the database using operating system logins. When connecting to Informix from the Database Connections dialog box or the Create Database Connection geoprocessing tool, though, you can specify the Database authentication option. This allows you to save the specific user name and password of the login, which is required if you will register the database connection with ArcGIS Server.
If you require functionality such as attachments, archiving, topology, geodatabase versions for simultaneous editing, or the ability to replicate data, you can enable a geodatabase in your Informix database.
See what versions of Informix are supported with ArcGIS 10.7.1.

IBM Netezza Data Warehouse Appliance

IBM Netezza Data Warehouse Appliance is deprecated at this release.
Data warehouses typically store large amounts of data. To use ArcGIS to visualize, perform analyses, or publish data to Netezza, use a subset of data. How you do this depends on what you want to do with the data.
  • If you are adding data to ArcMap for viewing and analysis within the map, add a query layer and define the query layer expression to return only a subset of the data.
  • If you are adding data to ArcMap to publish a map service, define a database view that contains only a subset of the data. Database views are stored in the database. You can use the Create Database View geoprocessing tool or an SQL client to define views.
  • If you are adding data to ArcMap to publish a feature service or to publish to a portal, uncheck the option to Make newly added layers visible by default before adding your data to the map. Next, open the Layer Properties and create a Definition Query that defines a subset of data using SQL. Once you have restricted the amount of data that displays in the map, make the layer visible and prepare your map for publishing.
Caution:
Unless your map is set so that data is not automatically displayed when added, do not drag data directly from your database connection to the map if you have not already defined a subset of data. Extremely large datasets, as are normally stored in a data warehouse appliance, would take an inordinate amount of time to display on the map.
There are two ArcGIS-specific limitations when using a Netezza Data Warehouse Appliance:
  • If you use the legacy Netezza spatial type, the spatial columns in your tables must be named shape or aliased to shape.
    The legacy spatial type uses the varchar data type; therefore, the shape column name or alias is how ArcGIS identifies that the column stores spatial data rather than text. If the spatial column in your table has a different name, create a view on the table and alias the spatial column name to shape.
  • ArcGIS requires a unique identification field to render features in a map. ArcMap prompts you to specify this unique ID field when you add a spatial table to the map.
    ArcGIS tools do not insert new values into the unique ID fields in a database; rather, they rely on the database to insert values to the field. Since Netezza does not have a data type or built-in mechanism to populate values in an ID field, ArcGIS tools that create new rows in a database table cannot be used with Netezza. Also, since you cannot insert features to a Netezza table from ArcGIS, you cannot publish a feature service that contains data from a Netezza Data Warehouse Appliance.
Other limitations are related to Netezza itself.
  • Since Netezza spatial packages use different data types, which spatial package you use affects how you configure the Netezza ODBC driver to connect to Netezza from client applications.
  • As a Netezza user, you know that Netezza Spatial stores simple data. Therefore, you cannot paste nonsimple data from a geodatabase or another database into Netezza.
  • As a Netezza user, you also know that rows are limited to a total size of 65,535 bytes. To help you calculate row sizes, the following table shows the Netezza data types that ArcGIS creates in Netezza and the amount of disk space the data types use:
ArcGIS field typeNetezza data type createdDisk usage
DATE
timestamp
8 bytes
DOUBLE
numeric(p,s)
By default, p (precision) = 38 and s (scale) = 8.
Precision of 9 or less = 4 bytes
Precision of 10–18 = 8 bytes
Precision of 19–38 = 16 bytes
FLOAT
If precision of 6 or less is specified, numeric(p,s) is created. If precision 7–15 is specified, double is created.
numeric = 4 bytes
double = 8 bytes
GEOMETRY
If you use the legacy Netezza Spatial Package, a variable length character (up to 64,000) column is created.
If you use the Netezza Spatial Esri Package, an ST_Geometry column is created.
For the legacy Netezza Spatial Package
  • Point geometries are created as character varying(80).
  • The base size for all other geometries is 64,000 bytes. However, if creating a field of this size would cause the total row size to exceed 65,535 bytes, the varchar field will be created with a smaller precision.
    For example, if the total size of all other fields in the row is 2,000 bytes, the geometry field will be created as character varying(63,535).
For the Netezza Spatial Esri Package
  • Point geometries are created as ST_Geometry(140).
  • The base size for all other geometries is 64,000 bytes. However, if creating a field of this size would cause the total row size to exceed 65,535 bytes, the ST_Geometry field will be created with a smaller precision.
    For example, if the total size of all other fields in the row is 2,000 bytes, the geometry field will be created as ST_Geometry(63,535).
Note:
If the size of any individual geometry exceeds the size of the field when data is inserted to the feature class, a null geometry is inserted.
For example, if you copy a polygon feature class from another data source and paste it into your Netezza database, the table gets created with a geometry field (maximum size of 64,000 bytes). Next, the records are inserted into the table. If any of the geometries being inserted exceed 64,000 bytes, the record and all the other attributes are inserted, but a null is inserted into the geometry field.
GUID
Fixed-length character(38)
40 bytes
LONG INTEGER
If scale is greater than 0, numeric(p,s) is created. If scale is 0, an integer is created.
integer = 4 bytes
numeric with precision of 9 or less = 4 bytes
numeric with precision of 10–18 = 8 bytes
numeric with precision of 19–38 = 16 bytes
OBJECTID
integer
4 bytes
SHORT INTEGER
smallint
2 bytes
TEXT
Variable length, Unicode(p)
P is the field length you specify for the text field. The default value is 50.
p x 4 = number of bytes used

See what versions of Netezza Data Warehouse Appliance are supported with ArcGIS 10.7.1.

Microsoft Azure databases

If you deploy ArcGIS on Microsoft Azure, you can use any of the following Microsoft Azure cloud-based database services to store your data:
  • Microsoft Azure SQL Database
  • Microsoft Azure Database for PostgreSQL
When possible, you should run your ArcGIS clients on Microsoft Azure and use the same location (region) as your Azure databases for better performance.
Tip:
Esri provides a Microsoft Azure image that includes ArcGIS Enterprise. You can download the ArcGIS Enterprise Cloud Builder for Microsoft Azure from My Esri, and use it to deploy ArcGIS Enterprise on Microsoft Azure. You can optionally register an Azure database with ArcGIS Server sites in an Enterprise deployment. See ArcGIS Enterprise on Microsoft Azure help for more information.

See what versions of Azure database services are supported with ArcGIS 10.7.1.

Azure SQL Database

You can use a single database or Managed Instance of Azure SQL Database with ArcGIS. To connect to these databases, install a supported Microsoft ODBC driver for SQL Server on the ArcGIS client machine.
Keep the following in mind when working with Azure SQL Database from ArcGIS:
  • Because Azure SQL Database and SQL Server implementations are so similar, there is not a separate Azure SQL Database option in the Database Connection dialog box or the Create Database Connection geoprocessing tool. Specify SQL Server when connecting to Azure SQL Database, and follow the instructions for creating a connection from ArcGIS to SQL Server.
  • You must type the database name In the Database Connection dialog box. You cannot choose the database from a drop-down list, and you cannot leave the Database text box blank.
  • When loading large amounts of data to a feature class (several million records or more), you should estimate the spatial (x,y) extent of the data and create an appropriate spatial index on the feature class before loading the data. The operation of creating a spatial index on a feature class containing millions of records could be terminated if Microsoft Azure determines that the operation is consuming too many server resources. This can leave your feature class with no spatial index. Therefore, Esri recommends you create an appropriate spatial index first, and then load data.
  • There are some tasks separate from ArcGIS that you perform directly in the database management system. If you are familiar with using SQL Server, there are some differences you should be aware of if you intend to use Azure SQL Database instead. See the Microsoft TechNet article, Feature comparison: Azure SQL Database versus SQL Server for information on these differences.

Azure Database for PostgreSQL

When you use an Azure Database for PostgreSQL (whether it contains a geodatabase or not), spatial data is always stored using a PostGIS spatial data type. You cannot install or use ST_Geometry in these databases.

Microsoft SQL Server

Although Microsoft allows SQL Server user accounts to write data to schemas of any name, ArcGIS requires that user accounts that create tables, views, or other objects in the database have identical user names and schema names. For example, you cannot connect from ArcGIS as elmer and write to a schema named dataowner.
If you require functionality such as attachments, archiving, topology, geodatabase versions for simultaneous editing, or the ability to replicate data, you can enable a geodatabase in your SQL Server database.

See what versions of SQL Server are supported with ArcGIS 10.7.1.

Oracle

ArcGIS supports Oracle SDO_Geometry and Esri ST_Geometry spatial types in Oracle databases. SDO_Geometry is a separate, third-party installation. If you want to use the Esri ST_Geometry type to store spatial data, copy the ST_Geometry shape library to your Oracle machine and run the Create Spatial Type geoprocessing tool to create the ST_Geometry type, subtypes, and functions.
If you require functionality such as attachments, archiving, topology, geodatabase versions for simultaneous editing, or the ability to replicate data, you can enable a geodatabase in your Oracle database.

See what versions of Oracle are supported with ArcGIS 10.7.1.

PostgreSQL

ArcGIS supports PostGIS geometry and geography types and Esri ST_Geometry spatial types in PostgreSQL databases. PostGIS is a separate, third-party installation. If you want to use the Esri ST_Geometry type to store spatial data, copy the st_geometry library into your PostgreSQL installation and run the Create Spatial Type geoprocessing tool to create the ST_Geometry type, subtypes, views, and functions.
Although PostgreSQL allows user accounts to write data to schemas of any name, ArcGIS requires that user accounts that create tables, views, or other objects in the database have identical user names and schema names. For example, you cannot connect from ArcGIS as vlad and write to the public schema.
If you require functionality such as attachments, archiving, topology, geodatabase versions for simultaneous editing, or the ability to replicate data, you can enable a geodatabase in your PostgreSQL database.

See what versions of PostgreSQL and PostGIS are supported with ArcGIS 10.7.1.

SAP HANA

Keep the following information in mind when using SAP HANA:
  • SAP HANA uses columnar data storage, which behaves as its own index. Therefore, the spatial columns in SAP HANA tables do not require a spatial index for query performance.
  • Since SAP HANA does not utilize user-defined indexes, you do not manage or rebuild indexes on tables in SAP HANA.
  • When you load data into SAP HANA, the spatial reference used by the data must exist in the SAP HANA system table.
  • SAP HANA redefined their spatial reference system EPSG ID 4326 from a planar representation of WGS84 to a round-earth representation. Tables that store spatial data in SAP HANA with spatial reference system 4326 may not behave as expected in ArcGIS.
    The round-earth implementation of WGS84 (EPSG ID: 4326) in SAP HANA contains key limitations that makes it unsuitable when working with certain types of data. For example, global datasets that exceed hemisphere boundaries or cross-hemisphere boundary lines return inconsistent results. Data you create or load using ArcGIS continues to be stored using a planar version of the WGS84 (EPSG ID 1000004326) coordinate system to ensure that ArcGIS can work with all types of data. However, ArcGIS can read round-earth WGS data that you create in SAP HANA using SQL or a third-party product.
  • SAP HANA stores spatial reference system information in their ST_SPATIAL_REFERENCE_SYSTEMS system view. Spatial data you load into SAP HANA must either have a null spatial reference, or use a spatial reference that exists in the ST_SPATIAL_REFERENCE_SYSTEMS system view. If this view does not contain the spatial reference system for data you want to load, use the SAP HANA CREATE SPATIAL REFERENCE SYSTEM Statement to update the view with the spatial reference system you need.
    Tip:
    More information can be found in the CREATE SPATIAL REFERENCE SYSTEM page of the SAP HANA Spatial Reference documentation.
    If the spatial data you import or copy to your SAP HANA database does not have a spatial reference defined, SAP HANA assigns a default SRID of 0, a Cartesian spatial reference system that works with data on a flat 2-dimensional plane.

See what versions of SAP HANA are supported with ArcGIS 10.7.1.

SQLite

ArcGIS supports SQLite databases with the following file extensions and that contain one of the following storage types:
  • Esri ST_Geometry—The database file must have the extension .sqlite.
  • SpatiaLite—The database file must have the extension .sqlite.
  • GeoPackages—The GeoPackage file must have the extension .gpkg.
You can use only one spatial storage type per SQLite database. See Spatially enable an SQLite database for information on adding a spatial storage type to an existing database.
To create an SQLite database that uses either ST_Geometry or SpatiaLite storage, or to create a GeoPackage, use the Create SQLite Database geoprocessing tool.

See what versions of SQLite are supported with ArcGIS 10.7.1.

Data access

You control access to an SQLite database or GeoPackage by controlling permissions on the folder where it is stored. Unlike other databases, you do not create users who are authenticated by the database or grant privileges on specific datasets to other users.
SQLite and GeoPackages can be read by multiple users, but do not update the database or any of its contents while someone else is editing data or updating the database. For example, do not append data into an existing table or create a table in the database while someone else is loading data or creating a table in the database.

Data types

SQLite is also different from other databases in that fields are not assigned specific data types and data type definitions are not strictly enforced. Instead, SQLite uses storage classes in which values of different data types can be stored.
ArcGIS, however, can only work with one data type per field and strictly enforces data types. You should be aware of this difference in data type enforcement when viewing SQLite data in ArcGIS.
The following example creates a table with integer and text fields:
Even though the weight field is defined as an integer, SQLite will allow you to store numbers with decimals in it. It will even allow you to store text in it. For example, you can insert the following records:
However, the values appear as follows in ArcGIS because the weight field is defined as integer:
iditemweightstore
1
magnetic dual elliptical trainer with seat
75
CardioPlus Equipment
2
superfit treadmill4000
81
Sports Pit
3
serenity yoga mat
0
Aerobic Angels Sporting Goods
4
swim fins
0
The Plunge
See DBMS data types supported in ArcGIS for a list of which SQLite data types map to which ArcGIS data types.

Teradata Data Warehouse Appliance

To connect from ArcGIS to a Teradata Data Warehouse Appliance, you must install the Teradata GSS client, ICU library, and ODBC driver on the ArcGIS client machines in a specific order. See Connect to Teradata for information.

See what versions of the Teradata Data Warehouse Appliance and client libraries are supported with ArcGIS 10.7.1.

The following is a list of guidelines for using a Teradata Data Warehouse Appliance with ArcGIS:
I am using PL/SQL Developer 11.0.4.1774. In my work, I need to use database whole day. Sometimes, during 30-60 minutes, I do not use ide and when I need to use it again, it requires reconnection.
I do not want to reconnect, is there any way to keep the connection alive whole day?
Lalit Kumar B
35.5k8 gold badges56 silver badges89 bronze badges
Mehmet Akif GüzeyMehmet Akif Güzey

3 Answers

Go to Tools --> Preferences and click 'Check connection'. It pings the database every 60 seconds, making the session active and avoiding idle session disconnects caused by profiles.
Jon HellerJon Heller
25.6k3 gold badges50 silver badges96 bronze badges
you need check 'IDLE_TIME' and 'CONNECT_TIME' setting for your user profile
If a user exceeds the CONNECT_TIME or IDLE_TIME session resource limit, then the database rolls back the current transaction and ends the session. When the user process next issues a call, the database returns an error.
I have PL/SQL Developer opens for days and don't have the issuemy PL/SQL Dev Tools-Prefernces-connection settings are:
and TIME settings are:
areare
2,1332 gold badges13 silver badges23 bronze badges
  1. Install the Reconnect plug-in so at least reconnecting can be a simple button-press.
  2. Possibly against the intention of the DBA (if the issue is due to profile/resource manager settings), but you might try setting a Sessions window (or other report/query etc) to auto-refresh every 25 minutes or so.
Edit: I see on the Bar Solutions website the developer has moved Reconnect from 'Plugins' to 'Old Plugins'. At my current client site I can't install plugins, so I can't tell whether it still works at the moment.
William RobertsonWilliam Robertson
8,8183 gold badges22 silver badges33 bronze badges

Not the answer you're looking for? Browse other questions tagged oraclesessiontimeplsqldeveloper or ask your own question.

The Mobile client database contains a subset of data stored in the Oracle database. This subset is stored in snapshots in the Mobile client database. Unlike a base table, a snapshot keeps track of changes made to it in a change log. Users can make changes in the Mobile client database and can synchronize these with the Oracle database.
The following sections describe how synchronization functions between the Mobile clients and an Oracle database using the Mobile Server. This chapter discusses how you can programmatically initiate the synchronization both from the client or the server side.

2.1 How Oracle Database Lite Synchronizes

When most people think of synchronizing data, they think of their Palm Pilot. When you hit the synchronization button for the Palm Pilot, any changes are added to the database of information on the Windows machine immediately. This is not the case for Oracle Database Lite, in that the synchronization is used for multiple clients—rather than a single user. In order to accommodate a large number of concurrent users, the application tables on the back-end database cannot be locked by a single user. Thus, the synchronization process involves using queues to manage the information between the Mobile clients and the application tables in the database.
Oracle Database Lite uses a synchronization model that maintains data integrity between the Mobile Server and the Mobile client. In addition, the synchronization is asynchronous and that as a result, change propagation is not immediate. The benefit, however, is that the clients do not stay connected for long while the changes are being applied.
You can specify if the synchronization occurs automatically or by manual request. For more details, see Section 2.1.3, 'Deciding on Automatic or Manual Synchronization'.
A simplified view of Mobile synchronization is as follows:
Figure 2-1 illustrates the architecture for Oracle Database Lite 10g applications.
Note:
This section describes how the synchronization is performed across several components and enterprise tiers to complete successfully. For more details on each component, see Section 1.2, 'Oracle Database Lite 10g Application Model and Architecture'.
Figure 2-1 Oracle Database Lite 10g Architecture

Description of 'Figure 2-1 Oracle Database Lite 10g Architecture'
Note:
Web-to-Go clients have one additional component, a light weight HTTP listener that is not shown in the diagram.
Oracle Database Lite uses the Mobile Server to replicate data between the Mobile clients with their client databases and the application tables, which are stored on a back-end Oracle database.
Thus, the more detailed description of how synchronization is performed within the separate components of Oracle Database Lite is demonstrated by Figure 2-2.
Figure 2-2 Data Synchronization Architecture

Description of 'Figure 2-2 Data Synchronization Architecture'
  1. A synchronization is initiated on the Mobile client either by the user or from automatic synchronization. Note that the Mobile client may be a PDA, a Windows platform client, or a supported Linux platform client.
  2. Mobile client software gathers all of the client changes into a transaction and the Sync Client uploads the transaction to the Sync Server on the Mobile Server.
  3. Sync Server places the transaction into the In-Queue.
    Note:
    When packaging your application, you can specify if the transaction is to be applied at the same time as the synchronization. If you set this option, then the transaction is immediately applied to the application tables. However, note that this may not be scaleable and you should only do this if the application of the transaction immediately is important and you have enough resources to handle the load.
  4. Sync Server gathers all transactions destined for the Mobile client from the Out-Queue.
  5. Sync client downloads all changes for client database.
  6. Mobile client applies all changes for client database. For Oracle Lite Mobile clients, if this is the first synchronization, the Oracle Lite database is created.
    Note:
    For information on what Oracle Lite database (ODB) files are installed on the client, see Section 6.3, 'Synchronize or Execute Applications on the Mobile Client'in the Oracle Database Lite Client Guide.
  7. All transactions uploaded by all Mobile clients are gathered by the MGP out of the In-Queue. The MGP executes independently and periodically based upon an interval specified in the Job Scheduler in the Mobile Server.
  8. The MGP executes the apply phase by applying all transactions for the Mobile clients to their respective application tables to the back-end Oracle database. The MGP commits after processing each publication. If any conflicts occur during this phase, most are resolved by the MGP or by the conflict resolution rules. If the conflict cannot be resolved, the transaction is moved into the Error Queue. See Section 1.3.1, 'Defining the Weight and Conflict Resolution for Publication Items' for more information.
    Note:
    The behavior of the apply/compose phase can be modified. See Section 6.1.1, 'Defining Behavior of Apply/Compose Phase for Synchronization' in the Oracle Database Lite Administration and Deployment Guide for more information.
  9. MGP executes the compose phase by gathering the client data into outgoing transactions for Mobile clients.
  10. MGP places the composed data for Mobile clients into the Out-Queue, where the Sync Server downloads these updates to the client on the next client synchronization.
Overall, synchronization involves two parties: the Mobile client using the Sync Client/Server to upload and download changes and the MGP process interacting with the queues and the application tables to apply and compose transactions. These are displayed separately in the Data Synchronization section of the Mobile Manager.
The following sections describe synchronization activity:

2.1.1 Oracle Lite Mobile Client Database Created on First Synchronization

When a user synchronizes an Oracle Lite Mobile client for the first time, the Mobile client creates an Oracle Lite database on the client machine for each subscription that is provisioned to the user. The Mobile client then creates a snapshot in this database for each publication item contained in the subscription, and populates it with data retrieved from the server database by running the SQL query (with all the variables bound) associated with the publication item. Once installed, Oracle Database Lite is transparent to the end user; it requires minimal tuning or administration.
As the user accesses and uses the application, changes made to the data in the Oracle Lite database are captured by the snapshots. When the connection to the Mobile Server is available, the changes can be synchronized with the Mobile Server.

2.1.2 Using Multiple Databases for Application Data

By default, the Mobile repository metadata and the application data are stored on the same database. However, if for performance or other reasons, you may store application data on a separate database other than the main database where the Mobile repository exists. In this manner, the Mobile repository exists on the main database and the data for one or more applications may exist on the main database or another database of your choosing.
Figure 2-3 Separating Application Data from Mobile Repository

Description of 'Figure 2-3 Separating Application Data from Mobile Repository'
You can register one or more databases to host the application data. Once registered, you can specify during publication creation where to host the application data. Synchronization is executed on a per publication basis rotating through the databases. For more information, see Section 3.2, 'Register a Remote Oracle Database for Application Data'.

2.1.3 Deciding on Automatic or Manual Synchronization

In the past, all that was available was manual synchronization. That is, a client manually requests a synchronization either through an application program executing an API or by a user manually pushing the Sync button.
Currently, you can configure for synchronization to automatically occur under specific circumstances and conditions. When these conditions are met, then Oracle Database Lite automatically performs the synchronization for you without locking your database, so you can continue to work while the synchronization happens in the background. This way, synchronization can happen seamlessly without the client's knowledge.
Note:
Within a publication, you can have one or more publication items. You can define both manual and automatic publication items within the same publication.
Manual Synchronization may be initiated, as follows:
Automatic Synchronization can be configured to automatically occur under specific circumstances and conditions. When these conditions are met, then Oracle Database Lite automatically performs the synchronization for you without locking your database, so you can continue to work while the synchronization happens in the background. This way, synchronization can happen seamlessly without the client's knowledge.
For example, you may choose to enable automatic synchronization for the following scenarios:
These are just a few examples of how automatic synchronization can make managing your data easier, be more timely, and occur at the moment you need it to be uploaded.
Synchronization is closely tied to how you define the snapshot for your application. See Section 1.3, 'Creating the Publish-Subscribe Model for Mobile Users' for a description of a snapshot and its components. One of the components is a publication item. If you want automatic synchronization, you define it at the publication item level.
Note:
When a manual synchronization is requested by the client, ALL publication items are synchronized at that time—including those defined as manual and automatic synchronization. However, if an automatic synchronization is currently executing, the manual synchronization request is delayed until the automatic synchronization completes. Alternatively, you can stop the automatic synchronization to allow the manual synchronization to occur. If you choose to do this, then after the manual synchronization is finished, re-start the automatic synchronization.
The differences between the two types of synchronization are as follows:
Table 2-1 Difference Between Automatic and Manual Synchronization

Manual SynchronizationAutomatic Synchronization
Initiation
After the snapshot is set up, you can initiate either by the user initiating mSync or by an application invoking one of the synchronization APIs.
All of the set up for automatic synchronization is configured. Once configured, it happens automatically, so there is no synchronization API.
Configuration for automatic synchronization can be defined when you create the publication item, publication or the platform.
Controlling synchronization
Synchronization occurs exactly when the user/application requests it.
Synchronization occurs without the user being aware of it occuring. You may have to manage synchronization through the Sync Control API if you have publications that contain both manual and automatic synchronization publication items.
Objects synchronized
All
The following objects are not synchronized by an automatic synchronization: sequences, DDL scripts, resources—such as Java stored procedures—indexes and automatic synchronization rules and conditions.

Automatic synchronization is based on a different model than manual synchronization. Automatic synchronization operates on a transactional basis. Thus, when the conditions are correct, any new data transactions are uploaded to the server, in the order of the specified priority for the data. In the manual synchronization model, you can synchronize all data or use the selective sync option, where you can detail only certain portions of the data to be synchronized. The selective sync option is not supported in automatic synchronization, since we are no longer concerned with synchronization of only a subset of data.
To enable high priority synchronization for automatic synchronization, Oracle Database Lite adds a hidden column (MSG$PRIO) to all automatic synchronization snapshots to designate if this data has a higher priority for synchronization if conditions are right. If users need to indicate that a particular record is high priority, they can set the column value to (0). Then the sync agent automatically schedules a high priority synchronization for the transaction that contains this record.

2.1.4 Deciding on Synchronization Refresh Option

How or when data changes are applied to either the Mobile Server or the Mobile client depends upon the synchronization refresh option at the publication item level. Synchronization refresh options may ease the cost burden for resources, such as wireless connectivity, bandwidth and network availability, personnel loss of time during the synchronization process, and so on.
Oracle Database Lite employs synchronization refresh options that may be utilized to synchronize data between the Oracle enterprise database and the Mobile client. With the following Oracle Database Lite refresh options, you can maintain data accuracy and integrity between the Oracle database and Mobile client:

2.1.4.1 Fast Refresh

The most common method of synchronization is a fast refresh publication item where changes are uploaded and downloaded by the client. Meanwhile, the MGP periodically collects changes uploaded by all clients and applies them to the back-end Oracle database tables. Then, the MGP composes new data, ready to be downloaded to each client during the next synchronization, based on pre-defined subscriptions.

2.1.4.2 Complete Refresh

During a complete refresh, all data for a publication is downloaded to the client. For example, during the first synchronization session, all data on the client is refreshed from the Oracle database. This form of synchronization takes longer because all rows that qualify for a subscription are transferred to the client device, regardless of existing client data.
The complete refresh model is resource intensive as all aspects of synchronization are performed. This model should only be utilized for snapshots/publication items where it is an absolute requirement.

2.1.4.3 Queue-Based Refresh

The developer creates their own queues to handle the synchronization data transfer. There is no synchronization logic created with a queue-based refresh; instead, the synchronization logic is implemented solely by the developer. A queue-based publication item is ideally suited for scenarios that require synchronization to behave in a different manner than normally executed. For instance, data collection on the client; all data is collected on the client and pushed to the server.
With data collection, there is no need to worry about conflict detection, client state information, or server-side updates. Therefore, there is no need to add the additional overhead normally associated with a fast refresh or complete refresh publication item.

2.1.4.4 Forced Refresh

This is not a refresh option; however, we discuss it here because it is often mistaken for a refresh option—specifically, it is often confused with the complete refresh option. The Forced Refresh is a one-time execution request made from within Mobile Manager, the GUI interface for the Mobile Server. The forced refresh option may result in a loss of critical data on the client.
The forced refresh option is an emergency only synchronization option. This option is used when a client is corrupt or malfunctioning, so that you decide to replace the Mobile client data with a fresh copy of data from the enterprise data store with the forced refresh. When this option is selected, any data transactions that have been made on the client are lost.
When a forced refresh is initiated all data on the client is removed. The client then brings down an accurate copy of the client data from the enterprise database to start fresh with exactly what is currently stored in the enterprise data store.

2.1.5 Synchronizing to a File With File-Based Sync

There are times when you do not have network access to the Mobile Server, but there is a way you can use removable media to transport a file between the Mobile Server and the client. In this instance, you may want to use File-Based Sync, which saves all transactions in an encrypted file either for the upload from the client for the Mobile Server or the download from the Mobile Server for the client.
Once saved within the encrypted file, the file is manually transported and copied onto the desired recipient—whether Mobile client or Mobile Server. This file is uploaded and the normal synchronization steps are performed. The only difference is that the interim transmission of the data is through a file copied to the correct machine—rather than transmitted over a network.
For full details on file-based synchronization, see Section 6.8, 'Synchronizing to a File with File-Based Sync' in the Oracle Database Lite Administration and Deployment Guide. To enable and perform file-based synchronization through the APIs, see Chapter 4, 'Invoking Synchronization in Applications With the Mobile Sync APIs'.

2.1.6 How Downloaded Data is Processed on the Mobile Client

The client processes the downloaded data. By default, the steps taken to process the received data on the client is as follows:
  1. Process each publication item
  2. Commit
  3. Process each DDL statement
  4. Commit
Note:
The acknowledgment is sent only in the subsequent synchronization.
Alternatively, the configuration could effect how the data is processed on the client. The following are two scenarios where the commit may occur before all of the publication items are processed:

2.1.7 How Updates Are Propagated to the Back-End Database

The synchronization process applies client operations to the tables in the back-end database, as follows:
  1. The operations for each publication item are processed according to table weight. The publication creator assigns the table weight to publication items within a specific publication. This value can be an integer between 1 and 1023. For example, a publication can have more than one publication item of weight '2' which would have INSERT operations performed after those for any publication item of a lower weight within the same publication. You define the order weight for tables when you add a publication item to the publication. See Section 2.4.1.7.2, 'Using Table Weight' for more information.
  2. Within each publication item being processed, the SQL operations are processed as follows:
    1. Client INSERT operations are executed first, from lowest to highest table weight order.
    2. Client DELETE operations are executed next, from highest to lowest table weight order.
    3. Client UPDATE operations are executed last, from highest to lowest table weight order.
For details and an example of exactly how the weights and SQL operations are processed, see Section 2.4.1.7.2, 'Using Table Weight'.
Note:
This order of executing operations can cause constraint violations. See Section 2.10, 'Synchronizing With Database Constraints' for more information.
In addition, the order in which SQL statements are executed against the client Oracle Lite database is not the same as how synchronization propagates these modifications. Instead, synchronization captures the end result of all SQL modifications as follows:
  1. Insert an employee record 4 with name of Joe Judson.
  2. Update employee record 4 with address.
  3. Update employee record 4 with salary.
  4. Update employee record 4 with office number
  5. Update employee record 4 with work email address.
When synchronization occurs, all modifications are captured and only a single insert is performed on the back-end database. The insert contains the primary key, name, address, salary, office number and email address. Even though the data was created with multiple updates, the Sync Server only takes the final result and makes a single insert.

2.1.8 How Modified BLOB Data is Synchronized

If you update the contents of a BLOB belonging to a particular row in particular table, then the row is not synchronized. The row only stores a handle to the BLOB object—not the object itself. So, the handle does not change. Therefore, the changes made to the BLOB are not propagated to the server. To cause the BLOB contents to be synchronized to the server, the row must be explicitly updated by the application. Any update of the row should be sufficient—even updating the column to the same value.

2.2 Enabling Automatic Synchronization

Automatic synchronization occurs in the background, so that the user does not have to perform a synchronization; thus, the client appears continually connected to the back-end database without user interaction. All modifications to each record are saved in a log within the client database. When you requested synchronization manually, Oracle Database Lite locked the database while processing your request. However, with automatic synchronization, it could be occurring while you are performing other tasks to the client database.

Symmetricds Tutorial

When synchronization occurs, all of the modified records stored in the log are uploaded to the server. In addition, any modified records from the server are downloaded into the client database. This occurs in the same manner as manual synchronization. The only difference is when the synchronization is executed and how the modified records are stored.
The following are details about automatic synchronization:
Table 2-2 Automatic Synchronization
Steps for Automatic SynchronizationSee the Following for Details
The developer enables the publication item to use automatic synchronization.
The client can disable and enable automatic synchronization through the client Workspace or with the Sync Control API.
You can configure under what rules the automatic synchronization occurs.
The server can notify the client of data waiting for download.
The client application can request status of the outcome of an automatic synchronization.

The following sections detail how you can configure for automatic synchronization:

2.2.1 Enable Automatic Synchronization at the Publication Item Level

Automatic synchronization can be enabled at publication item level. It is only the 'enabled' publication items within a snapshot that can have automatic synchronization. All other publication items use manual synchronization. See Section 6.4, 'Create a Publication Item' for details of how to enable synchronization in a publication item using MDW or Section 2.4.1.3, 'Create Publication Items' using the API.
Within a publication, you can have one or more publication items. You can define both manual and automatic synchronization publication items within the same publication. However, if you have automatic synchronization enabled, then an automatic synchronization may be occurring when the client asks for a manual synchronization. In this case, the manual synchronization stops the automatic synchronization so that all snapshots are synchronized, unless the synchronization in in the middle of a large data commit or is executing over a slow network . If the automatic synchronization does not stop immediately due to these exceptions and you want the manual synchronization to start immediately, you can stop the automatic synchronization to allow the manual synchronization to occur. After the manual synchronization is finished, re-start the automatic synchronization. You can start and stop automatic synchronization either programmatically or through the client Workspace. See Section 2.2.2, 'Enable/Disable Automatic Synchronization on the Mobile Client' for full details.

2.2.2 Enable/Disable Automatic Synchronization on the Mobile Client

Automatic synchronization is enabled by default if a publication is enabled for automated synchronization. However, you may turn on and off automatic synchronization—either temporarily or permanently—as follows:
The following control APIs can be used to manage the automatic synchronization or enable/disable automatic synchronization:

2.2.2.1 Start or Stop Automatic Synchronization

Use the start/stop methods to temporarily start or stop the Sync Agent. The user may want to stop the Sync Agent for many reasons, such as aborting an automatic synchronization that may be running longer than desired, freeing up system resources, or de-fragmenting or backing up an Oracle Lite database. If the automatic synchronization is not re-started with the control API, then it restarts by either a manual synchronization or the device management agent after reboot.
By default, if you are using the mSync GUI to initiate a synchronization, the underlying code stops and restarts the automatic synchronization for you, as described below:
  1. Stops the automatic synchronization with the Sync Control API.
  2. Initiates a manual synchronization with the programmatic API.
  3. Starts the automatic synchronization with the Sync Control API.
You can stop or start automatic synchronization using the Sync Control API. The stop API has one parameter for input, which is a timeout. You can supply one of the following values for the timeout, which is a long that specifies a time in milliseconds to wait for any current activity in the automatic synchronization to complete.
Note:
There is also a GUI for starting, stopping the automatic synchronization process. See Section 6.4.2, 'Start, Stop, or Get Status for Automatic Synchronization' in the Oracle Database Lite Administration and Deployment Guide for more details.

2.2.2.2 Enable or Disable Automatic Synchronization

The start and stop methods only control the automatic synchronization temporarily. Use the disable method to fully disable automatic synchronization, so that it is not restarted when a device is powered on. Turn the automatic synchronization engine back on with the enable method. You can enable or disable automatic synchronization in one of three ways, as described below:

2.2.2.3 Sync Control APIs to Start or Enable Automatic Synchronization

The following sections describe how to start/stop or enable/disable automatic synchronization from the Sync Control API:
2.2.2.3.1 C/C++ Sync Control APIs to Start or Enable Automatic Synchronization
The following sections describe the Sync Control APIs for C/C++ applications.
To start or stop the Sync Agent, use the following APIs:
To enable or disable the Sync Agent, use the following APIs:
The olGetSyncOptions and olSetSyncOptions methods take a pointer to the olSyncOpt structure as a parameter. The olSyncOpt structure contains the bDisable boolean, which is true if the Sync Agent is disabled.
To enable the Sync Agent, perform the following:
To disable the Sync Agent, perform the following:
Use olGetSyncOptions method to retrieve the current value of the bDisable boolean.
2.2.2.3.2 C# Sync Control APIs to Start or Enable Automatic Synchronization
The following BGSyncControl class has the following methods:
All methods throw an OracleException in case of failure.
2.2.2.3.3 Java Sync Control APIs to Start or Enable Automatic Synchronization
The following BGSyncControl class has the following methods:

2.2.3 Define the Rules Under Which the Automatic Synchronization Starts

You can configure under what circumstances a synchronization should occur and then Oracle Database Lite performs the synchronization for you automatically. The circumstances under which an automatic synchronization occurs is defined within the synchronization rules, which includes the following:
The relationship between events and conditions when evaluating if an automatic synchronization occurs is as follows:
So, if an event occurs, the conditions are evaluated. If the conditions are valid, then the synchronization occurs; if the conditions are not met, then the synchronization is queued until the conditions are valid.
For example, if the event for new data inserted and the condition specified is that the network must be available, then a synchronization occurs when the network is available and there is new data.
You can define the rules for automatic synchronization within certain parts of the normal snapshot setup and platform configuration, as follows:
By default, the client will automatically synchronize after every commit if network bandwidth is detected. To change these defaults, you must modify the Client commit condition in the publication rules and the event rule for network bandwidth in either the platform or publication rules.
If after defining these rules and publishing the application, you want to modify the rules, you can do so through MDW. However, you must perform a manual synchronization. The manual synchronization restarts the automatic Sync Agent, which will then use the new rules The new settings will NOT be applied during automatic synchronization.
The following sections detail all of the rules you can configure for automatic synchronization:

2.2.3.1 Configure Publication-Level Automatic Synchronization Rules

Within the publication, you specify the rules under which the synchronization occurs for all publication items in that publication. These rules are defined when you create the publication either using MDW or programmatically with the APIs. To create this through MDW, see Section 6.5, 'Define the Rules Under Which the Automatic Synchronization Starts' ; to add publication-level automatic synchronization rules with the API, see Section 2.4.1.4, 'Define Publication-Level Automatic Synchronization Rules'.
When you are creating the publication, you can define events that will cause an automatic synchronization. Although these are defined at the publication level, they enable only the publication items within this publication that has automatic synchronization enabled.
Table 2-3 describes the publication level events for automatic synchronization. The lowest value that can be provided is 1.
Table 2-3 Automatic Events for the Publication
EventsDescription
Client commit
For Oracle Lite Mobile client only. Upon commit to the Oracle Lite database, the Oracle Lite Mobile client detects the total number of record changes in the automatic synchronization log. If the number of modifications is equal to or greater than your pre-defined number, automatic synchronization occurs. This rule is on by default and set to start an automatic synchronization if only one record is changed. You must modify this rule if you do not want the automatic synchronization to occur after every commit.
Server MGP compose
If after the MGP compose cycle, the number of modified records for a user is equal to or greater than your pre-defined number, then an automatic synchronization occurs. Thus, if there are a certain number of records contained in an Out Queue destined for a client on the server, these modifications are synchronized to the client.

Note:
If you want to modify the publication-level automatic synchronization rules after you publish the appliation, you can do so through the Mobile Manager, as follows:
  1. Click Data Synchronization.
  2. Click Repository.
  3. Click Publications.
  4. Select the publication and click Automatic Synchronization Rules.

2.2.3.2 Configure Platform-Level Automatic Synchronization Rules

Some of the rules are specific to the platform of the client, such as battery life, network bandwidth, and so on. These rules apply to all enabled publication items that exist on this particular platform, such as WinCE. You configure these rules through Mobile Manager or MDW. This section describes Mobile Manager.
The platform-level synchronization rules apply to a selected client platform and all publications that exist on that platform. You can specify both platform events and conditions using the Mobile Manager.
To assign platform-level automatic synchronization rules, perform the following in Mobile Manager:
  1. Click Data Synchronization.
  2. Click Platform Settings, which brings up a page with the list of all the platforms that support automatic synchronization.
  3. Click on the desired platform.
  4. You can modify the following for each platform:
    • Event Rules—See Section 2.2.3.2.1, 'Event Rules for Platforms'.
    • Conditions—See Section 2.2.3.2.2, 'Condition Rules for Platforms'.
    • Network settings—See Section 2.2.3.2.3, 'Network Configuration for the Client Platform'.
2.2.3.2.1 Event Rules for Platforms
Table 2-4 shows the platform events for automatic synchronization.
Table 2-4 Automatic Event Rules for the Client Platform
EventDescription
Network bandwidth
If the Mobile client detects that it is connected to a network with a pre-defined minimum bandwidth, then automatic synchronization occurs.
Battery life
If the battery life drops below a pre-defined minimum, then synchronization is automatically triggered.
AC Power
As soon as AC power is detected, then synchronization is automatically triggered.
Time
Synchronize at a specific time or time interval. You can configure an automatic synchronization to occur at a specific time each day or as an interval.
  • Select Specify Time if you want to automatically synchronize at a specific hour, such as 8:00 AM, everyday.
  • Select Specify Time Interval if you want to synchronize at a specific interval. For example, if you want to synchronize every hour, then specify how long to wait in-between synchronization attempts.

2.2.3.2.2 Condition Rules for Platforms
Table 2-5 shows the platform conditions for automatic synchronization.
Table 2-5 Automatic Condition Rules for Client Platform
ConditionDescription
Battery level
Specify the minimum battery level required in order for an automatic synchronization to start. The battery level is specified as a percentage.
Network conditions
Network quality can be specified using several properties. This condition enables you to specify a minimum value for the following network properties:
  • Minimum network bandwidth, which is measured in bits per second.
  • Maximum ping delay, which is measured in milliseconds.
  • Data priority, which is either high or regular. You can specify the priority of your data in the table row.
For example, you can define a rule where all high priority data is automatically synchronized at a specified network bandwidth. The ping delay is optional. If not specified, the ping is not calculated.

2.2.3.2.3 Network Configuration for the Client Platform
You can set proxy information for your network provider, if required for accessing the internet.
Note:
If you are not using a proxy, then you do not need to define proxy information on this page.
You could have two types of networks, as follows:
If the platform has an always-on network, then this network is always tried first for the connection. If this network is not available, then the dial-up networks are tried in the order specified. You can rearrange the order of the dial-up networks by selecting one of the networks and clicking the up or down button. For dial-up, Oracle Database Lite can automatically establish the network connection before initiating the synchronization.

2.2.4 Setting Data as High Priority in Automatic Synchronization

Oracle Database Lite adds a hidden column (MSG$PRIO) to all automatic synchronization snapshots to designate if this data has a higher priority for synchronization if conditions are right. If users need to indicate that a particular record is high priority, they can set the column value to (0). Then sync agent automatically schedules a high priority synchronization for the transaction that contains this record.

2.2.5 Enable the Server to Notify the Client to Initiate a Synchronization to Download Data

If you have designed the compose yourself—that is, you do not use the MGP—then, you can notify the client if any data exists on the server that can be downloaded to the client through enqueue notification APIs. You can also use these APIs to manage the automatic synchronization schedule for your clients.
For more information on enqueue notification APIs, see Section 2.13.3, 'Selecting How/When to Notify Clients of Composed Data'.

2.2.6 Notify Application on Completion of Automatic Synchronization Cycle

You can develop your client application to be notified when an automatic synchronization cycle occurs. The application is notified from the Sync Agent when the automatic synchronization completes as well as when a critical event occurs in the client device. For example, when the device battery runs critically low, Oracle Database Lite can notify the application.
In the client application, create a procedure that executes one of the following message APIs. When your application calls the get message API, it blocks until an event occurs within an automatic synchronization. It returns a structure that describes this event.
The following sections provide implementation details for each development language:
Automatic Synchronization Notification for C/C++ Application
Use the olGetSyncMsg method in your client application to receive the automatic synchronization notification when implementing for C/C++ applications. In order to block for the status, you need to perform the following:
  1. Start the application messaging service with the olStartSyncMsg method, providing a queue handle of type olAppMsgQ. This message starts the messaging service and returns the queue handle in the olAppMsgQ.
  2. Execute the olGetSyncMsg with the olAppMsgQ message handle and the defined olSyncMsg structure for the returned automatic synchronization information.
The following provides the method definitions:
The olGetSyncMsg method blocks until an event occurs, then the Sync Agent returns the olSyncMsg class, which you provide as an input parameter, with the information on what happened, as follows:
See 'Input Parameters for Automatic Synchronization Notification' for a description of the input parameters in the structure.
The C/C++ application performs in a different manner than the Java and C# versions in that this creates a message service with its own message queue. Thus, when finished you must perform some cleanup to ensure that the message queue handle is released. Use the olStopSyncMsg method to stop the messaging service and release the handle. This must be performed for every message queue that is opened with the olStartSyncMsg method.
If you want to force an existing olGetSyncMsg to return, use the olCancelSyncMsg from another thread in the application. This causes the olGetSyncMsg to return with the BG_ERR_APP_MSG_CANCEL error.
Automatic Synchronization Notification for C# Application
Use the GetMessage method in your client application to receive the automatic synchronization notification when implementing for C# applications, as follows:
This method blocks until an event occurs, then the Sync Agent returns the BGSyncMsg class with the information on what happened, as follows:
See 'Input Parameters for Automatic Synchronization Notification' for a description of the input parameters in the class.
Automatic Synchronization Notification for Java Application
Use the getMessage method in your client application to receive the automatic synchronization notification when implementing for Java applications, as follows:
This method blocks until an event occurs, then the Sync Agent returns the BGSyncMsg class with the information on what happened, as follows:
See 'Input Parameters for Automatic Synchronization Notification' for a description of the input parameters in the class.
Input Parameters for Automatic Synchronization Notification
The input parameters in the input structure/class are as follows:
Table 2-6 The Sync Message Variables
VariableDescription
Event type
The event can be of three types, each of which indicate the level of severity of this notification:
  • INFO
  • ERROR
  • WARNING
Event identifier for INFO types:
The INFO event identifer describes what occurred, as follows:
  • SYNC_STARTED: The Sync Agent has started the synchronization task.
  • SYNC_SUCCEEDED: Data synchronization completed successfully.
  • APPLY_STARTED: The Sync Agent has started the apply task.
  • APPLY_SUCCEEDED: The apply phase completed successfully.
  • SVR_NOTIF: The Sync Agent has received a server notification. The message contains information about the server notification, such as publication name, number of modified records and the record priority (high priority or normal).
  • NETWORK_CHANGED: Device has moved into a different network
  • AGENT_STARTED: The Sync Agent started.
  • AGENT_STOPPED: The Sync Agent stopped.
Event identifier for the WARNING type:
The WARNING event identifier describes in more detail what occurred, as follows:
  • BATTERY_LOW: Device's battery is running low
  • MEMORY_LOW: Device's memory is running low
Event identifier for the ERROR type:
The ERROR event identifier describes in more detail what occurred, as follows:
  • APPLY_FAILED: The apply failed. In this case, 'message' contains the reason for failure.
  • SYNC_FAILED: Data synchronization failed. In this case, 'message' contains the reason for failure.
  • AGENT_ERROR: An internal error condition occurred. The message contains the actual error message. Examples would be failure to load a rule, failure to process server notification, failure to evaluate system power, and so on. In spite of this error, the Sync Agent continues to execute. Fatal errors are written to the olSyncAgent.err file.
Event Message
String message that expounds on the information provided by the event type and identifier.

2.2.7 Request Status for Automatic Synchronization Cycle

If you want to know at what stage the automatic synchronization cycle is, you can request status from the Sync Agent. In the client application, execute the get status API, which will return immediately with at what stage the automatic synchronization cycle is executing. This is different from the notification message API, which only returns when an event is completed within the synchronization cycle.
The get status API returns a structure that describes this event.
The following sections provide implementation details for each development language:
Retrieving Status for C/C++ Application
Use the olGetSyncStatus method in your C/C++ client application to retrieve status on the automatic synchronization, as follows:
The Sync Agent returns the olSyncStatus class, which you provide as an input parameter, with the information on what happened, as follows:
See 'Input Parameters for Retrieving Messages' for a description of the input parameters in the structure.
Retrieving Status for C# Application
Use the GetStatus method in your C/C++ client application to retrieve status on the automatic synchronization, as follows:
This method returns the BGSyncStatus class with the status information on the automatic synchronization, as follows:
See 'Input Parameters for Retrieving Messages' for a description of the input parameters in the structure.
Retrieving Status for Java Application
Use the getStatus method in your Java client application to retrieve status on the automatic synchronization, as follows:
This method returns the BGSyncStatus class with the status information on the automatic synchronization, as follows:
See 'Input Parameters for Retrieving Messages' for a description of the input parameters in the structure.
Input Parameters for Retrieving Messages
The input parameters in the input structure/class are as follows:
Table 2-7 Status Class Fields
FieldDescription
clientId
Username
syncState
A numeric value that denotes the current synchronization stage, such as compose, send, or receive.
syncStateStr
String describing the state, as denoted in the syncState, for the automatic synchronization.
syncProgress
A percentage that indicates the current progress for the automatic synchronization.
lastSyncError
If an error occurred in the last synchronization, this is the error code. If no error, this value is zero.
lastSyncType
The priority of the data for the last synchronization. If 1, then high priority data; if 0, then regular priority data was synchronized.
lastSyncTime
Time of the last automatic synchronization.
applyState
Code that indicates the state for the apply phase.
applyStateStr
String describing the state for the apply phase, as denoted in the applyState variable.
applyProgress
A percentage that indicates the current progress for the apply phase.
lastApplyError
If an error occurred in the last apply phase, this is the error code. If no error, this value is zero.
lastApplyTime
Time of the last apply phase.
networkName
The network name assigned to this network.
networkSpeed
Current bandwidth of the network.
batteryPower
Current battery power percentage.

2.3 What is The Process for Setting Up a User For Synchronization?

Before you can perform the synchronization, the publication must be created, the user created and granted access to the publication, and optionally, the publication packaged up with an application and published to the Mobile Server. This is referred to as the publish and subscribe model, which can be implemented in one of two ways:
Once created and subscribed, the user can be synchronized, as follows:
On the back-end of the synchronization process, you have the option to customize how the apply and compose phase are executed. See Section 2.6, 'Customize the Compose Phase Using MyCompose'.

2.3.1Creating a Snapshot Definition Declaratively

Use the Mobile Database Workbench (MDW), a GUI based tool of Oracle Database Lite—described fully in Chapter 6, 'Using Mobile Database Workbench to Create Publications'—to create snapshots declaratively. The convenience of a graphical tool is a safer and less error prone technique for developers to create a Mobile application. Before actual application programming begins, the following steps must be executed:
  1. Verify that the base tables exist on the server database; if not, create the base table.
  2. Use MDW to define an application and the snapshot with the necessary publicatino and its publication items. See Chapter 6, 'Using Mobile Database Workbench to Create Publications' for details.
  3. Use the Packaging Wizard to publish the application to the Mobile Server. This creates the publication items associated with the application. See Chapter 7, 'Using the Packaging Wizard' for details.
  4. Use the Mobile Manager to create a subscription for a given user.
  5. Install the application on the development machine.
  6. If using manual synchronization, then initiate synchronization for the Mobile client with the Mobile Server to create the client-side snapshots. In addition, for Oracle Lite Mobile client, create the Oracle Lite database automatically.

2.3.1.1Manage Snapshots

The Mobile Server administrator can manage a snapshot, which is a full set or a subset of rows of a table or view. Create the snapshot by executing a SQL query against the base table. Snapshots are either read-only or updatable.
The following sections describes how to manage snapshots using MDW:
2.3.1.1.1Read-only Snapshots
Read-only snapshots are used for querying purposes only. The data is downloaded from the Oracle server to the client; no data on the client is ever uploaded to the server. Any data added on the client in a read-only snapshot can be lost, since it is never uploaded to the server. Changes made to the master table in the back-end Oracle database server are replicated to the Mobile client. See Section 6.9.2, 'Publication Item Tab Associates Publication Items With the Publication' for instructions on how to define the publication item as read-only.
Note:
A subscription created as complete refresh and read-only is light weight; thus, to keep the subscription light weight, the primary keys are not included in the replication. If you want to include primary keys, then create them with the createPublicationItemIndex API.
Also, because read-only does not upload any data from the client, there are no conflicts. Thus, when specified within MDW, you will only be able to select Custom for conflict resolution.
2.3.1.1.2Updatable Snapshots
When you define a snapshot as updatable, then the data propagated within a synchronization is bi-directional. That is, any modifications made on the client are uploaded to the server; any modifications made on the back-end Oracle server are downloaded to the client. See Section 6.9.2, 'Publication Item Tab Associates Publication Items With the Publication' for instructions on how to define the publication item as updatable.
A snapshot can only be updated when all the base tables that the snapshot is based on have a primary key or virtual primary key. If the base tables do not have a primary key, a snapshot cannot be updated and becomes read-only. Table 2-8 shows each refresh method type and whether it is updatable or read-only depending on primary key or virtual primary key:
Table 2-8 Which Refresh Methods Can Be Updatable or Read-Only

FastCompleteQueue-Based
Table Uses a Primary Key
Updatable or Read-Only
Updatable or Read-Only
Updatable or Read-Only
Table Uses a Virtual Primary Key
Updatable or Read-Only
Updatable or Read-Only
Updatable or Read-Only
No Primary Key or Virtual Primary Key Used
Not applicable since all Fast Refresh tables use a primary or virtual primary key.
Read-Only
Read-Only

2.3.1.1.3Refresh a Snapshot
Your snapshot definition determines whether an updatable snapshot uses the complete or fast refresh method.
See Section 6.4, 'Create a Publication Item' and Section 2.9, 'Understanding Your Refresh Options'
2.3.1.1.4Snapshot Template Variables
Snapshots are application-based. In some cases, you may quantify the data that your application downloads for each user by specifying all of the returned data match a predicate. You can accomplish this by using snapshot templates.
A snapshot template is an SQL query that contains data subsetting parameters. A data subsetting parameter is a colon (:), followed by an identifier name, as follows:
Note:
If the subsetting parameter is on a CHAR column of a specified length, then you should either preset all characters to spaces before setting the value or pad for the length of the column with spaces after setting the parameter.
When the Mobile client creates snapshots on the client machine, the Mobile Server replaces the snapshot variables with user-specific values. By specifying different values for different users, you can control the data returned by the query for each user.
You can use MDW to specify a snapshot template variable in the same way that you create a snapshot definition for any platform.
Data subsetting parameters are bind variables and so should not be enclosed in quotation marks ('). If you want to specify a string as the value of the data subsetting parameter, then the string contains single quotation marks. You can specify the values for the template variables within the Mobile Manager.
The following examples specify a different value for every user. By specifying a different value for every user, the administrator controls the behavior and output of the snapshot template.
You define this select statement in your publication item. See Section 6.4.1, 'Create SQL Statement for Publication Item' for instructions. Then, modify the user in the Mobile Manager to add the value for :dno. Then, when the user synchronizes, the value defined for the user is replaced in the select script. See Section 5.5, 'Managing Application Parameter Input (Data Subsetting)' in the Oracle Database Lite Administration and Deployment Guide for information on how to define the value of the variable. This value can only be defined after the application is published and the user is associated with it.
Table 2-9 provides a sample set of snapshot query values specified for separate users.
Table 2-9 Snapshot Query Values for Separate Users
UserValueSnapshot Query
John
10
select * from emp where deptno = 10
Jane
20
select * from emp where deptno = 20

Table 2-10 provides another sample snapshot query value.
Table 2-10 Snapshot Query Value for User Names
UserValueSnapshot Query
John
'KING'
select * from emp where ename = 'KING'

2.3.2Creating the Snapshot Definition Programmatically

You can use the Resource Manager or Consolidator Manager APIs to programmatically create the publication items on the Mobile Server. Create publication items from views and customize code to construct snapshots.
Note:
The Consolidator Manager API can only create a publication, which cannot be packaged with an application. In addition, a publication created with the Consolidator Manager API cannot be packaged with an application. See Section 2.4, 'Creating Publications Using Oracle Database Lite APIs' for information on the Consolidator Manager API. Use the Resource Manager APIs to create the publication, package it with an application, and publish it to the Mobile Server. See the oracle.mobile.admin.ResourceManager Javadoc in the Oracle Database Lite API Specification, which you can link to off the ORACLE_HOME/Mobile/index.htm page.
The base tables must exist before the Consolidator Manager API can be invoked. The following steps are required to create a a subscription:
The details of how to create a publication are documented in Chapter 6, 'Using Mobile Database Workbench to Create Publications'. Anything that you can do with the MDW tool, you can also perform programmatically using the Consolidator Manager API. Refer to the Javadoc for the syntax.

2.4Creating Publications Using Oracle Database Lite APIs

Mobile Server uses a publish and subscribe model to centrally manage data distribution between Oracle database servers and Mobile clients. Basic functions, such as creating publication items and publications, can be implemented easily using the Mobile Development Workspace (MDW). See Chapter 6, 'Using Mobile Database Workbench to Create Publications' for more information.
These functions can also be performed using the Consolidator Manager or Resource Manager APIs by writing Java programs to customize the functions as needed. Some of the advanced functionality can only be enabled programmatically using the Consolidator Manager or Resource Manager APIs.
The publish and subscribe model can be implemented one of two ways:

2.4.1 Defining a Publication With Java Consolidator Manager APIs

While we recommend that you use MDW (see Chapter 6, 'Using Mobile Database Workbench to Create Publications') for creating your publications, you can also create them, including the publication items and the user, with the Consolidator Manager API. Choose this option if you are performing more advanced techniques with your publications.
After creating the database tables in the back-end database, create the Resource Manager and Consolidator Manager objects to facilitate the creation of your publication:
The order of creating the elements in the publication is the same as if you were using MDW. You must create a publication first and then add the publication items and other elements to it. Once the publications are created, subscribe users to them. See the Javadoc for full details on each method. See Chapter 6, 'Using Mobile Database Workbench to Create Publications' for more details on the order of creating each element.
Note:
The following sections use the sample11.java sample to demonstrate the Resource Manager and Consolidator Manager methods used to create the publication and the users for the publication. The full source code for this sample can be found in the following directories:
On UNIX: <ORACLE_HOME>/mobile/server/samples
On Windows: <ORACLE_HOME>MobileServerSamples
Note:
To call the Publish and Subscribe methods, the following JAR files must be specified in your CLASSPATH.

2.4.1.1 Create the Mobile Server User

Use the createUser method of the MobileResourceManager object to create the user for the publication.
  1. Create the MobileResourceManager object. A connection is opened to the Mobile Server. Provide the schema name, password, and JDBC URL for the database the contains the schema (the repository).
  2. Create one or more users with the createUser method. Provide the user name, password, the user's real name, and privilege, which can be one of the one of the following: 'O' for publishing an application, 'U' for connecting to Web-to-Go as user, or 'A' for administrating the Web-to-Go. If NULL, no privilege is assigned.
    Note:
    Always request a drop user before you execute a create, in case this user already exists.
  3. If you want members to be created for this device, perform the tasks described in Section 2.4.1.1.2, 'Create Member Users for Sharing Application and Data on Device'.
  4. Commit the transaction, which was opened when you created the MobileResourceManager object, and close the connection.
Note:
If you do not want to create any users, you do not need to create the MobileResourceManager object.
2.4.1.1.1Change Password
You can change passwords for Mobile Server users with the setPassword method, which has the following syntax:
Note:
Both username and passwords are limited to a maximum of 28 characters.
Execute the setPassword method before you commit the transaction and release the connection. The following example changes the password for the user MOBILE:
2.4.1.1.2 Create Member Users for Sharing Application and Data on Device
The Member user enables you to define multiple users on a device using the same application and data. Each member is created and associated with a user. After the user grants access to the member to its data, each member can log on with his/her username and password and can access the data as defined by the user. This enables multiple people, such as shift workers, to use the same device, without needing to use the same username and password or the same access privileges. For more details on how the member is used and instructions on how to create the member with the Mobile Manager, see Section 5.5.3, 'Adding New Members and Associating Them With Users' in the Oracle Database Lite Administration and Deployment Guide.
To create a member user and associate it with a user with the APIs, perform the following:
  1. After you have created the MobileResourceManager object described in Section 2.4.1.1, 'Create the Mobile Server User', you can issue the createUser method to create a member. Provide member name, password, full name and 'M' as the privilege parameter to designate the creation of a member.
  2. After you have created all members, associate one or more members with the user by executing the MobileResourceManager.associateMemberToUser method. Provide the existing user name and member name or a Vector containing all member names.
After you complete creation of all users and members, end the transaction and close the connection.
The following detail other methods that support member users:

2.4.1.2 Create Publications

A subscription is an association of publications and the users who access the information gathered by the publications. Create any publication through the ConsolidatorManager object.
  1. Create the ConsolidatorManager object.
  2. Connect to the database using the openConnection method. Provide the schema name, password, and JDBC URL for the database the contains the schema.
  3. Create the publication with the createPublication method, which creates an empty publication. An example of the createPublication method syntax is as follows:
    The createPublication method can have some of the following input parameters:
    • name—A character string specifying the new publication name.
    • db_inst—Null, unless you are using a registered database for application data, as described in Section 3.2, 'Register a Remote Oracle Database for Application Data'. If using a registered database, provide the application database name in this field.
    • client_storage_type—An integer specifying the client storage type for all publication items in the new publication. If you are defining a publication exclusively for a SQLite Mobile client, you must specify the Consolidator.SQLITE_CREATOR_ID as the storage type.
      Other values are Consolidator.DFLT_CREATOR_ID and Consolidator.OKPI_CREATOR_ID.
    • client_name_template—A template for publication item instance names on client devices. This parameter contains the following predefined values:
      • %s—Default.
      • DATABASE.%s—Causes all publication items to be instantiated inside an OKAPI database with the name DATABASE.
      • SFT-EE_%s—Must be used for Satellite Forms-based applications.
    • enforce_ri—Reserved for future use. Use null or an empty string.
    • dev_types_flg—Specifies which device types or platforms the publication supports. The default flag is set to Consolidator.DEV_FLG_GEN, which includes all device platforms. If a publication is for more than one platform, use the sum of the platform flags.
      Available platforms are as follows:
      • SQLite DB: 'SQLite LINUX', 'SQLite WCE', 'SQLite WIN32', 'SQLiteJava'
      • Oracle Lite DB: 'EPOC', 'LINUX', 'WCE', 'WIN32', 'WTG'
      To retrieve the device flag for a platform, call the getPlatformDevFlg function. The syntax for this function is as follows:
Note:
Always request a drop publication before you execute a create, in case this publication already exists.
Note:
Special characters including spaces are supported in publication names. The publication name is case-sensitive.

2.4.1.3 Create Publication Items

An empty publication does not have anything that is helpful until a publication item is added to it. Thus, after creating the publication, it is necessary to create the publication item, which defines the snapshot of the base tables that is downloaded for your user.
Note:
You can create a publication using MDW. To see more details on publications and publication items, refer to Section 6.4, 'Create a Publication Item'.
When you create each publication item, you can specify the following:
Symmetricds alternative
Note:
For full details on the method parameters, see the Javadoc.
Publication item names are limited to twenty-six characters and must be unique across all publications. The publication item name is case-sensitive. The following examples create a publication item named P_SAMPLE11-M.
Note:
Always drop the publication item in case an item with the same name already exists.
The following example uses the createPublicationItem method, which creates a manual synchronization publication item P_SAMPLE11-M based on the ORD_MASTER database table with fast refresh. Use the addPublicationItem method to add this publication item to the publication.
Note:
For full details on the method parameters, see the Javadoc.
When you create a publication item that uses automatic synchronization through the createPublicationItem method, you can also define the following:
The following example uses the automatic synchronization version of createPublicationItem method, which uses the PubItemProps class to define all publication item definitions, including automatic synchronization, as follows:
2.4.1.3.1Defining Publication Items for Updatable Multi-Table Views
Publication items can be defined for both tables and views. When publishing updatable multi-table views, the following restrictions apply:

2.4.1.4 Define Publication-Level Automatic Synchronization Rules

Once the publication is created, you can create and add automatic synchronization rules that apply to all enabled publication items in this publication. Perform the following to add a rule to a publication:
  1. The rule is made up of a rule name and a String that contains the rule definition. The rules can be created using the Rules classes and RuleInfo objects.
    1. Define the rule and convert it to a String using the RuleInfo object and the setSyncRuleParams method.
      There are RuleInfo objects for all of the main automatic synchronization rules. So, in order to specify a rule, you obtain the appropriate RuleInfo object from the Rules class and then define the variable. Table 2-11, 'Automatic Synchronization Rule Info Objects' describe the different types of rules you can specify for triggering automatic synchronization:
      Note:
      See the Javadoc for examples and the parameters that you need to set for each rule.
      Table 2-11 Automatic Synchronization Rule Info Objects
      Rule Info ObjectDescription
      RULE_MAX_DB_REC_ri
      For Oracle Lite Mobile clients only. Synchronize if the client database for all publication items on the client contains more than NREC modified records, where you specify the NREC of modifed records in the client database to trigger an automatic synchronization.
      RULE_NOTIFY_MAX_PUB_REC_ri
      Synchronize if the Out Queue contains more than NREC modified records, where you specify the NREC of modifed records in the server database to trigger an automatic synchronization.
      RULE_MAX_PI_REC_ri
      Client automatically synchronizes if the number of modified records for a publication item is greater than NREC.
      RULE_HIGH_BANDWIDTH_ri
      Synchronize when the network bandwidth is greater than <number> bits/second. Where <number> is an integer that indicates the bandwidth bits/seconds. When the bandwidth is at this value, the synchronization occurs.
      RULE_LOW_PWR_ri
      Synchronize when the battery level drops to <number>%, where <number> is a percentage. Often you may wish to synchronize before you lose battery power.
      RULE_AC_PWR_ri
      Synchronize when the AC power is detected; that is, when the device is plugged in.
      RULE_MIN_MEM_ri
      Specify the minimum battery level required in order for an automatic synchronization to start. The battery level is specified as a percentage.
      RULE_NET_PRIORITY_ri
      Network conditions can be specified using the following properties: data priority, ping delay and network bandwidth.
      RULE_MIN_PWR_ri
      If the battery life drops below a pre-defined minimum, then synchronization is automatically triggered.
      NET_CONFIG_ri
      Configure network parameters (currently only the network specific proxy configuration is supported) The configuration rule contains a vector of hashtables with a hashtable representing properties of each individual network.
      RULE_TIME_INTERVAL_ri
      Schedule sync at a given time of day with a certain frequency (interval).
      Specify the time (PARAM_START_TIME) for an automatic synchronization to start. The format of time is standard date string: H24:MI:SS e.g. 00:00:00 or 23:59:00 The time is GMT. If not set, the synchronization starts when the Sync Agent starts and all other conditions are satisfied Set the period (PARAM_PERIOD), in seconds, to specify the frequency of scheduled synchronization events.

    2. Define a name for the rule, which should be a name not attached to any particular publication, so you can use the rule for several publications.
  2. Create the rule with the createSyncRule method, which creates the rule with the name, the String containing the rule, and a boolean on whether to replace the rule if it already exists. Once completed, then this rule can be associated with any publication.
  3. Associate the rule with the desired publication or platform using the addSyncRule method. This method can add any existing rule to a designated publication. To add to a publication, use the publication name as the first parameter, as follows:
    To add a rule to a client platform—Win32 or WINCE platform—perform the following:
    Where the platform name is a constant defined in the Consolidator class as either DEFAULT_TEMPLATE_WIN32 or DEFAULT_TEMPLATE_WCE.
You can also perform the following:
2.4.1.4.1 Retrieve All Publications Associated with a Rule
Just as you can with resources, scripts and sequences that are associated with publications, you can retrieve all publications that are associated with a rule with the getPublicationNames method. The following retrieves all publications that are associated with the rule within the ruleName variable. The object type is defined as Consolidator.RULES_OBJECT.
2.4.1.4.2 Retrieve Rule Text
You can retrieve the text of the rule using the getSyncRule and providing the rule name. This is useful if you are not sure what the rule is and need to discover the text before associating it with another publication.
2.4.1.4.3 Check if Rule is Modified
You can compare the rule within the repository with a provided string to see if the rule has been modified with the isSyncRuleModified method. A boolean value of true is returned if the provided ruleText is different from what exists in the repository.
2.4.1.4.4 Remove Rule
You can remove the association of a rule from a publication by using the removeSyncRule method. You can delete the entire rule from the repository by using the dropSyncRule method. If you drop the rule and it is still associated with one or more publications, the rule is automatically unassociated from these publications.

2.4.1.5 Data Subsetting: Defining Client Subscription Parameters for Publications

Data subsetting is the ability to create specific subsets of data and assign them to a parameter name that can be assigned to a subscribing user. When creating publication items, a parameterized Select statement can be defined. Subscription parameters must be specified at the time the publication item is created, and are used during synchronization to control the data published to a specific client.
Creating a Data Subset Example
In this sample statement, data is being retrieved from a publication named CORP_DIR1, and is subset by the variable COMPANY.
Note:
Within the select statement, the parameter name for the data subset must be prefixed with a colon, for example:COMPANY.
When a publication uses data subsetting parameters, set the parameters for each subscription to the publication. For example, in the previous example, the parameter COMPANY was used as an input variable to describe what data is returned to the client. You can set the value for this parameter with the setSubscriptionParameter method. The following example sets the subscription parameter COMPANY for the client DAVIDL in the CORP_DIR1 publication to DAVECO:
Note:
This method should only be used on publications created using the Consolidator Manager API. To create template variables, a similar technique is possible using MDW.

2.4.1.6 Create Publication Item Indexes

The Mobile Server supports automatic deployment of indexes in Oracle Database Lite on clients. The Mobile Server automatically replicates primary key indexes from the server database. The Consolidator Manager API provides calls to explicitly deploy unique, regular, and primary key indexes to clients as well.
By default, the primary key index of a table is automatically replicated from the server. You can create secondary indexes on the snapshot table for a publication item. If you do not want the primary index, you must explicitly drop it from the publication item.
If you want to create and associate other indexes on any columns in your application tables in the publication item, then use the createPublicationItemIndex method. You can drop an index from the publication item and from the snapshot table with the dropPublicationItemIndex method.
The following demonstrates how to set up indexes on the name field in our publication item P_SAMPLE11-M:
An index can contain more than one column. You can define an index with multiple columns, as follows:
Note:
All indexes created by this API can be viewed within the CV$ALL_PUBLICATIONS_INDEXES view.
2.4.1.6.1Define Client Indexes
Client-side indexes can be defined for existing publication items. There are three types of indexes that can be specified:
Note:
When an index of type 'U' or 'P' is defined on a publication item, there is no check for duplicate keys on the server. If the same constraints do not exist on the base object of the publication item, synchronization may fail with a duplicate key violation. See the Oracle Database Lite API Specification for more information.

2.4.1.7 Adding Publication Items to Publications

Once you create a publication item, you must associate it with a publication using the addPublicationItem method, as follows:
See Section 2.4.1.12, 'Modifying a Publication Item' for details on how to change the definition.
2.4.1.7.1Defining Conflict Rules
When adding a publication item to a publication, the user can specify winning rules to resolve synchronization conflicts in favor of either the client or the server. See Section 2.11, 'Resolving Conflicts with Winning Rules' for more information.
2.4.1.7.2 Using Table Weight
Table weight is an integer associated with publication items that determines in what order the transactions for all publication items within the publication are processed. For example, if three publication items exist—one that contains SQL to modify the emp table, one that modifies the dept table, and one that modifies the mgr table, then you can define the order in which the transactions associated with each publication item are executed. In our example, assign table weight of 1 to the publication item that contains the dept table, table weight of 2 to the publication item that contains the mgr table, and table weight of 3 to the publication item that contains the emp table. In doing this, you ensure that the publication item that contains the master table dept is always processed first, followed by the publication item that modifies the mgr table, and lastly by the publication item that modifies the emp table.
The insert, update, and delete client operations are executed in the following order:
  1. Client INSERT operations are executed first, from lowest to highest table weight order. This ensures that the master table entries are added before the details table entries.
  2. Client DELETE operations are executed next, from highest to lowest table weight order. Processing the delete operations ensures that the details table entries are removed before the master table entries.
  3. Client UPDATE operations are executed last, from highest to lowest table weight order.
In our example with dept, mgr, and emp tables, the execution order would be as follows:
  1. All insert operations for dept are processed.
  2. All insert operations for mgr are processed.
  3. All insert operations for emp are processed.
  4. All delete operations for emp are processed.
  5. All delete operations for mgr are processed.
  6. All delete operations for dept are processed.
  7. All update operations for emp are processed.
  8. All update operations for mgr are processed.
  9. All update operations for dept are processed.
A publication can have more than one publication item of weight 2. In this case, it does not matter which publication is executed first.
Define the order weight for publication items when you add it to the publication.

2.4.1.8 Creating Client-Side Sequences for the Downloaded Snapshot

A sequence is a database schema object that generates sequential numbers. After creating a sequence, you can use it to generate unique sequence numbers for transaction processing. These unique integers can include primary key values. If a transaction generates a sequence number, the sequence is incremented immediately whether you commit or roll back the transaction. For full details of what a sequence is and how Oracle Database Lite creates them, see Section 6.6, 'Create a Sequence'.
If you do not want to use MDW to create a sequence, then use the Consolidator Manager API to manage the sequences with methods that create/drop a sequence, add/remove a sequence from a publication, modify a sequence, and advance a sequence window for each user. All of the same behavior exists for the Consolidator Manager APIs as are available through MDW.
Once you have created the sequence, you place it into the publication with the publication item to which it applies.
Note:
If the sequences do not work properly, check your parent publications. All parent publications must have at least one publication item. If you do not have any publication items for the parent publication, then create a dummy publication item within the parent.
See the Oracle Database Lite API Specification for a complete listing of the APIs to define and administrate sequences.

2.4.1.9Subscribing Users to a Publication

Subscribe the users to a publication using the createSubscription function. The following creates a subscription between the S11U1 user and the T_SAMPLE11 publication:

2.4.1.10 Instantiate the Subscription

After you subscribe a user to a publication, you complete the subscription process by instantiating the subscription, which associates the user with the publication in the back-end database. The next time that the user synchronizes, the data snapshot from the publication is provided to the user.

Note:
If you need to set subscription parameters for data subsetting, this must be completed before instantiating the subscription. See Section 2.4.1.5, 'Data Subsetting: Defining Client Subscription Parameters for Publications' for more information.

2.4.1.11 Bringing the Data From the Subscription Down to the Client

You can perform the synchronization and bring down the data from the subscription you just created. The client executes SQL queries against the client ODB to retrieve any information. This subscription is not associated with any application, as it was created using the low-level Consolidator Manager APIs.

2.4.1.12Modifying a Publication Item

You can add additional columns to existing publication items. These new columns are pushed to all subscribing clients the next time they synchronize. This is accomplished through a complete refresh of all changed publication items.
To change the definition, use one of the following:

2.4.1.13Callback Customization for DML Operations

Once a publication item has been created, a user can use the Consolidator Manager API to specify a customized PL/SQL procedure that is stored in the Mobile Server repository to be called in place of all DML operations for that publication item. There can be only one Mobile DML procedure for each publication item. The procedure should be created as follows:
Note:
You can use thegenerateMobileDMLProcedure to generate the procedure specification for a given publication item. This specification can be used as a starting point in creating your own custom DML handling logic in a PL/SQL procedure. See the Oracle Database Lite API Specification for more information.
The parameters for customizing a DML operation are listed in Table 2-12:
Table 2-12 Mobile DML Operation Parameters
ParameterDescription
DML
DML operation for each row. Values can be 'D' for DELETE, 'I' for INSERT, or 'U' for UPDATE.
COL1 .. COLn
List of columns defined in the publication item. The column names must be specified in the same order that they appear n the publication item query. If the publication item was created with 'SELECT * FROM exp', the column order must be the same as they appear in the table 'exp'.
PK1 .. PKn
List of primary key columns. The column names must be specified in the same order that they appear in the base or parent table.

The following defines a DML procedure for publication item exp:
Assuming A is the primary key column for exp, then your DML procedure would have the following signature:
During runtime, this procedure is invoked with 'I', 'U', or 'D' as the DML type. For insert and delete operations, A_OLD will be null. In the case of updates, it will be set to the primary key of the row that is being updated. Once the PL/SQL procedure is defined, it can be attached to the publication item through the following API call:
where exp is the publication item name and PUB_exp is the publication name.
Refer to the Oracle Database Lite API Specification for more information.
2.4.1.13.1DML Procedure Example
The following piece of PL/SQL code defines an actual DML procedure for a publication item in one of the sample publications. As described below, the ORD_MASTER table. The query was defined as:
ord_master Table
Code Example
The API call to add this DML procedure is as follows:
where T_SAMPLE11 is the publication name and P_SAMPLE11-M is the publication item name.

2.4.1.14Restricting Predicate

A restricting predicate can be assigned to a publication item as it is added to a publication.The predicate is used to limit data downloaded to the client. The parameter, which is for advanced use, can be null. For using a restricting predicate, see Section 1.2.10 'Priority-Based Replication' in the Oracle Database Lite Troubleshooting and Tuning Guide.

2.5Client Device Database DDL Operations

The first time a client synchronizes, Oracle Database Lite automatically creates the snapshot tables for the user subscriptions on the Mobile client. For the Oracle Lite Mobile client, the Oracle Lite database is also created. If you would like to execute additional DDL statements on the database, then add the DDL statements as part of your publication. Oracle Database Lite executes these DDL statements when the user synchronizes.
This is typically used for adding constraints and check values.
For example, you can add a foreign key constraint to a publication item. In this instance, if the Oracle Database Lite created snapshots S1 and S2 during the initial synchronization, where the definition of S1 and S2 are as follows:
If you would like to create a foreign key constraint between C3 on S2 and the primary key of S1 , then add the following DDL statement to your publication item:
Then, Oracle Database Lite executes any DDL statements after the snapshot creation or, if the snapshot has already been created, after the next synchronization.
See the Oracle Database Lite API Specification for more information on these APIs.

2.6 Customize the Compose Phase Using MyCompose

The compose phase takes a query for one or more server-side base tables and puts the generated DML operations for the publication item into the Out Queue to be downloaded into the client. The Consolidator Manager manages all DML operations using the physical DML logs on the server-side base tables. This can be resource intensive if the DML operations are complex—for example, if there are complex.

2.6.2.1 Implement the needCompose Method

The needCompose method to identifies a client that has changes to a specific publication item that is to be downloaded. Use this method as a way to trigger the doCompose method.
The parameters for the needCompose method are listed in Table 2-13:
Table 2-13 needCompose Parameters
ParameterDefinition
conn
Database connection to the Main Mobile Server repository.
rmt_conn
Database connection to the remote database for application. Set to NULL if the base tables are on the Main database where the Mobile repository exists. For details on remote databases, see Section 3.2, 'Register a Remote Oracle Database for Application Data'.
clientid
Specifies the client that is being composed.

The following example examines a client base table for changes—in this case, the presence of dirty records. If there are changes, then the method returns MyCompose.YES, which triggers the doCompose method.
This sample uses subsidiary methods discussed in Section 2.6.3, 'Use Get Methods to Retrieve Information You Need in the User-Defined Compose Class' to check if the publication item has any tables with changes that need to be sent to the client. In this example, the base tables are retrieved, then checked for changed, or dirty, records. If the result of that test is true, a value of Yes is returned, which triggers the call for the doCompose method.

2.6.2.2 Implement the doCompose Method

The doCompose method populates the DML log table for a specific publication item, which is subscribed to by a client.
The parameters for the doCompose method are listed in Table 2-14:
Table 2-14 doCompose Parameters
ParameterDefinition
conn
Database connection to the Main Mobile Server repository.
rmt_conn
Database connection to the remote database for application. Set to NULL if the base tables are on the Main database where the Mobile repository exists. For details on remote databases, see Section 3.2, 'Register a Remote Oracle Database for Application Data'.
clientid
Specifies the client that is being composed.

The following example contains a publication item with only one base table where a DML (Insert, Update, or Delete) operation on the base table is performed on the publication item. This method is called for each client subscribed to the publication item.
This code uses subsidiary methods discussed in Section 2.6.3, 'Use Get Methods to Retrieve Information You Need in the User-Defined Compose Class' to create a SQL statement. The MyCompose method retrieves the base table, the base table primary key, the base table DML log name and the publication item DML table name using the appropriate get methods. You can use the table names and other information returned by these methods to create a dynamic SQL statement, which performs an insert into the publication item DML table of the contents of the base table primary key and DML operation from the base table DML log.

2.6.2.3 Implement the init Method

The init method provides the framework for user-created compose preparation processes. The init method is called once for all clients prior to the individual client compose phase. The default implementation has no effect.
The parameter for the init method is described in Table 2-15:
Table 2-15 init Parameters
ParameterDefinition
conn
Database connection to the Main Mobile Server repository.

2.6.2.4 Implement the destroy Method

The destroy method provides the framework for compose cleanup processes. The destroy method is called once for all clients after to the individual client compose phase. The default implementation has no effect.
The parameter for the destroy method is described in Table 2-16:
Table 2-16 destroy Parameters
ParameterDefinition
conn
Database connection to the Main Mobile Server repository.

2.6.3Use Get Methods to Retrieve Information You Need in the User-Defined Compose Class

The following methods return information for use by primary MyCompose methods.

2.6.3.1 Retrieve the Publication Name With the getPublication Method

The getPublication method returns the name of the publication.

2.6.3.2 Retrieve the Publication Item Name With the getPublicationItem Method

The getPublicationItem method returns the publication item name.

2.6.3.3 Retrieve the DML Table Name With the getPubItemDMLTableName Method

The getPubItemDMLTableName method returns the name of the DML table or DML table view, including schema name, which the doCompose or init methods are supposed to insert into.
You can embed the returned value into dynamic SQL statements. The table or view structure is as follows:
The parameters for getPubItemDMLTableName are listed in Table 2-17:
Table 2-17 getPubItemDMLTableName View Structure Parameters
ParameterDefinition
PubItemPK
The value returned by getPubItemPK()
DMLTYPE$$
This can have the values 'I' for insert, 'D' for delete, or 'U' for Update.

2.6.3.4 Retrieve the Primary Key With the getPubItemPK Method

Returns the primary key for the listed publication in comma separated format in the form of <col1>,<col2>,<col3>.

2.6.3.5 Retrieve All Base Tables With the getBaseTables Method

Returns all the base tables for the publication item in an array of two-string arrays. Each two-string array contains the base table schema and name. The parent table is always the first base table returned, in other words, baseTables[0].

2.6.3.6 Retrieve the Primary Key With the getBaseTablePK Method

Returns the primary key for the listed base table in comma separated format, in the form of <col1>, col2>,<col3>.
The parameters for getBaseTablePK are listed in Table 2-18:
Table 2-18 getBaseTablePK Parameters
ParameterDefinition
owner
The schema name of the base table owner.
baseTable
The base table name.

2.6.3.7 Discover If Base Table Has Changed With the baseTableDirty Method

Returns the a boolean value for whether or not the base table has changes to be synchronized.
The parameters for baseTableDirty are listed in Table 2-19:
Table 2-19 baseTableDirty Parameters
ParameterDefinition
owner
The schema name of the base table.
store
The base table name.

2.6.3.8 Retrieve the Name for DML Log Table With the getBaseTableDMLLogName Method

Returns the name for the physical DML log table or DML log table view for a base table.
The parameters for getBaseTableDMLLogName are listed in Table 2-20:
Table 2-20 getBaseTableDMLLogName Parameters
ParameterDefinition
owner
The schema name of the base table owner.
baseTable
The base table name.

You can embed the returned value into dynamic SQL statements. There may be multiple physical logs if the publication item has multiple base tables. The parent base table physical primary key corresponds to the primary key of the publication item. The structure of the log is as follows:
The parameters for getBaseTableDMLLogName view structure are listed in Table 2-21:
Table 2-21 getBaseTableDMLLogName View Structure Parameters
ParameterDefinition
Base Table PK
The primary key of the parent base table.
DMLTYPE$$
This can have the values 'I' for insert, 'D' for delete, or 'U' for Update.

2.6.3.9 Retrieve View of the Map Table With the getMapView Method

Returns a view of the map table which can be used in a dynamic SQL statement and contains a primary key list for each client device. The view can be an inline view.
The structure of the map table view is as follows:
The parameters of the map table view are listed in Table 2-22:
Table 2-22 getMapView View Structure Parameters
ParameterDefinition
CLID$$CS
This is the client ID column.
Base Table PK
The primary key columns of the publication item.
DMLTYPE$$
This can have the values 'I' for insert, 'D' for delete, or 'U' for Update.

2.6.4Register the User-Defined Class With the Publication Item

Once you have created your sub-class, it must be registered with a publication item. The Consolidator Manager API now has two methods registerMyCompose and deRegisterMyCompose to permit adding and removing the sub-class from a publication item.

2.7 Customize What Occurs Before and After Synchronization Phases

You can customize what happens before and after certain synchronization processes by creating one or more PL/SQL packages. The following sections detail the different options you have for customization:

2.7.1 Customize What Occurs Before and After Every Phase of Each Synchronization

You can customize the MGP phase of the synchronization process through a set of predefined callback methods that add functionality to be executed before or after certain phases of the synchronization process. These callback methods are defined in the CUSTOMIZE PL/SQL package. Note that these callback methods are called before or after the defined phase for every publication item.
Note:
If you want to customize certain activity for only a specific publication item, see Section 2.7.2, 'Customize What Occurs Before and After Compose/Apply Phases for a Single Publication Item' for more information.
Manually create this package in the Mobile Server repository and any remote database that has publication items that are relevant for the customization. For more information on remote databases, see Section 3.2, 'Register a Remote Oracle Database for Application Data'.
The methods and their respective calling sequence are as follows:
Note:
Some of the procedures in the package are invoked for each client defined in your Mobile Server, such as the BeforeClientCompose and AfterClientCompose methods.

2.7.1.1 NullSync

The NullSync procedure is called at the beginning of every synchronization session. It can be used to determine whether or not a particular user is uploading data.

2.7.1.2 BeforeProcessApply

The BeforeProcessApply procedure is called before the entire apply phase of the MGP process.

2.7.1.3 AfterProcessApply

The AfterProcessApply procedure is called after the entire apply phase of the MGP process.

2.7.1.4 BeforeProcessCompose

The BeforeProcessCompose procedure is called before the entire compose phase of the MGP process.

2.7.1.5 AfterProcessCompose

The AfterProcessCompose procedure is called after the entire compose phase of the MGP process.

2.7.1.6 BeforeProcessLogs

The BeforeProcessLogs procedure is called before the database log tables (CLG$) are generated for the compose phase of the MGP process. This log tables capture changes for MGP and should not be confused with the trace logs.

2.7.1.7 AfterProcessLogs

The AfterProcessLogs procedure is called after the database log tables (CLG$) are generated for the compose phase of the MGP process. This log tables capture changes for MGP and should not be confused with the trace logs.

2.7.1.8 BeforeClientCompose

The BeforeClientCompose procedure is called before each user is composed during the compose phase of the MGP process.

2.7.1.9 AfterClientCompose

The AfterClientCompose procedure is called after each user is composed during the compose phase of the MGP process.

2.7.1.10 BeforeSyncMapCleanup

For every publication item, Oracle Database Lite maintains a map table, where the MGP inserts the DML operations to be carried out on the Oracle Lite database or new records to be inserted in the case of a complete refresh. At the end of the every synchronization session, the map tables are cleaned up where all old entries are deleted.
During this cleanup, if the connection properties are not ideal, then you may have performance issues. The callbacks added before and after the map cleanup operation enable you to optimize the connection properties and revert back to old connection properties after the operation is complete.
The BeforeSyncMapCleanup procedure is called at the beginning of the cleanup; the AfterSyncMapCleanup procedure is called after cleanup is finished. You can configure the connection settings can be changed in the BeforeSyncMapCleanup and reverted back in the AfterSyncMapCleanup procedure. These methods are invoked only once during the synchronization cycle.
The properties you can manage in these callback procedures are as follows:
Note:
In the CONSOLIDATOR section of the webtogo.ora file, you may want to modify the MAX_U_COUNT parameter before the synchronization starts.
The MAX_U_COUNT parameter controls the number of SQL statements that are executed together in a SQL batch statement while performing the map cleanup. The default value for the MAX_U_COUNT parameter is 256. However, if the value is 256 during the map cleanup, then a maximum of 256 SQL statements can be executed together in a batch. Modify this parameter and restart the Mobile Server to enable a larger batch of SQL statements to be processed during map cleanup.

2.7.1.11 AfterSyncMapCleanup

The AfterSyncMapCleanup procedure is called at the end of the map cleanup. If you set any parameters in the BeforeSyncMapCleanup callback, you can set them back to the original settings in this procedure. See Section 2.7.1.10, 'BeforeSyncMapCleanup' for more information.

2.7.1.12 Example Using the Customize Package

If a developer wants to use any of the procedures listed above, perform the following:
WARNING:
It is the developer's responsibility to ensure that the package is defined properly and that the logic contained does not jeopardize the integrity of the synchronization process.

2.7.1.13 Error Handling For CUSTOMIZE Package

Errors are logged for the CUSTOMIZE package only if logging is enabled for the MGP component for the finest level for all event types. Thus, you should set the logging level to ALL and the type to ALL.
If any errors occur due to an invalid CUSTOMIZE package, they are logged only on the first MGP cycle after the Mobile Server restarts. On subsequent synchronizations, the errors are not re-written to the logs, sine the MGP does not attempt to re-execute the CUSTOMIZE package until the Mobile Server is restarted.
Note:
One requirement is that the CUSTOMIZE package can only be executed as user mobileadmin.
To locate these errors easily within the MGP_<x>.log files, search for the MGP.callBoundCallBack method. Another option is to restart the Mobile Server and check the MGP log right after the next synchronization.

2.7.2 Customize What Occurs Before and After Compose/Apply Phases for a Single Publication Item

When creating publication items, the user can define a customizable PL/SQL package that MGP calls during the Apply and Compose phase of the MGP background process for that particular publication item. To customize the compose/apply phases for a publication item, perform the following:
  1. Create the PL/SQL package with the customized before/after procedures.
  2. Register this PL/SQL package with the publication item.
    Note:
    If you are using a remote database for application data, then the callbacks must be defined on the same database as the application. See Section 3.2.4, 'Using Callbacks on Remote Databases' for more details.
Then when the publication item is being processed, MGP calls the appropriate procedures from your package.
Client data is accumulated in the In Queue prior to being processed by the MGP. Once processed by the MGP, data is accumulated in the Out Queue before being pulled to the client by Mobile Sync.
You can implement the following PL/SQL procedures to incorporate customized code into the MGP process. The clientname and tranid are passed to allow for customization at the user and transaction level.
The following is a PL/SQL example that creates a callback package and registers it when creating the P_SAMPLE3 publication item. The BeforeApply procedure disables constraints before the apply phase; the AfterApply procedure enables these constraints. Even though you are only creating procedures for the before and after apply phase of the MGP process, you still have to provide empty procedures for the other parts of the MGP process.
  1. Create PL/SQL package declaration with callback owner/schema name of SAMPLE3 and callback package name of SAMP3_PKG.
  2. Create the package definition, with all MGP process procedures with callback owner.callback package name of SAMPLE3.SAMP3_PKG. Provide a null procedure for any procedure you do not want to modify.
  3. Register the package as the callback package for the SAMPLE3 publication item. If you are creating the publication item, provide the callback schema/owner and the callback package names as input parameters to the createPublicationItem method. If you want to add the callback package to an existing publication item, do the following:
    1. Retrieve the template metadata with getTemplateItemMetaData for the publication item.
    2. Modify the attributes that specify the callback owner/schema (cbk_owner) and the callback package (cbk_name).
    3. Register the package by executing the setTemplateItemMetaData method.
Then, register the callback package with the createPublicationItem method call, as follows:
In the previous code example, the following is required:

2.8 Initiating Client Synchronization With Synchronization APIs

You can modify the client-side application to start the synchronization programmatically. This section describes how to perform the synchronization upload and download phases for the client using the Synchronization APIs.
Note:
Currently, there are no APIs to perform the upload activity on the UNIX platforms.
To execute the upload portion of synchronization from the client (see steps 1 and 2 in Figure 2-1) from within your C, C++, or Java application, perform the following steps:
  1. Initialize the synchronization parameters.
  2. Set up the transport parameters.
  3. Initialize the synchronization options and environment, such as username, password, and selective synchronization.
  4. Perform the synchronization.
The following sections demonstrates how you can perform these steps in each of the allowed programming languages:

2.8.1Starting Synchronization Upload and Download Phases With C or C++ Applications

You can initiate and monitor synchronization from a C or C++ client application. The synchronization methods for the C/C++ interface are contained in ocapi.h and ocapi.dll, which are located in the <ORACLE_HOME>Mobilebin directory. See Section 4.1, 'Synchronization APIs For C or C++ Applications' for full details.

2.8.2 Starting Synchronization Upload and Download Phases With Java Applications

You can initiate and monitor synchronization from a Java client application. See Section 4.2, 'Synchronization API for Java Applications' for more information.

2.8.3Starting Synchronization Upload and Download Phases With the ADO.NET Provider

You can initiate and monitor synchronization from an ADO.NET provider application. See Section 5.1.4, 'ADO.NET' for full details.

2.9 Understanding Your Refresh Options

The Mobile Server supports several refresh options. During a fast refresh, incremental changes are synchronized. However, during a complete refresh, all data is refreshed with current data. The refresh mode is established when you create the publication item using the createPublicationItem API call. In order to change the refresh mode, first drop the publication item and recreate it with the appropriate mode.
The following sections describe the types of refresh for your publication item that can be used to define how to synchronize:
The following sections describe the refresh options in more detail:

2.9.1Fast Refresh

Publication items are created for fast refresh by default. Under fast refresh, only incremental changes are replicated. The advantages of fast refresh are reduced overhead and increased speed when replicating data stores with large amounts of data where there are limited changes between synchronization sessions.
The Mobile Server performs a fast refresh of a view if the view meets the following criteria:
The view requires only a unique primary key for the parent table. The primary keys of other tables may be duplicated. For each base table primary key column, you must provide the Mobile Server with a hint about the column name in the view. You can accomplish this by using the primaryKeyHint method of the Consolidator Manager object. See the Javadoc in the Oracle Database Lite API Specification for more information.

2.9.2Complete Refresh for Views

A complete refresh is simply a complete execution of the snapshot query. When application synchronization performance is slow, tune the snapshot query. Complete refresh is not optimized for performance. Therefore, to improve performance, use the fast refresh option. The Consperf utility analyzes only fast refresh publication items.
Publication items can be created for complete refresh using the C refresh mode in the createPublicationItem API from the Consolidator Manager API. When this mode is specified, client data is completely refreshed with current data from the server after every sync. An administrator can force a complete refresh for an entire publication through an API call. This function forces complete refresh of a publication for a given client.
See the Javadoc in the Oracle Database Lite API Specification for more information.
The following lists what can cause a complete refresh, ordered from most likely to least likely:
  1. The same Mobile user synching from multiple devices on the same platform, or synching from different platforms when the publications are not platform specific.
  2. Republishing the application.
  3. An unexpected server apply condition, such as constraint violations, unresolved conflicts, and other database exceptions.
  4. Modifying the application, such as changing subsetting parameters or adding/altering publication items. This refresh only affects the publication items.
  5. A force refresh requested by server administrator or a force refresh requested by the client.
  6. On Oracle Lite Mobile clients, restoring an old Oracle Lite database (ODB file).
  7. Two separate applications using the same backend store.
  8. An unexpected client apply conditions, such as a moved or deleted database, database corruption, memory corruption, other general system failures.
  9. Loss of transaction integrity between the server and client. The server fails post processing after completing the download and disconnects from the client.
  10. Data transport corruptions.

2.9.3 Queue-Based Refresh

You can create your own queues. Mobile Server uploads and downloads changes from the user. Perform customized apply/compose modifications to the back-end database with your own implementation. See the Section 2.13, 'Customizing Synchronization With Your Own Queues' for more information.

2.9.4 Forced Refresh

This is actually NOT a refresh option; however, we discuss it here in order to inform you of the consequences of performing a forced refresh. Out of all the different synchronization options, the Forced Refresh synchronization architecture is probably the most misunderstood synchronization type. This option is commonly confused with the Complete Refresh synchronization. This confusion may result in tragic consequences and the loss of critical data on the client.
The Forced Refresh option is an emergency only synchronization option. This option is for when a client is so corrupt or malfunctioning so severely that the determination is made to replace the Mobile client data with a fresh copy of data from the enterprise data store. When this option is selected, any data transactions that have been made on the client are lost.
When a Forced Refresh is initiated all data on the client is removed. The client will then bring down an accurate copy of the client data from the enterprise database to start fresh with exactly what is currently stored in the enterprise data store.

2.10 Synchronizing With Database Constraints

When you have database constraints on your table, you must develop your application in a certain way to facilitate the synchronization of the data and keeping the database constraints.
Note:
For more details on database constraints in Oracle Database Lite, refer to Section 1.10, 'Database Constraints' in the Oracle Database Lite SQL Reference.
The following sections detail each constraint and what issues you must take into account:

2.10.1 Synchronization And Database Constraints

Oracle Database Lite does not keep a record of the SQL operations executed against the database; instead, only the final changes are saved and synchronized to the back-end database.
For example, if you have a client with a unique key constraint, where the following is executed against the client database:
  1. Record with primary key of one and unique field of ABC is deleted.
  2. Record with primary key of 4 and unique field of ABC is inserted.
When this is synchronized, according the Section 2.4.1.7.2, 'Using Table Weight' discussion, the insert is performed before the delete. This would add a duplicate field for ABC and cause a unique key constraint violation. In order to avoid this, you should defer all constraint checking until after all transactions are applied. See Section 2.10.3.2, 'Defer Constraint Checking Until After All Transactions Are Applied'.
Another example of how synchronization captures the end result of all SQL modifications is as follows:
  1. Insert an employee record 4 with name of Joe Judson.
  2. Update employee record 4 with address.
  3. Update employee record 4 with salary.
  4. Update employee record 4 with office number
  5. Update employee record 4 with work email address.
When synchronization occurs, all modifications are captured and only a single insert is performed on the back-end database. The insert contains the primary key, name, address, salary, office number and email address. Even though the data was created with multiple updates, the Sync Server only takes the final result and makes a single insert.
Note:
If you want these constraints to apply on the Mobile client, see Section 2.10.6, 'Generating Constraints on the Mobile Client'.

2.10.2 Primary Key is Unique

When you have multiple clients, each updating the same table, you must have a method for guaranteeing that the primary key is unique across all clients. Oracle Database Lite provides you a sequence number that you can use as the primary key, which is guaranteed to be unique across all Mobile clients.
For more information on the sequence number, see Section 2.4.1.8, 'Creating Client-Side Sequences for the Downloaded Snapshot'.

2.10.3 Foreign Key Constraints

A foreign key exists in a details table and points to a row in the master table. Thus, before a client adds a record to the details table, the master table must first exist.
For example, two tables EMP and DEPT have referential integrity constraints and are an example of a master-detail relationship. The DEPT table is the master table; the EMP table is the details table. The DeptNo field (department number) in the EMP table is a foreign key that points to the DeptNo field in the DEPT table. The DeptNo value for each employee in the EMP table must be a valid DeptNo value in the DEPT table.
When a user adds a new employee, first the employee's department must exist in the DEPT table. If it does not exist, then the user first adds the department in the DEPT table, and then adds a new employee to this department in the EMP table. The transaction first updates DEPT and then updates the EMP table. However, Oracle Database Lite does not store the sequence in which these operations were executed.
Oracle Database Lite does not keep a record of the SQL operations executed against the database; instead, only the final changes are saved and synchronized to the back-end database. For our employee example, when the user replicates with the Mobile Server, the Mobile Server could initiate the updates the EMP table first. If this occurs, then it attempts to create a new record in EMP with an invalid foreign key value for DeptNo. Oracle database detects a referential integrity violation. The Mobile Server rolls back the transaction and places the transaction data in the Mobile Server error queue. In this case, the foreign key constraint violation occurred because the operations within the transaction are performed out of their original sequence.
In order to avoid this violation, you can do one of two things:

2.10.3.1 Set Update Order for Tables With Weights

Set the order in which tables are updated on the back-end Oracle database with weights. To avoid integrity constraints with a master-details relationship, the master table must always be updated first in order to guarantee that it exists before any records are added to a details table. In our example, you must set the DEPT table with a lower weight than the EMP table to ensure that all records are added to the DEPT table first.
You define the order weight for tables when you add a publication item to the publication. For more information on weights, see Section 2.4.1.7.2, 'Using Table Weight'.

2.10.3.2 Defer Constraint Checking Until After All Transactions Are Applied

You can use a PL/SQL procedure avoid foreign key constraint violations based on out-of-sequence operations by using DEFERRABLE constraints in conjunction with the BeforeApply and AfterApply functions. DEFERRABLE constraints can be either INITIALLY IMMEDIATE or INITIALLY DEFERRED. The behavior of DEFERRABLE INITIALLY IMMEDIATE foreign key constraints is identical to regular immediate constraints. They can be applied interchangeably to applications without impacting functionality.
The Mobile Server calls the BeforeApply function before it applies client transactions to the server and calls the AfterApply function after it applies the transactions. Using the BeforeApply function, you can set constraints to DEFFERED to delay referential integrity checks. After the transaction is applied, call the AfterApply function to set constraints to IMMEDIATE. At this point, if a client transaction violates referential integrity, it is rolled back and moved into the error queues.
To prevent foreign key constraint violations using DEFERRABLE constraints:
  1. Drop all foreign key constraints and then recreate them as DEFERRABLE constraints.
  2. Bind user-defined PL/SQL procedures to publications that contain tables with referential integrity constraints.
  3. The PL/SQL procedure should set constraints to DEFERRED in the BeforeApply function and IMMEDIATE in the AfterApply function as in the following example featuring a table named SAMPLE3 and a constraint named address.14_fk:

2.10.4 Unique Key Constraint

A unique key constraint enforces uniqueness of data. However, you may have multiple clients across multiple devices updating the same table. Thus, a record may be unique on a single client, but not across all clients. Enforcing uniqueness is the customer's reponsibility and depends on the data.
How do you guarantee that the records added on separate clients are unique? You can use the sequence numbers generated on the client by Oracle Database Lite. See Section 2.4.1.8, 'Creating Client-Side Sequences for the Downloaded Snapshot' for more information.

2.10.5 Not Null Constraint

When you have a not null constraint on the client or on the server, you must ensure that this constraint is set on both sides.

2.10.6 Generating Constraints on the Mobile Client

The Primary Key, Foreign Key, Not Null and Default Value constraints can be synchronized to the Mobile client; the Unique constraints cannot be synchronized. For foreign key constraints, you decide if you want the foreign key on the Mobile client. That is, when you create a foreign key constraint on a table on the back-end server, you may or may not want this constraint to exist on the Mobile client.
In order to generate the constraints on the Mobile client, perform the following:
  1. Within the process for creating or modifying an existing publication using the APIs, invoke the assignWeights method of the ConsolidatorManager object, which does the following tasks:
    1. Calculates a weight for each of the publication items included in the publication.
    2. Creates a script that, when invoked on the client, generates the constraints on the client. This script is automatically added to the publication.
  2. On the Mobile client, perform a synchronization for the user, which brings down the snapshot and the constraint script. The script is automatically executed on the Mobile client.
Once executed on the client, all constraints on the server for this publication are also enforced on the Mobile client.

2.10.6.1 The assignWeights Method

The assignWeights method automatically calculates weights for all publication items belonging to a publication. If a new publication item is added or if there is a change in the referential relationships, the API should be called again.
The following defines the assignWeights method and its parameters:
Where:

2.11 Resolving Conflicts with Winning Rules

When you have a conflict, you need to determine which party wins. The following are the settings that you can choose for conflict resolution on the server:
The Mobile Server uses internal versioning to detect synchronization conflicts. A separate version number is maintained for each client and server record. When the client updates are applied to the server, then the Mobile Server checks the version numbers of the client against the version numbers on the server. If the version does not match, then the conflict resolves according to the defined winning rules—such as client wins or server wins, as follows:
The Mobile Server does not automatically resolve synchronization errors. Instead, the Mobile Server rolls back the corresponding transactions, and moves the transaction operations into the Mobile Server error queue. It is up to the administrator to view the error queue and determine if the correct action occurred. If not, the administrator must correct and re-execute the transaction. If it did execute correctly, then purge the transaction from the error queue.
One type of error is a synchronization conflict, which is detected in any of the following situations:
All the other errors, including nullity violations and foreign key constraint violations are synchronization errors. See Section 2.10, 'Synchronizing With Database Constraints' for more information.
On the server, synchronization errors and conflicts are placed into the error queue. For each publication item created, a separate and corresponding error queue is created. The purpose of this queue is to store transactions that fail due to unresolved conflicts. The administrator can attempt to resolve the conflicts, either by modifying the error queue data or that of the server, and then attempt to re-apply the transaction.
The administrator can resolve the errors, and then re-execute or purge transactions from the error queue using either of the following:

2.11.1 Resolving Errors and Conflicts on the Mobile Server Using the Error Queue

The error queue stores transactions that fail due to synchronization errors or unresolved conflicts. For unresolved conflicts, only the 'Server Wins' conflicts are reported. If you have set your conflict rules to 'Client Wins', then these are not reported. The administrator can do one of the following:
View the error queue through the Mobile Manager GUI, where you can see what the conflict was. You can fix the problem and reapply the data by modifying the DML operation appropriately and then re-executing. See Section 6.11.4.3 'Viewing Transactions in the Error Queue' in the Oracle Database Lite Administration and Deployment Guide for directions.

2.11.2 Viewing Client-Side Synchronization Conflicts from Automatic Synchronization

For automatic synchronization publication items only, any synchronization conflict error information that occurs on the client is stored in the CONF$<snapshot> table in the same client database as the snapshot.
Note:
The rules for the client conflict resolution is the same as what was set for the server. These tables show the errors that occured on the client during automatic synchronization.
This table has the same columns and the snapshot, plus some additional columns. Some of the additional columns in this table are reserved, but the columns that you can use to determine what course to take in resolving the conflict are as follows:

2.11.3 Customizing Synchronization Conflict Resolution Outcomes

You can customize synchronization conflict resolution by doing the following:
  1. Configure the winning rule to Client Wins.
  2. Perform only ONE of the following:
    • Create and attach one or more triggers on the back-end Oracle database base tables to execute before the INSERT, UPDATE, or DELETE DML statements. The triggers should be created to evaluate the data and handle the conflict. Triggers are created to compare old and new row values and resolve client changes as defined by you. See the Oracle Database documentation for full details on how to create and attach triggers.
    • Create a custom DML procedure. See Section 2.4.1.13, 'Callback Customization for DML Operations' for an example of how to create a custom DML procedure.
      You can use thegenerateMobileDMLProcedure to generate the procedure specification for a given publication item. This specification can be used as a starting point in creating your own custom DML handling logic in a PL/SQL procedure. You use the addMobileDMLProcedure API to attach the PL/SQL procedure to the publication item. See the Oracle Database Lite API Specification for more information.

2.12Using the Sync Discovery API to Retrieve Statistics

The Sync Discovery feature is used to request an estimate of the size of the download for a specific client, based on historical data. The following statistics are gathered to maintain the historical data:
The following sections contain methods that can be used to gather statistics:

2.12.1getDownloadInfo Method

The getDownloadInfo method returns the DownloadInfo object. The DownloadInfo object contains a set of PublicationSize objects and access methods. The PublicationSize objects carry the size information of a publication item. The method Iterator iterator() can then be used to view each PublicationSize object in the DownloadInfo object.
Note:
See the Javadoc in the Oracle Database Lite API Specification for more information.

2.12.2DownloadInfo Class Access Methods

The access methods provided by the DownloadInfo class are listed in Table 2-23:
Table 2-23 DownloadInfo Class Access Methods
MethodDefinition
iterator
Returns an Iterator object so that the user can traverse through the all the PublicationSize objects that are contained inside the DownloadInfo object.
getTotalSize
Returns the size information of all PublicationSize objects in bytes, and by extension, the size of all publication items subscribed to by that user. If no historical information is available for those publication items, the value returned is '-1'.
getPubSize
Returns the size of all publication items that belong to the publication referred to by the string pubName. If no historical information is available for those publication items, the value returned is '-1'.
getPubRecCount
Returns the number of all records of all the publication items that belong to the publication referred by the string pubName, that will be synchronization during the next synchronization.
getPubItemSize
Returns the size of a particular publication item referred by pubItemName. It follows the following rules in order.
  1. If the publication item is empty, it will return '0'.
  2. If no historical information is available for those publication items, it will return '-1'.
getPubItemRecCount
Returns the number of records of the publication item referred by pubItemName that will be synced in the next synchronization.

Note:
See the Javadoc in the Oracle Database Lite API Specification for more information.

2.12.3PublicationSize Class

The access methods provided by the PublicationSize class are listed inTable 2-24:
Table 2-24 PublicationSize Class Access Methods
ParameterDefinition
getPubName
Returns the name of the publication containing the publication item.
getPubItemName
Returns the name of the publication item referred to by the PublicationSize object.
getSize
Returns the total size of the publication item referred to by the PublicationSize object.
getNumOfRows
Returns the number of rows of the publication item that will be synchronized in the next synchronization.

Note:
See the Javadoc in the Oracle Database Lite API Specification for more information.
Sample Code

2.13Customizing Synchronization With Your Own Queues

Application developers can manage the synchronization process programmatically by using queue-based publication items. By default on the server-side, the MGP manages both the In Queues and the Out Queues by gathering all updates posted to the In Queue, applying these updates to the relevant tables, and then composing all new updates created on the server that are destined for the client and posting it to the Out Queue. This is described in Section 2.1, 'How Oracle Database Lite Synchronizes'.
However, you can bypass the MGP and provide your own solution for the apply and compose phases on the server-side for selected publication items. You may wish to bypass the MGP for the publication item if one or more of the following are true:
Figure 2-4 shows how the Sync Server invokes the UPLOAD_COMPLETE PL/SQL procedure when the client upload is complete. And before it downloads all composed updates to the client, the Sync Server invokes the DOWNLOAD_INIT PL/SQL procedure.
Figure 2-4 Queue-Based Synchronization Architecture

Description of 'Figure 2-4 Queue-Based Synchronization Architecture'
To bypass the MGP, do the following:
  1. Define your publication item as queue-based or data collection. Then, the MGP is not aware of the queues associated with this publication item. You can do this when creating the publication item either through MDW or Consolidator APIs.
  2. If queue-based, then create a package, either PL/SQL or Java, that implements the queue interface callback methods. This includes the following callback methods:
    • UPLOAD_COMPLETE to process the incoming updates from the client.
    • DOWNLOAD_INIT to complete the compose phase.
    • DOWNLOAD_COMPLETE if you have any processing to perform after the compose phase.
  3. Create the queues. The In Queue, CFM$<publication_item_name> is created by default for you. Create the Out Queue as CTM$<publication_item_name>.
The following sections describe the methods for customizing the server-side apply/compose phases-++:

2.13.1 Customizing Apply/Compose Phase of Synchronization with a Queue-Based Publication Item

Note:
The sample for queue-based publication items is located in <OLITE_HOME>/Mobile/Sdk/samples/Sync/win32/QBasedPI.
When you want to substitute your own logic for the apply/compose phase of the synchronization process, use a queue-based publication item. The following briefly gives an overview of how the process works internally when using a queue-based publication item:
Similar to the In Queue, every record in the Out Queue should be associated with it a transaction identifier (TRANID$$). The Sync Server passes the last_tran parameter to indicate the last transaction that the client has successfully applied. New Out Queue records that have not been downloaded to the client are be marked with the value of curr_tran parameter. The value of curr_tran is always greater than that of last_tran, though not sequential. The Sync Server downloads records from the Out Queues when the value of TRANID$$ is greater than last_tran. When the data is downloaded, the Sync Server calls DOWNLOAD_COMPLETE.
When you decide to use queue-based publication items, you need to do the following:
  1. Create both the In and Out Queues used in the apply and compose phases.
    • You can use the default In Queue, which is named CFM$<publication_item_name>. Alternatively, you can create the queue of this name manually. For example, if you wanted the In Queue to be a view, then you would create the In Queue manually.
    • Create the Out Queue for the compose phase as CTM$<publication_item_name>.
  2. Create the publication item and define it as a queue-based publication item. This can be done either through MDW or the Consolidator APIs.
  3. Create the PL/SQL or Java callback methods for performing the apply and compose phases. Since the MGP has nothing to do with the queues used for these phases, when you are finished processing the data, you must manage the queues by deleting any rows that have completed the necessary processing.
  4. Register the package to be used for all of the queue processing for a particular publication item.
Note:
Normally, you define the package on the Main database where the Mobile repository is located. However, if you are using a remote database for your application data, then the package must be defined on the remote database. For more information on remote databases, see Section 3.2, 'Register a Remote Oracle Database for Application Data'.

2.13.1.1Queue Creation

If a queue-based publication item is created, it will always use a queue by the name of CFM$<publication_item_name>. However, if you want to customize how the In Queue is defined—for example, by defining certain rules, making it a view or designating the location of the queue—then you can create your own In Queue. The Out Queue is never defined for you, so you must create an Out Queue named CTM$<publication_item_name> in the Mobile Server repository manually using SQL.
These queues are created based upon the publication item tables. For example, the following table ACTIVESTATEMENT has five columns, as follows:
The application stores its data in these five columns. When synchronization occurs, this data must be uploaded and downloaded. However, there is also meta-information necessary for facilitating the synchronization phases. Therefore, the Out Queue that you create contains the meta-information in the CLID$$CS, TRANID$$ and DMLTYPE$$ columns, as well as the columns from the ACTIVESTATEMENT table, as follows:
Thus, before you can create the queues, you must already know the structure of the tables for the publication item, as well as the publication item name.
The following shows the structure and creation of the queues:
In Queue
All In Queues are named CFM$<name> where name is the publication item name. It contains the application publication item table columns, as well as the fields listed in Table 2-25:
Table 2-25 In Queue Interface Creation Parameters
ParameterDescription
CLID$$CS
A unique string identifying the client.
TRANID$$
A unique number identifying the transaction.
SEQNO$$
A unique number for every DML language operation per transaction in the inqueue (CFM$) only.
DMLTYPE$$
Checks the type of DML instruction:
  • 'I' - Insert
  • 'D' - Delete
  • 'U' - Update

The following designates the structure when creating the In Queue:
Note:
You must have the parameters in the same order as shown above for the In Queue. It is different than the ordering in the Out Queue.
Out Queue
All Out Queues are named CTM$<name> where name is the publication item name. It contains the application publication item table columns, as well as the fields listed in Table 2-26:
Table 2-26 Out Queue Interface Creation Parameters
ParameterDescription
CLID$$CS
A unique string identifying the client.
TRANID$$
A unique number identifying the transaction.
DMLTYPE$$
Checks the type of DML instruction:
  • 'I' - Insert
  • 'D' - Delete
  • 'U' - Update

The following designates the structure when creating the In Queue:
Note:
You must have the parameters in the same order as shown above for the Out Queue. It is different than the ordering in the In Queue.
Another example of creating an Out Queue is in the FServ example, which uses the default In Queue of CFM$PI_FSERV_TASKS and creates the CTM$PI_FSERV_TASKS Out Queue for the PI_FSERV_TASKS publication item, as follows:
Note:
The application publication item table for the FServ example contains columns for ID, EMP_ID, CUST_ID, STAT_ID, and NOTES.
Queue Control Table
The Sync Server automatically creates a queue control table, C$INQ, and a temporary table, C$PUB_LIST_Q. You will process the information in the queue control table in the PL/SQL or Java callout methods to determine which publication items have received new transactions.
The parameters for the control table queue are listed in Table 2-27:
Table 2-27 Queue Control Table Parameters
ParameterDescription
CLID$$CS
A unique string identifying the client.
TRANID$$
A unique number identifying the transaction.
STORE
Represents the publication item name in the queue control table.

The control table has the following structure:
Temporary Table
The DOWNLOAD_INIT procedure uses the Temporary Table C$PUB_LIST_Q for determining what publication items to download in the compose phase.
The parameters for the manually created queues are listed in Table 2-28:
Table 2-28 Queue Interface Creation Parameters
ParameterDescription
NAME
The publication item name that is to be downloaded from the repository to the Out Queue.
COMP_REF
This value is 'Y' for complete refresh.

2.13.1.2 Queue-Based PL/SQL Callouts

The PL/SQL package for the queue-based publication callouts is in a package where the UPLOAD_COMPLETE, DOWNLOAD_INIT, DOWNLOAD_COMPLETE, and POPULATE_Q_REC_COUNT procedures are defined. The signatures for both callout procedures are as follows:
2.13.1.2.1 In Queue Apply Phase Processing
Within the UPLOAD_COMPLETE procedure, you should develop a method of applying all changes from the client to the correct tables in the repository. The FServ example performs the following:
  1. From the Master Table C$INQ, locates the rows for the designated client and transaction identifiers that have been marked for update.
  2. Retrieves the application publication item data and the DMLTYPE$$ from the In Queue, based on the client and transaction identifiers.
  3. Performs insert, update, or delete (determined by the value in DMLTYPE$$) for updates in the application tables in the repository.
  4. After updates are complete, delete the rows in the C$INQ and the In Queue that you just processed.
2.13.1.2.2 Out Queue Compose Phase Processing
Within the DOWNLOAD_INIT procedure, develop a method of composing all changes from the server that are destined for the client from the publication item tables in the repository. The FServ example performs the following:
  1. From the Temporary Table C$PUB_LIST_Q, discover the publication items that you should download data for the user using the client id, current and last transaction.
  2. Retrieves the application publication item data into the Out Queue. This example always uses complete refresh.
If, however, you want to perform another type of refresh than a complete refresh, such as an incremental refresh, then do the following:
  1. Read the value of COMP_REF
  2. If the value is N, insert only the new data into the Out Queue.
In this situation, the LAST_TRAN parameter becomes useful.

2.13.1.3 Create a Publication Item as a Queue

You create the publication item as you would normally, with one change: define the publication item as queue-based. See Section 6.4, 'Create a Publication Item' for directions on how to define the publication item as queue-based when using MDW.
If you are using the Consolidator APIs, then the createQueuePublicationItem method creates a publication item in the form of a queue. This API call registers the publication item and creates CFM$<name> table as an In Queue, if one does not exist.
Note:
See the Javadoc in the Oracle Database Lite API Specification for more information.
You must provide the Consolidator Manager with the primary key, owner and name of the base table or view in order to create a queue that can be updated or refreshed with fast-refresh. If the base table or view name has no primary key, one can be specified in the primary key columns parameter. If primary key columns parameter is null, then Consolidator Manager uses the primary key of the base table.

2.13.1.4 Register the PL/SQL Package Outside the Repository

Once you finish developing the PL/SQL package, register the package in the MOBILEADMIN schema with the registerQueuePkg method. This method registers the package separately from the Mobile Server repository; although it refers to the In Queues, Out Queues, queue control table and temporary table that are defined in the repository.
The following methods register or remove a procedure, or retrieve the procedure name.
Note:
See the Javadoc in the Oracle Database Lite API Specification for more information.

2.13.2 Creating Data Collection Queues for Uploading Client Collected Data

If you have an application that collects data on a client, such as taking inventory or the amount collected on a parking meter, then you can use data collection queues to improve the performance of uploading the data collected to the server. Since the data only flows from the client to the server, then synchronous communication is the best method for uploading massive amounts of data.
Note:
If you are collecting data on the client, but still need updates from the server, you can use the default method for synchronization or create your own queues. See Section 2.13.1, 'Customizing Apply/Compose Phase of Synchronization with a Queue-Based Publication Item' for more information.
Data collection queues can be used for the following two types of data collection:
An example of the second type is a supply counting application. If you want to count the number of items in stock, then you could design the application table with the columns: Item and Count. Initially, populate the Item column and synchronize the data to the device, as follows:
Table 2-29 Stock Inventory Table
ItemCount
Apples
-
Pears
-
Oranges
-

The user on the client updates each item with the inventory amount, as follows:
Table 2-30 Stock Inventory Table
ItemCount
Apples
2
Pears
3
Oranges
1

The Data Collection Queue is lightweight and simple to create. Data collection queues are the same as regular queues with the exception that they provide automatic apply of the data uploaded by the client. However, you can customize whether the data is implicitly applied or not. This queue does not require the MGP to apply the changes. It does not create objects in the application schema or map data.
Data Collection Queues are easier to implement than a Queue-Based publication item. There is no need to create a package with callback methods, as Oracle Database Lite takes care of automatically uploading any new data from the client. In addition, you configure how Oracle Database Lite handles if there is any data to be downloaded or if you want the data on the client to be erased when it is uploaded to the server.
When you create the Data Collection Queue, the following is performed for you:
Just like for regular queues, users can create their own Out Queue logic. By default, the Out Queue created is an empty view with the name of (CTM$<publication_item>). An empty view is a view that selects zero records. Therefore, by default, data collection queues do not pick up any data from the server.
You can modify how the data collection queue behaves when you create it using the ConsolidatorManager.createDataCollectionQueue method. The following parameters effect the behavior of your data collection queue:

2.13.2.1 Creating a Data Collection Queue

When you create a data collection queue, you perform the following:
Note:
AllConsolidatorManager methods are fully documented in the Oracle Database Lite API Javadoc. This section provides context of the order in which to execute these methods.
  1. Create the table(s) for the data that the queue updates on the back-end Oracle database.
  2. Create the data collection queue and its publication item using the ConsolidatorManagercreateDataCollectionQueue method, where the input parameters are as follows:
    • name—A character string specifying a new publication item name.
    • owner—A string specifying the base schema object owner.
    • store—A string specifying the table name that it is based on.
    • inq_cols—A string specifigying columns in the order in which to replicate them. If null, then defaults to *, which makes the SQL statement, select * from <table>.
    • pk_columns—A string specifying the primary keys.
    • purgeClientAfterSync—If true, removes client data from the Mobile device when uploaded to the server.
    • isOutView—If true, then creates Out Queue as an empty view, otherwise creates Out Queue as a table.
    The following creates the PI_CUSTOMERS data collection queue:
  3. Create the publication that is to be used by the data collection queue. Use the ConsolidatorManagercreatePublication method. The following creates the PUB_CUSTOMERS publication that is used by the PI_CUSTOMERS data collection queue:
  4. Add the publication item created within step 1 within this publication with the ConsolidatorManageraddPublicationItem method. The following adds a publication item to the publication:
  5. If you want to have data download from the server to the Mobile client, create an Out Queue with a name that consists of CTM$<publication_item_name>. The following replaces the default Out Queue view for CUSTOMER with a view that selects all customers assigned to the EMP_ID associated with current sync session.
    Note:
    See the Oracle Database Lite samples page for the full data collection queue example from which these snippets were taken. The example demonstrates both a regular queue and a data collection queue.

2.13.3 Selecting How/When to Notify Clients of Composed Data

If you have created your own compose logic, such as in the queue-based publications, then you may want the server to notify the client that there is data to be downloaded. You can take control of starting an automatic synchronization from the server using the enqueue notification APIs.
There are other situations where you may want to control how and when clients are notified of compose data from the synchronization process. For example, if you have so many clients that to notify all of them of the data waiting for them would overload your system, you may want to control the process by notifying clients in batches.
In the normal synchronization process, when the compose phase is completed, all clients that have data in the Out Queue are notified to download the data. If, for example, you have 2000 clients, having all 2000 clients request a download at the same time could overrun your server and cause a performance issue. In this scenario, you could take control of the notification process and notify 100 clients at a time over the span of a couple of hours. This way, all of the clients receive the data in a timely fashion and your server is not overrun.
You can use the enqueue notification functionality, as follows:
This starts an automatic synchronization process for the intended client.
The enqueue notification APIs enable the server to tell the client that there is data to be downloaded and what type of data is waiting. Notifying the client of what type of data is waiting enables the client to evaluate whether it conforms to any automatic synchronization rules. For example, if the server has 10 records of low priority data, but the client has set the Server MGP Compose rule to only start an automatic synchronization if 20 records of low priority data exist, then the automatic synchronization is not started. So, the notification API input parameters include parameters that enable the server to describe the data that exists on the server.
A notification API is provided for you in both PL/SQL and Java, as follows:
Where the parameters for the above are as follows:
Table 2-31 Enqueue Notification Parameters
ParametersDescription
clientid
Consolidator client id, which is normally the username on the client device. This identifies the client to be notified. If the client does not have any automatic synchronization rules, this is the only required paramter for an automatic synchronization to start.
publication
Name of the publication for which you want notification control. This tells the client for which publication the data is destined.
pubItems
One or more publication items for which you want notification. Separate multiple publication items with a comma. This notifies the clients for which publication items the data applies.
recordCount
This notifies the client how many records exist on the server for the download.
dataSize
Reserved for future expansion.
priority
This notifies the client of the priority of the data that exists on the server. The value is 0 for high and 1 for low.

The enqueue notification API returns a unique notification ID, which can be used to query notification status in the isNotificationSent method, which is as follows:
If the notification has been sent, a boolean value of TRUE is returned.

2.14 Synchronization Performance

There are certain optimizations you can do to increase performance. See Section 1.2 'Increasing Synchronization Performance' in the Oracle Database Lite Troubleshooting and Tuning Guide for a full description.

2.15 Troubleshooting Synchronization Errors

The following section can assist you in troubleshooting any synchronization errors:

2.15.1Foreign Key Constraints in Updatable Publication Items

Replicating tables between Oracle database and clients in updatable mode can result in foreign key constraint violations if the tables have referential integrity constraints. When a foreign key constraint violation occurs, the server rejects the client transaction.

2.15.1.1Foreign Key Constraint Violation Example

For example, two tables EMP and DEPT have referential integrity constraints. The DeptNo (department number) attribute in the DEPT table is a foreign key in the EMP table. The DeptNo value for each employee in the EMP table must be a valid DeptNo value in the DEPT table.
A Mobile Server user adds a new department to the DEPT table, and then adds a new employee to this department in the EMP table. The transaction first updates DEPT and then updates the EMP table. However, the database application does not store the sequence in which these operations were executed.
When the user replicates with the Mobile Server, the Mobile Server updates the EMP table first. In doing so, it attempts to create a new record in EMP with an invalid foreign key value for DeptNo. Oracle database detects a referential integrity violation. The Mobile Server rolls back the transaction and places the transaction data in the Mobile Server error queue. In this case, the foreign key constraint violation occurred because the operations within the transaction are performed out of their original sequence.
Avoid this violation by setting table weights to each of the tables in the master-detail relationship. See Section 2.15.1.2, 'Avoiding Constraint Violations with Table Weights' for more information.

2.15.1.2Avoiding Constraint Violations with Table Weights

Mobile Server uses table weight to determine in which order to apply client operations to master tables. Table weight is expressed as an integer and are implemented as follows:
  1. Client INSERT operations are executed first, from lowest to highest table weight order.
  2. Client DELETE operations are executed next, from highest to lowest table weight order.
  3. Client UPDATE operations are executed last, from lowest to highest table weight order.
In the example listed in Section 2.15.1.1, 'Foreign Key Constraint Violation Example', a constraint violation error could be resolved by assigning DEPT a lower table weight than EMP. For example:
You define the order weight for tables when you add a publication item to the publication. For more information on setting table weights in the publication item, see Section 2.4.1.7.2, 'Using Table Weight'.

2.15.1.3Avoiding Constraint Violations with BeforeApply and After Apply

You can use a PL/SQL procedure to avoid foreign key constraint violations based on out-of-sequence operations by using DEFERRABLE constraints in conjunction with the BeforeApply and AfterApply functions. See Section 2.10.3.2, 'Defer Constraint Checking Until After All Transactions Are Applied' for more information.