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?

Answer 1

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.