------=_Part_23421_18612639.1211314092821
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
You would:
dbaccess mydatabase -
> unload to 'mytable_unload.unl' select * from mytable;
> truncate table mytable;
> load from 'mytable_unload.unl' insert in to mytable;
> ^D
> ^D
The truncate will only work if you have a later version of Informix that
sup****ts that command (it's a VERY good idea to always post your version
and
platform information when you post). If not, you'll have to exit
dbaccess,
run a 'dbschema -d mydatabase -t mytable -ss mytable.sql' to get a schema
of
the table, drop it, recreate it with the schema, possibly recreate foreign
keys on other tables that reference 'mytable', and then reload the data.
(FYI - you can use my dbschema replacement utility, myschema, with its -F
option that also outputs those referencing foreign keys - dbschema won't
do
it! Myschema is part of the package utils2_ak which you can download from
the IIUG Software Repository or the Oninit WEB Site - www.oninit.com)
or you can reload the file using dbload which may avoid long transaction
and
lock table overflow problems if the table is large.
You may want to increase the table's NEXT SIZE to reduce future
fragmentation.
However, the best (and usually fastest) way to reorg a fragmented table is
to use:
ALTER FRAGMENT ON mytable INIT IN some_dbspace;
The named dbspace can be the same one the table already lives in or a
different one (or even a fragmentation expression). This command only
needs
sufficient logical log space to avoid a long transaction rollback and all
indexes and constraints remain in place during and after the reorg.
Have you checked that the table is badly fragmented first? Unless the
table
has several dozen fragments, it is unlikely that fragmentation is having a
major performance impact. If you are having severe performance problems
then it is more likely that your engine is not properly configured or
tuned
for your applications. I would recommend that you contact one of the
several excellent consultancies that work with Informix users for a
comprehensive server health check. I will shamelessly mention Oninit, the
consultancy I work for, as one, but there are several others out there
that
a GOOGLE search for 'Informix consultant' will reveal. I only avoid
naming
them 'cause I'm getting old and if I forget one I'll insult some good
friend
or other. 8^)
Art S. Kagel
Oninit
On Tue, May 20, 2008 at 3:38 PM, Gentian Hila <genti.tech@[EMAIL PROTECTED]
>
wrote:
> I was told that to improve performance sometimes is a good idea to
> ex****t and im****t a table instead of sizing it (it's easier I've been
> told)
>
> How do I ex****t/im****t table in IDS 9.40? (table not database)
>
> Thanks
> _______________________________________________
> Informix-list mailing list
> Informix-list@[EMAIL PROTECTED]
> http://www.iiug.org/mailman/listinfo/informix-list
>
------=_Part_23421_18612639.1211314092821
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
You would:<br><br>dbaccess mydatabase - <br>> unload to
'mytable_unload.unl' select * from mytable;<br>> truncate table
mytable;<br>> load from 'mytable_unload.unl' insert in to
mytable;<br>> ^D<br>
> ^D<br><br>The truncate will only work if you have a later version of
Informix that sup****ts that command (it's a VERY good idea to always
post your version and platform information when you post). If not,
you'll have to exit dbaccess, run a 'dbschema -d mydatabase -t
mytable -ss mytable.sql' to get a schema of the table, drop it,
recreate it with the schema, possibly recreate foreign keys on other
tables that reference 'mytable', and then reload the data.
(FYI - you can use my dbschema replacement utility, myschema, with its -F
option that also outputs those referencing foreign keys - dbschema
won't do it! Myschema is part of the package utils2_ak which you
can download from the IIUG Software Repository or the Oninit WEB Site - <a
href="http://www.oninit.com">www.oninit.com</a>)<br>
<br>or you can reload the file using dbload which may avoid long
transaction and lock table overflow problems if the table is
large.<br><br>You may want to increase the table's NEXT SIZE to reduce
future fragmentation. <br>
<br>However, the best (and usually fastest) way to reorg a fragmented
table is to use:<br><br>ALTER FRAGMENT ON mytable INIT IN
some_dbspace;<br><br>The named dbspace can be the same one the table
already lives in or a different one (or even a fragmentation
expression). This command only needs sufficient logical log space to
avoid a long transaction rollback and all indexes and constraints remain in
place during and after the reorg.<br>
<br>Have you checked that the table is badly fragmented first?
Unless the table has several dozen fragments, it is unlikely that
fragmentation is having a major performance impact. If you are
having severe performance problems then it is more likely that your engine
is not properly configured or tuned for your applications. I would
recommend that you contact one of the several excellent consultancies that
work with Informix users for a comprehensive server health check. I
will shamelessly mention Oninit, the consultancy I work for, as one, but
there are several others out there that a GOOGLE search for 'Informix
consultant' will reveal. I only avoid naming them 'cause
I'm getting old and if I forget one I'll insult some good friend
or other. 8^)<br>
<br>Art S. Kagel<br>Oninit<br><br><div class="gmail_quote">On Tue, May 20,
2008 at 3:38 PM, Gentian Hila <<a
href="mailto:genti.tech@[EMAIL PROTECTED]
">genti.tech@[EMAIL PROTECTED]
>>
wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid
rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
I was told that to improve performance sometimes is a good idea to<br>
ex****t and im****t a table instead of sizing it (it's easier I've
been<br>
told)<br>
<br>
How do I ex****t/im****t table in IDS 9.40? (table not database)<br>
<br>
Thanks<br>
_______________________________________________<br>
Informix-list mailing list<br>
<a href="mailto:Informix-list@[EMAIL PROTECTED]
">Informix-list@[EMAIL PROTECTED]
><br>
<a href="http://www.iiug.org/mailman/listinfo/informix-list"
target="_blank">http://www.iiug.org/mailman/listinfo/informix-list</a><br>
</blockquote></div><br>
------=_Part_23421_18612639.1211314092821--
|
