SAP ASE

Source SAP ASE (Sybase ASE) #

The following steps refer the extracted Arcion self-hosted CLI download as the $REPLICANT_HOME directory.

I. Set up connection configuration #

Specify our ASE connection details to Replicant with a connection configuration file. You can find a sample connection configuration file sybasease_src.yaml in the $REPLICANT_HOME/conf/conn directory.

For connecting to ASE, you can choose between two methods for an authenticated connection:

  • Using basic username and password authentication
  • Using SSL

Connect with username and password #

For connecting to SAP ASE with basic username and password authentication, you have the following two options:

You can specify your credentials in plain YAML in the connection configuration file like the following sample:

type: SYBASE_ASE

host: HOSTNAME
port: PORT_NUMBER

database: 'DATABASE_NAME'
username: 'USERNAME'
password: 'PASSWORD'

client-charset: iso_1

max-connections: 20
max-retries: 10
retry-wait-duration-ms: 1000

Replace the following:

  • HOSTNAME: hostname of the SAP ASE server
  • PORT_NUMBER: port number of the SAP ASE server
  • DATABASE: the name of the SAP ASE database to connect to
  • USERNAME: the username of the DATABASE user
  • PASSWORD: the password associated with USERNAME

Feel free to change the following parameter values as you need:

  • max-connections: the maximum number of connections Replicant opens in AlloyDB
  • max-retries: number of times Replicant retries a failed operation
  • retry-wait-duration-ms: duration in milliseconds Replicant waits between each retry of a failed operation.
  • client-charset: the JDBC character set name.

If you want to use the bcp utility to extract data from your source ASE, you need specify some additional parameters in the connection configuration file. For more information, see Use bcp Utility for Extraction.

You can store your connection credentials in a secrets management service and tell Replicant to retrieve the credentials. For more information, see Secrets management.

Connect using SSL #

To connect to ASE using SSL, simply enable SSL in Replicant’s connection configuration file:

ssl:
  enable: true

If you want to add certificate to other KeyStore, specify the KeyStore’s location in the connection configuration:

ssl:
  enable: true
  trust-store:
    path: "PATH_TO_TRUSTSTORE"
    password: "TRUSTSTORE_PASSWORD"
    ssl-store-type: 'TRUSTSTORE_TYPE'
  key-store:
    path: "PATH_TO_KEYSTORE"
    password: "KEYSTORE_PASSWORD"

Replace the following:

  • PATH_TO_TRUSTSTORE: path to the TrustStore
  • TRUSTSTORE_PASSWORD: the TrustStore password
  • TRUSTSTORE_TYPE: the TrustStore type—for example, JKS, PKCS12
  • PATH_TO_KEYSTORE: path to the KeyStore
  • KEYSTORE_PASSWORD: the KeyStore password

Additional parameters #

client-charset [v23.07.31.03]

Allows you to specify a custom JDBC character set—for example, iso_1. For a full list of supported character sets and their SAP ASE names, see SAP ASE character set names.

II. Set up Extractor configuration #

To configure replication mode according to your requirements, specify your configuration in the Extractor configuration file. You can find a sample Extractor configuration file sybasease.yaml in the $REPLICANT_HOME/conf/src directory.

Arcion supports both snapshot and realtime modes for SAP ASE. For more information, see the following two sections:

Use snapshot mode #

For operating in snapshot mode, you can make changes under the snapshot section of the configuration file. For example:

snapshot:
  threads: 32
  fetch-size-rows: 10_000

  min-job-size-rows: 1_000_000
  max-jobs-per-chunk: 32

  extraction-method: {BCP|QUERY}

  per-table-config:
    - schema: tpch
      tables:
        partsupp:
          split-key: ps_partkey
        supplier:
          split-key: s_suppkey
        orders:
          split-key: o_orderkey
          nation:
          split-key: n_regionkey

The extraction-method parameter specifies what extraction method to use to extract data from Source ASE. You can set it to any of the following two values:

BCP

Replicant uses ASE’s bcp utility to extract data. For more information, see Use bcp Utility for Extraction.

QUERY (Default)

Replicant uses JDBC connection to extract the data.

