Converting simplex databases to multiplex

To change a simplex database to a multiplex, you simply connect to the simplex database and add a multiplex server. The simplex database server becomes the coordinator and the servers you add are called the secondary servers. You may use Sybase Central or Interactive SQL statements to create multiplex servers.

Converting databases to multiplex (Sybase Central)

If you are new to running Sybase Central, see “Using Sybase Central”.

DBA authority is required to perform the following steps.

Make sure that your system meets hardware prerequisites. The main store dbfiles must be visible to secondary nodes. See “Multiplex prerequisites”. and “Accessing the shared disk array”.
Start Sybase Central using the method appropriate for your platform.
Use the Start Server Wizard to start a Sybase IQ server.
Connect to the IQ server.
Right-click the server name and choose Convert to Multiplex (Alt+M). If you do not have DBA authority, the Convert to Multiplex menu item is disabled.
Follow the instructions on the Create Server Wizard screens.

Converting databases to multiplex (Interactive SQL)

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.

Make sure that your system meets hardware prerequisites. The main store dbfiles must be visible to secondary nodes. See “Multiplex prerequisites”. and “Accessing the shared disk array”.
Start a Sybase IQ server and connect to a database with a shareable main store. This server becomes the coordinator of the multiplex.

For example:
```
start_iq @params.cfg -n mpxnode_c -x "tcpip{host=host1;port=2763}" mpxtest.db
```
Connect using Interactive SQL, for example:
```
dbisql
```
In Interactive SQL, run the CREATE MULTIPLEX SERVER command, for example:
```
CREATE MULTIPLEX SERVER mpxnode_w1 DATABASE 'host2/mpx/mpxtest.db' HOST 'host2' PORT 2957 ROLE WRITER STATUS INCLUDED
```
For command reference details, see “CREATE MULTIPLEX SERVER statement”.

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. For example:

start_iq @params.cfg -n mpxnode_c -x "tcpip{host=host1;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.
Make sure that you 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.) For example:
```
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 the ALTER DBSPACE statement in Chapter 1, “SQL Statements,” in Reference: Statements and Options.