dbexport and dbimport behave very slowly

Hi. Did somebody ever heard of this: dbexport and dbimport are extremely slow only if the tables contain columns of the type TEXT. I made some tests: 1. UNLOAD a table with TEXT-columns in it: 16 seconds 2. UNLOAD same table with TEXT-columns replaced by CHAR(255): 13 seconds 3. DBEXPORT a table with TEXT-columns in it: 188 seconds 4. DBEXPORT same table with TEXT-columns replaced by CHAR(255): 24 seconds You see: dbexport of tables with TEXT-columns lasts nearly 8 times longer than without. Against unload it´s more than 10 times longer. To say it in other word: Instead of 2 hours the dbexport of the database will last more than 20 hours. Also will the dbimport. And there are only 2 GB in the database. This behaviour occurs with IDS2000 9.20UC1A and IDS2000 9.21UC2 with Reliant Unix 5.43C4001 on a Siemens RM600. Same tests with IDS2000 9.2x and Solaris 7 or Linux (SuSE 6.3) didn't show this bug. Because I need dbexport/dbimport rather urgently for reorganizing the extends of the tables I hope I get some help here. TIA Reinhard

Try my dbexport/dbimport replacement utilities, myexport/myimport, in the package myexport available from the IIUG Software Repository. They are fully compatible with dbexport & dbimport (ie the output from one can be loaded with the other) with some nice features. If you download the package also get my package utils2_ak and Jonathan Leffler's package, sqlcmd, as myexport/myimport uses features of these packages to do its thing. Art S. Kagel Reinhard Habichtsberg wrote: > > Hi. > > Did somebody ever heard of this: dbexport and dbimport are extremely slow > only if the tables contain columns of the type TEXT. > > I made some tests: > 1. UNLOAD a table with TEXT-columns in it: 16 seconds > 2. UNLOAD same table with TEXT-columns replaced by CHAR(255): 13 seconds > 3. DBEXPORT a table with TEXT-columns in it: 188 seconds > 4. DBEXPORT same table with TEXT-columns replaced by CHAR(255): 24 seconds > > You see: dbexport of tables with TEXT-columns lasts nearly 8 times longer than without. > Against unload it´s more than 10 times longer. To say it in other word: Instead of 2 hours > the dbexport of the database will last more than 20 hours. Also will the dbimport. And > there are only 2 GB in the database. > > This behaviour occurs with IDS2000 9.20UC1A and IDS2000 9.21UC2 with > Reliant Unix 5.43C4001 on a Siemens RM600. > > Same tests with IDS2000 9.2x and Solaris 7 or Linux (SuSE 6.3) didn't show this bug. > > Because I need dbexport/dbimport rather urgently for reorganizing the extends of the > tables I hope I get some help here. > > TIA > Reinhard

Thank you for your answer. Although a have to ask another question. With dbexport/dbimport AFAIK it is possible to reorganize your tables from many extents (we had nearly 200 at particular tables) to only one in a single operation. That is the real reason for what we need dbexpot/dbimport. Is this possible with your myexport/myimport, too? Reinhard Art S. Kagel <kagel@bloomberg.net> schrieb in im Newsbeitrag: 3A075684.488D58CC@bloomberg.net... > Try my dbexport/dbimport replacement utilities, myexport/myimport, in the > package myexport available from the IIUG Software Repository. They are > fully compatible with dbexport & dbimport (ie the output from one can be > loaded with the other) with some nice features. If you download the package > also get my package utils2_ak and Jonathan Leffler's package, sqlcmd, as > myexport/myimport uses features of these packages to do its thing. > > Art S. Kagel > > Reinhard Habichtsberg wrote: > > > > Hi. > > > > Did somebody ever heard of this: dbexport and dbimport are extremely slow > > only if the tables contain columns of the type TEXT. > > > > I made some tests: > > 1. UNLOAD a table with TEXT-columns in it: 16 seconds > > 2. UNLOAD same table with TEXT-columns replaced by CHAR(255): 13 seconds > > 3. DBEXPORT a table with TEXT-columns in it: 188 seconds > > 4. DBEXPORT same table with TEXT-columns replaced by CHAR(255): 24 seconds > > > > You see: dbexport of tables with TEXT-columns lasts nearly 8 times longer than without. > > Against unload it´s more than 10 times longer. To say it in other word: Instead of 2 hours > > the dbexport of the database will last more than 20 hours. Also will the dbimport. And > > there are only 2 GB in the database. > > > > This behaviour occurs with IDS2000 9.20UC1A and IDS2000 9.21UC2 with > > Reliant Unix 5.43C4001 on a Siemens RM600. > > > > Same tests with IDS2000 9.2x and Solaris 7 or Linux (SuSE 6.3) didn't show this bug. > > > > Because I need dbexport/dbimport rather urgently for reorganizing the extends of the > > tables I hope I get some help here. > > > > TIA > > Reinhard

