SAP SQL Anywhere CDC: Transaction log setup with Gluesync

Source data from SAP SQL Anywhere

The SAP SQL Anywhere source agent (agent tag sqlanywhere) captures changes from the transaction log of the database. It creates no triggers and no shadow tables, and installs nothing in the database.

How it works

SQL Anywhere writes every change to its transaction log. The SQL Anywhere client includes dbtran, a utility that translates a range of that log into SQL statements. The agent runs dbtran against the database, with the credentials of the agent, and reads what it writes:

A single reader serves every CDC entity of the agent. It checks the log every logPollingIntervalMilliseconds (default 1000 ms). A check that finds no transaction committed since the last one is one cheap query: dbtran runs only when there is something to read;
When there is a new commit, dbtran translates the log from the position after the last transaction the agent handled up to the end of the log. It outputs every transaction committed in that range, in commit order, including the statements of the transaction that were written before the range began. Transactions that are not committed yet are not output, and transactions that rolled back (in full or up to a savepoint) are removed;
The agent parses the output as a stream and keeps the changes of the tables that belong to active CDC entities. Each transaction is stored before the position moves past it, and the position is saved, so after a restart of Core Hub or of the agent the reading resumes where it stopped: changes can be read twice after a crash, never lost.

The position is the commit offset of the last transaction handled. A snapshot that starts records the end of the log first, so a change committed while the snapshot runs is delivered by CDC afterwards.

The latency of a change is about the polling interval plus the time of one dbtran run, which grows with the size of the active log (see Transaction log size and renames).

What the log carries for each operation:

Insert: every column of the row;
Update: on a table with a primary key, the changed columns and the full key. The agent reads the current row by key from the database and completes the change with the columns the log did not carry, so the target receives the full row (a target refuses a partial one). The columns that did not change come from the table as it is when the agent reads it, so a later change of the same row may already be in them: that change is replicated next, and the target ends up consistent. An update of a row that no longer exists is skipped, because a later delete or key change in the log removes it from the target. On a table without a primary key, the log carries the whole row before the change and the changed columns, so no read is needed;
Key change: an update that changes the primary key reaches the target as a delete of the old key and an insert of the new one;
Delete: the key, or the whole row before the change on a table without a primary key.

Changes made by triggers are captured, as the statements they run are in the log. Transactions that touch several tracked tables are delivered in commit order.

Prerequisites

To have Gluesync working on your SAP SQL Anywhere instance as a source you will need:

SAP SQL Anywhere 17.
The SAP SQL Anywhere 17 client available to Core Hub, on Linux x86_64. See The SAP SQL Anywhere client.
A database user with the BACKUP DATABASE system privilege. It is the privilege dbtran needs to scan the log. Without it dbtran answers "Permission denied: you do not have permission to do a log scan", and the agent reports that message and keeps asking until the privilege is granted. SYS_RUN_REPLICATION_ROLE alone is not enough.
SELECT on the tables to replicate, for the snapshot, for the table metadata and to complete updates.
Network access from Core Hub to the server, on its database port (default 2638).

To create such a user, you can follow this quick guide:

CREATE USER gluesync IDENTIFIED BY 'password';

GRANT BACKUP DATABASE TO gluesync;

GRANT SELECT ON owner.table_name TO gluesync;

Run the last statement for every table to replicate, or grant SELECT ANY TABLE instead.

The agent checks the log access when the entity is set up: it runs dbtran over an empty range at the end of the log and fails the setup with the message of the utility when it is refused.

The SAP SQL Anywhere client

The SAP JDBC driver and dbtran are SAP software that Gluesync is not allowed to distribute, so they are not part of Core Hub. The agent loads them at run time from a SQL Anywhere 17 client folder (the client home) that you provide. The agent looks for it, in this order:

the SQL Anywhere client folder set in the agent (sqlAnywhereClientPath);
the folder named by the SQLANY17 environment variable of Core Hub;
/opt/sqlanywhere17.

