Pivotal Knowledge Base

Follow

Setting up SQLFire/Gemfire XD DBSynchronizer

Applies To

SQLFire

Purpose

This article discusses DBSynchronizer and its setup.

Description

What is DBSynchronizer?

DBSynchronizer is essentially an AsyncEventListener implementation that can be used to populate the data from SQLFire to any thir-party, JDBC 4.0-compliant RDBMS (such as Oracle, mysql, or postgres).

DBSynchronizer is an open source implementation, so customers are free to modify the code as per their requirements.

Basic Setup steps

  1. You'll need to create an AsyncEventListener using sqlf or any sqlclient (like squirrel). The following example illustrates using the default DBSynchronizer.

    sqlf>create asynceventlistener dblistener
    (
    listenerclass 'com.vmware.SQLFire.callbacks.DBSynchronizer'
    initparams 'org.postgresql.Driver,jdbc:postgresql://localhost:5432/ADMIN,skipIdentityColumns=false,username,password'
    )
    server groups (group1)
    ;
    

    Here the init params are passed as inline parameters. In the case of inline parameters, sequence needs to be maintained. Note that username and password are provide without the trailing equals (i.e. username= and password=). Optionally, you may pass them in properties file like.

    create asynceventlistener dblistener
    (
    listenerclass 'com.vmware.SQLFire.callbacks.DBSynchronizer'
    initparams 'file=/usr/local/dbsynch-params.props'
    )
    server groups (group1)
    ;
    

    add the properties in dbsynch-params.props

    Driver=org.postgresql.Driver
    URL=jdbc:postgresql://localhost:5432/ADMIN
    User=username
    Secret=<encrypted password>
    SkipIdentityColumns=false
    
  2. If you are using a custom DBSynchronizer, you need to create a jar for the custom code and use the sqlf install-jar command to load the custom Dbsynchronizer. Then you can execute the create asynceventlistener command.

  3. Create tables in SQLFire.

    CREATE TABLE CDP_AUDIT_TRAIL (
    AUD_TRAIL_ID BIGINT generated always as identity,
    ACTION_PERFORMED varchar(50),
    ACTION_PERFORMED_TIME timestamp,
    CDPAccountCDP_ACCT_NO varchar(20),
    Action_Performed_By varchar(70),
    USER_ProfileLOGIN_ID varchar(20),
    Staff_ProfileLoginId varchar(20),
    CONSTRAINT PK_CDP_AUDIT_TRAIL PRIMARY KEY (AUD_TRAIL_ID)
    )
    PARTITION BY COLUMN (AUD_TRAIL_ID)
    REDUNDANCY 1
    EVICTION BY LRUMEMSIZE 105378675 EVICTACTION OVERFLOW PERSISTENT
    ASYNCHRONOUS
    asynceventlistener(dblistener) 
    ;
    
  4. Create table in backend database:

    CREATE TABLE CDP_AUDIT_TRAIL
    (
    AUD_TRAIL_ID BIGINT NOT NULL,
    ACTION_PERFORMED varchar(50),
    ACTION_PERFORMED_TIME timestamp,
    CDPAccountCDP_ACCT_NO varchar(20) NOT NULL,
    Action_Performed_By varchar(70),
    USER_ProfileLOGIN_ID varchar(20),
    Staff_ProfileLoginId varchar(20),
    CONSTRAINT PK_CDP_AUDIT_TRAIL PRIMARY KEY (AUD_TRAIL_ID)
    );
    
  5. Start the DBSynchronizer:

    sqlf>call SYS.START_ASYNC_EVENT_LISTENER ('dblistener');
  6. To debug and enable logs for DBSynchronizer execute

    sqlf>call sys.set_trace_flag('TraceDBSynchronizer', 'true');

    and do an insert into the SQLFire table. It should be passed to the backend database.

