Advantage Developer Zone

 
 
 

Advantage Knowledgebase Item




Ref No:
080912-2077
Last Modified:
Sunday, April 26, 2009
Product:
Advantage Clients ( General )
Category:
Programming
Title:
How To Begin Replication to a Blank Database
Problem Description:
You may want to begin replicating by simply deploying a data dictionary with Auto Create enabled on your tables. There is no way to do an initial replication to get all existing data into the destination database.

Could a UDF (user defined function) be created to do an initial replication to the destination database?
Solution:
Using Replication in 9.0 and newer you can set the publication to use the new MERGE statements, then use a script that touches each record on the source but doesn't change it. For instance UPDATE table SET id=id;

Warning: This will create an SQL statement for each record in every table of the database. Test and confirm that replication is working with single updates before running this function, otherwise if replication has an issue the replication queue table will grow in size to include all of the SQL statements created.

1.) Enable MERGE statements on your publication. This needs to be done for each table.
See this knowledge base item, "How to Enable MERGE for All Replication Publication Articles," to create a Stored Procedure that will do this for you.

2.) Create touch UDF using the script below and run it (SELECT touch() FROM system.iota;). It will update one column for all records in each table.

CREATE FUNCTION touch
(
)
RETURNS LOGICAL
BEGIN
DECLARE allColumns CURSOR;
DECLARE strSQL CHAR(250);
DECLARE tableName CHAR(25);
DECLARE fieldName CHAR(25);
DECLARE newTableName CHAR(25);

OPEN allColumns AS SELECT * FROM system.columns; //Open cursor with all columns.

WHILE FETCH allColumns DO //Go through columns.
TRY
newTableName = allColumns.Parent;
IF (newTableName = tableName)THEN //Checks to see if an update has already been done on one of the columns.
CONTINUE;
ELSE
ENDIF;

tableName = allColumns.Parent;
fieldName = allColumns.Name;

strSQL = 'UPDATE [' + trim(tableName) + '] SET [' + trim(fieldName) + '] = [' + trim(fieldName) +']'; //Build SQL statement.
EXECUTE IMMEDIATE strSQL;
CATCH ALL
tableName='retry'; //If the last update didn't work (possibly the column was autoinc) then try the next column.
FINALLY
END TRY;
END WHILE;
CLOSE allColumns;
RETURN TRUE;
END;
Was This Item Helpful?