Initializing the sysadmin database

Starting with Informix 11, there is a sysadmin database for database administration.

Because the sysadmin database contains Informix state information, it is recommended that you periodically delete the data if the monitoring data is accumulated in the database, or place the sysadmin database in a separate dbspace.

To regenerate the sysadmin database, you can use a script in the directory where Informix is ​​installed or the admin / task procedure in the sysadmin database.


1. Manually rebuild the sysadmin database

According to IBM Technote, there is a script in $INFORMIXDIR/etc/sysadmin to regenerate the sysadmin database.

Execute the following commands in order.


cd $INFORMIXDIR/etc/sysadmin;
dbaccess - db_uninstall.sql;
dbaccess - db_create.sql;
dbaccess sysadmin db_install.sql;
dbaccess sysadmin sch_tasks.sql;
dbaccess sysadmin sch_aus.sql;
dbaccess sysadmin sch_sqlcap.sql;
dbaccess sysadmin start.sql;


Looking at the contents of the db_create.sql file, there is a syntax for creating a database.


CREATE DATABASE sysadmin WITH LOG;


You can also modify the contents of the file to specify a dbspace with this syntax.


2. Using the procedures in the sysadmin database to move the sysadmin database to another dbspace

You can regenerate the sysadmin database using the admin / task procedure in the sysadmin database.


execute function task ("reset sysadmin", "admindbs");


If you do not specify a dbspace name, it is created in rootdbs. When regeneration is complete, the scheduler will restart automatically.



** Reference
https://www-01.ibm.com/support/docview.wss?uid=swg21266296
https://www-01.ibm.com/support/docview.wss?uid=swg21420189