Can you have the same table in two SE databases?
A client of mine wants to use the same table in two different databases in two different databases. Could they do so by pointing dirpath for the table in each database to the same c-isam file? And what are the predicted problems with doing so? I suspect we will have to wait for Jonathan to answer this one. Malcolm sending to informix-list
malcolm.iiug wrote: >A client of mine wants to use the same table in two different databases in >two different databases. Could they do so by pointing dirpath for the table >in each database to the same c-isam file? And what are the predicted >problems with doing so? > >I suspect we will have to wait for Jonathan to answer this one. > >Malcolm > >sending to informix-list > > Ages ago I used this with Microfocus Cobol (C-ISAM based) and pre-SE Informix database. Locking was handled by the OS and it worked fine. You will have to create a table in the second db, then remove it (and index file too) and create link to the first db. Should work. HTH Michael
malcolm.iiug wrote: > A client of mine wants to use the same table in two different > databases in two different databases. Could they do so by pointing > dirpath for the table in each database to the same c-isam file? And > what are the predicted problems with doing so? > > I suspect we will have to wait for Jonathan to answer this one. Doesn't SE support remote synonyms? I'm SURE it does..... It supports remote database access in the form database:table doesn't it, as long as the database can be found in the DBPATH?
Andrew Hamm wrote: > malcolm.iiug wrote: >>A client of mine wants to use the same table in two different >>databases in two different databases. Could they do so by pointing >>dirpath for the table in each database to the same c-isam file? And >>what are the predicted problems with doing so? >> >>I suspect we will have to wait for Jonathan to answer this one. Messrs Sturkenboom and Krzepkowski have given you a lot of the information you need. You can do it in a number of different ways. Symbolic links, hard links, and CREATE TABLE ... IN "/path/name" all work - more or less. Beware of clobbering your data with the explicit create table option. First you have to create the table in each database - with all indexes in place - and only then go combining the two tables. Make sure you are very confident about how you set it up. If you're not sure, don't do it; it is very easy to mess things up. Review your backup and recovery strategies very carefully. Note that you may need different strategies for each database if you use symbolic links in one of them. I'm assuming your SE is recent enough not to use lock files - if it does, you have to create lock file and link that too (otherwise all hell breaks loose because the locking is not coordinated - a sure fire source of corruption!) Don't go altering the table, either. I'm not sure how supported it is - but it works because the o/s mediates the locking. Incidentally, if you need the index file and data file on different file systems, the only way to achieve that is with a symbolic link. > Doesn't SE support remote synonyms? I'm SURE it does..... No. > It supports remote database access in the form database:table doesn't it, > as long as the database can be found in the DBPATH? No - you can access remote databases, but you cannot use that notation in a SQL statement other than a DATABASE statement or a CONNECT statement. (OK; or ROLLFORWARD or START or CREATE DATABASE). -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/