I need help regarding LOAD-command with BLOBs.
I notice very poor performance while loading a table which contains large BLOBs. While tracing the OS, I only have about 15MB/sec I/O-speed. But in other cases I have normal I/O-rates > 200MB/sec. (e.g. creating DB-backups, creating tablespaces, copy files etc.).
I already tried to use different extent-sizes, page-sizes and some LOAD-parameters like "DISK_PARALLELISM", "SORT BUFFER", "DATA BUFFER", "SAVECOUNT" etc. to improve LOAD-performance but I was not able to speed up the LOAD. The target table is empty and has no indices defined. Source and target are located on separate RAID-volumes with dedicated physical drives.
Any hints to improve LOAD performance or to further investigate the problem are very welcome.
Thank you in advance.
Ronny
My setup:
- SLES 11 SP2
- DB2 9.7 FP8
- Raid:
Source: 2 dedicated Disks, Raid-0 (Striping) with 64 KB stripe-size
Target: 2 dedicated Disks, Raid-0 (Striping) with 64 KB stripe-size
- File-System: EXT3
- Tablespace-Definition:
CREATE LARGE TABLESPACE LOB IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY DATABASE USING (FILE 'xxx' 20000000) PREFETCHSIZE AUTOMATIC BUFFERPOOL IBMDEFAULTBP DROPPED TABLE RECOVERY OFF;
- LOAD-Definition:
LOAD FROM xxx OF IXF LOBS FROM xxx MODIFIED BY lobsinfile METHOD N (ID, CONTENT, SEGMENTGROUPID, SEGMENTNUMBER) SAVECOUNT 1 ROWCOUNT 250 MESSAGES /xxx/CONTENT.log REPLACE INTO CONTENT (ID, CONTENT, SEGMENTGROUPID, SEGMENTNUMBER) NONRECOVERABLE;