A full SQL Anywhere 17 installation (the Developer Edition, or the client of your server installation) has the right layout. Copy it from a Linux x86_64 machine, or from a container that has it, to the host of Core Hub. The agent needs only this part of it:

/opt/sqlanywhere17
├── bin64
│   └── dbtran                    (source agent only)
├── java
│   └── sajdbc4.jar
├── lib64
│   ├── libdbjdbc17.so
│   ├── libdbtasks17_r.so
│   ├── libdblib17_r.so
│   ├── libdbtool17_r.so
│   ├── libdbicu17_r.so
│   ├── libdbicudt17.so
│   ├── libdbrsa17_r.so
│   ├── libdbencod17_r.so
│   └── libsapcrypto.so
├── res
│   └── dblgen17.res
└── SAP_SQL_Anywhere_version.txt  (optional, shown in the Core Hub log)

Keep the .so.1 files that sit next to the libraries when you copy them. The agent loads every .so file of lib64, so a full installation works too.

When Core Hub runs in a container, mount the folder read-only. This docker compose example mounts the client from the host:

services:
  gluesync-corehub:
    platform: linux/amd64
    volumes:
      # The SAP SQL Anywhere 17 client, where the agent looks for it by default
      - /opt/sqlanywhere17:/opt/sqlanywhere17:ro

Things to know:

The SQL Anywhere client is available for Linux x86_64 only. On a host with a different architecture (for example Apple silicon) run Core Hub as linux/amd64. On Windows or on any other platform, the agent stops during its setup with a message that says so;
A Core Hub process loads one client folder. To change sqlAnywhereClientPath to another folder, restart Core Hub;
When the client is missing, incomplete or has no bin64/dbtran, the setup of the agent fails and lists the missing files. The log of Core Hub names the client folder and its version when the client loads.

Setup via Web UI

Hostname / IP Address: DNS record or IP Address of your SQL Anywhere server;
Port: Optional, defaults to 2638;
Database name: Name of the database to read (the DBN of the connection);
Username: Username with the privileges listed in the prerequisites;
Password: Password belonging to the given username;
Enable TLS: Optional, defaults to false. When it is on, the agent connects with Encryption=TLS(…). Trust server certificate skips the check of the certificate name (skip_certificate_name_check), and the trust store path points to the certificate file the server certificate is checked against (trusted_certificates). The file can also be uploaded through Core Hub.

Custom properties

SQL Anywhere client folder: (optional, defaults to the SQLANY17 environment variable, then to /opt/sqlanywhere17) Folder of the SQL Anywhere 17 client. See The SAP SQL Anywhere client;
SQL Anywhere server name: (optional) The name of the database server (ServerName of the connection), for a host that runs several servers;
Use connection pool: (optional, defaults to true) Uses a pool of connections for snapshots and reads. Turning it off lowers the performance.

Specific configuration

Setting Default Description

Setting	Default	Description
`logPollingIntervalMilliseconds`	`1000`	How often the transaction log is checked for new commits, in milliseconds (minimum `100`). A check that finds no new commit is a cheap query. Lower values reduce the latency and, when the source is busy, run `dbtran` more often.
`dbtranTimeoutSeconds`	`900`	Time allowed to one run of `dbtran`. A run that takes longer is stopped and reported as an error, and the agent tries again.
`logRenameGapToleranceBytes`	`64`	Bytes of the transaction log that a rename may skip without the agent reporting a gap. `0` is strict. See Transaction log size and renames.
`excludedUsers`	empty	Comma-separated list of database users whose changes are not replicated (for example `etl_loop,gluesync_writer`). Use it in bidirectional setups: the changes the other Gluesync pipeline writes into this database are not sent back. See Excluding users.
`sourceChangeRetention` (Source change retention in hours)	`24`	Retention of the agent’s local change cache, in hours. The cache maintenance drops the cached changes older than this, even when the target has not received them yet, and logs a warning for each entity affected, also for paused entities. Set it above the longest pause or outage of the target you expect. See Source change retention in hours.
Max Concurrency	`0`	Maximum number of concurrent operations (`0` means no limitation).
Max Snapshot Concurrency	`0`	Maximum number of concurrent snapshot operations (`0` means no limitation).
Trim text values	`false`	Trims leading and trailing whitespace of `CHAR`, `VARCHAR`, `NCHAR` and `NVARCHAR` values read by the snapshot and while completing updates.
Technical fields	none	Adds `TRANSACTION_ID` (the commit offset of the log), `TRANSACTION_TIMESTAMP` (the commit time, on the clock of Core Hub), `TRANSACTION_OPERATION` (`I`, `U` or `D`) and `TRANSACTION_USER` (the database user that made the change) to the replicated rows. See Source Agent Advanced Settings.

