Tailor DB2 Everyplace with custom logic

CREATE TABLE CL.ORD_HDR ( 
   ORDER_NBR INTEGER NOT NULL, 
   CUSTOMER_NBR INTEGER NOT NULL, 
   TAKEN_DATE DATE NOT NULL, 
   PROMISE_DATE DATE, 
   DELIVERY_DATE DATE, 
   SALESPERSON_NBR INTEGER NOT NULL, 
   PRIMARY KEY (ORDER_NBR))

CREATE TABLE CL.NEXT_SEQ (NEXT_SEQ INTEGER)

SELECT -(COUNT(*)+1) FROM ORD_HDR WHERE ORDER_NBR < 0

CREATE TABLE CL.ORD_NBR_HOLD ( 
   CLIENT_ID VARCHAR(56) NOT NULL,
   OLD_NBR INTEGER NOT NULL,
   NEW_NBR INTEGER NOT NULL, 
   PRIMARY KEY(CLIENT_ID,OLD_NBR))

CREATE TABLE CL.ORD_DET ( 
   ORDER_NBR INTEGER NOT NULL, 
   SEQ_NBR SMALLINT NOT NULL, 
   ITEM_NBR CHAR(16) NOT NULL, 
   ITEM_DESC CHAR(40) NOT NULL, 
   ITEM_QTY INTEGER NOT NULL, 
   ITEM_PRICE DECIMAL(10,2) NOT NULL, 
   ITEM_COST DECIMAL(10,2), 
   PRIMARY KEY(ORDER_NBR, SEQ_NBR))

				
package com.jcasey.cl;
import com.ibm.db2ess.cl.*;
import java.sql.*;


/**
 * <pre>
 * Descriptive Name:  Custom Logic during the processing of input synchronization data.
 *
 * Description:
 *    This class is called by Sync Server during the processing of input synchronization 
 *    data for the sample order header table. For inserts coming from the client the 
 *    ORDER_NBR column is reset to the next order number available based upon 
 *    NEXT_ORD_NBR table in the source database (CUSTOM).
 *
 *
 *    @see com.ibm.db2ess.cl.TablePolicy
 * 		@see com.ibm.db2ess.cl.CLException
 */
public class OrdHdrTablePolicySyncIn extends TablePolicy {

