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)

and the order of inserts does not matter

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

Number of Indices and the key-size of each index is a contributing factor towards faster/slower inserts.

As you are comparing the INSERT in case 1 (15 minutes) and case 2 (30 minutes +), if you are on test environment or have the flexibility to drop the indexes, I will recommend you to drop all the eight indexes from both the tables and check the duration taken for INSERT in both the cases. If the time taken is somewhat close to each other, you have a strong reason to believe that number of indexes and their size in both the tables is the main reason for the difference in the time ( 15 min Vs 30 min). Else, we need to look into remaining factors contributing for INSERT performance .

As jayanta suggested could be indexes which is causing the slow response could u
please provide the type of indexes like is unique or any primary key defined and u can also try append on for the table which append ur insert at last

regds
Paul

Are you executing both insert statements at the same time? Are tables PERM1 and PERM2 defined in the same SMS tablespace?

Take a look at:
Tips for improving INSERT performance in DB2 Universal Database

The SMS tablespaces are not helping in this case. Over and over again DB2 has to knock on the O.S. door for another extend so the file can grow. Is there enough free space on disk? Which file-system is used and is it fragmented? If it is feasible I'd use a DMS tablespace with a size already large enough to contain all the data. Spread the containers over as many spindles as possible.

Thank you all for the replies, but this I've tried all of this before posting the question...

• Index removal does not help

• In test env. we have only one TS, all tables are sharing it. For the production system, all permanent tables are in DMS, the HDD has enough free space, so it's not the case here

• And yes, I've studied this developerworks article (as well as many other sources), but I'm limited to INSERT only, since APPEND ON, LOAD will lead to REORG PENDING state for a table or BACKUP PENDING state for db, which is not acceptable (over 40 mln records in prod. table, the calculation is done daily), and besides the application user is quite restricted...

Use DB2 diagnostic tools (db2top, db2pd, snapshot monitor) to determine where your bottleneck is: I/O performance; page cleaning; logging; data maintenance (free space search, index page splits). When you know what the problem is you will be able to better address it.

The order of the tables could be a factor. Unless I'm mistaken, z/OS tables are physically stored in clustering sequence. If the one DGTT is close to the clustering sequence of its target, and the second is not close to the clustering sequence of its target, that might explain the elapsed time difference. I hope that you're not issuing a single INSERT for a unit of work that regularly exceeds an hour. If you're not using a cursor to retreive the rows from the DGTT for the INSERTS, try coding one with an ORDER BY that matches the clustering sequence of the INSERT target table. Make sure you use a reasonable commit frequency, 30, maybe 60 seconds.

Unfortunately the order is not a factor - I've tried it already. And I'm on Linux, not Z/OS. The only significant difference between tables, I've found so far - is the PCTFREE value, but changing it did not help... And I do issue a single INSERT, but I don't use the transaction control, i.e. it's autocommitted and it is a single UOW. We do not see any problems with it, since it's not the OLTP environment. The problem is definitely in the table definition, because I've tried to switch the destination of INSERT and it worked fine...

If you sure of that and you want help on this forum,
I think that you should show complete DDLs(at least both tables and all indexes).
Although you showed all columns with data types, create table statement includes more parameters and options.

However, you also wrote...
Did you tried and compared with dropping all indexes on both tables?

also, you mention:
What do you mean with the stored procedures? Also, what do you mean with:
do you mean you performed the insert to another table, that is a duplicate of your table? If that is the case, I am guessing that it could be your new target table is empty. You mentioned playing with pctfree. After you changed it in your existing target, did you reorg the table? prior to attempting the inserts again.
Dave