Database alias in Informix

Question

In Informix I can run SQL statement that use other database:

INSERT INTO other_db:mytable ...

I would like "unite" both databases, but I would like do it "step by step".

At first I want to move all data from other_db to main database and make alias that other_db = main_database. This way I will have time to remove "other_db:" from all statements.

How can I set database alias?

Don't forget to select the answer - that's the big tick (check) mark beside the answer when you look at it. Click on it, and it goes green. (And it gets you 2 points, and it gives RET points too). — Jonathan Leffler
– Jonathan Leffler, Commented Dec 24, 2008 at 4:51
OK. Marked as accepted. I had some troubles noticing "V" sign :) . Now it is green) :). Regards, — Michał Niklas
– Michał Niklas, Commented Dec 31, 2008 at 8:29

RET · Accepted Answer · 2008-12-23 01:05:58Z

4

I'm not aware of any method for creating an alias for the whole database.

However, you can create synonyms across databases, in the form:

DATABASE old_db;
CREATE SYNONYM table_name FOR new_db:table_name;

If you create such an alias for each table as it's moved, you should be able to get the same effect. Once all tables have been relocated, you can remove all references to old_db.

You can query systables to identify real tables in old_db, ie:

DATABASE old_db;
SELECT tabname, nrows
  FROM systables
  WHERE tabtype = "T"
    AND tabid > 99 -- exclude internal tables

The row-count will of course depend on reasonably current UPDATE STATISTICS.

Hope that helps.

answered Dec 23, 2008 at 1:05

RET

9,1881 gold badge32 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Database alias in Informix

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related