Integrate data from Pervasive PSQL (Actian Zen) to Amazon Redshift using Matillion

Creating an Identity in Pervasive PSQL (Actian Zen)

Pervasive PSQL (now called Actian Zen) primarily manages access at the operating system (OS) or network level, rather than maintaining traditional internal database users and passwords, as seen in many other RDBMS platforms. However, you can control database access with operating system or domain accounts, and manage permissions within databases using Rights and Access Control.

If you require granular object-level access, you might use Database Users (identities) and Grant/Revoke statements (supported in newer Zen v14+ with the DB Security feature set).

Below are common scenarios:

1. Using OS/Domain Users for Database Access

By default, Actian Zen relies on Windows (or Linux) OS accounts for authentication. To "create" a user:

1. Create a user account in your OS (Windows or Linux).
2. Assign the user to appropriate groups (for example, give membership in a group that has permissions to the database folder and files).
3. Provide connection details to the user, who connects from their workstation or application using their Windows/Linux credentials.

No SQL script is used for this process: access is determined by the OS and database file permissions.

2. Creating a Database User (Zen v14 and Later)

If you have Zen Security enabled, you can define database identities and assign permissions. To do this:

Step 1: Enable the DB Security feature
Make sure your database has security enabled (see PSQL documentation for enabling security mode). Use ZenCC or ZenDBAdmin, or tools provided with the server.

Step 2: Create a Database User Identity

-- Create a database user
CREATE USER 'username' IDENTIFIED BY 'password';

Example:

CREATE USER 'report_user' IDENTIFIED BY 'S3cur3Pa$$w0rd';

You may need to be connected as a database administrator or as the owner.

Step 3: Grant Permissions (Optional)

-- Grant specific privileges, e.g., SELECT on a table
GRANT SELECT ON tablename TO 'username';

Example:

GRANT SELECT ON employees TO 'report_user';

Step 4: Revoking Permissions (Optional)

-- Revoke privilege if needed
REVOKE SELECT ON tablename FROM 'username';

3. Notes

• Commands may require semicolons depending on your SQL client/tool.
• Be sure to secure user credentials and do not use plaintext or insecure passwords.
• For full syntax and security management, refer to the Actian Zen SQL User Guide.

Example: Full Script

CREATE USER 'jdoe' IDENTIFIED BY 'Str0ng_pw!';
GRANT SELECT, INSERT ON customers TO 'jdoe';

If the security feature is not enabled, or if you’re using Btrieve-only (record-level access), please refer to file system and client configuration for access control instead of SQL users.

Installing the JDBC driver

At the time of writing, the JDBC driver for the Pervasive PSQL (Actian Zen) database is not included by default in Matillion Data Productivity Cloud. This restriction is due to licensing or redistribution limitations imposed by the database vendor. As a result, you must manually acquire and install the JDBC driver before you can connect Matillion workflows to a Pervasive PSQL (Actian Zen) database.

1. Downloading the JDBC Driver

• Visit the official resource for obtaining the Pervasive PSQL (Actian Zen) JDBC driver: [-]
• When available, select the Type 4 JDBC driver. Type 4 drivers are pure Java implementations and do not require native libraries; they are also best supported by cloud integration platforms like Matillion.

2. Preparing to Install the Driver

• After downloading, extract the JAR file(s) that comprise the JDBC driver, if necessary.
• Ensure you comply with all licensing terms before proceeding with distribution or use.

3. Uploading the JDBC Driver to Matillion Data Productivity Cloud

• Installation is performed by uploading the JDBC driver JAR file(s) to your Matillion Agent.
• Follow the step-by-step instructions for uploading external JDBC drivers, as outlined in the official documentation: https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/
• Typically, this involves navigating to your agent management interface and specifying the location of the JDBC driver JAR files.

4. Using the Driver in Matillion

• Once installed, you will be able to create and configure a database connection in Matillion Designer that targets your Pervasive PSQL (Actian Zen) system.
• For instructions on connecting to databases and executing queries within Matillion Data Productivity Cloud, refer to: https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/
• Be sure to reference the correct JDBC driver class and connection URL format as indicated by Actian’s documentation.

