Load performance on AIX w/ SAN

**rdudejr@gmail.com** · Nov 10 '08, 10:35 PM

Re: Load performance on AIX w/ SAN

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** · Nov 10 '08, 11:25 PM

Re: Load performance on AIX w/ SAN

rdudejr@gmail.c om 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.test table nonrecoverable data buffer 240000
disk_parallelis m 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_parallelis m settings?

**rdudejr** · Nov 11 '08, 12:15 AM

Re: Load performance on AIX w/ SAN

On Nov 10, 6:16 pm, Ian <ianb...@mobile audio.comwrote:

rdud...@gmail.c om 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.test table nonrecoverable data buffer 240000
disk_parallelis m 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_parallelis m 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** · Nov 11 '08, 08:25 AM

Re: Load performance on AIX w/ SAN

On Nov 11, 12:07 am, rdudejr <rdud...@gmail. comwrote:

On Nov 10, 6:16 pm, Ian <ianb...@mobile audio.comwrote:
>
>
>

rdud...@gmail.c om 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.test table nonrecoverable data buffer 240000
disk_parallelis m 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_parallelis m 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** · Nov 11 '08, 09:55 AM

Re: Load performance on AIX w/ SAN

On Nov 11, 8:15 am, Patrick Finnegan <finnegan.patr. ..@gmail.com>
wrote:

On Nov 11, 12:07 am, rdudejr <rdud...@gmail. comwrote:
>
>
>

On Nov 10, 6:16 pm, Ian <ianb...@mobile audio.comwrote:

>

rdud...@gmail.c om 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.test table nonrecoverable data buffer 240000
disk_parallelis m 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_parallelis m 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** · Nov 11 '08, 02:25 PM

Re: Load performance on AIX w/ SAN

On Nov 11, 4:48 am, Patrick Finnegan <finnegan.patr. ..@gmail.com>
wrote:

On Nov 11, 8:15 am, Patrick Finnegan <finnegan.patr. ..@gmail.com>
wrote:
>
>
>
>
>

On Nov 11, 12:07 am, rdudejr <rdud...@gmail. comwrote:

>

On Nov 10, 6:16 pm, Ian <ianb...@mobile audio.comwrote:

>

rdud...@gmail.c om 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.test table nonrecoverable data buffer 240000
disk_parallelis m 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_parallelis m 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...alth ough 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** · Nov 11 '08, 02:25 PM

Re: Load performance on AIX w/ SAN

>
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** · Nov 11 '08, 11:25 PM

Re: Load performance on AIX w/ SAN

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** · Nov 12 '08, 01:45 PM

Re: Load performance on AIX w/ SAN

On Nov 11, 2:16 pm, rdudejr <rdud...@gmail. comwrote:

On Nov 11, 4:48 am, Patrick Finnegan <finnegan.patr. ..@gmail.com>
wrote:
>
>
>

On Nov 11, 8:15 am, Patrick Finnegan <finnegan.patr. ..@gmail.com>
wrote:

>

On Nov 11, 12:07 am, rdudejr <rdud...@gmail. comwrote:

>

On Nov 10, 6:16 pm, Ian <ianb...@mobile audio.comwrote:

>

rdud...@gmail.c om 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.test table nonrecoverable data buffer 240000
disk_parallelis m 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_parallelis m 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...alth ough 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** · Nov 12 '08, 07:55 PM

Re: Load performance on AIX w/ SAN

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/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/c0004605.htm

read halfway down under "CPU_PARALLELIS M"

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** · Nov 17 '08, 09:05 PM

Re: Load performance on AIX w/ SAN

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 <rdud...@gmail. comwrote:

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_PARALLELIS M"
>
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** · Nov 17 '08, 09:15 PM

Re: Load performance on AIX w/ SAN

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** · Nov 17 '08, 11:25 PM

Re: Load performance on AIX w/ SAN

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)?

Load performance on AIX w/ SAN

Load performance on AIX w/ SAN

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment