Move a table to another DB

Hi all (and thanks for the answers to my previous question) Q1. IDS 7.x / Solaris I have one Informix DS and two DB's. I want to "move" a table from old_DB to the new_DB. I could unload - load the data or insert into new..select from old... BUT the table has 2,5 million recs and this process needs a lot of time. Is there anything else I could do ? Q2. What if the two tables are NOT identical? For example I 'd like to add another field to new table (with a numeric default value null or zero). Or, change a field from integer to char(10). Thanks in advance Pantelis. Athens, Greece --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.495 / Virus Database: 294 - Release Date: 30/6/2003

----- Original Message ----- From: "Pantelis Magos" <pas@logifer.gr> To: <ids@iiug.org> Sent: Thursday, August 14, 2003 03:31 Subject: Move a table to another DB [1688] > Hi all (and thanks for the answers to my previous question) > > Q1. IDS 7.x / Solaris > I have one Informix DS and two DB's. I want to "move" a table from old_DB to the new_DB. I could unload - load the data or insert into new..select from old... BUT the table has 2,5 million recs and this process needs a lot of time. > > Is there anything else I could do ? Unload using HPL to a pipe, import via the pipe into the new table. Use express mode. > > Q2. What if the two tables are NOT identical? For example I 'd like to add another field to new table (with a numeric default value null or zero). Or, change a field from integer to char(10). > Again you can use HPL for this, if memory serves me correctly. I don't think that express mode will be usable in this case. Mark

