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/

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/

Jonathan Leffler wrote:
>
>> 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).

Ahh well. Dare I suggest IDS ?-)