logPollingIntervalMilliseconds

1000

How often the transaction log is checked for new commits, in milliseconds (minimum 100). A check that finds no new commit is a cheap query. Lower values reduce the latency and, when the source is busy, run dbtran more often.

dbtranTimeoutSeconds

900

Time allowed to one run of dbtran. A run that takes longer is stopped and reported as an error, and the agent tries again.

logRenameGapToleranceBytes

64

Bytes of the transaction log that a rename may skip without the agent reporting a gap. 0 is strict. See Transaction log size and renames.

excludedUsers

empty

Comma-separated list of database users whose changes are not replicated (for example etl_loop,gluesync_writer). Use it in bidirectional setups: the changes the other Gluesync pipeline writes into this database are not sent back. See Excluding users.

sourceChangeRetention (Source change retention in hours)

24

Retention of the agent’s local change cache, in hours. The cache maintenance drops the cached changes older than this, even when the target has not received them yet, and logs a warning for each entity affected, also for paused entities. Set it above the longest pause or outage of the target you expect. See Source change retention in hours.

Max Concurrency

0

Maximum number of concurrent operations (0 means no limitation).

Max Snapshot Concurrency

0

Maximum number of concurrent snapshot operations (0 means no limitation).

Trim text values

false

Trims leading and trailing whitespace of CHAR, VARCHAR, NCHAR and NVARCHAR values read by the snapshot and while completing updates.

Technical fields

none

Adds TRANSACTION_ID (the commit offset of the log), TRANSACTION_TIMESTAMP (the commit time, on the clock of Core Hub), TRANSACTION_OPERATION (I, U or D) and TRANSACTION_USER (the database user that made the change) to the replicated rows. See Source Agent Advanced Settings.

For each table, a SQL Snapshot where clause filter limits the rows the snapshot reads, as it does on the other SQL agents.

Setup via Rest APIs

Here following an example of calling the Core Hub’s Rest API via curl to setup the connection for this Agent.

Connect the agent

curl --location --request PUT 'http://core-hub-ip-address/pipelines/{pipelineId}/agents/{agentId}/config/credentials' \
--header 'Content-Type: application/json' \
--header 'Authorization: ••••••' \
--data '{
    "agentType": "SOURCE",
    "agentTag": "sqlanywhere",
    "hostCredentials": {
        "connectionName": "myAgentNickName",
        "host": "sqlanywhere-host-address",
        "port": 2638,
        "databaseName": "db_name",
        "username": "gluesync",
        "password": "your_password",
        "maxConnectionsCount": 100,
        "enableTls": false
    },
    "customHostCredentials": {
        "sqlAnywhereClientPath": "/opt/sqlanywhere17",
        "serverName": ""
    }
}'

Setup specific configuration

curl --location --request PUT 'http://core-hub-ip-address/pipelines/{pipelineId}/agents/{agentId}/config/specific' \
--header 'Content-Type: application/json' \
--header 'Authorization: ••••••' \
--data '{
  "configuration": {
      "logPollingIntervalMilliseconds": 1000,
      "dbtranTimeoutSeconds": 900,
      "logRenameGapToleranceBytes": 64,
      "excludedUsers": "etl_loop"
    }
}'

Transaction log size and renames

