Search docs.molo17.com
    Configuration guide Connectors

    Set up SAP SQL Anywhere as a source and a target

    SAP SQL Anywhere has no CDC feature that Gluesync can switch on. The agent reads the transaction log with dbtran, a utility of the SAP client, and the SAP JDBC driver comes from the same client. Gluesync cannot ship either, so you provide the client and give the database user one privilege.

    In this guide

    You will create the database users for a source and a target, make the SAP SQL Anywhere 17 client available to Core Hub, build a pipeline from a source database to a target database, run a snapshot and CDC, check the rows on the target, turn on the bulk path and fix the errors that you are most likely to meet.

    Quick information

    Goal

    Replicate a SAP SQL Anywhere 17 database to another one, with a full copy first and then every committed insert, update and delete

    Audience

    Database administrators and Gluesync users who own a SQL Anywhere source or target

    Level

    Intermediate

    Time required

    About 45 minutes

    Area

    SQL Anywhere → Core Hub host → Control Plane → Data pipelines

    Applies to

    Gluesync 2.3

    Before you begin

    Make sure that:

    1. You have two SAP SQL Anywhere 17 databases that Core Hub can reach, on their database port (default 2638). The guide calls them the source and the target. The examples use the source table DBA.orders and the target schema target_schema;

    2. Core Hub runs on Linux x86_64. SAP ships its client for that platform only. On Apple silicon run Core Hub as linux/amd64;

    3. You have the SAP SQL Anywhere 17 client, for example from the Developer Edition or from your server installation. Gluesync does not distribute it;

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

    5. You can run SQL as DBA on both databases, for example with dbisql.

    The SAP SQL Anywhere agent does not change the source database. It creates no trigger, no shadow table and no other object. It reads the catalog, the tables and the transaction log.

    Guide steps

    Create the source user and a sample table

    SQL

    Skip the table if you already have tables to replicate. The agent needs the system privilege BACKUP DATABASE and SELECT on each table. SYS_RUN_REPLICATION_ROLE alone is not enough for dbtran.

    1. Connect to the source database as DBA;

    2. Run the script.

    CREATE USER gluesync IDENTIFIED BY '<password>';
GRANT BACKUP DATABASE TO gluesync;

CREATE TABLE DBA.orders (
  id       INTEGER NOT NULL PRIMARY KEY,
  customer NVARCHAR(100),
  amount   NUMERIC(12,2),
  created  TIMESTAMP DEFAULT CURRENT TIMESTAMP
);

INSERT INTO DBA.orders (id, customer, amount)
  SELECT row_num, 'customer ' || row_num, row_num * 1.5 FROM sa_rowgenerator(1, 1000);
COMMIT;

GRANT SELECT ON DBA.orders TO gluesync;

    Grant SELECT for every table to replicate, or grant SELECT ANY TABLE.

    Expected result

    The user gluesync exists and can read DBA.orders. The table holds 1,000 rows.

    Create the target user and schema

    SQL

    The target agent creates a missing table, and it truncates the table before a snapshot unless you turn that off. The user therefore needs the privileges to create, alter and truncate tables. The simplest way is to let the user own the target schema. The script below uses ANY TABLE privileges instead.

    1. Connect to the target database as DBA;

    2. Run the script.

    CREATE USER gluesync IDENTIFIED BY '<password>';
GRANT CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, TRUNCATE ANY TABLE TO gluesync;
GRANT SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO gluesync;
GRANT CREATE ANY INDEX, ALTER ANY INDEX, DROP ANY INDEX, CREATE ANY OBJECT TO gluesync;

-- A user without a password owns the schema: nobody can sign in as it
CREATE USER target_schema;
    Expected result

    The target database has the user gluesync and the empty schema target_schema.

    Make the SAP client available to Core Hub

    CLI

    The agent loads the client at run time. It looks for the folder in this order: the SQL Anywhere client folder connection property of the agent, the SQLANY17 environment variable of Core Hub, then /opt/sqlanywhere17. A full installation has the right layout. The agent needs only these parts:

    /opt/sqlanywhere17
├── bin64/dbtran                  (source agent only)
├── java/sajdbc4.jar
├── lib64/libdbjdbc17.so and the other lib*.so files
├── res/dblgen17.res
└── SAP_SQL_Anywhere_version.txt  (optional)

    When Core Hub runs in a container, mount the folder read-only.

    services:
  gluesync-corehub:
    platform: linux/amd64
    volumes:
      - /opt/sqlanywhere17:/opt/sqlanywhere17:ro

    Keep the .so.1 files that sit next to the libraries. A Core Hub process loads one client folder, so restart Core Hub after you change it.

    Expected result

    Core Hub can read /opt/sqlanywhere17/bin64/dbtran and /opt/sqlanywhere17/java/sajdbc4.jar. If the folder is incomplete, Test and continue in the pipeline wizard fails with a message that lists the missing files, for example The SQL Anywhere client folder /opt/sqlanywhere17 is incomplete: missing java/sajdbc4.jar, lib64/libdbjdbc17.so, res/dblgen17.res, lib64/*.so.

    Mount the log directory if you rename the log

    CLI

    Skip this step if you never rename the transaction log. If you do, dbtran runs in Core Hub and finds the renamed logs in the directory of the server’s own log, at the path the server reports.

    1. On the source, run SELECT DB_PROPERTY('LogName'). The renamed logs are next to that file;

    2. Mount that directory into Core Hub at the same path.

        volumes:
      - /opt/sqlanywhere17:/opt/sqlanywhere17:ro
      - /var/lib/sqlanywhere/log:/var/lib/sqlanywhere/log:ro
    Expected result

    With the mount, the agent reads a log that was renamed before it had read it. Without it, a rename that skips unread commits is a gap (see the troubleshooting table).

    Create the pipeline with the source and the target

    UI

    1. Open Data pipelines;

    2. Select the + button. On a Core Hub without pipelines, select Create your first pipeline. The Agents setup wizard opens on 1. Setup source;

    3. In Search agents, type SQL Anywhere and select the tile SAP SQL Anywhere agent. The connection form opens on the tab Base settings;

    4. Enter Host name / Address, Port (default 2638), Database name (the DBN of the connection), Username (gluesync) and Password of the source;

    5. Optional: under Advanced connection properties, select Add property and pick SQL Anywhere client folder, SQL Anywhere server name (for a host that runs several servers) or Use connection pool. Turn on Enable TLS under Security if the server needs it. The tab Advanced settings holds the settings of the agent, such as the polling interval of the log;

    6. Select Test and continue. The source appears as a card with a green check mark. Select Next;

    7. On 2. Setup target(s), select the tile SAP SQL Anywhere agent and enter the connection of the target in the same way;

    8. Select Test and continue, then Next;

    9. On 3. Ready, enter SQL Anywhere demo in Pipeline name and select Finish.

    The same tile serves as source and as target. To script this step, use the REST calls in Setup via Rest APIs.
    Expected result

    Each side shows a card with a green check mark and the user and address, for example gluesync@<host>:2638. The client folder loaded and the user could connect. An incomplete client folder fails here with the list of missing files. The pipeline appears in the Healthy group.

    Add the entity

    UI

    1. In the pipeline, select Entities. The page Add entities opens with the Object browser on the left;

    2. Expand the schema DBA and tick orders. The Field mapping dialog opens;

    3. In Target Schema, clear the prefilled DBA, type target_schema and select it from the list;

    4. In Target Table, type orders and select Create table "orders". The banner New table orders will be created on confirm appears and every field of the source is linked;

    5. Select Confirm. The dialog Create table orders shows the CREATE TABLE statement. Select Create table orders to run it on the target;

    6. Select Confirm and continue.

    Field mapping dialog with DBA.orders on the source and the new table target_schema.orders on the target
    Figure 1. Field mapping of orders: every source field is linked to a field of the new target table.

    The default tag next to the entity is its group for the wizard. The default types of the new table are in the data type section below.

    Expected result

    The entity orders is in the Hold group. On the target, target_schema.orders exists with the columns id integer (primary key), customer nvarchar(100), amount numeric(12,2) and created timestamp.

    Run the snapshot and start CDC

    UI

    1. In the Actions column of the entity, select the play icon. The dialog Sync changes (CDC) opens;

    2. In Start from, keep Full copy (marked Recommended for the first start);

    3. In Data already in the target, keep Keep and merge;

    4. Leave Speed on Standard and select Start entity.

    Data pipelines page with the pipeline SQL Anywhere demo healthy and the entity orders in the Active group
    Figure 2. The pipeline with the entity orders in the Active group.
    Expected result

    The entity moves to Active and the pipeline stays Healthy. The entity page reports the snapshot of 1,000 rows as 100% done, and the table on the target holds 1,000 rows.

    Verify the snapshot and the three change types

    SQL

    1. On the target, count the rows;

    2. On the source, run an insert, an update, a primary key change and a delete in one transaction;

    3. Wait a few seconds and read the same rows on the target.

    -- Target
SELECT COUNT(*) FROM target_schema.orders;   -- 1000

-- Source
INSERT INTO DBA.orders (id, customer, amount) VALUES (2001, 'cdc insert', 10.25);
UPDATE DBA.orders SET amount = 99.99 WHERE id = 5;
UPDATE DBA.orders SET id = 3001 WHERE id = 6;
DELETE FROM DBA.orders WHERE id = 7;
COMMIT;

-- Target
SELECT id, customer, amount FROM target_schema.orders WHERE id IN (5, 6, 7, 2001, 3001);
    Expected result

    The target holds 1,000 rows again (one added, one deleted). The row 5 has the amount 99.99, the row 2001 exists, the row 3001 exists and the rows 6 and 7 are gone. A change of the primary key moves the row to the new key.

    In the Control Plane, open the entity (the arrow at the end of its row) and, under Inspect tables, select Count both. The counts match when CDC has caught up, and the page shows 0 with Match between the two sides.

    Turn on the bulk path for large tables

    UI

    The bulk path loads each batch with one statement that reads the rows from OPENSTRING. It is off by default. Two settings of the target entity turn it on, one for the snapshot and one for CDC. They are independent.

    1. On the pipeline page, select the three dots of the entity and then Edit settings;

    2. Open Fine tuning. Under Bulk load settings, tick Bulk for Snapshot, Bulk for CDC, or both. See Write strategies for where they are;

    3. Close the dialog and select Confirm and continue;

    4. Pause the entity, select the play icon again and start it with Full copy to use the setting on the snapshot.

    Settings dialog of DBA.orders on Fine tuning with Bulk for CDC and Bulk for Snapshot ticked
    Figure 3. Bulk load settings in the settings of the entity.

    You can also choose the write method when you start the entity: in the dialog Sync changes (CDC) select Custom under Speed and pick Bulk load under During the copy, During live changes or both.

    Entity page of orders with the Bulk load badge
    Figure 4. Count both after the bulk load: the entity page shows the Bulk load badge and the two counts match.
    Expected result

    The entity page shows the Bulk load badge. At debug level the Core Hub log has one line per batch, such as Bulk write of 1000 operations into target_schema.orders took 45ms. Changes made on the source afterwards are written with the same bulk path.

    Data type notes

    When both sides are SQL Anywhere, the Field mapping dialog proposes for every column the type of the source. The source agent reads the type of each column from the catalog.

    Source type Proposed on the target

    NVARCHAR(n), VARCHAR(n), LONG NVARCHAR

    The same type and length

    NUMERIC(p, s), UNSIGNED BIGINT

    The same type, with the precision and scale of the source (UNSIGNED BIGINT is unsigned bigint with precision 20 and scale 0)

    TIMESTAMP, TIMESTAMP WITH TIME ZONE

    The same type with precision 6

    LONG BINARY

    long binary

    TINYINT, SMALLINT, UNSIGNED SMALLINT, BIGINT, DOUBLE, DATE

    The same type

    You can change the proposed type of a column in the dialog before you create the table. The types that a target table can use are listed in Target tables and data types.

    Troubleshooting

    Symptom Cause Fix

    Test and continue fails with Failed to save and the message The SQL Anywhere client folder …​ is incomplete: missing …​

    The client folder is incomplete, not mounted or not the one the agent looks for

    Check the mount and the file list in the step above. Restart Core Hub after you change the client folder

    After you change SQL Anywhere client folder on a running Core Hub, the save fails with The SQL Anywhere client was loaded from …​ and cannot be loaded from …​ in the same process: restart Core Hub to change it

    A Core Hub process loads one client folder. The connection is saved with the new value even though the agent refused it

    Restart Core Hub, or put the previous value back and select Test and continue again

    Test and continue fails with SAP ships the SQL Anywhere client for Linux x86_64 only, and this agent runs on Linux aarch64

    Core Hub runs on another architecture or operating system

    Run Core Hub as linux/amd64 on Linux

    The entity goes to the Error group and the notification SQL Anywhere transaction log cannot be read says that dbtran was refused. The pipeline itself still shows Healthy

    The user lacks BACKUP DATABASE

    Run GRANT BACKUP DATABASE TO gluesync;. The agent does not pick the grant up by itself, and pausing and starting the entity does not help. Open the pipeline menu, select Edit source and select Test and continue without changing anything. The entity returns to Active and delivers the changes that waited, with no restart of Core Hub

    The notification says the log was renamed or truncated past the position of the agent and lists offsets

    A BACKUP DATABASE …​ TRANSACTION LOG RENAME ran with commits that the agent had not read, and Core Hub could not read the renamed logs. The changes in between are not recoverable from the log

    If the renamed logs are still on the server, make them readable in Core Hub at the same path (see the step above) and start the entity again with Where it stopped: the agent then reads the renamed log and loses nothing. Without them, a plain start keeps failing with the same error. Start the entity with Full copy

    Changes arrive late and the lag grows

    dbtran reads the whole active log on every run that finds a new commit

    Rename the active log on a schedule so it stays small. Look at the duration of each dbtran run in the Core Hub log at debug level

    A warning asks for a re-snapshot of a table

    The log shows an ALTER TABLE, DROP TABLE or TRUNCATE TABLE of a tracked table. These are not replicated

    Snapshot the entity again

    The target refuses a row with SQL Anywhere error -193 or -196

    A row with that key is already on the target

    Choose the On duplicate key setting of the entity (Upsert, Skip or Fail) in Edit settings, Fine tuning. With Fail, the entity stops with the SQL Anywhere error -193. See Insert conflict strategy

    The agent logs connection errors -85, -101 or -308, or lock errors -210 or -306

    A connection was lost or a row is locked

    No action. The agent retries these statements

    Rows written by one pipeline come back through the other in a two-way setup

    Both pipelines read each other’s writes

    Give the writing agent its own database user and list it in excludedUsers of the agent that reads the same database

    Errors of the agent appear in the notification panel (the bell) and in the Notifications hub, with the full message when you expand a row.

    Notifications hub with an error row SQL Anywhere transaction log cannot be read expanded to the full message about the system privilege BACKUP DATABASE
    Figure 5. The Notifications hub with the message of a user that lacks BACKUP DATABASE.

    Related guides

    Contact MOLO17 Support if you need assistance with SAP SQL Anywhere.