StarQuest Technical Documents

Quick Start Guide to Using SQDR Plus on Windows with IBM Db2 for i (Part 1)

Last Update: 13 April 2021
Product: SQDR Plus
Version: 4.0 or later
Article ID: SQV00PU006-WI

Abstract

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

This document describes preparing the source database system and installing and configuring the SQDR Plus Platform. After completing the tasks in this document, continue with the appropriate version of Quick Start Guide to Using SQDR Plus (Part 2), which describes installing, configuring, and running SQDR.

We recommend printing out this worksheet and filling in the values as directed by the instructions below.

This document is intended for first-time users of SQDR and SQDR Plus. Users who are upgrading an existing installation should refer to the SQDR Release Notes or SQDR Plus Release Notes for upgrade instructions.

If you encounter any problems while using the evaluation software, please open a support ticket with Stelo Customer Support at https://support.stelodata.com or call +1 415.669.9619 for assistance.

The estimated time to complete all tasks is approximately 2 hours.

 

SQDR Plus Overview

SQDR Plus Prerequisites:

• Tier 1 Source Computer Requirements and Preparation
  
• Tier 2 SQDR Plus Platform Requirements
  
• Tier 3 SQDR Platform Requirements
• Tier 4 Destination Requirements
  
  

Installation and Configuration Tasks:

• Download and Install the SQDR Plus Software
  
• Connect to the SQDR Control Center
  
• Add the Source Database with the SQDR Control Center
  
• Verify SQDR Plus Installation using SQDR Control Center
  

SQDR Plus Overview

The Stelo Data Replicator solution consists of several software components, and involves up to four systems, all loosely connected via TCP/IP. Figure 1 illustrates the four tiers. In some cases, multiple tiers may be running on the same physical server; for instance, Tier 2 (SQDR Plus), Tier 3 (SQDR Platform) and Tier 4 (Destination DBMS) may all be installed on the same Windows system. This document covers topics related to Tiers 1 and 2.

Figure 1: SQDR Plus Architecture

 

Tier 1: Source Database System

The source system contains the data to be replicated and may be running one of the following:

• IBM DB2 for i
• IBM DB2 for Linux, Unix, Windows (DB2 for LUW)
• Microsoft SQL Server
• Oracle
• IBM Informix
• MySQL, MariaDB, or AWS Aurora
• PostgreSQL

Tier 2: SQDR Plus Platform

This system may be running either Windows or Linux. A local control database (IBM DB2 for LUW) is used for staging transactions; when a change is made on the source system, the data is stored here temporarily until it has been retrieved by the SQDR clients and applied to the destination database.

Three Java-based services are installed on this system:

• SQDR Plus Derby service - the Apache Derby database is used as a control database for the SQDR Plus Launch Agent.
• SQDR Plus Jetty service - the SQDR Control Center web application, running under the Apache Jetty WebServer
• SQDR Plus Launch Agent service - the primary SQDR Plus service. The initial process is called the Launch Agent and is used to configure and launch Staging Agents for each configured source system.

The SQDR Plus installer includes a JRE (Java Runtime Environment) for running the three services.

The SQDR Plus platform can be configured and managed from any system using a browser and the SQDR Control Center web application.

Tier 3: SQDR Platform

The SQDR service is installed on a Windows or Linux system. The SQDR installation includes the Data Replicator Manager application (running on a Windows system) for configuring and managing the replications and the StarSQL ODBC driver for connecting to DB2 systems (including the SQDR Plus staging database). The SQDR service uses a local or remote DB2 LUW or Microsoft SQL Server database for its control database. Using a local DB2 LUW database is recommended, especially for the typical scenario where both tiers 2 and 3 reside on the same system. The SQDR installer is included in the SQDR Plus installer for Windows for simplified installation in the typical combined tier2/tier3 scenario.

SQDR on Linux is typically deployed as a Docker container, though it is also available as a set of RPM files.

Tier 4: Destination DBMS

No software is installed on this platform, but it must be accessible from the SQDR Platform.

SQDR Plus Prerequisites

We recommend printing out this worksheet and filling in the values as directed by the instructions below.

The same individual or a combination of individuals must be capable of performing in the following roles in order to successfully complete the evaluation.

