SharePlex 12.1 - Installation and Setup Guide

Demonstration of transformation for PostgreSQL

Note: Before proceeding, make certain the SharePlex demonstration objects are installed. See Pre-work for the demonstrations.

In this demonstration, you will use a supplied transformation procedure to have SharePlex replicate data from two separate source tables and apply it to one target table.

Prepare the objects

Note: In this demonstration, the demonstration objects are assumed to be in the schema named demo. Substitute the actual schema, if different.

Perform the following steps to prepare the objects:

1. If you ran previous demonstrations, do the following:

   1. On the source and target, run sp_ctrl and issue the following command to shut down sp_cop.

      sp_ctrl (source)shutdown

      sp_ctrl (target)shutdown

   2. Run the pg_cleansp on the source and the ora_cleansp on the target according to the instructions provided in SharePlex Reference Guide. This removes the queues from the previous demonstrations and deactivates the previous configuration.

2. On the source and target, TRUNCATE the od_employee and od_salary tables.

   SQL> truncate table demo.od_employee;

   SQL> truncate table demo.od_salary;

3. On the target, grant the user who owns the demonstration objects the system privilege to execute the sp_cr package, which was installed in the SharePlex schema when SharePlex was first installed.

   SQL>grant execute on sp_cr touser_name

4. On the target, log into SQL*Plus as the user who owns the SharePlex demonstration objects, then run the transform.sql script from the util sub-directory of the SharePlex product directory. This installs transformation routines named od_transform_employee_insert and od_transform_employee_update. You are prompted for:

   • a schema and tablespace
   • the name of the SharePlex database user

Configure SharePlex

Perform the following steps to configure SharePlex:

1. On the target, open the transformation.SID file (where SID is the ORACLE_SID of the target database) in a text editor. This file is located in the data sub-directory in the SharePlex variable-data directory.

   Note: Post checks this file to determine if there is a transformation procedure that it must call instead of posting the operation to the database.

2. Create the following entries in the transformation.SID file.

   Separate each column with at least a few spaces or a tab character.

   demo.od_employee	I	demo.od_transform_employee_insert
   demo.od_employee	U	demo.od_transform_employee_update
   demo.od_salary	I	demo.od_transform_employee_insert
   demo.od_salary	U	demo.od_transform_employee_update

   Note: The components of each entry are as follows, in the order they must appear: The target table to which a transformation procedure is assigned. The operation type for which the specified transformation procedure will be called. The name of the assigned transformation procedure to use. Multiple entries can be used to assign different procedures to different operation types for the same table.

3. On the source, create a configuration file named od.transform that replicates the od_salary and od_employee tables.

   sp_ctrl(source)>create config od_transform

4. In the text editor, build your configuration file based on the following template.

   datasource:o.r.dbid
   demo.od_salary	"DEMO"."OD_SALARY"	target_system@o.target_SID
   demo.od_employee	"DEMO"."OD_EMPLOYEE"	target_system@o.target_SID

5. Save the file, then exit the editor. SharePlex automatically saves the file in the config sub-directory.

Activate and start replication

Perform the following steps to activate and start replication:

1. On the source, activate the configuration.

   sp_ctrl(source)> activate config od_transform

2. Confirm that the configuration activated successfully. The name od_transform should appear under File Name, and the word Active should appear under State.

   sp_ctrl(source)>list config

3. On the source, log in as the demo schema owner, then execute the od_add_emps procedure to populate the od_employee and od_salary tables. Use an IN parameter of 10 to create 50 new employees in the od_sales_emp_data table.

   dbname# call od_add_emps(10);

View the transformed data

Perform the following steps to view the transformed data:

1. On the target, run SQL*Plus.

2. In SQL*Plus, select all rows from od_sales_emp_data.

3. View the transformed data. You should see the following results:

   • The EMPLOYEE_NAME column contains the first and last name of the employee. Compare this to the source od_employee table, where the first and last names are in separate columns.
   • The DEPARTMENT column contains the department name. Compare this to the source od_employee table, where the EMP_DEPT_NO column contains a number. The transformation procedure transformed the replicated department number into the department name by referencing the od_department table.
   • The SALARY column contains the salary from the od_salary table.
