Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Are there hidden gotchas w.r.t. moving sysadmin tables?

  • 1.  Are there hidden gotchas w.r.t. moving sysadmin tables?

    Posted Sun June 07, 2020 09:50 PM
    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
    ------------------------------


  • 2.  RE: Are there hidden gotchas w.r.t. moving sysadmin tables?

    Posted Mon June 08, 2020 01:17 AM
    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
    ------------------------------



  • 3.  RE: Are there hidden gotchas w.r.t. moving sysadmin tables?

    Posted Mon June 08, 2020 01:52 AM
    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
    ------------------------------



  • 4.  RE: Are there hidden gotchas w.r.t. moving sysadmin tables?

    Posted 29 days ago
    Steps to move sysadmin

    https://www.ibm.com/support/pages/moving-resetting-sysadmin-database-new-dbspace-ibm-informix-dynamic-server-ids

    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 5.  RE: Are there hidden gotchas w.r.t. moving sysadmin tables?

    Posted 29 days ago
    Edited by David Grove 29 days ago
    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
    ------------------------------