I read all you post and it was very interesting...

I have only 2 thing to say and have in count that I don't know anything
about XPS:

1. In IDS 9.x, you can't select a NULL, you can use it in an UPDATE,
INSERT or DELETE. You can workaround this by creating an sp sp_genull()
who return a null and that do it... but is ugly...
If XPS can do it... it's good, inf fact there are some very cool
features only in the XPS SQL I wish to have in IDS.

2. In IDS there is no FULL OUTER JOIN... in some cases this is very
usefull. If XPS have it it's good.

As I said, I wish SQL-IDS has more features of XPS-SQL

Chucho!

Jack Parker wrote:
> I must have been offline during this discussion. I ran across it today
> looking for something else. Although late, I will add my .02. (embedded).
>
> The next time one of these come around and I'm not visible - please alert me
> to it?
>
> cheers
> j.
>
>
>>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...p?orderby=dbms
>>http://www.tpc.org/information/benchmarks.asp
>
>
> We all know about benchmarks - regardless - TPC-C is not the benchmark you
> would want to use to measure data warehouse performance, you would really
> want TPC-H, (as you mention below) - but even that is flawed in that it
> insists upon ongoing transactions (which are not normal warehouse processes)
> during the benchmark.
>
>
>>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.
>>
>
>
> Alas these results are long gone, IBM is also not in the business of
> benchmarking XPS, however; having worked with both databases, I can assure
> you that XPS will scale infinitely while Oracle will not.
>
>
>>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.
>
>
> The intent of memory allocation is to pre-allocate resources to intensive
> queries. This is by no means a requirement for XPS, nor is it a bad thing.
> With judicious use this memory can be put to very advantageous use in index
> building, hash joins, groups and sorts - I gather Oracle has a similar
> capability, I have not seen it clearly put to use yet.
>
>
>>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
>
>
> Well, not quite. You can allocate as much memory AS YOU ARE ALLOWED TO -
> which is under the control of the DBA. I realize that with Oracle a 'simple
> count' requires a full table (or index) scan, with Informix it is a single
> read against the table header and virtually instantaneous. You can also use
> a light scan for a filtered read (count(*) ... where condition) - this does
> not chew up memory. Oracle has no equivalent to the light scan - which is
> on average 4x faster than a traditional read.
>
> Yes, you can give a query enough memory so that other queries are gated and
> will not interfere with your process while it runs. This is preferable to
> the thrashing which would occur if this were not an option.
>
>
>>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'.
>
>
> Is it not a wonderful thing when you can fully utilize the power of the
> database and the machine with one query? At the same time you can prevent
> this from occuring.
>
>
>>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'.
>
>
> Not quite, only those which require DSS resources, so your 'simple count'
> would go straight through.
>
>
>>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.
>
>
> Exceeding temp space is a DBA matter - much akin to exceeding the size of a
> rollback segment under Oracle. Either you are properly sized or you are
> not. XPS, and all Informix engines, will use what memory is available to
> the process and swap to disk what is not - this is the same thing that
> Oracle will do. If you don't have enough disk - well you're SOL.
>
>
>>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.
>
>
> Actually IDS and XPS have the same memory allocation features (PDQPRIORITY),
> although under IDS it's called Memory Grant Manager, but they're the same
> thing. I have never seen XPS '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.
>>
>
>
> What have you been smoking? Where do you get this 'it is evident'? XPS
> performs in parallel with everything, across all horizontal and vertical
> portions of an operation. It exhibits the highest degree of parallelism
> that has ever been offered to the public.
>
>
>>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
>
>
> This is an issue which was corrected in 8.32, which was released 2.5 years
> ago. At your writing XPS was up to version 8.4
>
>
>>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.
>
>
> And this is a good thing. Alas with Oracle (and db2) all user sessions are
> operating systems processes, this means that context switching is removed
> from the control of the database and handed to the operating system which
> really has no clue as to what's going on inside the engine. Believe me, you
> want the threads within the engine and not subject to the OS overhead. You
> can in fact determine which sessions are tied to which clients with a simple
> onstat command.
>
>
>>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.
>
>
> And sometimes this will even remove the locks held by these sessions when
> they are thusly killed.
>
>
>>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.
>
>
> Not quite. At the time you wrote this, a counter was incremented to
> indicate that the lock threshold had been exceeded. However in current
> releases Informix has dynamic locks, more locks are allocated as needed.
>
>
>>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.
>
>
> There indeed were issues with XPS 8.31 where restarting the instance could
> cause issues. Normally, those with support contracts would call the 'Down
> Systems Group' which will answer the phone in under a minute and provide
> sunset support (i.e. 24x7) until your engine is back on line. Those without
> support would call the same group and be gently scolded - and then supported
> in the same fashion. The last time I called Oracle support, I got a call
> back three days later telling me to upgrade.
>
>
>>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.
>>
>
>
> Which can be a lot of fun. But this is a moot point. It's a question of
> sizing - if you size your rollback segments improperly - or choose the wrong
> one with which to perform your transaction - you are hosed. The lesson is
> that if you size things properly they work - whether they are Oracle or
> Informix.
>
>
>>Lack of diagnostic/monitoring tools
>>
>>No user friendly or graphical monitoring tools are available. This
>>makes diagnosis and performance monitoring extremely difficult.
>>
>
>
> There is indeed no 'Informix' tool to compare to the Oracle Enterprise
> Manager (did I get that right?). However there is a single standard command
> (onstat) to which all monitoring activity has been tied - not to mention the
> SQL interface to this same data. This can be much more effective than
> waiting around for a GUI to load and display data which you then have to
> drill into. I have to admit as an Oracle neophyte that I find the Oracle
> GUI useful for handling tasks without any understanding of what lies beneath
> them.
>
>
>>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.
>>
>
>
> I'm not sure to which problem you refer. Raw partitions are very simple to
> manage.
>
>
>>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.
>>
>
>
> 'Standard' is either 'cooked files' (OS files) or raw devices. You get a
> 15% performance improvement with raw devices. Oracle has the same feature.
> There is no similar benefit to raw devices under Oracle - I don't remember
> why at present. With a raw device Informix gets to control I/O to the
> device itself instead of going through the Unix file system - which tends to
> not only slow things down, but opens a window of vulnerability - you write
> to disk and commit, the database writes it to disk and thinks it made it,
> but the OS didn't get that far - so your 'committed transaction' wasn't.
> Management of raw spaces is incredibly simple - especially under XPS where
> you can use wildcards to manage hundreds of such spaces at once.
>
>
>>Informix can use cooked partitions, but it is not recommended by
>>Informix for performance reasons.
>
>
> True.
>
>
>>Oracle standard data storage is via datafiles, which are 'normal' uni
>>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.
>
>
> Oh right - I forgot about the resize thing - I've already mentioned the Unix
> filesystem issues. How can you resize a disk? If you buy an 80 GB disk -
> can you make it larger? So I guess you're right, under Informix you can't
> resize a raw disk, however you can add more of them and include them in the
> same dbspace (tablespace) so that the new space is usable by tables which
> need to grow in that space. So in fact you can 'resize' a dbspace.
>
>
>>Fast Loading
>>
>>Our data warehouse employs fast loading capabilities (bypassing ODBC)
>>in order to upload Mainframe EBCDIC data.
>
>
> That is a good thing. We are delighted that Oracle has come to the table
> with a fast (direct path) loader. We hope someday that it shows the same
> sort of performance as is capable with the Informix parallel loader. We
> further hope that some day it will allow you to unload data in the same
> fashion.
>
>
>>This feature is particular in Informix to XPS; in Oracle it is
>>standard functionality.
>
>
> Actually HPL and the light append has been around since 7.x, I first used it
> around 1996.
>
>
>>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.
>
>
> Actually this is true with XPS. You do get a header to each row indicating
> the error, you have to trim the header to retrieve your original data.
> Interesting - this would be a good feature request if not already done.
> With 7.x and 9.x engines the error is written to a separate file than the
> error message or you have the option of allowing it to load and then picking
> it out of a violation table later. For more on this topic visit
> www.artentech.com/downloads.htm and look for the Informix Load FAQ.
>
>
>>Oracle produces two output files when EBCDIC uploading - an error log,
>>and an EBCDIC reject file - ready for reprocessing.
>>
>
>
> If you have an error, then there is a problem. In a DSS world, you don't
> want to go chasing singleton rows, you want to do the whole thing again.
>
>
>>We currently have to reprocess complete datasets, or reject complete
>>datasets. With Oracle this would not be necessary.
>
>
> This is ok when you are dealing with a small number of rows and can afford
> the luxury of debugging each one. When you are dealing with billions of
> rows and you have a problem, you really don't want to get into the details
> of any individual row. I guess it's a question of how big you intend to
> get.
>
>
>>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.
>
>
> Not sure what you're saying here. If you have 18 files to load, under XPS
> you can define them all as a single external table. You can define this
> single external table across filename, filesystems, coservers(each instance
> in a cluster) and servers, you can define it with a single file statement
> even (not 18) - it's pretty powerful. Furthermore, this file (or external
> table) is visible to the database - you don't have to go through sqlldr to
> get it into the database, you can treat it as a table and select columns
> from it directly, or join it with other tables - whatever. It's really
> cool. Even cooler - you can unload to it directly with a 'select <columns>
> from <> insert into <>' statement.
>
>
>>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.
>>
>
>
> See above.
>
>
>>Limitation comparisons
>>
>>Informix XPS
>>18 characters for database objects
>
> Ok.
>
>
>>256 byte maximum row size in Indexes
>
> You use indices? Are you in a data warehouse environment or an OLTP
> environment? XPS can perform the hash join so quickly that indices are a
> detriment, not a requirement. What you might consider a fast join between
> two tables with indices can be performed 100 times faster with a hash join -
> but then you need some memory to play these sorts of games. Have a look at
> http://www7b.boulder.ibm.com/dmdd/zo...rticle/parker/
> 0502parker.html for some examples.
>
>
>>255 byte limit on varchar fields
>
> True, although you can go to text and have at least 2GB last time I
> checked.
>
>
>>SQL Restrictions -
>>Cannot SELECT NULL
>
> What? Sure you can.
>
>
>>No general DUAL table
>
> Create one. It's the same thing. I don't generally bother, I 'select
> 'whatever' from systables where tabid=1'; It's the same thing.
>
>
>>No MINUS operations
>
> True, that's a neat Oracle feature.
>
>
>>No GROUP BY or Aggregation in SUBQUERY, NESTED QUERY, COMPOUND QUERY,
>
>
>>EMBEDDED QUERY
>
> Haven't tried this. Never needed it.
>
>
>>No Full OUTER JOIN
>
> Sure there is. Or do you mean that you want all rows from table 'a' and 'b'
> even where neither intersect. Who's been dreaming up your data models? You
> need to clean up your data if this is a requirement. But then I guess
> Informix doesn't do that particular join.
>
>
>>No Implicit ROWID
>
> Not anymore, you can ask for one if you need it.
>
>
>>XPS - No true database flatfile Import Export
>
> Totally untrue. For that matter, has Oracle come up with a true flatfile
> export yet?
>
>
>>Varchar fields not treated as ANSI standard
>
> Well, you can use the ANSI standard if you like, you can also allocate a
> minimum to each varchar which is incredibly useful when it comes time to
> update. Unlike Oracle, which compresses data as much as possible to
> preserve space - and then when you need to update it - oooops won't fit into
> it's original space - have to move it to a new page - which can be time
> consuming - the reservation of space allows the intelligent DBA to
> counteract this sort of thing.
>
>
>>No DISABLE CONSTRAINT commands - Must DROP and RECREATE constraints -
>
> Bullshit. You need to spend more time with the manual.
>
>
>>Additional processing
>>Stored procedures are not prepared - Object dependence not checked
>>until runtime
>
>
> Stored procedures, when compiled are checked against what is available -
> compilation will fail if a required object is not present. What you should
> properly say is that Oracle's PL/SQL is far superior to the Informix stored
> procedure language. Within Oracle you can develop entire applications with
> PL/SQL, the Informix approach to Stored procedures is more basic - to
> perform this or that function - program logic does not truly belong in an
> Informix SPL - more properly in one of it's more standard languages 4GL or
> esql/c. Actually I'm not that crazy about the way Oracle will mark a
> procedure as invalid when something that it depends on changes. This can be
> a real pain to manage. I know what I need to rebuild - I don't need the
> database thinking it knows better than me.
>
>
>>Page size can only be 2 or 4k
>
>
> or 8K. This isn't really an issue, unless you have some reason to need a
> 48K page? You have data rows which are bigger than 8k? Why? Have you
> considered a relational model? Informix will still support big rows, but
> they will span pages.
>
>
>>Oracle
>>30 characters - more meaningful names
>
> If you can't fit it into 18 - why is 30 better? Why don't you bring up 9.4
> and it's support for object names of 128? IDS is the engine you should be
> comparing to, not XPS. Oracle doesn't have anything to compare to XPS.
>
>
>>Unlimited
>
> (this was the index size - not sure why you want this)
>
>
>>4000 limit
>
> (2GB limit on text)
>
>
>>No comparable restrictions
>
> How about 'order by 1,2,3'? You guys sure do like to type, especially those
> 30 character names.
> You're 'comparable restrictions' refer perhaps to the ANSI standard which is
> a subset of all RDBMS vendors SQL.
>
>
>>Flatfile IMPORT EXPORT available for backups and rebuilds
>
> But not a true flatfile.
>
>
>>No comparable ANSI incompatibility
>
> Moot.
>
>
>>DISABLE ENABLE DEFER VALIDATE NOVALIDATE CONSTRAINT possible
>
> The same basic stuff is available. Since this is not ANSI standard the name
> may vary slightly.
>
>
>>Stored procedures are compiled as PCODE
>
> ??? Informix stored procedures are also compiled into code within the
> engine. Not sure where you are getting this stuff.
>
>
>>Page size can be as large as 64K - 16 times fewer reads for DSS table
>>scans
>
>
> No, actually 8 times more reads. When I want one row, I don't want to pull
> 64K, or 8 times the standard disk buffer - hence 8 disk reads instead of 1.
>
>
>>Warehouse functional comparisons
>>
>>Informix
>>Tables and related indexes are partitioned on the same key -
>
>
> Untrue again. Hybrid fragmentation has been around for some time -
> including back in 8.31.
>
>
>>inflexible configuration
>>GK Indexes - Index prejoin only
>
>
> You need to be pulling out materialized views here - which is the proper
> comparison to the GK index.
>
>
>>Necessary to manage security utilising both OS and database levels
>
>
> Sorry? This is an Oracle issue - you need two layers of security, Informix
> uses the OS security.
>
>
>>Sagent supports ODBC only for Informix
>
>
> Who?
>
>
>>Oracle
>>Partitioning tables and indexes on alternate keys - flexible
>>configuration for performance
>
> Moot.
>
>
>>Materialised Views - Database managed JOINS of tables - Physically
>>existing summary tables for enhanced performance. Time savings in
>>aggregations
>
>
> Yes, but then you have to refresh that summary table. I have no real
> quarrel here, but you're comparing quasi-identical features and claiming
> it's bad there and good here.
>
>
>>Security and permission totally managed within the database
>
>
> Ah, for clients perhaps. Still Informix is relying on their ability to
> connect to the server, not on an additional layer of security within the
> database.
>
>
>>Sagent support for High Speed Loading into Oracle
>>
>
>
> I guess if Sagent is a requirement you should evaluate whether they are Open
> enough for your current and future requirements.
>
> -----
>
> But then you are comparing volkswagens and ferraris. XPS is a true
> clustered shared nothing environment which can scale literally infinitely -
> ok I am getting away from myself - it can only have 32K coservers and each
> can only support 32 Pedabytes of data - so if you need to go beyond that
> you'd have to pick up the phone and ask the engineers to bump the counter -
> the thing is that with XPS this is not a theoretical limit, performance does
> not drop off as you add more instances (coservers) to the cluster - it
> continues to scale linearly. With Oracle this will only happen on a slide
> projector. There are only two other databases currently available which
> have the architecture to scale in this fashion - DB2 (pdf or whatever it's
> called now) and Teradata. If you want to compare Oracle - you need to be
> comparing to Informix 9.
>
> Yeah sure RAC - give me a break, it's all controlled through a single
> manager. Bottleneck city.
>
> Perhaps obviously you have (had) a need to justify going to Oracle over XPS.
> Perhaps you know Oracle better and are more comfortable with it - fine -
> that's a good enough reason. DBA skills are more generally expensive than
> the database itself. Just don't try to justify it by trying to compare an
> apple with a mango.
>
> As far as features as concerned, you've mentioned quite a few that XPS
> doesn't have, or that you haven't uncovered yet. You might also want to
> look at the XPS 'UPDATE JOIN' and 'DELETE JOIN' statements - talk about
> supporting a warehouse - they rock. There are others, but I digress.
>
> Tell you what. Send me a copy of your schema, some data and some code, and
> I'll put my XPS instance (P4 2.4Ghz, 2xIDE box) up against whatever you
> have. Maybe IBM won't benchmark, but I will.
>
> cheers
> j.
>
>
>
>

