StarQuest Technical Documents

SQDR Plus: Migrating to a New Server

Last Update: 24 April 2018
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL025

Abstract

This technical document describes the process for moving an existing production SQDR replication environment to a new system. This note describes moving only tier 2 (SQDR Plus) to a new system and assumes that tier 3 (SQDR) will not be migrated; see SQV00PL023 SQDR and SQDR Plus: Migrating to a New Server for a more extensive migration involving both tier 2 and tier 3 (SQDR Plus and SQDR).

This document assumes that the original and new systems are running similar operating systems (e.g. migrating from 64-bit Windows to 64-bit Windows or 64-bit Linux to 64-bit Linux). It also assumes that both systems are running similar versions of DB2 LUW (e.g. 10.5).

The process below describes moving only one agent, with a home directory named agt0 and a staging database named SQDRP0. If you have multiple agents, use the appropriate directory and database names. It also assumes that there is no need to keep the existing production replications in operation during the move - i.e. we can stop all SQDR services on the old system, rather than stopping and moving one agent at a time.

On Linux, use the root user to install and license DB2 and install the SQDR Plus software, and use the instance owner (e.g. db2inst1) to perform DB2 operations such as RESTORE.

Overview

Preparation

  • Download software in advance of the migration
  • Examine the existing environment to determine what needs to be moved to or recreated on the new system
  • Make sure that we know the user credentials that will be needed
  • Verify connectivity between the systems

Initial setup of new system

  • Install and license DB2 LUW on the new system
  • Install SQDR Plus on the new system
  • Install DB2 Data Studio Client and Data Studio Web Console if desired
  • Rebind StarSQL packages on the source system if necessary
  • Create local Windows or Linux user sqdr with same password as original system.
  • Instantiate the DB2 function/jar directory by creating a temporary agent with SQDR Control Center, or by invoking SQLJ.REPLACEJAR().

Move SQDR Plus Agent

On the old machine:

  • Pause I/R groups and stop SQDR
  • Stop SQDR Plus Jetty & Launch Agent services service on old machine
  • DB2 backup of SQDRP0
  • Copy DB2 backup image and agt0 subdirectory to the new machine

On the new machine:

  • Stop SQDR Plus jetty & derby services
  • Restore SQDRP0 and verify access. Compact the DB2 table spaces if necessary.
  • Copy agt0 home directory to conf directory and copy node.properties from agt0 to the conf root
  • Using a temporary control database, replicate one row of SQ_AGENTS from Derby (old system) to Derby (new system)
  • Update host components (DB2 for i source)

Preparation

Environment

Examine the existing environment to determine what needs to be moved to or recreated on the new system:

  • What is the operating system? - special considerations apply if Windows 2003 or a 32-bit OS.
  • What version of DB2 LUW is running - e.g. DB2 10.1 or 10.5; Express or Express-C. Use the command db2licm -l.
  • In some cases, SQDR Control Center on the existing system may have been configured to use a port other than 8080 - e.g. existing application ServeRAID already using 8080. Determine if the new system also has the requirement. Even it does, you may wish to continue using the non-default port if you have existing bookmarks on other systems.
  • What are the versions of SQDR & SQDR Plus?

Gather information about SQDR Plus

  • Determine the database (e.g. SQDRP0) and home directory (e.g. agt0) being used by the agent. The home directory is displayed on the list of agents when you select the top item (system name) in Control Center; to obtain the database name, select the agent and expand the Configuration settings window and examine controlDbUrl.
  • Check agent's configuration for non-default properties such as clientODBCString property

Gather Credentials

  • Local Windows or Linux user sqdr - we will be creating an identical user on the new system
  • Derby user sqdr - we will be creating a snapshot replication to replicate specific contents of the Launch Agent control database to the new system. The Derby passwords on the two systems do not need to match, but you do need to know both passwords.
  • If you are also updating the version of SQDR Plus and the source is DB2 for i, you will need a QSECOFR type of user on the iSeries - this is needed to update the host components and to bind packages in the SQDR collection. See SQDR Plus v4_IBMi User Authorities

Verify Connectivity

  • Verify that we can connect to Derby on the old machine - possible issues: firewall
  • Access to the iSeries source from the new machine - possible issues: firewalls, different networks, DNS, exit programs that restrict access
  • Access to the destination from the new machine
  • If firewall is enabled on the old machine, make sure that it has exceptions for Derby (port 1527)

We will also need a mechanism (e.g. a network folder) to transfer several files (backup of SQDRP0 and the agt0 subdirectory) from the old machine to the new one. Use a zip or tar utility to move the agt0 subdirectory.

