Managing Column Storage in a Table Partition

Store data for individual columns in a range or hash-range partition separately from the dbspace for the partition. You can add, delete and move columns within the partition to specific dbspaces.

Prerequisites

Database Version	Table Partition Privileges
SAP Sybase IQ 15.3 and 15.4	View any table partition property page – none. Modify any table partition property pertaining to moving partition location or separate column storage for a partition – you must have one of: DBA authority SPACE ADMIN authority CREATE permission on the dbspace to which the partition is being moved along with one of: ALTER permission on the underlying table You own the table Modify any other table partition property: Table owned by self – none. Table owned by other users – you must have one of: DBA authority ALTER permission on the table
SAP Sybase IQ 16.0	View any table partition property page – none. Modify any table partition property pertaining to moving partition location or separate column storage for a partition – you must have one of: ALTER ANY TABLE system privilege ALTER ANY OBJECT system privilege MANAGE ANY DBSPACE system privilege One of the following: CREATE ANY OBJECT system privilege CREATE permission on the dbspace to which the partition is being moved Along with one of: ALTER permission on the underlying table You own the underlying table Modify any other table partition property: Table owned by self – none. Table owned by other users – you must have one of: ALTER ANY TABLE system privilege ALTER ANY OBJECT system privilege ALTER permission on the table

Database Version

Table Partition Privileges

SAP Sybase IQ 15.3 and 15.4

View any table partition property page – none.

Modify any table partition property pertaining to moving partition location or separate column storage for a partition – you must have one of:

DBA authority
SPACE ADMIN authority
CREATE permission on the dbspace to which the partition is being moved along with one of:
- ALTER permission on the underlying table
- You own the table

Modify any other table partition property:

Table owned by self – none.
Table owned by other users – you must have one of:
- DBA authority
- ALTER permission on the table

SAP Sybase IQ 16.0

View any table partition property page – none.

Modify any table partition property pertaining to moving partition location or separate column storage for a partition – you must have one of:

ALTER ANY TABLE system privilege
ALTER ANY OBJECT system privilege
MANAGE ANY DBSPACE system privilege
One of the following:
- CREATE ANY OBJECT system privilege
- CREATE permission on the dbspace to which the partition is being moved
- Along with one of:
  - ALTER permission on the underlying table
  - You own the underlying table

Modify any other table partition property:

Table owned by self – none.
Table owned by other users – you must have one of:
- ALTER ANY TABLE system privilege
- ALTER ANY OBJECT system privilege
- ALTER permission on the table

The SAP Sybase IQ resource is authenticated and running.
In a multiplex configuration, the coordinator node is running.
You must be licensed for the Very Large Database Management option (IQ_VLDBMGMT).

Task

Note: The ability to store individual columns within a partition on different dbspaces should not be confused with the ability to store individual columns of a table on different dbspaces. See Placement under Viewing and Modifying Column Properties.

In the Perspective Resources view, select the resource, and select Resource > Administration Console.
In the left pane, expand IQ Servers > Schema Objects > Tables, and then select Tables.
Select an IQ main store table from the right pane and either:
- Click the arrow to the right of the name and select Properties, or
- From the Administration Console menu bar, select Resource > Properties.
The Table Properties view appears.
In the left pane, select Partitions.
Select a hash-range or range partition from the list and click Edit.
Click Columns and do one of:
- Click Add, select a column from the list and specify a dbspace.
- Select a column and click Delete.
- Click Move, select a column from the list and specify a dbspace.
  Note: If no other dbspaces are available, the list is empty, and a move cannot be performed.
- Moving column data to a new dbspace adds the column to the list of those with separate storage, in Partition Properties and in the Partitions page of Table Properties. Moving the column back to the partition dbspace removes the column from those lists.
- When you are modifying properties, you need not click Apply before changing screens; however, doing so saves any changes.
- If you do not have privileges to modify properties, SAP Control Center displays the properties view in read-only mode.
Do one of:
- Click OK to update any changes to the database and exit the properties view.
- Click Apply to update any changes to the database, but remain in the properties view.
- Click Cancel to cancel any changes not updated to the database and exit the properties view.