• Tier 1 Database Administrator is someone with administrative authority on the source system.
• Tier 2 Administrator is someone with administrative authority to install SQDR Plus and a local DB2 LUW database on a Windows or Linux computer.
• Tier 3 Administrator is someone with administrative authority to install SQDR and either DB2 LUW or SQL Server database on a Windows or Linux computer. This may be the same role as Tier 2 Administrator in a typical combined tier2/tier3 deployment.
• Tier 4 Database Administrator is someone with administrative authority on the destination system.

 

Tier 1 DB2 for i host computer requirements

The DB2 for i host must be running v5r4 or later.

All tables to be replicated must be journaled, preferably as *BOTH rather than *AFTER. See Tips for Db2 for i (iSeries) Journaling for more information.

The SQDR Plus Staging Agent communicates with DB2 for i host using the System i® Access host servers (ports 449 and 8470-8476, or ports 449 and 9470-9476 for SSL), and the SQDR client machine communicates using DRDA (default port 446, or port 448 for SSL); be sure that the appropriate host servers are running on the System i host and that these ports are not blocked by firewalls.

Use DSPSYSVAL to check the system value QALWOBJRST. The value should be *ALL (default - Any object can be restored to your system by a user with the proper authority) or *ALWPGMADP (Objects that adopt authority can be restored to the system); otherwise creation of an agent or update of host components will fail with misleading error symptoms.

Use DSPAUTL QPWFSERVER to check the authorization list which may restrict access to QSYS.LIB objects from remote clients. If the setting for *PUBLIC is *EXCLUDE rather than the default of *USE, you will need to add the agent user (e.g. SQDR) with *USE authority after creation of the agent.

Caveat: Some Db2 for i tables, especially in older databases, may contain columns lacking a CCSID attribute. To ensure correct data conversion of such binary columns, you may need to set the StarSQL expert setting BinaryCCSID - see Data Conversion Issues with Binary Columns for details.

During creation of an SQDR Plus Staging Agent for the DB2 for i host, you will be prompted for the user name and password of a user with SECOFR authority (e.g. QSECOFR). A collection will be created on the host, and two additional users are created: a non-privileged user that is the owner of the collection (e.g. SQDR), and a privileged user that is the owner of a service program used for reading journal receivers (e.g. SQDRADM).

Tier 2 SQDR Plus Platform requirements

• We recommend printing out this worksheet and filling in the values as directed by the instructions below.
• The SQDR Plus Platform requires TCP/IP access to the Tier 1 database host and from the Tier 3 Windows platform.
• Use Regedit to configure TCP KeepAlive Settings.
• Create the following Windows account and record its password:
  • sqdr - SQDR client user. This account requires no special operating system authorities and can be either a local or a domain account, but we recommend using the name sqdr and its password must conform to the password complexity requirements of the Windows system. It is used for communication from the SQDR client platform and is the owner of the control tables and stored procedures stored in the SQDR Plus control database. You will be prompted for this user and password by the SQDR Control Center when creating a connection to the source database. We recommend configuring this user as "Password never expires" and uncheck "User must change password at next logon". To use a name other than "sqdr", see Tier 2 UserID Considerations.
• Verify that ports 8080, 8443, 50000, 50005, and some number of ports following 50005 (equal to the number of agents you plan to create) are not in use by another application. It is not uncommon for port 8080 to be used by a Java-based server such as tomcat or a RAID management tool:
  • Enter netstat -an | findstr LISTENING | findstr :8080
  • Open the Resource Monitor from the Performance tab of Task Manager and examine the Listening Ports displayed on the Network tab.
  • If ports 8080 and 8443 are in use, choose different jetty listening ports during the installation of SQDR Plus.
  • If port 50000 is in use, choose a different port when installing Db2. In addition, you will need to add the clientODBCString property to the configuration of all agents; see Configuration Reference in the Control Center Help.
  • If port 50005 (and some number of following ports equal to the number of agents you plan to create) are not available, choose another port range and follow the instructions in Configuring Non-standard RMI Ports.
• Install Db2 LUW (Linux, UNIX & Windows) 11.5.5fp1 or later for use as a control database and temporary store for SQDR Plus. Db2 LUW is bundled with SQDR Plus.
  
• The Db2 installer will create an instance that starts automatically with the system. Examine the properties of the instance to determine the port number; the default port number is 50000.
• Plan the location of Db2 database files and logs; we recommend using high performance drives (e.g. SSD) and isolating the disk activity of Db2 database files and logs from other system activity. We recommend specifying those locations during installation of SQDR Plus.

