Updating the IQ_SYSTEM_MAIN dbspace

The IQ_SYSTEM_MAIN dbspace manages important database structures including the freelist, which tracks which blocks are in use. Sybase recommends shutting down secondary nodes before adding space to IQ_SYSTEM_MAIN. In a 15.0 multiplex, if a dbfile is added to IQ_SYSTEM_MAIN, all running secondary nodes shut down automatically and the IQ message file for the secondary node reports:

Multiplex secondary node shutting down 
due to a file added to the IQ_SYSTEM_MAIN dbspace.
This node must be synchronized and restarted.

This behavior is an exception only for IQ_SYSTEM_MAIN and does not apply to any other dbspace. Similarly, for other dbspace operations there is no disruption and all nodes in the multiplex continue to run. See “Adding space to IQ_SYSTEM_MAIN on a multiplex coordinator”.

After adding new files to IQ_SYSTEM_MAIN, synchronize and restart secondary nodes. The path of all dbfiles in IQ_SYSTEM_MAIN must be visible to the secondary nodes. If a file path for a dbfile in IQ_SYSTEM_MAIN is not visible to the secondary node, it fails to start, and reports Error opening DBFILE 'filepath'.

StepsChanging an IQ_SYSTEM_MAIN file path

If you need to change an IQ_SYSTEM_MAIN file path that cannot be resolved by creating links, follow these steps on the coordinator:

  1. Shut down all the servers in the multiplex.

  2. Start the coordinator in single node mode using the -iqmpx_sn 1 switch.

  3. Make the dbfile read-only. For example:

    ALTER DBSPACE IQ_SYSTEM_MAIN ALTER FILE 
    mainfile READONLY
    
  4. Run the sp_iqemptyfile procedure against mainfile. For example:

    sp_iqemptyfile mainfile
    
  5. Drop the dbfile. For example:

    ALTER DBSPACE IQ_SYSTEM_MAIN DROP FILE 
    mainfile
    
  6. Add the dbfile with the path visible to all secondary nodes. For example, on UNIX:

    ALTER DBSPACE IQ_SYSTEM_MAIN ADD FILE 
    mainfile '/dev/rdsk/c4t600A0B80005A7F5D0000024' 
    

    For example, on Windows:

    ALTER DBSPACE IQ_SYSTEM_MAIN ADD FILE 
    mainfile '\\\\.\\PhysicalDrive1' 
    
  7. Restart the coordinator normally, without the -iqmpx_sn 1 switch.

  8. Synchronize secondary nodes. For details, see “Synchronizing secondary servers”.

StepsAdding space to IQ_SYSTEM_MAIN on a multiplex coordinator

If the coordinator runs out of space in IQ_SYSTEM_MAIN, including reserve space, it may abort to prevent database corruption and you may be unable to start the coordinator normally, because the coordinator may require space during recovery. In that case, start the coordinator in a single node mode using the -iqmpx_sn startup switch, and add more space by adding file(s) to IQ_SYSTEM_MAIN. Restart the coordinator normally and synchronize all secondary nodes, as in the following procedure.

When the coordinator needs space in IQ_SYSTEM_MAIN, proceed as follows:

  1. Shut down all servers in the multiplex.

  2. Start the coordinator in single node mode using -iqmpx_sn 1. For example:

    start_iq @params.cfg mpxtest.db -iqmpx_sn 1 -n mpxnode_c
    
  3. To add more space to IQ_SYSTEM_MAIN with a new file on a raw device, use syntax like the following:

    Windows:

    ALTER DBSPACE IQ_SYSTEM_MAIN ADD FILE 
    mymainfile '\\.\PhysicalDrive3'
    

    Unix:

    ALTER DBSPACE IQ_SYSTEM_MAIN ADD FILE mymainfile
    '/dev/rdsk/c4t600A0B80005A7F5D0000024'
    

    where mymainfile is the logical or chosen logical name of the new dbfile.

  4. Restart the coordinator normally, without the -iqmpx_sn 1 switch.

  5. Synchronize secondary nodes. For details, see “Synchronizing secondary servers”.