  /**
   * This method will be invoked by the SyncServer during the processing of input 
   * synchronization data.
   *
   * @exception com.ibm.db2ess.cl.CLException
   * Custom logic generated or encountered exception.
   */
  public void execute(CLRecord iRecord) throws CLException {

	KCDataLog sdl = KCDataLog.getInstance();
		// Go thru the candidate to look if we need to perform Primary Key mapping
			
		// The primary key column index is 0 in this sample.
		int pkColInd = 0;

		// Get the candidate array and process it
		CLCandidate[] clCandidateArray = iRecord.getCandidates();
		// Scan all the the CLCandidates
		String DSY_user2 = "jcasey";
  		String DSY_pw2   = "songhui";
  		String URL2 = "jdbc:db2:M_CUSTOM";
  		String DSY_user = "jcasey";
  		String DSY_pw   = "songhui";
  		String URL = "jdbc:db2:CUSTOM";
  		String stmtID = "trying to connect to CUSTOM";
        
      	try {
      	   Connection con = DriverManager.getConnection(URL, DSY_user, DSY_pw );
          con.setAutoCommit(false);
          String sqlValue1 = "UPDATE CL.NEXT_SEQ SET NEXT_SEQ = NEXT_SEQ + 1";
          stmtID = "prepare " + sqlValue1;
          PreparedStatement stmt1 = con.prepareStatement(sqlValue1);
          String sqlValue2 = "SELECT NEXT_SEQ FROM CL.NEXT_SEQ";
          stmtID = "prepare " + sqlValue2;
          PreparedStatement stmt2  = con.prepareStatement(sqlValue2); 
          stmtID = "trying to connect to M_CUSTOM";
		Connection con2 = DriverManager.getConnection(URL2, DSY_user2, DSY_pw2 );
		con2.setAutoCommit(false);
		String sqlValue3 = "INSERT INTO CL.ORD_NBR_HOLD VALUES (?,?,?)";
		stmtID = "prepare " + sqlValue3;
		PreparedStatement stmt3  = con2.prepareStatement(sqlValue3);
		String sqlValue4 = "SELECT NEW_NBR FROM CL.ORD_NBR_HOLD 
					WHERE CLIENT_ID = ?" +
		                   " AND OLD_NBR = ?";
		stmtID = "prepare " + sqlValue4;
		PreparedStatement stmt4  = con2.prepareStatement(sqlValue4);
		// Scan all the the CLCandidates
		for (int i=0; i < clCandidateArray.length; i++) {
*	The CLCandidate.getAuthor() method returns a CLCorrespondent object, 
	which has a method getDeviceId(), which in turn returns a String device id.
*/
      		CLCorrespondent myClc = clCandidateArray[i].getAuthor(); 						
		   String deviceId = myClc.getDeviceId();
					
		   if ((clCandidateArray[i].getOp() == CLCandidate.Op.INSERT)
		     && (clCandidateArray[i].fromUser())&&
			   (clCandidateArray[i].getInt(pkColInd) > 0)) {
			int oldINT = clCandidateArray[i].getInt(pkColInd);
			CLCandidate clone = iRecord.createCLCandidate(clCandidateArray[i]);
			// Now we can operate on the clone
			int newINT;
			stmtID = "Setting Parameters for " + sqlValue4;
			stmt4.setString(1,deviceId);
			stmt4.setInt(2,oldINT);
			stmtID = "rs4 " + sqlValue4;
        	ResultSet rs4  = stmt4.executeQuery();
             	stmtID = "rs4.next " + sqlValue4;
             	if (rs4.next()){
             	  stmtID = "rs4.getInt(1) " + sqlValue4;
             	  newINT = rs4.getInt(1);
             	  stmtID = "rs4.close() " + sqlValue4;
             	  rs4.close();
             	}
             	else {
		        stmtID = "update " + sqlValue1;
                stmt1.executeUpdate();
                stmtID = "rs " + sqlValue1;
             	  ResultSet rs  = stmt2.executeQuery();
             	  stmtID = "rs.next " + sqlValue2;
             	  rs.next();
             	  newINT = rs.getInt(1);
             	  stmtID = "rs.close() " + sqlValue2;
             	  rs.close();
             	  stmtID = "con.commit()" ;
             	  con.commit();
             	  // Now record the key substitution
			stmtID = "Setting parms for insert" + sqlValue3;
		 	stmt3.setString(1,deviceId);
			stmt3.setInt(2,oldINT);
			stmt3.setInt(3,newINT);
			stmtID = "executing update " + sqlValue3;
			stmt3.executeUpdate();
			stmtID = "(con2.commit()";
			con2.commit();
             	  }
		  clone.setField(pkColInd, newINT);
		  // Now force the candidate
		  // Sync server is responsible to free the clone when we exit
		  iRecord.force(clone);
		  iRecord.apply();
		  String s = new String( "OrdHdrTablePolicySyncIn for device " + deviceId +
      	"Old value: " + oldINT + " New value: " + newINT);
			sdl.writeOut(s);
		} // end of insert if
		} // end of FOR loop
		stmtID = "close stmts and cons routine";
		stmt1.close();
		stmt2.close();
		stmt3.close();  
		con.close();
		con2.close();
	}
catch (CLCandidateException clCandidateException) {
	sdl.writeOut("!!!CLCandidateException occurs in OrdHdrTablePolicySyncIn!!!");
	}
catch (SQLException e) {
	sdl.writeOut("OrdHdrTablePolicySyncIn Unsuccessful when " + stmtID);
	  sdl.writeOut(e.getMessage());
     	  throw new CLException("OrdHdrTablePolicySyncIn had bad sql return code",e);
	}

	}
}

				
package com.jcasey.cl;
import com.ibm.db2ess.cl.*;
import java.sql.*;


