RE: Whatcha' wanta have?????

Hi, Madison, It would be nice to see the following two features in IDS9.6: 1. Ability to manage FIRST/NEXT extent size for index. Extent size is a very big problem for big tables, containing LVARCHAR. For such tables, index extent size, calculated by Informix automatically, becomes too small, and the table might easily run out of extents for the index partition while it has just few extents in the data partition 2. Ability to PURGE all data from a table in a single unlogged operation. Current workaround is to drop the table and create it again, which is sometimes not possible. PURGE should be considered DDL, not DML statement, and should be non-recoverable. PURGE should also release all unused table extents except the initial extent. Best regards, Alexey Sonkin -----Original Message----- From: Madison Pruet [mailto:mpruet@comcast.net] Sent: Friday, February 06, 2004 6:41 PM To: informix-list@iiug.org Subject: Whatcha' wanta have????? As a mild diversion from the IDS-DB2 conversion thread, its time for one of my more favorite exercises. ;-) We are nearing the end of the coding cycle for IDS 9.5. We've got a whole bunch of really cool stuff in place and - well - its time to get input from you guys as to what you want to see in the 9.6 release. Now, I know that everyone's favorite thing is going to be "marketing", but I'm in development. So I need to talk features and functionality. So feel free to send them on in. Just an FYI - I'll be away for a while and won't be able to get email via my comcast email address. But, I'll be following the newsgroup rather closely. Also, next week I'll be in some planning meeting. So getting responses back fairly quickly would really help. Thanks M.Pruet sending to informix-list

Alexey Sonkin wrote: > It would be nice to see the following two features in IDS9.6: > > 1. Ability to manage FIRST/NEXT extent size for index. > Extent size is a very big problem for big tables, containing > LVARCHAR. For such tables, index extent size, calculated > by Informix automatically, becomes too small, and the table > might easily run out of extents for the index partition > while it has just few extents in the data partition A well known request - not unreasonable. > 2. Ability to PURGE all data from a table in a single > unlogged operation. > Current workaround is to drop the table and create it again, > which is sometimes not possible. > PURGE should be considered DDL, not DML statement, > and should be non-recoverable. > PURGE should also release all unused table extents except > the initial extent. PURGE TABLE - aka TRUNCATE TABLE... Reasonable request - mostly. I agree it is probably DDL rather than DML - it certainly isn't a regular DELETE operation, and should use a different privilege, and ALTER TABLE might be OK, though a new specific TRUNCATE privilege would be better, I think. I know Oracle doesn't allow recovery of its TRUNCATE TABLE, but then it doesn't allow any other DDL operation to be recovered from. However, Informix does provide recovery of every operation within a transaction -- why would you want to lose the ability in this one statemen? You can drop a table in a transaction and rollback the TX and the table is still there - why should TRUNCATE TABLE, which does less work, be any different? [Background: we had an intense discussion about this, and DB2 in particular does not want to implement a recoverable TRUNCATE TABLE. The XPS implementation of TRUNCATE TABLE does not allow recovery. However, it seems to me inherently bad to make one statement different from all the rest. In particular, the XPS design is such that you can't do anything else with the truncated table in the same transaction. I would foresee that there would be occasions where I wanted to delete, say, 80% of the records, but to reseed the table with the remaining 20% of the records - in the same TX. I'd expect to do that by unloading to a temp table (cross-loading?) the stuff to retain, truncating the main table, and then cross-loading the material from the temp table back into the main table, all in a single transaction. Basically, I lost this argument - and I'm still being a sore loser about it, because I think it is fundamentally b******t that we can recover from dropping a table and not from truncating it. However, here's an external contrary view, so I'm seeking to know why it is not critical to recover from a truncated table, even though you can recover from a dropped table.] -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Jonathan Leffler wrote: > Basically, I lost this argument - and I'm still being a sort loser > about it, because I think it is fundamentally b******t that we > can recover from dropping a table and not from truncating it. > However, here's an external contrary view, so I'm seeking to know why > it is not critical to recover from a truncated table, even though you > can recover from a dropped table.] I agree with your point of view that IDS has established practice where everything is logged. I also agree that some operations probably should not be logged, or only optionally. If you introduce a a few baroque options like truncate table with no log; drop table with no log; drop index with no log; and so on, then everyone's a winner?

Interesting that you would consider it DDL. We TRUNCATE tables on a daily basis as a command with SQL-Server, but keep the table without destroying and then re-creating it with DDL. Don't really see why it must be DDL, and a simple command to truncate the table. Bummer with SQL-Server is that it's logged, so it can really be a time-consuming task on large tables. You might want to add to the request that it have a LOGGED or NO LOG option as well. ( SQL-Server cannot turn off logging completely, I don't think DB2 can either without that goofy alter table command ) Just my .02 USD Tim "Jonathan Leffler" <jleffler@earthlink.net> wrote in message news:402717D1.2090508@earthlink.net... > Alexey Sonkin wrote: > > It would be nice to see the following two features in IDS9.6: <...> > PURGE TABLE - aka TRUNCATE TABLE... > > Reasonable request - mostly. I agree it is probably DDL rather than > DML - it certainly isn't a regular DELETE operation, and should use a > different privilege, and ALTER TABLE might be OK, though a new > specific TRUNCATE privilege would be better, I think. > > I know Oracle doesn't allow recovery of its TRUNCATE TABLE, but then > it doesn't allow any other DDL operation to be recovered from. > > However, Informix does provide recovery of every operation within a > transaction -- why would you want to lose the ability in this one > statemen? You can drop a table in a transaction and rollback the TX > and the table is still there - why should TRUNCATE TABLE, which does > less work, be any different? > >