Converting Databases to Multiplex

To change a simplex database to a multiplex, connect to the simplex database and use CREATE MULTIPLEX SERVER.

Prerequisites

In a multiplex environment, all IQ main store file paths must be absolute or relative in a way accessible to all servers in the multiplex.

Task

Make sure that your system meets hardware prerequisites. The main store dbfiles must be visible to secondary nodes.
Start an SAP Sybase IQ server and connect to a database with a shareable main store. This server becomes the coordinator of the multiplex:
```
start_iq @params.cfg -n mpxnode_c -x "tcpip{port=2763}" mpxtest.db
```
Connect using Interactive SQL:
```
dbisql
```
In Interactive SQL, run the CREATE MULTIPLEX SERVER command:
```
CREATE MULTIPLEX SERVER mpxnode_w1 DATABASE 
'<database path>/mpxtest.db' HOST 'host2' PORT 2957 ROLE WRITER STATUS INCLUDED
```
This command makes the connected server mpxnode_c into the coordinator and creates the definition for a secondary writer server mpxnode_w1.
The server shuts down to re-initialize the server as a multiplex coordinator. (As a normal part of this shutdown, when the first secondary node is created, Interactive SQL may return a disconnected (-308) error that requires no corrective action.)

Restart the coordinator.

start_iq @params.cfg -n mpxnode_c -x "tcpip{port=2763}" mpxtest.db

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.