informix dba routines
Hello,
Our dba just left us for good. and I need your
generous help!
I want to increase the performance of our
informix online dynamic server 7.31. I read from
book that recreating indexes periodically helps.
Should I extract all create index script from the
database and run it (how) or should I unload the
data for each table, drop table, recreate table
and then reload data to each table? I heard that
recreating a table will cause the index to be
recreated automatically. We have around 300
tables in the database, is there an easy way to
construct the script to unload data, drop table
and recreate table?
One more question, when our database was created,
we didn't add a contraint name for 'not null'
column. After I do dbexport, I found that most of
the not null columns have a system generated
constraint name, but not all of them. I ran into
problem when I import the database to another
development server with the error message saying
that the constraint name is already used.
Obviously, the development machine was creating a
constraint name for the left out "not null "
columns and a duplicate system generated
constraint name was created(one from development
machine, and one from the server where export
occured.)
Is it better to manually add constraint name for
all 'not null' columns? Again, what is the best
way to do this?
Thanks a lot!!!
Sent via Deja.com http://www.deja.com/
Before you buy.
First, check to ensure that UPDATE STATISTICS is run as often as is
possible. This is your first great performance improver. (Look in the
Performance Guide for guidelines on how to run). I would build a script
that builds the necessary SQL with information from the database
"syscatalogs", and run it every night (if time windows permit!, on a
regular basis if not).
Next, simply CLUSTERING indexes will effectively rebuild them,
reclaiming space and physically removing deleted entries. Sometimes on
large tables it is actually faster to drop and recreate the index.
Again, you can build a script to do this for indexes on large/often
queried indexes.
I would REALLY hesitate to automate a process that drops tables on a
regular basis. I would certainly ENSURE that my backup routine was
working VERY well before attempting this type of routine on a production
system.
As for constraint names, the best thing I have found is to use an
automated tool such as ER/Win to define my database model. This not only
gives you a graphical representation of the data model, but can also
become a data dictionary if used fully. It can take substantial effort
to create the first time, but will save tons of time over the life of
the system.
As a temporary solution, write a little 'sed' script (I assume you are
on UNIX), to simply remove the default constraint names from your schema
before trying to use.
Hope this helps.
juliehl@my-deja.com wrote:
> Hello,
> Our dba just left us for good. and I need your
> generous help!
> I want to increase the performance of our
> informix online dynamic server 7.31. I read from
> book that recreating indexes periodically helps.
> Should I extract all create index script from the
> database and run it (how) or should I unload the
> data for each table, drop table, recreate table
> and then reload data to each table? I heard that
> recreating a table will cause the index to be
> recreated automatically. We have around 300
> tables in the database, is there an easy way to
> construct the script to unload data, drop table
> and recreate table?
>
> One more question, when our database was created,
> we didn't add a contraint name for 'not null'
> column. After I do dbexport, I found that most of
> the not null columns have a system generated
> constraint name, but not all of them. I ran into
> problem when I import the database to another
> development server with the error message saying
> that the constraint name is already used.
> Obviously, the development machine was creating a
> constraint name for the left out "not null "
> columns and a duplicate system generated
> constraint name was created(one from development
> machine, and one from the server where export
> occured.)
> Is it better to manually add constraint name for
> all 'not null' columns? Again, what is the best
> way to do this?
> Thanks a lot!!!
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
juliehl@my-deja.com wrote:
>
> Hello,
> Our dba just left us for good. and I need your
> generous help!
Our condolences.
> I want to increase the performance of our
> informix online dynamic server 7.31. I read from
Run the recommended suite of UPDATE STATISTICS commands as described in
the Performance Guide or get one of the excellent utilities available
from the IIUG Software Repository that implement the protocol
automatically, including my own dostats which is included in the
package utils2_ak. Running this frequently can improve performance
noticably.
> book that recreating indexes periodically helps.
Not really.
> Should I extract all create index script from the
> database and run it (how) or should I unload the
If neccessary you can use the Informix utility dbschema to generate
a schema of a database or a single table and extract the create index
statements from there. Alternatively, utils2_ak also contains my
dbschema replacement utility, myschema, which does almost everything
that dbschema does and much more, including placing the create index
and ALTER TABLE ADD CONSTRAINT... statements to a separate file if a
second filename is specified on the commandline. In addition,
myschema does NOT generate those annoying autogenerated CONSTRAINT
names for NOT NULL constraints like dbschema does simply because they
cause name clash problems when porting the schema to another server.
> data for each table, drop table, recreate table
> and then reload data to each table? I heard that
> recreating a table will cause the index to be
> recreated automatically. We have around 300
Only if you have a schema for the table that contains the create
index statements. Another alternative to rebuild indexes, much
faster than CLUSTERing the indexes, is to disable each index then
enable it again the engine will rebuilt the newly enabled index
automatically then. BTW you can only have one clustered index on
a table and clustering causes the table's data to be physically
sorted by that indexes key to speed sequential access by that key.
This causes ALTER INDEX .... TO CLUSTER to be rather slow to just
rebuild an index.
> tables in the database, is there an easy way to
> construct the script to unload data, drop table
> and recreate table?
My package utils4_ak contains a set of AWK scripts that read dbschema
or myschema output and generate various usefull SQL and ksh scripts
and are easily used as templates for other such scripts.
> One more question, when our database was created,
> we didn't add a contraint name for 'not null'
> column. After I do dbexport, I found that most of
> the not null columns have a system generated
> constraint name, but not all of them. I ran into
> problem when I import the database to another
> development server with the error message saying
> that the constraint name is already used.
> Obviously, the development machine was creating a
> constraint name for the left out "not null "
> columns and a duplicate system generated
> constraint name was created(one from development
> machine, and one from the server where export
> occured.)
> Is it better to manually add constraint name for
> all 'not null' columns? Again, what is the best
> way to do this?
See my note above about using myschema instead or in addition.
Myschema also has a flag (-l) which causes it to output a schema which
is completely compatible with dbexport/dbimport and can be used to
replace the script that dbexport created in the <databasename>.exp
directory if you wrote to disk. If you wrote to tape you can use the
dbimport -f schema_file option to use the myschema script instead.
Also look into my package myexport which provides a script that works
with myschema and Jonathan Leffler's sqlcmd package (also available
from the repository) to emulate dbexport and dbimport but without
locking the database.
Have fun.
Art S. Kagel