Sybase ASE CDC: Log reader setup with Gluesync

Source data from SAP (Sybase) ASE with the log reader

The Sybase ASE (log reader) source agent (agent tag sybase-logreader) captures changes from the SAP (Sybase) ASE transaction log. It creates no triggers and no shadow tables, and installs nothing on the ASE host.

Log reader or triggers

Gluesync has two source agents for SAP (Sybase) ASE. They read the same tables and deliver the same events to the target; they differ in how the changes are captured.

Log reader (sybase-logreader) Triggers (sybase)

	Log reader (`sybase-logreader`)	Triggers (`sybase`)
Capture	ASE transaction log, through ASE’s Replication Agent (RepAgent) thread.	Insert, update and delete triggers that write to a `_CDC` table per audited table. See Source data from SAP (Sybase) ASE.
Objects created in your database	None. The setup marks the tables for replication and configures the RepAgent of the database.	Three triggers and one `_CDC` table per audited table.
Load on the writing application	None added to the transactions: nothing runs inside them.	The triggers run inside every insert, update and delete of an audited table.
Tables with their own triggers	No conflict.	ASE allows one trigger per operation on a table, so existing triggers must be merged by hand.
Before images	Yes, for updates and deletes.	No.
Truncate	Captured.	Not detected.
Network	ASE connects to Gluesync Core Hub on the log reader port, in addition to Gluesync connecting to ASE.	Gluesync connects to ASE only.
Log retention	ASE keeps its transaction log until Gluesync confirms it has the changes. See Log retention.	No effect on the ASE log.
One agent per database	Yes: a database has one RepAgent, so it cannot also be replicated by SAP Replication Server.	No such constraint.

Capture

ASE transaction log, through ASE’s Replication Agent (RepAgent) thread.

Insert, update and delete triggers that write to a _CDC table per audited table. See Source data from SAP (Sybase) ASE.

Objects created in your database

None. The setup marks the tables for replication and configures the RepAgent of the database.

Three triggers and one _CDC table per audited table.

Load on the writing application

None added to the transactions: nothing runs inside them.

The triggers run inside every insert, update and delete of an audited table.

Tables with their own triggers

No conflict.

ASE allows one trigger per operation on a table, so existing triggers must be merged by hand.

Before images

Yes, for updates and deletes.

No.

Truncate

Captured.

Not detected.

Network

ASE connects to Gluesync Core Hub on the log reader port, in addition to Gluesync connecting to ASE.

Gluesync connects to ASE only.

Log retention

ASE keeps its transaction log until Gluesync confirms it has the changes. See Log retention.

No effect on the ASE log.

One agent per database

Yes: a database has one RepAgent, so it cannot also be replicated by SAP Replication Server.

No such constraint.

Choose the log reader when you cannot or do not want to add triggers and tables to the source database, when the tables already have triggers, when you need before images or truncate events, or when the write load makes trigger overhead unacceptable. Choose triggers when ASE cannot open a connection to the Core Hub host, or when the database is already replicated by SAP Replication Server.

How it works

ASE ships a log reader inside the server: the RepAgent thread scans the transaction log of one database, decodes the log records of the tables marked for replication, and pushes them to a replication endpoint over TCP in the Log Transfer Language (LTL). The log reader agent is that endpoint: Core Hub listens on a TCP port (ltlListenPort), ASE’s RepAgent logs in with the credentials you configure, and from then on ASE streams every committed and rolled back transaction of the replicated tables. Gluesync keeps the changes of each transaction aside until it commits, hands only committed transactions to the pipeline, and tells ASE how far it has safely stored them, so that ASE can release the log up to that point and resume from there after a restart or a network failure. When an entity is first set up, the tables are marked and the RepAgent started before the snapshot begins and the stream position is recorded, so no change made between the snapshot and the start of CDC is lost; a row seen by both is applied once, as an upsert.

Prerequisites

To have Gluesync working on your SAP (Sybase) ASE instance with the log reader you will need:

SAP ASE 15.7 or later. The agent is validated on ASE 16.0 SP02 PL02.
ASE Developer or Enterprise edition. The RepAgent thread needs no additional licensed option beyond ASE_CORE.
A login with permission to read the tables to replicate (used for the snapshot and for the table metadata).
A login with sa_role for the automatic setup, which runs with the username configured in the agent. With a split of duties, sa_role is only needed to run sp_configure 'enable rep agent threads' once for the server; the rest of the setup (sp_config_rep_agent, sp_setreptable, sp_start_rep_agent) runs with replication_role plus ownership of the tables. If your DBA prefers to run the statements, disable the automatic setup and follow Manual setup.
Network access from ASE to Gluesync Core Hub. ASE opens the connection to Core Hub, not the other way round. The log reader port (ltlListenPort, default 5500) must be reachable from the ASE host, and when Core Hub runs in a container the port must be published from the Core Hub container:
```
services:
  gluesync-corehub:
    ports:
      - "5500:5500"
```
Gluesync still connects to ASE on its usual port (default 5000) for the snapshot, the setup and the metadata.
One RepAgent per database. ASE runs a single RepAgent for each database. A database whose RepAgent already sends to another Replication Server (including a real SAP Replication Server) is not taken over unless you set forceRepAgentTakeover, which points it at Gluesync and stops the other replication.
One database per agent. The agent reads the log of the database named in the connection; log streams of other databases are refused.

To create a login with the permissions needed by the automatic setup, you can follow this quick guide:

sp_addlogin gluesync_admin, password
go

sp_adduser gluesync_admin, gluesync_admin
go

grant role sa_role to gluesync_admin
go

Setup via Web UI

Hostname / IP Address: DNS record or IP Address of your ASE server;
Port: Optional, defaults to 5000;
Database name: Name of the database whose transaction log is read;
Username: Login used by Gluesync to read the tables and to run the setup;
Password: Password belonging to the given username.

Log reader credentials

Field Default Description

Field	Default	Description
LTL listen port (`ltlListenPort`)	`5500`	TCP port on which Core Hub listens for the RepAgent of ASE. Publish it from the Core Hub container and open it on any firewall between ASE and Core Hub.
LTL advertised host (`ltlAdvertisedHost`)	none (required)	Host name or IP address that ASE uses to reach Core Hub, as seen from the ASE host. It is written into ASE together with the port (`host:port`), so use a bare host name or address, without port or spaces. When Core Hub runs behind Docker or a NAT, this is the address of the machine that publishes the port, not the container address.
LTL bind address (`ltlBindAddress`)	empty (all interfaces)	Optional. Local interface address on which the port is opened.
Replication Agent user (`repAgentUser`)	`gs_ltl_user`	Login name that the RepAgent presents to Gluesync. It is a credential pair that Gluesync checks, not an ASE login, so it does not need to exist in ASE. Up to 30 letters, digits, `_`, `.`, `-` or `@`.
Replication Agent password (`repAgentPassword`)	none (required)	Password that the RepAgent presents to Gluesync. It must not contain quotes or control characters. See Security.
Maintenance user (`maintenanceUser`)	`gs_ltl_maint`	Name of an ASE login that never writes to the replicated tables. ASE does not capture the transactions of the maintenance user. Choose a name that no application uses.

LTL listen port (ltlListenPort)

5500

TCP port on which Core Hub listens for the RepAgent of ASE. Publish it from the Core Hub container and open it on any firewall between ASE and Core Hub.

LTL advertised host (ltlAdvertisedHost)

none (required)

Host name or IP address that ASE uses to reach Core Hub, as seen from the ASE host. It is written into ASE together with the port (host:port), so use a bare host name or address, without port or spaces. When Core Hub runs behind Docker or a NAT, this is the address of the machine that publishes the port, not the container address.

LTL bind address (ltlBindAddress)

empty (all interfaces)

Optional. Local interface address on which the port is opened.

Replication Agent user (repAgentUser)

gs_ltl_user

Login name that the RepAgent presents to Gluesync. It is a credential pair that Gluesync checks, not an ASE login, so it does not need to exist in ASE. Up to 30 letters, digits, _, ., - or @.

Replication Agent password (repAgentPassword)

none (required)

Password that the RepAgent presents to Gluesync. It must not contain quotes or control characters. See Security.

Maintenance user (maintenanceUser)

gs_ltl_maint

Name of an ASE login that never writes to the replicated tables. ASE does not capture the transactions of the maintenance user. Choose a name that no application uses.

Specific configuration

Field Default Description

Field	Default	Description
Disable automatic Replication Agent setup (`disableAutomaticRepAgentSetup`)	`false`	When `true`, Gluesync does not configure ASE: it writes the statements a DBA has to run to the Core Hub log. See Manual setup.
Force Replication Agent takeover (`forceRepAgentTakeover`)	`false`	When `true`, a database whose RepAgent already sends to another Replication Server is pointed at Gluesync. Leave it `false` unless you are sure nothing else depends on that RepAgent.
Warn when a stopped stream keeps more of the log than (MB) (`retainedLogWarningMb`)	`1024`	When the stream of a database is stopped and the log that ASE keeps for Gluesync grows past this size, Gluesync posts a warning. `0` disables the warning. See Log retention.
Max Concurrency (`maxConcurrency`)	`0`	Maximum number of entities that run at the same time. `0` means no limit.
Max Snapshot Concurrency (`maxSnapshotConcurrency`)	`0`	Maximum number of snapshots that run at the same time. `0` means no limit.
Trim text values	`false`	Trims trailing whitespace of text values before they are sent to the target, as in the triggers agent.
Technical fields	none	Adds transaction id, timestamp, operation and user to the replicated rows. See Source Agent Advanced Settings.

Disable automatic Replication Agent setup (disableAutomaticRepAgentSetup)

false

When true, Gluesync does not configure ASE: it writes the statements a DBA has to run to the Core Hub log. See Manual setup.

Force Replication Agent takeover (forceRepAgentTakeover)

false

When true, a database whose RepAgent already sends to another Replication Server is pointed at Gluesync. Leave it false unless you are sure nothing else depends on that RepAgent.

Warn when a stopped stream keeps more of the log than (MB) (retainedLogWarningMb)

1024

When the stream of a database is stopped and the log that ASE keeps for Gluesync grows past this size, Gluesync posts a warning. 0 disables the warning. See Log retention.

Max Concurrency (maxConcurrency)

0

Maximum number of entities that run at the same time. 0 means no limit.

Max Snapshot Concurrency (maxSnapshotConcurrency)

0

Maximum number of snapshots that run at the same time. 0 means no limit.

Trim text values

false

Trims trailing whitespace of text values before they are sent to the target, as in the triggers agent.

Technical fields

none

Adds transaction id, timestamp, operation and user to the replicated rows. See Source Agent Advanced Settings.

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": "sybase-logreader",
    "hostCredentials": {
        "connectionName": "myAgentNickName",
        "host": "ase-host-address",
        "port": 5000,
        "databaseName": "db_name",
        "username": "gluesync_admin",
        "password": "your_password",
        "maxConnectionsCount": 100
    },
    "customHostCredentials": {
        "ltlListenPort": 5500,
        "ltlAdvertisedHost": "corehub-host-address",
        "repAgentUser": "gs_ltl_user",
        "repAgentPassword": "your_rep_agent_password",
        "maintenanceUser": "gs_ltl_maint"
    }
}'

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": {
      "disableAutomaticRepAgentSetup": false,
      "forceRepAgentTakeover": false
    }
}'

What the automatic setup does

When you set up an entity, Gluesync runs the following statements on ASE. Each step looks at the server first, so running the setup again changes nothing that is already in place.

sp_configure 'enable rep agent threads', 1                                    -- server, once
sp_config_rep_agent <db>, 'enable', '<advertisedHost>:<port>', '<user>', '<password>'
sp_config_rep_agent <db>, 'net password encryption', 'false'
sp_setreptable <owner.table>, 'true'                                          -- per table
sp_start_rep_agent <db>

Gluesync also sets the RepAgent to start with the server (auto start), to scan the log every 2 seconds when it is quiet (scan timeout) and to retry a lost connection every 10 seconds (retry timeout). While no RepAgent session is connected, Gluesync checks periodically that the RepAgent of the database is running, and starts it when it is not.

Manual setup

If DBAs run the changes, or the Gluesync login has no sa_role, set Disable automatic Replication Agent setup to true. When the entity is set up, Gluesync does not touch ASE and logs the exact statements to run, as sa, in the source database, with <repAgentPassword> in place of the password:

sp_configure 'enable rep agent threads', 1
go
sp_config_rep_agent <db>, 'enable', '<advertisedHost>:<port>', '<repAgentUser>', '<repAgentPassword>'
go
sp_config_rep_agent <db>, 'net password encryption', 'false'
go
sp_config_rep_agent <db>, 'auto start', 'true'
go
sp_setreptable '<owner.table>', 'true'
go
sp_start_rep_agent <db>
go

Run sp_setreptable once per table that you replicate. <advertisedHost>:<port> is ltlAdvertisedHost and ltlListenPort, and <repAgentUser> and <repAgentPassword> are the values configured in the agent. With the manual setup you also run sp_start_rep_agent <db> yourself whenever the RepAgent is stopped.

