DirectLog

Capture every SAP ASE change exactly by reading the log device through a log broker on the ASE host, with the tables marked for replication.

DirectLog reads the SAP ASE transaction log straight from the database's log device, through a log broker on the ASE host, and decodes every change itself. It is the wizard's default for Sybase. Use it when the selected tables may be marked for replication and have no text, unitext or image columns: it is exact and the fastest Sybase mode.

How it works

The log broker on the ASE host reads the database's log device, read-only, and serves its pages to the agent over TLS. The agent decodes the log records into changes, and reads table metadata over its ordinary database connection.

ASE logs complete row images only for a table that is marked for replication, so DirectLog requires every selected table to be marked with sp_setreptable. Inserts, updates and deletes all come from the log: the agent reads no data pages and needs no RepAgent or SAP Replication Server.

How DirectLog holds the log

DirectLog sets the database's secondary truncation point when the pipeline starts, so the log isn't truncated before the agent reads it, and releases it when the pipeline stops cleanly. While the pipeline runs, dump transaction frees no log space past the point where the capture started.

Requirements

Setup

Replace <login> with the capture login, <password> with its password, <database> with the source database, and <owner>.<table> with each table you replicate.

1. Check the server build and page size. The build must be the one in Requirements, and the page size 16384.

   select @@version, @@maxpagesize
   go

2. Check the database's devices. In the sp_helpdb output, the log must sit on its own device (log only), not on a device that holds data and log. In the sp_helpdevice output, note the physical name of the log device: it must be a file on a file system, and the broker needs its folder.

   sp_helpdb <database>
   go
   sp_helpdevice
   go

3. Confirm that no RepAgent runs in the database and that no secondary truncation point is set. is_rep_agent_enabled must return 0, and dbcc gettrunc must show no secondary truncation point.

   use <database>
   go
   select is_rep_agent_enabled(db_id())
   go
   dbcc gettrunc
   go

4. Turn off trunc log on chkpt, then run a checkpoint in the database so the change takes effect:

   use master
   go
   sp_dboption <database>, 'trunc log on chkpt', false
   go
   use <database>
   go
   checkpoint
   go

5. As a login with sso_role, create the capture login and grant it replication_role:

   use master
   go
   sp_addlogin <login>, '<password>', <database>
   go
   grant role replication_role to <login>
   go

6. Add the login to the source database and grant read access to each table:

   use <database>
   go
   sp_adduser <login>
   go
   grant select on <owner>.<table> to <login>
   go

7. Install a log broker on the ASE host. In --allow-paths, include the folder that holds the log device file. Approve the broker under Agents → Brokers.

8. Enable SSL on the ASE listener if it isn't already. With Encryption at Require (default), the agent connects only over SSL and validates the server certificate and host name. See Encrypted data.

9. In the pipeline wizard, choose Sybase on the Source step and keep DirectLog under Capture mode. Enter the host, port, the source database in Database, the capture login, and its password or a secret reference. The wizard looks up the broker registered for the source host.

10. Select Test connection & list tables. The test lists the tables and checks that the broker serves the log device. Then choose the tables on the Tables step and your target on the Target step.

11. On the Prepare step, mark the tables that aren't marked yet:

    • Select Mark now to have the agent mark them with the pipeline login, which needs replication_role.
    • Or open the DBA SQL tab, select Copy DBA SQL, and have a DBA run the script in the source database.

    Then select Check again.

To see which tables are marked, run sp_setreptable with no parameters in the source database. Every start checks the requirements again and names any that are unmet.

Encrypted data

• Database encryption: refused at start. DirectLog reads the log device as stored on disk, which full database encryption encrypts.

• Encrypted columns: a selected table with an encrypted column is refused before the initial load. Leave those tables out of the pipeline.

• Encrypted connections: the connection's Encryption setting decides the transport:

  Setting	What the agent does
  Require (default)	Connects over SSL and validates the server certificate and host name, against the agent host's trust store or the file in Trusted certificate file on agent.
  Prefer	Connects over SSL, validated the same way, when Trusted certificate file on agent is set. Otherwise it connects without encryption, because ASE can't negotiate SSL on a port.

  To trust a private certificate authority without adding it to the agent host's store, put its PEM certificate on the agent host and enter that path in Trusted certificate file on agent. With the CLI, set the connection option sslRootCertificate. See Connections commands.

  The broker connection uses TLS 1.3, or TLS 1.2 where the host lacks it, with a pinned server certificate.

Pros and cons

Pros

• Exact: every insert, update and delete comes from the log, with full before and after images and every intermediate version of a row.
• The fastest Sybase mode: the agent reads the log device as ASE writes it, with lag in milliseconds.
• The broker reads only the log device. ASE runs no dbcc command and no query to find changes.
• The login needs replication_role, not sa_role.
• Captures all-pages locked and data-rows locked tables.

Cons

• Needs a log broker on the ASE host and the one supported ASE build.
• Needs the tables marked for replication, so it can't run beside a RepAgent or another product that marks the tables.
• Holds the log while the pipeline runs, so the log keeps growing.

Limitations

Tables refused at the start. The readiness check refuses these tables by name:

• Tables that aren't marked for replication, and tables another product marked.
• Tables with row, page or LOB compression, or that contain compressed data.
• Tables with a text, unitext or image column. Reconstruction captures these columns.
• Tables without a primary key constraint. A unique index alone isn't used as the key.
• Tables with encrypted columns.
• Tables with a column type SQL Flow can't decode. See Data type support by engine.

