Db2 range partitioning attach and detach issue

db2partitioning

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

Best Answer

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.

Related Question