• If a firewall is enabled, create inbound exceptions for the following TCP ports:
  • SQDR Control Center port (jetty) - default 8080
  • SQDR Control Center SSL port (jetty) - default 8443
  • Db2 (SQDR Plus control database) port - default 50000

Tier 3 SQDR Platform requirements

See the Quick Start Guide to Using SQDR Plus (Part 2) for considerations for the Tier 3 Windows platform that will run SQDR.

 

Tier 4 Destination database requirements

• The destination DBMS must be accessible from the Tier 3 SQDR platform.
• If the destination is SQL Server, we recommend the following settings be applied on the destination database:
  ALTER DATABASE [mydb] SET allow_snapshot_isolation ON
  ALTER DATABASE [mydb] SET read_committed_snapshot ON

See Avoiding Locks for SQL Server Destination for details.

Summary of tasks

Perform the tasks in the order described, noting that some provide information used in subsequent steps. The detailed instructions for each task follow this summary.

1. Request to download SQDR Plus.
2. Receive the e-mail with a Registration Key and download links for StarAdmin, SQDR, and SQDR Plus.
3. Print the SQDR Plus Worksheet and use it as a guide while following the instructions below.
4. Tier 1 Database Administrator: verify pre-requisites on the host database.
5. Tier 2 SQDR Plus Platform Administrator: Download SQDR Plus using the download link from step 2, complete the pre-installation tasks (including creating users and installing a local copy of Db2 11.x for use as the SQDR Plus control database), and install the software on the SQDR Plus server.
6. Tier 1 SQDR Plus Platform Administrator or Tier 1 Database Administrator: Use a browser to connect to SQDR Control Center (SQDR Manager) to verify installation.
7. Tier 1 Database Administrator: Use SQDR Control Center to configure a connection to the source database.
8. Tier 1 Database Administrator or Tier 2 SQDR Plus Platform Administrator: Verify SQDR Plus installation and test Support functions using SQDR Control Center.
9. Continue to Quick Start Guide to Using SQDR Plus (Part 2), which describes installing, configuring, and running SQDR on the Tier 3 platform.

Step 1: Request SQDR Plus Software

Estimated Time: 5 minutes

All software packages are distributed as compressed files that you download from the StarQuest Ventures Web site. From a web browser enter the address http://www.starquest.com/ and click on Download for Trial under the Products menu. Request to download SQDR Plus.

Step 2: Receive the Download Confirmation E-mail from StarQuest

Estimated Time: 5 minutes

After submitting a request to download SQDR Plus, you will receive an e-mail containing a Registration Key and download links for the SQDR Plus, StarAdmin, and SQDR software. The registration key will be used in step 4b in Part 2 to obtain temporary license keys valid for 15 days and the download links will be used below.

Step 3: Print the SQDR Plus Worksheet

Step 4: Verify prerequisites on the Tier 1 Source Database system

See Tier 1 host computer requirements above.

Step 5: (Tier 2 SQDR Plus Platform Administrator) Download and Install the SQDR Plus Software

Estimated Time: 10 minutes

1. Verify the Tier 2 prerequisites: DB2 Workgroup for LUW 11.5.5 should be installed and running, firewall exceptions defined, and Windows user sqdr created.
2. Using the link provided in the confirmation e-mail, download the SQDR Plus software. Right-click and select Properties of the zip file and unblock it if necessary. extract the .zip file in a temporary folder.
3. Log on to the computer as a user with Administrator rights, and change to the directory where you extracted the installer.
4. Execute setup.exe to launch the installer.
5. The following dialog will appear; click Install whether or not you plan to install SQDR on this system.
   

   

6. The following dialog will appear; click Yes to install SQDR on this machine (typical combined tier 2 & 3 scenario).
   

   

7. A progress dialog is displayed while SQDR is installed.
8. On the Welcome dialog, click Next.
9. Accept the License Agreement and click Next.
10. The Service Parameters dialog will appear :

11. Enter the following values and press Next:

• Password for Launch Agent Control Database (Derby): Enter a password that will be used for access to the Derby control database used by the Launch Agent. Do not supply a sensitive password, as this value is stored in a non-secure manner. You will be prompted for this password when connecting to SQDR Control Center.
• Port for SQDR Control Center (jetty): default 8080
• SSL Port for SQDR Control Center (jetty): default 8443

12. The DB2 Parameters dialog will appear; this allows you to specify the location of the database files and logs used by the local DB2 LUW staging database.

    