/**
 * <pre>
 * Descriptive Name:  Custom Logic during the processing of input synchronization data.
 *
 * Description:
 *  This class is called by Sync Server during the processing of input synchronization data
 *  for the sample order header table. For inserts coming from the client the ORDER_NBR 
 *  column is reset to the next order number available based upon NEXT_ORD_NBR table in the
 *  source database (CUSTOM).
 *
 *
 *    @see com.ibm.db2ess.cl.TablePolicy
 * 		@see com.ibm.db2ess.cl.CLException
 */
public class OrdDetTablePolicySyncIn extends TablePolicy {

  /**
   * This method will be invoked by the SyncServer during the processing of input 
   *  synchronization data.
   *
   * @exception com.ibm.db2ess.cl.CLException
   * Custom logic generated or encountered exception.
   */
  public void execute(CLRecord iRecord) throws CLException {

   KCDataLog sdl = KCDataLog.getInstance();

        // Go thru the candidate to look if we need to perform Primary Key mapping
	 // The primary key column index is 0 in this sample.
	 int pkColInd = 0;
	 // Get the candidate array and process it
	 CLCandidate[] clCandidateArray = iRecord.getCandidates();
	 // Scan all the the CLCandidates
	 String DSY_user2 = "jcasey";
	 String DSY_pw2   = "songhui";
	 String URL2 = "jdbc:db2:M_CUSTOM";
	 String stmtID = "trying to connect to CUSTOM";
       
   try {
	Connection con = DriverManager.getConnection(URL2, DSY_user2, DSY_pw2 );
			
	String sqlValue = "SELECT NEW_NBR FROM CL.ORD_NBR_HOLD WHERE CLIENT_ID = ?" +
	                   " AND OLD_NBR = ?";
	stmtID = "prepare " + sqlValue;
	PreparedStatement stmt  = con.prepareStatement(sqlValue);
	// Scan all the the CLCandidates
	for (int i=0; i < clCandidateArray.length; i++) {
/*		The CLCandidate.getAuthor() method returns a CLCorrespondent object, 
		which has a method getDeviceId(), which in turn returns a String device id.
*/
      	CLCorrespondent myClc = clCandidateArray[i].getAuthor(); 						
		String deviceId = myClc.getDeviceId();
		if ((clCandidateArray[i].getOp() == CLCandidate.Op.INSERT)
			&& (clCandidateArray[i].fromUser())&&
		   (clCandidateArray[i].getInt(pkColInd) > 0)) {
			int oldINT = clCandidateArray[i].getInt(pkColInd);   				
			CLCandidate clone = iRecord.createCLCandidate(clCandidateArray[i]);
			// Now we can operate on the clone
			stmtID = "Setting Parameters for " + sqlValue;
			stmt.setString(1,deviceId);
			stmt.setInt(2,oldINT);
			stmtID = "rs " + sqlValue;
      		ResultSet rs  = stmt.executeQuery();
      		stmtID = "rs.next " + sqlValue;
      		rs.next();
      		stmtID = "rs.getInt(1) " + sqlValue;
      		int newINT = rs.getInt(1);
      		stmtID = "rs.close() " + sqlValue;
      		rs.close();
		  clone.setField(pkColInd, newINT);
		  // Now force the candidate
		  // Sync server is responsible to free the clone when we exit
		  iRecord.force(clone);
		   iRecord.apply();
		  String s = new String( "OrdDetTablePolicySyncIn for device " + deviceId +
		  " Old value: " + oldINT + " New value: " + newINT);
		  sdl.writeOut(s);
		} // end of insert if
		} // end of FOR loop
	stmtID = "close stmts and cons routine";
		stmt.close();
		con.close();
	}
	catch (CLCandidateException clCandidateException) {
         sdl.writeOut("!!!CLCandidateException occurs in OrdDetTablePolicySyncIn!!!");
	  }
	catch (SQLException e) {
	  sdl.writeOut("OrdDetTablePolicySyncIn Unsuccessful when " + stmtID);
     	  sdl.writeOut(e.getMessage());
     	  throw new CLException("OrdDetTablePolicySyncIn had bad sql return code",e);
	  }
}
}

				
package com.jcasey.cl;
import com.ibm.db2ess.cl.*;
import java.sql.*;

