Search docs.molo17.com
    Configuration guide Connectors

    Set up a SAP (Sybase) ASE triggers source

    The SAP (Sybase) ASE Triggers agent captures changes with three triggers on each table that you replicate. Every trigger writes a copy of the changed row into a CDC table that Gluesync creates next to your data, and the agent reads that table in order. The agent needs only one connection, from Core Hub to ASE, and it works on every ASE version from 15.7. This guide creates the login, runs a snapshot and live changes to PostgreSQL, shows every object that Gluesync adds, explains how long changes stay in your database, and helps you choose between the triggers agent and the log reader.

    In this guide

    You will create the ASE login that the automatic setup needs, create the source connection in the Control Plane, and add an entity. You will list the objects that Gluesync adds to ASE and read the raw changes while the entity is on hold. You will start a snapshot with live changes, check inserts, updates, deletes and a rolled back transaction on the target, and see what a change of the primary key does. You will set the retention of the CDC table, create the triggers by hand when your DBA does not allow automatic ones, and remove an entity with its objects.

    Quick information

    Goal

    Replicate a SAP (Sybase) ASE table to PostgreSQL with a snapshot followed by trigger-based change data capture (CDC), and know what the agent adds to your database

    Audience

    Database administrators and data engineers who own an ASE server and cannot, or do not want to, let ASE connect to Core Hub

    Level

    Intermediate

    Time required

    About 60 minutes

    Area

    SAP ASE → SQL and isql; Control Plane → Data pipelines → SAP (Sybase) ASE Triggers agent → Base settings and Advanced settings; entity → start dialog

    Applies to

    Gluesync 2.3

    Before you begin

    Make sure that:

    1. You have SAP ASE 15.7 or later and you can run SQL on it as sa. This guide was tested on ASE 16.0 SP02 PL02;

    2. Core Hub can open a connection to the ASE port (5000 by default). ASE does not need to open any connection to Core Hub;

    3. You have a source table with a primary key. This guide uses dbo.orders with 5,000 rows. The first step creates it;

    4. You have a PostgreSQL database as the target, and a login that can create tables in it. The target can be any other agent: the steps for the source are the same;

    5. You can sign in to the Control Plane with a role that can create pipelines;

    6. The transaction log of your database has free space. The triggers write each change twice inside the same transaction, once in your table and once in the CDC table, so the log grows faster.

    A trigger runs inside every transaction that changes the table. Try this guide on a test database first, and tell the owners of the application before you add triggers to a production table.

    The messages and screens in this guide were captured in the Control Plane, in the Core Hub log and in isql with ASE 16.0 SP02 PL02 as the source and PostgreSQL 16 as the target. The options of the agent are listed in the triggers agent reference and the SAP ASE agent overview. Where the reference and this guide differ, the guide describes what the Control Plane did.

    Triggers or log reader

    Gluesync has two source agents for SAP ASE. Both read the same tables and deliver the same events to the same targets. They differ in how they see the changes.

    Triggers agent (this guide) Log reader

    Capture

    Insert, update and delete triggers write each change to a CDC table

    ASE reads its own transaction log and pushes the changes to Gluesync

    Network path

    Core Hub opens a connection to ASE

    Core Hub opens a connection to ASE, and ASE opens a connection to Core Hub on a TCP port

    Objects in your database

    One CDC table and three triggers for each table

    None. The setup marks the table for replication and configures the Replication Agent of the database

    Load on your application

    One more insert inside every insert, update and delete of an audited table

    Nothing runs inside your transactions

    Tables that already have triggers

    The triggers run side by side on the lab server, see Existing triggers on the table

    No conflict

    Database already replicated by SAP Replication Server

    Works: it does not use the Replication Agent

    One database has one Replication Agent, so you cannot share it unless you take it over

    Before images and truncate events

    Not captured

    Captured

    Pick the triggers agent when ASE cannot reach Core Hub (firewall, network address translation, a separate network), when SAP Replication Server already reads the database, or when you cannot give the Replication Agent to Gluesync. Pick the log reader when you do not want to add objects to your schema, when you need before images or truncate events, or when the cost of a trigger inside every write is too high. Follow Set up a SAP ASE log reader source to use the log reader.

    Guide steps

    Create the sample table

    SQL

    This step is optional. Skip it if you have your own table with a primary key. Connect to ASE as sa and create a table with 5,000 rows. The notes column is filled for every 500th row only, because ASE keeps each text value on its own page and a small test database fills up quickly.

    use demo
