IBM Support

dbimport of a database can use much more disk space than expected

Troubleshooting


Problem

You may observe that a dbimport of a database consumes much more disk space in a dbspace than expected. This may even lead to an interruption of the dbimport with insufficient disk space error.

Symptom

When you want to import a database that was exported with dbexport without the "-ss" option you may run into insufficient disk space errors in the target dbspace or you may observe much more disk space used for the imported database compared to the disk space used on the source side.

Cause

When a dbexport of a database is taken you have the option to include server-specific information by using the "-ss" option. For a create table statement this server-specific information would include an EXTENT SIZE and NEXT SIZE clause that is based on the actual table usage information.

If no server-specific information is generated (the dbexport ran without the "-ss" option) the create table statement is written without this extent size information.

When a dbimport is run the create table statement is read from the sql file generated during export. When an EXTENT SIZE and NEXT SIZE clause is found it is applied as it is.

If there is no such clause the EXTENT SIZE and NEXT SIZE will be internally calculated by the dbimport utility. For rows that have a varying length the maximum row size is multiplied by the number of rows to determine this information.

In case of LVARCHAR() columns without a specific storage information the default column length used to calculate the maximum row size is 2048 bytes. Especially in this case you may find a huge discrepancy in the maximum row size used compared to the actual average row size depending on how much data is really stored in the columns of variable length.

In these cases the calculated maximum storage for the table may be way larger than the actual used one in the database that was exported.

Environment


Diagnosing The Problem

When you are trying to do a dbimport from a dbexport taken without the "-ss" option, you have at least the same amount of pages free in the target dbspace as are used by the database in the source dbspace and there is a substantial difference in the average row length that is actually used to the maximum row length this may apply.

Resolving The Problem

When you can do the dbexport again you may try to specify the "-ss" option which should result in about the same usage of disk space.

When you can no longer do the dbexport again (or it would be too time consuming or for other reasons infeasible) you can try to manually add the EXTENT SIZE and NEXT SIZE size clauses to the most space consuming tables.

In versions of Informix starting with 11.70.xC9 and 12.10.xC4 you can use the new option "-d" (default) for the dbimport which will skip the calculation of the extent sizes for a dbexport taken without the "-ss" option which will therefore use the default for EXTENT SIZE and NEXT SIZE when creating the table. To prevent extent fragmentation in this case you may want to limit the object creation in the target dbspace to the dbimport.

[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"--","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF022","label":"OS X"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"11.5;11.7;12.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
16 June 2018

UID

swg21683229