Re: database market share 2003

"Alexey Sonkin" <alexeis@grandvirtual.com> wrote in message news:c9i9qu$o8q$1@news.xmission.com... > > Goob, > > First, some more facts about DB2: > > 1. In DB2, the location of table indexes MUST be specified > at table creation. It's impossible to 'create index in....'. > Very unflexible > OK so what you're saying is true, but incomplete. What I think you are trying to say is that you cannot create detached indexes as an afterthought, after already creating the table with a place to put the index--otherwise the index goes in the same tablespace as the table. Yes, I agree this is a bit backward, and hopefully somebody at IBM will either enlighten us on why they have it this way, or they will eventually make it more Informix-like, which I think is the intuitive way. It is important, but again, not urgent for us to have this fixed. In the short-term we'll probably think ahead, and develop a discipline for thinking ahead when creating tables and knowing which indexes need to be detached and where they will go. > 2. DB2 installes into a fixed location on a hard drive. > This directory can't be 're-linked', because DB2 installer > ceated a lot of links to that location. > This has a severe effect on DB2 upgrades. > To switch the system from one DB2 version to another, > it is necessary first to stop DB2, then completely remove old > software, install new software into the same location > and only then start DB2. We've applied fixpacks without incident. I don't know why fixed drives are a problem for you, but it could be a problem in some environments. We also have only used version 8. > It takes few seconds to one minute with Informix to upgrade > from one version to another (in terms of system downtime), > and takes up to 30 minutes to do it with DB2. > Is DB2 a database for 24x7 systems? > Can't argue with you on that, we don't have Informix so we only know how long it takes for DB2. But it was easy, just run the fixpack and update the system. Pretty simple if you ask me. > 3. Cross-database queries within a single DB2 instance do not perform > as though they are local queries (like in Informix). > This will be a shock to those why normally have different > databases for loosely related applications with Informix and > makes occasional inter-database queries... > We don't have this problem. > See other comments below > > Alexy continues... > > What You say is that one can easily simulate a cluster on a single > machine - that is, run several DB2 instances on a single machine. > Not a big deal. Many XPS customers were doing the same when they > were running 32-bit version of XPS (when 64-bit XPS was not available) > on the monstrous 64-bit SMP hardware. > > What I say is that XPS support HYBRID fragmentation. > Table can be fragmented on a single node (vertical fragmentation) > by hash and at the same time fragmented by expression across > XPS co-servers (instances in DB2 terminology) - horizontal fragmentation. > How many XPS customers are out there? 1? 2? DB2 has partition keys and multi-demensional-cluster indexes and several other features to eliminate data skew, etc. etc. > > > 2. DB2 still operates with 32-bit rowid's. > > > This imposes a very hard limit (4 billion rows) on the table size. > > > > Thanks we'll stay below 4 billion rows. And probably a good idea > > to jump into 64-bit, and eliminate that 32-bit rowid problem. > > Are You sure, that 64-bit DB2 is using 64-bit ROWID? > I think, You are wrong on that > OK, well we'll stay below 4 billion rows, I promise on my mother's bible. > > > With Informix, this limit can be overcomed with intranode fragmentation. > > > The only option with DB2 is table partitioning across cluster nodes. > > > > Wrong. > > I can just repeat, that there is no intra-instance table fragmentation in > DB2. I'm pretty sure I'm not wrong on that > OK If I read correctly what you are saying is that somehow you need the ability to partition the data for a table in a one-instance-server-partion into some kind of data fragmentation ( partition ) scheme based on expression, or key. You can have multiple server partitions on one server in one instance and partition the data according to whatever you want. DB2 'Stinger' supposedly supports the informix-dbspace method you're referring to, but I haven't downloaded it yet to test it. This would be the one-partition-many-tablespace scheme if I'm guessing correctly. > > I am not a DB2 expert but I think your understanding of DB2 is > > a bit off. But I also get the feeling that somehow you feel Informix is > > better than DB2, but one has to ask just what is it better at? What > > features are really missing from DB2 that we just gotta have? I already > > know for our purposes we won't be using anything more than the basic > > features. > > Did You ever deal with LARGE systems? > YES > > > How many people are using inter-node partitioning for OLTP systems? > > > > I have no figures to report to you. > > Share-nothing architecture is good for DataWarehousing, not for OLTP. > Can't argue with that, other than to say, on DB2 you can have a mixed system, partition what you need, and stand-alone what you don't want to partition on one node. All within the same instance. > > > 3. DB2 with it's 'no checkpoint' architecture makes very > > > poor caching of DB writes: in DB2, only one modification > > > is possible (from different sessions) for a page before > > > it MUST be synced with the disk. > > > In a real Informix/Oracle OLTP environment a page can modified many > > > times from concurrent sessions before it goes to disk (like the > > > 'current' page in any accumulative table) > > > (Our typical disk write caching is >85%) > > > > > > > I don't know DB2 well enough to argue this point, but DB2 on our > > system is pretty darn fast, and a lot faster than SQL-Server, so > > fast I can't believe the difference. Guess that's all that really > > matters to us, and we are seeing fantastic performance with only a > > moderate attempt at tuning. But it does sound like DB2 has some room > > for improvement. We'll certainly take advantage of those improvements > > when they come, interesting that some of the companies we talked to > > using DB2 didn't seem to have any issues like this one. > > I believe, that DB2 is very fast in reporting, in index creation... > I'm talking about OLTP insert performance from parallel sessions > into a single table. > Yes, we have had great results with loading, very fast, especially compared to SQL-Server. > > > > > > > 4. DB2 doesn't support implicit type casting in SQL statements and > > > stored procedures. > > > > Is this something we need? Or is it a reflection that you have a bad > > data model and need to convert data types? Converting data does not > > necessarily have to be done in the SQL, it can be done outside the > > SQL with a good ETL tool, and we have one just for that purpose. > > > > > Can anybody imagine a headache of porting Oracle/Informix applic