The SQL/Data Synchronization Facility (SQL/DSF) assists DB2/VM database administrators in managing distributed database environments. In such environments, it must be ensured that the distributed tables remain in a consistent state. The following SQL/DSF facilities help database administrators in achieving this goal:
 

Synchronization	Propagates changes performed on DB2/VM tables to other databases.
Transfer	Transfers tables between different databases.
Compare	Compares tables in different databases.
Conditional transfer	Transfers a table to the target database, when a table compare results in mismatch.

All SQL/DSF functions are performed from the VM/ESA DB2/VM environment. They operate:

• on DB2/VM databases
• on database platforms that can connect to DB2/VM using DRDA

• From the CMS prompt or a CMS EXEC.
• Using the RULES of the SQL/DSF Synchronization Scheduler.
• By submitting a command file to the Synchronization Scheduler.

Synchronization progagates table changes from a DB2/VM source database to the target database, which can be another DB2/VM or any other database platform that can connect to DB2/VM. Synchronization is a cost-effective alternative for a complete table copy, as it applies only the INSERT, DELETE and UPDATE statements executed in the source database.

Data Capturing

Synchronization implies that the source table changes have been captured in the source database. The capturing function is performed by the SQL/Auditing Facility, a program product available from Software Product Research. All SQL statements, both dynamic and compiled, are captured, regardless of their origin.

Synchronization Method

The log of the SQL/Auditing Facility contains the full text of all SQL statements captured in the DB2/VM server. Synchronization consists in executing these statements against the target database. SQL/DSF may be requested to synchronize static mode SQL statements to the target table in the same static execution mode. To achieve this, SQL/DSF automatically creates and maintains "shadow" packages in the target database.

Synchronizing to a CMS file

If the target database cannot connect to DB2/VM using DRDA, SQL/DSF can be requested to create a CMS file with all SQL statements needed to synchronize the target table. This file may then be submitted to the command processor of the target database.

Synchronization Performance

Since SQL/DSF uses the captured statement for synchronization and not the DB2/VM log, an SQL statement that alters multiple rows will be synchronized in a single transaction. The static synchronization method of SQL/DSF is considerably faster than dynamic synchronization, commonly used by synchronization software. As a result, throughput of synchronization will be comparable to that of the SQL statements in the originating applications.

Synchronization User Exit

If requested, SQL/DSF will invoke a user exit before applying the source statements to the target database. The exit can modify the statement text or take any other appropriate action.

The transfer function copies all rows of a designated table to the target table in another database. At the user's choice, transfer will replace or append to the target. The transfer is performed using CMS workunits: no disk or tape storage is required during the operation.

TABLE COMPARE

The function compares all rows of a named table with the table in the designated target database and prints the contents of the mismatching rows.

CONDITIONAL TABLE TRANSFER

The conditional transfer function is a combination of the compare and the transfer functions. A conditional transfer compares both tables and initiates a transfer when the compare results in a mismatch.

THE SQL/DSF SCHEDULER

SQL/DSF provides the SQLDSFS Scheduler program to assist an installation in setting up a DataSync Server environment. Using the Scheduler, most synchronization tasks can be automated. The Scheduler processes:

• table synchronizations and transfers automatically and chronologically, as requested in its RULES file
• SQL/DSF command files forwarded by means of the SQLDSF SUBMIT command