Re: Informix limitations, should we be using Oracle?

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?

1. 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.) Received on Wed Oct 16 2002 - 22:43:56 CEST