Re: Can you have the same table in two SE databases?

Jonathan Leffler wrote: > 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. One further potential source of problems. If either database has a transaction log (or if both do), it would be a very bad idea to attempt recovery with a rollforward. The trouble would really occur if databaseA attempted to update a record inserted in databaseB since the backup was made. During recovery of databaseA, the record would not be present, so the rollforward process would immediately claim everything was corrupt. This is an amplification of 'review ... recovery strategies', and applies whether you use symlinks, hard links or CREATE TABLE. It also would be a good idea to ensure that either the databases are both unlogged or both logged. I'm not sure under what circumstances anything would go wrong, but I suspect it could. > I'm not sure how supported it is - but it works because the o/s > mediates the locking. It works during normal operation. ROLLFORWARD DATABASE by definition is not 'normal operation'. Given the gotcha above, I'm fairly sure it is unsupported. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/