Moving (Resetting) the sysadmin database to a new dbspace in IBM Informix Dynamic Server (IDS)

How do you move the sysadmin database to a new dbspace?


The sysadmin database is created in the root dbspace during database server initialization. This database is required for the Scheduler API and Remote Administration feature.

Use the SQL Administration API command RESET SYSADMIN to move the sysadmin database to a different dbspace. This command drops the sysadmin database from the root dbspace and recreates it in the specified dbspace.

1. Make sure this message is in the message log after database server startup:

SCHAPI: Started 2 dbWorker threads. 

2. Create a new dbspace where sysadmin will reside.

This example shows you how to create a new dbspace.

$ execute function task(“create dbspace”“admindbs”“/vol/dbspaces/admindbs”“1 GB”“0”);

Here is another method to create a dbspace.

$ onspaces -c -d admindbs -p /vol/dbspaces/admindbs -s 1000000 -o 0

3. Run these commands as user informix:

$ dbaccess sysadmin –

$ execute function task(“reset sysadmin”“admindbs”);

You will see this message:

SCHAPI: ‘sysadmin’ database will be moved to ‘admindbs’. See online message log.

The internal thread bld_sysadmin (seen via onstat -g ath) waits up to five minutes to obtain exclusive access to the sysadmin database. The progress of the bld_sysadmin thread is logged in the online message log. Exit dbaccess to allow sysadmin database move to complete. On successful completion the sysadmin database is dropped and recreated in the new dbspace. The dbScheduler and dbWorker threads are restarted automatically.