StarQuest Technical Documents
Quick Start Guide to Using SQDR Plus - Worksheet
Last Update: 25 May 2023
Product: SQDR Plus
Version: 5x or later
Article ID: SQV00PU006WS
Abstract
The Quick Start Guide to Using SQDR Plus 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.
| 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. |
| 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) |
| 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. |
| 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 |
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. |
| 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 |
||
| 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. |
| 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. |
| Hostname | myhost | Hostname or IP Address of the source system | ||
| Port | 5432 | 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. | ||
| 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 used by SQDR and SQDR Plus. The name is also used for the Publication (within the database) and the Replication Slot (in the instance). | ||
| 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. |
| 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. |
| 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). |
| 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 | (when using SQL Server as the control database). 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. |
| 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. |
Configuration Parameter |
Sample Value |
Your Value |
| Log Level | INFO | |
Email Notification Level: |
SEVERE |
|
SMTP Server: |
mail.mydomain.com |
|
From: |
sqdrplus@mydomain.com |
|
To: |
sqdradmin@mydomain.com |
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.
(DB2 for i) |
(DB2 for LUW) |
Tier 1 (SQL Server) |
Tier 1 (Oracle) |
Tier 1 (Informix) | Tier 1 (MySQL) | Tier1 (PostgreSQL) | |||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| 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 (rare) |
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) | ||||||
| 5432 | PostgreSQL | IN | OUT (if tier 1 is PostgreSQL) | OUT (if Tier 4 is PostgreSQL | IN (PostgreSQL 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.