SyncManager FAQ

Introduction

This FAQ is for questions that appear regularly in the SyncManager Community forums. If you can't find the answer to your question here, then please ask your question on the forum, where it will be quickly answered.

General

What is Realworld SyncManager

Realworld SyncManager is the most flexible and complete solution to export all information from a Smallworld VMDS datastore to Oracle. For more information see our brochures.

Why would I need SyncManager when there is InSync?

Even though InSync is provided for free with the Smallworld product, there can be valid business reasons to use a tool which supports the needs of a professional organisation. For more information see our SyncManager whitepaper.

What are the system requirements

Realworld SyncManager operates on Smallworld SCT 4.0 and higher and on Oracle 9.2 and higher. For standard operation Oracle Locator is sufficient. For advanced functions like Topology export, Oracle Spatial is needed.

Functionality related

How are geometries stored into Oracle?

The Realworld SyncManager has comprehensive geometry support. Points, oriented or not, lines, areas and texts are all supported. The SDO_GEOMETRY column type is used to store geometries into Oracle. Since there is no text geometry type in Oracle, each smallworld text geometry requires 2 Oracle columns, 1 to keep the geometry itself, as a point, and a second column to store the string of the text geometry.
The V2 release has support the dimension geometries as well. Each Smallworld dimension_holder geometry is stored into 5 Oracle columns.
- 1 sdo_geometry of type MULTILINE that holds the lines making up the dimension geometry
- 2 sdo_geometry of type MULTIPOINT columns that hold the location of the symbols and labels making up the dimension geometry
- 2 sync_dim_info, which is a string vector type, columns to store the symbol names and text strings.

How are the Smallworld arcs, circles or b-splines converted to Oracle?

Oracle has support for storing arcs so that is used for storing arcs. care must be taken when exporting old 3.1 arcs since this are stored in Smallworld as tangent arcs and they will be interpreted by the engine as b-splines resulting in a totally different shape. The Smallworld circles are stored as 2 arcs. There is no support in Oracle to store b-splines hence when b-splines are received from Smallworld, the syncmanager engine transforms them into a series of connected lines that resembles the original shape.

How can I change the log file names?

The Realworld SyncManager uses log4j logging facilities. There is a log4j.properties file, located under the resources folder of the syncmanager product, which is used by all the export processes, so changes made in this file affects the log files generated by all the export process. If required the log file names to include information related to their export process this can be done modifying the !sync_timestamp_format! global variable that defaults to "#Y.#m.#d_#H.#M". For example, to change the log file name to include the configuration name it relates to, one can change the !sync_timestamp_format! to "aConfigName_#Y.#m.#d_#H.#M".

If the initial sync fails, what is the preferred manner to “start back up and finish”?

If the process failed due to external factors then just restarting the export would suffice. If it failed due to internal factors then you need to see what the problem was. In either case, you need not create a separate configuration.While replicating, a commit is performed after each successfully replicated table and this information is stored in the syncmanager metadata datastore. This means that when starting up after a failure, for the successfully replicated mappings only the changes will be synchronised and the un-processed tables, from the failed run, will be replicated.
In case you want to skip a table you should just remove it from the configuration and later on add it again. The export can see that the new table should be replicated.

How large Oracle tablespaces should be?

The Oracle tablespaces should be as large as the ds files from where the data is exported from, although in practice the tablespaces won't grow that large. Special attention should be payed to the UNDO tablespace. When replicating data, the UNDO tablespace should be large enough to store the records from the largest collection being replicated. When synchronising it should be large enough to hold all the data changed since the last synchronisation.

Could you give me more detail on error 'Unable to transform record for syncronisation' ?

Usually the error is triggered in magik when the export engine is unable to transform a record from the datastore shape to java shape. Most of the time, doing a print on the failed record or its geometries will help you spot the problem. In case the record seems fine then it is definitely something related to relational integrity and checking the joined records will help you identify the problem. A common problem is when the intermediate table for the many-to-many relationship contains references to non-existing records.

What is the purpose of the SYNC_OWNER table?

The SYNC_OWNER is there to give two pieces of information:
1. which record types can own internal worlds/maps.
2. for the above record types, what is the column that holds the foreign key (the internal world id) and what is the type of the world/map .
So if there is an entry in there like below:
ID   MAP_TYPE_ID   TABLE_NAME           MAP_ID_FIELD_NAME           CONTAINED_MAP_NAME                                                      
1    3                    ACCESS_POINT         MAP_ID_MIT_INTERNALS      mit_internals         

it means the ACCESS_POINT table has a column named MAP_ID_MIT_INTERNALS. In that column each ACCESS_POINT record holds a FK to a record in SYNC_MAP table. An application like SpatialWorkshop, allows you when inspecting an ACCESS_POINT record to jump into its internal world and see also the contained geometries. That is really the purpose of the SYNC_OWNER table, to tell an application that a given column in a table holds a world id.

