StarQuest Technical Documents

Quick Start Guide to Using SQDR Plus v4 - Worksheet

Last Update: 16 May 2017
Product: SQDR Plus
Version: 4.7 or later
Article ID: SQV00PU006WS

Abstract

The Quick Start Guide to Using SQDR Plus v4 describes how to install and configure all of the software you need to perform incremental replication operations using SQDR and SQDR Plus.

We recommend printing out this worksheet and entering the configuration values that apply to your deployment.

Tier 1 Source Database (IBM i)
Parameter
Sample Value
Your Value
Password
Description
Hostname myas400     Hostname or IP Address of the source system
DRDA port 446     DRDA port of the source system
RDB MYRDB     The relational database name on the source system. You can use the DSPRDBDIRE CL command to list the RDB directory.
Installing User QSECOFR     This user should have SECOFR authority and is used by SQDR Control Center when it creates a Staging Agent. Example: QSECOFR.
Agent User and Schema SQDR     This user is created by SQDR Control Center (if it does not already exist) when it creates a Staging Agent. This user requires no special authorities. In addition to the user, SQDR Control Center will create and populate a collection (schema) by the same name. The collection contains control tables and stored procedures used by SQDR and SQDR Plus.
Privileged Owner SQDRADM     This user is created by SQDR Control Center (if it does not already exist) when it creates a Staging Agent. This user the owner of the Journal Reader service program and requires *SECADM authorities.
Database Administrator       A user with authority to bind SQL packages on the DB2 source system, using StarAdmin on the Tier 3 system. This may or may not be the same as the installing user.
Source Database User SQDRUSER     This user needs sufficient authority to select from the desired source tables and to execute stored procedures located in the Agent User collection. Typically this will be an existing end user. This user is used when connecting to the source system from the Tier 3 SQDR platform.

 

Tier 1 Source Database (IBM DB2 for LUW)
Parameter
Sample Value
Your Value
Password
Description
Hostname myhost     Hostname or IP Address of the source system
DRDA port 50000     DRDA port of the source system
RDB MYRDB     The database name on the source system. You can use the db2 list database directory command to list the databases on the source system.
Database User

db2admin db2inst1

    This user should have SYSADM or DBADM authority and is used by SQDR Control Center to create a schema and contents during creation of a Staging Agent and schema, and to monitor database logs for change data during operation.
Agent Schema SQDR     This schema is created by SQDR Control Center (if it does not already exist) when it creates a Staging Agent. The schema contains control tables and stored procedures used by SQDR and SQDR Plus.
Database Administrator       A user with authority to bind SQL packages on the DB2 source system, using StarAdmin on the Tier 3 system. This may or may not be the same as the Database User listed above.
Source Database User SQDRUSER     This user needs sufficient authority to select from the desired source tables and to execute stored procedures located in the Agent User schema. Typically this will be an existing end user. This user is used when connecting to the source system from the Tier 3 SQDR platform.
AUTHENTICATION SERVER     Configure udbAuthentication prperty of the Agent configuration to match if this is a value other than SERVER (e.g. SERVER_ENCRYPT)

Tier 1 Source Database (Microsoft SQL Server)
Parameter
Sample Value
Your Value
Password
Description
Hostname myhost     Hostname or IP Address of the source system
Port 1433     Listening port of the source system
Database MyDatabase     The database name on the source system.
SQL Server User

sa

    If using SQL Server authentication, you will supply this user and password when configuring an SQDR Plus Agent. This user should have authority to create tables and stored procedures in a new schema (e.g. SQDR) on the host database and to alter the database and all subscribed tables to use CHANGE TRACKING; this may require administrative privileges.
Windows User for SQL Server access mydomain\myID     If using Windows authentication (Integrated Security), this user will be used to run the SQDR Plus services. This user should have authority to create tables and stored procedures in a new schema (e.g. SQDR) on the host database and to alter the database and all subscribed tables to use CHANGE TRACKING; this may require administrative privileges.
Source Database User SQDRUSER     This user needs sufficient authority to select from the desired source tables and to execute stored procedures located in the Agent User schema. Typically this will be an existing end user. This user is used when connecting to the source system from the Tier 3 SQDR platform and may be either a SQL Server user or a Windows user using Integrated Security.

 

Tier 1 Source Database (Oracle)
Parameter
Sample Value
Your Value
Password
Description
Hostname myhost     Hostname or IP Address of the source system
Port 1521     Port of the source system
Database MYDB     The database name (SID or Service Name) on the source system
PDB       Pluggable database name (Oracle 12c multitenant container database)
Installing User SYSTEM     This user should have sufficient authority to create a new user and grant authorities to it. It is used by SQDR Control Center when it creates a Staging Agent.
Agent User and Schema

SQDR
C##SQDR

    This user is created by the SQDR Control Center (if it does not already exist) when it creates a Staging Agent. In addition to the user, the SQDR Control Center will create and populate a collection (schema) by the same name. The collection contains control tables and stored procedures used by SQDR and SQDR Plus.
Source Database User SQDRUSER     This user needs sufficient authority to select from the desired source tables and to execute stored procedures located in the Agent User collection. Typically this will be an existing end user. This user is used when connecting to the source system from the Tier 3 SQDR platform.

 

Tier 1 Source Database (Informix)
Parameter
Sample Value
Your Value
Password
Description
Hostname myhost     Hostname or IP Address of the source system
Ports 9088, 9089     Informix and DRDA ports of the source system. The SQDR Plus agent on Tier 2 connects to the Informix source with the Informix port; connections from theTier 3 SQDR platform can be made using either port, depending on the choice of ODBC driver.
Database mydatabase    

The database name on the source system. To list the databases on the source system, use the statement SELECT name
FROM sysmaster:sysdatabases;
or use dbaccess or OpenAdmin Tool.

Database User

informix

    This user should have DBA authority and is used by SQDR Control Center to create a schema and contents during creation of a Staging Agent and schema, and to monitor database logs for change data during operation.
Agent Schema sqdr     This schema is created by SQDR Control Center (if it does not already exist) when it creates a Staging Agent. The schema contains control tables and stored procedures used by SQDR and SQDR Plus.
Source Database User sqdruser     This user needs sufficient authority to select from the desired source tables and to execute stored procedures located in the Agent User schema. Typically this will be an existing end user. This user is used when connecting to the source system from the Tier 3 SQDR platform.
Tier 1 Source Database (MySQL/MariaDB/Aurora)
Parameter
Sample Value
Your Value
Password
Description
Hostname myhost     Hostname or IP Address of the source system
Port 3306     port of the source system.
Agent User SQDR     This user should be created before creating the SQDR Plus agent, and be granted authorities as described in the Quick Start Guide.
Agent Database (Schema) SQDR     This database (schema) is created by SQDR Control Center (if it does not already exist) when it creates a Staging Agent. The schema contains control tables used by SQDR and SQDR Plus.
Source Database User sqdruser     This user needs sufficient authority to select from the desired source tables and the SQDR Plus control tables located in the Agent User schema. Typically this will be an existing end user. This user is used when connecting to the source system from the Tier 3 SQDR platform.


Tier 2 SQDR Plus Platform (Windows)
Parameter
Sample Value
Your Value
Password
Description
Installing User       This user should be able to log on to the Tier 2 SQDR platform and have sufficient authority to install software, create local users, and manage services. This may be either a local or a domain user.

DB2 Service User

db2admin     During installation of DB2 LUW, you are offered an option to create a new user for running the DB2 services, or using Local System Account. You can choose either option; we recommend either choosing Local System Account or creating a user named db2admin.
SQDR Plus Schema Owner SQDR DO NOT CHANGE   This user is used to access the SQDR Plus Staging Agent control databases (local DB2) from the Tier 3 SQDR platform. It is created as a prerequisite before installing SQDR Plus on the the Tier 2 Platform. This may be either a local or a domain user.
SQDR Plus Launch Agent Schema Owner SQDR DO NOT CHANGE   This user is used to access the SQDR Plus Launch Agent control database (local Derby database). This is the userID used when connecting to the SQDR Plus Manager through a browser. You will be prompted to create the password for this user during installation.
SQDR Control Center port (jetty) 8080     Prompted during installation
SQDR Control Center SSL port (jetty) 8443     Prompted during installation
DB2DFTDBPATH D:\     Location of local DB2 database files. Prompted during installation. This value is a drive letter; default is the drive that DB2 is installed on.
DB2NEWLOGPATH E:\DB2Logs     Location of local DB2 logs. Prompted during installation. This value is a directory path; default is the root directory of the drive that DB2 is installed on.

 

Tier 2 SQDR Plus Platform (Linux)
Parameter
Sample Value
Your Value
Password
Description
Installing User       This user should be able to log on to the Tier 2 SQDR platform and have sufficient authority to install software, create local users, and manage services. This is typically root.

DB2 Instance User

db2inst1     This user is created by the DB2 installer and is used to run the DB2 services..
SQDR Plus Schema Owner sqdr DO NOT CHANGE   This user is used to access the SQDR Plus Staging Agent control databases (local DB2) from the Tier 3 SQDR platform. It is created as a prerequisite before installing SQDR Plus on the the Tier 2 Platform.

SQDR Plus Service User

sqdr     SQDR Plus Service User: This user is used to run the SQDR Plus services. It must be created before installing SQDR Plus and granted SYSADM privileges to the local DB2 instance used for SQDR Plus control databases - i.e. it should be a member of the DB2 SYSADM group. We recommend using "sqdr" - ie. use the same user for both Schema Owner and Service User.

DB2 SYSADM group

db2grp1     This is an operating system group that includes the SQDR Plus Service User and has been defined to the local DB2 with UPDATE DBM CFG USING SYSADM_GROUP. Edit /etc/group and add the SQDR Plus Service user (sqdr) to this group.
SQDR Plus Launch Agent Schema Owner SQDR DO NOT CHANGE   This user is used to access the SQDR Plus Launch Agent control database (local Derby database). This is the userID used when connecting to the SQDR Plus Manager through a browser. You will be prompted to create the password for this user during installation.
SQDR Control Center port (jetty) 8080      
SQDR Control Center SSL port (jetty) 8443      
DB2DFTDBPATH /disk2/DB2     Location of local DB2 database files. Configured in setup.conf. The directory must exist and be writable by the DB2 instance owner (e.g. db2inst1). Leave blank to use the default, which is typically the home directory of the DB2 instance home (e.g. /home/db2inst1/db2inst1).
DB2NEWLOGPATH /disk3/DB2Logs     Location of local DB2 logs. Configured in setup.conf. The directory must exist and be writable by the DB2 instance owner (e.g. db2inst1). Leave blank to use the default, which is typically the home directory of the DB2 instance home (e.g. /home/db2inst1/db2inst1).

 

Tier 3 SQDR Platform (Windows)
Parameter
Sample Value
Your Value
Password
Description
Installing User       This user should be able to log on to the Tier 3 SQDR platform and have sufficient authority to install software, create local users, and manage services.
SQL Server Administrator sa     A user with system administrator ("sa") authority or who is a member of the sysadmin fixed server role on the SQL Server database, or has Windows administrative authority to install a version of SQL Server on the Tier 3 SQDR platform.

 

Tier 4 Destination Database
Parameter
Sample Value
Your Value
Password
Description
Destination Database User       This user should have sufficient authority to create and populate database tables on the destination. In the example in this Quick Start Guide, we will be using a local SQL Server database as a destination.

 

Logging and Notification Parameters

Configuration Parameter

Sample Value

Your Value

Log Level INFO  

Email Notification Level:

SEVERE

 

SMTP Server:

mail.mydomain.com

 

From:

[email protected]

 

To:

[email protected]

 

 

Refer to the following table when configuring inbound and outbound firewall filters on the various tier platforms. All ports are TCP except 2728, which is UDP.

TCP/IP Port Usage (default values)
Port
Description
Tier 1
(DB2 for i)
Tier 1
(DB2 for LUW)

Tier 1
(SQL Server)

Tier 1
(Oracle)
Tier 1 (Informix) Tier 1 (MySQL)
Tier 2
Tier 3
Tier4
446 DDM/DRDA
(DB2 for i)
IN             OUT IN (DB2 for i or DB2 for z/OS destination)
50000 DRDA
(DB2 for LUW)
  IN        

IN

OUT (if tier 1 is DB2 for LUW)

OUT IN (DB2 for LUW destination)
9088
Informix         IN   OUT (if tier 1 is Informix) OUT (if tier 1 is Informix) IN (Informix destination)
9089 Informix
DRDA
        IN     OUT (if tier 1 is Informix) IN (Informix destination)
449, 8470 through 8476 Host Servers for i Access and JTB IN           OUT (if tier 1 is DB2 for i)    
8080, 8443 Jetty
(SQDR Control Center)
            IN (for remote access to SQDR Control Center)    
22 ssh   IN (Linux or UNIX)   IN (Linux or UNIX)     IN (Linux)    
23 telnet IN IN (Linux or UNIX)   IN (Linux or UNIX)     IN (Linux)    
3389 RDP   IN (Windows) IN (Windows) IN (Windows)     IN (Windows) IN  
4999 StarLicense             IN OUT  
4997 StarLicense failover             IN    
2728/UDP SQDR notification             OUT IN  
1433 SQL Server
(default instance)
    IN       OUT (if tier 1 is SQL Server) OUT (if Tier 4 is SQL Server) IN (SQL Server destination)
1521 Oracle       IN     OUT (if tier 1 is Oracle) OUT (if Tier 4 is Oracle) IN (Oracle destination)
3306 MySQL           IN OUT (if tier 1 is MySQL) OUT (if Tier 4 is MySQL) IN (MySQL destination)

KEY:
REQUIRED
CONDITIONAL

 


DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.