Blocking Replication

You might need to put data into a database that you do not want replicated, perhaps for a new server or because you had to drop and re-create a table.

To block replication while you prepare a table, use the BEGIN WORK WITHOUT REPLICATION statement. This starts a transaction that does not replicate to other database servers.

The following code fragment shows how you might use this statement:

BEGIN WORK WITHOUT REPLICATION
LOCK TABLE office
DELETE FROM office WHERE description = 'portlandR_D'
COMMIT WORK 

The following list indicates actions that occur when a transaction starts with BEGIN WORK WITHOUT REPLICATION:

• SQL does not generate any values for the shadow columns (cdrserver and cdrtime) for the rows that are inserted or updated within the transaction. You must supply values for these columns with the explicit column list. You must supply these values even if you want the column values to be NULL.
• To modify a table with shadow columns that is already defined in Enterprise Replication, you must explicitly list the columns to be modified. The following two examples show an SQL statement and the correct changes to the statement to modify columns:
  • If table_name1 is a table defined for replication, you must change the following statement:

  LOAD FROM filename INSERT INTO table_name1; 

  to:

  LOAD FROM filename INSERT INTO table_name1 \
     (list of columns);

The list of columns must match the order and the number of fields in the load file.

• If table_name3 and table_name4 are tables defined for replication with the same schema, you must change the following statement:

INSERT INTO table_name3 SELECT * FROM table_name4; 

to an explicit statement, where col1, col2,..., colN are the columns of the table:

INSERT INTO table_name3 VALUES
      (cdrserver, cdrtime, col1, ..., colN)
   cdrserver, cdrtime *
   FROM table_name4;

The shadow columns (cdrserver and cdrtime) are not included in an * list.

For more information about these statements, refer to the IBM Informix Guide to SQL: Syntax.

Using DB-Access to Begin Work Without Replication

The following example shows how to use DB–Access to begin work without replication as well as update the Enterprise Replication shadow columns cdrserver and cdrtime:

DATABASE adatabase;
BEGIN WORK WITHOUT REPLICATION
INSERT into mytable (cdrserver, cdrtime, col1, col2, ....) 
   VALUES (10, 845484154, value1, value2, ....);
UPDATE mytable
   SET cdrserver = 10, cdrtime = 945484154 
   WHERE col1 > col2;
COMMIT WORK

Using ESQL/C to Begin Work Without Replication

The following example shows how to use ESQL/C to begin work without replication as well as update the Enterprise Replication shadow columns cdrserver and cdrtime:

MAIN (argc, argv)
   INT      argc;
   CHAR      *argv[];
{
   EXEC SQL CHAR      stmt[256];
   EXEC SQL database mydatabase;
   
   sprintf(stmt, "BEGIN WORK WITHOUT REPLICATION");
   EXEC SQL execute immediate :stmt;
   
   EXEC SQL insert into mytable (cdrserver, cdrtime,
   col1, col2, ...)
   values (10, 845494154, value1, value2, ...);

   EXEC SQL update mytable
   set cdrserver = 10, cdrtime = 845494154
   where col1 > col2;
   EXEC SQL commit work;
}

sprintf(stmt, "BEGIN WORK WITHOUT REPLICATION");
EXEC SQL execute immediate :stmt;