Re: Informix limitations, should we be using Oracle?

Tim,
Chill out buddy. I tried to make such silly statements that you might get a chuckle. (logic - or lack thereof -> X is better even though no experience with Y. clearly a rather silly statement.) Truly, I have not used Informix and so really can't honestly comment on its capabilities.(and I am not) Jim
"Tim Schaefer" <tim_at_datad.com> wrote in message news:Me4r9.131121$nE3.113935_at_atlpnn01.usenetserver.com...
> Jim,
>
> I wasn't debating whether Oracle or Informix is better,
> but merely pointed out that the original poster made
> statements that indicated they didn't know what the
> f*** they were talking about regarding XPS. Personally
> I think XPS is superior to Oracle but for reasons that
> would probably surprise you, such as Informix is easier
> to use than Oracle, has a better architecture, is a
> newer engine than even Oracle 9i, etc. But XPS probably
> isn't better at slide presentations, where Oracle reigns
> supreme.
>
> Thanks,
>
> Tim
>
> "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
 news:6b4r9.54285$Fz.163137_at_rwcrnsc51.ops.asp.att.net...
> > Tim,
> > But this is an Oracle newsgroup so clearly Oracle is better. :-)
 (reading
> > comp.databases.oracle) Even though I've never used Informix, clearly
 Oracle
> > is better.
> > Jim
> > "Tim Schaefer" <tim_at_datad.com> wrote in message
> > news:KV3r9.130936$nE3.60074_at_atlpnn01.usenetserver.com...
> > > You state that you are using XPS on only one node.
> > > Why aren't you clustering?
> > >
> > > You are using a multi-node product on one box, like
> > > buying a multi-engine boat and only using one motor
> > > then complaining that nobody can ski because the
> > > boat is too big.
> > >
> > > You are also wrong about flatfile support, XPS has
> > > table loading via external tables. Geez, did you
> > > even read the docs and understand the product?
> > >
> > > I don't think you actually even have XPS, only read
> > > about it in a magazine. Your comments suggest you
> > > don't even have XPS installed. The benchmarks are
> > > also outdated, but to even bother commenting on the
> > > rest of your post would suggest you even know what
> > > XPS is.
> > >
> > > Tim
> > >
> > >
> > > "Simon M." <freetheshrimps_at_yahoo.com.au> wrote in message
 news:f650c990.0210151555.40c31157_at_posting.google.com...
> > > > Hi,
> > > >
> > > > We are trying to implement what will become a multi-terrabyte data
> > > > warehouse on Informix XPS but have hit a number of significant
> > > > problems. We are at the point where we are considering switching
> > > > database providers to Oracle but want to be sure that the problems
 we
> > > > are encountering are indeed valid issues. We have prepared a
 document
> > > > of which I have included an extract that details the issues we are
> > > > having. If anyone can provide me with feedback on these issues to
 let
> > > > me know if I'm barking up the wrong tree or if indeed they are
 issues,
> > > > it would be greatly appreciated. We are currently running Informix
 XPS
> > > > 8.3.1 on a 4CPU, 4Gb HP N4000 with HP-UX 11.11.
> > > >
> > > >
> > > > Informix XPS Issues and Comparison to Oracle
> > > >
> > > > Performance
> > > >
> > > > As detailed on TPC websites
> > > > http://www.tpc.org/tpch/results/tpch_results.asp?orderby=dbms
> > > > http://www.tpc.org/information/benchmarks.asp
> > > >
> > > > Looking at the TPC-H results for a 1000Gb database running on an
> > > > HP9000 Superdome
> > > > runs 2.65 times faster than XPS. The pricing of the two databases as
> > > > reflected in the Price/QphH also shows that Oracle represents 3.5
> > > > times more 'bang for the buck' than XPS.
> > > >
> > > > Issues :
> > > >
> > > > Memory Management - CRITICAL
> > > >
> > > > XPS has an essential flaw in it's memory management implementation
 for
> > > > parallel Decision Support System Queries. The Resource Grant Manager
> > > > configuration makes it necessary to allocate either large memory
> > > > segments or small memory segments to all sessions utilising Decision
> > > > Support Resources (large joins, sorts, ordering etc).
> > > >
> > > > It is expected that intention is so that Decision Support System
 (i.e.
> > > > Warehouse queries - DSS) queries can preallocate huge memory
 segments