Hi, Q1: Experiment with PDQ/PSORT_NPROCS to help your speed. Try the HPLoader (I can't, I am using SAP(application), but I have heard/seen wonderful things about HPLoader). Don't create indices on the new table until after the load (but of course, it still takes time). Q2: If you add the new field to the table AT THE END OF THE TABLE, you will be doing an "in place alter", meaning you will experience the joy of Informix table versioning. Informix will leave all the current rows as 'version 0' of the table. It will not need to process through the table and expand all rows. Any new rows (or updated rows) will be adjusted to add space for the new field and be part of 'version 1' of the table. Later, if you reorg the table (touch all rows) you may have space issues depending on row length. To see how many rows are in each version of the table, run 'oncheck -pT' on the table ('oncheck -pt' does not show versioning). With in-place alter/table versioning, adding a new field at the end of a table, no matter how many rows are in the table, is quick and easy. I don't think the same applies when changing definition of an existing field (integer -> char(10))... other people may know more. good luck, Norma Jean -----Original Message----- From: pas@logifer.gr [mailto:pas@logifer.gr] Sent: Thursday, August 14, 2003 2:31 AM To: ids@iiug.org; forum.subscriber@iiug.org Subject: Move a table to another DB [1688] Hi all (and thanks for the answers to my previous question) Q1. IDS 7.x / Solaris I have one Informix DS and two DB's. I want to "move" a table from old_DB to the new_DB. I could unload - load the data or insert into new..select from old... BUT the table has 2,5 million recs and this process needs a lot of time. Is there anything else I could do ? Q2. What if the two tables are NOT identical? For example I 'd like to add another field to new table (with a numeric default value null or zero). Or, change a field from integer to char(10). Thanks in advance Pantelis. Athens, Greece --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.495 / Virus Database: 294 - Release Date: 30/6/2003 --openmail-part-40aaab9c-00000002 Content-Type: application/rtf Content-Disposition: attachment; filename="BDY.RTF" ;Creation-Date="Thu, 14 Aug 2003 07:44:12 -0500" Content-Transfer-Encoding: base64 {\\rtf1\\ansi\\ansicpg1252\\fromtext \\deff0{\\fonttbl {\\f0\\fswiss Arial;} {\\f1\\fmodern Courier New;} {\\f2\\fnil\\fcharset2 Symbol;} {\\f3\\fmodern\\fcharset0 Courier New;}} {\\colortbl\\red0\\green0\\blue0;\\red0\\green0\\blue255;} \\uc1\\pard\\plain\\deftab360 \\f0\\fs20 Hi,\\par \\par Q1: Experiment with PDQ/PSORT_NPROCS to help your speed. Try the HPLoader (I can't, I am using SAP(application), but I have heard/seen wonderful things about HPLoader). Don't create indices on the new table until after the load (but of course, it still takes time).\\par \\par Q2: If you add the new field to the table AT THE END OF THE TABLE, you will be doing an "in place alter", meaning you will experience the joy of Informix table versioning. Informix will leave all the current rows as 'version 0' of the table. It will not need to process through the table and expand all rows. Any new rows (or updated rows) will be adjusted to add space for the new field and be part of 'version 1' of the table. Later, if you reorg the table (touch all rows) you may have space issues depending on row length. To see how many rows are in each version of the table, run 'oncheck -pT' on the table ('oncheck -pt' does not show versioning). With in-place alter/table versioning, adding a new field at the end of a table, no matter how many rows are in the table, is quick and easy. \\par I don't think the same applies when changing definition of an existing field (integer -> char(10))... other people may know more.\\par \\par good luck,\\par Norma Jean\\par \\par \\par \\par -----Original Message-----\\par From: pas@logifer.gr [mailto:pas@logifer.gr]\\par Sent: Thursday, August 14, 2003 2:31 AM\\par To: ids@iiug.org; forum.subscriber@iiug.org\\par Subject: Move a table to another DB [1688]\\par \\par \\par Hi all (and thanks for the answers to my previous question)\\par \\par Q1. IDS 7.x / Solaris\\par I have one Informix DS and two DB's. I want to "move" a table from old_DB to the new_DB. I could unload - load the data or insert into new..select from old... BUT the table has 2,5 million recs and this process needs a lot of time.\\par \\par Is there anything else I could do ?\\par \\par Q2. What if the two tables are NOT identical? For example I 'd like to add another field to new table (with a numeric default value null or zero). Or, change a field from integer to char(10).\\par \\par Thanks in advance\\par \\par Pantelis. Athens, Greece\\par \\par ---\\par Outgoing mail is certified Virus Free.\\par Checked by AVG anti-virus system (http://www.grisoft.com).\\par Version: 6.0.495 / Virus Database: 294 - Release Date: 30/6/2003\\par \\par \\par \\par } --openmail-part-40aaab9c-00000002--

Hi: Answer 1: You can use High Performance Loader (HPL) to unload and load to quickly perform this move. Make sure create table without indexes at the destination server, load the data using HPL, create indexes and perform update stats. Answer 2: In order to change the int to char(10) - you can use alter table statement. In order to add another field to a table - use alter table statement. Add the new field with default clause (NULL or 0). Update all the rows for the new field with value 0 if needed. (update <table> set <new field> = 0 ;) Thank You Ramesh Vasudevan "Pantelis Magos" <pas@logifer.gr> To: ids@iiug.org Sent by: cc: forum.subscriber@ Subject: Move a table to another DB [1688] iiug.org 08/14/03 03:31 AM Hi all (and thanks for the answers to my previous question) Q1. IDS 7.x / Solaris I have one Informix DS and two DB's. I want to "move" a table from old_DB to the new_DB. I could unload - load the data or insert into new..select from old... BUT the table has 2,5 million recs and this process needs a lot of time. Is there anything else I could do ? Q2. What if the two tables are NOT identical? For example I 'd like to add another field to new table (with a numeric default value null or zero). Or, change a field from integer to char(10). Thanks in advance Pantelis. Athens, Greece --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.495 / Virus Database: 294 - Release Date: 30/6/2003

Pantelis, This is easy enough. Create your new table in the new db. lock the new table in exclusive mode, select from the old table and add a place holder for the new column, create your indexes on the new table and then update stats medium and high. The example below will assume old_tab has columns col_a,col_b,col_c, col_d and new_tab has columns col_a, col_b, col_c, col_d, col_e example (note: "default value" is your place holder and will be the new column in whatever location in the new table): database new_db; create table new_tab ( col_a,col_b,col_c, col_d, col_e) .... database old_db;begin work; lock table new_db@servername:new_tab in exlcusive mode; insert into new_tab (col_a, col_b, col_c, col_d, col_e) select col_a,col_b,col_c, col_d, "default value") from old_tab;commit work; database new_db;create index .... update stats Hope this helps. "Mark Denham" <mkdenham@comcast To: ids@iiug.org .net> cc: Sent by: Subject: Re: Move a table to another DB [1690] forum.subscriber@ iiug.org 08/14/03 08:29 AM ----- Original Message ----- From: "Pantelis Magos" <pas@logifer.gr> To: <ids@iiug.org> Sent: Thursday, August 14, 2003 03:31 Subject: Move a table to another DB [1688] > Hi all (and thanks for the answers to my previous question) > > Q1. IDS 7.x / Solaris > I have one Informix DS and two DB's. I want to "move" a table from old_DB to the new_DB. I could unload - load the data or insert into new..select from old... BUT the table has 2,5 million recs and this process needs a lot of time. > > Is there anything else I could do ? Unload using HPL to a pipe, import via the pipe into the new table. Use express mode. > > Q2. What if the two tables are NOT identical? For example I 'd like to add another field to new table (with a numeric default value null or zero). Or, change a field from integer to char(10). > Again you can use HPL for this, if memory serves me correctly. I don't think that express mode will be usable in this case. Mark

I have not used HPL before, just a few questions. Does HPL work on windows NT, and if so, how long would it take to load 1 million records. Can the HP loader be automated from a remote unix box to a win NT box with a 4GL program David -----Original Message----- From: Mark Denham <mkdenham@comcast.net> To: ids@iiug.org <ids@iiug.org> Date: 14 August 2003 15:13 PM Subject: Re: Move a table to another DB [1690] > >----- Original Message ----- >From: "Pantelis Magos" <pas@logifer.gr> >To: <ids@iiug.org> >Sent: Thursday, August 14, 2003 03:31 >Subject: Move a table to another DB [1688] > > >> Hi all (and thanks for the answers to my previous question) >> >> Q1. IDS 7.x / Solaris >> I have one Informix DS and two DB's. I want to "move" a table from >old_DB to the new_DB. I could unload - load the data or insert into >new..select from old... BUT the table has 2,5 million recs and this process >needs a lot of time. >> >> Is there anything else I could do ? > >Unload using HPL to a pipe, import via the pipe into the new table. Use >express mode. > >> >> Q2. What if the two tables are NOT identical? For example I 'd like to add >another field to new table (with a numeric default value null or zero). Or, >change a field from integer to char(10). >> >Again you can use HPL for this, if memory serves me correctly. I don't think >that express mode will be usable in this case. > >Mark > >