Re: Re: Informix limitations, should we be using Oracle?

For me too.. ;) but: select null from systables; gives: 201: A syntax error has occurred. I have : Informix Dynamic Server Version 9.30.HC2W8 HP-UX B.11.00 U 9000/856 I could bet the same hapen in 9.4 BTW: select getnull() from systables; Works... ugly but it works... (it's a stored procedure)... and I can live with it. Chucho! -----Original Message----- From: Paul Watson <paul@oninit.com> To: informix-list@iiug.org Date: Mon, 17 Nov 2003 23:36:32 +0000 Subject: Re: Informix limitations, should we be using Oracle? 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_results.asp?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

If you cast null then it should work OK you need 9.x select null::INT from systables; Jean Sagi wrote: > > For me too.. ;) > > but: > > select null > from systables;> > gives: > > 201: A syntax error has occurred.> > I have : > Informix Dynamic Server Version 9.30.HC2W8 > HP-UX B.11.00 U 9000/856 > > I could bet the same hapen in 9.4 > > BTW: > > select getnull() > from systables; > > Works... ugly but it works... (it's a stored procedure)... and I can live with it. > > Chucho! > > -----Original Message----- > From: Paul Watson <paul@oninit.com> > To: informix-list@iiug.org > Date: Mon, 17 Nov 2003 23:36:32 +0000 > Subject: Re: Informix limitations, should we be using Oracle? > > 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_results.asp?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@@

On Tue, 18 Nov 2003 14:41:05 +0000, Paul Watson <paul@oninit.com> wrote: >If you cast null then it should work OK you need 9.x > >select null::INT >from systables; > Same here: select null::char(1) from systables JWC

Of course unless the column is null then you need select eric.null from eric John Carlson wrote: > > On Tue, 18 Nov 2003 14:41:05 +0000, Paul Watson <paul@oninit.com> > wrote: > > >If you cast null then it should work OK you need 9.x > > > >select null::INT > >from systables; > > > > Same here: > > select null::char(1) > from systables > > JWC -- 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 #