Integrate data from Sybase Adaptive Server Enterprise (SAP ASE) to Amazon Redshift using Matillion

Creating a User in Sybase Adaptive Server Enterprise

To create a new user in Sybase Adaptive Server Enterprise (ASE), you must first ensure that a valid login exists at the server level. The user is then mapped to a specific database by adding the login as a database user. The following instructions and example SQL scripts outline this process.

1. Create a Login (if necessary)

A login grants access to the ASE server. You only need to perform this step if the login does not already exist.

-- Run as a user with 'sa_role'
sp_addlogin login_name, password

login_name

password

Example:

sp_addlogin alice, SecurePa$$w0rd

2. Add the User to a Database

Once the login is created, add the user to a specific database:

``` -- Connect to the target database USE target_database GO

-- Add the login as a database user sp_adduser login_name [, user_name] [, grpname]

``
-

: The ASE login you created earlier.
-

(optional): The username to use within the database. If omitted, it defaults to

.
-

(optional): The database group (such as

,

Example: ``` USE mydb GO

sp_adduser alice, alice, public ```

3. (Optional) Grant Roles or Permissions

You may need to explicitly grant database or object permissions to the user.

Example:

GRANT SELECT, INSERT ON mydb.dbo.mytable TO alice

4. Confirm User Creation

To list users in the current database:

sp_helpuser

Note:
- These actions require appropriate administrative privileges (

sa_role

,

sso_role

). - Password security policies may be enforced by your ASE configuration.

Installing the JDBC Driver

The Sybase Adaptive Server Enterprise (ASE) JDBC driver is required to enable connectivity between Matillion Data Productivity Cloud and your Sybase ASE instance. However, due to licensing or redistribution restrictions, this JDBC driver is not included by default with Matillion Data Productivity Cloud. You will need to manually obtain and install the driver before configuring any Sybase ASE integrations.

Downloading the JDBC Driver

1. Navigate to the SAP Sybase ASE product page: https://www.sap.com/products/sybase-ase.html.
2. Locate the download resources for clients and drivers. You may be required to log in with an SAP account, or to register for access. Review any license terms and conditions presented by SAP before downloading.
3. During the download process, seek out the Type 4 JDBC driver (also known as the "pure Java" driver) for maximum compatibility and portability. This is typically named

   jconn4.jar

   or similarly.

Installing the JDBC Driver into Matillion Data Productivity Cloud

To add the Sybase ASE JDBC driver to Matillion Data Productivity Cloud, follow these installation steps:

1. Refer to the detailed instructions provided in Matillion documentation:
   Uploading external drivers.
2. Access your Matillion Agent or Agent UI, as described in the documentation.
3. Using the guidance from the documentation above, upload the downloaded JDBC

   .jar

   file (e.g.,

   jconn4.jar

   ) to the appropriate location on your Agent, following the supported upload method.
4. After upload, restart the relevant Matillion services or Agent as required to ensure the driver is recognized by the application.

Next Steps: Connecting to Sybase ASE

With the driver in place, you are ready to configure database integrations and use Matillion Data Productivity Cloud to query your Sybase ASE instance. For further guidance on setting up and using database query features with your new driver, refer to the following documentation:
Database Query component usage instructions.

Ensure that any connection settings or authentication prerequisites for your Sybase ASE environment are met prior to beginning database operations.

Checking network connectivity

To enable successful connectivity between Matillion Data Productivity Cloud and your Sybase Adaptive Server Enterprise (ASE) database, you must ensure that the Sybase ASE instance is configured to allow incoming network connections, according to your specific deployment type:

• Full SaaS Agent Configuration:
  Allow incoming connections from the set of IP addresses that Matillion’s SaaS infrastructure uses. The complete and up-to-date list of these IP addresses can be found at:
  https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/

• Hybrid SaaS Deployment:
  Allow incoming connections from your own Virtual Private Cloud (VPC). This allows Matillion agents running within your environment to reach the Sybase ASE database.
  You can verify and troubleshoot network access using utilities found at:
  https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/

Additionally, if the Sybase ASE database is referenced using a DNS hostname (rather than a direct IP address), ensure that the agent (whether Full SaaS or Hybrid SaaS) can properly resolve this hostname to the correct IP address. Proper DNS configuration is essential for successful connectivity.

Querying Data from Sybase Adaptive Server Enterprise

This guide provides examples and best practices for querying data from a Sybase Adaptive Server Enterprise (ASE) database. It covers core SQL

SELECT

1. Example Sybase ASE: Basic SQL SELECT Queries

``` -- Select all columns and rows from a table SELECT * FROM employees;

-- Select specific columns SELECT employee_id, first_name, last_name FROM employees;

-- Filter rows with a WHERE clause SELECT * FROM employees WHERE department = 'Sales';

-- Aggregate query SELECT department, COUNT(*) AS headcount FROM employees GROUP BY department;

-- Join two tables SELECT e.employee_id, e.first_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; ```

2. Datatype Conversion Notes

When transferring/querying data between Sybase ASE and Amazon Redshift, you may encounter datatype differences. Some examples:

• DATETIME

  (Sybase) maps to

  TIMESTAMP

  (Redshift)

• MONEY

  (Sybase) may map to

  DECIMAL

  /

  NUMERIC

  (Redshift)

• VARCHAR

  is generally compatible, but verify maximum lengths required

Prior to migration or ETL loading, check your column datatypes and plan necessary conversions for compatibility.

3. ETL Pattern: Initial Load and Incremental Load

When building a data pipeline, the recommended approach is:

• Once-off Initial Load: Extract the entire dataset (no filter).
• Incremental Loads: Extract only records changed/created since the last load (filtered by a timestamp or change tracking column).

Both load types can use the same Database Query component. The only difference is whether a filter is used.

a. Initial Load Example (No Filter Clause)

-- No WHERE clause, fetches all records
SELECT *
FROM orders;

b. Incremental Load Example (With Filter Clause)

Suppose you have a timestamp column named

last_updated

-- Fetch only new or changed rows since the last extraction
SELECT *
FROM orders
WHERE last_updated > '2024-06-01 00:00:00';

Set the filter value dynamically based on the last successful load time.

More details: For a deeper dive on incremental loads, read Incremental Load Data Replication Strategy.

Note: Use the same Database Query component instance for both initial and incremental loads, adjusting the query’s WHERE clause as appropriate.

Data Integration Architecture

One of the key benefits of the ELT (Extract, Load, Transform) architecture is the ability to "divide and conquer" the data integration problem by splitting it into two distinct steps. First, data is loaded into the target system, such as Amazon Redshift, in advance; only then is it transformed and integrated for downstream analysis. This separation enables teams to utilize data transformation pipelines, which are well-suited to manage the complexity and requirements of data integration. With ELT, all data transformation and integration processes occur inside the target Redshift database itself. This approach offers significant advantages—it allows organizations to leverage Redshift's fast, on-demand, and scalable processing capabilities, eliminating the need for separate, costly data processing infrastructure outside of the database.