Are there any plans for a truncate table partition command (with and without
save to side table)? We also need ability to reattach a partition (from the
table created during detach) with the same partition info as existed before
detach without having to specify it in detail.

Authorization for such a command would be separate from any existing
authorization (for an ETL person), or maybe included in LOAD authorization.
Right now I would have to grant DBADM to ETL team to do this (or control on
each table) and I don't want to give them that much authority just to
truncate the data in a partition. Currently I have to detach, load side
table from /dev/null, and attach side table back into the table as a new
partition.

In conjunction with this new authority should be the ability to attach a
table back as a partition using the same information (partition name,
ranges, etc) as existed when the detach took place. So an ETL person could
only be authorized to put a partition back exactly it was the way before it
was detached (but they could insert, update, delete, or truncate the data in
the side table before putting it back as a partition).

On 8/24/2010 2:26 PM, Mark A wrote:
requests....

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

"Serge Rielau" <srielau (AT) ca (DOT) ibm.com> wrote

I am not making a formal request, I am just asking if this is in the plans
so that I don't have to make a request.

You are more than welcome to steal this idea and make it your own, but if
think it would be best for me to make a formal request, then let me know how
to do that.

On 8/24/2010 9:02 PM, Mark A wrote:
Would need to get a better handle on whether they are truly trying to
empty a current partition and then roll data back into the same
partition definition or are they trying to empty a current partition and
then reattach as a new partition which will be used for roll in purposes.

If the former then they are truly looking for a truncate partition, if
the latter they are looking for the ability to attach and detach with
finer grained authority rules.

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

"Serge Rielau" <srielau (AT) ca (DOT) ibm.com> wrote

The first scenario (empty a partition) is more important since it can
functionally be seen as an extension of DELETE authority on a table without
any DDL rights having to be granted. Often times when ETL team is loading
data, they discover a problem and need to start over on a particular
partition (whether it be a staging table or a final table). The ETL team
already has LOAD authority, but that only applies to the whole table, and
they need LOAD Replace from /dev/null (aka truncate) on a particular
partition.

The other scenario of creating a finer authorization granularity to allow
detach and attach authority without having CONTROL authority on the table
would also be a good idea IMO. I might even go further to distinguish the
following:

- Detach any partition within the authorized table
- Attach only what was previously detached and attach it with same
partitioning range (must be attached as same partition name, range, etc as
existed before detach
- Attach any partition to the authorized table