Buenos días.
Tengo un erp hecho en informix r4gl bajo unix sco.
la tabla del histórico de movimientos de almacén, cada cierto tiempo va
alcanzando los 2gb de tamaño por lo que hay que descargar fechas anteriores
y compactarla, esto lo hago 2 veces al año.
la última vez que lo hice, al ir a compactarla (lo hago mediante un alter
table de un campo decimal 3,0 a decimal 4.0 y despues de 4.0 a 3.0) se me
llenó el filesystem donde está la base de datos, y no pudo hacerlo, con lo
que lo que hice fue descargar todos los registros de la tabla, borrar la
tabla, crear la tabla y los 7 índices y cargar la tabla.
el rendimiento desde entonces fué pésimo, programas de consulta de esa tabla
que tardaban 2 minutos, se pegaban 8 horas, procesos en los que intervenía
la tabla tardaban mucho más ... pero no todos.
hice un bcheck y no me dio nada raro, borre los indices y los volví a crear,
pero sigo bastante parecido.
se me ha pasado algo por alto?
alguien me puede orientar sobre lo que puede pasar?
Gracias
Good morning.
i have a erp maked in informix r4gl under unix sco.
the table of warehouse moviments, with the work is growing up and when is
taking 2gb i unload and delete the older dates, this i do 2 times a year.
last time i done that, when i tried to compact (with an alter table) the
filesystem where the dd bb is in, was full and i couldn´t do that, then i
unloaded all records, drop table, create table, create index and load the
records.
after that the programs that touch that table, are too slow. programs of 2
minutes are now in 8 hours of proccess. the proccess are slower but not all
proccess.
i made a bcheck and was ok. i drop the indexes and create, but not is
better.
what i have forgotten?
someone can help me? if possible in spanish.
thanks and sorry for my poor english.

Did you run update statistics after you reloaded the table???
did you create all the needed indexes on the table???
Before you do this take a explain from the program so you know what
the good qry exec plan is.
after you reload the table, recreate the indexes and run update stats
take another explain and compare.



Superboer.

On Jul 24, 2:47 am, "Juanito" <n...@cual.es> wrote:
> Buenos días.
> Tengo un erp hecho en informix r4gl bajo unix sco.
> la tabla del histórico de movimientos de almacén, cada cierto tiempo va
> alcanzando los 2gb de tamaño por lo que hay que descargar fechas anteriores
> y compactarla, esto lo hago 2 veces al año.
> la última vez que lo hice, al ir a compactarla (lo hago mediante un alter
> table de un campo decimal 3,0 a decimal 4.0 y despues de 4.0 a 3.0) se me
> llenó el filesystem donde está la base de datos, y no pudo hacerlo, con lo
> que lo que hice fue descargar todos los registros de la tabla, borrar la
> tabla, crear la tabla y los 7 índices y cargar la tabla.
> el rendimiento desde entonces fué pésimo, programas de consulta de esa tabla
> que tardaban 2 minutos, se pegaban 8 horas, procesos en los que intervenía
> la tabla tardaban mucho más ... pero no todos.
> hice un bcheck y no me dio nada raro, borre los indices y los volví a crear,
> pero sigo bastante parecido.
> se me ha pasado algo por alto?
> alguien me puede orientar sobre lo que puede pasar?
> Gracias
> Good morning.
> i have a erp maked in informix r4gl under unix sco.
> the table of warehouse moviments, with the work is growing up and when is
> taking 2gb i unload and delete the older dates, this i do 2 times a year.
> last time i done that, when i tried to compact (with an alter table) the
> filesystem where the dd bb is in, was full and i couldn´t do that, theni
> unloaded all records, drop table, create table, create index and load the
> records.
> after that the programs that touch that table, are too slow. programs of 2
> minutes are now in 8 hours of proccess. the proccess are slower but not all
> proccess.
> i made a bcheck and was ok. i drop the indexes and create, but not is
> better.
> what i have forgotten?
> someone can help me? if possible in spanish.
> thanks and sorry for my poor english.

Your English is better than my Spanish, so...

The reason that ALTER TABLE doesn't compact the table is that since
7.30 IDS has been altering tables in-place. Unlike earlier versions
which created a new table with the new schema and copied the rows from
the old table to the new one which WOULD compact the table, in-place
alters do not. To compact the table you can do one of the following:
1- unload, drop, recreate, reload as you have been doing. As you
noted, in versions prior to 9.40 you'll have a problem doing this as
the table's export file exceeds 2GB. There are ways around that.
2- alter an index TO CLUSTER (or if it's already clustered alter
it TO NOT CLUSTER then back TO CLUSTER).
3- ALTER FRAGMENT FOR TABLE <tablename> INIT IN <dbspace or
fragmentation expression>; This can be used to reorganize and compact
a table even into the same dbspace in which it already resides. This
is the most efficient method but takes lots of logical log space
unless you first alter the table to RAW first to turn off logging.

Your final question, why performance after the reload is poor: You
have to run UPDATE STATISTICS on the table and all of its indexes
after the reload (and on any stored procedures that reference the
table as well). You should follow the recommendations in the
Performance Guide and in John Miller III's paper on the subjec (URL
below). If you have an optimized version of IDS (later than 7.31xD2
or 9.40xC3) you can take advantage of the optimizations suggested in
John's paper. An alternative to crafting the UPDATE STATS statements
yourself is to get my dostats utility from the IIUG Software
Repository (www.iiug.org/software get the package utils2_ak) which
automatically implements these recommendations including detecting
your server version - which by the way you should always post with
your question.

John Miller's paper is at:

http://www-128.ibm.com/developerwork...203miller.html

Art S. Kagel

Hiciste el UPDATE STATISTICS? El no haberlo hecho podría causar la baja
perfeormance.

HTH

Mario R. Canto
Juanito escribió:
> Buenos días.
> Tengo un erp hecho en informix r4gl bajo unix sco.
> la tabla del histórico de movimientos de almacén, cada cierto tiempo va
> alcanzando los 2gb de tamaño por lo que hay que descargar fechas anteriores
> y compactarla, esto lo hago 2 veces al año.
> la última vez que lo hice, al ir a compactarla (lo hago mediante un alter
> table de un campo decimal 3,0 a decimal 4.0 y despues de 4.0 a 3.0) se me
> llenó el filesystem donde está la base de datos, y no pudo hacerlo,con lo
> que lo que hice fue descargar todos los registros de la tabla, borrar la
> tabla, crear la tabla y los 7 índices y cargar la tabla.
> el rendimiento desde entonces fué pésimo, programas de consulta de esa tabla
> que tardaban 2 minutos, se pegaban 8 horas, procesos en los que intervenía
> la tabla tardaban mucho más ... pero no todos.
> hice un bcheck y no me dio nada raro, borre los indices y los volví acrear,
> pero sigo bastante parecido.
> se me ha pasado algo por alto?
> alguien me puede orientar sobre lo que puede pasar?
> Gracias
> Good morning.
> i have a erp maked in informix r4gl under unix sco.
> the table of warehouse moviments, with the work is growing up and when is
> taking 2gb i unload and delete the older dates, this i do 2 times a year.
> last time i done that, when i tried to compact (with an alter table) the
> filesystem where the dd bb is in, was full and i couldn´t do that, then i
> unloaded all records, drop table, create table, create index and load the
> records.
> after that the programs that touch that table, are too slow. programs of 2
> minutes are now in 8 hours of proccess. the proccess are slower but not all
> proccess.
> i made a bcheck and was ok. i drop the indexes and create, but not is
> better.
> what i have forgotten?
> someone can help me? if possible in spanish.
> thanks and sorry for my poor english.
>
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>