--


Atte,


Jesús Antonio Santos Giraldo
jeansagi@myrealbox.com
jeansagi@netscape.net

sending to informix-list

What wrong with

select *
from table
where colname is null;

seems to work for me

Jean Sagi wrote:
>
> I read all you post and it was very interesting...
>
> I have only 2 thing to say and have in count that I don't know anything
> about XPS:
>
> 1. In IDS 9.x, you can't select a NULL, you can use it in an UPDATE,
> INSERT or DELETE. You can workaround this by creating an sp sp_genull()
> who return a null and that do it... but is ugly...
> If XPS can do it... it's good, inf fact there are some very cool
> features only in the XPS SQL I wish to have in IDS.
>
> 2. In IDS there is no FULL OUTER JOIN... in some cases this is very
> usefull. If XPS have it it's good.
>
> As I said, I wish SQL-IDS has more features of XPS-SQL
>
> Chucho!
>
> Jack Parker wrote:
> > I must have been offline during this discussion. I ran across it today
> > looking for something else. Although late, I will add my .02. (embedded).
> >
> > The next time one of these come around and I'm not visible - please alert me
> > to it?
> >
> > cheers
> > j.
> >
> >
> >>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...p?orderby=dbms
> >>http://www.tpc.org/information/benchmarks.asp
> >
> >
> > We all know about benchmarks - regardless - TPC-C is not the benchmark you
> > would want to use to measure data warehouse performance, you would really
> > want TPC-H, (as you mention below) - but even that is flawed in that it
> > insists upon ongoing transactions (which are not normal warehouse processes)
> > during the benchmark.
> >
> >
> >>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.
> >>
> >
> >
> > Alas these results are long gone, IBM is also not in the business of
> > benchmarking XPS, however; having worked with both databases, I can assure
> > you that XPS will scale infinitely while Oracle will not.
> >
> >
> >>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.
> >
> >
> > The intent of memory allocation is to pre-allocate resources to intensive
> > queries. This is by no means a requirement for XPS, nor is it a bad thing.
> > With judicious use this memory can be put to very advantageous use in index
> > building, hash joins, groups and sorts - I gather Oracle has a similar
> > capability, I have not seen it clearly put to use yet.
> >
> >
> >>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
> >
> >
> > Well, not quite. You can allocate as much memory AS YOU ARE ALLOWED TO -
> > which is under the control of the DBA. I realize that with Oracle a 'simple
> > count' requires a full table (or index) scan, with Informix it is a single
> > read against the table header and virtually instantaneous. You can also use
> > a light scan for a filtered read (count(*) ... where condition) - this does
> > not chew up memory. Oracle has no equivalent to the light scan - which is
> > on average 4x faster than a traditional read.
> >
> > Yes, you can give a query enough memory so that other queries are gated and
> > will not interfere with your process while it runs. This is preferable to
> > the thrashing which would occur if this were not an option.
> >
> >
> >>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'.
> >
> >
> > Is it not a wonderful thing when you can fully utilize the power of the
> > database and the machine with one query? At the same time you can prevent
> > this from occuring.
> >
> >
> >>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'.
> >
> >
> > Not quite, only those which require DSS resources, so your 'simple count'
> > would go straight through.
> >
> >
> >>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.
> >
> >
> > Exceeding temp space is a DBA matter - much akin to exceeding the size of a
> > rollback segment under Oracle. Either you are properly sized or you are
> > not. XPS, and all Informix engines, will use what memory is available to
> > the process and swap to disk what is not - this is the same thing that
> > Oracle will do. If you don't have enough disk - well you're SOL.
> >
> >
> >>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.
> >
> >
> > Actually IDS and XPS have the same memory allocation features (PDQPRIORITY),
> > although under IDS it's called Memory Grant Manager, but they're the same
> > thing. I have never seen XPS '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.
> >>
> >
> >
> > What have you been smoking? Where do you get this 'it is evident'? XPS
> > performs in parallel with everything, across all horizontal and vertical
> > portions of an operation. It exhibits the highest degree of parallelism
> > that has ever been offered to the public.
> >
> >
> >>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
> >
> >
> > This is an issue which was corrected in 8.32, which was released 2.5 years
> > ago. At your writing XPS was up to version 8.4
> >
> >
> >>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.
> >
> >
> > And this is a good thing. Alas with Oracle (and db2) all user sessions are
> > operating systems processes, this means that context switching is removed
> > from the control of the database and handed to the operating system which
> > really has no clue as to what's going on inside the engine. Believe me, you
> > want the threads within the engine and not subject to the OS overhead. You
> > can in fact determine which sessions are tied to which clients with a simple
> > onstat command.
> >
> >
> >>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.
> >
> >
> > And sometimes this will even remove the locks held by these sessions when
> > they are thusly killed.
> >
> >
> >>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.
> >
> >
> > Not quite. At the time you wrote this, a counter was incremented to
> > indicate that the lock threshold had been exceeded. However in current
> > releases Informix has dynamic locks, more locks are allocated as needed.
> >
> >
> >>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.
> >
> >
> > There indeed were issues with XPS 8.31 where restarting the instance could
> > cause issues. Normally, those with support contracts would call the 'Down
> > Systems Group' which will answer the phone in under a minute and provide
> > sunset support (i.e. 24x7) until your engine is back on line. Those without
> > support would call the same group and be gently scolded - and then supported
> > in the same fashion. The last time I called Oracle support, I got a call
> > back three days later telling me to upgrade.
> >
> >
> >>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.
> >>
> >
> >
> > Which can be a lot of fun. But this is a moot point. It's a question of
> > sizing - if you size your rollback segments improperly - or choose the wrong
> > one with which to perform your transaction - you are hosed. The lesson is
> > that if you size things properly they work - whether they are Oracle or
> > Informix.
> >
> >
> >>Lack of diagnostic/monitoring tools
> >>
> >>No user friendly or graphical monitoring tools are available. This
> >>makes diagnosis and performance monitoring extremely difficult.
> >>
> >
> >
> > There is indeed no 'Informix' tool to compare to the Oracle Enterprise
> > Manager (did I get that right?). However there is a single standard command
> > (onstat) to which all monitoring activity has been tied - not to mention the
> > SQL interface to this same data. This can be much more effective than
> > waiting around for a GUI to load and display data which you then have to
> > drill into. I have to admit as an Oracle neophyte that I find the Oracle
> > GUI useful for handling tasks without any understanding of what lies beneath
> > them.
> >
> >
> >>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.
> >>
> >
> >
> > I'm not sure to which problem you refer. Raw partitions are very simple to
> > manage.
> >
> >
> >>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.
> >>
> >
> >
> > 'Standard' is either 'cooked files' (OS files) or raw devices. You get a
> > 15% performance improvement with raw devices. Oracle has the same feature.
> > There is no similar benefit to raw devices under Oracle - I don't remember
> > why at present. With a raw device Informix gets to control I/O to the
> > device itself instead of going through the Unix file system - which tends to
> > not only slow things down, but opens a window of vulnerability - you write
> > to disk and commit, the database writes it to disk and thinks it made it,
> > but the OS didn't get that far - so your 'committed transaction' wasn't.
> > Management of raw spaces is incredibly simple - especially under XPS where
> > you can use wildcards to manage hundreds of such spaces at once.
> >
> >
> >>Informix can use cooked partitions, but it is not recommended by
> >>Informix for performance reasons.
> >
> >
> > True.
> >
> >
> >>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 Uix file. No special system privileges are required to add
> >>or resize datafiles.
> >
> >
> > Oh right - I forgot about the resize thing - I've already mentioned the Unix
> > filesystem issues. How can you resize a disk? If you buy an 80 GB disk -
> > can you make it larger? So I guess you're right, under Informix you can't
> > resize a raw disk, however you can add more of them and include them in the
> > same dbspace (tablespace) so that the new space is usable by tables which
> > need to grow in that space. So in fact you can 'resize' a dbspace.
> >
> >
> >>Fast Loading
> >>
> >>Our data warehouse employs fast loading capabilities (bypassing ODBC)
> >>in order to upload Mainframe EBCDIC data.
> >
> >
> > That is a good thing. We are delighted that Oracle has come to the table
> > with a fast (direct path) loader. We hope someday that it shows the same
> > sort of performance as is capable with the Informix parallel loader. We
> > further hope that some day it will allow you to unload data in the same
> > fashion.
> >
> >
> >>This feature is particular in Informix to XPS; in Oracle it is
> >>standard functionality.
> >
> >
> > Actually HPL and the light append has been around since 7.x, I first used it
> > around 1996.
> >
> >
> >>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.
> >
> >
> > Actually this is true with XPS. You do get a header to each row indicating
> > the error, you have to trim the header to retrieve your original data.
> > Interesting - this would be a good feature request if not already done.
> > With 7.x and 9.x engines the error is written to a separate file than the
> > error message or you have the option of allowing it to load and then picking
> > it out of a violation table later. For more on this topic visit
> > www.artentech.com/downloads.htm and look for the Informix Load FAQ.
> >
> >
> >>Oracle produces two output files when EBCDIC uploading - an error log,
> >>and an EBCDIC reject file - ready for reprocessing.
> >>
> >
> >
> > If you have an error, then there is a problem. In a DSS world, you don't
> > want to go chasing singleton rows, you want to do the whole thing again.
> >
> >
> >>We currently have to reprocess complete datasets, or reject complete
> >>datasets. With Oracle this would not be necessary.
> >
> >
> > This is ok when you are dealing with a small number of rows and can afford
> > the luxury of debugging each one. When you are dealing with billions of
> > rows and you have a problem, you really don't want to get into the details
> > of any individual row. I guess it's a question of how big you intend to
> > get.
> >
> >
> >>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.
> >
> >
> > Not sure what you're saying here. If you have 18 files to load, under XPS
> > you can define them all as a single external table. You can define this
> > single external table across filename, filesystems, coservers(each instance
> > in a cluster) and servers, you can define it with a single file statement
> > even (not 18) - it's pretty powerful. Furthermore, this file (or external
> > table) is visible to the database - you don't have to go through sqlldr to
> > get it into the database, you can treat it as a table and select columns
> > from it directly, or join it with other tables - whatever. It's really
> > cool. Even cooler - you can unload to it directly with a 'select <columns>
> > from <> insert into <>' statement.
> >
> >
> >>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.
> >>
> >
> >
> > See above.
> >
> >
> >>Limitation comparisons
> >>
> >>Informix XPS
> >>18 characters for database objects
> >
> > Ok.
> >
> >
> >>256 byte maximum row size in Indexes
> >
> > You use indices? Are you in a data warehouse environment or an OLTP
> > environment? XPS can perform the hash join so quickly that indices are a
> > detriment, not a requirement. What you might consider a fast join between
> > two tables with indices can be performed 100 times faster with a hash join -
> > but then you need some memory to play these sorts of games. Have a look at
> > http://www7b.boulder.ibm.com/dmdd/zo...rticle/parker/
> > 0502parker.html for some examples.
> >
> >
> >>255 byte limit on varchar fields
> >
> > True, although you can go to text and have at least 2GB last time I
> > checked.
> >
> >
> >>SQL Restrictions -
> >>Cannot SELECT NULL
> >
> > What? Sure you can.
> >
> >
> >>No general DUAL table
> >
> > Create one. It's the same thing. I don't generally bother, I 'select
> > 'whatever' from systables where tabid=1'; It's the same thing.
> >
> >
> >>No MINUS operations
> >
> > True, that's a neat Oracle feature.
> >
> >
> >>No GROUP BY or Aggregation in SUBQUERY, NESTED QUERY, COMPOUND QUERY,
> >
> >
> >>EMBEDDED QUERY
> >
> > Haven't tried this. Never needed it.
> >
> >
> >>No Full OUTER JOIN
> >
> > Sure there is. Or do you mean that you want all rows from table 'a' and 'b'
> > even where neither intersect. Who's been dreaming up your data models? You
> > need to clean up your data if this is a requirement. But then I guess
> > Informix doesn't do that particular join.
> >
> >
> >>No Implicit ROWID
> >
> > Not anymore, you can ask for one if you need it.
> >
> >
> >>XPS - No true database flatfile Import Export
> >
> > Totally untrue. For that matter, has Oracle come up with a true flatfile
> > export yet?
> >
> >
> >>Varchar fields not treated as ANSI standard
> >
> > Well, you can use the ANSI standard if you like, you can also allocate a
> > minimum to each varchar which is incredibly useful when it comes time to
> > update. Unlike Oracle, which compresses data as much as possible to
> > preserve space - and then when you need to update it - oooops won't fit into
> > it's original space - have to move it to a new page - which can be time
> > consuming - the reservation of space allows the intelligent DBA to
> > counteract this sort of thing.
> >
> >
> >>No DISABLE CONSTRAINT commands - Must DROP and RECREATE constraints -
> >
> > Bullshit. You need to spend more time with the manual.
> >
> >
> >>Additional processing
> >>Stored procedures are not prepared - Object dependence not checked
> >>until runtime
> >
> >
> > Stored procedures, when compiled are checked against what is available -
> > compilation will fail if a required object is not present. What you should
> > properly say is that Oracle's PL/SQL is far superior to the Informix stored
> > procedure language. Within Oracle you can develop entire applications with
> > PL/SQL, the Informix approach to Stored procedures is more basic - to
> > perform this or that function - program logic does not truly belong in an
> > Informix SPL - more properly in one of it's more standard languages 4GL or
> > esql/c. Actually I'm not that crazy about the way Oracle will mark a
> > procedure as invalid when something that it depends on changes. This can be
> > a real pain to manage. I know what I need to rebuild - I don't need the
> > database thinking it knows better than me.
> >
> >
> >>Page size can only be 2 or 4k
> >
> >
> > or 8K. This isn't really an issue, unless you have some reason to need a
> > 48K page? You have data rows which are bigger than 8k? Why? Have you
> > considered a relational model? Informix will still support big rows, but
> > they will span pages.
> >
> >
> >>Oracle
> >>30 characters - more meaningful names
> >
> > If you can't fit it into 18 - why is 30 better? Why don't you bring up 9.4
> > and it's support for object names of 128? IDS is the engine you should be
> > comparing to, not XPS. Oracle doesn't have anything to compare to XPS.
> >
> >
> >>Unlimited
> >
> > (this was the index size - not sure why you want this)
> >
> >
> >>4000 limit
> >
> > (2GB limit on text)
> >
> >
> >>No comparable restrictions
> >
> > How about 'order by 1,2,3'? You guys sure do like to type, especially those
> > 30 character names.
> > You're 'comparable restrictions' refer perhaps to the ANSI standard which is
> > a subset of all RDBMS vendors SQL.
> >
> >
> >>Flatfile IMPORT EXPORT available for backups and rebuilds
> >
> > But not a true flatfile.
> >
> >
> >>No comparable ANSI incompatibility
> >
> > Moot.
> >
> >
> >>DISABLE ENABLE DEFER VALIDATE NOVALIDATE CONSTRAINT possible
> >
> > The same basic stuff is available. Since this is not ANSI standard the name
> > may vary slightly.
> >
> >
> >>Stored procedures are compiled as PCODE
> >
> > ??? Informix stored procedures are also compiled into code within the
> > engine. Not sure where you are getting this stuff.
> >
> >
> >>Page size can be as large as 64K - 16 times fewer reads for DSS table
> >>scans
> >
> >
> > No, actually 8 times more reads. When I want one row, I don't want to pull
> > 64K, or 8 times the standard disk buffer - hence 8 disk reads instead of 1.
> >
> >
> >>Warehouse functional comparisons
> >>
> >>Informix
> >>Tables and related indexes are partitioned on the same key -
> >
> >
> > Untrue again. Hybrid fragmentation has been around for some time -
> > including back in 8.31.
> >
> >
> >>inflexible configuration
> >>GK Indexes - Index prejoin only
> >
> >
> > You need to be pulling out materialized views here - which is the proper
> > comparison to the GK index.
> >
> >
> >>Necessary to manage security utilising both OS and database levels
> >
> >
> > Sorry? This is an Oracle issue - you need two layers of security, Informix
> > uses the OS security.
> >
> >
> >>Sagent supports ODBC only for Informix
> >
> >
> > Who?
> >
> >
> >>Oracle
> >>Partitioning tables and indexes on alternate keys - flexible
> >>configuration for performance
> >
> > Moot.
> >
> >
> >>Materialised Views - Database managed JOINS of tables - Physically
> >>existing summary tables for enhanced performance. Time savings in
> >>aggregations
> >
> >
> > Yes, but then you have to refresh that summary table. I have no real
> > quarrel here, but you're comparing quasi-identical features and claiming
> > it's bad there and good here.
> >
> >
> >>Security and permission totally managed within the database
> >
> >
> > Ah, for clients perhaps. Still Informix is relying on their ability to
> > connect to the server, not on an additional layer of security within the
> > database.
> >
> >
> >>Sagent support for High Speed Loading into Oracle
> >>
> >
> >
> > I guess if Sagent is a requirement you should evaluate whether they are Open
> > enough for your current and future requirements.
> >
> > -----
> >
> > But then you are comparing volkswagens and ferraris. XPS is a true
> > clustered shared nothing environment which can scale literally infinitely -
> > ok I am getting away from myself - it can only have 32K coservers and each
> > can only support 32 Pedabytes of data - so if you need to go beyond that
> > you'd have to pick up the phone and ask the engineers to bump the counter -
> > the thing is that with XPS this is not a theoretical limit, performance does
> > not drop off as you add more instances (coservers) to the cluster - it
> > continues to scale linearly. With Oracle this will only happen on a slide
> > projector. There are only two other databases currently available which
> > have the architecture to scale in this fashion - DB2 (pdf or whatever it's
> > called now) and Teradata. If you want to compare Oracle - you need to be
> > comparing to Informix 9.
> >
> > Yeah sure RAC - give me a break, it's all controlled through a single
> > manager. Bottleneck city.
> >
> > Perhaps obviously you have (had) a need to justify going to Oracle over XPS.
> > Perhaps you know Oracle better and are more comfortable with it - fine -
> > that's a good enough reason. DBA skills are more generally expensive than
> > the database itself. Just don't try to justify it by trying to compare an
> > apple with a mango.
> >
> > As far as features as concerned, you've mentioned quite a few that XPS
> > doesn't have, or that you haven't uncovered yet. You might also want to
> > look at the XPS 'UPDATE JOIN' and 'DELETE JOIN' statements - talk about
> > supporting a warehouse - they rock. There are others, but I digress.
> >
> > Tell you what. Send me a copy of your schema, some data and some code, and
> > I'll put my XPS instance (P4 2.4Ghz, 2xIDE box) up against whatever you
> > have. Maybe IBM won't benchmark, but I will.
> >
> > cheers
> > j.
> >
> >
> >
> >
>
> --
>
> Atte,
>
> Jesús Antonio Santos Giraldo
> jeansagi@myrealbox.com
> jeansagi@netscape.net
>
> sending to informix-list

