Tpump - Is blocking normal?

Tools & Utilities
Fan

Tpump - Is blocking normal?

We are attempting to performance tune some tables in our database. One of the items we are testing is join indexes.

The current load utility for the table we are tuning is mload which cannot be used on a table with a join index. This has now lead me down the "tpump" path.

While testing, I noticed the session is considerably slower. After having the DBA check the system, I was told my tpump sessions are blocking each other.

Is this normal for tpump? Or is there something I missed when setting up the tpump session.

My sessions are set at 4 and my packing factor is 20. We are currently running v2r5.

I've searched the forum hoping to find an answer... but did not find anything.

Any help would be greatly appreciated.

Thanks...
nudoam
5 REPLIES
gg
Enthusiast

Re: Tpump - Is blocking normal?

alternative way to load the data:

1. fastload file into empty staging table
2. insert select via bteq
use the same PI for staging and destination table

Enthusiast

Re: Tpump - Is blocking normal?

Hi,
I've already encountered the problem you are describing here. Some colleagues of mine experienced tpump sessions locking each other.

At this moment I don't remember really exactly but if I'm right we resolved the problem using the SERIALIZE option of tpump (on or off you have to look at the manual); without changing load utility.

Hope this helps,
Bye,
TDUser
Enthusiast

Re: Tpump - Is blocking normal?

This is a good way to do:

alternative way to load the data:

1. fastload file into empty staging table
2. insert select via bteq
use the same PI for staging and destination table

and we did the following way too:
1.we had a job that drops the index,
2.then a job to mload the table
3.then a job to create new index and statistics.

The only drawback in the above case is table is locked.

This worked fine too.
But TPUMP is very slow, no matter how well you tune it. (correct me if I am wrong)

Fan

Re: Tpump - Is blocking normal?

I tried the serialize option and we still get numerous blocks.

Some processes we can drop indexes, mload, and recreate indexes.

However, the process I'm testing is an extremely large table and to drop/recreate index is not an option.

We'll have to look at another option.
gg
Enthusiast

Re: Tpump - Is blocking normal?

If the table write lock is the only problem remaining you may want to rearrange your batch window. If you have to load and query at the same time. You can give users access to that table via views only. Within the views you can specify an ACCESS LOCK. This will allow a "dirty read" for users during loads and prevents the loader being blocked by user requests.