go
create table dbo.orders (
    id int not null,
    customer varchar(50) not null,
    status varchar(20) null,
    amount numeric(12,2) null,
    created datetime null,
    notes text null,
    primary key (id)
)
go
set nocount on
go
declare @i int
select @i = 1
while @i <= 5000
begin
    insert dbo.orders (id, customer, status, amount, created, notes)
    values (@i, 'customer-' + convert(varchar(10), @i), 'new', @i * 1.5, getdate(),
            case when @i % 500 = 0 then 'note ' + convert(varchar(10), @i) else null end)
    select @i = @i + 1
end
go
set nocount off
go
select count(*) from dbo.orders
go
    Expected result

    The last query returns 5000.

    Create the login for the automatic setup

    SQL

    When you add an entity, Gluesync creates the CDC table and the triggers with the login that you put in the connection. That login needs sa_role, and it must be a user of the source database. Replace the password.

    1. Create the login, add it as a user of the source database and give it the role:

      sp_addlogin gluesync_admin, <a strong password>
go
use demo
go
sp_adduser gluesync_admin, gluesync_admin
go
use master
go
grant role sa_role to gluesync_admin
go

    2. Check the login:

      sp_displaylogin gluesync_admin
go
    Expected result

    sp_displaylogin shows Configured Authorization: followed by sa_role (default ON).

    The user name matters. Gluesync creates each CDC table with the login name as the owner, so gluesync_admin owns gluesync_admin.dbo_orders_CDC. A login that is only an alias of dbo does not work, and neither does a login with SELECT and CREATE TABLE only. Step 5 shows the error of each case, and the troubleshooting table lists them.

    Create the pipeline and the source connection

    UI

    1. In the Control Plane, select Create your first pipeline, or the + button of the pipeline list if you already have pipelines. The Agents setup wizard opens;

    2. In 1. Setup source, search for sybase and select the SAP (Sybase) ASE tile. It offers two connectors: select SAP (Sybase) ASE Triggers agent, not the Log Reader agent;

    3. In Base settings, fill in the connection:

      • Host name / Address and Port: your ASE server and its port, 5000 by default;

      • Database name: the database that you replicate, here demo. The agent reads one database;

      • Username and Password: the login of step 2;

    4. Leave Max connections count at its default of 25 for now, and select Test and continue. The source appears with a green check mark. Select Next.

    Base settings of the SAP Sybase ASE Triggers agent with host
    Figure 1. The source connection of the SAP (Sybase) ASE Triggers agent. The protocol is fixed to JDBC.
    Expected result

    The source row shows gluesync_admin@<host>:5000 and a green check mark. The Core Hub log shows Check Connection finished and CheckVersionComponentSetup - Database version: Adaptive Server Enterprise/16.0 SP02 PL02. At this point Gluesync has not changed anything in ASE: the setup runs when you add an entity.

    To use TLS between Core Hub and ASE, follow Enable HTTPS and TLS to databases.

    Create the PostgreSQL target and name the pipeline

    UI

    1. In 2. Setup target(s), search for postgres, choose the PostgreSQL CDC agent tile and fill in Base settings: Host name / Address, Port (5432), Database name, Username and Password. Select Test and continue. The target appears with a green check mark. Select Next;

    2. In 3. Ready, enter a Pipeline name, for example ASE triggers to PostgreSQL, and select Finish.

    Expected result

    The pipeline appears in the HEALTHY group of the pipeline list, with the ASE and PostgreSQL icons, and the page shows No entities found.

    Add the entity and read what Gluesync created

    UI + SQL

    1. In the pipeline, select Entities. The Add entities page opens with the Object browser. Expand the schema dbo and select the table orders. The Field mapping dialog opens;

    2. Under Target, open Target Schema and select public. In Target Table type orders and choose Create table "orders";

    3. Two fields show a red error and Missing required field appears at the top: the Precision of the datetime column (created) and the Length of the text column (notes). Enter 6 as the precision of created. Enter a length for notes, for example 1000000. PostgreSQL stores the column as plain text whatever the length is. The Confirm button turns blue;

    4. Select Confirm. The Create table orders dialog shows the read-only SQL statement. Read it, then select Create table orders to run it on PostgreSQL;

    5. Back on the Add entities page, select Confirm and continue.

    Field mapping dialog for dbo.orders with the new table public.orders
    Figure 2. The Field mapping dialog after the two required fields are set. The source types are on the left, the PostgreSQL types on the right.

    The entity appears on Hold. Gluesync has now added its objects to ASE. List them as sa:

    use demo
go
select u.name as owner, o.name, o.type
from sysobjects o, sysusers u
where o.uid = u.uid and o.type in ('U', 'TR')
order by o.name
go
select * from gluesync_admin.dbo_orders_CDC where 1 = 0
go
sp_helptext trigger_dbo_orders_update
go
    Expected result

    Besides dbo.orders, the first query lists four objects:

    • gluesync_admin.dbo_orders_CDC: the CDC table, a user table owned by the login. Its name is <schema>_<table>_CDC;

    • trigger_dbo_orders_insert, trigger_dbo_orders_update and trigger_dbo_orders_delete: the three triggers, named trigger_<schema>_<table>_<operation>. They belong to dbo, the owner of the table.

    The second query shows the columns of the CDC table: GS_ID, TIMESTAMP, OPERATION, then id, customer, status, amount and created. There is no notes: text, unitext and image columns are not copied into the CDC table. sp_helptext shows that the update trigger inserts GETDATE(), the letter U and the row from inserted into the CDC table.

    The CDC table has GS_ID, a bigint identity that is also its primary key (ASE creates a unique clustered index on it), the capture time in TIMESTAMP and OPERATION, one of I, U or D.

    In the Control Plane, the Object browser now lists the schema gluesync_admin with the CDC table inside. Do not add it as an entity.

    Right after the first entity you can see a notification such as gluesync_admin.dbo_orders_CDC not found. Specify owner.objectname or use sp_help to check whether the object exists. The cleaner of the agent, which step 9 describes, ran once before the CDC table was created. The table exists now, as the query above shows, and the next pass of the cleaner finds it.

    To see the errors of an under-privileged login, create gluesync_ro as a user of the database with grant create table and grant select on dbo.items, put it in a second pipeline and add dbo.items (a table that you create with two columns, id int and name varchar(30)). The entity is refused, and the page shows the message of ASE:

    Add entities page with the error The entity could not be saved
    Figure 3. The entity page of a login that has SELECT and CREATE TABLE but no sa_role.

    If the login is an alias of dbo (sp_addalias gluesync_ro, dbo), the message is User name gluesync_ro does not exist in sysusers. In both cases, give the login sa_role and add it as a user, as in step 2.

    Watch the raw changes while the entity is on hold

    SQL

    The triggers record every change from the moment the entity is saved, also while it is on hold. As sa, change four rows, then read the CDC table:

    insert dbo.orders (id, customer, status, amount, created, notes)
    values (900001, 'Acme', 'new', 99.50, getdate(), 'first order')
go
update dbo.orders set status = 'paid', amount = 120.00 where id = 5
go
delete from dbo.orders where id = 6
go
update dbo.orders set id = 7000 where id = 7
go
select GS_ID, OPERATION, id, status, amount from gluesync_admin.dbo_orders_CDC order by GS_ID
go
    Expected result

    The CDC table holds four rows:

    GS_ID OPERATION id status amount

    1

    I

    900001

    new

    99.50

    2

    U

    5

    paid

    120.00

    3

    D

    6

    new

    9.00

    4

    U

    7000

    new

    10.50

    The delete row holds the values of the deleted row. The update of the primary key is a single U row with the new key 7000: there is no D row for the old key 7.

    The trigger copies inserted for an insert and an update, and deleted for a delete. It records the new image only: the agent has no before image. See Working with Before & After images.
    Start the snapshot and CDC

    UI + SQL

    1. On the row of the entity, select the play button. The Sync changes (CDC) dialog opens;

    2. Under Start from keep Full copy, Recommended for the first start. It copies all the rows, then follows the changes. Where it stopped is not available before the first run, and Now skips what changed before the start;

    3. Under Data already in the target keep Keep and merge, and under Speed keep Standard;

    4. Select Start entity.

    Sync changes CDC dialog for dbo.orders with Start from Full copy
    Figure 4. The start dialog of a new entity, with Full copy and Keep and merge.

    Compare the two sides and read the CDC table again:

    -- on the source
select count(*) from dbo.orders
select GS_ID, OPERATION, id from gluesync_admin.dbo_orders_CDC order by GS_ID
    -- on the target
SELECT count(*) FROM public.orders;
SELECT id, status, amount FROM public.orders WHERE id IN (5, 6, 7, 7000, 900001) ORDER BY id;
    Expected result

    The source and the target both count 5000. The target has rows 5 (paid, 120.00), 7000 and 900001, and no row 6 or 7: the snapshot reads the table as it is now, so it already has the four changes of the previous step. The CDC table still holds its four rows. The agent does not delete the rows that it reads, see step 9.

    Pipeline ASE triggers to PostgreSQL with the entities dbo.orders
    Figure 5. The entities of the pipeline are active and follow the changes.
    Follow live changes and check the operations

    SQL

    On the source, run an insert, an update, a delete, a rolled back transaction, a change of a primary key, a change of the text column and a bigger update:

    insert dbo.orders (id, customer, status, amount, created, notes)
    values (900002, 'Live', 'new', 5, getdate(), 'live note')
go
update dbo.orders set status = 'paid', amount = 30 where id = 10
go
delete from dbo.orders where id = 11
go
begin tran
insert dbo.orders (id, customer, status, amount, created)
    values (900003, 'Rolled back', 'new', 1, getdate())
rollback tran
go
update dbo.orders set id = 8000 where id = 8
go
update dbo.orders set notes = 'changed note' where id = 900001
go
update dbo.orders set amount = amount + 1 where id between 100 and 1099
go

    After a few seconds, read the rows on the target:

    SELECT count(*) FROM public.orders;
SELECT id, status, amount, left(notes, 20) AS notes FROM public.orders
WHERE id IN (8, 10, 11, 8000, 900001, 900002, 900003) ORDER BY id;
SELECT id, amount FROM public.orders WHERE id IN (99, 100, 1099, 1100) ORDER BY id;
    Expected result

    Row 10 is paid with 30.00, row 11 is gone, row 900002 has live note, row 900001 has changed note, and there is no row 900003: a rolled back transaction never reaches the CDC table. Only rows 100 to 1099 have the new amount (151.00 and 1649.50 for the two edge rows). The CDC table gained 1,005 rows, and its GS_ID has a gap: the rolled back insert used one value.

    The count is 5001, one more than the 5000 rows of the source. Row 8 is still on the target next to the new row 8000. See the note below.

    An update that changes a primary key reaches the target as an update of the new key. The row with the old key stays on the target. Do not change primary keys of replicated tables, or delete the old key on the target after you change one. To find such rows, follow Find and fix row differences between source and target.

    For the same reason, truncate table is not replicated. ASE does not run delete triggers for it, so the CDC table gets no row. After a truncate table of a source table, the target still has all its rows: empty it yourself, or start the entity again with Full copy and Empty the table first.

    A text, unitext or image column is not in the CDC table. The agent reads it from the table when it processes the change, so an update of notes alone still reaches the target, as the row 900001 shows.

    Set the retention of the CDC table

    UI + SQL

    The agent does not delete the rows of the CDC table when it has read them. A cleaner deletes the rows that are older than Source change retention in hours. The default is 24 hours. The cleaner runs when the agent starts and then every hour, and it purges the CDC table of every entity of the pipeline, also when the entity is paused.

    1. Count the rows, as gluesync_admin:

      select count(*), min(GS_ID) from gluesync_admin.dbo_orders_CDC
go

    2. In a test database only, age the 600 oldest rows by hand, so that you can see the cleaner work. Do not edit the CDC table of a pipeline that you run for real:

      update gluesync_admin.dbo_orders_CDC set TIMESTAMP = dateadd(hour, -48, TIMESTAMP) where GS_ID <= 600
go

    3. In the pipeline, select the gear icon next to Entities, then Edit source. Open the Advanced settings tab and select Add property. In Key choose Source change retention in hours, enter 12 as the Value and select Test and continue;

    4. To see the cleaner work now in a test environment, restart Core Hub. The cleaner purges right away when it starts. Count the rows again.

    Edit source page with the Advanced settings tab
    Figure 6. The Advanced settings tab of the source. The retention is 12 hours and Disable automatic triggers creation is on (see step 11).
    Expected result

    Before the purge the count is 1009 and the smallest GS_ID is 1. After it the count is 410 and the smallest GS_ID is 601: the 599 rows that you aged (GS_ID 1 to 600, except the rolled back value) are gone. The Core Hub log shows the pass for each table:

    SybaseCdcCleaner INFO - Start to purge CDC data for table: gluesync_admin.dbo_orders_CDC
SybaseCdcCleaner INFO - Successfully purged CDC data for table: gluesync_admin.dbo_orders_CDC

    The target is not touched: it still counts 5001 rows.

    What the retention means in practice:

    • Only unread rows are needed. The agent keeps its position by GS_ID, so a row that was already delivered is safe to delete. A row that is not delivered yet is lost if the cleaner deletes it. Do not set a retention shorter than the longest time that an entity can stay paused, on hold or in error;

    • A value of 0 switches the cleaner off. The log then says Purge CDC data for table: <table> is set to 0 and the CDC table grows until you delete rows yourself;

    • A paused entity keeps recording. The triggers go on writing to the CDC table, and the cleaner purges the table as usual. The next step shows it.

    Pause and resume

    UI + SQL

    Pausing an entity keeps its position. The triggers go on recording, so nothing is lost until the retention deletes the rows.

    1. Select the pause button of the entity, then Pause entity;

    2. On the source, change some rows:

      update dbo.orders set status = 'paused-change' where id between 200 and 204
go
insert dbo.orders (id, customer, status, amount, created)
    values (900010, 'while-paused', 'new', 7, getdate())
go

    3. Count the rows that wait in the CDC table:

      select count(*), max(GS_ID) from gluesync_admin.dbo_orders_CDC
go

    4. Select the play button. Where it stopped is now selected: keep it and select Start entity.

    Expected result

    While the entity is paused, the CDC table gains six rows (416 rows in the lab, five updates and one insert) and the target still has new for row 200. After the start with Where it stopped, the target shows paused-change for rows 200 and 204 and has the new row 900010.

    Create the triggers by hand

    UI + SQL

    Use this step when your DBA must review every statement before it runs, or when you do not want Gluesync to create triggers. Gluesync still creates the CDC table, and you create the three triggers.

    1. In the pipeline, select the gear icon next to Entities, then Edit source. In Advanced settings select Add property, choose Disable automatic triggers creation, switch it on and select Test and continue. The setting applies to the whole pipeline;

    2. Create a small table, then add it as an entity as in step 5. Name it parts:

      create table dbo.parts (id int not null primary key, label varchar(30) null)
go
insert dbo.parts values (1, 'bolt')
go

    3. The CDC table is created and no trigger is. Gluesync writes the statements that you must run to the Core Hub log at DEBUG level, after the line Automatic triggers creation disabled, trigger definition will be logged for each table. Each statement follows a line such as Trigger statement for table dbo.parts and INSERT operation:. Run all three on ASE, as a login that owns the table or has sa_role:

      CREATE TRIGGER trigger_dbo_parts_insert
ON dbo.parts
FOR INSERT
AS
BEGIN
   INSERT INTO gluesync_admin.dbo_parts_CDC (TIMESTAMP, OPERATION, id, label)
   SELECT GETDATE(), 'I', id, label
   FROM inserted
END
go
CREATE TRIGGER trigger_dbo_parts_update
ON dbo.parts
FOR UPDATE
AS
BEGIN
   INSERT INTO gluesync_admin.dbo_parts_CDC (TIMESTAMP, OPERATION, id, label)
   SELECT GETDATE(), 'U', id, label
   FROM inserted
END
go
CREATE TRIGGER trigger_dbo_parts_delete
ON dbo.parts
FOR DELETE
AS
BEGIN
   INSERT INTO gluesync_admin.dbo_parts_CDC (TIMESTAMP, OPERATION, id, label)
   SELECT GETDATE(), 'D', id, label
   FROM deleted
END
go

    4. Start the entity with Full copy, then change the table:

      insert dbo.parts values (3, 'screw')
go
update dbo.parts set label = 'bolt-m6' where id = 1
go
delete dbo.parts where id = 2
go
    Expected result

    Before step 3, sysobjects lists the table dbo_parts_CDC and no trigger for parts. After the changes, the target has rows 1 (bolt-m6) and 3 (screw), and the CDC table holds I, I, U and D rows in order.

    Keep the names trigger_<schema>_<table>_insert, _update and _delete, and the column list that the log shows. Gluesync looks for a trigger by name, and a trigger that it does not find is created again as soon as you switch the property off. A change made before the triggers exist is never recorded: the lab lost the rows that it changed between adding the entity and creating the triggers. Create the triggers before you start the entity, or start it with Full copy again afterwards.

    The statements list every column of the table except text, unitext and image.

    When you switch Disable automatic triggers creation off again, Gluesync creates the triggers that are missing for every entity of the pipeline and leaves the existing ones alone.

    Remove an entity and its objects

    UI + SQL

    Deleting an entity removes what Gluesync added for its table. Pausing it does not.

    1. Pause the entity. On its row, open the menu and select Delete entity, then Delete;

    2. List the objects of the table again:

      select o.name, o.type from sysobjects o where o.name like '%parts%'
go
    Expected result

    Only the table parts is left. The CDC table and the three triggers are gone, also when you created the triggers by hand: Gluesync drops them by name.

    Gluesync drops only the triggers named trigger_<schema>_<table>_<operation>. A trigger of your own on the same table stays and keeps firing.

    Existing triggers on the table

    The reference says that ASE allows only one trigger for each operation on a table. The lab server did not enforce that. On ASE 16.0 SP02 PL02, a table dbo.customers with its own insert trigger (customers_audit) got the three triggers of Gluesync, and sp_helptrigger listed both insert triggers:

    use demo
go
sp_helptrigger 'dbo.customers'
go
     Trigger Name                 Firing Action   Firing Order   Enabled Status
 trigger_dbo_customers_delete delete          no order       enabled
 customers_audit              insert          no order       enabled
 trigger_dbo_customers_insert insert          no order       enabled
 trigger_dbo_customers_update update          no order       enabled

    An insert fired both: the audit table of the customer trigger got a row, and so did the CDC table. Run sp_helptrigger on your own server before you add an entity to a table that has triggers. If your version replaces the existing trigger of the same operation, create the Gluesync triggers by hand as in step 11 and merge the statements with yours.

    Retention and cleanup

    • The CDC table is a ledger. The agent reads it in order of GS_ID and never deletes what it reads. Only the cleaner deletes rows;

    • The cleaner deletes by age. It runs when the agent starts and every hour, and it deletes the rows older than Source change retention in hours (24 by default) in every CDC table of the pipeline. The reference describes this setting in days: in the Control Plane, and in the cleaner, the unit is hours;

    • The size of the table is your backlog plus the retention. A table that keeps growing means that nothing reads it (an entity on hold, paused or in error) or that the retention is long or 0. Check the state of the entity with Diagnose a stalled pipeline or entity;

    • Stopping an entity does not remove anything. The triggers go on writing to the CDC table until you delete the entity;

    • Retire the pipeline from the Control Plane. Delete the entities, as in step 12. If you drop the objects by hand, drop the triggers first, because they refer to the CDC table: a trigger whose CDC table is gone makes every insert, update or delete on the source table fail;

    • Size the log. Every change is written twice in the transaction log of the database. If you meet error 1105 on syslogs, see the troubleshooting table.

    Data types

    The agent maps the types as the Field mapping dialog lists them. This table was captured with a PostgreSQL target, from a table with these columns, and every value was checked on the target after a snapshot and after a live insert.

    ASE type PostgreSQL type proposed Note

    int, smallint, bigint

    int, smallint, bigint

    numeric(10,3)

    numeric with precision and scale

    The precision and the scale are kept

    money

    decimal(19,4)

    money is read as 19.9900

    float

    float

    Read as a 32-bit float: 0.1 arrived as 0.10000000149011612. Use numeric for exact values. real is read as a decimal

    char(8), varchar(20)

    char(8), varchar(20)

    The length is kept. A char value arrives padded with spaces (`abc `). Turn on Trim text values to remove them

    bit

    boolean

    true and false

    binary(4), varbinary(8)

    bytea

    0xCAFEBABE arrived as \xcafebabe

    date

    date

    time

    time

    The dialog requires a precision

    datetime

    timestamp without time zone

    The dialog requires a precision

    text, unitext, image

    text, bytea

    The dialog requires a length for text. These columns are not in the CDC table. The agent reads them from the table when it processes the change, so an event of a deleted row carries no value for them

    The tables that you replicate need a primary key. If yours has none, follow Choose keys for tables without a primary key.

    Speed and concurrency settings

    The agent reads the CDC table with one query ordered by GS_ID, which the clustered index of the table serves, so there is no polling interval to tune. The settings that matter are the speed of the snapshot, the size of the connection pool and how many entities run at once.

    Setting Default When to change it

    Speed in the start dialog (Standard, Faster, Custom)

    Standard: 1 read, 2 writers

    Choose Faster or Custom for a large table, when ASE and the target can take more load. See Speed up snapshots and explain row count differences

    Max connections count (Base settings)

    25

    Raise it when many entities run at once. Each connection is a session on ASE

    Max Concurrency (0 means no limitation) (Advanced settings)

    0: no limit

    Limit how many entities run at once, to protect ASE or the target

    Max Snapshot Concurrency (0 means no limitation) (Advanced settings)

    0: no limit

    Limit how many snapshots run at the same time

    Trim text values (Advanced settings)

    Off

    Remove trailing spaces of char values

    The ASE agent does not support bulk load. Read Tune replication throughput for the settings that apply to every target.

    Verify the result

    You have set up the triggers source if:

    • The login has sa_role and is a user of the source database, and sp_displaylogin shows it;

    • sysobjects lists a CDC table and three triggers for each replicated table, with the names that this guide gives;

    • The row count of the target equals the source after the snapshot;

    • A CDC insert, update and delete reach the target, and a rolled back transaction does not;

    • You know that a change of a primary key and a truncate table are not replicated;

    • You chose a retention that is longer than the longest time an entity can stay paused or in error;

    • The log of the database has room for the writes of the triggers.

    Troubleshooting

    Problem Possible cause What to do

    The entity could not be saved. You must have any of the following role(s) to execute this command/procedure: 'sa_role' . Please contact a user with the appropriate role for help.

    The login of the connection has no sa_role, even if it can select from the table and create tables. Creating the triggers on a table that dbo owns needs the role. Without it, ASE answers CREATE TRIGGER permission denied on object <table>, database <db>, owner dbo

    Run grant role sa_role to <login> and add the login as a user of the database, as in step 2. If your DBA does not allow sa_role, set Disable automatic triggers creation and follow step 11: the login still needs to create the CDC table

    User name <login> does not exist in sysusers.

    The login is an alias of dbo (sp_addalias) and not a user of the database. Gluesync creates the CDC table with the login name as the owner

    Drop the alias (sp_dropalias <login>) and run sp_adduser <login>, <login> in the source database

    ASE answers Server user id <n> is not a valid user in database '<db>' when the login connects (seen in isql)

    The login exists on the server, but it is not a user of the source database

    Run sp_adduser <login>, <login> in the source database

    Permission denied, database <db>, owner dbo. You need the following permission(s) to run this command: CREATE TABLE.

    The login cannot create the CDC table

    Give the login sa_role

    A notification <login>.<schema>_<table>_CDC not found. Specify owner.objectname or use sp_help to check whether the object exists appears right after the first entity

    The cleaner ran once before Gluesync created the CDC table

    Check that the CDC table exists with the query of step 5. If it does, ignore the notification: the next pass purges the table. If the table is missing, delete the entity and add it again

    ASE refuses writes with error 1105: Can’t allocate space for object 'syslogs' in database '<db>' because 'logsegment' segment is full/has no free extents

    The transaction log is full. The triggers add a second insert to every change, and the CDC tables grow until the cleaner runs

    Dump the transaction log, as the message says, or extend the database with alter database <db> on <device> = <size in MB>. Size the log for the writes of the triggers and for the longest backlog

    The target keeps a row after its primary key was changed on the source

    A trigger records an update of the key as one U row with the new key

    Delete the old row on the target, or start the entity again with Full copy and Empty the table first. Avoid changing keys

    The target keeps its rows after truncate table on the source

    ASE runs no delete trigger for a truncate

    Empty the target table yourself, or start the entity again with Full copy and Empty the table first

    Inserts, updates or deletes on a replicated table fail with Msg 208 …​ Procedure 'trigger_<schema>_<table>_<operation>' …​ <login>.<schema>_<table>_CDC not found

    Someone dropped the CDC table by hand. The triggers still refer to it, so every change of the source table fails

    Drop the three triggers of the table. To keep replicating, delete the entity in the Control Plane (it drops what is left), add it again and start it with Full copy

    Changes are missing for a table with hand-made triggers

    The triggers did not exist yet, have other names than trigger_<schema>_<table>_<operation>, or write to another CDC table

    Compare them with the statements of the Core Hub log, then start the entity with Full copy

    The CDC table keeps growing

    Nothing reads it: the entity is on hold, paused or in error. Or the retention is long or 0

    Start the entity, check its error, and set Source change retention in hours to a value that fits your backlog. See Diagnose a stalled pipeline or entity

    The log shows Purge CDC data for table: <table> is set to 0

    Source change retention in hours is 0, which switches the cleaner off

    Set a value of 1 hour or more, or delete the rows yourself

    float values on the target differ in the last digits

    The agent reads an ASE float as a 32-bit float

    Use numeric or decimal for values that must be exact

    char values on the target end with spaces

    ASE pads a char column to its length

    Turn on Trim text values in Advanced settings, or use varchar

    Related guides

    Contact MOLO17 Support if you need assistance with the SAP ASE triggers agent.