Moving BIG smart large objects
Hello, I need recommendation how to move BIG tables with CLOB/BLOB between to physical servers in effective way. We have system where data layout (schemas) will be changed (reason is - to optimize it in respect of current requirements) This source system (current production) runs on IDS 11.70 (OS AIX) The destination (future production db-system) will be run on IDS 12.10 (OS AIX). Layout change is the reason of disqualification onBAR or other binary utilities from using it. Clasic Unload/Load are very slow (there is about 4TB of LOBs) When I use HPL or External Tables speed is OK but there is prerequisite of big file system to unload the data and I don't have so much space for it. HPL and External table have an option to use named pipes that resolve my problem with space for unload for all data with the exception of LOBs. My question is: Is there any way to move BIG tables with LOBs without unoading LOBs to files? Thanks a lot for any answers lempo
Does the new box not have enough disk, either? If it does, you can NFS mount some of that onto your old box. 4TB isn't that much any more -- Regards Spokey > On 8 Dec 2014, at 12:08, Peter Lempochner <peter.lempochner@dignitas.sk> wrote: > > Hello, > > I need recommendation how to move BIG tables with CLOB/BLOB between to > physical servers in effective way. > > We have system where data layout (schemas) will be changed (reason is - > to optimize it in respect of current requirements) > This source system (current production) runs on IDS 11.70 (OS AIX) > The destination (future production db-system) will be run on IDS 12.10 > (OS AIX). > > Layout change is the reason of disqualification onBAR or other binary > utilities from using it. > Clasic Unload/Load are very slow (there is about 4TB of LOBs) > > When I use HPL or External Tables speed is OK but there is prerequisite > of big file system to unload the data > and I don't have so much space for it. > HPL and External table have an option to use named pipes that resolve my > problem with space for unload > for all data with the exception of LOBs. > > My question is: > Is there any way to move BIG tables with LOBs without unoading LOBs to > files? > > Thanks a lot for any answers > > lempo > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Thanks for reaction. Either (old and new box) have spaces on disk array and space is problem in those environments - this is simply a fact I have to live with it :-( In fact I can write scripts to split my "file system problem" to several smaller pieces. I`m searching for a way move data with LOBs without using filesystem. If there is no such way I will unload/load it through the NFS in several steps ;-) lempo Dòa 8.12.2014 13:54 Spokey Wheeler wrote / napísal(a): > Does the new box not have enough disk, either? If it does, you can NFS mount > some of that onto your old box. 4TB isn't that much any more >
Why not copy the data directly from server to server with no disk in between? You could use INSERT INTO .... SELECT * FROM ... or use my dbmove.ec utility in the utils2_ak package which will be a bit faster. For tables that have not smart blob (BLOB or CLOB) or LVARCHAR columns and have one or no dumb blob (TEXT or BYTE) columns you can use my dbcopy.ec utility which is even faster (never added code to dbcopy to handle smart blobs, lvarchars are a problem due to a CSDK bug that's never been fixed, and dbcopy gets confused if there are two or more dumb blob columns - haven't gotten around to fixing that one yet). Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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 Mon, Dec 8, 2014 at 10:08 AM, Peter Lempochner < peter.lempochner@dignitas.sk> wrote: > Thanks for reaction. > > Either (old and new box) have spaces on disk array and space is problem > in those environments - this is simply a fact I have to live with it :-( > In fact I can write scripts to split my "file system problem" to several > smaller pieces. > > I`m searching for a way move data with LOBs without using filesystem. > If there is no such way I will unload/load it through the NFS in several > steps ;-) > > lempo > > Da 8.12.2014 13:54 Spokey Wheeler wrote / napísal(a): > > Does the new box not have enough disk, either? If it does, you can NFS > mount > > some of that onto your old box. 4TB isn't that much any more > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0158c302d8ebb70509b5eba1
Hello Art, Thank`s for your recommendations. I will try your utility dbmove (my tables includes LVARCHAR and more than one LOBs) Best regards lempo Dòa 8.12.2014 16:17 Art Kagel wrote / napísal(a): > Why not copy the data directly from server to server with no disk in > between? > > You could use INSERT INTO .... SELECT * FROM ... or use my dbmove.ec > utility in the utils2_ak package which will be a bit faster. For tables > that have not smart blob (BLOB or CLOB) or LVARCHAR columns and have one or > no dumb blob (TEXT or BYTE) columns you can use my dbcopy.ec utility which > is even faster (never added code to dbcopy to handle smart blobs, lvarchars > are a problem due to a CSDK bug that's never been fixed, and dbcopy gets > confused if there are two or more dumb blob columns - haven't gotten around > to fixing that one yet). > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.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 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 Mon, Dec 8, 2014 at 10:08 AM, Peter Lempochner < > peter.lempochner@dignitas.sk> wrote: > >> Thanks for reaction. >> >> Either (old and new box) have spaces on disk array and space is problem >> in those environments - this is simply a fact I have to live with it :-( >> In fact I can write scripts to split my "file system problem" to several >> smaller pieces. >> >> I`m searching for a way move data with LOBs without using filesystem. >> If there is no such way I will unload/load it through the NFS in several >> steps ;-) >> >> lempo >> >> Da 8.12.2014 13:54 Spokey Wheeler wrote / napísal(a): >>> Does the new box not have enough disk, either? If it does, you can NFS >> mount >>> some of that onto your old box. 4TB isn't that much any more >>> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --089e0158c302d8ebb70509b5eba1 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ----- > No virus found in this message. > Checked by AVG - www.avg.com > Version: 2015.0.5577 / Virus Database: 4235/8701 - Release Date: 12/08/14 > >