Hello,

I have a trouble doing an insert from TEMP (global temporary table, on
commit preserve rows, not logged) table into a permanent one:

1. insert into PERM1 select * from TMP1 (about 250 000 rows):
2. insert into PERM2 select * from TMP2 (about 300 000 rows)

Both tables are in SMS tablespace, have similar structure, no triggers
or constraints (some stored procedures though), PERM1 has 5 indexes on
it and PERM2 has 3. However the 1st insert takes about 15 minutes, but
the second one - more than one hour.

What could be the reason of such big difference? Could you advise how
to investigate?

Thanks!

P.S.

PERM1 table:
Column
Data type name Length Scale Nulls
------------------- ---------- ----- -----
INTEGER 4 0 No
INTEGER 4 0 Yes
VARCHAR 100 0 Yes
TIMESTAMP 10 0 No
INTEGER 4 0 Yes
INTEGER 4 0 Yes
VARCHAR 50 0 Yes
VARCHAR 20 0 Yes
VARCHAR 50 0 Yes
VARCHAR 25 0 Yes
VARCHAR 25 0 Yes
INTEGER 4 0 Yes
INTEGER 4 0 Yes
VARCHAR 30 0 Yes
TIMESTAMP 10 0 Yes
INTEGER 4 0 Yes
INTEGER 4 0 Yes
CHARACTER 1 0 Yes
BIGINT 8 0 Yes
VARCHAR 15 0 Yes
TIMESTAMP 10 0 Yes
VARCHAR 40 0 Yes
VARCHAR 10 0 Yes
SMALLINT 2 0 Yes
CHARACTER 1 0 Yes
INTEGER 4 0 Yes
INTEGER 4 0 Yes
INTEGER 4 0 Yes
INTEGER 4 0 Yes
INTEGER 4 0 Yes
INTEGER 4 0 Yes
INTEGER 4 0 Yes
TIMESTAMP 10 0 Yes
TIMESTAMP 10 0 No
VARCHAR 20 0 Yes
INTEGER 4 0 Yes
INTEGER 4 0 Yes

PERM2 table:
Column
Data type name Length Scale Nulls
------------------- ---------- ----- ------
INTEGER 4 0 No
TIMESTAMP 10 0 No
INTEGER 4 0 Yes
INTEGER 4 0 Yes
INTEGER 4 0 Yes
BIGINT 8 0 Yes
INTEGER 4 0 Yes
VARCHAR 100 0 Yes
INTEGER 4 0 Yes
INTEGER 4 0 Yes
VARCHAR 50 0 Yes
VARCHAR 20 0 Yes
VARCHAR 50 0 Yes
INTEGER 4 0 Yes
TIMESTAMP 10 0 Yes
CHARACTER 1 0 Yes
TIMESTAMP 10 0 Yes
VARCHAR 40 0 Yes
TIMESTAMP 10 0 No
VARCHAR 20 0 Yes
CHARACTER 1 0 Yes
VARCHAR 10 0 Yes
SMALLINT 2 0 Yes
INTEGER 4 0 Yes
INTEGER 4 0 Yes
INTEGER 4 0 Yes
TIMESTAMP 10 0 Yes
INTEGER 4 0 Yes
INTEGER 4 0 Yes
INTEGER 4 0 Yes
INTEGER 4 0 Yes

First, try to increase to size of bufferpools.

You can also check where your applicaion spend most of its time.

Use this query ( it works for DB2 V9.7) to analyze total_wait_time :

select
application_handle,
total_wait_time,
agent_wait_time,
wlm_queue_time_total,
lock_wait_time,
log_buffer_wait_time,
log_disk_wait_time,
tcpip_recv_wait_time,
tcpip_send_wait_time,
ipc_recv_wait_time,
fcm_recv_wait_time,
fcm_send_wait_time,
pool_read_time,
pool_write_time,
direct_read_time,
direct_write_time

from table ( mon_get_connection(null,-1)) as x


it gives you answer about the time of writting data to bufferpool and
disk, writting to log, waiting for locks.

Regards