Load performance on AIX w/ SAN

[rdudejr]

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 am using:

db2 "load from loadset1 of del modified by chardel| coldel& insert
into testschema.testtable nonrecoverable data buffer 240000
disk_parallelism 32"

Now I am loading about 440 rows/second, which to me is abysmally
slow. The tablespaces I am loading into have 8 containers and I
believe there are at least 30 disks on the SAN that the data
eventually lives on. So needless to say there should be all the I/O
power available to load this data.

My data file I am loading from lives on the same filesystem (and
therefore same logical volume), so I am aware that IO headseek issues
could be the problem. However, the way I understand SAN is that all
the disks are working together, so SAN takes care of the data
allocation so that headseek does not become a problem.

I guess what I am asking is a twofold question...first (and the most
appropriate for this forum), is my load command most appopriate for
what I am trying to do?

Second, does the output of topas below indicate that the disks are
really only working at 20% on average? Or is it a lie since I am
running on SAN? Anyone with simular experience please help!

If what is below is indeed correct, why is the load not using all the
disk and/or CPU?
Topas Monitor for host: server1 EVENTS/QUEUES FILE/TTY
Mon Nov 10 16:09:09 2008 Interval: 2 Cswitch 8071
Readch 1520.1K
Syscall 5308
Writech 1976.3K
CPU User% Kern% Wait% Idle% Reads 168
Rawin 0
cpu1 69.5 6.5 0.0 24.0 Writes 889
Ttyout 624
cpu2 1.0 4.0 7.5 87.5 Forks 0
Igets 0
cpu4 0.5 4.0 21.5 74.0 Execs 1
Namei 24
cpu0 0.5 3.0 0.0 96.5 Runqueue 1.0
Dirblk 0
cpu5 0.0 3.0 25.5 71.5 Waitqueue 0.0
cpu3 0.0 2.0 15.5 82.5
cpu6 0.0 2.0 15.0 83.0
PAGING MEMORY
cpu7 0.0 5.5 12.5 82.0
Faults 1032 Real,MB 24576

Steals 390 % Comp 39.9
Network KBPS I-Pack O-Pack KB-In KB-Out PgspIn 0 %
Noncomp 13.3
en6 0.8 1.0 1.5 0.1 0.7 PgspOut 0 %
Client 2.2
en5 0.0 0.0 0.0 0.0 0.0 PageIn 1267
lo0 0.0 0.0 0.0 0.0 0.0 PageOut 888
PAGING SPACE
Sios 2155
Size,MB 12032
Disk Busy% KBPS TPS KB-Read KB-Writ %
Used 0.0
hdisk17 18.0 944.0 236.0 518.0 426.0 NFS (calls/sec) %
Free 100.0
hdisk27 14.5 1.0K 237.0 456.0 584.0 ServerV2 0
hdisk42 14.0 780.0 195.0 384.0 396.0 ClientV2 0
Press:
hdisk22 13.5 876.0 219.0 424.0 452.0 ServerV3 0 "h"
for help
hdisk12 12.0 892.0 211.5 414.0 478.0 ClientV3 0 "q"
to quit
hdisk37 11.0 900.0 214.0 392.0 508.0
hdisk7 11.0 950.0 237.5 480.0 470.0
hdisk32 6.0 874.0 218.5 464.0 410.0
hdisk29 2.0 512.0 2.0 512.0 0.0
hdisk24 2.0 512.0 2.0 512.0 0.0
hdisk9 1.5 256.0 1.0 256.0 0.0
hdisk19 1.0 256.0 1.0 256.0 0.0
hdisk16 0.5 6.0 1.5 0.0 6.0
hdisk15 0.0 0.0 0.0 0.0 0.0
hdisk14 0.0 0.0 0.0 0.0 0.0
hdisk11 0.0 2.0 0.5 0.0 2.0
hdisk1 0.0 0.0 0.0 0.0 0.0
hdisk20 0.0 0.0 0.0 0.0 0.0

