hello!
i am wanting to know if folks can share insights and comments
regarding spatially enabling an OLTP database.

There are some developers who are contemplating having this installed
on our informix oltp database which seems to me like it may run into
issues in regards to affecting performance of the database and the web
application that runs on it, etc.
Currently, the idea is to spatially enable the warehouse data that is
a pull of information from the oltp system but they are looking into
the idea of doing this directly within the oltp database at a possible
future date.
Thanks in advance! Tom

Tom,

Mixing data warehouse and OLTP is never a good idea, both have different performance goals. I have done a bit of work building spatial enabled data warehouses.... It can give you a whole different perspective on what you are trying to understand in your data.

Adding the spatial data blade by itself does not add much overhead, adding spatial data (streets, images) that you join with your text data is where the benefit comes in and also where additional performance load happens.

http://www.advancedatatools.com/Solu...Solutions.html

Regards - Lester

Tom Lehr wrote:
--
__________________________________________________ ____________________
Lester Knutsen lester (AT) advancedatatools (DOT) com
Advanced DataTools Corporation Voice: 703-256-0267 x102
Visit our Web page: http://www.advancedatatools.com
__________________________________________________ ____________________

I have to agree with Lester (and not because he's my new boss ;-). Mixing
OLTP and DW queries on the same server is almost always a bad idea. Ideally
you should be running the DW server on a separate machine with separate
storage, next best is a separate server instance on the same machine. If
neither option is feasible, and you really have to share the database on
disk, I would startup an SDS server and run all of the DW queries on that
instance to keep the DW and OLTP caches separate. Hopefully the extra
licensing cost of adding a MACH11 license will convince the powers that be
to let you have a DW machine.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art (AT) iiug (DOT) org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.



On Wed, Feb 10, 2010 at 9:11 AM, Tom Lehr <tomcaml (AT) gmail (DOT) com> wrote:

I may have not been clear on what some folks are looking at here . We
have a separate warehouse arena (it is db2 actually) - while the OLTP
system is not apart of the warehouse and runs on Informix with a web
front end. What some here have suggested is spatially enabling the
informix oltp system rather than doing so in the warehouse. my concern
was that spatially enabling the oltp system would cause enough
overhead to be detrimental to the performance of the oltp system. does
that make sense?
thanks again

Tom,

Yes, both Art and I were agreeing, spatially enabling the OLTP area to use it as a data warehouse would cause performance concerns. I my experience however, you will get better performance spatial enabling Informix then db2.

And if you need the spatial for the OLTP, then got for it. I have supported OLTP systems that are spatially enabled. Thats how real time tracking and routing systems work. Like any other process you add to a system, it will take some more resources, but I would go for it if you need it. You can tune most Informix servers to gain a bit more performance to make up for adding process.

Regards - Lester


Tom Lehr wrote:
--
__________________________________________________ ____________________
Lester Knutsen lester (AT) advancedatatools (DOT) com
Advanced DataTools Corporation Voice: 703-256-0267 x102
Visit our Web page: http://www.advancedatatools.com
__________________________________________________ ____________________

thank you all for your posts.
this would be used in the sense mentioned by Lester "Thats how real
time tracking and routing systems work."
we are a transportation (trucking) and logistics company.
I would have to do exactly as you kind folks have done, which is pose
these kind of specifics to the community pondering this idea.
Regards -

one more twist to this thread - what do you think of these two
postings?

1)
Microsoft SQL Server 2008 has been uniquely designed to support
spatial data.

Overview
Microsoft SQL Server 2008 delivers comprehensive spatial support that
enables organizations to seamlessly consume, use, and extend location-
based data through spatial-enabled applications which ultimately helps
end users make better decisions.

http://www.microsoft.com/sqlserver/2...tial-data.aspx

2)
http://spatialnews.geocomm.com/commu...phtml?id=22912

The idea of a "spatially enabled" database is seriously obsolete
architecture. I don't blame the vendors who would like to codify this
approach, since after all they are only trying to protect their aging
products against more modern competition. But, if you look at what is
possible with modern technology it's clear that there are many
approaches
that provide all of the same technical benefits with much greater
performance.

If you are working with modern GIS software you can work with very
high
speed with any enterprise class database. In fact, the last thing you
want
is to have a handful of trivial DBMS "spatial" hacks standing in the
way of
intense, scalable geoprocessing performance.

That's true, but the price you pay for using OGC is that it locks you
down
to an obsolete, slow and non-scalable architecture. The gain of going
vendor-specific is often a hundred-fold or thousand-fold increase in
performance, superior integration, dramatically expanded capabilities
and
reduced cost of ownership.

A good example is the difference between Manifold's Enterprise Edition
using
standard Oracle (or SQL Server) as opposed to, say, an ESRI OGC
client/middleware combination working with Oracle Spatial. If you have
100
users doing real geoprocessing with the OGC approach your performance
will
drop to unusuably slow levels. With Manifold, because it is a truly
distributed and scalable solution, your geoprocessing performance with
100
users will be the same as with one user. Not only is Manifold 100
times
faster, it is much less expensive, it provides many more capabilities
and
you have a better choice of DBMS vendors.

Tom,

You really need to define your requirements in terms of number of users and performance. I have used SQL Server with spatial. For small offices with 4 or 5 users it will work. ESRI has a great system architecture sizing document that helps identify the limits of this approach. I also know a client trying to do a 10TB data warehouse on SQL server that gave it up. And I know of some large one's using Informix that are working well.

I have not tested Manifold's Enterprise Edition so I cannot comment in it. But it looks like another form of middle ware like ESRI's ArcSDE....

Regards - Lester


Tom Lehr wrote:
--
__________________________________________________ ____________________
Lester Knutsen lester (AT) advancedatatools (DOT) com
Advanced DataTools Corporation Voice: 703-256-0267 x102
Visit our Web page: http://www.advancedatatools.com
__________________________________________________ ____________________

Thanks again.

Tom,

Post 1 is just Microsoft marketing fluff.

Post 2...
I have no clue what manifold is.
With respect to scalable architectures that can handle geospatial data...

What I would like to say is ... [REDACTED] ...
(Sorry but I am legally limited to what I can say.)
In general terms...
There are some dot com companies that are working with alternative technologies with respect to location based services that do scale much better thantraditional databases. The key issue is how much scale do you need? You'renot talking Yahoo! or Google volumes of data. (Sorry Lester, but it was reported that back in 2007 Google was doing more than 30TB of net new data a month. God only knows how much they ingest now on a daily,weekly and monthly basis.) Yahoo! and others are using Hadoop(HBase,Hive & pig) to process massive amounts of data. The really sad thing is that if Informix hadcompleted their project Arrowhead prior to the acquisition by IBM, the XPS/IDS hybrid would have been a good tool for this kind of work.


That is not to say that traditional relational databases are not good or are not scalable to meet your specific needs.
You can use the geospatial datablades in IDS and they will perform well enough to meet or exceed your stated needs.
The issue is that accurate geocoding is probably going to be a large expense and more of a pain.
The other pain is that when you query against a table, you can't use two indexes and you can't combine a geospatial index with another column. So ifyour subset of data is large enough, you end up doing a sequential scan.But that's a different issue and rumor has it that IDS will have this feature in a near term release.


But hey! What do I know? Definitely more than I'm legally allowed to say. ;-)
Maybe this time next year I can talk about parts of it.


-G



__________________________________________________ _______________
Your E-mail and More On-the-Go. Get Windows Live Hotmail Free.
http://clk.atdmt.com/GBL/go/201469229/direct/01/