Accessing Smallworld data in Oracle without the Official GE Oracle Solution Suite

Through this article I hope to describe a way in which you can access Smallworld data in Oracle without using any of the components of the official GE Oracle Solution Suite.

A comment by Anonymous got me thinking about Smallworld SQL Server. Not to be confused with Microsoft's SQL Server database, Smallworld SQL Server is Magik code that creates a Magik server image that provides access for external applications into the Smallworld VMDS database.

If you open the Smallworld Help Documentation and search for...

• "sql server" (in SW 4.0 help)
• "22 sql server" (in SW 3.21 help)

... you will be taken to a link that provides answers to Frequently Asked Questions.

A description of SQL Server in the 4.0 help documentation states...

Essentially the Smallworld SQL Server provides remote access to a Smallworld database from Windows-based clients. Using the SQL Server and a suitable ODBC-enabled application, you can quickly and easily access all the data within a Smallworld database on a remote machine from a Windows-based PC.

Once you have set up a SQL Server image on a machine, you can install the Smallworld-supplied ODBC client driver on any machine in your network. As part of the configuration for that ODBC driver, you specify the host name and port of the SQL server image's machine. Then, you can view Smallworld data using any 3rd party tool that can read ODBC data sources.

That means that you can use other programming languages to get at Smallworld data and you can also use applications like Excel to read Smallworld data.

A couple of months ago I helped a client set up SQL Server and the way we tested it initially was to use Excel to read in data from Smallworld VMDS using the Excel "Import External Data" feature. Very cool, in my opinion.

And that leads me into the next piece of the "Smallworld Data in Oracle" puzzle. Did you know that you can configure Oracle to read external data as though that data actually resided in Oracle? That functionality is called Oracle Heterogeneous Services. Basically you can use that to access non-Oracle systems from the Oracle database server. One of the non-Oracle systems that are supported by this feature ODBC data sources. You can probably see where I am going with this:

• use Smallworld's SQL Server to serve up data readable by an ODBC client driver
  
• use Oracle Heterogeneous Services to make it seem to Oracle and your other applications that the Smallworld data resides in Oracle

Once you have the Oracle Heterogeneous Services configured, then all the tools you have available to you to process Oracle data are automatically available to you to process Smallworld VMDS data.

Performance issues
It is likely that anything served up by a Magik server image may be slower than your external applications expect. One way around this is for your Oracle techies to create their own VMDS/Oracle table synchronization application. Now that they have all the VMDS data available to Oracle tools, they can use the programming tools they are familiar with to update native Oracle tables with data from the ODBC-sourced Oracle tables.

Ideally you would not want to have two tables with the same data, but you might need to do some kind of synchronization to improve performance. The good news is that whoever writes the synchronization function will not need any Magik knowledge.

Geometries
Smallworld SQL Server does not serve up geometry data types. But if it is configured correctly it can serve up geometry coordinates which could be passed through the ODBC mechanism to Oracle. The Smallworld help documentation describes how to expose coordinates to the ODBC client.


Based on my customer interaction over the years, this technology does not seem widely known or used. I am not sure about the licensing requirements, but Anonymous suggested that the price might be considered expensive. I would suggest that if you are interested in this technology you could probably arrange a "try-before-you-buy" license with GE before paying anything for it.

I have limited experience with this configuration so I am curious to hear from anyone that has tried (or would be willing to try) this configuration. It seems that it holds some promise. I am interested to hear about performance issues and how easy it is to set up this scenario.