Are there hidden gotchas w.r.t. moving sysadmin tables?
David Grove (IDS 12.10.FC12, Solaris 10) wanted to drop a rootdbs chunk still holding the sysadmin table ph_run. SangGyu Jeong advised moving the table with ALTER FRAGMENT, dropping the chunk and, ideally, stopping the scheduler first. David warned the documented 'reset sysadmin' recreates sysadmin and loses data such as scheduler jobs. Gaurav Kumar linked IBM move steps.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
IDS 12.10.FC12 Solaris 10 1/13 I would like to drop a rootdbs chunk that is no longer needed... Except that it still contains a sysadmin table (happens to be ph_run). Is there any danger in using ALTER FRAGMENT to move that table to another dbspace, then DROP the (now empty) rootdbs chunk, and then again use ALTER FRAGMENT to move ph_run back to rootdbs (which would now be minus the chunk that I dropped [in which ph_run formerly resided])? Thank you. Regards, David Grove ------------------------------ David Grove ------------------------------ #Informix
Hi David, Just move and delete the table in the rootdbs chunk you want to delete. It is recommended that the db scheduler be stopped before moving the table. There is also an option to create a new sysadmin database in another dbspace. Ex) execute function task("reset sysadmin", "admindbs"); ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------
Thank you, SangGyu. That was the response I hoped for: move the table, delete the chunk, move the table back (to rootdbs). I did check the documentation, and found the "reset sysadmin" in a section about moving sysadmin. I almost fell into the trap. I think the documentation is very misleading. It describes the function as moving sysadmin. As if that is the intended functionality. But that is not the case. (admittedly, the name hints at this.) One might easily infer that the sysadmin database would be moved, and miss the language at the end of that section that mentions that all your data is lost because the result is that sysadmin is recreated as it was with only the original content. (Scheduler jobs lost, for instance.) It really makes me wonder why Informix doesn't provide a mechanism to actually move sysadmin, and not re-initialize it. Anyway, thank you for your reply. Regards, David Grove ------------------------------ David Grove ------------------------------
Steps to move sysadmin https://www.ibm.com/support/pages/moving-resetting-sysadmin-database-new-dbspace-ibm-informix-dynamic-server-ids ------------------------------ Gaurav Kumar ------------------------------
Thank you, Gaurav, for providing that link. It is more detailed than in the "regular" documentation. One has to be careful, though. Following that recipe will result in loss of any changes that have been made to sysadmin (for example, Scheduler jobs). (Aside: IMO, it is suboptimal for IBM to have intentionally made databases that are no longer independent (as in self-contained) units. One can no longer simply move a database to a new location and have the original behavior fully preserved, by default. I realize others may perceive that differently, but that is my perspective.) ------------------------------ David Grove ------------------------------