4. [OPTIONAL] To see how transformation works for UPDATEs, you can update the od_employee table manually. The od_transform_employee_update procedure will make the transformation. To further this demonstration, you may construct a transformation procedure for DELETEs.

Sample configuration for PostgreSQL to PostgreSQL replication

SharePlex gets its replication instructions from “configurations,” which are user-defined specifications that tell SharePlex what to do. For each group of objects that you want to replicate, you create a configuration file.

Configurations reside on the source system and define:

• The datasource (source database) — the identifier of the source database.
• The source objects — the names of the objects within the source database that contain the data to be replicated. You can replicate some or all of the tables within a database.
• The target objects — the names of the objects in the database on the target system that will receive the replicated data.
• The routing map — the name of the target system and, if the target is a database, its identifier.

To create the demonstration configuration:

1. Create a replication configuration named sample_config by issuing the create config command in sp_ctrl on the source system. This opens the default text editor, which is vi for Linux systems.

   sp_ctrl(sysA)> create config sample_config

   Refer to Template 1 below as you construct your configuration.

   Template 1: Basic demonstration configuration sample_config

   datasource:r.source_DB
   "qarun"."basic_c127"	"splex"."basic_c127"	10.250.14.105@r.sp_ad

    

2. On the first non-commented line of the file, type the following, leaving no space between any of the items.

   datasource:r.source_databasename

   (Substitute the PostgreSQL database name of the source instance for source_databasename.) This tells SharePlex where to find the table whose data will be replicated. The r. tells Share- Plex that PostgreSQL data is being replicated.

3. On the next line, enter the owner name (splex) and table name (demo_src) of the source table, separating the two items with a dot (.) but no spaces. Using the owner’s name with a table name ensures that SharePlex replicates the correct table, since different tables in different schemas in a database could have the same name.

   splex.demo_src
4. Type at least a few spaces or a tab to create a second column. Do not press Enter.

5. In the second column, enter the owner name (splex) and table name (demo_dest) of the target table, separating the two items with a dot (.) but no spaces.

   splex.demo_dest

6. Type a few spaces or a tab to create a third column. Do not press Enter.

7. In the third column, type the following items with no space between them. This creates the routing map for your configuration, telling SharePlex where to put the replicated data.

   • the name of the target system
   • the @ symbol
   • the letter r
   • a dot (.)
   • the target instance

   Example:

   sysB@r.databasename

8. Save the file and exit the editor. This returns you to the sp_ctrl prompt.

9. [OPTIONAL] To view the configuration, issue the view config command in sp_ctrl on the source system:

   sp_ctrl(sysA)>view config sample_config

10. Activate the configuration in sp_ctrl on the source system. Configuration names are case-sensitive.

    sp_ctrl(sysA)>activate config sample_config

11. To confirm that your configuration is active, type the following sp_ctrl command on the source system to display a list of all configurations. The sample_config configuration should appear under “File Name,” and the word “Active” should appear under “State.”

    sp_ctrl(sysA)>list config

Tip: If your configuration activation fails, use the view config sample_config command in sp_ctrl to view the file. Compare it to Template 1 and make sure all of the information you entered is correct. For example, check for extra spaces that are not supposed to be there, or for missing components, such as the r. before the database name.

To correct mistakes in the configuration file:

1. Run the edit config sample_config command in sp_ctrlbefore you activate the configuration file (or if the activation failed).
   This command opens the file in the text editor.
2. Make the changes by editing the file.
3. Save the changes, and re-try the activation.

Note: To change an active configuration, you must copy it to a new file first with the copy config command, and then edit and activate the copy.

Configure, activate, and verify replication on the PostgreSQL source

Note: Before proceeding, make certain the SharePlex demonstration objects are installed.

In this demonstration, you will create and activate a replication configuration, load data to the source table, and then confirm that the data was replicated successfully to the target table. You will also repair a table that is out of synchronization.

Create a configuration file

Note: In this demonstration, the demonstration objects are assumed to be in the schema named demo. Substitute the actual schema, if different.

Perform these steps on the source system:

1. Run sp_ctrl .

2. Issue the following command to create a configuration file named sample_config that replicates the sample_department, sample_salary, sample_timesheet, and sample_employee tables to target tables of the same names on the target system..

   sp_ctrl(source)>create config sample_config

