Need Truncate Table Partition Command
Mark A
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).
Serge Rielau
requests....
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab
Mark A
news:8disag...@mid.individual.net...
> Uhm, I hope you don't think that usenet is the formal way for feature
> requests....
>
> --
> Serge Rielau
> SQL Architect DB2 for LUW
> IBM Toronto Lab
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.
Serge Rielau
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.
Mark A
> Comments from backstage:
> 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
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