Important: When using BCP as the extraction method with filters, or split-key in Extractor configuration, make sure that the Replicant user has access privilege to create views in data schema.

For more information about the Extractor parameters for snapshot mode, see Snapshot mode.

Use realtime mode #

For real-time replication, follow these instructions:

1. Grant necessary permissions #

First, make sure that the ASE account you specify in the Replicant connection configuration file possesses the following permissions:

  • sa_role
  • replication_role
  • sybase_ts_role

2. Specify Extractor parameters #

Specify extraction parameters under the realtime section of the configuration file. For example:

realtime:
  threads: 1
  fetch-size-rows: 100000

  fetch-interval-s: 1

  _traceDBTasks: true

  heartbeat:
    enable: true
    catalog: tpch
    schema: blitzz
    interval-ms: 10000
Important: Always enable heartbeat table. Otherwise, truncation point does not move forward.

For more information about the Extractor parameters for realtime mode, see Realtime mode.

Additional real-time parameters #

cdc-log-fetch-timeout-s
Sets the timeout for CDC log scan in seconds.

Default: 600.

dump-transactions [v23.08.31.4]

{true|false}.

Specifies the behavoir of dumping committed transaction logs.

true #
  1. Replicant disables automatic log dump on the ASE server.
  2. Replicant manually dumps commited transaction logs acccording to the value you set in purge-interval-s.
false #

The current ASE server settings control dumping of commited transaction logs.

Default: true.

purge-interval-s [v23.08.31.4]
Controls the frequency of truncation point update and transaction log dump in seconds. This parameter takes effect only if sufficient logs exist and page number has changed from the last update. If you set dump-transactions to false, Replicant only updates the truncation point.

Default: 60.

Limitations #

  • You can run only one Extractor thread for each SAP ASE database. You can run multiple snapshot tasks in parallel.
  • DDL Replication isn’t supported.
  • Running merge operations during CDC results in a non-recoverable error. To sync the target database again, you must run reinit or full snapshot again.
  • View Replication is not supported for real-time replication but possible for snapshot replication.
  • It’s not possible to manually reset truncation point.

Handling secondary truncation point #

When a replication starts, Replicant establishes a $replication_truncation_point entry in the syslogshold system table. This indicates an ongoing replication process. While Replicant is working, it advances the replication truncation point at regular intervals, according to the amount of data that has already been copied to the target.

Once Replicant establishes the $replication_truncation_point entry, you must keep Replicant running at all times to prevent the database log from becoming excessively large. To stop the replicant task permanently, remove the replication truncation point:

dbcc settrunc('ltm','ignore')

After removing the truncation point, you cannot resume the replication job. If automatic trunction is enabled, ASE continues to automatically truncate the log at the checkpoints.

For a detailed explanation of configuration parameters in the Extractor file, read Extractor Reference.

Use bcp utility for extraction #

Note: bcp is only supported for snapshot mode.

You can configure Replicant to use the bcp (bulk copy program) utility for extracting data from your Source ASE. To do so, follow the steps below:

Specify connection details #

In your SAP ASE connection configuration file, specify the bcp connection details under a new field bcp-connection:

bcp-connection:
  host: HOSTNAME
  port: PORT_NUMBER
  username: 'USERNAME'
  password: 'PASSWORD'
  sybase-dir: 'SYBASE_SETUP_DIRECTORY'
  ocs-dir-name: 'OCS_DIRECTORY_NAME'

Replace the following:

  • HOSTNAME: hostname of the SAP ASE server
  • PORT_NUMBER: port number of the SAP ASE server
  • USERNAME: the username of the ASE database user
  • PASSWORD: the password associated with USERNAME
  • SYBASE_SETUP_DIRECTORY: the absolute path for Sybase setup directory on Replicant machine
  • OCS_DIRECTORY_NAME: the OCS directory name that is inside your Sybase setup directory

Specify the extraction method in Extractor configuration file #

In your SAP ASE Extractor configuration file, set the value of extraction-method to BCP. This tells Replicant to use ASE’s bcp utility for extraction.

Important: When using BCP as the extraction method with filters, or split-key in Extractor configuration, make sure that the Replicant user has access privilege to create views in data schema.