Dbexport/Dbimport problems

>> >> tony@bilpin.co.uk (Tony Desborough) Writes.. >> >> Hi, >> >> We have a production database that has been live for about a >> year. Application performance on this database has been fine. >> >> As the number of extents for some tables was becoming excessive, >> a full dbexport/dbimport was carried out on the database. Although >> no problems were experienced during the export/import, once the >> system was live again, the performance had degraded *significantly*. >> After a days use, the performance is still awful. I had expected >> the performance to increase. >> >> Has anyone had similar experiences following a dbimport, or can >> anyone explain what the problem may be. >> >> INFORMIX-OnLine Version 4.10.UE1 Hi Tony, You`ve just hit upon one of my favourite themes, the dbexport dbimport feature of Informix OnLine. I do manage, on a regular basis to improve performance at different installations using these tools. As an OnLine database grows, the number of extents will increase (fragmentation) and consequently the amount of time required by OnLine to access data increases. In later OnLine versions I think in 6.0+ this can be turned to some advantage (parallel queries) but in your version it just makes the system run sloooooowwww!!. The problem is exagerated when the system calalog tables (systables, syscolumns...) are effected. The solution to fragmentation is to dbexport/dbimport the database and remember to control the <dbname>.sql file that dbexport produces so that your newly created/dbimported database has adequate next sizes defined for, in particular, the system catalog tables. You can define where this control file goes by calling dbex/import using the -f <filname> option. If you're exporting to disk the file apears in your <dbname>.exp directory. Edit this file so that before any create table commands you... ALTER TABLE systables MODIFY NEXT SIZE <nnn> ; ALTER TABLE syscolumns MODIFY NEXT SIZE <nnn> ; ALTER TABLE systabauth... Ascertain the required value for <nnn> by using the tbcheck -pT command for any of these sys... tables that appear in the tbcheck -ce report. tbcheck -ce ( reports on tables having more than 8 extents ) tbcheck -pT <dbname>:<owner>.<tabname> to get Tablespace usage. Dbex/import, on your version, however have a drawback and that is that the full schema is not exported. What`s missing is locking information and extents information. This is easily overcome, in fact I have a script somewhere that automatically inserts this info into a <dbname>.sql file. I need to make it pretty and test it and then I will post it to the group. Running update statistics is always the first recommendation for improving performance, you could also check if any previous row level locking is intact, run tbcheck -ce and RTFM (the manuals for 4.x may not be brilliant but, in general the later ones are). At this point I should take the chance to recommend the excellent and concise... Informix OnLine Performance Tuning by Elizabeth Suto Published by Prentice Hall ISBN 0-13-124322-5 I`m tempted to write a review for what its worth and hopefully Kerry will put the recommendation in the FAQ. If you need further information please feel free to contact me direct. Hope this helps, Tom Hogarty Standard Disclaimers Apply SNI Paderborn Germany Tel +49-52518-11602 +49-5254-65553($HOME) e-mail <hogarty.pad@sni.de>