13. Select the desired drive locations for DB2 database files and DB2 logs.
14. The default value for DB2 Instance is obtained from the DB2INSTANCE environment variable. Change this value if you will be using a different DB2 instance for the staging databases (e.g. in a Windows cluster environment).
15. The name of the DB2 service is displayed for informational purposes. The value may vary depending on the version and edition of DB2 being used, or if you are using a Windows clustered environment.
16. Select Next.
17. Accept to install SQDR Plus in the default directory of C:\Program Files\StarQuest\sqdrplus or browse to select an alternate directory. Click Next to continue.
18. Accept to place SQDR Plus Program Data in the default location of C:\ProgramData\StarQuest or browse to select an alternate directory. Click Next to continue.
19. Click Install to begin the installation or click the Back button to make any changes.
20. A command window will appear briefly while the installer creates a DB2 database SQDRC for use as SQDR control database and updates DB2 database manager parameters. Review the results and and hit Enter to close the window when the operations complete.
21. After the installation is complete, click Finish.
22. Open the Services control panel and verify that all three SQDR Plus services are active.

Step 6: (Tier 2 SQDR Plus Platform Administrator or Tier 1 Database Administrator) Connect to the SQDR Control Center

The SQDR Control Center (formerly known as SQDR Manager) is a web-based application. For best results, we recommend using the Chrome web browser. If you are using Internet Explorer on a Windows Server system and Enhanced Security Configuration is enabled, open Internet Options from the Tools menu and add the URLs described below to the Trusted Sites zone on the Security tab; you may also need to enable Javascript.

To verify the SQDR Plus installation, connect to the SQDR Control Center with a browser by either selecting the SQDR Control Center shortcut in the SQDR Plus program group (Windows only), or by starting a browser and entering the URL http://127.0.0.1:8080/SQDRManager (assuming you accepted the default jetty port values during installation).

You can also connect to the SQDR Control Center from another machine by starting a browser and entering the URL http://mysqdrplus:8080/SQDRManager or https://mysqdrplus:8443/SQDRManager (where mysqdrplus is the hostname of the SQDR Plus platform). Be sure that the firewall has been configured to accept incoming connections on these ports.

If you select the SSL connection and are using the self-signed SSL certificate included with the installation, the browser will issue a warning about the certificate; allow an exception to connect.

When prompted for credentials, enter user SQDR (upper-case) and the Launch Agent Control Database (Derby) password you supplied during installation.

If the browser is unable to connect to the SQDR Control Center, check the following:

• Open the Services control panel and verify that all three SQDR Plus services are started.
• If the services are not started, use the SQDR Plus program group shortcuts to examine the Launch Agent log and Derby log, as well as wrapper.log for each of the three services. The default location of the log files is
  C:\ProgramData\Application Data\StarQuest\sqdrplus.
• Examine the Windows event log for any errors related to starting the services.
• If the services are running, verify that the ports are not being blocked by a firewall
• Verify that the ports are not in use by another application. Stop the SQDR Plus services and view the ports in use by entering netstat -a -o or by opening the Resource Monitor from the Performance tab of Task Manager and examining the Listening Ports displayed on the Network tab. It is not uncommon for port 8080 to be in use by a Java-based server such as tomcat or a RAID management tool.
• If anti-virus software is installed, verify that it is not interfering with operation.

Specifying the location of the DB2 LUW control database files and logs: If you wish to specify the location of the database files or logfiles used for the local DB2 LUW control database (for example, you wish to place them on a high speed drive) and you did not specify the desired locations during installation of SQDR Plus, change them now by editing the Launch Agent's configuration and restarting the Launch Agent service - see Specifying the location of DB2 database files or Specifying the location of DB2 logfiles in Tips for the SQDR Plus DB2 LUW Staging Database.

Step 7: (Tier 1 Source Database Administrator task) Add the Source Database with the SQDR Control Center

Estimated Time: 5 minutes

1. Connect to SQDR Control Center with a browser as described in the previous section.
2. Logon as user SQDR, using the Launch Agent Control Database (Derby) password as supplied during installation.
3. Select Add Database from the Databases drop-down menu.

 

4. Choose the appropriate source database type (IBM i) and select Continue. You will see the following screen:

5. Enter the following values:

• Database: database name (RDB) of the IBM i source system. Use the DSPRDBDIRE CL command to obtain this name.
• Host Name : Host Name or IP address of the IBM i source system.
  
• Userid: the installing user. This is an existing user on the IBM i source system with *SECOFR authority (or with *SECADM and *ALLOBJ authority)
  