Checking network connectivity

To enable Matillion Data Productivity Cloud to connect to your Pervasive PSQL (Actian Zen) database, you must ensure that the database allows incoming connections from the appropriate sources based on your deployment type:

• Full SaaS Agent Configuration
  You must allow incoming connections to the Pervasive PSQL (Actian Zen) database from the public IP addresses that Matillion Data Productivity Cloud uses. The list of required IP addresses is available at the following URL: https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/.

• Hybrid SaaS Deployment
  In a hybrid deployment, you must allow incoming connections from your own Virtual Private Cloud (VPC) where the Matillion Hybrid SaaS agent is running. To test or verify network access, you can use the network checking utilities provided by Matillion here: https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/.

Important:
If your Pervasive PSQL (Actian Zen) database is referenced using a DNS hostname (rather than a static IP address), the Matillion agent—whether deployed as Full SaaS or Hybrid SaaS—must have the ability to resolve the DNS address. Ensure that appropriate DNS resolution is configured wherever the agent is running.

Querying Data from a Pervasive PSQL (Actian Zen) Database

Pervasive PSQL (Actian Zen) databases support standard SQL for querying data. Below are instructions and examples aimed at technical users who need to extract data from Actian Zen for loading into another data warehouse such as Amazon Redshift, with considerations for initial and incremental loading patterns.

Example SQL SELECT Queries

Here are typical SQL queries for retrieving data from tables in Pervasive PSQL (Actian Zen):

``` -- Retrieve all columns from the "customers" table SELECT * FROM customers;

-- Retrieve specific columns from the "orders" table SELECT order_id, order_date, customer_id, amount FROM orders;

-- Filter data by date for incremental loading SELECT * FROM orders WHERE order_date > '2024-06-01'; ```

When using Pervasive PSQL, typical SQL operators, column aliases, functions (e.g., UPPER(), SUBSTRING()), and WHERE clauses are supported.

Datatype Conversion Considerations

When migrating data from Pervasive PSQL (Actian Zen) to Amazon Redshift, datatype conversion may be necessary because the source and target databases support different data types. For example:

Note: Always inspect and test datatype mappings as needed for your schema.

Data Loading Pattern: Initial and Incremental Loads

A best practice is to perform an initial (full) load of the data, followed by scheduled incremental loads. This approach minimizes cutover complexity and optimizes change capture. Use the same Database Query component (or equivalent) in your orchestration/ETL tool for both patterns.

Initial Load

• Extracts all rows.
• No filter clause in the SQL query.

Example:

SELECT * FROM customers;

Incremental Load

• Extracts only new or modified rows since the last load.
• Includes a filter clause based on a time window or monotonically increasing value (e.g., timestamp, ID).

Example:

SELECT * FROM orders
WHERE modified_date > '2024-06-10 00:00:00';

Tip: The filter value is typically parameterized with the date/time (or column) of the last successful load.

For more about incremental loading patterns, refer to Matillion: Incremental load data replication strategy.

Using the Database Query Component

• Configure the Database Query component with a connection to your Pervasive PSQL (Actian Zen) database.
• For the initial load, leave the SQL query unfiltered.
• For incremental loads, add an appropriate WHERE clause to limit rows.
• Ensure all relevant schema objects and datatype conversions are accounted for in your ETL or data integration pipelines.

Data Integration Architecture

Loading data into Amazon Redshift in advance of integration allows you to break down the data integration challenge into two manageable steps: first, loading the raw data, and second, transforming and integrating it for analytics. This "divide and conquer" approach is a core advantage of the Extract, Load, Transform (ELT) architecture. Data integration almost always requires transformation, and the most robust way to manage this process is by orchestrating data transformation pipelines, which ensure consistent and repeatable handling of complex data flows. Another primary benefit of the ELT approach is that all data transformations and integrations occur directly inside the Redshift database—this not only delivers high performance and scalability but also enables on-demand processing. Moreover, since transformations happen within Redshift, there is no need for external data processing infrastructure, reducing both cost and operational complexity.