Adding Dbfiles to Shared Dbspaces

Use ALTER DATABASE ADD FILE statement in Interactive SQL to add dbfiles to shared dbspaces.

Prerequisites
When adding space to IQ_SHARED_TEMP, consider the distributed query processing workload. Dbspace commands on IQ_SHARED_TEMP require the MANAGE ANY DBSPACE system privilege.
Task
  1. Connect to the coordinator.
    Note: Do not start the coordinator in single-node mode (-iqmpx_sn 1) with -iqro, or adding a file returns the error SQL Anywhere Error -757: Modifications not permitted for read-only database.
  2. Add the file. In the ADD FILE clause, specify either a full path to a raw device, or a soft link. These examples specify full paths:
    Platform Syntax
    UNIX 
    ALTER DBSPACE IQ_SHARED_TEMP ADD FILE mydbfilename
'/dev/rdsk/c4t600A0B80005A7F5D0000024'
    Windows 
    ALTER DBSPACE IQ_SHARED_TEMP ADD FILE mydbfilename
'\\\\.\\PhysicalDrive2'
    This example specifies a soft link on a UNIX system:
    ALTER DBSPACE IQ_SHARED_TEMP ADD FILE mydbfilename
'store/userdb1'

    You need not synchronize or restart any secondary servers.

  3. Confirm that the file is visible to all secondary nodes:
    sp_iqmpxfilestatus
  4. Shared files on IQ_SYSTEM_MAIN and user main dbspaces are implicitly read-write. Dbfiles on IQ_SHARED_TEMP are created as read-only. If the dbfile is on a shared temporary dbspace, alter the new file to read-write status:
    ALTER DBSPACE IQ_SHARED_TEMP ALTER FILE mydbfilename
READWRITE
If an error is returned regarding the file status issues from one or more nodes, run the sp_iqmpxfilestatus procedure to troubleshoot and correct problems. You can force read-write status on IQ_SHARED_TEMP dbfiles if you need to postpone problem correction:
ALTER DBSPACE IQ_SHARED_TEMP ALTER FILE mydbfilename
FORCE READWRITE

Use of the FORCE READWRITE clause returns an error on IQ_SYSTEM_MAIN and user main dbfiles.

Parent topic: Updates on IQ_SHARED_TEMP