Reconstruction

Capture SAP ASE changes through a log broker with nothing enabled on the database, completing partial updates from data pages the broker serves.

Reconstruction reads the SAP ASE transaction log through the log broker, like DirectLog, but it needs no table marking and nothing enabled on the database. Use it when the tables can't be marked for replication or have text, unitext or image columns, and the target may converge to the source's current state rather than replay every intermediate version of a row.

How it works

The log broker on the ASE host serves the database's log, data and LOB devices, read-only, to the agent over TLS. The agent never opens a database file: every page it needs comes through the broker. Inserts and deletes are exact, because ASE logs the whole row.

ASE logs an update to an unmarked table only as the changed bytes, so the agent completes the row from its current data page, or reads the current row by its key over SQL. It may first run checkpoint in the source database, so the pages on disk are current. Because the row comes from its current state, the target converges to the source. A change the agent can't complete stops the pipeline; it is never dropped.

How Reconstruction holds the log

Like DirectLog, Reconstruction 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). In the sp_helpdevice output, note the physical names of every device the database uses: each must be a file on a file system, and the broker needs their folders.

   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. If the login won't be the database owner, also grant oper_role, so it can run checkpoint.

   use master
   go
   sp_addlogin <login>, '<password>', <database>
   go
   grant role replication_role to <login>
   go
   -- only when the login isn't the database owner:
   grant role oper_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 folders that hold the database's log, data and LOB device files. 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 select Reconstruction 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 every device of the database. Then choose your tables on the Tables step.

Every start checks the requirements again and names any that are unmet.

Encrypted data

• Database encryption: refused at start. Reconstruction reads the devices 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 same as DirectLog. With Require (default), the agent 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 uses SSL only when that file is set, and otherwise connects without encryption. See DirectLog: Encrypted data.

Pros and cons

Pros

• Needs no table marking, no RepAgent and nothing enabled on the database.
• The agent never opens database files: the broker serves every page it reads.
• Captures text, unitext and image values in full, which DirectLog refuses.
• Runs as a source monitor, with no target, which DirectLog can't.
• Deletes carry the full deleted row.

Cons

• The target converges to the source: an update to a row not seen since capture started carries the row's current value, not every intermediate version.
• Slower than DirectLog: updates wait for data-page reads, and sometimes for a checkpoint in the source database or a keyed read over SQL.
• The broker must serve the data and LOB devices, which hold your table data, not only the log.
• Holds the log while the pipeline runs, so the log keeps growing.
• Needs a log broker on the ASE host and the one supported ASE build.

Limitations

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

• Tables with row, page or LOB compression, or that contain compressed data.
• Data-pages and data-rows locked tables. DirectLog captures data-rows locked tables.
• 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.

Intermediate versions. When a row changes several times before the agent reads it, an earlier version can carry a later value. The target converges to the source rather than replaying every intermediate state.

Large values. A text, unitext or image value whose page chain is longer than 4,096 pages is refused. See Large values and LOBs.

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 Reconstruction can't decode stops capture rather than being skipped.

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.

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, or the related message ASE native log page size <size> is unapproved. reports a page size other than 16 KB. SQL Flow reads the log and page formats of one ASE build only.

Fix: run the source on the supported build, with a 16 KB logical page size.

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. Reconstruction reads the log with the same reader as DirectLog, so the message names DirectLog.

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.

Device is a raw device

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

Cause: one of the database's devices 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 one of the database's device files. Reconstruction needs the data and LOB devices as well as the log device.

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.

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), or can't run checkpoint in the source database. A role that's granted but not active in the session counts as missing.

Fix: grant what the message lists (step 5 of Setup), confirm the roles with select show_role() as the capture login, then start the pipeline again.

Compressed storage is not decoded

Sybase reconstruction 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. Reconstruction 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.

LOB page chain too long

LOB chain exceeded 4096 pages; refusing to walk a runaway/corrupt chain.

Cause: a text, unitext or image value spans more than 4,096 pages, or its page chain is damaged.

Fix: remove the table from the pipeline, or have a DBA check the table with dbcc checktable.

Unsupported server character set

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

Cause: a selected table has char, varchar, nchar, nvarchar or text 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
• DirectLog, which captures every change exactly
• Log brokers
• Lag, spool and log retention
• Initial load