ifx_grid_connect() procedure

The ifx_grid_connect() procedure opens a connection to the grid. Through an ifx_grid_connect() procedure, you can run routines and data definition language (DDL) commands on a source server, and then propagate the routines or commands to the other grid servers.

Syntax

Element	Purpose	Restrictions
`grid_name`	Name of the grid.	Must be the name of an existing grid.
`ER_enable`	Enable or disable the creation of a replicate and replicate set and starting replication for any tables that are created while the connection to the grid is open. Optionally suppress any errors that might be raised when the procedure is run.	Valid values are: 0 = Default. Enterprise Replication is disabled. 1 = Enterprise Replication is enabled. 2 = Enterprise Replication is disabled and errors are suppressed. 3 = Enterprise Replication is enabled and errors are suppressed.
`defer`	Run DDL statements on the local server but delay the propagation of the statements to other servers in the grid. Optionally enable the creation of a replicate and replicate set and starting replication.	Valid values are: 4 = Defer the propagation of DDL statements. 5 = Defer the propagation of DDL statements and enable Enterprise Replication. Use this value when you run DDL statements on existing replicated tables.
`tag`	A character string to identify grid operations.	Must be unique among grid sessions with deferred DDL statements that are outstanding.

Usage

Use the ifx_grid_connect() procedure start a grid connection. All DDL SQL statements and routines that you run in the grid connect are propagated to all the servers in the grid. Use the ifx_grid_disconnect() procedure to close the grid connect and disable grid propagation. If the databases on your replication servers have different schemas or data, a DDL statement that is run through a grid might have different results on each server. In a replication system, when you run a statement locally, the results are replicated to the other replication servers. When you run a statement through a grid, that statement is simultaneously run on each server.

You must run this routine as an authorized user on an authorized server, as specified by the cdr enable grid command.

You must connect to a database before you run the ifx_grid_connect() procedure. If you are planning to create a database, you can connect to the sysmaster database.

If you enable Enterprise Replication, when you create a table through the grid, a replicate is created that contains the newly created table with all the servers in the grid as participants. The replicate belongs to a replicate set that has the same name as the grid. When you create a replicated table through the grid, the ERKEY shadow columns are added automatically.

If you run the ifx_grid_connect() procedure automatically as part of the sysdbopen() procedure, set the ER_enable argument to 2 or 3 to suppresses errors that might prevent the session from accessing the database.

You can defer the propagation of DDL statements to other servers in the grid by setting the defer argument. The DDL statements are queued for propagation but not sent to other grid servers until you run the ifx_grid_release() function.

You cannot perform the following actions in the context a grid connection:

Propagate data manipulation language statements through a grid.
Replicate a database object that exists on a server in the grid.
Use the @servername syntax while connected to the grid.
Drop a replicated column through a grid. To drop a replicated column, you must manually remaster the replicate and then drop the column.
Renaming a replicated database. You must manually rename the database on each participant server.

Example 1: Create a table

In the following example, a grid connection is opened that enables the propagation of only DDL statements, a table is created on all servers in the grid, and then the grid connection is closed:

database sales;

EXECUTE PROCEDURE ifx_grid_connect('grid1');

CREATE TABLE special_offers(	  
    offer_description	varchar(255),
    offer_startdate	date,
    offer_enddate	date,
    offer_rules		lvarchar);

EXECUTE PROCEDURE ifx_grid_disconnect();

In this example, the data in the special_offers table is not replicated.

Example 2: Create a replicated table

In the following example, a grid connection is opened that enables the propagation of DDL statements and the replication of data, a table is created on all servers in the grid, and then the grid connection is closed:

database sales;

EXECUTE PROCEDURE ifx_grid_connect('grid1', 1);

CREATE TABLE special_offers(
	  offer_description	varchar(255),
    offer_startdate	date,
    offer_enddate	date,
    offer_rules		lvarchar)
    WITH CRCOLS;

EXECUTE PROCEDURE ifx_grid_disconnect();

A replicate for the special_offers table is created with timestamp conflict resolution and replication of the data in the table is started.

Example 3: Alter a replicated table to add a column

The following example alters the special_offers table to add a column and remasters the replicate on all participants that are members of the grid:

database sales;

EXECUTE PROCEDURE ifx_grid_connect('grid1', 1);

ALTER TABLE special_offers ADD (
	  offer_exceptions 	varchar(255));

EXECUTE PROCEDURE ifx_grid_disconnect();

Example 4: Alter a replicated table to add a column that is not replicated

The following example alters the special_offers table to add a column whose data is not replicated:

database sales;

EXECUTE PROCEDURE ifx_grid_connect('grid1', 0);

ALTER TABLE special_offers ADD (
	  local_promotions 	varchar(255));

EXECUTE PROCEDURE ifx_grid_disconnect();

The column local_promotions is added to the special_offers table on all grid servers, but the data in the local_promotions column is not replicated.

Example 5: Defer propagation of a DDL statement

The following example defers propagation of the ALTER operation across grid1:

database sales;

EXECUTE PROCEDURE ifx_grid_connect('grid1','tag1',4);

ALTER TABLE special_offers ADD (
	  local_restrictions	 varchar(255));

The column local_restrictions is added to the special_offers table on the local server only. The ALTER operation is queued for propagation to the other grid servers.