Read-only and read-write dbspaces and files

For a read-only dbspace, the administrator has the following capabilities:

Add a file
Rename the file path of a dbfile in the dbspace (requires main dbspaces are offline)
Drop an empty file
Rename the dbspace or dbfile in the dbspace

A file is read-only when either the file status is read-only or the file status is read-write, but the owning dbspace status is read-only. Altering a dbspace to read-only does not alter the catalog status of its associated files to read-only, but does make the file(s) read-only at the operating system level. In other words, the file's catalog read-only or read-write status remains the same, but data in the file cannot be modified. For permissions required to create and move objects in dbspaces, see “Dbspace management permissions”.

A dbspace and its associated files can have individual RO or RW status, for example:

Object	Status	Effective status	Table’s dbspace	Table’s status
dbspace1	RW	RW	dbspace1	RW
- file1	RO	RO
- file2	RW	RW
dbspace2	RO	RO	dbspace2	RO
- file1	RO	RO
- file2	RW	RO
dbspace3	RW	RO	dbspace3	RO
- file1	RO	RO
- file2	RO	RO

A table or join index is read-only when assigned to a RO dbspace. A table partition is read-only when the partition is assigned to a RO dbspace. No data modifications such as insert, delete, update, load, truncate table, and insert/delete/update through an updatable cursor are allowed to a RO table or RO table partition. No DDL operations such as ALTER TABLE add/drop column, create/drop index are allowed on a RO table or RO table partition.

Attempts to write to a read-only dbspace are detected when the modified pages are flushed to disk. Pages modified during an INSERT...VALUES statement are not written to the database until the next command that is not an INSERT...VALUES statement. (INSERT...VALUES is the only command that behaves this way.) Sybase IQ returns an error for DML operations on a read-only table or read-only table partition.

Operations to join indexes, including create join index, drop join index, and synchronize join index, fail if any of the join tables are RO.

The following table lists allowed dbspace configuration transitions.

**Table 5-5: Allowed dbspace configuration transformations**
State	Alter Type	Allowed	Allowed
		User Main	IQ_SYSTEM_MAIN, IQ_SYSTEM_TEMP

Online DBSpace
	Alter DBSpace Offline	Yes	No
	Alter DBSpace Online	No	No for temp, yes for IQ_SYSTEM_MAIN
	Alter DBSpace RO	Yes, if it is RW	No
	Alter DBSpace RW	Yes, if it is RO	No
	Alter Striping Parameters	Yes	Yes, on single node and multiplex coordinator
	Rename DBSpace	Yes	No
	Add File	Yes	Yes
	Drop File	Yes, if empty	Yes, if empty and RO
	Alter File RO	Yes, if it is RW	Yes, if it is RW and not the last RW dbfile
	Alter File RW	Yes, if it is RO	Yes, if it is RO
	Alter File Size	Yes	Yes
	Alter File Rename Logical Name	Yes	Yes
	Alter File Rename Path	No	No


Offline DBSpace
	Alter DBSpace Offline	No	NA
	Alter DBSpace Online	Yes	NA
	Alter DBSpace RO	No	NA
	Alter DBSpace RW	No	NA
	Alter Striping Parameters	Yes	NA
	Rename DBSpace	Yes	NA
	Add File	No	NA
	Drop File	Yes, if empty	NA
	Alter File RO	Yes	NA
	Alter File RW	Yes	NA
	Alter File Size	No	NA
	Alter File Rename Logical Name	Yes	NA
	Alter File Rename Path	Yes	NA

Dynamically Offline DBSpace
	Alter DBSpace Offline	Yes, if RO	NA
	Alter DBSpace Online	Yes	NA
	Alter DBSpace RO	Yes, if RW	NA
	Alter DBSpace RW	No	NA
	Alter Striping Parameters	Yes	NA
	Rename DBSpace	Yes	NA
	Add File	No	NA
	Drop File	Yes, if empty	NA
	Alter File RO	Yes	NA
	Alter File RW	Yes	NA
	Alter File Size	No	NA
	Alter File Rename Logical Name	Yes	NA
	Alter File Rename Path	No	NA

Read-only DBSpace
	Alter DBSpace Offline	Yes, if online	NA
	Alter DBSpace Online	Yes, if offline	NA
	Alter DBSpace RO	No	NA
	Alter DBSpace RW	Yes, if online	NA
	Alter Striping Parameters	Yes	NA
	Rename DBSpace	Yes	NA
	Add File	Yes	NA
	Drop File	Yes, if empty	NA
	Alter File RO	Yes, if RW	NA
	Alter File RW	Yes, if RO	NA
	Alter File Size	No	NA
	Alter File Rename Logical Name	Yes	NA
	Alter File Rename Path	Yes, if offline	NA

read-write DBSpace
	Alter DBSpace Offline	No	No
	Alter DBSpace Online	Yes, if dynamically offline	No
	Alter DBSpace RO	Yes	No
	Alter DBSpace RW	No	No
	Alter Striping Parameters	Yes	Yes
	Rename DBSpace	Yes	No
	Add File	Yes	Yes
	Drop File	Yes, if empty	Yes, if empty and RO
	Alter File RO	Yes, if RW	Yes, if RW
	Alter File RW	Yes, if RO	Yes, if RO
	Alter File Size	Yes, if RW	Yes, if RW
	Alter File Rename Logical Name	Yes	Yes
	Alter File Rename Path	No	No

Read-only File
	Alter File RO	No	No
	Alter File RW	Yes	Yes
	Alter File Size	No	No
	Alter File Rename Logical Name	Yes	Yes
	Alter File Rename Path	Yes, if offline	No

Read-write File
	Alter File RO	Yes	Yes
	Alter File RW	No	No
	Alter File Size	Yes	Yes
	Alter File Rename Logical Name	Yes	Yes
	Alter File Rename Path	No	No
Notes Dynamically offline means the dbspace is marked offline in memory, as opposed to marked offline in the catalog A read-only IQ_SYSTEM_MAIN dbfile can be dynamically offline For IQ_SYSTEM_MSG, the only modification that is permitted is to rename the path, which is done using the command `ALTER DBSPACE IQ_SYSTEM_MSG RENAME 'filepath'`