Expand all | Collapse all

"Breaking out" a database

  • 1.  "Breaking out" a database

    Posted Tue September 22, 2020 07:32 PM
    IDS 12.10.FC14
    Solaris 10 1/13

    I have a legacy database that was laid out based on the Oracle S.A.M.E. (Stripe And Mirror Everything) strategy.  It has served us well for many years.  We have had no problem with performance, and there has been nearly zero admin effort on space issues (table organization, dbspaces layout, etc.)

    But, now I would like to "evolve it" a little.  I want to do two things, immediately; and, possibly a third thing a bit later.

    First two things I want to do:
    1) Put indexes into a separate dbspace.  I want to use wide (16K) pages for indexes.  (Our current dbspace pages are 2K.)
    2) Move the database out of the root dbspace.

    I would solicit opinions related to these objectives:

    1) How best to move the indexes?

                      My first thought is to write a script using ALTER FRAGMENT.  Or, I wonder if HPL could be of use (I have to admit that I have only rarely used HPL, and need to "bone up" on it).

    2) Does the order matter?

                        That is, move the indexes to a new dbspace, and then use dbexport/dbimport to move the database (system catalog and all tables) to a new dbspace.  Or, move the database first, then move the indexes.

    At some later time, I will work on creating multiple dbspaces and placing tables (possibly fragmented) into them.  But, steps 1 & 2, above, are first.

    Thank you for any comments.



    David Grove

  • 2.  RE: "Breaking out" a database

    Posted Tue September 22, 2020 09:50 PM
    Maybe a more direct approach of dropping, and then rebuilding indexes, in the new dbspace would be better?

    David Grove

  • 3.  RE: "Breaking out" a database

    Posted Wed September 23, 2020 04:27 AM
    Hi David,

    Yes you can drop the indexes and create them in 16k dbspace. But the point is when you are planning to move out your database from roodbs, then why recreating indexes at the first step. Because if you move the table later from your root dbspace to someother dbspace, you have to drop the table in rootdbs which will also drop your indexes in the new dbspace.

    So i think you can use dbexport/dbimport and modify the script to change the dbspaces of indexes and tables where ever you want.

    Else you can also use the manual way (LOAD/UNLOAD), first create a new raw table in new dbsapce and move all its data (insert into * from ...) syntax or load/unload, change the table logging, and  create index to the new dbspace. Later in the downtime, just rename the table to original table name after loading the delta rows...

    Other option is as you are on 1210FC14, you can Use loopback replication for tables..

    Again, that all depends on how you want to move your data. although it would be a tedious job for you, but you can do that in bits & pieces.

    And if you want to do in one shot, then dbexport/dbimport is the best way

    Gaurav Kumar

  • 4.  RE: "Breaking out" a database

    Posted Tue September 22, 2020 11:32 PM

    The only way to move the database's catalog tables is to export the data and schema (easiest using dbexport or myexport), drop the database, then recreate it, and reload it (again easiest with dbimport or myimport). So moving the indexes first will not work.

    Using dbexport/dbimport or myexport/myimport will create the indexes for you. With dbexport/dbimport you will have to use the -ss option to include storage clauses. However if the tables and indexes are all in the roots with the catalogs dbexport will not generate any storage clauses, so you will have to enter them manually. Also dbexport will not break out constraint indexes so you will have to manually write those index DDL commands in order to move them to 16k pages.

    Myexport/myimport will handle all of that for you and allow you to offer a mapping file automatically moving the indexes or tables to different dbspaces. Also myexport and myimport can use external tables which is faster than the method that dbexport and dbimport use.


  • 5.  RE: "Breaking out" a database

    Posted Wed September 23, 2020 02:52 AM
    Edited by David Grove Wed September 23, 2020 02:55 AM
    Thank you for your thorough and helpful answer, Art.

    I should have realized that dbexport/dbimport would do the indexes, too.  I have done enough of those export/import operations that I recognize the inaptness of even asking the question.

    I had done some experiments with your myimport/myexport some months back.  The advantages you describe are very strong.  I'll have to take another look.

    Thank you.


    P.S.  That database has been in use in its original structure (aside from increasing disk space a few times) with great success for over 15 years.

    David Grove

  • 6.  RE: "Breaking out" a database

    Posted Wed September 23, 2020 04:21 AM
    Hi David,

    It would good to know the rough size of your tables or database when answering your question. The fact you are considering HPL would suggest it's fairly large. If not, HPL is not worth the effort.

    Loopback replication is a good option if minimal downtime is a requirement. It is not that difficult to set up but it does need work on sqlhosts so a bounce of your system would be needed.

    If you do use dbexport/dbimport you can export without the -ss option and set AUTOLOCATE when importing. Providing you are happy with the results and what it provides, you can avoid adding any storage clauses before the import or just add storage clauses to selected tables and indices.


    Benjamin Thompson

  • 7.  RE: "Breaking out" a database

    Posted Wed September 23, 2020 01:56 PM

    Is it only the indexes you want to move to a DBspace with bigger pages?  Howzabout the table itself?  Are you happy with the page sizes and average bytes wasted per row?

    The way I used to reorg tables to move them to other spaces and properly fragment them was as follows:
    1. Create a new table name table_new identical to table but in the new DLL specify your fragmentation scheme.
    2. Create a separate DLL script to mimic the index creation, where you can specify fragmentation schemes for each index.  In this DLL, include SET PDQPRIORITY to something reasonable.  But specify table_new and index_new for each index.
    3. Here's where I'm in competition with Art  :-) :  Download my Perl utility, fcopy-table from this link.  And, of course, unpack it.
    4. Run perldoc to see how to run the command.
    5. Run fcopy-table according to the above documentation.   It will issue a time-stamped message at every commit so you can pace it.
    6. At the shell level, set PSORT_NPROCS and PSORT_DBTEMP to some reasonable values and run them to build the indexes.
    7. Rename the original table to table_old.  Then rename table_new to table.  You might want to rename the indexes also but you gotta lose the old indexes first.
    8. And you are done.
    What if the indexes are ginormous and each "create index' transaction runs the risk of a LTX rollback?  Well, you might want to create the indexes on the empty table (steps 2, 6) and then start the copy.   Obviously the copy will not be as fast as on an unindexed table.

    This is, of course, more work than Art's solution but I think you's have more control over the minutiae of the process.

    Best of luck!

    -- Jacob S

  • 8.  RE: "Breaking out" a database

    Posted 30 days ago
    Thank you, Ben and Jacob.

    Regarding size... a Level 0 archive is about 40GB, so not too large.

    Not sure if AUTOLOCATE would work well... I have not used it before, but, it appears that I wouldn't be able to specify one (or a set of ) dbspace for indexes, and another for tables, and a sbspace for smart blobs.  But, it does sound like Art's myimport might let me control that.

    Jacob's method might let me move tables, locking only one at a time, in a live database.  But, I would still have to create table-specific DDL, which I don't think I would with Art's utilities.

    I think I want to do it in phases.  First phase, just move the database to a new sbspace.  Second phase, move the tables and indexes to new dbspaces.  (With suitable mapping, I'm thinking that could be combined with phase one, if using Art's utilities, and without creating individual DDL scripts for each table & index.  Final phase would be fragmenting (which tables and how to fragment still needs to be worked out).

    I must give more thought to this adventure.

    Thank you, all, for you comments.



    David Grove