473,544 Members | 2,299 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

LOAD & IMPORT results in different disk space, occupied by a table: why?

Recently I became interested, - Are the data, bulk loaded in the table with
LOAD utility, consume the same disk space as loaded with IMPORT utility? The
answer turned out to be NOT !

Here is a nutshell description of the test. The testing was done at
"DB2/LINUX 8.2.3".

Tables for tests:
F4106 has 5203 rows, 32 columns.
F42199 has 1399252 rows, 245 columns.

Load command:
load client from '/home/share/tabXXXX.ixf' of ixf insert into
proddta.fXXXX NONRECOVERABLE"
Import command:
import from '/home/share/tabXXXX.ixf' of ixf insert into proddta.fXXXX

Between loads I used the following commands to truncate a table under
investigation and clear statistics:

ALTER TABLE PRODDTA.fXXXX ACTIVATE NOT LOGGED INITIALLY WITH EMPTY
TABLE;
RUNSTATS on table PRODDTA.fXXXX

After load I used the same RUNSTATS as above to get the "used pages"
counter (npages) in syscat.tables.

Here are the results:

syscat.tables, npages:
----------------------
TABLE IMPORT LOAD
------- --------- -------
F4106 372 401
F42199 694862 700326

One can see the disk space occupied by data, loaded with LOAD utility is
slightly greater then its counterpart.

If anybody understand this, please, explain.

Cheers,
--
Konstantin Andreev.
Jul 5 '06 #1
5 2159
"Konstantin Andreev" <pl**********@d atatech.ruwrote in message
news:e8******** **@dns.comcor.r u...
Recently I became interested, - Are the data, bulk loaded in the table
with LOAD utility, consume the same disk space as loaded with IMPORT
utility? The answer turned out to be NOT !

Here is a nutshell description of the test. The testing was done at
"DB2/LINUX 8.2.3".

Tables for tests:
F4106 has 5203 rows, 32 columns.
F42199 has 1399252 rows, 245 columns.

Load command:
load client from '/home/share/tabXXXX.ixf' of ixf insert into
proddta.fXXXX NONRECOVERABLE"
Import command:
import from '/home/share/tabXXXX.ixf' of ixf insert into proddta.fXXXX

Between loads I used the following commands to truncate a table under
investigation and clear statistics:

ALTER TABLE PRODDTA.fXXXX ACTIVATE NOT LOGGED INITIALLY WITH EMPTY
TABLE;
RUNSTATS on table PRODDTA.fXXXX

After load I used the same RUNSTATS as above to get the "used pages"
counter (npages) in syscat.tables.

Here are the results:

syscat.tables, npages:
----------------------
TABLE IMPORT LOAD
------- --------- -------
F4106 372 401
F42199 694862 700326

One can see the disk space occupied by data, loaded with LOAD utility is
slightly greater then its counterpart.

If anybody understand this, please, explain.

Cheers,
--
Konstantin Andreev.
The load utility loads the data in blocks (or pages) in the same format they
were exported, even if there are pages which are not completely full when
the data is exported. This is done for reasons of speed and efficiency.

The import utility processes the data by row and performs an insert for each
row, so that it can use all the sequential space in target table without
leaving any unused space on a page.
Jul 5 '06 #2
Mark A wrote:
>Here are the results:

syscat.tables, npages:
----------------------
TABLE IMPORT LOAD
------- --------- -------
F4106 372 401
F42199 694862 700326
The load utility loads the data in blocks (or pages) in the same format they were exported, even if there are pages which are not completely full when the data is exported.
Can not be true. One reason and one confirmation:

- The intermediate data format (DEL,IXF) intended to be interoperable. This allows moving data between different platforms and on-disk structures. It (data format) by definition does not contain page and block information. Thus LOAD operation must reconstruct any data blocks specifically for target platform.

- I just checked - the source table F42199, when exported, occupied: npages=1399252, fpages=1399430. If you are right, LOAD'ed table would occupy the same number of pages, but it occupies just half of them. This is because VALUE COMPRESSION option for target table. Thus, the data pages for pages *were* reconstructed by LOAD.

Cheers,
--
Konstantin Andreev.
Jul 6 '06 #3
"Konstantin Andreev" <pl**********@d atatech.ruwrote in message
news:e8******** **@dns.comcor.r u...
>
Can not be true. One reason and one confirmation:

- The intermediate data format (DEL,IXF) intended to be interoperable.
This allows moving data between different platforms and on-disk
structures. It (data format) by definition does not contain page and block
information. Thus LOAD operation must reconstruct any data blocks
specifically for target platform.

- I just checked - the source table F42199, when exported, occupied:
npages=1399252, fpages=1399430. If you are right, LOAD'ed table would
occupy the same number of pages, but it occupies just half of them. This
is because VALUE COMPRESSION option for target table. Thus, the data pages
for pages *were* reconstructed by LOAD.

Cheers,
--
Konstantin Andreev.
Let me amend my response to be more accurate.

The load utility loads data a page at a time. It takes the data from the
input file and formats the pages to be loaded. New rows are not placed on
existing pages.

The import utility does regular SQL inserts, and therefore may use existing
space on pages that already have some rows, but where the page is not full.
Jul 6 '06 #4
Mark A wrote:
>Thus LOAD operation must reconstruct any data blocks specifically for target platform.
Let me amend my response to be more accurate.

The load utility loads data a page at a time. It takes the data from the input file and formats the pages to be loaded. New rows are not placed on existing pages.

The import utility does regular SQL inserts, and therefore may use existing space on pages that already have some rows, but where the page is not full.
Sounds reasonable. Let me a bit expand the proposed scenario, to check my understanding.

- Some time the data row in sequence can't be fit on the currently constructed page, thus page fired to disk by LOAD utility and forgotten. Meanwhile among the rows to come could be encountered one, short enough to be placed on the fired page, but LOAD have to place it on the new page. I also expect that if the all rows have equal lengthes then page counts used by LOAD and IMPORT will also by equal.

Please, correct me, if I flounder about.

Thank you,
--
Konstantin Andreev.
Jul 7 '06 #5
"Konstantin Andreev" <pl**********@d atatech.ruwrote in message
news:e8******** **@dns.comcor.r u...
Sounds reasonable. Let me a bit expand the proposed scenario, to check my
understanding.

- Some time the data row in sequence can't be fit on the currently
constructed page, thus page fired to disk by LOAD utility and forgotten.
Meanwhile among the rows to come could be encountered one, short enough to
be placed on the fired page, but LOAD have to place it on the new page. I
also expect that if the all rows have equal lengthes then page counts used
by LOAD and IMPORT will also by equal.

Please, correct me, if I flounder about.

Thank you,
--
Konstantin Andreev.
Data loaded via the load utility is formatted into pages by the load utility
an then stored in the table a page at a time. This is done outside of the
normal SQL engine. Existing pages are not used for adding the data, and only
new pages are created. It has nothing to do with whether rows will fit in
existing pages, it is done that way for speed. Because the SQL engine is not
used by the load utility, insert triggers will not fire for new rows added
to the table.

Imports are done by submitting regular inserts through the SQL engine and
therefore the rows may end up being inserted on existing pages where there
is space available. Insert triggers are fired, and all data is logged just
like normal SQL.

Therefore it is possible that the import uses less total space than load.
Jul 7 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
4498
by: Otto | last post by:
Hello to all I have a problem how to write correctly a PHP string. I did the following import with the help of PHPMyAdmin: Nombre d'enregistrements insérés : 364 (traitement: 0.0553 sec.) requête SQL: LOAD DATA LOCAL INFILE '/tmp/phpVfO8r8' INTO TABLE `fncid`
1
5132
by: Gh! | last post by:
How can I see how much disk space is being occupied by an InnoDB table? Looking into /var/lib/mysql doesn't seem to work anymore for InnoDB tables. Please tell also if there is a way to see how much space is occupied by a particular row or column of a table. Thanks
13
6577
by: rdudejr | last post by:
Hi all, I hardly ever make a post unless I am having a very purplexing issue, so this one should be good... I am trying to do a load against a database on an AIX server into a DB2 v9.1 database, using SAN for storage. The table has a few CLOBs (smallish clobs but we are storing XML data in non-native format). Here is the load command I...
4
8391
hemantbasva
by: hemantbasva | last post by:
We have designed an aspx page having five ajax tab in it. the data of first four are designed on page whereas for the fifth tab it renders a user control named MYDOMAIN. in the tab container's even onactivetabindexchanged we have called a method loadtabpanel() which is defined in javascript in same page.the problem is it sometime give the message...
0
7437
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7625
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7389
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7717
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5928
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5306
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3427
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3421
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
993
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.