/**
 * <pre>
 * Descriptive Name:  Custom Logic after completion of handling input synchronization data.
 *
 * Description:
 *    This class is called by Sync Server after completion of handling input 
 *    synchronization data for the sample order header table and 
 *    cleans up the CL.ORD_NBR_HOLD table in the M_CUSTOM database.
 *
 *
 *    @see com.ibm.db2ess.cl.CLCandidate
 *    @see com.ibm.db2ess.cl.CLException
 */
public class OrdDetAfterTablePolicySyncIn extends AfterTablePolicy {

  /**
   * This method will be invoked by the SyncServer after completion of handling 
   *  input synchronization data.
   *
   * @exception com.ibm.db2ess.cl.CLException
   * Custom logic generated or encountered exception.
   */
public void execute(CLCorrespondent[] iCorrespondents,CLResult iCLResult) 
  throws CLException {

  	KCDataLog sdl = KCDataLog.getInstance();
  	String DSY_user = "jcasey";
  	String DSY_pw   = "songhui";     									
  	String URL = "jdbc:db2:M_CUSTOM";
  	String stmtID = "Connection " + URL;
	try {  		
      		Connection con = DriverManager.getConnection(URL, DSY_user, DSY_pw );
     		 for (int i=0; i < iCorrespondents.length; i++) {
      		String deviceId = iCorrespondents[i].getDeviceId();	
      		String sqlValue  = "DELETE FROM CL.ORD_NBR_HOLD WHERE CLIENT_ID = '" +
                       			  	 deviceId + "'" ;
      		stmtID = "Create statement " + sqlValue;
     		Statement stmt = con.createStatement();
     		stmtID = "Execute update " + sqlValue;
     		int deleteCount = stmt.executeUpdate(sqlValue);
     		if (deleteCount > 0){
			sdl.writeOut( "OrdDetAfterTableSyncInPolicy deleted " +
			deleteCount + " rows for device ID = " + deviceId);
			}
		}
	}

  catch (SQLException e) {
	sdl.writeOut("OrdDetAfterTablePolicySyncIn Unsuccessful when " + stmtID);
  sdl.writeOut(e.getMessage());
   throw new CLException("OrdDetAfterTablePolicySyncIn had bad sql return code",e);
        }

	}
}

CREATE TABLE CL.CONTEST (
   C1 INTEGER NOT NULL, D1 CHAR(30), 
   TS_OF_UPDATE TIMESTAMP NOT NULL,
   PRIMARY KEY (C1))

				\
package com.jcasey.cl;
import com.ibm.db2ess.cl.*;
import java.sql.*;

/**
 * Descriptive Name:  Custom Logic during the replication of one specific table.
 *
 * Description:
 *  This class is called by Sync Server during the replication of one specific table.
 *  For demonstration purpose, this Custom Logic sample demonstrate resolving conflicts
 *  based upon timestamp of the update. This method requires that clocks on all 
 *  participating machines be closely in synch. Custom Logic to resolve conflicts
 *  can only be used for JDBC subscriptions. Custom Logic to resolve conflict CAN NOT 
 *  be used for DPROPR subscriptions.
 *
 */
