jwp81
asked on
improve performance of procedure
I need help improving performance on an export/import procedure.
I need to export all content of a table, delete all the content, and import it again, into the same table.
Exporting goes alright (not super fast but ok).
When trying to import my computer crashes!
I need to export all content of a table, delete all the content, and import it again, into the same table.
Exporting goes alright (not super fast but ok).
When trying to import my computer crashes!
http://database.ittoolbox.com/groups/technical-functional/db2-l/how-to-do-exportimport-with-table-has-lob-datatype-1181994
db2 "export to 'clob_test_export.txt' OF DEL LOBS TO '/data/bin/test/lobs/'
LOBFILE my_clob_export MODIFIED BY LOBSINFILE select ID,MY_CLOB from
clob_test"
That does not result in the expected output... and then:
In clob_test_export.txt it says:
1,"my_clob_export.001.0.72
2,"my_clob_export.001.7218
And ls displays:
-rw-r----- 1 db2inst1 db2iadm1 144309 Aug 3 08:44 my_clob_export.001
So - the export just handles it slightly different.
It has create a file "my_clob_export.001" - and here there are two clobs:
One from 0 with a length of 72184 bytes. And the other starts at position
72184 and is 72125 bytes long.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi jwp,
Is this an exercise or do you have a specific reason for wanting to dump/load the same table? If the table has been fragmented, you might consider a REORG on the table instead of dump/load.
Good Luck,
Kent
Is this an exercise or do you have a specific reason for wanting to dump/load the same table? If the table has been fragmented, you might consider a REORG on the table instead of dump/load.
Good Luck,
Kent
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Load and Import are completely different processes. Import is closer to a SQL INSERT statement in functionality and inner workings than it is to Load. And Load is a batch (offline) process that read/writes the tables directly without most of the overhead associated with SQL.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the 'load' was the key for my performance improvement, that I figured out on my own.
Hi jwp,
Sorry for not getting back sooner -- I was out of the office all afternoon.
There are actually quite a few differences. IBM has even gone to the trouble to list them "side by side" on their "infocenter" website.
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0004639.htm
A general rule (that's mostly accurate) is that, IMPORT treats the items that it's dealing with as a database and ensures that all of the items adhere to the rules of the database. LOAD treats the items as data and skips most of the single-row DBMS overhead. One great example is that when IMPORT inserts a row, it fires all necessary triggers and updates all the appropriate indexes before moving to the next row. LOAD does not fire the triggers and rebuilds the table's indexes after all rows are loaded. If you're dealing with a large volume of data the performance differences can be huge.
Kent
Sorry for not getting back sooner -- I was out of the office all afternoon.
There are actually quite a few differences. IBM has even gone to the trouble to list them "side by side" on their "infocenter" website.
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0004639.htm
A general rule (that's mostly accurate) is that, IMPORT treats the items that it's dealing with as a database and ensures that all of the items adhere to the rules of the database. LOAD treats the items as data and skips most of the single-row DBMS overhead. One great example is that when IMPORT inserts a row, it fires all necessary triggers and updates all the appropriate indexes before moving to the next row. LOAD does not fire the triggers and rebuilds the table's indexes after all rows are loaded. If you're dealing with a large volume of data the performance differences can be huge.
Kent
ASKER
My table contains 1000000 rows.
The export method I used:
db2 => export to myfile.ixf of ixf
db2 (cont.) => select * from person;
SQL3132W The character data in column "PASSWORD" will be truncated to size
"32700".
(this last SQL notification worries me!)
The import method I tried:
db2 import from myfile.ixf of ixf replace into person
My table creation statement:
create table Person ( person_id int not null, username varchar(30 ), password blob, first_name varchar(30), last_name varchar(30), home_street varchar(100), home_city varchar(30), home_zip_code int, home_state varchar(30), business_street varchar(100), business_city varchar(30), business_zip_code int, business_state varchar(30), home_phone varchar(10), business_phone varchar(10), email varchar(20), language varchar(12), PRIMARY KEY (person_id));