--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
www.oninit.com #

you can select null, you just need to give your column a datatype.

i.e. select null::integer from sometable;

-Brian

select case when 1=2 then 1 else null end::integer null_value
from table(set{1})

Jean Sagi wrote in message ...
>
>I read all you post and it was very interesting...
>
>I have only 2 thing to say and have in count that I don't know anything
>about XPS:
>
>1. In IDS 9.x, you can't select a NULL, you can use it in an UPDATE,
>INSERT or DELETE. You can workaround this by creating an sp sp_genull()
>who return a null and that do it... but is ugly...
>If XPS can do it... it's good, inf fact there are some very cool
>features only in the XPS SQL I wish to have in IDS.
>
>2. In IDS there is no FULL OUTER JOIN... in some cases this is very
>usefull. If XPS have it it's good.
>
>As I said, I wish SQL-IDS has more features of XPS-SQL
>
>Chucho!
>
>Jack Parker wrote:
>> I must have been offline during this discussion. I ran across it today
>> looking for something else. Although late, I will add my .02.
(embedded).
>>
>> The next time one of these come around and I'm not visible - please alert
me
>> to it?
>>
>> cheers
>> j.
>>
>>
>>>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...p?orderby=dbms
>>>http://www.tpc.org/information/benchmarks.asp
>>
>>
>> We all know about benchmarks - regardless - TPC-C is not the benchmark
you
>> would want to use to measure data warehouse performance, you would really
>> want TPC-H, (as you mention below) - but even that is flawed in that it
>> insists upon ongoing transactions (which are not normal warehouse
processes)
>> during the benchmark.
>>
>>
>>>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.
>>>
>>
>>
>> Alas these results are long gone, IBM is also not in the business of
>> benchmarking XPS, however; having worked with both databases, I can
assure
>> you that XPS will scale infinitely while Oracle will not.
>>
>>
>>>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.
>>
>>
>> The intent of memory allocation is to pre-allocate resources to intensive
>> queries. This is by no means a requirement for XPS, nor is it a bad
thing.
>> With judicious use this memory can be put to very advantageous use in
index
>> building, hash joins, groups and sorts - I gather Oracle has a similar
>> capability, I have not seen it clearly put to use yet.
>>
>>
>>>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
>>
>>
>> Well, not quite. You can allocate as much memory AS YOU ARE ALLOWED TO -
>> which is under the control of the DBA. I realize that with Oracle a
'simple
>> count' requires a full table (or index) scan, with Informix it is a
single
>> read against the table header and virtually instantaneous. You can also
use
>> a light scan for a filtered read (count(*) ... where condition) - this
does
>> not chew up memory. Oracle has no equivalent to the light scan - which
is
>> on average 4x faster than a traditional read.
>>
>> Yes, you can give a query enough memory so that other queries are gated
and
>> will not interfere with your process while it runs. This is preferable
to
>> the thrashing which would occur if this were not an option.
>>
>>
>>>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'.
>>
>>
>> Is it not a wonderful thing when you can fully utilize the power of the
>> database and the machine with one query? At the same time you can
prevent
>> this from occuring.
>>
>>
>>>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'.
>>
>>
>> Not quite, only those which require DSS resources, so your 'simple count'
>> would go straight through.
>>
>>
>>>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.
>>
>>
>> Exceeding temp space is a DBA matter - much akin to exceeding the size of
a
>> rollback segment under Oracle. Either you are properly sized or you are
>> not. XPS, and all Informix engines, will use what memory is available to
>> the process and swap to disk what is not - this is the same thing that
>> Oracle will do. If you don't have enough disk - well you're SOL.
>>
>>
>>>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.
>>
>>
>> Actually IDS and XPS have the same memory allocation features
(PDQPRIORITY),
>> although under IDS it's called Memory Grant Manager, but they're the same
>> thing. I have never seen XPS '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.
>>>
>>
>>
>> What have you been smoking? Where do you get this 'it is evident'? XPS
>> performs in parallel with everything, across all horizontal and vertical
>> portions of an operation. It exhibits the highest degree of parallelism
>> that has ever been offered to the public.
>>
>>
>>>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
>>
>>
>> This is an issue which was corrected in 8.32, which was released 2.5
years
>> ago. At your writing XPS was up to version 8.4
>>
>>
>>>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.
>>
>>
>> And this is a good thing. Alas with Oracle (and db2) all user sessions
are
>> operating systems processes, this means that context switching is removed
>> from the control of the database and handed to the operating system which
>> really has no clue as to what's going on inside the engine. Believe me,
you
>> want the threads within the engine and not subject to the OS overhead.
You
>> can in fact determine which sessions are tied to which clients with a
simple
>> onstat command.
>>
>>
>>>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.
>>
>>
>> And sometimes this will even remove the locks held by these sessions when
>> they are thusly killed.
>>
>>
>>>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.
>>
>>
>> Not quite. At the time you wrote this, a counter was incremented to
>> indicate that the lock threshold had been exceeded. However in current
>> releases Informix has dynamic locks, more locks are allocated as needed.
>>
>>
>>>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.
>>
>>
>> There indeed were issues with XPS 8.31 where restarting the instance
could
>> cause issues. Normally, those with support contracts would call the
'Down
>> Systems Group' which will answer the phone in under a minute and provide
>> sunset support (i.e. 24x7) until your engine is back on line. Those
without
>> support would call the same group and be gently scolded - and then
supported
>> in the same fashion. The last time I called Oracle support, I got a call
>> back three days later telling me to upgrade.
>>
>>
>>>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.
>>>
>>
>>
>> Which can be a lot of fun. But this is a moot point. It's a question of
>> sizing - if you size your rollback segments improperly - or choose the
wrong
>> one with which to perform your transaction - you are hosed. The lesson
is
>> that if you size things properly they work - whether they are Oracle or
>> Informix.
>>
>>
>>>Lack of diagnostic/monitoring tools
>>>
>>>No user friendly or graphical monitoring tools are available. This
>>>makes diagnosis and performance monitoring extremely difficult.
>>>
>>
>>
>> There is indeed no 'Informix' tool to compare to the Oracle Enterprise
>> Manager (did I get that right?). However there is a single standard
command
>> (onstat) to which all monitoring activity has been tied - not to mention
the
>> SQL interface to this same data. This can be much more effective than
>> waiting around for a GUI to load and display data which you then have to
>> drill into. I have to admit as an Oracle neophyte that I find the Oracle
>> GUI useful for handling tasks without any understanding of what lies
beneath
>> them.
>>
>>
>>>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.
>>>
>>
>>
>> I'm not sure to which problem you refer. Raw partitions are very simple
to
>> manage.
>>
>>
>>>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.
>>>
>>
>>
>> 'Standard' is either 'cooked files' (OS files) or raw devices. You get a
>> 15% performance improvement with raw devices. Oracle has the same
feature.
>> There is no similar benefit to raw devices under Oracle - I don't
remember
>> why at present. With a raw device Informix gets to control I/O to the
>> device itself instead of going through the Unix file system - which tends
to
>> not only slow things down, but opens a window of vulnerability - you
write
>> to disk and commit, the database writes it to disk and thinks it made it,
>> but the OS didn't get that far - so your 'committed transaction' wasn't.
>> Management of raw spaces is incredibly simple - especially under XPS
where
>> you can use wildcards to manage hundreds of such spaces at once.
>>
>>
>>>Informix can use cooked partitions, but it is not recommended by
>>>Informix for performane reasons.
>>
>>
>> True.
>>
>>
>>>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.
>>
>>
>> Oh right - I forgot about the resize thing - I've already mentioned the
Unix
>> filesystem issues. How can you resize a disk? If you buy an 80 GB
disk -
>> can you make it larger? So I guess you're right, under Informix you
can't
>> resize a raw disk, however you can add more of them and include them in
the
>> same dbspace (tablespace) so that the new space is usable by tables which
>> need to grow in that space. So in fact you can 'resize' a dbspace.
>>
>>
>>>Fast Loading
>>>
>>>Our data warehouse employs fast loading capabilities (bypassing ODBC)
>>>in order to upload Mainframe EBCDIC data.
>>
>>
>> That is a good thing. We are delighted that Oracle has come to the table
>> with a fast (direct path) loader. We hope someday that it shows the same
>> sort of performance as is capable with the Informix parallel loader. We
>> further hope that some day it will allow you to unload data in the same
>> fashion.
>>
>>
>>>This feature is particular in Informix to XPS; in Oracle it is
>>>standard functionality.
>>
>>
>> Actually HPL and the light append has been around since 7.x, I first used
it
>> around 1996.
>>
>>
>>>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.
>>
>>
>> Actually this is true with XPS. You do get a header to each row
indicating
>> the error, you have to trim the header to retrieve your original data.
>> Interesting - this would be a good feature request if not already done.
>> With 7.x and 9.x engines the error is written to a separate file than the
>> error message or you have the option of allowing it to load and then
picking
>> it out of a violation table later. For more on this topic visit
>> www.artentech.com/downloads.htm and look for the Informix Load FAQ.
>>
>>
>>>Oracle produces two output files when EBCDIC uploading - an error log,
>>>and an EBCDIC reject file - ready for reprocessing.
>>>
>>
>>
>> If you have an error, then there is a problem. In a DSS world, you don't
>> want to go chasing singleton rows, you want to do the whole thing again.
>>
>>
>>>We currently have to reprocess complete datasets, or reject complete
>>>datasets. With Oracle this would not be necessary.
>>
>>
>> This is ok when you are dealing with a small number of rows and can
afford
>> the luxury of debugging each one. When you are dealing with billions of
>> rows and you have a problem, you really don't want to get into the
details
>> of any individual row. I guess it's a question of how big you intend to
>> get.
>>
>>
>>>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.
>>
>>
>> Not sure what you're saying here. If you have 18 files to load, under
XPS
>> you can define them all as a single external table. You can define this
>> single external table across filename, filesystems, coservers(each
instance
>> in a cluster) and servers, you can define it with a single file statement
>> even (not 18) - it's pretty powerful. Furthermore, this file (or
external
>> table) is visible to the database - you don't have to go through sqlldr
to
>> get it into the database, you can treat it as a table and select columns
>> from it directly, or join it with other tables - whatever. It's really
>> cool. Even cooler - you can unload to it directly with a 'select
<columns>
>> from <> insert into <>' statement.
>>
>>
>>>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.
>>>
>>
>>
>> See above.
>>
>>
>>>Limitation comparisons
>>>
>>>Informix XPS
>>>18 characters for database objects
>>
>> Ok.
>>
>>
>>>256 byte maximum row size in Indexes
>>
>> You use indices? Are you in a data warehouse environment or an OLTP
>> environment? XPS can perform the hash join so quickly that indices are a
>> detriment, not a requirement. What you might consider a fast join
between
>> two tables with indices can be performed 100 times faster with a hash
join -
>> but then you need some memory to play these sorts of games. Have a look
at
>>
http://www7b.boulder.ibm.com/dmdd/zo...rticle/parker/
>> 0502parker.html for some examples.
>>
>>
>>>255 byte limit on varchar fields
>>
>> True, although you can go to text and have at least 2GB last time I
>> checked.
>>
>>
>>>SQL Restrictions -
>>>Cannot SELECT NULL
>>
>> What? Sure you can.
>>
>>
>>>No general DUAL table
>>
>> Create one. It's the same thing. I don't generally bother, I 'select
>> 'whatever' from systables where tabid=1'; It's the same thing.
>>
>>
>>>No MINUS operations
>>
>> True, that's a neat Oracle feature.
>>
>>
>>>No GROUP BY or Aggregation in SUBQUERY, NESTED QUERY, COMPOUND QUERY,
>>
>>
>>>EMBEDDED QUERY
>>
>> Haven't tried this. Never needed it.
>>
>>
>>>No Full OUTER JOIN
>>
>> Sure there is. Or do you mean that you want all rows from table 'a' and
'b'
>> even where neither intersect. Who's been dreaming up your data models?
You
>> need to clean up your data if this is a requirement. But then I guess
>> Informix doesn't do that particular join.
>>
>>
>>>No Implicit ROWID
>>
>> Not anymore, you can ask for one if you need it.
>>
>>
>>>XPS - No true database flatfile Import Export
>>
>