IDS Forum

Re: Moving a single table from one database to....

Posted By: Art Kagel
Date: Wednesday, 31 August 2011, at 2:38 p.m.

In Response To: Re: Moving a single table from one database to ano (MARK JALKIEWICZ)

Hmm, interesting idea, but the OP will not have to unload the data at all
this way. The steps would be:

1. Create a new dbspace.

2. ALTER FRAGMENT ON TABLE <tablename> INIT IN <new dbspace>;

3. Create the new table in the new database residing in the original

dbspace (set the FIRST EXTENT SIZE appropriately to minimize space

allocations during the copy).

4. INSERT INTO <new database>:<tablename> SELECT * FROM <tablename>;

If the logging modes of the databases are different (ie one logged and the
other not logged), which will prevent step 4 from working, you can use by
dbcopy utility (in utils2_ak) to copy between the tables. Between tables on
the same server it's not noticably faster than the INSERT in step 4 so I
didn't suggest using it there but it can work between databases with
incompatible logging modes.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.

On Wed, Aug 31, 2011 at 2:31 PM, MARK JALKIEWICZ <
mark.jalkiewicz@verizon.net> wrote:

> your problem is space... test this first on a small table to see how and if
> it
> works out :
>
> check and see if there is space ANYWHERE on the file system where you can
> allocate it to the dbspace and just add it (u can use cooked file). Problem
> is
> that the new table will go there.. so after you populate it you have drop
> the
> original table , unload the new table, drop the new table and then
> recreate/load which *in theory* should put the new table in the same chunks
> the original resided in. This should empty out the new chunk so u can drop
> it
> .. No guarantee, depends on how the free extents look like in the dbspace.
> If
> the largest extent in the dbspace after dropping the original table is say
> 128K make your extent size slightly smaller on the remake of the new table
> to
> see if the engine takes the bait and places the table in that location.
> Make
> sure you include 'in dbspace xxx" in your create table schema otherwise the
> table gets created in the dbspace the database was created in. Then once
> done
> make sure there is no data in the newly added chunk so you can drop it
> (oncheck -pe <dbspace>
>
> to speed things along with the first load/unload :
>
> mkfifo table.unl
>
> echo "unload to table.unl select * from table" | dbaccess db1
>
> hit <enter key> to start unload
>
> in another window :
>
> echo "load from table.unl insert into table ;" | dbaccess db2
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--90e6ba21241d55988804abd170da

Messages In This Thread

• Moving a single table from one database to another
  Schleicher, Keith -- Wednesday, 31 August 2011, at 12:55 p.m.
  • Re: Moving a single table from one database to ano
    Paul Watson -- Wednesday, 31 August 2011, at 1:06 p.m.
    • Re: Moving a single table from one database to....
      Peter_Logan@spartanstores.com -- Wednesday, 31 August 2011, at 1:17 p.m.
      • Re: Moving a single table from one database to....
        Paul Watson -- Wednesday, 31 August 2011, at 1:20 p.m.
        • Re: Moving a single table from one database to....
          Paul Watson -- Wednesday, 31 August 2011, at 1:22 p.m.
    • Re: Moving a single table from one database to....
      Jonathan Leffler -- Wednesday, 31 August 2011, at 1:24 p.m.
      • RE: Moving a single table from one database to....
        Knox, Ernest -- Wednesday, 31 August 2011, at 1:50 p.m.
    • Re: Moving a single table from one database to....
      Art Kagel -- Wednesday, 31 August 2011, at 1:57 p.m.
    • Re: Moving a single table from one database to....
      Obnoxio The Clown -- Wednesday, 31 August 2011, at 2:34 p.m.
      • Re: Moving a single table from one database to....
        Paul Watson -- Wednesday, 31 August 2011, at 2:35 p.m.
  • Re: Moving a single table from one database to ano
    Art Kagel -- Wednesday, 31 August 2011, at 2:01 p.m.
  • Re: Moving a single table from one database to ano
    MARK JALKIEWICZ -- Wednesday, 31 August 2011, at 2:31 p.m.
    • Re: Moving a single table from one database to....
      Art Kagel -- Wednesday, 31 August 2011, at 2:38 p.m.
  • Re: Moving a single table from one database to ano
    Khaled Bentebal -- Wednesday, 31 August 2011, at 2:57 p.m.
  • Re: Moving a single table from one database to ano
    Obnoxio The Clown -- Wednesday, 31 August 2011, at 7:04 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.