Name PID CPU% PgSp Owner
db2sysc 1839138 10.7 0.6 tvpi01
db2sysc 574364 0.1 0.5 tvpi01
db2sysc 1188050 0.1 0.6 tvpi01
db2sysc 663620 0.1 0.7 tvpi01
db2sysc 1225124 0.1 0.6 tvpi01
db2sysc 290888 0.1 0.7 tvpi01
db2sysc 1552616 0.1 0.6 tvpi01
db2sysc 671780 0.1 0.6 tvpi01
db2sysc 1339656 0.1 0.6 tvpi01
db2sysc 1511780 0.1 0.6 tvpi01
db2sysc 1323334 0.1 0.6 tvpi01
db2sysc 1679524 0.1 0.6 tvpi01
topas 1564806 0.1 3.5 tvpi01
db2sysc 983414 0.1 0.6 tvpi01
db2sysc 143704 0.1 0.7 tvpi01
db2sysc 1216702 0.1 0.6 tvpi01
db2sysc 975286 0.0 0.6 tvpi01
db2sysc 872862 0.0 0.7 tvpi01
db2sysc 962762 0.0 0.6 tvpi01
db2sysc 1180054 0.0 0.6 tvpi01

[rdudejr]

After making my post I realize how difficult the topas output is to
read so let me post the highlights:

8 CPUs, 1 working at 65%, the all hovering near 1%, wait% from 5-25%.

20+disks, 10 at 10-20% busy, each reading and writing at around 500kb/
s, the rest hardly doing any work at all.

Memory 24GB, 55% free, 0% pageing space used
page in/out 1267/888 respectively.

Hope that helps!

[Ian]

rd*****@gmail.com wrote:

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 am using:

db2 "load from loadset1 of del modified by chardel| coldel& insert
into testschema.testtable nonrecoverable data buffer 240000
disk_parallelism 32"

Now I am loading about 440 rows/second, which to me is abysmally
slow. The tablespaces I am loading into have 8 containers and I
believe there are at least 30 disks on the SAN that the data
eventually lives on. So needless to say there should be all the I/O
power available to load this data.

Can you post any more information, such as table DDL, tablespace
definition, maybe a few sample rows, etc?

Have you tried the load letting DB2 choose its own defaults for
data buffer / disk_parallelism settings?

[rdudejr]

On Nov 10, 6:16*pm, Ian <[e-mail]:

[e-mail] wrote:

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 am using:

db2 "load from loadset1 of del modified by chardel| coldel& insert
into testschema.testtable nonrecoverable data buffer 240000
disk_parallelism 32"

Now I am loading about 440 rows/second, which to me is abysmally
slow. *The tablespaces I am loading into have 8 containers and I
believe there are at least 30 disks on the SAN that the data
eventually lives on. *So needless to say there should be all the I/O
power available to load this data.

Can you post any more information, such as table DDL, tablespace
definition, maybe a few sample rows, etc?

Have you tried the load letting DB2 choose its own defaults for
data buffer / disk_parallelism settings?- Hide quoted text -

- Show quoted text -

I have tried to allow the database to choose its own load values. I
experience simular performance.

I cant post exact DDL/sample rows for security reasons, but I will do
my best to describe:

The table itself is a parent table to other child tables ( but this
should not matter with a load). It has about 25 columns, all of which
are varchar, bigint, char, or date, except for 2, which are CLOB
(around 3000 characters/clob).

It is linked to 3 tablespaces, one for LOBs (32k pagesize), one for
indexes (4k pagesize), one for normal data (4k pagesize). All 3
tablespaces have 8 containers and live on the same filesystem which is
mounted on a VG that is tied to a SAN with 30+ disk (probably around
100 disk, but I dont know exactly as I am not the SAN adm).

Is that enough info to help?

Also a new discovery! After loading 15mil records to this table, I
did a few table scans and got the disk usage in topas to report up to
80% busy, so it seems that the load really is not using the full
capacity of the disk (as the load is using about 15% of the disk).
Also, the tablescan uses all 8 CPUs uniformially, where the load is
only using 1 or 2 CPUs.

[Patrick Finnegan]

On Nov 11, 12:07*am, rdudejr <[e-mail]:

On Nov 10, 6:16*pm, Ian <[e-mail]:

[e-mail] wrote:

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 am using:

db2 "load from loadset1 of del modified by chardel| coldel& insert
into testschema.testtable nonrecoverable data buffer 240000
disk_parallelism 32"

