Read-only and read-write dbspaces and files

For a read-only dbspace, the administrator can:

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 associated files 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 read-only (RO) or read-write (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 it is assigned to a read-only dbspace. A table partition is read-only when the partition is assigned to a read-only dbspace. No data modifications such as insert, delete, update, load, truncate table, and insert/delete/update through an updatable cursor are allowed to a read-only table or read-only table partition. No DDL operations such as ALTER TABLE add/drop column, create/drop index are allowed on a read-only table or read-only 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 creating, dropping and synchronizing join indexes, fail if any of the join tables are read-only.

Table 5-5 lists allowed dbspace configuration transitions.

**Table 5-5: Allowed dbspace configuration transformations**
State	Alter type	Allowed for User Main	Allowed for IQ_SYSTEM_MAIN or IQ_SYSTEM_TEMP

Online dbspace
	ALTER DBSPACE OFFLINE	Yes, if RO	No
	ALTER DBSPACE ONLINE	No	Yes for IQ_SYSTEM_MAIN, Yes for IQ_SYSTEM_TEMP
	ALTER DBSPACE READONLY	Yes, if it is RW	No
	ALTER DBSPACE READWRITE	Yes, if it is RO	No
	ALTER STRIPING or STRIPESIZEKB	Yes	Yes
	RENAME DBSPACE	Yes	No
	ADD FILE	Yes	Yes
	DROP FILE	Yes, if empty	Yes, if empty
	ALTER FILE READONLY	Yes, if it is RW	Yes, if it is RW and not the last RW dbfile
	ALTER FILE READWRITE	Yes, if it is RO	Yes, if it is RO
	ALTER FILE SIZE	Yes, if RW (dbspace and dbfile)	Yes, if RW file
	ALTER FILE RENAME LOGICAL NAME	Yes	Yes
	ALTER FILE RENAME PATH	No	No, for IQ_SYSTEM_MAIN, Yes, for IQ_SYSTEM_TEMP

Offline dbspace
	ALTER DBSPACE OFFLINE	No	N/A
	ALTER DBSPACE ONLINE	Yes	N/A
	ALTER DBSPACE READONLY	No	N/A
	ALTER DBSPACE READWRITE	No	N/A
	ALTER STRIPING or STRIPESIZEKB	Yes	N/A
	RENAME DBSPACE	Yes	N/A
	ADD FILE	No	N/A
	DROP FILE	Yes, if empty	N/A
	ALTER FILE RO	Yes	N/A
	ALTER FILE RW	Yes	N/A
	ALTER FILE SIZE	No	N/A
	ALTER FILE RENAME LOGICAL NAME	Yes	N/A
	ALTER FILE RENAME PATH	Yes	N/A

Dynamically offline dbspace
	ALTER DBSPACE OFFLINE	Yes, if RO	N/A
	ALTER DBSPACE ONLINE	No	N/A
	ALTER DBSPACE READONLY	No	N/A
	ALTER DBSPACE READWRITE	No	N/A
	ALTER STRIPING or STRIPESIZEKB	Yes	N/A
	RENAME DBSPACE	Yes	N/A
	ADD FILE	No	N/A
	DROP FILE	Yes, if empty	N/A
	ALTER FILE READONLY	Yes, if RW file	N/A
	ALTER FILE READWRITE	Yes, if RO file	N/A
	ALTER FILE SIZE	No	N/A
	ALTER FILE RENAME LOGICAL NAME	Yes	N/A
	ALTER FILE RENAME PATH	No	N/A

Read-only dbspace
	ALTER DBSPACE OFFLINE	Yes, if online	N/A
	ALTER DBSPACE ONLINE	Yes, if offline	N/A
	ALTER DBSPACE READONLY	No	N/A
	ALTER DBSPACE READWRITE	Yes, if online	N/A
	ALTER STRIPING and STRIPESIZEKB	Yes	N/A
	RENAME DBSPACE	Yes	N/A
	ADD FILE	Yes	N/A
	DROP FILE	Yes, if empty	N/A
	ALTER FILE READONLY	Yes, if RW	N/A
	ALTER FILE READWRITE	Yes, if RO	N/A
	ALTER FILE SIZE	No	N/A
	ALTER FILE RENAME LOGICAL NAME	Yes	N/A
	ALTER FILE RENAME PATH	Yes, if offline	N/A

Read-write dbspace
	ALTER DBSPACE OFFLINE	No	No
	ALTER DBSPACE ONLINE	Yes, if dynamically offline	Yes
	ALTER DBSPACE READONLY	Yes, if RW	No
	ALTER DBSPACE READWRITE	Yes, if RO	No
	ALTER STRIPING and STRIPESIZEKB	Yes	Yes
	RENAME DBSPACE	Yes	No
	ADD FILE	Yes	Yes
	DROP FILE	Yes, if empty	Yes, if empty
	ALTER FILE READONLY	Yes, if RW file	Yes, if RW and not the last dbfile
	ALTER FILE READWRITE	Yes, if RO file	Yes, if RO file
	ALTER FILE SIZE	Yes, if RW file	Yes, if RW file
	ALTER FILE RENAME LOGICAL NAME	Yes	Yes
	ALTER FILE RENAME PATH	No	No, for IQ_SYSTEM_MAIN, No, for IQ_SYSTEM_TEMP

Read-only file
	ALTER FILE READONLY	No	No
	ALTER FILE READWRITE	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 READONLY	Yes	Yes
	ALTER FILE READWRITE	No	No
	ALTER FILE SIZE	Yes, if dbspace is RW and ONLINE	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'.`