Every run of dbtran reads the whole active transaction log from the server, also when only its last transaction is new. The cost of a poll therefore grows with the size of the active log: a log of several tens of megabytes takes a second or more to translate and to transfer, and the latency of every change grows with it. Keep the active log small by renaming it regularly, with a statement such as:

BACKUP DATABASE DIRECTORY '/path/to/backup' TRANSACTION LOG ONLY TRANSACTION LOG RENAME MATCH;

A rename closes the active log (it becomes an offline log, in the directory of the server’s log) and starts a new, empty one. Two rules keep it safe for the agent:

Rename only after the agent has read the log. The agent reads from the online log. When the log is renamed with unread commits in it, the agent finds them only if the renamed log is readable from where dbtran runs (see below). Otherwise the changes in between are lost, which the agent detects and reports (see A gap in the log);
Make the renamed logs readable. dbtran runs in the machine of Core Hub and finds the renamed logs in the directory of the server’s own log, at the path the server reports. Mount that directory into Core Hub at the same path. To find the path, run SELECT DB_PROPERTY('LogName'): the renamed logs are next to that file. With the directory mounted, the agent reads a log that was renamed before it had read it, and the rename can happen at any moment. The setup logs Offline transaction logs readable: yes or no.

services:
  gluesync-corehub:
    volumes:
      - /opt/sqlanywhere17:/opt/sqlanywhere17:ro
      # The directory of the server's transaction log, at the same path the server reports for it
      - /var/lib/sqlanywhere/log:/var/lib/sqlanywhere/log:ro

Without the mount, a rename still works while the agent is caught up. The new log starts a few bytes above where the agent had stopped (27 bytes measured with no commit in between): up to logRenameGapToleranceBytes bytes (default 64) are taken as no loss, and the agent goes on from the start of the new log, logging a warning when the distance is not zero. A rename that skips more is a gap. Set logRenameGapToleranceBytes to 0 for a strict check.

A gap in the log

When the log was renamed or truncated past the position the agent needs, and the renamed logs cannot be read, the changes in between are gone. The agent never skips them silently: every CDC entity of the agent reports an error that names the offsets, and the reader waits. Mount the log directory as described above, and snapshot the entities again: the reading resumes at the start of the log once every entity affected has started a new snapshot.

Excluding users

excludedUsers is passed to dbtran, which leaves out every transaction of those database users. In a bidirectional setup, give the Gluesync agent that writes into a database its own database user, and list that user in excludedUsers of the agent that reads the same database: the changes one pipeline writes are not replicated back by the other. Changes that triggers make while those users write are excluded with them.

Data types

The agent reads the type of each column from the SQL Anywhere catalog. A column whose type is a user-defined domain is read as its base type.

SQL Anywhere type Gluesync type Notes

SQL Anywhere type	Gluesync type	Notes
`BIT`	`BOOLEAN`
`TINYINT`, `SMALLINT`	`SHORT`	`TINYINT` holds 0 to 255.
`INTEGER`, `UNSIGNED SMALLINT`	`INT`
`BIGINT`, `UNSIGNED INT`	`LONG`
`NUMERIC`, `DECIMAL`, `MONEY`, `SMALLMONEY`	`BIG_DECIMAL`	The declared precision and scale are kept.
`UNSIGNED BIGINT`	`BIG_DECIMAL`	Precision 20, scale 0, so that a target built from it is wide enough.
`REAL`, `FLOAT`	`FLOAT`
`DOUBLE`	`DOUBLE`
`DATE`	`LOCAL_DATE`
`TIME`	`LOCAL_TIME`	Microseconds are kept.
`TIMESTAMP`, `DATETIME`, `SMALLDATETIME`	`LOCAL_DATE_TIME`	Microseconds are kept.
`TIMESTAMP WITH TIME ZONE`	`OFFSET_DATE_TIME`	The offset is kept.
`CHAR`, `VARCHAR`, `LONG VARCHAR`, `TEXT`, `NCHAR`, `NVARCHAR`, `LONG NVARCHAR`, `NTEXT`, `XML`, `SYSNAME`, `UNIQUEIDENTIFIERSTR`	`STRING`	The string literals of the log are decoded with the `CHAR` character set of the database, and `NCHAR` values as UTF-8.
`UNIQUEIDENTIFIER`	`STRING`	The 36 characters of the text form, in lower case.
`VARBIT`, `LONG VARBIT`	`STRING`	A bit string: the value `1010 1100` is the text `10101100`.
`BINARY`, `VARBINARY`, `LONG BINARY`, `IMAGE`	`BYTE_ARRAY`
`ST_GEOMETRY`	`BYTE_ARRAY`	The geometry as EWKB (the PostGIS flavor of WKB, which carries the SRID), the same bytes in the snapshot and in CDC.