> > > > through this configuration.
> > > >
> > > > When DSS queries are issued the memory is allocated up to the
> > > > DS_MEMORY_TOTAL. When this occurs, all other DSS queries are queued.
> > > > It should be noted that the memory allocated is a fixed amount,
> > > > regardless of the complexity or priority of the query being
 executed -
> > > > i.e. Simple counts are allocated the same amount of memory as
 massive
> > > > join and sort queries
> > > >
> > > > ETL tools parallelise their processing for enhanced performance.
 Using
> > > > XPS, it is not uncommon that queries issued in the same program may
> > > > expend all of the DS_TOTAL_MEMORY and other SQL statements within
 the
> > > > same program are queued. This is the cause of the classic Dawa
 problem
> > > > - 'the locked plan'.
> > > >
> > > > This situation is exacerbated by the fact that the other statements
> > > > within the program still retain their memory in Informix - so all
 SQL
> > > > statements within the database are 'locked'.
> > > >
> > > > A resolution to this is to create small memory allocations for each
> > > > session. Underallocating the memory segment size causes Reports
 (which
> > > > do lots or GROUPING and ORDERING) to run extremely slowly, or exceed
> > > > temp space allocation and fail.
> > > >
> > > > Oracle (or Informix IDS) does not employ the Resource Grant Manager
> > > > architecture. Small queries use small memory, and large queries use
> > > > large amounts of memory as required. This means the database slows
> > > > down, but does not lock on memory.
> > > >
> > > > It should be noted that XPS (Extended Parallel Server) refers to
> > > > parallelism in Platforms - it is evident that for SQL queries that
 it
> > > > is NOT optimised for parallelism.
> > > >
> > > > Removing Sessions
> > > >
> > > > Informix XPS 8.31 D has an outstanding bug in which it is not
 possible
> > > > to remove a Informix client session with assurance. Failed attempts
 to
> > > > issue the remove session command have resulted in:
> > > > · reboot of database instance
> > > > · reboot of Unix
> > > > · inability to restart the database instance
> > > >
> > > > Neither does it appear possible to link an Informix client session
 to
> > > > a Unix session id / thread - making it impossible to sessions to be
> > > > identified and removed at the Operating System level.
> > > >
> > > > Oracle, however, runs each session as an easily identifiable Unix
> > > > process, and sessions can be removed either through DBA commands, or
> > > > at the Operating System Level.
> > > >
> > > > Exceeding lock count
> > > >
> > > > Informix locks database records using memory latches. A maximum
 number
> > > > of memory latches is specified in the config file. When this limit
 is
> > > > exceeded the database server crashes.
> > > >
> > > > This is exacerbated when the database restarts and attempt to
 rollback
> > > > the transaction that caused the locks to be exceeded - this again
> > > > exceeds the maximum locks. In our experience, the only solution is
 to
> > > > destroy and recreate the database.
> > > >
> > > > Oracle does not use memory latches to lock records. It's standard
> > > > row-level-locking is managed in the rollback segment architecture.
> > > > Exceeding the rollback segment will not crash the database - it
 causes
> > > > the session to rollback the current SQL transaction.
> > > >
> > > >
> > > > Lack of diagnostic/monitoring tools
> > > >
> > > > No user friendly or graphical monitoring tools are available. This
> > > > makes diagnosis and performance monitoring extremely difficult.
> > > >
> > > > Many monitoring and diagnostic tools are available for Oracle. Many
 of
> > > > them are free. Administration, diagnosis and monitoring is vastly
> > > > simplified.
> > > >
> > > > Raw partitions exacerbate the problem.
> > > >
> > > >
> > > > Raw partitions and storage management
> > > >
> > > > Standard data storage in Informix is via RAW unix partitions. These
> > > > partitions cannot be monitored via standard Unix utilities. They are
> > > > more difficult to manage and create - Unix 'root' privileges are
> > > > required to manage them. Furthermore, raw partitions used by
 Informix