Now I am loading about 440 rows/second, which to me is abysmally
slow. *The tablespaces I am loading into have 8 containers and I
believe there are at least 30 disks on the SAN that the data
eventually lives on. *So needless to say there should be all the I/O
power available to load this data.

Can you post any more information, such as table DDL, tablespace
definition, maybe a few sample rows, etc?

Have you tried the load letting DB2 choose its own defaults for
data buffer / disk_parallelism settings?- Hide quoted text -

- Show quoted text -

I have tried to allow the database to choose its own load values. *I
experience simular performance.

I cant post exact DDL/sample rows for security reasons, but I will do
my best to describe:

The table itself is a parent table to other child tables ( but this
should not matter with a load). *It has about 25 columns, all of which
are varchar, bigint, char, or date, except for 2, which are CLOB
(around 3000 characters/clob).

It is linked to 3 tablespaces, one for LOBs (32k pagesize), one for
indexes (4k pagesize), one for normal data (4k pagesize). *All 3
tablespaces have 8 containers and live on the same filesystem which is
mounted on a VG that is tied to a SAN with 30+ disk (probably around
100 disk, but I dont know exactly as I am not the SAN adm).

Is that enough info to help?

Also a new discovery! *After loading 15mil records to this table, I
did a few table scans and got the disk usage in topas to report up to
80% busy, so it seems that the load really is not using the full
capacity of the disk (as the load is using about 15% of the disk).
Also, the tablescan uses all 8 CPUs uniformially, where the load is
only using 1 or 2 CPUs.

Get the DB2 write i/o speeds for transaction log data and application
data from the database snapshot and work out the number of writes per
millisecond.

E.G.

Log write time (sec.ns) = 1730.000000004
Number write log IOs = 1035798

(1730.000000004 * 1000) / 1035798 = 1.67 milliseconds per write.

Direct writes = 40354784
Direct write elapsed time (ms) = 959854

959854 / 40354784 = 0.024 milliseconds per write.

Ask the storage admins for the i/o stats from the san and compare.

Where are the DB2 transaction logs located and how big are they?

[Patrick Finnegan]

On Nov 11, 8:15*am, Patrick Finnegan <[e-mail]>
wrote:

On Nov 11, 12:07*am, rdudejr <[e-mail]:

On Nov 10, 6:16*pm, Ian <[e-mail]:

[e-mail] wrote:
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 am using:

db2 "load from loadset1 of del modified by chardel| coldel& insert
into testschema.testtable nonrecoverable data buffer 240000
disk_parallelism 32"

Now I am loading about 440 rows/second, which to me is abysmally
slow. *The tablespaces I am loading into have 8 containers and I
believe there are at least 30 disks on the SAN that the data
eventually lives on. *So needless to say there should be all the I/O
power available to load this data.

Can you post any more information, such as table DDL, tablespace
definition, maybe a few sample rows, etc?

Have you tried the load letting DB2 choose its own defaults for
data buffer / disk_parallelism settings?- Hide quoted text -

- Show quoted text -

I have tried to allow the database to choose its own load values. *I
experience simular performance.

I cant post exact DDL/sample rows for security reasons, but I will do
my best to describe:

The table itself is a parent table to other child tables ( but this
should not matter with a load). *It has about 25 columns, all of which
are varchar, bigint, char, or date, except for 2, which are CLOB
(around 3000 characters/clob).

It is linked to 3 tablespaces, one for LOBs (32k pagesize), one for
indexes (4k pagesize), one for normal data (4k pagesize). *All 3
tablespaces have 8 containers and live on the same filesystem which is
mounted on a VG that is tied to a SAN with 30+ disk (probably around
100 disk, but I dont know exactly as I am not the SAN adm).

Is that enough info to help?

Also a new discovery! *After loading 15mil records to this table, I
did a few table scans and got the disk usage in topas to report up to
80% busy, so it seems that the load really is not using the full
capacity of the disk (as the load is using about 15% of the disk).
Also, the tablescan uses all 8 CPUs uniformially, where the load is
only using 1 or 2 CPUs.

Get the DB2 write i/o speeds for transaction log data and application
data from the database snapshot and work out the number of writes per
millisecond.

E.G.

