Re: Informix limitations, should we be using Oracle?

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 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 >> >

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 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 > >>paral

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_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 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 b

Jean Sagi wrote: > As I said, I wish SQL-IDS has more features of XPS-SQL Log a case and submit a feature request. -- Ciao, The Obnoxious One "Ogni uomo mi guarda come se fossi una testa di cazzo"