Re: Copying and Renamin Tables to diffent dbs

->From: bt@irl3b2c.att.com ->>To: informix-list@rmy.emory.edu ->Date: Fri Feb 4 9:05:38 GMT 1994 ->Subject: Copying and Renamin Tables to diffent dbs ->Cc: weller@zorro.cecer.army.mil ->Bonnie Weller (weller@zorro.cecer.army.mil) wrote: -> ->>I am new to Informix and have not found any reference to whether I can ->>copy or rename a table to a different database. What about copying ->>strucures to a new empty table and loading different data in a new ->>database. I know how to do this in FoxPro but have not seem similar ->>capabilities in Informix. -> ->>If I can do these things, how. And if these commands are in the Informix ->>books, where? I searched the indexes and found nothing. ->Barry Toomey (bt@irl3b2c.att.comm) replied: -> ->I had a somewhat similar sitation some years ago where I wanted to share a ->table between two databases. Having created the an identical table in the ->second database I then used the Unix ln function to link the tablename.dat ->and tablename.idx files from the first database to the second. -> ->You have to be careful, of course, because if you get the order wrong ->you'll wipe your original data. It seemed to work reasonably well, in that ->I, as the database owner, could update the table in *either* database and ->it would automatically update in the other, although I seem to remember ->other users having occasional permission problems on UPDATE or INSERT. I ->*know* this is totally undocumented, and probably unsupported by Informix, ->but is there any reason why it shouldn't work? -> ->I would think mv or cp should work likewise. Has anyone out there tried ->this? Alan Popiel (alan@po.den.mmc.com) responds: The technique Barry describes applies only to Standard Engine (SE) databases, not to OnLine. Barry is right that such techniques are unsupported, and probably cause Informix to cringe. If you have an older version of Informix SE, the following technique is SLIGHTLY cleaner than using Unix links, though it is assuredly unsupported also: After creating your identical table in the second (third, ...) database, use an SQL update statement to set column systables.dirpath to contain the absolute pathname of the table in the first database. However, do NOT include the .dat or .idx extension in the dirpath field. The .dat and .idx files must be in the same directory, with the same name, up to the extension. I have a couple of these hanging around in one of my databases; for example: "SELECT tabname, dirpath FROM systables" yields (in part): tabname dirpath activity activit235 { these are found using DBPATH } drawings drawing113 files /home/database/vital1.dbs/files__234 { these are absolute } plot_queues /home/database/vital1.dbs/plot_qu219 You must be the database owner or have DBA privilege to do this in version 2.10. I think that sometime in version 4.X Informix took away completely the capability to directly update data dictionary tables such as systables, although the tables are still available for SELECT. This is a shame. Using absolute paths this way was a great way to spread an SE database across multiple devices, in order to reduce head contention and improve performance. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\ ----- End Included Message -----