> > > > cannot be resized or grown. A completely new partition must be
> > > > allocated.
> > > >
> > > > Informix can use cooked partitions, but it is not recommended by
> > > > Informix for performance reasons.
> > > >
> > > > Oracle standard data storage is via datafiles, which are 'normal'
 unix
> > > > files. These files can be monitored for growth and utilisation much
 as
> > > > any other Unix file. No special system privileges are required to
 add
> > > > or resize datafiles.
> > > >
> > > > Fast Loading
> > > >
> > > > Our data warehouse employs fast loading capabilities (bypassing
 ODBC)
> > > > in order to upload Mainframe EBCDIC data.
> > > >
> > > > This feature is particular in Informix to XPS; in Oracle it is
> > > > standard functionality.
> > > >
> > > > The reject output from Informix is a single-file mixture of ASCII
> > > > error report and EBCDIC data. It is impossible to re-upload rejected
> > > > records from an Informix EBCDIC reject file - All packed decimal,
 for
> > > > instance, is corrupt.
> > > >
> > > > Oracle produces two output files when EBCDIC uploading - an error
 log,
> > > > and an EBCDIC reject file - ready for reprocessing.
> > > >
> > > > We currently have to reprocess complete datasets, or reject complete
> > > > datasets. With Oracle this would not be necessary.
> > > >
> > > > The administration of the External Table definitions in Informix is
> > > > excessively onerous also. For instance - 6 sales invoice files for
> > > > three states necessitates 18 external table definitions and 18
> > > > internal table definitions. Addition of EBCDIC datafiles for other
> > > > states necessarily implies additional external and internal
> > > > definitions.
> > > >
> > > > With Oracle only 6 internal tables are necessary - generic to the
> > > > Invoice, they could be used for all states. This vastly simplifies
> > > > Data Dictionary management.
> > > >
> > > >
> > > > Limitation comparisons
> > > >
> > > > Informix XPS
> > > > 18 characters for database objects
> > > > 256 byte maximum row size in Indexes
> > > > 255 byte limit on varchar fields
> > > > SQL Restrictions -
> > > > Cannot SELECT NULL
> > > > No general DUAL table
> > > > No MINUS operations
> > > > No GROUP BY or Aggregation in SUBQUERY, NESTED QUERY, COMPOUND
 QUERY,
> > > > EMBEDDED QUERY
> > > > No Full OUTER JOIN
> > > > No Implicit ROWID
> > > >
> > > > XPS - No true database flatfile Import Export
> > > > Varchar fields not treated as ANSI standard
> > > > No DISABLE CONSTRAINT commands - Must DROP and RECREATE
 constraints -
> > > > Additional processing
> > > > Stored procedures are not prepared - Object dependence not checked
> > > > until runtime
> > > > Page size can only be 2 or 4k
> > > >
> > > > Oracle
> > > > 30 characters - more meaningful names
> > > > Unlimited
> > > > 4000 limit
> > > > No comparable restrictions
> > > > Flatfile IMPORT EXPORT available for backups and rebuilds
> > > > No comparable ANSI incompatibility
> > > > DISABLE ENABLE DEFER VALIDATE NOVALIDATE CONSTRAINT possible
> > > > Stored procedures are compiled as PCODE
> > > > Page size can be as large as 64K - 16 times fewer reads for DSS
 table
> > > > scans
> > > >
> > > > Warehouse functional comparisons
> > > >
> > > > Informix
> > > > Tables and related indexes are partitioned on the same key -
> > > > inflexible configuration
> > > > GK Indexes - Index prejoin only
> > > > Necessary to manage security utilising both OS and database levels
> > > > Sagent supports ODBC only for Informix
> > > >
> > > > Oracle
> > > > Partitioning tables and indexes on alternate keys - flexible
> > > > configuration for performance
> > > > Materialised Views - Database managed JOINS of tables - Physically
> > > > existing summary tables for enhanced performance. Time savings in
> > > > aggregations
> > > > Security and permission totally managed within the database
> > > > Sagent support for High Speed Loading into Oracle
> > >
> > >
> > >
> >
> >
>
>
>
Received on Wed Oct 16 2002 - 04:50:46 CEST