Log write time (sec.ns) * * * * = 1730.000000004
Number write log IOs * * * * * * * * = 1035798

(1730.000000004 * 1000) / 1035798 * * * = 1.67 milliseconds per write.

Direct writes * * * * * * * * * * * * * * =40354784
Direct write elapsed time (ms) * *= 959854

959854 / 40354784 * * * * * * * * * * * * * ** = 0.024 milliseconds per write.

Ask the storage admins for the i/o stats from the san and compare.

Where are the DB2 transaction logs located and how big are they?

If the SAN is EMC raid 5 and the write times are slow set the
following registry variables at the instance level.

DB2_PARALLEL_IO *

Set the following instance parameters.

Enable intra-partition parallelism INTRA_PARALLEL ON
Maximum query degree of parallelism MAX_QUERYDEGREE ANY

Set the following database parameters.

Default query optimization class DFT_QUERYOPT 5
Degree of parallelism DFT_DEGREE ANY

Switch on concurrent i/o at tablespace level for all tablespaces in
the database.

db2 ALTER TABLESPACE USERSPACE1 NO FILE SYSTEM CACHING

And if the load is still slow get the details for the san config.

E.G.

SAN is DMX-3.
RAID is RAID 5(3+1).
Data Block size is 256KB
Parity Block size is 256
Logical stripe(block) size is 768KB(256*3)

Recreate the tablspaces as DMS using a page size larger than 4k and
make the extent size the same as the san stripe size.

Something like this.

create regular tablespace xxxx
PAGESIZE 16K
MANAGED BY DATABASE
USING (FILE '/home/data/xxxx/DMSCONTAINERS/CONT_01' 2 G,
FILE '/home/data/xxxx/DMSCONTAINERS/CONT_02' 2 G,
FILE '/home/data/xxxx/DMSCONTAINERS/CONT_03' 2 G,
FILE '/home/data/xxxx/DMSCONTAINERS/CONT_04' 2 G)
BUFFERPOOL yyyy
EXTENTSIZE 768
PREFETCHSIZE 192
OVERHEAD 8.6
TRANSFERRATE 0.2
NO FILE SYSTEM CACHING;

[rdudejr]

On Nov 11, 4:48*am, Patrick Finnegan <[e-mail]>
wrote:

On Nov 11, 8:15*am, Patrick Finnegan <[e-mail]>
wrote:

On Nov 11, 12:07*am, rdudejr <[e-mail]:

On Nov 10, 6:16*pm, Ian <[e-mail]:

[e-mail] wrote:
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 intoa
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 am using:

db2 "load from loadset1 of del modified by chardel| coldel& insert
into testschema.testtable nonrecoverable data buffer 240000
disk_parallelism 32"

Now I am loading about 440 rows/second, which to me is abysmally
slow. *The tablespaces I am loading into have 8 containers and I
believe there are at least 30 disks on the SAN that the data
eventually lives on. *So needless to say there should be all the I/O
power available to load this data.

Can you post any more information, such as table DDL, tablespace
definition, maybe a few sample rows, etc?

Have you tried the load letting DB2 choose its own defaults for
data buffer / disk_parallelism settings?- Hide quoted text -

- Show quoted text -

I have tried to allow the database to choose its own load values. *I
experience simular performance.

I cant post exact DDL/sample rows for security reasons, but I will do
my best to describe:

The table itself is a parent table to other child tables ( but this
should not matter with a load). *It has about 25 columns, all of which
are varchar, bigint, char, or date, except for 2, which are CLOB
(around 3000 characters/clob).

It is linked to 3 tablespaces, one for LOBs (32k pagesize), one for
indexes (4k pagesize), one for normal data (4k pagesize). *All 3
tablespaces have 8 containers and live on the same filesystem which is
mounted on a VG that is tied to a SAN with 30+ disk (probably around
100 disk, but I dont know exactly as I am not the SAN adm).

Is that enough info to help?

Also a new discovery! *After loading 15mil records to this table, I
did a few table scans and got the disk usage in topas to report up to
80% busy, so it seems that the load really is not using the full
capacity of the disk (as the load is using about 15% of the disk).
Also, the tablescan uses all 8 CPUs uniformially, where the load is
only using 1 or 2 CPUs.

