Adding Multiplex Servers

Follow these steps to add multiplex servers from Interactive SQL.

Check server status. The coordinator must be running in order to add secondary servers.
In Interactive SQL, run the CREATE MULTIPLEX SERVER command.
For example:
```
CREATE MULTIPLEX SERVER mpxnode_w2 DATABASE 'host1/mpx/mpxtest.db' HOST 'host1' PORT 2957 ROLE WRITER STATUS INCLUDED
```
This command creates the definition for a secondary writer server mpxnode_w2.
Back up the catalog store to the directory where the secondary server will run.
From the secondary node machine, run this backup command from the computer where the secondary node will run, because the .db file should be on a local disk. For example, run the following command from the directory where the secondary node’s .db file resides:
```
dbbackup -y -d -c "uid=DBA;pwd=sql;links=tcpip{port=2763};eng=mpxnode_c"
```
If you have changed to the directory where the secondary node’s .db file will reside, you can specify a dot (.) instead of the path on the dbbackup command.
Remove the transaction log files in the directory where the secondary server will run:
```
rm -rf mpxtest.log
```
Run dblog to reset the transaction log:
```
dblog -r -t mpxtest.log mpxtest.db
```

Start the secondary server:

start_iq -STARTDIR /host2/mpx @params.cfg -n mpxnode_w1 -x "tcpip{port=2957}" mpxtest.db

Start Interactive SQL and connect to the secondary multiplex node:
```
dbisql -c "uid=DBA;pwd=sql;eng=mpxnode_w1;links=tcpip{port=2957}"
```
Add the temporary dbfile in Interactive SQL using the ALTER DBSPACE statement.
Secondary servers do not allow CREATE DBSPACE.
```
ALTER DBSPACE IQ_SYSTEM_TEMP ADD FILE mpxnode_w1_temp 'w1_temp1.iqtmp' SIZE 500
```
Starting the server creates a message log for you, but you must create the temporary store file yourself. You must connect to the secondary server to add a temp store file for that server. Adding temporary store files in multiplex always adds a file specifically to the server to which you are connected. You can start the server, but cannot run IQ queries until a temporary store is added.

For command reference details, see ALTER DATABASE in Reference: Statements and Options.

The first time you start the multiplex after adding a secondary server, the coordinator may return an error similar to:

I. 12/11 12:50:08. Trying to start TCPIP link ...
I. 12/11 12:50:08. Multiplex environment incorrect for this server
I. 12/11 12:50:08. Please connect and run procedure sp_iqmpxvalidate for help

This error occurs because the secondary server has no temporary dbfile in IQ_SYSTEM_TEMP.