BIT

BOOLEAN

TINYINT, SMALLINT

SHORT

TINYINT holds 0 to 255.

INTEGER, UNSIGNED SMALLINT

INT

BIGINT, UNSIGNED INT

LONG

NUMERIC, DECIMAL, MONEY, SMALLMONEY

BIG_DECIMAL

The declared precision and scale are kept.

UNSIGNED BIGINT

BIG_DECIMAL

Precision 20, scale 0, so that a target built from it is wide enough.

REAL, FLOAT

FLOAT

DOUBLE

DATE

LOCAL_DATE

TIME

LOCAL_TIME

Microseconds are kept.

TIMESTAMP, DATETIME, SMALLDATETIME

LOCAL_DATE_TIME

Microseconds are kept.

TIMESTAMP WITH TIME ZONE

OFFSET_DATE_TIME

The offset is kept.

CHAR, VARCHAR, LONG VARCHAR, TEXT, NCHAR, NVARCHAR, LONG NVARCHAR, NTEXT, XML, SYSNAME, UNIQUEIDENTIFIERSTR

STRING

The string literals of the log are decoded with the CHAR character set of the database, and NCHAR values as UTF-8.

UNIQUEIDENTIFIER

STRING

The 36 characters of the text form, in lower case.

VARBIT, LONG VARBIT

STRING

A bit string: the value 1010 1100 is the text 10101100.

BINARY, VARBINARY, LONG BINARY, IMAGE

BYTE_ARRAY

ST_GEOMETRY

BYTE_ARRAY

The geometry as EWKB (the PostGIS flavor of WKB, which carries the SRID), the same bytes in the snapshot and in CDC.

A value that CDC reads from the log equals the value a snapshot of the same row reads, item by item. Long values (LONG VARCHAR, LONG NVARCHAR, LONG BINARY), which the log writes in several pieces, are reassembled by the agent before they are delivered.

Tables without a primary key

A table without a primary key replicates: updates and deletes carry the whole row before the change, which is how the agent identifies the row. A target that locates rows by key needs a key chosen on the entity, and the agent posts a warning at setup when a tracked table has neither a primary key nor an entity key.

Changes of the table structure

An ALTER TABLE, DROP TABLE or TRUNCATE TABLE of a tracked table is not replicated as an operation. When the log shows one, the agent posts a Re-snapshot required warning that names the table: snapshot its entities again to keep the target consistent.

Troubleshooting

The setup fails with a client message

The message says what is missing: the folder (The SQL Anywhere client folder … is incomplete: missing …), bin64/dbtran, a library that does not load, or the platform (SAP ships the SQL Anywhere client for Linux x86_64 only). Check the mount and the files listed in The SAP SQL Anywhere client. After you change the client folder of a running Core Hub, restart it.

Permission denied: you do not have permission to do a log scan

The database user lacks the BACKUP DATABASE system privilege. Run GRANT BACKUP DATABASE TO <user>;. The agent keeps polling and goes on by itself once the privilege is granted.

The log was renamed or truncated

The entities report a gap: see A gap in the log. Mount the directory of the server’s transaction log into Core Hub at the same path, and snapshot the entities again.

The changes arrive late

Look at the size of the active log (dbtran reads all of it on every run that has something to read) and rename it, see Transaction log size and renames. The Core Hub log, at debug level, shows the duration of each dbtran run.