3. In the text editor, build your configuration file based on the following template. Allow no spaces between the characters in a component (source specification, target specification, routing map), and place at least one space between each component.

where:

• # denotes a comment.
• source_databasename is the PostgreSQL database name of the source database.
• target_system is the name or IP address of the target system.
• target_databasename is the PostgreSQL database name of the target database.

4. Save the file, then exit the editor. SharePlex automatically saves the file in the config sub-directory.

5. In sp_ctrl, verify that the configuration file will activate successfully.

   sp_ctrl(source)>verify config sample_config

Syntax configuration

An example of a config file for PostgreSQL to PostgreSQL replication:

An example of a config file for PostgreSQL to Oracle replication:

Activate the configuration

Perform these steps on the source system. When you activate a configuration, SharePlex is ready to capture transactional changes that are made to the specified source data.

1. Activate the configuration.

   sp_ctrl(source)>activate config sample_config

   Note: Configuration names are case-sensitive.

2. Confirm that the configuration activated successfully. The name config sample_config should appear under File Name, and the word Active should appear under State.

   sp_ctrl(source)>list config

Database Setup for PostgreSQL

Pre-requisites to configure PostgreSQL database setup as a source:

1. In the PostgreSQL configuration file, set the wal_level parameter to logical.
   For example: wal_level = logical # minimal, replica, or logical
2. Add an entry of SharePlex user in the pg_hba.conf file so that SharePlex Capture can establish a connection with the walsender process of PostgreSQL
   For example: # TYPE DATABASE USER ADDRESS METHOD
   host replication all 10.10.10.10/32 trust
3. For all the tables which are going to be part of replication, set the 'replica identity' to 'full' before activation.
   For example: alter table "DEMO_SRC" replica identity full;
4. Navigate to the /var/lib/pgsql/13/data directory and edit the PostgreSQL file.
   Remove the comment and increase the below two parameters' value as below:
   wal_sender_timeout = 6000s
   wal_receiver_timeout = 6000s
5. Set the value of logical_decoding_work_mem = 4096MB (recommended). This specifies the maximum amount of memory to be used by logical decoding.
6. Navigate to the bin directory and restart database: cd /usr/pgsql-13/bin/./pg_ctl -D /var/lib/pgsql/13/data/ restart

Pre-requisites to configure PostgreSQL database setup as a target:

Fujitsu Enterprise PostgreSQL

Fujitsu PostgreSQL contains a customized ODBC driver designed to run with the shared libraries supplied by Fujitsu PostgreSQL. In order to ensure the proper configuration, you must create a symbolic link to ensure that the correct libraries are found at runtime.

Issue Description:

If you attempt to run the pg_setup program prior to creating the symbolic link, pg_setup will fail with the following error message:

Error: [unixODBC]invalid connection option "target_server"

To create a symbolic link:

To ensure that the proper library gets loaded, first locate the path to the directory where the Fujitsu PostgreSQL libraries are installed (for example /opt/fsepv95server64/lib). Then create a symbolic link in the SharePlex OPTDIR/lib/hooks directory pointing to the libpq.so.5 library in the Fujitsu PostgreSQL library directory. For example:

cd /opt/splex/lib/hooks

ln –s /opt/fsepv95server64/lib/libpq.so.5

Overview

Run the PG Setup utility for PostgreSQL (pg_setup) to establish a user account, schema, and tables for use by SharePlex.

Guidelines for use

• Run the pg_setup utility on PostgreSQL instances in the SharePlex replication configuration.
• Within a server cluster, run the pg_setup utility on the node to which the shared disk that contains the SharePlex variable-data directory is mounted.
• For consolidated replication, run the pg_setup utility for each variable-data directory.
• The pg_setup utility requires two environment variables to be set for system ODBC configuration:
  • ODBCINI – Specifies the full path to the odbc.ini file.
    Example: export ODBCINI=/path/to/odbc.ini

  • ODBCSYSINI – Specifies the directory containing the odbcinst.ini file.
    Example: export ODBCSYSINI=/path/to/odbc/

  Note: If neither variable is defined, SharePlex defaults to using its internal ODBC ini files located at: /var/odbc.

• Provide a DSN (data source name).

  Connection Type on Linux

  What to do

  DSN

  If you have a DSN defined, and you want to use it for the SharePlex connection, copy or link the ODBC files in which that DSN is defined (odbc.ini and odbcinst.ini) to the odbc subdirectory of the SharePlex variable-data directory. This prevents connection errors when the SharePlex processes connect to the database. If you do not have a DSN defined but want to use one, you can create it in the template files provided in the odbc subdirectory.

Required Privilege

Review the following requirements to ensure that the setup succeeds.

• The Database Setup utility must be run as a PostgreSQL Administrator in order to grant SharePlex the required privileges to operate on the database and to create the SharePlex database account.

• (Symfoware only) If the person who is running the setup utility is not a Fujitsu Enterprise PostgreSQL owner, set the environment variable LD_LIBRARY_PATH to include the path to the lib subdirectory in the Fujitsu Enterprise PostgreSQL installation directory. The LD_LIBRARY_PATH is set in the .bash_profile file of the Fujitsu Enterprise PostgreSQL owner.

  Example:

  export LD_LIBRARY_PATH= /opt/symfoserver64/lib:$LD_LIBRARY_PATH

  If you do not set this path, the following error occurs:

  symbol lookup error: /opt/fsepv95client64/odbc/lib/psqlodbca.so: undefined symbol: PQconnectdbParams

• Cloud installations:
  • Common restrictions on privileges in cloud-hosted database services make it difficult for the setup utility to succeed in every possible type of scenario. To ensure that the database setup succeeds, only use the setup utility for the following purposes: To do a first-time database setup with a new SharePlex user, or, to modify an existing SharePlex user that either owns the database or has access to it.

  • You might need to grant usage/privileges on the target schema and tables to the SharePlex user, as shown in the following example:

    Log in as the schema owner, then issue the following commands:

    grant usage on schema schema_name to user_name;

    grant all privileges on all tables in schema schema_name to user_name;

  • For Google Cloud database, grant the following privileges if a user wants to change the owner of a table in replication:

    alter user splex_user createdb createrole login;

    create role role_name;

    grant role_name to splex_user;

Run PG setup for PostgreSQL

Follow the below instructions to run the PG setup for PostgreSQL:

1. Shut down any running SharePlex processes and sp_cop on the source system.

2. Run the pg_setup program from the bin subdirectory of the SharePlex product directory.

   Important! If you installed the SharePlex instance on any port other than the default of 2100, use the -p option to specify the port number. For example, in the following command the port number is 9400.

   $ /users/splex/bin> pg_setup -p9400

Table 14: Setup prompts and responses

A successful setup terminates with a message similar to the following:

Completed SharePlex for PostgreSQL database configuration

SharePlex User name: splexab

Database name: testdb

-- The datasource identifier in the SharePlex configuration is 'r.testdb' --

Support for hyphen, dot, and plus (-, ., and +) special characters

When the database name contains a hyphen, dot, or plus (-, ., or +) special characters, then remove these characters. Append the _<count> value to the identifier, where the count depends on the datasource identifier existing in the connections.yaml file.

Example:

• For the database name aaa.db, the datasource identifier will be r.aaadb_1.

• For the database name aaa+db, the datasource identifier will be r.aaadb_2, as r.aaadb_1 already points to the aaa.db database.

If the database does not contain any special characters but its entry exists in the connections.yaml file, append _<count> to its identifier.

Example:

If the database name is aaadb_1 and its entry exists in the connections.yaml file, and the database name is different, the identifier will become r.aaadb_1_1.

If the database name does not contain a hyphen, dot, or plus character, then the database name is the same as the datasource identifier.

Configure SharePlex to support PostgreSQL database on Azure platform

You can configure SharePlex to support the PostgreSQL database on the Azure platform using the below steps:

1. Add the DSN entry in the Odbc.ini file at /etc/odbc.ini and the …/var/odbc directory.
2. Copy the /etc/odbcinst.ini file to the …/var/odbc directory.
3. Run the PostgreSQL setup utility.

Note: While performing DML operations in bulk for PostgreSQL PaaS (Azure), a performance slowness issue is observed.

Table 15: Setup Prompts and Responses

A successful setup terminates with a message similar to the following:

Completed SharePlex for PostgreSQL database configuration

SharePlex User name: postgres@pslazpgdb02.postgres.database.azure.com

Database name: sp_mk

The data source identifier in the SharePlex configuration is 'r.sp_mk' –