Re: Informix limitations, should we be using Oracle?

Daniel Morgan wrote:
>
> Obnoxio The Clown wrote:
>
> > Daniel Morgan wrote:
> > > "Simon M." wrote:
> > >
> > > Assuming Simon's questions are sincere ... why is it that no one is
> > > honestly dealing with each and every one of them and providing guidance? I
> > > was really hoping one person would deal, point-by-point with his posting.
> > > The answers so far have ranged from insulting to non-responsive which I
> > > would take, were I Simon, as a push toward Oracle.
> >
> > The question may be sincere, but the understanding of how XPS works is
> > completely and utterly wrong. If someone is looking to address a
> > multi-terabyte DW on a single node HP box, they probably would be better
> > off with IDS, DB2 EE or (yuk!) Oracle8i than with XPS. Basically, we
> > could either spend weeks rebutting or Simon could get his (education)
> > problems fixed by getting a consultant in or talking to IBM.
> >
> > But since you asked, I thought I'd have a go (using XPS 8.40, the
> > current version):
> >
> > 1. TPC-H benchmark. the XPS benchmark is pretty much two years old.
> > Hardly surprising then, given Moore's law, that current benchmarks show
> > better bang and better bang per buck.
> >
> > 2. Memory management. I'll quote Andreas's post: "If you use implicit
> > PDQ priority, XPS will allocate memory according to the specific memory
> > need of each query. If all memory is already allocated, only queries
> > which require memory have to wait. All DSS queries which don't require
> > memory (e.g. queries with PDQ 0 and please note that PDQ 0 queries can
> > be parallel queries in XPS) can start running immediately."
> >
> > 3. Parallelism in platforms. This is just rubbish, straight from an
> > competitive slide. And patently untrue, unless Oracle has a specific
> > definition of "parallel" that only they adhere to.
> >
> > 4. Removing sessions. "Problem identifying a client session at the OS
> > level" -- please clarify? Client sessions can either be local or remote,
> > so how would you identify a client session on a remote client?
> >
> > 5. Oracle's process per connection is inherently less efficient than a
> > thread per connection. So that's a loss if you have lots of connections,
> > but a win if you want to kill them with a UNIX command.
> >
> > 6. I've never had an engine crash on me because of a shortage of locks
> > or latches, but I suppose it's not impossible (in the sense that nothing
> > is impossible when it comes to bugs! :o) The only bug I was able to find
> > on this dates back two years and applies to beta code when running XA
> > only (and turned out to be a bug in the test case! :o) Probably not that
> > likely to be a problem.
> >
> > 7. Monitoring tools. What do you need? (This sounds like it was copied
> > from a competitive presentation.) XPS has I-SPY and ISA, it also has
> > monitoring utilities you can like into something like www.bb4.com ...
> >
> > 8. Raw partitions. Raw partitions are faster (certainly for Informix).
> > It is quite feasible to use cooked files or devices, but the performance
> > overhead is massive and it adds no value. And raw devices really are not
> > difficult to monitor or administer.
> >
> > 9. Fast loading. XPS has excellent fast loading facilities, although I'd
> > agree they're very ASCII oriented. If the EBCDIC conversion is flaky,
> > there is no reason why you couldn't pipe your EBCDIC data through "dd
> > conv=ascii".
> >
> > 10. External tables. What on earth is your database design? Why do you
> > have different tables for invoices from different states?
> >
> > 11. Limitations.
> >
> > 18 characters for database objects -- Errr...128 -- did you make a typo?
> >
> > 256 byte maximum row size in Indexes -- still true (I think)
> >
> > 255 byte limit on varchar fields -- still true (I think)
> >
> > Cannot SELECT NULL -- In what way?
> >
> > No general DUAL table -- but you can create one? What does it do?
> >
> > No MINUS operations -- true.
> >
> > No GROUP BY or Aggregation in SUBQUERY, NESTED QUERY, COMPOUND QUERY,
> > EMBEDDED QUERY -- pass, I'm afraid. I don't have an XPS instance at home
> > to try this out.
> >
> > No Full OUTER JOIN -- In what way?
> >
> > No Implicit ROWID -- News to me.
> >
> > No true database flatfile Import Export -- News to me.
> >
> > Varchar fields not treated as ANSI standard -- In what way?
> >
> > No DISABLE CONSTRAINT commands - Must DROP and RECREATE constraints -
> > Additional processing -- You mean SET CONSTRAINT DISABLED, perhaps?
> >
> > Stored procedures are not prepared - Object dependence not checked
> > until runtime -- So what happens at run time if you drop an object on a
> > prepared stored procedure then?
> >
> > Page size can only be 2 or 4k -- so?
> >
> > 12. Functional comparisons.
> >
> > Tables and related indexes are partitioned on the same key -
> > inflexible configuration -- The expression "complete bollocks" comes to
> > mind. The default is that indexes are fragmented in the same way as
> > data, there is no reason you can't override it. Also, you don't even
> > need to fragment by a key field, you can really fragment by pretty much
> > anything. This smacks of "competitive presentation" to me.
> >
> > GK Indexes - Index prejoin only --What do you mean? To quote TFM: "GK
> > indexes allow you to store the result of an expression, selection of a
> > data set, or intersect of data sets from joined tables as a key in a
> > B-tree or bitmap index, which can be useful in specific queries on one
> > or more large tables."
> >
> > Necessary to manage security utilising both OS and database levels --
> > no, that's Oracle. Informix uses OS authentication.
> >
> > Sagent supports ODBC only for Informix -- who is Sagent?
> >
> > Did I miss anything, Daniel?
> >
> > (PS -- I got 90% of this from the manuals, freely available from the
> > website and 10% from my very rusty memory of a dark and distant XPS
> > project. So most of these issues can really be described as a troll.)
>
> With as much as I know about all of the RDBMS products ... a multi-terabyte
> database in my opinion likely belongs on a Teradata machine. From my experience,
> when data warehousing is for queries rather than for processing, nothing can
> beat the database from NCR.
>
> And their marketing is worse than everyone else's.
>
> Thanks.
>
> Daniel Morgan

