IDS Forum

RE: Move a table to another DB

Posted By: NormaJean.Sebastian@tellabs.com
Date:

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

e1xydGYxXGFuc2lcYW5zaWNwZzEyNTJcZnJvbXRleHQgXGRlZmYwe1xmb250dGJsDQp7XGYw
XGZzd2lzcyBBcmlhbDt9DQp7XGYxXGZtb2Rlcm4gQ291cmllciBOZXc7fQ0Ke1xmMlxmbmls
XGZjaGFyc2V0MiBTeW1ib2w7fQ0Ke1xmM1xmbW9kZXJuXGZjaGFyc2V0MCBDb3VyaWVyIE5l
dzt9fQ0Ke1xjb2xvcnRibFxyZWQwXGdyZWVuMFxibHVlMDtccmVkMFxncmVlbjBcYmx1ZTI1
NTt9DQpcdWMxXHBhcmRccGxhaW5cZGVmdGFiMzYwIFxmMFxmczIwIEhpLFxwYXINClxwYXIN
ClExOiBFeHBlcmltZW50IHdpdGggUERRL1BTT1JUX05QUk9DUyB0byBoZWxwIHlvdXIgc3Bl
ZWQuICBUcnkgdGhlIEhQTG9hZGVyIChJIGNhbid0LCBJIGFtIHVzaW5nIFNBUChhcHBsaWNh
dGlvbiksIGJ1dCBJIGhhdmUgaGVhcmQvc2VlbiB3b25kZXJmdWwgdGhpbmdzIGFib3V0IEhQ
TG9hZGVyKS4gIERvbid0IGNyZWF0ZSBpbmRpY2VzIG9uIHRoZSBuZXcgdGFibGUgdW50aWwg
YWZ0ZXIgdGhlIGxvYWQgKGJ1dCBvZiBjb3Vyc2UsIGl0IHN0aWxsIHRha2VzIHRpbWUpLlxw
YXINClxwYXINClEyOiAgSWYgeW91IGFkZCB0aGUgbmV3IGZpZWxkIHRvIHRoZSB0YWJsZSBB
VCBUSEUgRU5EIE9GIFRIRSBUQUJMRSwgeW91IHdpbGwgYmUgZG9pbmcgYW4gImluIHBsYWNl
IGFsdGVyIiwgbWVhbmluZyB5b3Ugd2lsbCBleHBlcmllbmNlIHRoZSBqb3kgb2YgSW5mb3Jt
aXggdGFibGUgdmVyc2lvbmluZy4gIEluZm9ybWl4IHdpbGwgbGVhdmUgYWxsIHRoZSBjdXJy
ZW50IHJvd3MgYXMgJ3ZlcnNpb24gMCcgb2YgdGhlIHRhYmxlLiBJdCB3aWxsIG5vdCBuZWVk
IHRvIHByb2Nlc3MgdGhyb3VnaCB0aGUgdGFibGUgYW5kIGV4cGFuZCBhbGwgcm93cy4gIEFu
eSBuZXcgcm93cyAob3IgdXBkYXRlZCByb3dzKSB3aWxsIGJlIGFkanVzdGVkIHRvIGFkZCBz
cGFjZSBmb3IgdGhlIG5ldyBmaWVsZCBhbmQgYmUgcGFydCBvZiAndmVyc2lvbiAxJyBvZiB0
aGUgdGFibGUuICBMYXRlciwgaWYgeW91IHJlb3JnIHRoZSB0YWJsZSAodG91Y2ggYWxsIHJv
d3MpIHlvdSBtYXkgaGF2ZSBzcGFjZSBpc3N1ZXMgZGVwZW5kaW5nIG9uIHJvdyBsZW5ndGgu
ICBUbyBzZWUgaG93IG1hbnkgcm93cyBhcmUgaW4gZWFjaCB2ZXJzaW9uIG9mIHRoZSB0YWJs
ZSwgcnVuICdvbmNoZWNrIC1wVCcgb24gdGhlIHRhYmxlICgnb25jaGVjayAtcHQnIGRvZXMg
bm90IHNob3cgdmVyc2lvbmluZykuICBXaXRoIGluLXBsYWNlIGFsdGVyL3RhYmxlIHZlcnNp
b25pbmcsIGFkZGluZyBhIG5ldyBmaWVsZCBhdCB0aGUgZW5kIG9mIGEgdGFibGUsIG5vIG1h
dHRlciBob3cgbWFueSByb3dzIGFyZSBpbiB0aGUgdGFibGUsIGlzIHF1aWNrIGFuZCBlYXN5
LiAgXHBhcg0KSSBkb24ndCB0aGluayB0aGUgc2FtZSBhcHBsaWVzIHdoZW4gY2hhbmdpbmcg
ZGVmaW5pdGlvbiBvZiBhbiBleGlzdGluZyBmaWVsZCAoaW50ZWdlciAtPiBjaGFyKDEwKSku
Li4gb3RoZXIgcGVvcGxlIG1heSBrbm93IG1vcmUuXHBhcg0KXHBhcg0KZ29vZCBsdWNrLFxw
YXINCk5vcm1hIEplYW5ccGFyDQpccGFyDQpccGFyDQpccGFyDQotLS0tLU9yaWdpbmFsIE1l
c3NhZ2UtLS0tLVxwYXINCkZyb206IHBhc0Bsb2dpZmVyLmdyIFttYWlsdG86cGFzQGxvZ2lm
ZXIuZ3JdXHBhcg0KU2VudDogVGh1cnNkYXksIEF1Z3VzdCAxNCwgMjAwMyAyOjMxIEFNXHBh
cg0KVG86IGlkc0BpaXVnLm9yZzsgZm9ydW0uc3Vic2NyaWJlckBpaXVnLm9yZ1xwYXINClN1
YmplY3Q6IE1vdmUgYSB0YWJsZSB0byBhbm90aGVyIERCIFsxNjg4XVxwYXINClxwYXINClxw
YXINCkhpIGFsbCAgIChhbmQgdGhhbmtzIGZvciB0aGUgYW5zd2VycyB0byBteSBwcmV2aW91
cyBxdWVzdGlvbilccGFyDQpccGFyDQpRMS4gSURTIDcueCAvIFNvbGFyaXNccGFyDQogICAg
SSBoYXZlIG9uZSBJbmZvcm1peCBEUyBhbmQgdHdvIERCJ3MuIEkgd2FudCB0byAibW92ZSIg
YSB0YWJsZSBmcm9tIG9sZF9EQiB0byB0aGUgbmV3X0RCLiBJIGNvdWxkIHVubG9hZCAtIGxv
YWQgdGhlIGRhdGEgb3IgaW5zZXJ0IGludG8gbmV3Li5zZWxlY3QgZnJvbSBvbGQuLi4gQlVU
IHRoZSB0YWJsZSBoYXMgMiw1IG1pbGxpb24gcmVjcyBhbmQgdGhpcyBwcm9jZXNzIG5lZWRz
IGEgbG90IG9mIHRpbWUuXHBhcg0KIFxwYXINCiAgICBJcyB0aGVyZSBhbnl0aGluZyBlbHNl
IEkgY291bGQgZG8gP1xwYXINClxwYXINClEyLiBXaGF0IGlmIHRoZSB0d28gdGFibGVzIGFy
ZSBOT1QgaWRlbnRpY2FsPyBGb3IgZXhhbXBsZSBJICdkIGxpa2UgdG8gYWRkIGFub3RoZXIg
ZmllbGQgdG8gbmV3IHRhYmxlICh3aXRoIGEgbnVtZXJpYyBkZWZhdWx0IHZhbHVlIG51bGwg
b3IgemVybykuIE9yLCBjaGFuZ2UgYSBmaWVsZCBmcm9tIGludGVnZXIgdG8gY2hhcigxMCku
XHBhcg0KXHBhcg0KVGhhbmtzIGluIGFkdmFuY2VccGFyDQpccGFyDQpQYW50ZWxpcy4gQXRo
ZW5zLCBHcmVlY2VccGFyDQpccGFyDQotLS1ccGFyDQpPdXRnb2luZyBtYWlsIGlzIGNlcnRp
ZmllZCBWaXJ1cyBGcmVlLlxwYXINCkNoZWNrZWQgYnkgQVZHIGFudGktdmlydXMgc3lzdGVt
IChodHRwOi8vd3d3LmdyaXNvZnQuY29tKS5ccGFyDQpWZXJzaW9uOiA2LjAuNDk1IC8gVmly
dXMgRGF0YWJhc2U6IDI5NCAtIFJlbGVhc2UgRGF0ZTogMzAvNi8yMDAzXHBhcg0KIFxwYXIN
ClxwYXINClxwYXINCn0=

--openmail-part-40aaab9c-00000002--

Messages In This Thread

• Move a table to another DB
  Pantelis Magos -- Thursday, 14 August 2003, at 3:31 a.m.
  • Re: Move a table to another DB
    Mark Denham -- Thursday, 14 August 2003, at 8:29 a.m.
    • Re: Move a table to another DB
      Darren_Jacobs@carmax.com -- Thursday, 14 August 2003, at 9:42 a.m.
    • Re: Move a table to another DB
      David Reed -- Thursday, 14 August 2003, at 11:57 p.m.
  • RE: Move a table to another DB
    NormaJean.Sebastian@tellabs.com -- Thursday, 14 August 2003, at 8:47 a.m.
  • Re: Move a table to another DB
    ramesh.vasudevan@verizon.com -- Thursday, 14 August 2003, at 9:26 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.