Log retention

The RepAgent holds a secondary truncation point in the transaction log of the database, and Gluesync moves it forward only after it has stored the changes (the RepAgent resumes strictly after the position Gluesync last confirmed). As a result:

ASE keeps the log until Gluesync confirms it. While Core Hub is stopped, the pipeline is paused, or ASE cannot reach Core Hub, the log keeps growing and dump transaction does not free the part that Gluesync has not confirmed.
Size the log for the longest outage you accept. Estimate the log volume per hour at peak, multiply it by the longest time the pipeline can be down, and monitor the log space (sp_spaceused syslogs, and dbcc gettrunc for the position of the truncation point). If the log fills, ASE refuses writes in the database (error 1105) until the log is released: restore the connection to Core Hub, or, if the agent is not coming back, disable the RepAgent as described below.
Release the log when you remove the agent. When you remove the last entity of the agent, Gluesync stops the RepAgent, unmarks the tables (sp_setreptable <owner.table>, 'false') and disables it (sp_config_rep_agent <db>, 'disable'), which drops the secondary truncation point so that the log is no longer retained. If the setup was manual, or the Core Hub is gone, a DBA must do it:
```
sp_stop_rep_agent <db>
go
sp_setreptable '<owner.table>', 'false'
go
sp_config_rep_agent <db>, 'disable'
go
```

Disabling the RepAgent and enabling it again starts a new history of the log: changes made while it was disabled are not captured, so take a new snapshot.

Data types

The agent decodes the values of the log with the type of the column in the ASE catalog. A column whose type is a user-defined data type is read by its base type. The Gluesync type of each ASE type is the same as for the triggers agent and the target agent.

ASE type Gluesync type Notes

ASE type	Gluesync type	Notes
`tinyint`, `int`, `unsigned smallint`	`INT`
`smallint`	`SHORT`
`bigint`, `unsigned int`	`LONG`
`bit`	`BOOLEAN`
`decimal`, `numeric`	`BIG_DECIMAL`	The declared scale is kept.
`smallmoney`, `money`	`BIG_DECIMAL`
`unsigned bigint`	`BIG_DECIMAL`	Precision 20, scale 0.
`real`	`FLOAT`
`float`, `double precision`	`DOUBLE`
`date`	`LOCAL_DATE`
`time`, `bigtime`	`LOCAL_TIME`	`bigtime` keeps microseconds.
`datetime`, `smalldatetime`, `bigdatetime`	`LOCAL_DATE_TIME`	`bigdatetime` keeps microseconds. ASE logs `datetime` and `time` fractions truncated (`.997` as `.996`); Gluesync restores the stored value.
`char`, `varchar`, `nchar`, `nvarchar`	`STRING`	Read with the character set of the ASE server.
`unichar`, `univarchar`	`STRING`	UTF-16 values.
`text`	`STRING`	Large object, see below.
`unitext`	`STRING`	Large object, UTF-16.
`binary`, `varbinary`	`BYTE_ARRAY`	`binary` values are padded back to the declared length.
`image`	`BYTE_ARRAY`	Large object, see below.
`timestamp`	`BYTE_ARRAY`	The 8 bytes of the ASE timestamp.

tinyint, int, unsigned smallint

INT

smallint

SHORT

bigint, unsigned int

LONG

bit

BOOLEAN

decimal, numeric

BIG_DECIMAL

The declared scale is kept.

smallmoney, money

BIG_DECIMAL

unsigned bigint

BIG_DECIMAL

Precision 20, scale 0.

real

FLOAT

float, double precision

DOUBLE

date

LOCAL_DATE

time, bigtime

LOCAL_TIME

bigtime keeps microseconds.

datetime, smalldatetime, bigdatetime

LOCAL_DATE_TIME

bigdatetime keeps microseconds. ASE logs datetime and time fractions truncated (.997 as .996); Gluesync restores the stored value.

char, varchar, nchar, nvarchar

STRING

Read with the character set of the ASE server.

unichar, univarchar

STRING

UTF-16 values.

text

STRING

Large object, see below.

unitext

STRING

Large object, UTF-16.

binary, varbinary

BYTE_ARRAY

binary values are padded back to the declared length.

image

BYTE_ARRAY

Large object, see below.

timestamp

BYTE_ARRAY

The 8 bytes of the ASE timestamp.

text, unitext and image columns are large objects: ASE logs them in segments, and Gluesync reassembles them into a single value. A large object column that is not null is always sent with the row, also when only another column of the row changed.