Perfectly true - until 1998, when disks were small and slow at the same time.

Nowadays everything < 100 TB is too small to need teradata's outstanding technology.

Nevertheless it is unbeatable if your lots of data is really touched in the avg. queries. Experiece shows me, that active (i.e.'hot') data parts are more or less of the same size (in magnitude) and does not so much depend on the tons of garbage one can find often on the same
disks.
Even if that part of the data with low I/O is as huge as the biggest tape storage robots I've seen so far :)
Of course it is a matter of design and can and will vary widely.

That is why I started to tune SANs with comparable huge solid state disks, as soon as those nice beasts became available at a reasonable price & size in late 1998.

I'd say that if your estimate of data growth makes you beliefe a size of approx. 750 TB in 3 yrs time from now, you will be very well off with using IBM INFORMIX or IBM DB/2.

The first one is a tight bit better to tune, if you really know your data and I/O load - and if you put enough memory into the iron.

The latter might be easier to reach a good enough goal, if you are not forced into squeezing out the last drop of every piece you have in your machine / machine cluster.

Both products are reaching up very near into the teradata range, but are way cheaper to run (TCO) in a datacenter.

Don't take me wrong: I do have clients, who need NCR/Teradata and nothing else, as it is the only piece out there capable of satisfying customer needs.

That I neither mention SQL server nor ORACLE is beyound my scope. I never was in there, and I never told anybody to avoid those two.

Maybe there is a reason, that they never crossed my way, but I don't know.

Dic_k

-- 
Richard Kofler
Consulting & Software Design
mail:	richard.kofler_at_chello.at
phone:	+43 (0)699 11303027