Get the DB2 write i/o speeds for transaction log data and application
data from the database snapshot and work out the number of writes per
millisecond.

E.G.

Log write time (sec.ns) * * * * = 1730.000000004
Number write log IOs * * * * * * * * = 1035798

(1730.000000004 * 1000) / 1035798 * * * = 1.67 milliseconds perwrite.

Direct writes * * * * * * * * * * * * * * = 40354784
Direct write elapsed time (ms) * *= 959854

959854 / 40354784 * * * * * * * * * * * * * * * = 0.024 milliseconds per write.

Ask the storage admins for the i/o stats from the san and compare.

Where are the DB2 transaction logs located and how big are they?

If the SAN is EMC raid 5 and the write times are slow set the
following registry variables at the instance level.

DB2_PARALLEL_IO *

Set the following instance parameters.

Enable intra-partition parallelism * * *INTRA_PARALLEL *ON
Maximum query degree of parallelism * * MAX_QUERYDEGREE ANY

Set the following database parameters.

Default query optimization class * * * *DFT_QUERYOPT * *5
Degree of parallelism * DFT_DEGREE * * *ANY

Switch on concurrent i/o at tablespace level for all tablespaces in
the database.

db2 ALTER TABLESPACE USERSPACE1 NO FILE SYSTEM CACHING

And if the load is still slow get the details for the san config.

E.G.

SAN is DMX-3.
RAID is RAID 5(3+1).
Data Block size is 256KB
Parity Block size is 256
Logical stripe(block) size is 768KB(256*3)

Recreate the tablspaces as DMS using a page size larger than 4k and
make the extent size the same as the san stripe size.

Something like this.

create regular tablespace xxxx
PAGESIZE 16K
MANAGED BY DATABASE
USING (FILE '/home/data/xxxx/DMSCONTAINERS/CONT_01' 2 G,
* * * *FILE '/home/data/xxxx/DMSCONTAINERS/CONT_02' 2 G,
* * * *FILE '/home/data/xxxx/DMSCONTAINERS/CONT_03' 2 G,
* * * *FILE '/home/data/xxxx/DMSCONTAINERS/CONT_04' 2 G)
BUFFERPOOL * yyyy
EXTENTSIZE * 768
PREFETCHSIZE *192
OVERHEAD * * * 8.6
TRANSFERRATE * 0.2
NO FILE SYSTEM CACHING;- Hide quoted text -

- Show quoted text -

These are all really good suggestions, but most we are already
applying.

We are using DMS tablespaces, seperating LOB data from index data from
normal data.

We use 8 contianers to "trick" the database into thinking there are 8
Disks (I picked the number since we have 8 CPUs, so each CPU can
concentrate on sending IO requests...although I think we could have
just as easily gone with 16 or 24 containers.) This trick forces
parallel IO for normal inserts if you read up on the DB2
documentation. The extent size, of course, is 8 times the prefetch
size.

I did research last night and found that when you load a table with
LOB data, DB2 forces parallelism to 1 cpu, and parallel_IO to 4
disks. This would match up with what I see on the load where only 4-6
disks are working and only 1 CPU is working. As I said earlier,
inserts use all 8 CPUs and 20 + disks @ 80% busy. Basically, the
disks are beating the CPU in regards to throughput. Can anyone
confirm this about LOB data loading into a table? And if this is the
case, this would leave me to believe that an IMPORT is actually faster
in my situation since it can take advantage of parallelism of all 8
CPUs???

[rdudejr]

>
Where are the DB2 transaction logs located and how big are they?- Hide quoted text -

- Show quoted text -

Also these are non-logged loads, that is not a factor. As I said
earlier, inserts are fine, it is the load that is giving me the issue
of not using the available resources.

[Mark A]

db2 ALTER TABLESPACE USERSPACE1 NO FILE SYSTEM CACHING

I assume this is not for the tablespace with LOBs?

LOB's do not use DB2 bufferpools and therefore file system caching should be
on for any tablespaces that contain LOBs.

[Patrick Finnegan]

On Nov 11, 2:16*pm, rdudejr <[e-mail]:

On Nov 11, 4:48*am, Patrick Finnegan <[e-mail]>
wrote:

