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