Informix Error -556: Cannot create, drop, or modify an object that is external to current database.

Cause and resolution

Cannot create, drop, or modify an object that is external to current database.

This statement attempts to create, drop, or alter an object in an external database, one other than the current database. You can only read the contents of an external database.

If you make the external database your current database, you can modify the contents. Review all uses of names beginning with <dbname>, which refers to an object in the external database <dbname>. </dbname></dbname>

The local database (the coordinator) can reference the objects on a remote database (a participant), but a remote database cannot reference objects on the local database or on any other database. You can query data from a remote database, but you cannot perform DDL operations on a remote database. and You cannot execute a remote routine which queries data from a remote table.

Example 1: You cannot performs DDL operations in a remote database, whether that database is on the same server or a remote server

The following procedure is created on the server Server_B in database named as 'db':

CREATE PROCEDURE test_qry() create table tab(col1 int); END FUNCTION;

The following statement run on the server Server_A fails because the procedure was created on the server Server_B:

EXECUTE PROCEDURE db@server_B:test_qry();

The following statement run on the server Server_A from in the database 'db1' fails because the procedure was created in the database 'db':

EXECUTE PROCEDURE db:test_qry();

Or

The following SQL statement fails to create a table 'tab1' on remote server Server_B in database 'db1'.

CREATE TABLE db1@Server_B:tab(col1 int);

Example 2 : You can read data from a remote table, whether the table is created across different database in a same server instance or at remote server.

The following function is created in database named 'db1' on 'Server_B' and selects rows from a remote table in the database named 'db':

CREATE FUNCTION test_qry_1() returning integer; RETURN (SELECT iid FROM db:onerow); END FUNCTION;

The following statement run in the database 'db' succeeds: EXECUTE FUNCTION db1:test_qry_1();

or

The following SQL statament successfully run on server 'Server_A' that queries data from a remote table located at 'Server_B' SELECT * from db1@Server_B:tab1;

Example 3 : You cannot query local data using a remote routine.

The following function is created on server Server_B and queries data on the server Server_A:

CREATE FUNCTION test_qry() returning integer; RETURN (SELECT iid FROM db@server_A:onerow); END FUNCTION;

The following statement run on the server Server_A fails, even though it queries data on Server_A, because the function test_qry was created on Server_B:

EXECUTE FUNCTION db@Server_B:test_qry();

DROP TABLE other_db:staging_import;
-- -556: other_db is external to the current database

DATABASE other_db;
DROP TABLE staging_import;