Yes and more accurately. Myexport uses my dbschema replacement utility, myexport from utils2_ak, to generate the schema file and by default uses the -a option which calculates actual pages used for each table and ajusts the EXTENT and NEXT sizes under some conditions and inserts comments suggesting other modifications to be made manually. Myschema also supports other options to allow for growth in the EXTENT and/or NEXT size parameters so that you can generate your own schema file for myimport/dbimport to use including the adjustments. In this way even if you originally created the tables with a 16 page initial extent and they have grown to 200,000 pages and you can use myimport's -p (parallel load) option your tables will not be interleaved after loading. Dbimport depends on extent compression to accomplish this, myexport/myimport leaves nothing to chance. Look for an updated myexport shortly, the myimport script in the current release gets confused with some combinations of arguments and is not forgiving. New November 6 release will fix that, to be uploaded soon. Art S. Kagel Reinhard Habichtsberg wrote: > > Thank you for your answer. Although a have to ask another question. > With dbexport/dbimport AFAIK it is possible to reorganize your tables from > many extents (we had nearly 200 at particular tables) to only one in a > single operation. That is the real reason for what we need dbexpot/dbimport. > Is this possible with your myexport/myimport, too? > > Reinhard > > Art S. Kagel <kagel@bloomberg.net> schrieb in im Newsbeitrag: 3A075684.488D58CC@bloomberg.net... > > Try my dbexport/dbimport replacement utilities, myexport/myimport, in the > > package myexport available from the IIUG Software Repository. They are > > fully compatible with dbexport & dbimport (ie the output from one can be > > loaded with the other) with some nice features. If you download the package > > also get my package utils2_ak and Jonathan Leffler's package, sqlcmd, as > > myexport/myimport uses features of these packages to do its thing. > > > > Art S. Kagel > > > > Reinhard Habichtsberg wrote: > > > > > > Hi. > > > > > > Did somebody ever heard of this: dbexport and dbimport are extremely slow > > > only if the tables contain columns of the type TEXT. > > > > > > I made some tests: > > > 1. UNLOAD a table with TEXT-columns in it: 16 seconds > > > 2. UNLOAD same table with TEXT-columns replaced by CHAR(255): 13 seconds > > > 3. DBEXPORT a table with TEXT-columns in it: 188 seconds > > > 4. DBEXPORT same table with TEXT-columns replaced by CHAR(255): 24 seconds > > > > > > You see: dbexport of tables with TEXT-columns lasts nearly 8 times longer than without. > > > Against unload it´s more than 10 times longer. To say it in other word: Instead of 2 hours > > > the dbexport of the database will last more than 20 hours. Also will the dbimport. And > > > there are only 2 GB in the database. > > > > > > This behaviour occurs with IDS2000 9.20UC1A and IDS2000 9.21UC2 with > > > Reliant Unix 5.43C4001 on a Siemens RM600. > > > > > > Same tests with IDS2000 9.2x and Solaris 7 or Linux (SuSE 6.3) didn't show this bug. > > > > > > Because I need dbexport/dbimport rather urgently for reorganizing the extends of the > > > tables I hope I get some help here. > > > > > > TIA > > > Reinhard