VACUUM on 2.10

Nearest I can tell I am running Informix 2.10.03Y., or at least the core of it is. Looks like it might have been bought and repackaged. None the less, what if any is the SQL to perform a VACUUM on older informix dbs? Currently the staff is doing UDR everytime there is a major deletion. Thanks for your help. VD

VD1 wrote: > Nearest I can tell I am running Informix 2.10.03Y., or at least the Informix 2.10? I very much doubt it. Why do you think you are running such a version? > core of it is. Looks like it might have been bought and repackaged. > None the less, what if any is the SQL to perform a VACUUM on older > informix dbs? what is a VACUUM? > Currently the staff is doing UDR everytime there is a major deletion. And what is 'UDR'? Certainly not 'User Defined Routine' which is its current meaning in Informix parlance. By its context I cannot tell what that is. Please provide more information. A file listing of the installation directory would help us to either determine what database flavor you have or to suggest how you can find out. Art S. Kagel

> > Nearest I can tell I am running Informix 2.10.03Y., or at least the Run "onstat" dude! > > core of it is. Looks like it might have been bought and repackaged. > > None the less, what if any is the SQL to perform a VACUUM on older > > informix dbs? > what is a VACUUM? "update statistics", this is old Postgres (ingres?) speak. > > Currently the staff is doing UDR everytime there is a major deletion. > And what is 'UDR'? Certainly not 'User Defined Routine' which is its > current meaning in Informix parlance. By its context I cannot tell what > that is. > Please provide more information. A file listing of the installation > directory would help us to either determine what database flavor you have or > to suggest how you can find out.

This is a product that is at least 10 years old, and was licensed from Informix for redistribution. So I do not think they have updated anything, since that would require them to re-license Informix. Doing an ./sqlexec -V give me 2.10.03Y so that is what I am starting with. VACUUM is the SQL statement that will basically remove dead space caused from multiple updates and deletions. Compacting the database to its true size (size being file size as it is stored in the File System) a UDR is Unload Delete and Replace. Basically they export out all the data, build the table from scratch, and import the data back in, effectively removing any dead space. Art S. Kagel wrote: > VD1 wrote: > > Nearest I can tell I am running Informix 2.10.03Y., or at least the > > Informix 2.10? I very much doubt it. Why do you think you are running such > a version? > > > core of it is. Looks like it might have been bought and repackaged. > > None the less, what if any is the SQL to perform a VACUUM on older > > informix dbs? > > what is a VACUUM? > > > Currently the staff is doing UDR everytime there is a major deletion. > > And what is 'UDR'? Certainly not 'User Defined Routine' which is its > current meaning in Informix parlance. By its context I cannot tell what > that is. > > Please provide more information. A file listing of the installation > directory would help us to either determine what database flavor you have or > to suggest how you can find out. > > Art S. Kagel

VD1 wrote: > This is a product that is at least 10 years old, and was licensed from > Informix for redistribution. So I do not think they have updated > anything, since that would require them to re-license Informix. > > Doing an ./sqlexec -V give me 2.10.03Y so that is what I am starting > with. The full output would help since the product and copyright dates are listed. It's probably the old Informix Standard Engine (SE) circa 1989/1990. > VACUUM is the SQL statement that will basically remove dead space > caused from multiple updates and deletions. Compacting the database to > its true size (size being file size as it is stored in the File System) Got it. There IS no such command. Informix (whether SE, OL, or IDS) reserves the space vacated by deleted rows for reuse by new inserts. There is no built-in table reorganization command like a 'VACUUM' (although IBM has been asked to consider adding a built-in table reorg for IDS v10.50 or v11.00 the next two releases, but that won't help you). There is one possibility, I don't remember if SE had clustered indexes, if so, altering an index TO CLUSTER will cause the table's rows to be sorted into new space and unused space will be released. That is often faster than the alternative. The command is something like: ALTER INDEX indexname TO CLUSTER; It's usually best to select a primary key index or an index that's commonly used for sequential reporting in order to take advantage of the benefits of having the data sorted within the table (though clustering is not maintained as rows are inserted and updated). > a UDR is Unload Delete and Replace. Basically they export out all the > data, build the table from scratch, and import the data back in, > effectively removing any dead space. In SE that's the only other alternative. If you were running Informix Dynamic Server (IDS v7.xx, 9.xx, or 10.xx) there is another command, ALTER FRAGMENT...INIT.. which will reorganize a table's storage and release unneeded table space to the common space pool. However, since you are certainly not using IDS (the first version was IDS v7.10 not 2.10) it's not worth more than a mention. Art S. Kagel > Art S. Kagel wrote: > >>VD1 wrote: >> >>>Nearest I can tell I am running Informix 2.10.03Y., or at least the >> >>Informix 2.10? I very much doubt it. Why do you think you are running such >>a version? >> >> >>>core of it is. Looks like it might have been bought and repackaged. >>>None the less, what if any is the SQL to perform a VACUUM on older >>>informix dbs? >> >>what is a VACUUM? >> >> >>>Currently the staff is doing UDR everytime there is a major deletion. >> >>And what is 'UDR'? Certainly not 'User Defined Routine' which is its >>current meaning in Informix parlance. By its context I cannot tell what >>that is. >> >>Please provide more information. A file listing of the installation >>directory would help us to either determine what database flavor you have or >>to suggest how you can find out. >> >>Art S. Kagel > >