On Nov 11, 8:15*am, Patrick Finnegan <[e-mail]>
wrote:

On Nov 11, 12:07*am, rdudejr <[e-mail]:

On Nov 10, 6:16*pm, Ian <[e-mail]:

[e-mail] wrote:
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 am using:

db2 "load from loadset1 of del modified by chardel| coldel& insert
into testschema.testtable nonrecoverable data buffer 240000
disk_parallelism 32"

Now I am loading about 440 rows/second, which to me is abysmally
slow. *The tablespaces I am loading into have 8 containers and I
believe there are at least 30 disks on the SAN that the data
eventually lives on. *So needless to say there should be all the I/O
power available to load this data.

Can you post any more information, such as table DDL, tablespace
definition, maybe a few sample rows, etc?

Have you tried the load letting DB2 choose its own defaults for
data buffer / disk_parallelism settings?- Hide quoted text -

- Show quoted text -

I have tried to allow the database to choose its own load values. *I
experience simular performance.

I cant post exact DDL/sample rows for security reasons, but I will do
my best to describe:

The table itself is a parent table to other child tables ( but this
should not matter with a load). *It has about 25 columns, all of which
are varchar, bigint, char, or date, except for 2, which are CLOB
(around 3000 characters/clob).

It is linked to 3 tablespaces, one for LOBs (32k pagesize), one for
indexes (4k pagesize), one for normal data (4k pagesize). *All 3
tablespaces have 8 containers and live on the same filesystem whichis
mounted on a VG that is tied to a SAN with 30+ disk (probably around
100 disk, but I dont know exactly as I am not the SAN adm).

Is that enough info to help?

Also a new discovery! *After loading 15mil records to this table,I
did a few table scans and got the disk usage in topas to report up to
80% busy, so it seems that the load really is not using the full
capacity of the disk (as the load is using about 15% of the disk).
Also, the tablescan uses all 8 CPUs uniformially, where the load is
only using 1 or 2 CPUs.

Get the DB2 write i/o speeds for transaction log data and application
data from the database snapshot and work out the number of writes per
millisecond.

E.G.

Log write time (sec.ns) * * * * = 1730.000000004
Number write log IOs * * * * * * * * = 1035798

(1730.000000004 * 1000) / 1035798 * * * = 1.67 milliseconds per write.

Direct writes * * * * * * * * * * * * * *= 40354784
Direct write elapsed time (ms) * *= 959854

959854 / 40354784 * * * * * * * * * * * * ** * = 0.024 milliseconds per write.

Ask the storage admins for the i/o stats from the san and compare.

Where are the DB2 transaction logs located and how big are they?

If the SAN is EMC raid 5 and the write times are slow set the
following registry variables at the instance level.

DB2_PARALLEL_IO *

Set the following instance parameters.

Enable intra-partition parallelism * * *INTRA_PARALLEL *ON
Maximum query degree of parallelism * * MAX_QUERYDEGREE ANY

Set the following database parameters.

Default query optimization class * * * *DFT_QUERYOPT * *5
Degree of parallelism * DFT_DEGREE * * *ANY

Switch on concurrent i/o at tablespace level for all tablespaces in
the database.

db2 ALTER TABLESPACE USERSPACE1 NO FILE SYSTEM CACHING

And if the load is still slow get the details for the san config.

E.G.

SAN is DMX-3.
RAID is RAID 5(3+1).
Data Block size is 256KB
Parity Block size is 256
Logical stripe(block) size is 768KB(256*3)

Recreate the tablspaces as DMS using a page size larger than 4k and
make the extent size the same as the san stripe size.

Something like this.

create regular tablespace xxxx
PAGESIZE 16K
MANAGED BY DATABASE
USING (FILE '/home/data/xxxx/DMSCONTAINERS/CONT_01' 2 G,
* * * *FILE '/home/data/xxxx/DMSCONTAINERS/CONT_02' 2 G,
* * * *FILE '/home/data/xxxx/DMSCONTAINERS/CONT_03' 2 G,
* * * *FILE '/home/data/xxxx/DMSCONTAINERS/CONT_04' 2 G)
BUFFERPOOL * yyyy
EXTENTSIZE * 768
PREFETCHSIZE *192
OVERHEAD * * * 8.6
TRANSFERRATE * 0.2
NO FILE SYSTEM CACHING;- Hide quoted text -