How To Debug DBSynchronizer issues

  1. Enable the trace level logging.

    sqlf>call sys.set_trace_flag('TraceDBSynchronizer', 'true');
  2. Ensure the parameters passed in the external database URL are correct.

  3. See if the DBSynchronizer is properly registered. There should be no exception

  4. See if any errors occurred while performing the SQL operation. You will see an exception if something fails

    Something like:

    [warning 2014/09/12 11:39:02.906 JST <Event Processor for GatewaySender_AsyncEventQueue_GSLISTENER> tid=0x46] (tid=11 msgId=4) DBSynchronizer::processEvents: Exception while executing statement=org.postgresql.jdbc4.Jdbc4Statement@e9e963a for event=SqlfCBArgForSynchPrms:- dml string = INSERT INTO SGXCDP.CDP_AUDIT_TRAIL (ACTION_PERFORMED,ACTION_PERFORMED_TIME,CDPACCOUNTCDP_ACCT_NO,ACTION_PERFORMED_BY,USER_PROFILELOGIN_ID,STAFF_PROFILELOGINID)
     VALUES ('x',{ts '2014-09-10 10:44:01.547'},'x','x','x','x'); Schema = SGXCDP; Originator = 1; Recipients = [-1]; Num Parameters = 0; Approx Size = 482: org.postgresql.util.PSQLException: ERROR: null value in column "aud_trail_id" violates not-null constraint
     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2120)
     at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1853)
     at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:260)
     at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:513)
     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:375)
     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:303)
     at com.vmware.SQLFire.callbacks.DBSynchronizer.processEvents(DBSynchronizer.java:963)
     at com.vmware.SQLFire.internal.engine.ddl.wan.SqlfGatewayEventListener.processEvents(SqlfGatewayEventListener.java:51)
     at com.gemstone.gemfire.internal.cache.wan.GatewaySenderEventCallbackDispatcher.dispatchBatch(GatewaySenderEventCallbackDispatcher.java:167)
     at com.gemstone.gemfire.internal.cache.wan.GatewaySenderEventCallbackDispatcher.dispatchBatch(GatewaySenderEventCallbackDispatcher.java:85)
     at com.gemstone.gemfire.internal.cache.wan.AbstractGatewaySenderEventProcessor.processQueue(AbstractGatewaySenderEventProcessor.java:390)
     at com.gemstone.gemfire.internal.cache.wan.serial.SerialGatewaySenderEventProcessor.run(SerialGatewaySenderEventProcessor.java:221)
    
  5. You should also check the third-party database's logs as they may provide a more detailed error message.

  6. DBSychronizer continues retrying in the case of any failure. This may lead to problems with disk space consumption due to error logs growth, either on the SQLFire side or that of the external database.

Some Known cases:

  1. By Default, the DBSynchronizer keeps on retrying forever in case of failures. This might appear as if the dbsynch is not working or is hung, so you should check the logs if the dbsych keeps on failing and address any indicated problems to resolve this. You can modify the dbsych code to change this behavior, or there is a custom dbsynch available from engineering team that tries only three times in case of failure.

  2. Identity or autogenerating column values going to the database from SQLFire as nulls.

    Solution:

    Add the following flag in the initparams

    skipIdentityColumns=false
    
    create asynceventlistener dbsynchlistener
    (
    listenerclass 'com.vmware.SQLFire.callbacks.DBSynchronizer'
    initparams 'org.postgresql.Driver,jdbc:postgresql://localhost:5432/ADMIN,
      skipIdentityColumns=false,postgres,postgres'
    )
    server groups (group1)
    ;
    

    Essentially, you need to add the skipIdentityColumns=false flag.

    If you are passing command line arguments, be careful to maintain the sequence, i.e:

    initparams 'org.postgresql.Driver,jdbc:postgresql://localhost:5432/ADMIN,
      skipIdentityColumns=false,postgres,postgres'
    1
    2 <user> <pwd>
    

    If you are using file for providing initial parameters add the following flag to your properties file:

    skipIdentityColumns=false

    The essential elements are:

    1. Autogenerated columns should only be in SQLFire.

    2. No autogenerated columns in postgres.

    3. SQLFire must use the same schema as the username provided while logging into SQLFire (for example, if the 'admin' user is used, the table names will be 'admin.tablename'.

Comments

Powered by Zendesk