public class ContestTableReplicatePolicy extends TablePolicy 
	{

  /**
   * This method will be invoked by the SyncServer during the replication of 
   * one specific table.
   *
   * @exception com.ibm.db2ess.cl.CLException
   * Custom logic generated or encountered exception.
   */
public void execute(CLRecord iRecord) throws CLException 
  {

  KCDataLog sdl = KCDataLog.getInstance();
  int tsColInd = 2;
  // The primary key column index is 0 in this example.
  		int pkColInd = 0;
				
  try 
   {
	// Detect the conflict and manage it
	if (iRecord.conflictExists()) 
 	{
	// Go thru the candidate to select the winner
	// Get the candidate array and process it
	CLCandidate[] clCandidateArray = iRecord.getCandidates();
	// select winner logic - begin
	int winner = 0;
	// Scan all the the CLCandidates
	for (int i=0; i+1 < clCandidateArray.length; i++) {
	// Change the default conflict resolution rule by selecting the winner 
	//based upon timestamp
	// of update instead of the source always winning
	// When there are identical timestamps, just pick one
	if (((clCandidateArray[i+1].getDate(tsColInd)).compareTo
		(clCandidateArray[i].getDate(tsColInd)))== 1)
			{
			winner = i + 1;
			}		
	}	
	// select winner logic - end	
	iRecord.force(clCandidateArray[winner]);
	iRecord.apply();
	CLCorrespondent myClc = clCandidateArray[winner].getAuthor(); 						
	String deviceId = myClc.getDeviceId();
	if (deviceId == "null") deviceId = "Source server";
	else deviceId = "Device " + deviceId;
	String s = new String( "ContestTableReplicatePolicy - " + deviceId +
	" was the winner for key value " + clCandidateArray[winner].getInt(pkColInd));
	sdl.writeOut(s);
	}
  }
  catch (CLCandidateException clCandidateException) {
    sdl.writeOut("!!!CLCandidateException occurs in ContestTableReplicatePolicy!!!");
	}
 }				
}

				
package com.jcasey.cl;
import com.ibm.db2ess.cl.*;
import java.util.*;
import java.io.*;			// To get the File IO class
import java.text.*;

/**
 * Descriptive Name:  Log messages on screen and file
 *
 * Description:
 *    This class is called by Custom Logic to monitor synchronization/replication 
 *    events
 *
 */
public class KCDataLog {
 private static final String logDataFile =
  "C:\\DB2Everyplace\\server\\logs\\KCDataLog.txt";  
    // For Windows platform

  /**
   * This instantiates the singleton KCDataLog object.
   */
	private static final KCDataLog INSTANCE= new KCDataLog(); 
	// Create the singleton
 /**
  * Private Default Constructor
  */
  private KCDataLog(){}

  /**
     * This method returns the unique instance of the KCDataLog.
   *
   */
  public static KCDataLog getInstance() {
  	return INSTANCE;
  }

	public void writeOut(String logRecord) {
		DateFormat longTimestamp = 
     	DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT);
     	String logRecordX = longTimestamp.format(new Date()) + " " + logRecord;
		System.out.println(logRecordX);	// Also print on screen

		// Open the data log file, write to it and close the file
		FileWriter toFile = null;
		try {
			toFile = new FileWriter(logDataFile, true);
			toFile.write(logRecordX + "\n");
		}
		catch (IOException e) {System.out.println("ERROR in KCDataLog");}
		finally {
		  if (toFile != null) try { toFile.close(); 
		  }
		  catch (IOException e) {System.out.println("ERROR2 in KCDataLog");}
		}
	}
}

				
Dec 7, 2004 9:31:24 AM OrdHdrTablePolicySyncIn for device GmA@rVzhgYN1LoyMYbQx 
  Old value: -1 New value: 6
Dec 7, 2004 9:31:25 AM OrdHdrTablePolicySyncIn for device GmA@rVzhgYN1LoyMYbQx 
  Old value: -2 New value: 7
Dec 7, 2004 9:31:25 AM OrdDetTablePolicySyncIn for device GmA@rVzhgYN1LoyMYbQx 
  Old value: -1 New value: 6
Dec 7, 2004 9:31:25 AM OrdDetTablePolicySyncIn for device GmA@rVzhgYN1LoyMYbQx 
  Old value: -1 New value: 6
Dec 7, 2004 9:31:25 AM OrdDetTablePolicySyncIn for device GmA@rVzhgYN1LoyMYbQx 
  Old value: -2 New value: 7
Dec 7, 2004 9:31:26 AM OrdDetTablePolicySyncIn for device GmA@rVzhgYN1LoyMYbQx 
  Old value: -2 New value: 7
Dec 7, 2004 9:31:26 AM OrdDetAfterTableSyncInPolicy deleted 2 rows for 
  device ID = GmA@rVzhgYN1LoyMYbQx
Dec 7, 2004 9:32:31 AM ContestTableReplicatePolicy - Device GmA@rVzhgYN1LoyMYbQx 
  was the winner for key value 1