- Show quoted text -

These are all really good suggestions, but most we are already
applying.

We are using DMS tablespaces, seperating LOB data from index data from
normal data.

We use 8 contianers to "trick" the database into thinking there are 8
Disks (I picked the number since we have 8 CPUs, so each CPU can
concentrate on sending IO requests...although I think we could have
just as easily gone with 16 or 24 containers.) * This trick forces
parallel IO for normal inserts if you read up on the DB2
documentation. *The extent size, of course, is 8 times the prefetch
size.

I did research last night and found that when you load a table with
LOB data, DB2 forces parallelism to 1 cpu, and parallel_IO to 4
disks. *This would match up with what I see on the load where only 4-6
disks are working and only 1 CPU is working. *As I said earlier,
inserts use all 8 CPUs and 20 + disks @ 80% busy. *Basically, the
disks are beating the CPU in regards to throughput. *Can anyone
confirm this about LOB data loading into a table? *And if this is the
case, this would leave me to believe that an IMPORT is actually faster
in my situation since it can take advantage of parallelism of all 8
CPUs???

If the disk write speeds from the database snapshot are less than .5
milliseconds per write and the load with lobs is single threaded then
an import may be faster.

Do you have a link for the research indicating single threading with
lobs?

If you still want to use load and the instance runs on an lpar then
ask the Aix admin for at least one dedicated physical cpu on the
lpar. Chances are you are running with virtual cpu which may amount
to less than one physical cpu i.e. DB2 may run faster on one physical
cpu than multiple virtual cpus and the load if single threaded will
probably run faster on that physical cpu.

[rdudejr]

We turn off filesystem caching on all our tablespaces, regardless of
if they have LOBs or not (as we do not want giant lobs in memory).

Here is the link about parallelism not supported with LOBs:

http://publib.boulder.ibm.com/infoce...c/c0004605.htm

read halfway down under "CPU_PARALLELISM"

We do not use LPARs here, the only shared resource is Disk on the
SAN. I tried the import and it was even slower. I did strip out the
LOBs and create the table SANs lob loaded data SANs lob, and I was
able to load 3 million records in less than a minute...more of the
performance I was expecting.

So I guess all of this data leads me to this question...what is the
best way to load a LOB into a table, seeing as DB2's LOAD utility does
an abysmal job of dealing with LOBs?????

[rdudejr]

Noone has any more good ideas on this?

And yes I know this is a shameless bump but I am getting pretty
desperate/frustrated with DB2's behaviour at this point in regards to
LOBs.

On Nov 12, 2:50*pm, rdudejr <[e-mail]:

We turn off filesystem caching on all our tablespaces, regardless of
if they have LOBs or not (as we do not want giant lobs in memory).

Here is the link about parallelism not supported with LOBs:

http://publib.boulder.ibm.com/infoce...x.jsp?topic=/c....

read halfway down under "CPU_PARALLELISM"

We do not use LPARs here, the only shared resource is Disk on the
SAN. *I tried the import and it was even slower. *I did strip out the
LOBs and create the table SANs lob loaded data SANs lob, and I was
able to load 3 million records in less than a minute...more of the
performance I was expecting.

So I guess all of this data leads me to this question...what is the
best way to load a LOB into a table, seeing as DB2's LOAD utility does
an abysmal job of dealing with LOBs?????

[rdudejr]

Noone has any more good ideas on this?

And yes I know this is a shameless bump but I am getting pretty
desperate/frustrated with DB2's behaviour at this point in regards to
LOBs.

[Ian]

rdudejr wrote:

Noone has any more good ideas on this?

And yes I know this is a shameless bump but I am getting pretty
desperate/frustrated with DB2's behaviour at this point in regards to
LOBs.

You're not going to get much help if you can't give any useful
information. No DDL, no sample data, etc.

What process do you see consuming the most CPU when you're running the
load? Is it db2lfrm (i.e. the process that formats the actual data
pages)? Or is it something else?

Do you get warnings or errors from your load process (i.e. does DB2
complain about your data?)

Are your lobs inline in the data file, or are they external (i.e. lob
locators in the actual import file)?