Initial setup on the new system

  • Identify drives or folders for DB2 tablespaces and logs (e.g. E: & L: on Windows; /DB2/data and /DB2/logs on Linux)..
  • Install DB2: choose custom. On Windows: unselect "Enable Operating System Security". Record the password for (local user) db2admin (db2inst1 on Linux) created by the installer.
  • license DB2
    db2licm -a db2exp_u.lic
  • Install Data Studio Client and Data Studio Web Console (optional).
  • Install SQDR Plus. Since we are moving only SQDR Plus, not SQDR, you can choose to skip the installation of SQDR (Windows). A fresh install of SQDR Plus will also create the SQDRC database (for use as the SQDR control database) and customize DB2 DBM (Database Manager) parameters.
  • Create local Windows or Linux user sqdr. The password must match the password on original system.
  • Instantiate the stored procedure JAR file:
    Windows : C:\ProgramData\IBM\DB2\DB2COPY1\\function\jar\SQDR\CAPTUREPROCS_JAR .jar
    Linux: /home/db2inst1/sqllib/function/jar/SQDR
    /CAPTUREPROCS_JAR.jar
    using either of the following methods:
  • -- Create a temporary SQL Server database and an agent to it
    • On the old machine, use SQL Server Management Studio to create a scratch SQL Server database.
    • Create an agent to that database
    • Delete the temporary agent - make sure SQDRP0 gets dropped and agt0 subdirectory deleted.
    • On the old machine, use SQL Server Management Studio to drop the scratch database.
  • -- Connect to SQDRC and call sqlj.replace_jar()
    • Create the directory C:\ProgramData\IBM\DB2\DB2COPY1\function\jar (Windows) or /home/db2inst1/sqllib/function/jar (Linux)
    • db2> connect to SQDRC user sqdr using password
      db2> CALL sqlj.replace_jar( 'file:C:\Program Files\StarQuest\sqdrplus\capagent\UDBProcs.jar', 'CAPTUREPROCS_JAR' )
      or
      db2> CALL sqlj.replace_jar( 'file:/opt/\StarQuest/sqdrplus/capagent/UDBProcs.jar', 'CAPTUREPROCS_JAR' )
      db2> commit
      db2> disconnect SQDRC

       

Move SQDR Plus Agent

On the old system:

  • Make sure we know the following info for the agent to be moved:
    - agt0 directory
    - SQDRP0 database
    - the name of the agent as known to Derby
  • Verify that there are no active connections to the SQDRP0 database with DB2 LIST APPLICATION. Since we have already stopped SQDR, Jetty & Capagent services, it is unlikely that there will be any active connections.
  • Create a DB2 backup of SQDRP0 - be sure to specify COMPRESS. This could take up to 30 minutes if the table spaces have grown.

Windows: db2 backup database SQDRP0 to E:\temp compress
Linux: db2 backup database SQDRP0 to /tmp compress

  • Copy the backup of SQDRP0 and the agt0 directory to the new system. If you are using ftp, be sure to specify binary transfer mode for the DB2 backup and the zip or tar archive of agt0.

On the new system:

  • Stop Jetty & Launch Agent services
  • Place agt0 directory in /ProgramData/StarQuest/sqdrplus/conf/ (Windows) or /var/sqdrplus/conf (Linux)
  • Copy conf/agt0/node.properties to conf (rename the original just in case)
  • Use SQDR to do a snapshot of the one row of interest in Derby SQ_AGENTS:

Source:
Server=SQDRPLUS;HostName=old-sys;Port=1527
Advanced - turn off incremental

Destination:
Server=SQDRPLUS;HostName=new-sys;Port=1527

Snapshot:
destination: Use existing table
Criteria: CONTROLDB='SQDRP0' (this should return one row)

(Note: on LInux, you can also transfer the entire directory /var/sqdrplus/derby/SQDRPLUS instead of using snapshot replication).

If the location of the StarQuest ProgamData has changed - for example, when migrating to a clustered configuration where ProgramData is located on the shared S: drive rather than C: - then update the DIRECTORY column of the Derby SQ_AGENTS table. See Changing SQDR Plus ProgramData directory.

  • Restore SQDRP0
    Windows: RESTORE DATABASE SQDRP0 from E:\TEMP ON E: DBPATH ON L: comprlib db2compr.dll
    Linux: RESTORE DATABASE SQDRP0 from /tmp ON /DB2/data DBPATH ON /DB2/logs comprlib libdb2compr.so

Windows: If you get an authorization error for the RESTORE operation and the DB2 commands described in the next steps, shift/right-click on "DB2 Command Window" in Program group and select "run as another user", and enter the credentials for user db2admin (which you just created as part of the installation of DB2).

db2 connect to SQDRP0
db2 -x "SELECT 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),';' FROM SYSCAT.TABLES WHERE (type= 'T') AND (tabschema = 'SQDR')" > reorg_sqdr.sql
db2 -tvf reorg_sqdr.sql
db2 ALTER TABLESPACE USERSPACE1 REDUCE MAX

  • If desired, connect to SQDRP0 and run these commands:
    db2 update db cfg using AUTO_MAINT ON
    db2 update db cfg using AUTO_TBL_MAINT ON
    db2 update db cfg using AUTO_RUNSTATS ON
    db2 update db cfg using AUTO_REORG ON
  • Verify that you can connect to the database
    db2 connect to SQDRP0 user sqdr
    db2 disconnect SQDRP0
  • Create a new backup of SQDRP0 just in case
    Windows: db2 backup database SQDRP0 to E:\temp compress
    Linux: db2 backup database SQDRP0 to /tmp compress
  • Start SQDR Plus and verify that the agent is listed.
  • Starting the agent updates the ODBC connection string in SQ_PROPERTIES on the source.
  • If you are also updating the version of SQDR Plus, stop the agent and update iSeries host components - you will need QSECOFR credentials on the iSeries. Restart the agent.

In Data Replicator Manager:

  • Start the SQDR service.
  • Refresh the source (pick up the new ODBC connection string) by viewing its properties and making a change (e.g. change the notification address, click OK, change it back, and click OK)
  • Examine SQDR.SRCDEST in the SQDR control database (SQDRC for DB2 LUW; ControlDB for SQL Server) to make sure connection strings and dbms_type look OK.
  • Resume the paused I/R groups.

 

 


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.