User-defined types created with sp_addtype, sysname and longsysname replicate as their base type.

Captured operations

Insert: the row is delivered with the values of the inserted columns.
Update: the row is delivered with the new values and the before image of the columns. See Working with before and after images.
Delete: the row is delivered with the key and the before image.
Large object changes: inserts and updates of text, unitext and image columns, including changes made with writetext, are delivered as updates of the row.
Truncate: truncate table on a replicated table is delivered as a truncate operation to the target, like on the other log-based agents.
Transactions: only committed transactions are delivered, in commit order. Transactions that are rolled back, in full or up to a savepoint, are not delivered.
Schema changes: when a replicated table is altered (for example alter table … add column), ASE tells Gluesync and the agent reloads the columns of the table. A new column is replicated from then on; a dropped column is ignored.

Changes made by the maintenance user (maintenanceUser) are not captured, and tables that are not part of an entity of the agent are not replicated even if they are marked for replication.

Working with before and after images

Unlike the triggers agent, the log reader supports Before & After images: for an update, the log carries the values the row had before the change, and Gluesync passes them on with the change. For a large object column that did not change, the old and the new value are the same.

Troubleshooting

The RepAgent does not connect

Check the state of the RepAgent in ASE: sp_help_rep_agent <db> (and sp_help_rep_agent <db>, 'process' for the connection). It must be running and its replication server must be <ltlAdvertisedHost>:<ltlListenPort>.
Check the ASE errorlog for the RepAgent messages: they name the host and port it tried and the error (connection refused, timeout, login failed).
From the ASE host, make sure the host and port are reachable (ltlAdvertisedHost resolves from the ASE host, the port is published from the Core Hub container and is open on every firewall).
In the Core Hub log, look for Listening for the Replication Agent: it shows the port that Core Hub opened. The line The log stream cannot listen on port means that the port is already in use: change ltlListenPort (and the published port) or free the port.
The line Replication Agent of … connected confirms that the RepAgent logged in; Connection for … refused means that it asked for a different database than the one the agent is set up for.

The RepAgent password is wrong

The Core Hub log shows Login of '…' refused. When ASE’s RepAgent is refused it stops, and it does not retry. Correct repAgentPassword (or repAgentUser) in the agent, and start the RepAgent again with sp_start_rep_agent <db>. With the automatic setup, Gluesync updates the RepAgent with the new credentials and restarts it for you.

The RepAgent is stopped

ASE can stop a RepAgent after an error. With the automatic setup, Gluesync starts it again when no session is connected. If it does not start, or the setup is manual, run sp_start_rep_agent <db> and read the ASE errorlog for the reason.

The database is already replicated

The setup stops with a message saying that the RepAgent of the database already sends to another server. A database has one RepAgent, so a database already replicated by SAP Replication Server cannot also be read by the log reader. Either use the triggers agent for that database, or, if the other replication is obsolete, set Force Replication Agent takeover to true to point the RepAgent at Gluesync (this stops the other replication).

The login lacks a role

The setup stops with a message saying that the login has neither sa_role nor replication_role, or lacks sa_role for sp_configure 'enable rep agent threads'. Grant the role, enable the Replication Agent threads yourself, or use the manual setup.

The log is full

ASE raises error 1105 on syslogs. The secondary truncation point is held by Gluesync because it has not confirmed the changes: check that the pipeline is running and the RepAgent is connected, so that the log is released as the backlog is stored. If the log cannot wait, extend the log device (alter database … log on). If the agent is being retired, disable the RepAgent (see Log retention, which also covers sizing the log).

An update of another column of a table with a text or image column fails

A text or image column that is set to be replicated only when it changes (sp_setrepcol … 'replicate_if_changed') has no value in the log when another column is updated. Set it to always replicate:

sp_setrepcol '<owner.table>', '<column>', 'always_replicate'
go

Security

The RepAgent password is not encrypted on the wire. ASE sends it with the login of the RepAgent to the log reader port, and the log reader does not support the encrypted password handshake, so the setup sets net password encryption to false for the RepAgent of the database. The LTL stream itself, which carries the data of your tables, is not encrypted either. Keep the link between ASE and Core Hub on a trusted network (same data center, private network or VPN), use a password for repAgentPassword that you use nowhere else, and do not expose the log reader port to untrusted networks. The credentials Gluesync uses to read ASE (host, port, username, password) are unaffected by this.