• Password: password for the Userid
  
• Control schema: use the default value (SQDR) unless there are multiple SQDR Plus systems accessing this host database. This value is also used as the Agent User, a user that will be created on the host system if it doesn't already exist. The Agent User will be the owner of the control schema (collection) and its contents.
• Use SSL: If you wish to use SSL (Secure Sockets Layer) to connect to the DB2 for
  i source system, select the Use SSL checkbox and select Manage Certificates to open
  a new browser window where you can add the IBM i system's Certificate Authority
  certificate. After adding the certificate, you can close the new browser window and
  continue with the wizard to Add a Database. See Configuring SSL to DB2 for i Source in the Control Center help
  for details.

6. Select the Next button. You will see the following screen:

 

7. The values in the left column refer to the local Db2 for LUW database system which was installed as part of the Tier 2 SQDR Plus Platform Prerequisites above. Enter the following values:

• Stage to local DB2 database: enabled (default)
• Control Database: select <New Control DB> or choose an existing database (if one exists).
• Port Number: enter the port of the local DB2 database. Default is 50000.
• Userid: enter sqdr.
• Password: enter the password of the Windows or Linux user sqdr created on the SQDR Plus platform as part of the Tier 2 SQDR Plus Platform Prerequisite above.

 

The values in the right column refer to userID's to be created on the IBM i source system:

Agent User: This value is the Control Schema name entered on the previous dialog and cannot be modified on this screen.

Agent Password: Enter the password for the Agent User. If the user already exists, it must match the current password.

Privileged Owner: Enter the name of a user that will be created with *SECADM and *ALLOBJ authority; This user will be created on the source system if it does not already exist. It will be the owner of the remote journal reader service program.

Privileged Password: Enter the password for the Privileged Owner. If the user already exists, it must match the current password. After the agent is created, the password for the privileged owner can be set to *NONE.

See the technical note SQDR Plus v4 IBM i User Authorities for more information about the three IBM i userID's (installing user, agent user, and privileged user).

.

8. Select the Next button and configure the logging and optional email notification values. You can leave these fields blank during the installation and configure the email notification feature at a later time.

9. Select Next to create the Agent. This operation will take several minutes.
10. Verify that the new DB2 LUW control database (e.g. SQDRP0) was created on the desired drive (as specified in task 3 in Step 7 above) - e.g. if you specified the D: drive, expand D:\DB2 and look for a folder named SQDRP0.
11. When you see a plus sign next to Databases, expand Databases and select the new database.
12. Choose Start/Restart Agent from the Database drop-down menu.

Selecting Start/Restart Agent creates the Staging Agent on the SQDR Plus system and creates configuration tables and stored procedures on the source system. This process may take several minutes; a green checked icon will appear next to the database name when the process is complete. If the icon remains red or yellow, additional troubleshooting may be required before proceeding.

Step 8: (DB2 Administrator task) Verify SQDR Plus Installation using SQDR Control Center

Estimated Time: 5 minutes

Use the SQDR Control Center to verify that the Capture Agent is running and to test the Publication and Support functions.

1. Connect to SQDR Control Center with a browser and logon as described above.
2. Expand Databases and select the desired database. Verify that the Staging Agent is running by observing that there is a green checked icon next to the database name.
3. Verify that the SQDR Plus platform can send SQDR Plus support logs to StarQuest Customer Support by selecting Send Logs to StarQuest from the Support drop-down menu. This will collect and transfer support files directly to StarQuest. If the SQDR Plus platform is not able to reach external ftp sites, select Save Logs to local file system (as zip file) to create and store the support files on an alternative computer. Send these files to StarQuest Customer Support if requested to do so.
4. Test the Publication function. Note that publishing tables before creating subscriptions is required for Oracle source databases.

 

1. Select the Agent and then select the Publications panel in the right window.
2. Next select Add Publication from the Resource drop-down menu or the Plus sign from the top of the window.
3. For DB2 for i and Oracle source databases, you will be prompted for the credentials of a user with sufficient authority to grant access to the Agent user (e.g. SQDR) for the tables to be subscribed to; this will typically be a user with SECOFR or SYSTEM authority.
4. Enter schema and table names (you can use SQL wildcards like %).
5. Click Search
6. Select one or more tables to be published. A state of "Published" indicates a successful publication.

Next Steps

Continue with the appropriate version of Quick Start Guide to Using SQDR Plus (Part 2), which describes configuring and running SQDR on the Tier 3 platform.