Art S. Kagel wrote: > VD1 wrote: >> This is a product that is at least 10 years old, and was licensed from >> Informix for redistribution. So I do not think they have updated >> anything, since that would require them to re-license Informix. >> >> Doing an ./sqlexec -V give me 2.10.03Y so that is what I am starting >> with. > > The full output would help since the product and copyright dates are > listed. It's probably the old Informix Standard Engine (SE) circa > 1989/1990. 2.10.03A would have been 1988 (+/- 1), I think; it would take a while to get to the Y fix pack, but definitely nearer 20 than 10 years old. >> VACUUM is the SQL statement that will basically remove dead space >> caused from multiple updates and deletions. Compacting the database to >> its true size (size being file size as it is stored in the File System) > > Got it. There IS no such command. Informix (whether SE, OL, or IDS) > reserves the space vacated by deleted rows for reuse by new inserts. > There is no built-in table reorganization command like a 'VACUUM' > (although IBM has been asked to consider adding a built-in table reorg > for IDS v10.50 or v11.00 the next two releases, but that won't help you). > > There is one possibility, I don't remember if SE had clustered indexes, > if so, altering an index TO CLUSTER will cause the table's rows to be > sorted into new space and unused space will be released. That is often > faster than the alternative. The command is something like: > > ALTER INDEX indexname TO CLUSTER; That works in SE as well as in OnLine and IDS. > It's usually best to select a primary key index or an index that's > commonly used for sequential reporting in order to take advantage of the > benefits of having the data sorted within the table (though clustering > is not maintained as rows are inserted and updated). > >> a UDR is Unload Delete and Replace. Basically they export out all the >> data, build the table from scratch, and import the data back in, >> effectively removing any dead space. > > In SE that's the only other alternative. If you were running Informix > Dynamic Server (IDS v7.xx, 9.xx, or 10.xx) there is another command, > ALTER FRAGMENT...INIT.. which will reorganize a table's storage and > release unneeded table space to the common space pool. However, since > you are certainly not using IDS (the first version was IDS v7.10 not > 2.10) it's not worth more than a mention. > > Art S. Kagel >> Art S. Kagel wrote: >>> VD1 wrote: >>> >>>> Nearest I can tell I am running Informix 2.10.03Y., or at least the >>> >>> Informix 2.10? I very much doubt it. Why do you think you are >>> running such a version? >>> >>>> core of it is. Looks like it might have been bought and repackaged. >>>> None the less, what if any is the SQL to perform a VACUUM on older >>>> informix dbs? >>> >>> what is a VACUUM? >>> >>>> Currently the staff is doing UDR everytime there is a major deletion. >>> >>> And what is 'UDR'? Certainly not 'User Defined Routine' which is its >>> current meaning in Informix parlance. By its context I cannot tell what >>> that is. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Jonathan Leffler wrote: > Art S. Kagel wrote: > >>VD1 wrote: >> >>>This is a product that is at least 10 years old, and was licensed from >>>Informix for redistribution. So I do not think they have updated >>>anything, since that would require them to re-license Informix. >>> >>>Doing an ./sqlexec -V give me 2.10.03Y so that is what I am starting >>>with. >> >>The full output would help since the product and copyright dates are >>listed. It's probably the old Informix Standard Engine (SE) circa >>1989/1990. > > > 2.10.03A would have been 1988 (+/- 1), I think; it would take a while to > get to the Y fix pack, but definitely nearer 20 than 10 years old. > > >>>VACUUM is the SQL statement that will basically remove dead space >>>caused from multiple updates and deletions. Compacting the database to >>>its true size (size being file size as it is stored in the File System) >> >>Got it. There IS no such command. Informix (whether SE, OL, or IDS) >>reserves the space vacated by deleted rows for reuse by new inserts. >>There is no built-in table reorganization command like a 'VACUUM' >>(although IBM has been asked to consider adding a built-in table reorg >>for IDS v10.50 or v11.00 the next two releases, but that won't help you). >> >>There is one possibility, I don't remember if SE had clustered indexes, >>if so, altering an index TO CLUSTER will cause the table's rows to be >>sorted into new space and unused space will be released. That is often >>faster than the alternative. The command is something like: >> >>ALTER INDEX indexname TO CLUSTER;> > > That works in SE as well as in OnLine and IDS. > > >>It's usually best to select a primary key index or an index that's >>commonly used for sequential reporting in order to take advantage of the >>benefits of having the data sorted within the table (though clustering >>is not maintained as rows are inserted and updated). >> >> >>>a UDR is Unload Delete and Replace. Basically they export out all the >>>data, build the table from scratch, and import the data back in, >>>effectively removing any dead space. >> >>In SE that's the only other alternative. If you were running Informix >>Dynamic Server (IDS v7.xx, 9.xx, or 10.xx) there is another command, >>ALTER FRAGMENT...INIT.. which will reorganize a table's storage and >>release unneeded table space to the common space pool. However, since >>you are certainly not using IDS (the first version was IDS v7.10 not >>2.10) it's not worth more than a mention. >> >>Art S. Kagel >> >>>Art S. Kagel wrote: >>> >>>>VD1 wrote: >>>> >>>> >>>>>Nearest I can tell I am running Informix 2.10.03Y., or at least the >>>> >>>>Informix 2.10? I very much doubt it. Why do you think you are >>>>running such a version? >>>> >>>> >>>>>core of it is. Looks like it might have been bought and repackaged. >>>>>None the less, what if any is the SQL to perform a VACUUM on older >>>>>informix dbs? >>>> >>>>what is a VACUUM? >>>> >>>> >>>>>Currently the staff is doing UDR everytime there is a major deletion. >>>> >>>>And what is 'UDR'? Certainly not 'User Defined Routine' which is its >>>>current meaning in Informix parlance. By its context I cannot tell what >>>>that is. >>> > Errrrr, the engine in question could be Illustra - both udr's and vacuum there... -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm