SAP SQL Anywhere agent

Target SAP SQL Anywhere

Prerequisites

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

SAP SQL Anywhere 17.
The SAP SQL Anywhere 17 client available to Core Hub, on Linux x86_64. The target needs the SAP JDBC driver and its libraries; dbtran is only used by the source. See The SAP SQL Anywhere client for what to install and how to mount it into the Core Hub container.
A database user that can write to the target tables. Gluesync creates a target table when it does not exist, and truncates the table before a snapshot unless you turn that off, so the user needs privileges to create, alter and truncate tables as well as to SELECT, INSERT, UPDATE and DELETE on them. The simplest way is to let the user own the target schema. Otherwise grant the ANY TABLE system privileges, for example:

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;

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 your target database (the DBN of the connection);
Username: Username with read & write access to the target tables;
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, and the trust store path points to the certificate file the server certificate is checked against.

Custom properties

SQL Anywhere client folder: (optional, defaults to the SQLANY17 environment variable, then to /opt/sqlanywhere17) Folder of the SQL Anywhere 17 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. Turning it off lowers the performance.

Specific configuration

Disable truncate on target table before snapshot inserts: (optional, defaults to false) When false, the target table is emptied (TRUNCATE) before a snapshot writes into it. When true, the snapshot writes over the data that is already there. See On duplicate key for what happens to a row that is already on the target.

For each entity, the SQL pre- and post-commands for snapshot and CDC run before and after the task, as on the other SQL targets.

Writing to SQL Anywhere

Changes are written in batches, in the order of the stream:

Inserts are INSERT statements;
Updates and upserts are MERGE statements that update the row with the same key and insert it when it is not there. A row that carries a decimal of more than 30 digits is written one row at a time, with an UPDATE by key and, when it matches nothing, an INSERT, because the one-row source of a MERGE cuts a decimal wider than that;
Deletes match the key of the row, and a key that is NULL is matched with IS NULL;
Truncate is TRUNCATE TABLE.

A row that the target refuses because of a duplicate key (SQL Anywhere errors -193 and -196) is handled by the On duplicate key setting of the entity. See Insert conflict strategy.

Statements that fail because a connection was lost (-85, -101, -308) or because a row is locked (-210, -306) are retried.

Bulk operations

For the highest throughput, the agent can load the rows of a batch with a single statement. Two flags of the entity turn this on, and each is independent of the other:

Use bulk operations while snapshot (useBulkOperationsWhileSnapshot, defaults to false): snapshot batches are bulk loaded;
Use bulk operations during CDC (useBulkOperationsDuringCDC, defaults to false): CDC batches are bulk loaded.

See Write strategies for how to set them.

The rows of a batch travel to the server as one text parameter, in chunks of up to 8 MB, and the statement reads them with OPENSTRING(VALUE ?):

A batch with nothing that can already be on the target (a snapshot, or new rows) is written with INSERT … SELECT from OPENSTRING;
Other batches are written with MERGE from OPENSTRING, which updates the rows that are there and inserts the others. An update stays an UPDATE of the row, so triggers on the target table behave as they do with the plain batches;
Deleted keys are removed with MERGE … WHEN MATCHED THEN DELETE;
The statements run in the transaction of the write, are logged row by row (a table that is written in bulk can still be the source of another SQL Anywhere agent) and commit together. No staging table is created.

A batch that cannot be written in bulk (for example a NaN or an infinity in a FLOAT or DOUBLE value, which the text format cannot carry), or that fails, is rolled back and written again with the plain batch statements, so a row is never lost to the bulk path.

Target tables and data types

Gluesync creates a target table when it does not exist, with the columns of the source and the default types below. A table you create yourself is written as it is, with the types it has. The last column lists the types you can choose for a column of that Gluesync type.

Gluesync type Default SQL Anywhere type Supported types