One pipeline per database. The secondary truncation point belongs to the whole database, so a second Sybase pipeline on the same database, in either capture mode, is refused.

Undecodable records. A log record DirectLog can't decode stops capture rather than being skipped. No change at or after it is published.

TRUNCATE TABLE. A truncate table of a tracked table is captured in order with the other changes and applied to the target. See Supported changes.

Source monitor. A DirectLog pipeline can't be a source monitor; for one, use Reconstruction.

See Schema changes for DDL handling.

Troubleshooting

Unsupported ASE build

ASE native log layout is unapproved. Supported build: Adaptive Server Enterprise/16.0 SP02 PL02/EBF 25320 SMP/P/x86_64/Enterprise Linux/ase160sp02plx/2492/64-bit/FBO/Sat Nov 21 04:05:39 2015.

Cause: @@version reports another ASE build. SQL Flow reads the log and page formats of one ASE build only.

Fix: run the source on the supported build. No capture mode reads another build.

Unsupported server page size

ASE native log page size <size> is unapproved. Supported build: ...; page size 16384.

Cause: the server's logical page size isn't 16 KB.

Fix: capture from a server built with a 16 KB logical page size. The page size of an ASE server can't be changed after it is created.

Log and data on the same device

ASE loginfo(dbid, 'root_page') returned 0. DirectLog requires a positive authoritative tail page; Mixed Log Data databases return zero because this loginfo option is unsupported. ...

Cause: the database's log shares a device with its data, so ASE can't report where the log ends.

Fix: have a DBA move the log to a device of its own, following SAP's procedure for separating the log from the data, then start again.

Log device is a raw device

Sybase managed broker access requires a finite device length; raw devices reporting length 0 are unsupported.

Cause: a database device is a raw partition or block device, which reports no length.

Fix: keep the database's devices in files on a file system.

Device outside the broker's allowed folders

Discovered log path '<path>' is outside the log broker's allowed folders (<folders>) on host '<host>'. ...

Cause: the broker doesn't serve the folder that holds the log device file.

Fix: rerun SqlFlow.LogBroker install on the ASE host with a new join code from Add broker and --allow-paths including that folder. The broker keeps its identity and approval. See Log brokers.

Device discovery failed

Could not discover the DirectLog log-path candidates for this source: <reason>

Cause: the source test couldn't read the device map from master..sysusages and master..sysdevices, or the database spans more than 64 device files.

Fix: check that the login can read both tables, which every login can by default, and that the reason names no other fault.

Missing role at start

Sybase CDC reader is missing privileges / settings: <missing>.

Cause: the privilege check at start found that the capture login lacks replication_role (or sa_role). A role that's granted but not active in the session counts as missing.

Fix: grant the role (step 5 of Setup), confirm it with select show_role() as the capture login, then start the pipeline again.

RepAgent configured in the database

Marked source has a configured or running RepAgent.

Cause: the database is set up for SAP Replication Server. RepAgent owns the database's secondary truncation point, which DirectLog needs.

Fix: capture from a database that RepAgent doesn't replicate.

Secondary truncation point already set

Marked source LTM point has no durable product-root owner fence.

Cause: another product, or a DBA, set the database's secondary truncation point, and SQL Flow didn't.

Fix: find out what set it. If nothing uses it any more, have a DBA release it with dbcc settrunc('ltm', 'ignore') in the source database, then start again.

Table already marked by another product

Marked source table is already marked without product ownership.

Cause: a selected table was marked for replication outside SQL Flow, for example for SAP Replication Server.

Fix: leave the table out of the pipeline, or capture it with Reconstruction if no other product uses the marking.

Compressed storage is not decoded

Sybase DirectLog cannot capture tracked table '<table>' (objid=<id>): <compression>. Compressed or unknown ASE storage is not decoded.

Cause: the message names the compression, for example row compression or page compression. DirectLog can't decode compressed rows.

Fix: remove compression from the table and rebuild it so existing rows are stored uncompressed, or remove the table from the pipeline.

Unsupported server character set

ASE server character set '<name>' is unsupported; refusing a replacement encoding.

Cause: a selected table has char, varchar, nchar or nvarchar columns, and SQL Flow can't decode the server's character set.

Fix: remove those tables from the pipeline. Supported character sets lists the character sets SQL Flow decodes.

Log truncated past the resume position

Sybase database '<database>': the transaction log was truncated past the capture resume position (page <page>, row <row>); ...

Cause: the log was truncated while the pipeline wasn't holding it, either by the automatic checkpoint with trunc log on chkpt on or by a dump transaction. Changes after the resume position may be gone, so capture refuses to continue.

Fix: reload the pipeline to rerun the initial load, and turn off trunc log on chkpt (step 4 of Setup).

Agent log warns about trunc log on chkpt

[cdc] Sybase database '<database>' has 'trunc log on chkpt' enabled: while no reader is attached, ASE's automatic checkpoint task can truncate the transaction log at any time, ...

Cause: the source database has trunc log on chkpt on. While the pipeline is paused, ASE's checkpoint can truncate log it still needs.

Fix: turn the option off (step 4 of Setup).

See also

• Sybase ASE capture modes
• Reconstruction, which needs no table marking
• Log brokers
• Lag, spool and log retention
• Sybase ASE target