To identify the geometries that are part of an internal world/map you have to use the information in the SYNC_CONTAINED_GEOM, which provides the list of possible geometries (table_name/column_name combination) that can live in a map_type. For each table_name/column_name combination, you have to perform a SQL query and filter out the geometries for your internal world/map id which is something like: 

select column_name from table t where t.column_name.sdo_point.z = your_world_id.

Can the checkpoints that are created by the syncmanager process be “cleaned up” ?  How many should remain?

The process only needs the last checkpoint for its proper functioning, but it is advisable to keep a number of them to be able to align the data in case of an Oracle backup restore.

Does Oracle needs to be on the same machine SyncManager is running on?

No, SyncManager only needs a TCP connection to Oracle, the machines can be miles apart.

My SyncManager do not export logical fields

SyncManager does not support logical fields, as it would be difficult to check if they are updated. Difference streams do not yield changes in Logical fields, hence a system with additional tables for updates would be needed, or a run through all records in a table to check for changes. The first is too much work, the second takes too long.

A solution would be that a physical field is created that is filled with the value of the logical field. This could be done through triggers. This way the updates will be signalled by the difference stream and the field can be exported with SyncManager.

How does SyncManager map type mapping work?

The SyncManager Map Type mapping should not be confused to mean that SyncManager will do a transform of the geometries from the coordinate system in Smallworld to the coordinate system in Oracle.

SyncManager is a sync tool and it is designed to be fast. This means that the coordinate systems in the Smallworld and the Oracle is assumed to be the same, however there are special circumstances when the SyncManager can not always find a match in the coordinate systems of Smallworld and Oracle. Therefore, user configuration of this mapping is required and therefore this map type mapping is configurable.

To further assist with this mapping configuration, SyncManager do allow for scaling, For example when you go from a coordinate system in meters to a similar coordinate system in mm.

We did get one request for transforming to sync between complete different coordinate systems. This is being considered by Realworld-Systems at this time and it might be in the form of an on-off switch to control when transforming should happen and keep SyncManager fast for the clients that do not require transforming.

As a temporary work around it could be considered to have post scripts in Oracle to do transforming.

Topology and Tracing in Oracle

How to export topology to Oracle?

Topology can be exported per Smallworld Manifold. For each Manifold, an Oracle Network will be created. Please consult the SyncManager Manual for more information on how to configure Topology Export.

I have exported topology, can I trace in Oracle?

To use Oracle networks from a Smallworld database with SyncManager is simple. In the configuration menu it is possible to select the Smallworld manifolds that must be created as Oracle networks. For the Smallworld manifold 4 standard Smallworld network tables are created in Oracle. The Smallworld manifold information is copied to the Oracle tables with alphanumeric data only - the geometries for the network are already in the relevant tables.

To see the network tables created by SyncManager you can use the following query in Oracle:

select node_table_name, link_table_name, path_table_name, path_link_table_name
from user_sdo_network_metadata;

For the Cambridge GIS database this is the result:

NODE_TABLE_NAME LINK_TABLE_NAME PATH_TABLE_NAME PATH_LINK_TABLE_NAME
------— ------------— ------——
CADASTRAL$NODE CADASTRAL$LINK CADASTRAL$PATH CADASTRAL$PL
RAILWAY$NODE RAILWAY$LINK RAILWAY$PATH RAILWAY$PL
ROAD$NODE ROAD$LINK ROAD$PATH ROAD$PL

Smallworld nodes and links are copied directly to the nodes and links tables with the Smallworld link and node key fields used to link the records to the Smallworld records.

The path table, for instance ROAD$PATH, is used to link the Smallworld records and geometries to the network node and link tables in Oracle. Note that the point and chain records from Smallworld are both in this table. The fields of the path table are used as follows:

PATH_ID contains a part of the geometry key in Smallworld for instance if the geom_id in Smallworld is "gis_id(22010755,0,1116029)" then the path ID for the record will be 1116029.
PATH_NAME contains the key field value for the Smallworld record. The type of the field PATH_NAME is VARCHAR2, keep this in mind when doing a query in Oracle.
PATH_TYPE contains the table and geometry name for the Smallworld source record, for instance; MIN_ROAD.CENTRE_LINE:chain.

Tracing of networks in Oracle is done in Java. Here is an example comparing the same trace in Smallworld done with Magik with a trace done with the Java classes. The Java trace is done with the Scala langauge. The results will differ, the reason for this is because the network in Oracle does not contain costs. For normal tracing of utility networks this is not a problem but for a road network (as in this case) the cost will have to be added from Smallworld.

Trace done in Smallworld:
nf « network_follower.new()
v « gis_program_manager.cached_dataset(:gis)
col « v.collection(:hotel)
h1 « col.at("Suffolk House")
h2 « col.at("Garden House")
_for l _over nf.shortest_path(h1.location.node, h2.location.node).fast_elements()
_loop
write(l.link)
_endloop

Trace done with Java:
To do a trace with Java you first need the node ID's, get these with SQL:
select start_node_id from road$path where path_name = 'Garden House'; — 537338
select start_node_id from road$path where path_name = 'Suffolk House'; — 10725
Make sure that the following jar files are in the CLASSPATH:

