db2 range partitioning attach and detach issue

Question

We have a table which is used in many select Queries, as it is be accessed by customers to fetch their information. Also they can change their information too whenever they want.

We had to retrieve records only less than 2 years of age. So whatever is older than 24 months gets deleted.

Also, we have a script which actually runs every month and adds (not ATTACH) a partition to the table.

The problem is, we were testing the add and DETACHING by loading the table with some dummy data and running 'Select * from tab'. The ADD and DETACH scripts seems to fail. Can you please elaborate the reason for this and also how can I achieve the above, without having to force connections and bringing the table down as its very critical and must be available at all times.

db2 "alter table tabname add partition <partname> starting 'date' ending 'date' exclusive"

now when table is not being used it goes on well. But when I load the table and just run db2 "select * from tabname" and then run the same (as above) command to add partitions following error comes. Here is the error:

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "68". SQLSTATE=40001

Answer 1

Use this:

db2 +c "lock table tabname in share mode"
db2 +c "alter table tabname add partition <partname> starting 'date' ending 'date' exclusive"
db2 "commit"

Here is the explanation:

+c : disable auto commit, so the command you send will not be committed until an explicit commit command sent or connection closed.

locking table in share mode: means that you want to acquire a shared lock which will prevent other transactions to make changes to the table. So, following command won't be blocked while working.

In this approach you say that: lock the table for me, then issue partition command, while issuing this command other transaction will wait till you finished your job and send commit statement.

I am not sure if it works or not. But it worth a try.

Answer 2

This has to do with waiting for locks. Notice that the error mentions that a transaction was rolled back due to a deadlock or timeout.

What you need to do to see what Reason code "68" is, is to run the following command:

db2 ? SQL0911N

this will show you the reason codes associated with that error. My understanding of reason code "68" is a transaction was rolled back due to a lock timeout. So what you need to ask is what is locking? What is your lock timeout threshold setting? (ie, LOCKTIMEOUT). What is your isolation level of your transactions that are running at the same time? Could one of them have a hold on your records? Perhaps you need to have a commit issued in a particular transaction?

Check these things first. They might give you clue as to what is waiting for a lock. You can also use the db2pd tool to help you with troubleshooting your locks and lock waits.

EDIT: In thinking about the issue, it may have to do with how you are partitioning. If you do not specify a partition range, such as the end of the partition, etc., then DB2 assumes you wish to re-organize the data across the partitions. It then does so automatically. The upside of this is the partition is transparent from a table definition standpoint. The downside, is the data is automatically "re-org'd" across all partitions to account for the old data partition rules across the all available partitions. This re-org is a performance hit to your database, and probably accounts for your locks that are occurring. The queries and/or loads going on may be placed on hold while your database is moving data around to account for the new partition.

So to avoid this you have a few choices.

1. You could play with lock settings, although I'm not sure this will get you anywhere.
2. You could quiesce the table space of the table you wish to add the partition to. This would leave your database "online" for other tables (pending you have more than one table space) and allow you to do maintenance to the table you wish. Essentially this is bringing that table "offline". I know you do not wish to do this, but... the upside is that the reorg of the data will happen faster and will not cause locks for other users.
3. If you already have partition ranges set up, set up a new range for this new partition so that only the data that fits that range will go into it. This will allow for the table and everything to be up and will cause little, if any, reorg'ing of the data, as only the data that fits the new partition data range definition will be reorg'd. The rest will be on new inserts. Data that fits your old data ranges will go into those partitions.

Even with these suggestions, I would still verify this with looking into the locks. I'm betting this is what it is, now that I have thought more about it. But it doesn't hurt to verify this.

Overall, I would recommend either Option 2 if you really want your data automatically re-org'd across partitions, or Option 3, if you can define that new data range and you wish to leave the table "online".

Answer 3

I believe the ALTER TABLE ADD PARTITION DDL requires certain table level z-lock to modify the definition of the table in the system catalog. This means any read/write access currently going on against the table will block the ALTER TABLE request. Perhaps adjusting the LOCKTIMEOUT parameter would help you with that.