Gluesync type	Default SQL Anywhere type	Supported types
`STRING`	`NVARCHAR(n)`	`CHAR`, `VARCHAR`, `LONG VARCHAR`, `TEXT`, `NCHAR`, `NVARCHAR`, `LONG NVARCHAR`, `NTEXT`, `XML`, `UNIQUEIDENTIFIER`, `UNIQUEIDENTIFIERSTR`, `SYSNAME`, `VARBIT`, `LONG VARBIT`
`BYTE_ARRAY`	`LONG BINARY`	`BINARY`, `VARBINARY`, `LONG BINARY`, `IMAGE`, `ST_GEOMETRY`
`BOOLEAN`	`BIT`	`BIT`
`SHORT`	`SMALLINT`	`SMALLINT`, `TINYINT`
`INT`	`INTEGER`	`INTEGER`, `UNSIGNED SMALLINT`
`LONG`	`BIGINT`	`BIGINT`, `UNSIGNED INT`
`BIG_DECIMAL`	`NUMERIC(p, s)`	`NUMERIC`, `DECIMAL`, `MONEY`, `SMALLMONEY`, `UNSIGNED BIGINT`
`FLOAT`	`REAL`	`REAL`, `FLOAT`
`DOUBLE`	`DOUBLE`	`DOUBLE`
`LOCAL_DATE`	`DATE`	`DATE`
`LOCAL_TIME`	`TIME`	`TIME`
`LOCAL_DATE_TIME`	`TIMESTAMP`	`TIMESTAMP`, `DATETIME`, `SMALLDATETIME`
`OFFSET_DATE_TIME`	`TIMESTAMP WITH TIME ZONE`	`TIMESTAMP WITH TIME ZONE`, `DATETIMEOFFSET`
`MAP`, `ARRAY`	`LONG NVARCHAR`	`VARCHAR`, `LONG VARCHAR`, `LONG NVARCHAR` (stored as text)

STRING

NVARCHAR(n)

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

BYTE_ARRAY

LONG BINARY

BINARY, VARBINARY, LONG BINARY, IMAGE, ST_GEOMETRY

BOOLEAN

BIT

SHORT

SMALLINT

SMALLINT, TINYINT

INT

INTEGER

INTEGER, UNSIGNED SMALLINT

LONG

BIGINT

BIGINT, UNSIGNED INT

BIG_DECIMAL

NUMERIC(p, s)

NUMERIC, DECIMAL, MONEY, SMALLMONEY, UNSIGNED BIGINT

FLOAT

REAL

REAL, FLOAT

DOUBLE

LOCAL_DATE

DATE

LOCAL_TIME

TIME

LOCAL_DATE_TIME

TIMESTAMP

TIMESTAMP, DATETIME, SMALLDATETIME

OFFSET_DATE_TIME

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE, DATETIMEOFFSET

MAP, ARRAY

LONG NVARCHAR

VARCHAR, LONG VARCHAR, LONG NVARCHAR (stored as text)

Notes on the types that Gluesync creates:

Text. The default is NVARCHAR(n) because it counts characters: on a UTF-8 database VARCHAR(n) counts bytes, and a text with multibyte characters would not fit. n is the length of the source column, up to 32767. A text that is longer than that, or that has no known length, becomes LONG NVARCHAR (LONG VARCHAR when you choose VARCHAR or CHAR). The same rule turns a wide BINARY or VARBINARY into LONG BINARY and a wide VARBIT into LONG VARBIT;
Decimals. NUMERIC(p, s) takes the precision and scale of the source, up to precision 127. When the source gives none, the column is NUMERIC(38, 10). A source UNSIGNED BIGINT is a BIG_DECIMAL of precision 20 and scale 0, so the target column is NUMERIC(20, 0);
Geometry. ST_GEOMETRY is carried as bytes in the EWKB format (the PostGIS flavor of WKB that includes the SRID), and a BYTE_ARRAY written into an ST_GEOMETRY column is read by the server as a geometry;
Bit strings. VARBIT and LONG VARBIT are carried as a text of 0 and 1 (10101100);
Narrow integers. TINYINT holds 0 to 255 and UNSIGNED SMALLINT holds 0 to 65,535: a value outside the range fails on write;
Dates and times. TIMESTAMP, TIME and the time zone type keep microseconds. Temporal values are sent as text in the form the server reads back unchanged, so a local time that does not exist in the time zone of Core Hub (the hour a clock moves forward) is not shifted;
Length. The server judges the length of a value against the column it is written to, so a value that is too long for a sized text or binary column is refused instead of being cut.

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": "TARGET",
    "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": {
      "disableTruncateOnSnapshot": false
    }
}'

Notes

The agent runs on Core Hub for Linux x86_64 only, because SAP provides the SQL Anywhere client for that platform only;
The SQL Anywhere client is not part of Gluesync. See The SAP SQL Anywhere client;
Query Studio is available on this agent.