Stelo Technical Documents

Quick Start Guide to using SQDR on Linux

Last Update: 27 December 2022
Product: StarQuest Data Replicator
Version: SQDR 6.11 or later
Article ID: SQV00DR050

Abstract

This Quick Start Guide describes how to install and configure the software you need, including the StarSQL ODBC driver for Db2, to perform snapshot and incremental replication operations using the StarQuest Data Replicator (SQDR) on Linux. Read the complete document before starting your installation.

To perform incremental replication, start with the Quick Start Guide to Using SQDR Plus, and then return to this document instead of continuing to the Part 2 sections for Windows.

Solution

• System Requirements
  
• Planning Decisions
  
• Installation Overview
  
• Installation Details
  
  • Microsoft SQL Server & PowerShell
    
  • StarSQL
    
  • SQDR_ODBC
    
  • SQDR
    
  • Licensing Stelo Products
    
• Use StarAdmin to bind Db2 packages (if necessary)
  
• Create users
  
• Test ODBC drivers
  
• Create a control database
  
• Run SQDR Configuration from Windows
  
• Start the service
  
• Configure SQDR sources, destinations, and subscriptions
  
• Running updates

System Requirements

• Red Hat or Oracle Linux 8.x (8.7 or later). Other RPM-based distributions (e.g. Rocky Linux, Alma Linux) may work. Note that RHEL/Oracle 9.x is not yet supported.
• A Db2 LUW 11.5.x or SQL Server instance to host the control database. A typical scenario is to run this on the SQDR/Linux system, but using a remote database is also an option.
• A Windows system with the Data Replicator Manager package (v6.x) installed, used for managing the SQDR/Linux system. The Windows system requires network access to the control database to be used by SQDR. It also needs network access (gRPC ports 7737 & 7738) to the Linux system. You can also use a Windows system with the full SQDR product installed, but be aware that using SQDR Configuration to configure the control database used by SQDR/Linux will temporarily disrupt the local SQDR service.
  

Planning Decisions

• Decide if SQDR will be colocated with SQDR Plus. This influences the choice of DBMS type for the control database, as SQDR Plus requires the presence of Db2 LUW, and its installer includes the creation of the Db2 LUW control database SQDRC. In a split tier environment, you may choose to use SQL Server instead of Db2.
• If you will be using SQDR Streaming or Data Lake Sink support, we recommend that SQDR & SQDR Plus should be colocated and using a local Db2 control database named SQDR.
• Choose the DBMS type (Db2 LUW 11.5.x or SQL Server) and location (local or remote) for the control database
• Choose the licensing model from the following options:
  • Using local node-locked licensing. This is the simplest, but requires a static IP address.
  • Using an existing StarLicense Server (running on Windows or another Linux system). This requires network connectivity (port 4999) to the StarLicense Server.
  • Installing StarLicense Server on the SQDR/Linux system. This requires a static IP address.

Installation Overview

• From the Microsoft yum repository
  • SQL Server ODBC driver. This pulls in the dependency of the Microsoft packaging of unixODBC 2.3.7. The distro packaging of unixODBC 2.3.7 can also be used.
  • Optional: install SQL Server itself if it will be used as the control database.
  • Optional: powershell
• If you will be using a local StarLicense Server, install & configure StarLicense
• Install StarSQL (64-bit) ODBC driver for Db2
• Install SQDR_ODBC (the bundled ODBC drivers for other platforms)
• Install StarAdmin (if necessary)
• Install other ODBC drivers - See the SQDR Help file for detailed documentation for the ODBC drivers

Installation Details

Microsoft

See the Microsoft documentation Install the Microsoft ODBC driver for SQL Server (Linux) for details on installing the ODBC driver; see Installation guidance for SQL Server on Linux for details on installing the full SQL Server.

Set up the yum repository:

# curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo

To install only unixODBC 2.3.7 (and not SQL Server)
# yum install -y unixODBC

Install the SQL Server ODBC driver:
# sudo ACCEPT_EULA=Y yum install -y msodbcsql17
(this will also install unixODBC 2.3.7)
optional: for bcp and sqlcmd
# sudo ACCEPT_EULA=Y yum install -y mssql-tools
# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
# source ~/.bashrc

Install PowerShell:
# yum install powershell

StarSQL

• StarSQL 6.4x: Install the compatibility package for OpenSSL 1.0.x. For RHEL/Oracle 9, you will need to obtain the RPM for a RHEL/Oracle 8 system and use the rpm command to install it. StarSQL 6.4x installs to /opt/StarQuest/starsql64.

# yum install compat-openssl10

• for StarSQL 6.5x or later: StarSQL 6.5x installs to /opt/stelo/starsql64. For RHEL/Oracle 9, install compat-openssl11.
  
• Install StarSQL:

# rpm -i starsql64-<version>.rpm

• Confirm that 2 StarSQL entries (StarSQL64 and StarSQL (64-bit)) have been added to /etc/odbcinst.ini.
  

SQDR_ODBC

Install the package:

# rpm -i sqdr-odbc<version>.rpm

Confirm that SQDR <drivername> entries are added to /etc/odbcinst.ini.

When starting the SQDR service, the following environment variables must be set. Depending on how you plan to start the service, this can be done in /opt/StarQuest/sqdr/sqdrsvcd.sh or /etc/systemd/user/sqdrsvc/sqdrsvc.d/custom.conf (recommended).

For all SQDR bundled drivers:
add /opt/StarQuest/sqdr_odbc/lib to LD_LIBRARY_PATH

For SQDR Salesforce:
export LD_LIBRARY_PATH=/opt/StarQuest/sqdr_odbc/lib;/usr/lib/jvm/jre/lib/amd64;/usr/lib/jvm/jre/lib/amd64/server
export CLASSPATH=/opt/StarQuest/sqdr_odbc/java/lib/sforce.jar

SQDR

Use RPM to install SQDR/Linux itself:

# rpm -i sqdr-<version>.rpm

The program objects end up in /opt/StarQuest/sqdr, and volatile configuration objects are in /var/sqdr.

The installer creates /var/sqdr/sqdrsvcreg.conf, which is the equivalent of the registry entry HKLM/Software/StarQuest/SQDR - it contains the install location, version information, and can optionally be used to request debug information.

Licensing Stelo Products

If you will be using node-locked licensing or an existing StarLicense Server

# cd /opt/stelo/starsql64/bin
# ./config-lic

Follow the prompts to add node-locked licensing or connections to an existing StarLicense Server. You will need 4 licenses: SQ, DR, DD, and $*

You can also add licenses or license connections from the command line:
# cd /opt/stelo/starsql64/bin
./starlic-admin client-key-add <license-key1>
./starlic-admin client-key-add <license-key2>
..

If you will be using a local StarLicense Server, see Quick Start Guide to Using StarLicense for UNIX.

• You can use wget to download the installer from downloads.stelodata.com
• expand the tar file and run setup
• Use /usr/share/starlicense64/configure to add licenses for SQ, DR, DD, and $*
• Make sure the service is running.
• Configure StarLicense service to start at boot - see How to Add the StarLicense for UNIX Daemon to the Boot Process
  

Use StarAdmin to bind Db2 packages (if necessary)

You only need to install the StarAdmin package if all the following conditions are met:

• You are using Db2 rather than SQL server for the control database
  
• You do not have SQDR Plus handy (if this a combined tier system, you already have SQDRC with packages bound; in addition, you also have a copy of StarAdmin that is bundled with SQDR Plus)
  
• You do not have StarAdmin handy in some other way (e.g. on the Windows system where you are running SQDR Configuration & Data Replicator Manager)

You will need a GUI environment (e.g. VNC or access to the console) to run StarAdmin on Linux.

If required:

• Bind StarSQL packages in the schema STARSQL in the control database (SQDRC)
• Bind StarSQL packages on the source Db2 system. Note that this is typically done by the StarAdmin bundled with SQDR Plus if you are performing incremental replication, so the most common need for running StarAdmin with an SQDR/Linux installation is for snapshot replication.
  Bind StarSQL packages on a destination Db2 system. This can also be performed from the SQDR Plus installation.

Create users

Create user "sqdr" - no special requirements

Test ODBC drivers

This is a good point to make sure the drivers are installed and licensed. You can use $STARSQL/bin/simpleconn or isql (part of unixODBC) for testing. Edit $HOME/.odbc.ini or /etc/odbc.ini with a text editor to create ODBC data sources; ODBCConfig (the GUI equivalent of Windows' ODBC Admin) is no longer supported. You can also use a connection string.

- Using isql with a DSN
$ isql -v DSN myuser mypasswd

-Using isql with a connection string
$ isql -v -k "DRIVER=StarSQL (64-bit);Server=SQDRC;HostName=127.0.0.1;port=50000;UID=myuser;PWD=mypasswd;PkgColID=STARSQL"

-Using iusql (Unicode version of isql) with a connection string. Note that iusql doesn't recognize the -k parameter; instead start the connection string with a semicolon.
$ iusql -v ";DRIVER=StarSQL (64-bit);Server=SQDRC;HostName=127.0.0.1;port=50000;UID=myuser;PWD=mypasswd;PkgColID=STARSQL"

Note that the bundled drivers will connect but may issue a warning that they are licensed only for use with SQDR. That license warning is issued for every 100 rows selected after a fetch, so do not perform a SELECT of a large table when verifying connectivity.

Create a control database

(see ./Linux_control_database.txt for more detailed doc)

There are many paths here, but for the case of a local Db2 control database, here are the steps:

• install Db2
• Make sure that the Linux firewall is not blocking access to Db2. (later you will also need ports 7737 & 7738 available for drmgr).
• If this a combined tier system, SQDR Plus has already created SQDRC, with pre-bound StarSQL packages

Otherwise:

$ db2 create db SQDRC

Run StarAdmin to bind StarSQL packages

$ db2 connect to SQDRC
$ db2 grant dbadm on database to user 'sqdr'
$ db2 disconnect all

Run SQDR Configuration from Windows

On the Windows system with drmgr or full SQDR package, with network access and credentials for the control database, run SQDR Configuration to create control tables and the sqdr.properties file.. The database should already exist.

• If you are using the full SQDR package on Windows and have a local SQDR service, save a copy of C:\ProgramData\StarQuest\sqdr\sqdr.properties, as it will get overwritten by SQDR Conf.
• Run SQDR Configuration.
  
• Connect to the control database e.g. SQDRC on the Linux system. Use user "sqdr" for credentials for the example of SQDRC (Db2 on Linux). The credentials may be different for SQL Server.
  
• Create control tables.
  
• Ignore the errors about service not found.
  
• After SQDR Configuration completes, use any available file transfer mechanism (e.g. ftp, sftp, samba) to copy sqdr.properties to /var/sqdr on the Linux system. You may need to add  ICONV4UTF8=Y to the StarSQL connection string.
  

Start the service

With the control tables and /var/sqdr/sqdr.properties in place, you are now ready to start the service.

You may choose to start & stop the service as root or as a designated user (e.g. sqdr) with appropriate authorities.

Using systemd:

Using systemd insures that the service will be restarted if it should terminate prematurely.
The installer creates a file /etc/systemd/user/sqdrsvc, and a directory /etc/systemd/user/sqdrsvc.d, where you can customize the environment (e.g. add directories to LD_LIBRARY_PATH) by adding a file called custom.conf.

You can use a non-privileged user such as sqdr for running the service.

- To start service
$ systemctl start sqdrsvc

- To stop service
$ systemctl stop sqdrsvc

- To restart service
$ systemctl restart sqdrsvc

- To get status of service
$ systemctl status sqdrsvc

- to enable the service to start at boot time
$ systemctl --user enable sqdrsvc

- to enable the service to run whether or not the user has an active terminal session
# loginctl enable-linger userid
e.g.
# loginctl enable-linger sqdr

Depending on what control database you are using, make sure that the Db2 or SQL Server service has started (or is running and accessible if you are using a remote control database)

using sqdrsvcd.sh

You can scripts start, stop, and status and place them in /opt/StarQuest (i.e. one level above sqdr, so they get preserved if sqdr is removed and reinstalled). start includes environment variables needed for certain ODBC drivers (bundled drivers, Salesforce bundled driver, Informix, IBM DB2 ODBC). These are explained in the ODBC documentation.

Configure SQDR sources, destinations, and subscriptions

SQDR can use either ODBC data sources (DSN's) or connection strings for sources or destinations. ODBC DSN's are configured on the Linux system using a text editor to modify /etc/odbc.ini (system DSN's) or $HOME/.odbc.ini (for the user that the SQDR service is running as). Connection strings are configured in the Data Replicator Manager and in most cases will be identical to the strings used when running SQDR on a Windows system.

• Create ODBC DSN's on the Linux system if necessary
• On the Windows system, start Data Replicator Manager
• choose Add Server from the Service menu and enter the hostname or IP address of the Linux system. Leave the user and passwords field blank.
• Create sources, destinations, and subscriptions using Data Replicator Manager
• To edit advanced service properties, use SQDR Service Properties (SQDRProps) from the Windows system.
• You can connect with PowerShell from any system (Windows, Linux, MacOS) that supports PowerShell. See Automating SQDR tasks using PowerShell.

 

RUNNING UPDATES

Update any products installed as part of the distro or from the Microsoft yum repository

# yum update

Update the Stelo products. It is recommended that you stop the SQDR service first.

# rpm -U starsql64-<version>.rpm
# rpm -U sqdr-odbc<version>.rpm
# rpm -U sqdr<version>.rpm