C:\oracle\product\11.1.0\db_1\oui\jlib\classes12.jar
C:\oracle\product\11.1.0\db_1\md\jlib\sdoapi.jar
C:\oracle\product\11.1.0\db_1\md\jlib\sdonm.jar
C:\oracle\product\11.1.0\db_1\md\jlib\sdoutl.jar

Start up a Scala session and run the following:
import oracle.spatial.network.NetworkManager
import java.sql._
import oracle.jdbc.driver._

DriverManager.registerDriver(new OracleDriver)
val conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "cambridge_gis", "cambridge_gis")
val network = NetworkManager.readNetwork(conn, "ROAD")
val path = NetworkManager.shortestPath(network, 10725, 537338)
path.getLinkArray.foreach(l => println(l.getID))

Error Descriptions

How do I circumvent the ORA-errors causing to fail my spatial indices?

If your syncmanager.util package fails on a particular spatial index with a ORA-XXX error, then, most likely, there's something wrong with certain geometries. The SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT() method can be used to analyse your table by Oracle and discover which geometries are corrupt. Usually, this has a high false positive rate. The only ORA-errors know to cause the problem with creation a spatial index are the following ORA-errors:

• ORA-13033: Invalid data in the SDO_ELEM_INFO_ARRAY in SDO_GEOMETRY object
• ORA-13346: the coordinates defining an arc are collinear (SYNC-2896)

Running the validate_layer_with_context() can be used like this:

CREATE TABLE corrupt_table_vr (sdo_rowid ROWID, result varchar2(1000));
CALL SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('corrupt_table','geometry','corrupt_table_vr', 10);

create table corrupt_table_br as
  select * from corrupt_table where rowid in (select sdo_rowid from corrupt_table_vr where substr(result, 0, 5) = '13346' or substr(result, 0, 5) = '13033');
delete from corrupt_table where rowid in (select sdo_rowid from corrupt_table_vr where substr(result, 0, 5) = '13346' or substr(result, 0, 5) = '13033');

What does the error message "the coordinates defining an arc are not distinct" mean (SYNC-2811)?

This is actually an Oracle error message: "ORA-13347: the coordinates defining an arc are not distinct" and causes the geometry to get set to NULL in the database (the rest of the record is present in the database). The cause is the difference between the units in Smallworld and Oracle, most likely, Smallworld uses milimeters, Oracle uses meters. After conversion, double coordinates are generated and Oracle doesn't like this. At the moment, the source data needs to be fixed for this.

Why do I get a "ORA-01461: can bind a LONG value only for insert into a LONG column" (SYNC-2808)?

The "text-joins" of Smallworld are mapped on types of VARCHAR2, these have a maximum of 4000 bytes. Records with large "text-joins" in Smallworld fail to insert with the abovementioned ORA-error. At the moment, removing these fields from the mapping or ignoring the errors is the only way to circumvent this from happening.

This kind of errors is raised when trying to insert a record in Oracle that has a filed of type string but the string value has more than 4000 characters.

What could cause a crashing JRE (SYNC-4557)?

A crashing JRE can have all sorts of causes, googling might be the most effective approach trying to solve this problem. A common cause is memory problems, investigating using industry standard tools might provide you with detailed information. A particular customer had crashing sync's due to running those in parallel (SYNC-4557).

Note: a crashing or early termination of the JRE results in a traceback at the moment (SYNC-4564), which might not reveal this problem immediate. If a synchronization crashes with the following traceback, it will most likely be a crashing JRE.

# --- [ info ] -----------------------------
SyncManager version: 2.2.3
# --- [ info ] -----------------------------
Core System Detected: 4.1
UVA64 in use: False
# --- [ info ] -----------------------------
Java Details
Java executable path: c:\Ties\2.2.x\dev_product\roos_sync_manager\java\jre\bin\
Java command: javaw -server
java version "1.6.0_13"
Java(TM) SE Runtime Environment (build 1.6.0_13-b03)
Java HotSpot(TM) Server VM (build 11.3-b02, mixed mode)

---- traceback: cli (heavy_thread 1375772) ----
time=08/04/2010 12:04:52
sw!version=4.1.1  (swaf)
os_text_encoding=cp1252
!snapshot_traceback?!=False

condition(information).raise(:acpt_stream_exhausted)
unnamed()
a sw:acpt_input_stream.end_of_file()
a sw:acpt_input_stream.peek()
a sync_comm_acp.start_acp()
a sync_comm_acp.connect()
a sync_stream_adapter.open(property_list(5))
a sync_manager(syncmanager).open_stream_adapter()
a sync_manager(syncmanager).init_stream_adapter()
a sync_manager(syncmanager).int!run(:cb_gis)
a sync_manager(syncmanager).run(:cb_gis)
*** top level ***()
a sw:magik_rep.process(sw:simple_vector:[1-5])
a sw:magik_rep.cli(a sw:terminal, "MagikSF> ")
cli()
light_thread_launcher_proc_990928()
(08/04/2010 12:04:52[user]) MagikSF>